Fully explore Java Excel API usage

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 the Excel file, but you cannot really manipulate the Excel file. This article will surprise you and introduce you to an open source project, Java
Excel API, which allows you 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.

Workbook class provides methods

1. Int getnumberofsheets ()

Obtain the number of worksheets (sheet) in the workbook, for example:

Jxl. Workbook RWB = jxl. Workbook. getworkbook (new file (sourcefile )); Int sheets = RWB. getnumberofsheets ();

2. Sheet [] getsheets ()

Returns an array of Worksheet (sheet) objects in the workbook, for example:

Jxl. Workbook RWB = jxl. Workbook. getworkbook (new file (sourcefile )); Sheet [] sheets = RWB. getsheets ();

3. String getversion ()

Return the version number of the API in use.

Jxl. Workbook RWB = jxl. Workbook. getworkbook (new file (sourcefile )); String apiversion = RWB. getversion ();

Methods provided by the sheet Interface

1) string getname ()

Obtain the sheet name, for example:

Jxl. Workbook RWB = jxl. Workbook. getworkbook (new file (sourcefile )); Jxl. Sheet rs = RWB. getsheet (0 ); String sheetname = Rs. getname ();

2) int getcolumns ()

Obtain the total number of columns in a sheet. For example:

Jxl. Workbook RWB = jxl. Workbook. getworkbook (new file (sourcefile )); Jxl. Sheet rs = RWB. getsheet (0 ); Int rscolumns = Rs. getcolumns ();

3) cell [] getcolumn (INT column)

Obtains all cells in a column and returns an array of cell objects. For example:

Jxl. Workbook RWB = jxl. Workbook. getworkbook (new file (sourcefile )); Jxl. Sheet rs = RWB. getsheet (0 ); Cell [] Cell = Rs. getcolumn (0 );

4) int getrows ()

Obtain the total number of rows in a sheet. Example:

Jxl. Workbook RWB = jxl. Workbook. getworkbook (new file (sourcefile )); Jxl. Sheet rs = RWB. getsheet (0 ); Int rsrows = Rs. getrows ();

5) cell [] getrow (INT row)

Obtain all cells in a row and return an array of cell objects. Example:

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 row)

To obtain the object reference of a specified cell, note the two parameters. The first parameter is the number of columns and the second parameter is the number of rows, which is different from the common combination of rows and columns.

Jxl. Workbook RWB = jxl. Workbook. getworkbook (new file (sourcefile )); Jxl. Sheet rs = RWB. getsheet (0 ); Cell cell = Rs. getcell (0, 0 );

2. generate a new Excel worksheet

The following code is used to show you how to generate a simple Excel worksheet. Here, the content of a cell does not contain any modification (such as font, color, and so on ), all contents are written as strings. (For the complete code, see excelwriting. Java)

Similar to reading an Excel worksheet, you must first use the workbook class factory method to create a writeable workbook object. Note that, you can create a workbook only by using the factory method provided by the API, instead of using the writableworkbook constructor, because the constructor of the writableworkbook class is of the protected type. The sample code snippet is as follows:

