Performance issues while processing Excel file in IS on HP/UX using JExcel

hello,all
We are using IS6.1 on HP-Unix. Recently, we wrote a java service in wm to process excel file. Each file is about 2M.And we are using jxl api. But it is really slow for the processing. And sometimes the service crashed so we had to restart the IS server. We don’t know if there is anything to do with webmethods or unix enviroment. 'cause the program runs well on windows itself.
Has anybody experienced this situation? And suggustions are highly appreciated.

Nancy

Nancy,

Are you saying that when you run your java service that reads Excel data in an Integration Server running on Windows performance is acceptable, but on an IS server running on HP/UX, it is not?

Or are you saying that when you run the java program which uses the JExcel API outside of IS that performance is OK?

mark,
Are you saying that when you run your java service that reads Excel data in an Integration Server running on Windows performance is acceptable, but on an IS server running on HP/UX, it is not?
Yes, it is. IS running on windows is much better than on HP unix.

Nancy

Nancy,

I’m not familiar with the JExcel API or how easy or complicated it is, but I have a quick question: would it be a possibility for the users (whoever they are) to save the Excel spreadsheet as a CSV or text file (ex. tab delimited) instead of (or in addition to) an XLS file? If they did, then you should be able to simply use the flat file adapter to read in the data and not have to worry about implementing your own Java services and etc.

Just an idea, but I realize it may not be applicable to your scenario.

  • Percio

Nancy,

Where are the files stored when they are processed by the IS running on HP/UX? Are they local to that server or accessed over a network link? Is it possible to attach the code which uses JExcel?

Mark

Percio
Thanks for your comments. But the customers are prefer to Xls documents.
Mark
The files are sent by Enterprise, accept by wm trigger. And We use base64 to do the preprocess so the file could be sent in binary array. That way, wm get the array and first doing the base64decode and call the java service in the attachment.

Thanks again.Mark

Nancy
xlscode.txt (1.7 KB)

I have had some experience with using the API from the open source POI - HSSF projects http://jakarta.apache.org/poi/hssf/.
I found that unless you have complete lockdown on the excel file so that only data can be input you will have problems - crashes etc. And the processing was slow, yes.
Java APIs for handling microsoft files will always lag behind the actual internal format microsoft uses. Microsoft does not always keep to their published standards. The format is convoluted - new capabilities seem to have been tacked on rather than properly integrated, there are dozens of pointers and dead-ends and so forth. That is why the project was called HSSF - Horrible Spread Sheet Format.
The only way to reliably handle microsoft files is with microsoft APIs.
When microsoft start using XML as their file format for excel maybe things will get better. As long as they do not keep changing their XML schemas (or DTDs) or worse bypass or stop using them (or use them in strange ways - I have seen this with MS Project Server PDS). At microsoft’s mercy I guess.
Microsoft - Refactoring, whats that?

We changed Jxl to org.apache.poi.hssf, much better than before.
Thanks NeilClifton and Mark.

Nancy

Hi there,
I have tried the POI. The processing speed is very fast. 200000 records (500 Bytes each) can be parsed within 5 second. No crushes happened.

You might have to monitor your memory, CPU and HD occupation in HPUX to decide where is the bottleneck if you have to use JXL.

To convert XLS to CSV is also a good method to parse large Excel file since Flatflie parser can parse the file section by section.

Hi Nancy,

Can you please tell me the .jar files which are required to run the code which you have provided. Also, let me know the imports required in the java service as I have the similar requirement to parse the .xls file.

Thanks & Regards,
Ravikiran.