Friends using the Windows operating system must not be unfamiliar with Excel, but to use Java language to manipulate the Excel file is not an easy task. Today, the Web is increasingly popular, the demand for the Excel file is increasing through the web, and the current more popular operation is to create a CSV (Comma Separated Values) file in the JSP or Servlet, and will file this file with MIME, TEXT / CSV type returns to the browser, then calls Excel and displays the CSV file. This is just to say that you can access the Excel file, but you can't really manipulate the Excel file. This article will give you a surprise, introduce you to an open source project, Java Excel API, use it, you can easily manipulate the Excel file.
Introduction to Java Excel API
Java Excel is an open source project, which can read the contents of the Excel file through its Java developers, create a new Excel file, update the existing Excel file. Using this API non-Windows operating system can also process the Excel data table by pure Java applications. Because it is written in Java, we can call the API to implement access to the Excel data table in a web application.
The stable version released now is V2.0, providing the following features:
Read data from the Format of Excel 95, 97, 2000; read the Excel formula (the formula after Excel 97); generate the Excel data table (format Excel 97); support font, numbers, date format Support the shadow operation of the cell, and color operation; modify the existing data table;
The following features are now also supported, but will soon provide:
You cannot read chart information; you can read, but you can't generate formulas, any type of formula last calculated value can be read;
Application example
1 Read data sheet from Excel file
The Java Excel API can either a file (.xls) of the local file system or from the input stream. The first step in reading the Excel data is to create Workbook (Terminology: Working). The following code snippet is explained how to operate: (full code see Excelreading.java)
Import java.io. *;
Import jxl. *;
... ...
Try
{
// Build a Workbook object, read-only Workbook object
/ / Create Workbook directly from local files
// Create Workbook from the input stream
InputStream IS = New FileInputStream (Sourcefile);
JXL.Workbook RWB = Workbook.getWorkbook (IS);
}
Catch (Exception E)
{
E.PrintStackTrace ();
}
Once you have created Workbook, we can access the Excel Sheet by it (Terminology: Worksheet). Refer to the following code segment:
/ / Get the first Sheet table
Sheet RS = rwb.getsheet (0);
We can access it through the name of Sheet, or you can access it by subscript. If you are accessible by the subscript, you should pay attention to the starting mark from 0, just like an array.
Once you get the Sheet, we can access Excel Cell (Terms: Cell). Refer to the following code segment:
/ / Get the first line, the value of the first column Cell C00 = rs.getcell (0, 0);
String strc00 = c00.getContents ();
/ / Get the value of the first line, the second column
Cell C10 = rs.getcell (1, 0);
String strc10 = c10.getContents ();
/ / Get the value of the second line, the second column
Cell C11 = rs.getcell (1, 1);
String strc11 = c11.getContents ();
System.out.println ("Cell (0, 0)" "Value:" STRC00 "; TYPE:" C00.GetType ());
System.out.println ("Cell (1, 0)" "Value:" STRC10 "; TYPE:" C10.Gettype ());
System.out.println ("Cell (1, 1)" "Value:" STRC11 "; TYPE:" C11.GETTYPE ());
If it is just a value of Cell, we can easily return any type of CELL value as a string by getContents () method. Cell (0, 0) in the sample code is text type, Cell (1, 0) is a digital type, Cell (1, 1) is a date type, through getContents (), three types of return values are characters.
If you need to know the exact type of Cell content, the API also provides a range of methods. Refer to the following code segment:
String strc00 = NULL;
Double STRC10 = 0.00;
Date strc11 = NULL;
Cell C00 = rs.getcell (0, 0);
Cell C10 = rs.getcell (1, 0);
Cell C11 = rs.getcell (1, 1);
IF (c00.gettype () == CellType.label)
{
Labelcell labelc00 = (labelcell) C00;
STRC00 = labelc00.getstring ();
}
IF (c10.gettype () == CellType.Number)
{
NMBERCELL NUMC10 = (Numbercell) C10;
STRC10 = Numc10.getValue ();
}
IF (c11.gettype () == CellType.date)
{
Datecell Datec11 = (Datecell) C11;
STRC11 = DATEC11.Getdate ();
}
System.out.println ("Cell (0, 0)" "Value:" STRC00 "; TYPE:" C00.GetType ());
System.out.println ("Cell (1, 0)" "Value:" STRC10 "; TYPE:" C10.Gettype ());
System.out.Println ("Cell (1, 1)" "" STRC11 "; TYPE:" C11.GetType (); After getting the Cell object, the unit can be obtained by gettype () method. The type of grid, then match the basic type provided by the API, forcibly converting into a corresponding type, and finally call the corresponding value method getxxx (), you can get the value of the determined type. The API provides the following basic types, corresponding to the data format of Excel, as shown below:
For details of each type, see Java Excel API Document.
When you complete the processing of Excel spreadsheet data, you must use the close () method to close the previously created object to release the memory space occupied by the read data table, which is especially important when reading a large amount of data. . Refer to the following code segment:
// When the operation is complete, turn off the object, release the occupied memory space
Rwb.close ();
The Java Excel API provides a number of ways to access the Excel data sheet, here I only briefly introduce several common methods, other methods, please refer to Java Excel API Document in the appendix.
Methods provided by the Workbook class
1. Int getNumberOfsheets () Get the number of worksheets (Sheets) in Workbook, examples:
JXL.Workbook RWB = jxl.Workbook.getWorkbook (new file (sourcefile);
Int sheets = rwb.getnumberofsheets ();
2. Sheet [] getSheets () Returns an array of worksheets (Sheet) objects in Workbook, examples:
JXL.Workbook RWB = jxl.Workbook.getWorkbook (new file (sourcefile);
Sheet [] sheets = rwb.getsheets ();
3. String getVersion () Returns the version number of the API that is being used, it seems that there is nothing big.
JXL.Workbook RWB = jxl.Workbook.getWorkbook (new file (sourcefile);
String apiversion = rwb.getversion ();
Method provided by the Sheet interface
1) String getName () Get the name of the Sheet, example:
JXL.Workbook RWB = jxl.Workbook.getWorkbook (new file (sourcefile);
JXL.SHEET RS = rwb.getsheet (0);
String sheetname = rs.getname ();
2) INT getColumns () Gets the number of total columns contained in the Sheet table, examples:
JXL.Workbook RWB = jxl.Workbook.getWorkbook (new file (sourcefile);
JXL.SHEET RS = rwb.getsheet (0);
INT RSCOLUMNS = rs.getcolumns ();
3) Cell [] getColumn (int column) Gets all cells in a column, returns the cell object array, example:
JXL.Workbook RWB = jxl.Workbook.getWorkbook (new file (source (sourcefile)); jxl.sheet = rwb.getsheet (0);
Cell [] Cell = rs.getColumn (0);
4) INT getRows () Gets the number of total rows included in the Sheet table, examples:
JXL.Workbook RWB = jxl.Workbook.getWorkbook (new file (sourcefile);
JXL.SHEET RS = rwb.getsheet (0);
Int rsRows = rs.getrows ();
5) Cell [] getRow (int ROW) Gets all cells of a certain line, returns the array of cell objects, examples:
JXL.Workbook RWB = jxl.Workbook.getWorkbook (new file (sourcefile);
JXL.SHEET RS = rwb.getsheet (0);
Cell [] Cell = rs.getrow (0);
6) Cell getcell (int column, int in) Gets the object reference specified cell, you need to pay attention to its two parameters, the first is the number of columns, the second is the number of rows, this with the usual row, column Some of the combination is different.
JXL.Workbook RWB = jxl.Workbook.getWorkbook (new file (sourcefile);
JXL.SHEET RS = rwb.getsheet (0);
Cell Cell = rs.getcell (0, 0);
2 Generate new Excel work
The following code is mainly to introduce you how to generate a simple Excel worksheet, where the content of the cell is no modified (e.g., font, color, etc.), and all the content is written as a string. (Full code See Excelwriting.java)
Similar to reading the Excel worksheet, you should first create a Workbook object using the factory method of the Workbook class. It should be noted here that it can only create Workbook through the factory method provided by the API, and cannot be used Writableworkbook constructor constructor because the constructor of the class WritableWorkbook is the protected type. The sample code snippet is as follows:
Import java.io. *;
Import jxl. *;
Import jxl.write. *;
... ...
Try
{
// Build a Workbook object, read-only Workbook object
//Method 1: Create a writable Excel workbook
Jxl.write.writableworkbook wwb = workbook.createworkbook (new file (targetfile);
//Method 2: Writing WritableWorkbook directly to output flow
/ *
OutputStream OS = New FileoutputStream (TargetFile);
JXL.WRITE.WRITABLEWORK WWB = Workbook.createworkbook (OS);
* /
}
Catch (Exception E)
{
E.PrintStackTrace ();
}
The API provides two ways to process the writable output stream. One is to generate a local file. If the file name does not have a full path, the default file is positioned in the current directory, if the file name has a full path The generated Excel file is positioned in the corresponding directory; another is to write the Excel object directly to the output stream, for example: the user accesses the web server through the browser, if the HTTP header is set correctly, the browser is automatically called The client's Excel application displays the dynamically generated Excel spreadsheet. The next step is to create a worksheet, the method of creating a worksheet is almost the same as the method of creating a work, and the corresponding object is also obtained through the factory mode method. This method requires two parameters, one is the name of the worksheet, the other is The worksheet is in the position of the work, refer to the following code snippet:
// Create an Excel worksheet
JXL.WRITE.WRITABLESHEET WS = WWB.CREATESHEET ("Test Sheet 1", 0);
"This pot is also supported, the material is also ready, you can start the pot!" Refer to the following code segment:
// 1. Add label object
JXL.WRITE.Label labelc = new jxl.write.label (0, 0, "this is a label cell");
WS.Addcell (labelc);
// Add object with a font formatting
Jxl.write.writablefont wf = new jxl.write.writablefont (WritableFont.Times, 18, Writablefont.bold, true);
Jxl.write.writablecellformat wcff = new jxl.write.writablecellformat (wf);
JXL.WRITE.Label labelcf = new jxl.write.label (1, 0, "this is a label cell", WCFF);
WS.Addcell (labelcf);
// Add object with font color formatting
JXL.WRITE.WRITABLEFONT WFC = New jxl.write.writablefont (WritableFont.Arial, 10, WritableFont.no_Bold, False,
Underlinestyle.no_underline, jxl.format.colour.red;
JXL.WRITE.WRITABECECELLFORMAT WCFFC = New JXL.WRITE.WRITABECECELLFORMAT (WFC);
JXL.WRITE.Label labelcfc = new jxl.write.label (1, 0, "this is a label cell", WCFFC);
WS.Addcell (labelcf);
// 2. Add Number object
Jxl.write.number labeln = new jxl.write.Number (0, 1, 3.1415926);
WS.Addcell (labeln);
// Add Number object with formatting
JXL.WRITE.NUMBERFORMAT NF = New jxl.write.Numberformat ("#. ##");
JXL.WRITE.WRITABECELLFORMAT WCFN = New JXL.WRITE.WRITABECECELLFORMAT (NF); jxl.write.number labelnf = new jxl.write.Number (1, 1, 3.1415926, wcfn);
WS.Addcell (labelnf);
// 3. Add Boolean object
Jxl.write.Boolean labelb = new jxl.write.Boolean (0, 2, false);
WS.Addcell (labelb);
// 4. Add a DateTime object
Jxl.write.datetime labeldt = new jxl.write.datetime (0, 3, new java.util.date ());
WS.Addcell (labeldt);
// Add a DateFormat object with formatting
JXL.WRITE.DATEFORMAT DF = New JXL.WRITE.DATEFORMAT ("DD MM YYYY HH: MM: SS");
JXL.WRITE.WRITABECECELLFORMAT WCFDF = New JXL.WRITE.WRITABECELLFORMAT (DF);
Jxl.write.datetime labeldtf = new jxl.write.datetime (1, 3, new java.util.date (), wcfdf);
Ws.addcell (labeldtf);
There are two points here to pay attention to everyone. The first point, when constructing the cell, the location of the cell in the worksheet has been determined. Once created, the location of the cell cannot be changed, although the content of the cell can be changed. In the second point, the positioning of the cell is the following regularity (COLUMN, ROW), and the subscript starts from 0, for example, A1 is stored in (0, 0), B1 is stored (1, 0) .
Finally, don't forget to close the open Excel workmail object to release the occupied memory, see the following code snippet:
// Write an EXEL worksheet
Wwb.write ();
// Close Excel work thin object
WWB.CLOSE ();
This may have little different from the operation of reading the Excel file. Before shutting down the Excel object, you must call the Write () method first, because the previous operation is stored in the cache, so the content to operate through this method Save in the file. If you turn off the Excel object first, you can only get an empty work.
3 copies, update Excel work
Next, I will introduce how to update an existing work, mainly the following two steps, the first step is to construct a read-only Excel work, the second step is to create new writable with the Excel workstorm that has been created. Excel works thin, refer to the following code snippet: (complete code see ExcelModifying.java)
// Create a read-only Excel work thin object
JXL.Workbook RW = jxl.workbook.getWorkbook (New file (sourcefile);
// Create a writable Excel work thin object
JXL.WRITE.WRITABLEWORKBOOK WWB = Workbook.createworkbook (New File (Targetfile), RW);
// Read the first work list
JXL.WRITE.WRITABLESHEET WS = WWB.GETSHEET (0);
// Get the first cell object
JXL.WRITE.WRITABECECELL WC = Ws.GetwritableCell (0, 0);
/ / Judgment the type of cell, make corresponding transformation IF (wc.gettype () == CellType.label)
{
Label L = (label) WC;
L.Setstring ("The Value Has Been Modified.");
}
// Write an Excel object
Wwb.write ();
// Close the writable Excel object
WWB.CLOSE ();
// Close the read-only Excel object
Rw.close ();
The reason why to build an Excel object is completely due to efficiency because the above example is the main application of the API. In order to improve performance, when reading a worksheet, some of the output information related to the data, all format information, such as: font, color, etc. are not processed, because our purpose is to get the value of row data, both There is no modification, nor does it affect the value of the value. The only unfavorable thing is that in memory will save both the same worksheet, so when the worksheet is large, it will occupy a considerable amount of memory, but now the size of memory is not a key factor.
Once you have a writable worksheet object, we can update the cell object, where we do not have to call the ADD () method provided by the API, because the cell has been in the worksheet, so we only need To call the corresponding setxxxx () method, you can complete the update operation.
Using the original formatted modification of the cells cannot be removed, we can also add new units to make the content of the cells in different forms.
The newly generated worksheet object is writable. In addition to updating the original unit, you can add a new cell to a worksheet, which is exactly the same as the operation of Example 2.
Finally, don't forget to call the Write () method, write the updated content into the file, then turn off the workmaking object, here there are two working thin objects to be turned off, one is read-only, and the other is writable.
summary
This article is only a introduction of the methods commonly used in the Java Excel API. To learn more about the API more detailed, please refer to the API document, or source code. Java Excel API is an open source project. Please pay attention to its latest progress, interested friends can also apply for joining this project or propose valuable comments.
Reference
Java Excel API Document http://www.andykhan.com/jexcelapi/
