2009. 10. 14. 18:59
Programming/Java
[Reference] http://www.andykhan.com/jexcelapi/tutorial.html#reading
Reading Spreadsheets
JExcelApi can read an Excel spreadsheet from a file stored on the local filesystem or from some input stream. The first step when reading a spreadsheet from a file or input stream is to create a Workbook. The code fragment below illustrates creating a workbook from a file on the local filesystem.
import java.io.File;(NOTE: when creating a spreadsheet from a ServletInputStream you must remove the HTTP header information before creating the Workbook object.)
import java.util.Date;
import jxl.*;
...
Workbook workbook = Workbook.getWorkbook(new File("myfile.xls"));
Once you have accessed the workbook, you can use this to access the individual sheets. These are zero indexed - the first sheet being 0, the second sheet being 1, and so on. (You can also use the API to retrieve a sheet by name).
Sheet sheet = workbook.getSheet(0);Once you have a sheet, you can then start accessing the cells. You can retrieve the cell's contents as a string by using the convenience method getContents(). In the example code below, A1 is a text cell, B2 is numerical value and C2 is a date. The contents of these cells may be accessed as follows
Cell a1 = sheet.getCell(0,0);The demo programs CSV.java and XML.java use the convenience method getContents() in order to output the spreadsheet contents.
Cell b2 = sheet.getCell(1,1);
Cell c2 = sheet.getCell(2,1);
String stringa1 = a1.getContents();
String stringb2 = b2.getContents();
String stringc2 = c2.getContents();
// Do stuff with the strings etc
...
However if it is required to access the cell's contents as the exact type ie. as a numerical value or as a date, then the retrieved Cell must be cast to the correct type and the appropriate methods called. The section of code below illustrates how JExcelApi may be used to retrieve a genuine java double and java.util.Date object from an Excel spreadsheet. For completeness the label is also cast to it's correct type, although in practice this makes no difference. The example also illustrates how to verify that cell is of the expected type - this can be useful when validating that the spreadsheet has cells in the correct place.
String stringa1 = null;When you have finished processing all the cells, use the close() method. This frees up any allocated memory used when reading spreadsheets and is particularly important when reading large spreadsheets.
double numberb2 = 0;
Date datec2 = null;
Cell a1 = sheet.getCell(0,0);
Cell b2 = sheet.getCell(1,1);
Cell c2 = sheet.getCell(2,1);
if (a1.getType() == CellType.LABEL)
{
LabelCell lc = (LabelCell) a1;
stringa1 = lc.getString();
}
if (b2.getType() == CellType.NUMBER)
{
NumberCell nc = (NumberCell) b2;
numberb2 = nc.getValue();
}
if (c2.getType() == CellType.DATE)
{
DateCell dc = (DateCell) c2;
datec2 = dc.getDate();
}
// Do stuff with dates and doubles
...
// Finished - close the workbook and free up memory
workbook.close();
