Fully explore Java Excel API usage (2)

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.