Methods offered by Workbook class 1. INT getNumberOfsheets () Gets the number of worksheets (Sheets) in Workbook, examples:
JXL.Workbook RWB = jxl.workbook.getworkbook (new file (source (sourcefile)); int shop = 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 in use, 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 Sheet, example:
JXL.Workbook RWB = jxl.workbook.getworkbook (new file (source (sourcefile)); jxl.sheet = rwb.getsheet (0); string sheetname = rs.getName ();
2) INT getColumns ()
Get the total number of columns included in the Sheet table, examples:
JXL.Workbook RWB = jxl.workbook.getworkbook (new file (sourcefile); jxl.sheet = rwb.getsheet (0); int gcolumns = rs.getColumns ();
3) Cell [] getColumn (int column)
Get all the cells of a column, returns a cell object array, example:
JXL.Workbook RWB = jxl.Workbook.getWorkbook (new file (sourcefile); jxl.sheet = rwb.getsheet (0); cell [] cell = rs.getColumn (0);
4) INT getRows ()
Get the total number of lines contained in the Sheet table, examples:
JXL.Workbook RWB = jxl.workbook.getworkbook (new file (sourcefile)); jxl.sheet = rwb.getsheet (0); int gROWS = rs.getrows ();
5) Cell [] GetRow (int Row)
Get all the cells of a certain line, returning the cell object array, an example:
JXL.Workbook RWB = jxl.Workbook.getWorkbook (new file (sourcefile)); jxl.sheet = rwb.getsheet (0); cell [] cell = rs.getrow (0);
6) Cell getcell (int column, int in)
Get the object reference to the 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, which is different from the usual row, the column combination is somewhat different. JXL.Workbook RWB = jxl.Workbook.getWorkbook (new file (sourcefile)); jxl.sheet = 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: WritableWorkbook written 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 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!" It is only the EXCEL basic data type provided by the API, and it is possible to add them to the worksheet. 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 a word formatting object jxl.write.writablefont wf = new jxl.write.writableFont (WritableFont.Times , 18, WritableFont.Bold, True; JXL.WRITE.WRITABECECELLFORMAT WCFF = New JXL.WRITE.WRITABECELLFORMAT (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.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.1415 926); 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, 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.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 to cause everyone to pay attention. First point, when constructing cells, the location of cells in the worksheet is already determined. Once After the creation, the location of the cell is unable to change, although the content of the cell can be changed. The second point, the positioning of the cell is the rulern, row, and the subscript is from 0 Start, 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 workmail 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, copy, update Excel work thin
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 workmail object Jxl.write.writableworkbook wwb = workbook.createworkbook (New file (targetfile), RW); // reads the first workpiece jxl.write.writablesheet WS = wwb.getsheet (0); // Get the first cell object jxl.write.writableCell WC = WS .Getwritablecell (0, 0); // Judgment the type of cell, make corresponding conversion IF (wc.gettype () == cellType.label) {Label L = (label) wc; l.setstring ("The value" ");} // 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.