Import java. Io .*; Import jxl .*; Import jxl. Write .*; ... ... ... ... Try { // Construct a workbook object, read-only workbook object // Method 1: Create an Excel worksheet that can be written Jxl. Write. writableworkbook WWB = Workbook. createworkbook (new file (targetfile )); // Method 2: Write writableworkbook directly to the output stream /* Outputstream OS = new fileoutputstream (targetfile ); Jxl. Write. writableworkbook WWB = Workbook. createworkbook (OS ); */ } Catch (exception E) { E. printstacktrace (); }

The API provides two methods to process writable output streams. One is to generate a local file directly. If the file name does not contain a full path, the default file is located in the current directory, if the file name contains a full path, the generated Excel file will be located in the corresponding directory; the other is to directly write the EXCEL object to the output stream, for example: the user accesses the web server through a browser. If the HTTP header is set correctly, the browser automatically calls the Excel application of the client to display the dynamically generated Excel Workbook.

The next step is to create a worksheet. The method for creating a worksheet is almost the same as that for creating a worksheet. The corresponding object is also obtained through the factory mode method. This method requires two parameters, one is the worksheet name, and the other is the position of the worksheet in the workbook. See the following code snippet:

// Create an Excel worksheet Jxl. Write. writablesheet Ws = WWB. createsheet ("test sheet 1", 0 ); "This pot has been supported, and the materials are ready. You can start the pot! ", Now you only need to instantiate the basic Excel data types provided by the API and add them to the worksheet. refer to the following code snippet: // 1. Add a label object Jxl. Write. Label labelc = new jxl. Write. Label (0, 0, "This is a label cell "); WS. addcell (labelc ); // Add an 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 an object with the 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. writablecellformat wcffc = new jxl. Write. writablecellformat (WFC ); Jxl. Write. Label labelcfc = new jxl. Write. Label (1, 0, "This is a label cell", wcffc ); WS. addcell (labelcf ); // 2. Add a number object Jxl. Write. Number labeln = new jxl. Write. Number (0, 1, 3.1415926 ); WS. addcell (labeln ); // Add a number object with formatting Jxl. Write. numberformat NF = new jxl. Write. numberformat ("#.##"); Jxl. Write. writablecellformat wcfn = new jxl. Write. writablecellformat (NF ); Jxl. Write. Number labelnf = new jxl. Write. Number (1, 1, 3.1415926, wcfn ); WS. addcell (labelnf ); // 3. Add a 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. writablecellformat wcfdf = new jxl. Write. writablecellformat (DF ); Jxl. Write. datetime labeldtf = new jxl. Write. datetime (1, 3, new java. util. Date (), wcfdf ); WS. addcell (labeldtf );

There are two points worth your attention. First, when constructing a cell, the position of the cell in the worksheet is determined. After a cell is created, the position of the cell cannot be changed, although the content of the cell can be changed. Second, the cell is located according to the following rule (column, row), and the subscript starts from 0. For example, A1 is stored in (0, 0 ), b1 is stored in (1, 0 ).

Finally, do not forget to close the opened Excel worksheet object to release the occupied memory. See the following code snippet:

// Write the Exel Worksheet WWB. Write (); // Close the Excel worksheet object WWB. Close ();

This may be slightly different from reading an Excel file. Before closing an Excel object, you must call the write () method first, because the previous operations are stored in the cache, therefore, you need to save the operation content in the file. If you close the EXCEL object, you can only get an empty workbook.

3. Copy and update the Excel worksheet

Next, we will briefly introduce how to update an existing worksheet, mainly in the following two steps. The first step is to construct a read-only Excel worksheet, the second step is to create a new writable Excel worksheet using the created Excel worksheet. refer to the following code snippet: (for the complete code, see excelmodifying. java)

// Create a read-only Excel worksheet object Jxl. Workbook RW = jxl. Workbook. getworkbook (new file (sourcefile )); // Create a writable Excel worksheet object Jxl. Write. writableworkbook WWB = Workbook. createworkbook (new file (targetfile), RW ); // Read the first Worksheet Jxl. Write. writablesheet Ws = WWB. getsheet (0 ); // Obtain the first cell object Jxl. Write. writablecell WC = ws. getwritablecell (0, 0 ); // Determine the cell type and convert it accordingly If (WC. GetType () = celltype. Label) { Label L = (Label) WC; L. setstring ("the value has been modified ."); } // Write an Excel Object WWB. Write (); // Close the Excel objects that can be written WWB. Close (); // Close the read-only Excel Object RW. Close ();

The reason for using this method to build an Excel object is efficiency, because the above example is the main application of the API. To improve performance, some data-related output information and all format information, such as fonts and colors, are not processed when reading a worksheet, because our goal is to obtain the value of the row data, without modification, it will not affect the value of the row data. The only disadvantage is that two identical worksheets will be saved in the memory at the same time, so that when the worksheet size is large, it will occupy a considerable amount of memory, but now it seems that the memory size is not a key factor.

Once a writable worksheet object is obtained, we can update the cell object. Here we do not have to call the add () method provided by the API, because the cell is already in the worksheet, we only need to call the corresponding setxxx () method to complete the update operation.

The original cell Formatting cannot be removed. We can still add the new cell modifier so that the cell content can be displayed in different forms.

Newly generated worksheet objects can be written. In addition to updating the original unit, we can also add new cells to the worksheet, which is exactly the same as the operation in example 2.

Finally, do not forget to call the write () method, write the updated content to the file, and then close the thin object. Here there are two thin objects to be closed, one is read-only, the other is writable.