Joe C said:
How robust is the Jakarta POI (HSSF) package at reading Excel files. I
have Excel files with many work sheets and some that have pivot tables
were the data rows have been collapsed when saved. Can the HSSF
package read multiple work sheets ? Will it be able to read data rows
that have been collapsed ? Are there any limitations on the size of
Excel files that can be read ?
Have you tried it?
Are there any other java based packages/products that can read Excel
files ?
Yes. JExcel
I'd probably start with POI. Try it. There is also a POI mailing list
which you probably find better for specific questions.
I had a very complex workbook .... I think 60 worksheets or so. Both POI and
JExcel would not modify my spreadsheet ... from what I can gather some of
the complexities of the spreadsheet were not supported. It would have taken
a long time to eliminate what it was though.
I found ExcelAssesor
http://www.alphaworks.ibm.com/alphabeans/excelaccessor/
It is a Windows specific solution though.
Once you cut through the surrounding "fluff" with the use of visual age for
java, this worked with my complex spreadsheet.
Here is some rough code that worked at the time ... I may have changed it
since. If you went this way I can probably find it.
public void testWritingToWorkBook2()
{
String value = "Test";
String fileName = "c:\\TestBook1.xls";
String sheet = "Sheet1";
ExcelWorkbook workbook = new ExcelWorkbook();
workbook.setReadOnly(false);
workbook.setWorkbookName(fileName);
for (int i = 1; i < 9; i++)
{
String range = "b" + i + ":b" + i;
createCell(workbook, sheet, range, value + 1);
}
workbook.openWorkbook();
workbook.closeWorkbook();
workbook.closeExcelApplication();
}
private void createCell(ExcelWorkbook workbook, String worksheet, String
range, String value)
{
ExcelCell cell = new ExcelCell();
cell.setRangeName(range);
cell.setWorksheetName(worksheet);
cell.setXlWorkbook(workbook);
cell.initCell();
cell.setValue(value);
}
HTH
Shane
