Fully explore Java Excel API usage (1)

Windows OS users are familiar with Excel (workbooks), but it is not easy to use Java to manipulate Excel files. As Web applications are becoming increasingly popular today, the demand for using Web to Operate Excel files is getting stronger and stronger. Currently, a popular operation is to create a CSV (comma separated values) file in JSP or Servlet, the file is returned to the browser as MIME, text/csv, and then the browser calls Excel and displays the CSV file. This is just to say that you can access Excel files, but you cannot really manipulate Excel files. This article will surprise you and introduce you to an open source project, Java Excel API, you can use it to easily manipulate Excel files.

　　Java Excel API Overview

Java Excel is an open source project. Java developers can read the content of an Excel file, create a new Excel file, and update an existing Excel file. Using this API, non-Windows operating systems can also use Java-only applications to process Excel Data Tables. Because it is written in Java, we can use JSP and Servlet in Web applications to call APIs to access Excel Data Tables.

The current stable version is V2.0, which provides the following functions:

Reads data from files in Excel formats such as 95, 97, and 2000;
Read the Excel Formula (the formulas after Excel 97 can be read );
Generate an Excel data table in the format of Excel 97 );
Supports formatting fonts, numbers, and dates;
Supports cell shadow and color operations;
Modify an existing data table;

The following features are not yet supported, but will be provided soon:

Cannot read chart information;
Can be read, but cannot generate formulas. The final calculated values of any type formulas can be read;
　
　　 Application Example

1. Read data tables from Excel files

Java Excel apican read an Excel data table from a file (.xls) of the internal file system or from an input stream. The first step to read an Excel data table is to create a workbook (term: Work thin). The following code snippet illustrates how to perform this operation: (for the complete code, see excelreading. Java)

Import java. io .*; Import jxl .*; ... ... ... ... Try { // Construct a Workbook object, read-only Workbook object // Create a Workbook directly from a local file // Create a Workbook from the input stream InputStream is = new FileInputStream (sourcefile ); Jxl. Workbook rwb = Workbook. getWorkbook (is ); } Catch (Exception e) { E. printStackTrace (); }

Once a workbook is created, we can use it to access the Excel sheet (term: Worksheet ). Refer to the following code snippet:

// Obtain the first Sheet Sheet rs = rwb. getSheet (0 );

We can access it either by the sheet name or by subscript. If a subscript is used for access, note that the subscript starts from 0, just like an array.

Once the sheet is obtained, we can use it to access the Excel cell (term: Cell ). Refer to the following code snippet:

// Obtain the value of the first row and column Cell c00 = rs. getCell (0, 0 ); String strc00 = c00.getContents (); // Obtain the value of the first and second columns Cell c10 = rs. getCell (1, 0 ); String strc10 = c10.getContents (); // Obtain the value of the second row and 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 we only get the cell value, we can easily use the getcontents () method, which can return any type of cell value as a string. In the sample code, cell (0, 0) is text type, cell (1, 0) is numeric type, cell () is date type, through getcontents (), the return values of the three types are 'struct' type.

If you need to know the exact type of cell content, APIS also provide a series of methods. Refer to the following code snippet:

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)" + "value:" + strc11 + "; type:" + c11.getType ());

After obtaining the cell object, you can use the GetType () method to obtain the cell type. Then, it matches the basic type provided by the API and is forcibly converted to the corresponding type, finally, call the corresponding value method getxxx () to obtain the value of the determined type. The API provides the following basic types, which correspond to the Excel data format, as shown in:

For the specific meaning of each type, see Java Excel API document.

After processing the data in an Excel worksheet, you must use the close () method to close the previously created object to release the memory space occupied by reading the data table, it is particularly important to read a large amount of data. See the following code snippet:

// When the operation is complete, close the object and release the occupied memory space Rwb. close ();

Java Excel API provides many methods to access Excel Data Tables. Here I will briefly introduce several common methods. For other methods, see Java Excel API document in the appendix.