jxl 教程
@author YHC
Java Excel API Tutorial
Sheet sheet = workbook.getSheet(0);当你有一个工作簿时,你然后可以开始访问单元格.你可以检索单元格内容,当内容是String类型时你可以使用简便的放法getContents() . 在下面示例代码, A1是一个text类型单元格, B2是一个数值类型和 C2 是一个date类型的单元格. 这些单元格的内容可以如下方式访问
Cell a1 = sheet.getCell(0,0);在demo示例程序中CSV.java 和 XML.java 使用简便方法 getContents() 方便输出Excel内容.
Cell b2 = sheet.getCell(1,1);
Cell c2 = sheet.getCell(2,1);
String stringa1 = a1.getContents();
String stringb2 = b2.getContents();
String stringc2 = c2.getContents();
// 针对字符串的操作....
...
String stringa1 = null;当你完成处理所有单元格时,使用close()方法.释放任何分配使用内存 ,当读取Excel时特别重要,尤其是读取较大Excel
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();
}
// 对日期数据和double类型数据操作 ....
...
// 最后 - 关闭workbook和释放内存
workbook.close();
WritableSheet sheet = workbook.createSheet("First Sheet", 0);现在剩余的就是添加单元格到工作表中了. 这个简单的示例添加单元格到sheet.以下代码片段放置一个label到单元格 A3, 和数字 3.14159 到单元格 D5.
Label label = new Label(0, 2, "A label record");
sheet.addCell(label);
Number number = new Number(3, 4, 3.1459);
sheet.addCell(number);
这里有许多地方需要注意. 首先, 在sheet中的单元格位置需要指定作为构造函数的一部分. 一旦创建, 不能改变单元格的位置, 虽然单元格的内容可以更改.
...
//所有sheet和cell添加后.现在写出workbook
workbook.write();
workbook.close();
Label label3 = new Label(2, 0, "Another Arial 10 point label", arial10format);这里创建另一个label,使用相同的格式在单元格C1中.
sheet.addCell(label3);
//创建一个单元格 字体为TIMES 字号是16 加粗由于时间原因,之后更新......
WritableFont times16font = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, true);
WritableCellFormat times16format = new WritableCellFormat (times16font);
// 创建 label, 指定内容和格式 specifying content and format
Label label4 = new Label(3,0, "Times 16 bold italic label", times16format);
sheet.addCell(label4);
NumberFormat fivedps = new NumberFormat("#.#####");It is, of course, also possible to specify font information as well eg. to display the same value in the 16 point times bold font defined earlier we can write
WritableCellFormat fivedpsFormat = new WritableCellFormat(fivedps);
Number number4 = new Number(2, 4, 3.141519, fivedpsFormat);
sheet.addCell(number4);
WritableCellFormat fivedpsFontFormat = new WritableCellFormat (times16font, fivedps);
Number number5 = new Number(3, 4, 3.141519, fivedpsFontFormat);
sheet.addCell(number5);
WritableSheet sheet2 = copy.getSheet(1);There is no need to call the add() method on the sheet, since the cell is already present on the sheet. The contents of numerical and date cells may be modified in a similar way, by using the setValue() and setDate() methods respectively.
WritableCell cell = sheet2.getWritableCell(1, 2);
if (cell.getType() == CellType.LABEL)
{
Label l = (Label) cell;
l.setString("modified cell");
}
WritableSheet sheet2 = copy.getSheet(1);Since the copy of the workbook is an ordinary writable workbook, new cells may be added to the sheet, thus:
WritableCell cell = sheet2.getWritableCell(2, 4);
NumberFormat fivedps = new NumberFormat("#.#####");
WritableCellFormat cellFormat = new WritableCellFormat(fivedps);
cell.setFormat(cellFormat);
Label label = new Label(0, 2, "New label record");As before, once the modifications are complete, the workbook must be written out and closed.
sheet2.addCell(label);
Number number = new Number(3, 4, 3.1459);
sheet2.addCell(number);
...The demo program contained in the source file ReadWrite.java may be studied as a more exhaustive example of how spreadsheets may be modified. This demo program copies the spreadsheet passed in on the command line; if the spreadsheet to be copied is the example spreadsheet, jxlrwtest.xls, located in the current directory, then certain modifications are carried out. DO NOT MODIFY THE EXAMPLE SPREADSHEET, otherwise the demo program will not work.
// All cells modified/added. Now write out the workbook
copy.write();
copy.close();
ant jxlallAs an alternative to using ant, JExcelApi may be built using the standard java tools. From the command line in the build subdirectory issue the following sequence of commands (modifying file separators and classpath separators as required for the target operating system):
javac -d out -classpath out:../src ../src/jxl/demo/*.java
jar cmf jxl.mf ../jxl.jar -C out common jxl
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException
{
try
{
ServletInputStream is = request.getInputStream();
byte[] junk = new byte[1024];
int bytesRead = 0;
// the first four lines are request junk
bytesRead = is.readLine(junk, 0, junk.length);
bytesRead = is.readLine(junk, 0, junk.length);
bytesRead = is.readLine(junk, 0, junk.length);
bytesRead = is.readLine(junk, 0, junk.length);
Workbook workbook = Workbook.getWorkbook(is);
// Do stuff with the workbook
...
}
catch (JXLException e)
{
...
}
}
Support for charts, macros and images
JExcelApi has limited support for charts: when copying a spreadsheet containing a chart, the chart is written out to the generated spreadsheet (as long as the sheet containing the chart contains other data as well as the chart).
All macro and information is ignored. Consequently when copying and writing out the macros that were in the original will not be present in the generated version.
All image information is preserved when copying spreadsheets. When adding an image to a spreadsheet only images in PNG format are supported
Date display
When displaying dates, the java.util package automatically adjusts for the local timezone. This can cause problems when displaying dates within an application, as the dates look as if they are exactly one day previous to that which is stored in the Excel spreadsheet, although this is not in fact the case.
Excel stores dates as a numerical value, and the conversion process for transforming this into a java.util.Date consists of converting the Excel number into a UTC value and then using the UTC number to create the java Date. Say the number in Excel represents 20 August 2003, then the UTC equivalent of this number is used to create a java.util.Date object.
The problem occurs if you are operating in a timezone other than GMT. As soon as you try and perform any user IO on that java Date object (eg. System.out.print(date)) the JVM will perform timezone adjustment calculations. If you are located in EST zone (which is GMT - 5 hours) java will subtract 5 hours from the date - so the Date object instead of being 00:00 20/08/2003 becomes 19:00 19/08/2003. Because java recognizes you only want a date and not a date time, it truncates the hours/minutes/seconds and presents 19/08/2003 - so it appears that the day is one day less than was stored in Excel, whereas it is really only a few hours (the timezone offset) less. Needless to say, this is a very annoying feature.
The easiest way to work around this (and the method used internally by the getContents() method of a jxl.DateCell) is to force the timezone of the date format as follows:
TimeZone gmtZone = TimeZone.getTimeZone("GMT");
SimpleDateFormat format = new SimpleDateFormat("dd MMM yyyy");
format.setTimeZone(gmtZone);
DateCell dateCell = ....
String dateString = format.format(dateCell.getDate());
Cell Formats Across Multiple Workbooks
Sometimes a single process may generate multiple workbooks. When doing this is it tempting to create the various cell formats once (eg. as member data or as static constants) and apply them to cells in both workbooks. This works fine for the first workbook, but for subsequent workbooks this can cause unexpected cell formatting. The reason for this is that when a format is first added to a workbook, JExcelApi assigns an internal cross-reference number to that cell, and all other cells which share this format simply store the cross reference number. However, when you add the same cell format to a different workbook, JExcelApi recognizes that the format has been added to a workbook, and simply refers to the format by the index number, rather than by initializing it properly. When Excel then tries to read this workbook, it sees an index number, but is unable to read the cell format (or reads a different one) as the cell can be formatted in an unpredictable manner.
The long and the short of it is that if it is necessary to re-use formats across multiple workbooks, then the WritableCellFormat objects must be re-created and initialised along with the each Workbook instance, and NOT re-used from a previous workbook.
Cross Sheet Formulas
JExcelApi supports formulas across sheets. However, please make sure all the sheets have been added to the workbook (even if they are blank) before attempting to create cross sheet formulas. This is because if you create a cross sheet formula referencing a sheet and then subsequently and or remove sheets from the workbook, the sheet reference in the formula when it was parsed won't necessarily reference the correct sheet, and could even cause Excel to crash
