Introduction to the Java Excel API

1. Reading data table from Excel file

The Java Excel API can either be from a file (. xls) on the local file system, or you can read the Excel data table from the input stream. The first step in reading an Excel data table is to create a workbook (term: Workbook), and the following code snippet illustrates how it should be done: (see Excelreading.java for the full code)

Import java.io.*;import jxl.* ... try{//build Workbook object, read-only Workbook object//create workbook//directly from the local file and create Workbookinputstream from the input stream is = new FileInputStream (sourcefile); Jxl. Workbook RWB = Workbook.getworkbook (is);} catch (Exception e) {e.printstacktrace ();}

Once the workbook is created, we can access the Excel Sheet (term: worksheet) through it. Refer to the following code snippet:

Get the first sheet table Sheet rs = rwb.getsheet (0);

We can access it either through the name of the sheet, or by subscript. If accessed by subscript, it is important to note that the subscript starts at 0, just like an array.

Once we get the sheet, we can access the Excel cell (the term: cell) through it. Refer to the following code snippet:

Gets the first row, the value of the first column, cell c00 = Rs.getcell (0, 0); String strc00 = c00.getcontents ();//Gets the first row, the value of the second column, cell C10 = Rs.getcell (1, 0); String STRC10 = c10.getcontents ();//Gets the second row, the value of 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 you just get the cell value, we can easily pass the getcontents () method, which can return any type of cell value as a string. In the sample code, the cell (0, 0) is a literal, the cell (1, 0) is a numeric type, the cell is a date type, and by getcontents (), three types of return values are character types.

If there is a need to know the exact type of cell content, the API also provides 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.get String ();} 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 you get the cell object, you can get the type of the cell through the GetType () method, match it to the underlying type provided by the API, cast it to the appropriate type, and finally call the corresponding accessor method getxxx () to get the value of the type. The API provides the following basic types, which correspond to the data format of Excel, as shown in:

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

When you have finished working with Excel spreadsheet data, be sure to use the close () method to close the previously created object to free up memory space used in reading the data table, which is especially important when reading large amounts of data. Refer to the following code snippet:

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

The Java Excel API provides a number of ways to access Excel data tables, where I only briefly describe a few common methods, and other methods refer to the Java Excel API document in the appendix.

Methods provided by the workbook class

1. int Getnumberofsheets ()

Get the number of worksheets (Sheet) in the workbook (Workbook), example:

Jxl. Workbook RWB = Jxl. Workbook.getworkbook (New File (sourcefile)); int sheets = Rwb.getnumberofsheets ();

2. sheet[] Getsheets ()

Returns an array of sheet (Sheet) objects in the workbook (Workbook), example:

Jxl. Workbook RWB = Jxl. Workbook.getworkbook (New File (sourcefile)); sheet[] sheets = rwb.getsheets ();

3. String getversion ()

Returns the version number of the API you are using, as if it is not much of a function.

Jxl. Workbook RWB = Jxl. Workbook.getworkbook (New File (sourcefile)); String apiversion = Rwb.getversion ();

Methods 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 ()

Get the total number of columns contained in the sheet table, 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)

Gets all the cells of a column, and returns an array of cell objects, example:

Jxl. Workbook RWB = Jxl. Workbook.getworkbook (New File (sourcefile)); Jxl. Sheet rs = rwb.getsheet (0); cell[] cell = rs.getcolumn (0);

4) int getRows ()

Get the total number of rows contained in the sheet table, 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)

Gets all the cells of a row and returns an array of cell objects, as shown in the 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)

Gets the object reference for the specified cell, noting that it has two parameters, the first is the number of columns, and the second is the number of rows, which is somewhat different from the usual 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. Create a new Excel workbook

The following code is mainly to show you how to generate a simple Excel worksheet, where the contents of the cell is not decorated with any (such as: font, color, etc.), all the content is written as a string. (see Excelwriting.java for full code)

Like reading an Excel worksheet, you first use the factory method of the Workbook class to create a writable workbook (Workbook) object, and it is important to note that the Workbook can only be created through the factory method provided by the API. Instead of using the Writableworkbook constructor, the constructor for class Writableworkbook is the protected type. The sample code snippet is as follows:

Import Java.io.*;import jxl.*;import jxl.write.*, ... try{//build Workbook object, read-only Workbook object//method 1: Create a writable Excel workbook 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 ways to process a writable output stream, one that generates a local file directly, and if the filename does not have a full path, the default file is positioned in the current directory, and if the file name has a full path, the resulting Excel file is located in the appropriate directory The other is to write the Excel object directly to the output stream, for example: the user accesses the Web server through a browser, and if the HTTP header is set correctly, the browser automatically calls the client's Excel application to display the dynamically generated Excel spreadsheet.

The next step is to create the worksheet, and the method to create the worksheet is almost the same as the method for creating the workbook, which requires two parameters, one for the sheet name and the other for the worksheet in the workbook, as well as the following code snippet:

Create an Excel worksheet Jxl.write.WritableSheet ws = Wwb.createsheet ("Test Sheet 1", 0); " The pot is also well-prepared, the materials are ready, you can start to hack! "All you have to do now is instantiate the Excel basic data types provided by the API and add them to the worksheet, and refer to the following code snippet://1. Adding 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, 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.add Cell (LABELCF);//Add an 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.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 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.WritableCellFormat WCFN = new Jxl.write.WritableCellFormat (NF); Jxl.write.Number labelnf = new Jxl.write.Number (1, 1, 3.1415926, WCFN); Ws.addcell (LABELNF);//3. Adding a Boolean object Jxl.write.Boolean Labelb = new Jxl.write.Boolean (0, 2, false); Ws.addcell (Labelb);//4. Adding a DateTime object jxl.write.DateTime Labeldt = new Jxl.write.DateTime (0, 3, New Java.util.Date ()); Ws.addcell (Labeldt);// Add 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 for everyone to draw attention to. 1th, when you construct a cell, the position of the cell in the worksheet is determined. Once created, the cell's position cannot be changed, although the contents of the cell can be changed. 2nd, the cell is positioned according to the following rules (column, row), and the subscript is starting from 0, for example, A1 is stored in (0, 0), B1 is stored in (1, 0).

Finally, don't forget to close the open Excel workbook object to free up memory, see the following code snippet:

Write Exel sheet wwb.write ();//Close Excel Workbook Object Wwb.close ();

This might be a little different from reading an Excel file, and before you close the Excel object, you must first call the Write () method, because the previous operation is stored in the cache, so you can save the contents of the operation in a file by this method. If you close the Excel object first, you can only get an empty workbook.

3. Copy and update Excel workbook

The next step is to briefly describe how to update an existing workbook, mainly the following two steps, the first step is to construct a read-only Excel workbook, and the second is to create a new writable Excel workbook with the Excel workbook that you have created, referring to the following code snippet: ( Complete code See EXCELMODIFYING.JAVA)

//Create a read-only Excel workbook object Jxl.workbook rw = jxl. Workbook.getworkbook (New File (sourcefile));//Create a writable Excel Workbook object Jxl.write.WritableWorkbook WWB = Workbook.createworkbook (New File (targetfile), rw);//Read first sheet Jxl.write.WritableSheet ws = Wwb.getsheet (0);// Get the first Cell object Jxl.write.WritableCell WC = Ws.getwritablecell (0, 0);//Determine the type of cell, make the appropriate conversion if (wc.gettype () = = Celltype.label) { Label L = (label) wc;l.setstring ("The value has been modified."); Write to Excel object Wwb.write ();//close writable Excel object Wwb.close ();//close read-only Excel object Rw.close (); 

The reason why you build an Excel object this way is because of the efficiency, because the example above is the main application of the API. To improve performance, when reading a worksheet, some output information related to the data, all formatting information, such as font, color, etc., is not processed, because our goal is to get the value of the row data, so that there is no adornment, and does not affect the value of the row data. The only disadvantage is that two identical worksheets are saved in memory, so that when the worksheet is larger, it takes up quite a bit of memory, but now it seems that the size of the memory is not a key factor.

Once the writable sheet object has been obtained, we can update the cell object, where we do not have to invoke the Add () method provided by the API, because the cell is already in the worksheet, so we just need to call the corresponding setxxx () method to complete the update operation.

The original formatting of the cell is not removed, we can still add the new cell decoration, so that the contents of the cell in a different form of expression.

The newly generated sheet object is writable, and we can add new cells to the worksheet in addition to updating the original cells, which is exactly the same as Example 2.

Finally, don't forget to call the Write () method, write the updated content to the file, and then close the workbook, where two workbook objects are closed, one is read-only, and the other is writable.

Introduction to the Java Excel API