简介

本篇简介意在展示如何通过JExcel读和写Excel数据。本指南不是完全手册，并且有些没有介绍的功能，并不代表JExcel没有。本篇读物旨在鼓励和研究API文档和例子代码(特别是Write.java

和 ReadWrite.java)，并更深刻地了解JExcel的功能和局限。

读电子数据表格

JExcelApi能够从存储在本地文件系统的文件或某些数入流中读取Excel数据表格。首先第一步是从一个文件或输入流读取数据表格到一个Workbook，下面的代码展示了从本地文件系统的文件创建Workbook。

(注意：在创建Workbook对象前，从ServletInputStream创建电子表格时，必须先去除HTTP头信息。)

一旦你能访问workbook，你就能使用他访问各自的工作表(sheet)。这些工作表以索引0开始编号，一次为0,1,2等。(你也可以通过工作表名称访问工作表。)

一旦你有了sheet，你就可以访问每个单元格(cell)。你可以方便地通过方法 getContents()

将单元格的内容作为字符串访问。下面的代码，a1是文本型单元格，b2是数值型单元格，c3是日期型单元格。这些单元格的内容可以象下面这样访问。

演示程序 CSV.java and XML.java使用方法getContents()输出了电子表格的数据。

然而如果需要通过精确的数据类型访问单元格的内容，比如：数值型或日期类型，就需要调用相对应的方法获得正确数据类型的数据。下面的代码片断展示了使用JExcel

API从Excel表格获取double和java.util.Date类型的数据。

当访问完所有的单元格后，需要使用 close() 方法。该方法将释放读取电子表格和导入的包所使用的内存。

写电子表格

基本原理

本节描述怎样输出简单的没有任何格式的电子表格。和读一个电子表个类似，首先第一步需要通过Workbook类的工厂方法创建一个可写的workbook。

This creates the workbook object. The generated file will be
located in the current working directory and will be called
"output.xls". The API can also be used to send the workbook
directly to an output stream eg. from a web server to the user's
browser. If the HTTP header is set correctly, then this will launch
Excel and display the generated spreadsheet.
The next stage is to create sheets for the workbook. Again, this is
a factory method, which takes the name of the sheet and the
position it will occupy in the workbook. The code fragment below
creates a sheet called "First Sheet" at the first position.
Now all that remains is to add the cells into the worksheet.
This is simply a matter of instantiating cell objects and adding
them to the sheet. The following code fragment puts a label in cell
A3, and the number 3.14159 in cell D5.
There are a couple of points to note here. Firstly, the cell's
location in the sheet is specified as part of the constructor
information. Once created, it is not possible to change a cell's
location, although the cell's contents may be altered.
The other point to note is that the cell's location is specified as
(column, row). Both are zero indexed integer values - A1 being
represented by (0,0), B1 by (1,0), A2 by (0,1) and so on.
Once you have finished adding sheets and cells to the workbook, you
call write() on the workbook, and then close the file. This final
step generates the output file (output.xls in this case) which may
be read by Excel. If you call close() without calling write()
first, a completely empty file will be generated.

添加格式信息

The previous section illustrates the fundamentals of generating
an Excel compatible spreadsheet using the JExcelApi. However, as it
stands Excel will render the data in the default font, and will
display the numbers to 3 decimal places. In order to supply
formatting information to Excel, we must make use of the overloaded
constructor, which takes an additional object containing the cell's
formatting information (both the font and the style).
The code fragment below illustrates creating a label cell for an
arial 10 point font.
Cell formats objects are shared, so many cells may use the same
format object, eg.
This creates another label, with the same format, in cell
C1.
Because cell formats are shared, it is not possible to change the
contents of a cell format object. (If this were permitted, then
changing the contents of the object could have unforeseen
repurcussions on the look of the rest of the workbook). In order to
change the way a particular cell is displayed, the API does allow
you to assign a new format to an individual cell.
The constructors for the WritableFont object have many overloads.
By way of example, the code fragment below creates a label in 16
point Times, bold italic and assigns it to position D1.

格式格数值型数据

Number formatting information may be passed to the cell format
object by a similar mechanism to that described for fonts.
A variety of predefined number formats are defined statically.
These may be used to format numerical values as follows:
The above code inserts the value 3.14159 into cells A5 and B5,
using the preset integer and floating points format respectively.
When Excel renders these cells, A5 will display as "3" and B5 will
display as "3.14", even though both cells contain the same floating
point value.
It's possible for a user to define their own number formats, by
passing in a number format string. The string passed in should be
in the same format as that used by the java.text.DecimalFormat
class. To format a number to display up to five decimal places in
cell C5, the following code fragment may be used:
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

格式化日期型数据

Dates are handled similarly to numbers, taking in a format
compatible with that used by the java.text.SimpleDateFormat class.
In addition, several predefined date formats are specified in the
jxl.write.DateFormat class.
As a brief example, the below code fragment illustrates placing the
current date and time in cell A7 using a custom format:
As with numbers, font information may be used to display the
date text by using the overloaded constructors on
WritableCellFormat.
For a more extensive example of writing spreadsheets, the
demonstration program Write.java should be studied. In addition to
the functionality described above, this program tests out a variety
of cell, formatting and font options, as well as displaying cells
with different background and foreground colours, shading and
boundaries.

复制和修改电子表格

This section describes the scenario where a spreadsheet is read
in, it's contents altered in some way and the modified spreadsheet
written out.
The first stage is to read in the spreadsheet in the normal
way:
This creates a readable spreadsheet. To obtain a writable
version of this spreadsheet, a copy must be made, as follows:
The API functions this way is for reasons of read efficiency
(since this is the primary use of the API). In order to improve
performance, data which relates to output information (eg. all the
formatting information such as fonts) is not interpreted when the
spreadsheet is read, since this is superfluous when interrogating
the raw data values. However, if we need to modify this spreadsheet
a handle to the various write interfaces is needed, which can be
obtained using the copy method above. This copies the information
that has already been read in as well as performing the additional
processing to interpret the fields that are necessary to for
writing spreadsheets. The disadvantage of this read-optimized
strategy is that we have two spreadsheets held in memory rather
than just one, thus doubling the memory requirements. For this
reason copying and modifying large spreadsheets can be expensive in
terms of processing and memory.
Once we have a writable interface to the workbook, we may retrieve
and modify cells. The following code fragment illustrates how to
modify the contents of a label cell located in cell B3 in sheet 2
of the workbook.
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.
Although cell formats are immutable, the contents of a cell may be
displayed differently by assigning a different format object to the
cell. The following code fragment illustrates changing the format
of numerical cell (in position C5) so that the contents will be
displayed to five decimal places.
Since the copy of the workbook is an ordinary writable workbook,
new cells may be added to the sheet, thus:
As before, once the modifications are complete, the workbook
must be written out and closed.
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.