Friends using the Windows operating system must not be unfamiliar with Excel, but to use Java language to manipulate the Excel file is not an easy task. Today, the Web is increasingly popular, the demand for the Excel file is increasing through the web, and the current more popular operation is to create a CSV (Comma Separated Values) file in the JSP or Servlet, and will file this file with MIME, TEXT / CSV type returns to the browser, then calls Excel and displays the CSV file. This is just to say that you can access the Excel file, but you can't really manipulate the Excel file. This article will give you a surprise, introduce you to an open source project ?? Java Excel API, use it, you can easily manipulate the Excel file. Java Excel API Introduction Java Excel is an open source project, through its Java developers, you can read the contents of the Excel file, create a new Excel file, update the existing Excel file. Using this API non-Windows operating system can also process the Excel data table by pure Java applications. Because it is written in Java, we can call the API to implement access to the Excel data table in a web application. The stable version of the current release is V2.0, providing the following features: • Read data from the files in formats such as Excel 95, 97, 2000; read the Excel formula (can read the formula after Excel 97); generate Excel Data table (format is Excel 97);? Support font, numbers, date formatting; support cell shadow operation, and color operation; modify the existing data table; now does not support the following functions, but shortly It will be provided: • You cannot read chart information; you can read, but you can't generate formulas, any type of formula last calculated value can be read; Application example reads data sheet from Excel file Java Excel API can be either from local files A file (.xls) of the system, or you can read the Excel data table from the input stream. The first step in reading the Excel data is to create Workbook (Terminology: Working). The following code snippet will illustrate how you should operate: (full code See Excelreading.java) Import java.io. *; import jxl. *; ... ... Try {// Building a Workbook object, read-only Workbook object // Create Workbook INPUTSTREAM IS = New FileInputStream (Sourcefile) from the input stream; JXL.Workbook RWB = Workbook.getWorkbook (IS );} catch (Exception E) {E.PrintStackTrace ();} Once you have created Workbook, we can access Excel Sheet by it. Refer to the following code snippet: // Get the first Sheet table sheet = rwb.getsheet (0); we may access it through the name of the Sheet, or you can access it through the subscript. If you are accessible by the subscript, you should pay attention to the starting mark from 0, just like an array. Once you get the Sheet, we can access Excel Cell (Terms: Cells) through it.
Refer to the following code snippet: // Get the first line, the value of the first column Cell C00 = gtcell (0); string strc00 = c00.getContents (); // Get the first line, second column Value Cell C10 = rs.getcell (1, 0); string strc10 = c10.getContents (); // Gets the second line, the second column value 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 it is only the value of Cell, we can easily return any type of CELL value as a string by getContents () method. Cell (0, 0) in the sample code is text type, Cell (1, 0) is a digital type, Cell (1, 1) is a date type, through getContents (), three types of return values are characters. If you need to know the exact type of Cell content, the API also provides a range of methods.
Refer to the following code segment: 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, the type of cell can be obtained through the getType () method, and then match the basic type provided by the API, forcibly converting into a corresponding type, finally call the corresponding value method Getxxx ( ), You can get the value of the determined type. The API provides the following basic types, corresponding to the data format of Excel. For details of each type, see Java Excel API Document. When you complete the processing of Excel spreadsheet data, you must use the close () method to close the previously created object to release the memory space occupied by the read data table, which is especially important when reading a large amount of data. . Refer to the following code segment: // When the operation is complete, turn off the object, release the occupied memory space RWB.Close (); Java Excel API provides many ways to access the Excel data table, here I only briefly introduce several common methods For other methods, please refer to Java Excel API Document in the appendix.
• Method for providing the Workbook class 1. INT getNumberOfsheets () gets the number of worksheets (Sheets) in Workbook, example: jxl.workbook rw B = jxl.workbook.getworkbook (new file (sourcefile); int shop = RWB.GETNUMBEROFSHEETS (); 2. Sheet [] getSheets () Returns an array of worksheets in 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 that is being used, it seems that there is not much role.
JXL.Workbook RWB = jxl.workbook.getWorkbook (new file (sourcefile)); string apiversion = rwb.getversion (); SHEET interface provides the method of the Sheet interface 1. String getName () Get the name of the Sheet, example: jxl.workbook RWB = jxl.workbook.getworkbook (new file (sourcefile)); jxl.sheet = rwb.getsheet (0); string sheetname = rs.getName (); 2. Int getColumns () Get the column included in the Sheet table Number, example: jxl.workbook rwb = jxl.workbook.getWorkbook (new file (sourcefile)); jxl.sheet = rwb.getsheet (0); int gcolumns = rs.getColumns (); 3. Cell [] getColumn INT Column) Gets all 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 () Gets the total number of lines contained in the Sheet table, example: jxl.workbook rwb = jxl.workbook.getworkbook (new file (sourcefile); JXL . SHEET RS = rwb.getsheet (0); int gr = rs.getrows (); 5. Cell [] getRow (int in) Get all cells of a certain row, returns a 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. C Ell getcell (int column, int in) Gets 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, this is a common line, the column combination different. JXL.Workbook RWB = jxl.Workbook.getWorkbook (new file (sourcefile)); jxl.sheet = rwb.getsheet (0); cell cell = rs.getcell (0, 0); generate new Excel works below The 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.), all of which are written as a string. (Full code See Excelwriting.java) Similar to reading Excel worksheets, first create a Workbook object using the factory method of the Workbook class, hereby pay attention, can only be provided by the API Method to create Workbook, not to use WritableWorkbook constructor because the constructor of class WritableWorkbook is protected by protected type.
The sample code snippet is as follows: import java.io. *; import jxl. *; Import jxl.write. *; ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... "read-only Workbook object // Method 1: Create 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 ();} API provides two ways to process writable output streams, one is to generate local files, if file If the name does not have a full path, the default file will be positioned in the current directory. If the file name comes with a full path, the generated Excel file is positioned in the corresponding directory; the other is to write the Excel object directly to Output stream, for example: users access the web server through the browser, if the HTTP header is set correctly, the browser automatically calls the client's Excel application to display dynamically generated Excel spreadsheets.
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 Worksheets in the position of work, refer to the following code snippet: file: // Create an Excel worksheet jxl.write.writablesheet WS = wwb.createsheet ("Test Sheet 1", 0); "This pot is also supported The material is also ready, and it is possible to start the pot! ", Now you have to do it is just the Excel basic data type provided by the API, and you can add them to the worksheet. Refer to the following code segment: File : // 1 /. Add label object jxl.write.label labelc = new jxl.write.label (0, 0, "this is a label cell"); ws.addcell (labelc); // Add with a word type Formatting object 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 a 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, ws.addcell (labelnf); // 3. Add Boolean object jxl.write.Boolean labelb = new jxl. Write.Boolean (0, 2, False); ws.addcell (label;
// 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 with formatting Object 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. The first point, when constructing the cell, the location of the cell in the worksheet has been determined. Once created, the location of the cell cannot be changed, although the content of the cell can be changed. In the second point, the positioning of the cell is the following regularity (COLUMN, ROW), and the subscript starts from 0, 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 usage memory, see the following code snippet: file: // Write the EXEL worksheet Wwb.write (); // Close Excel work thin object Wwb.close ( ); This may have little difference with the operation of reading the Excel file, before closing the Excel object, you must first call the Write () method, because the previous operation is stored in the cache, so it is necessary to operate through this method. The content is saved in the file. If you turn off the Excel object first, you can only get an empty work. Copy, update Excel work thin Next briefly introduces how to update an existing work, mainly below, the first step is to construct a read-only Excel work, the second step is to use the Excel workbook that has been created Create a new writable Excel workbook, refer to the following code snippet: (full code See ExcelModifying.java) file: // 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); // Determine the type of cell, make corresponding transformation IF ( Wc.gettype () == celltype.label) {Label L = (label) WC; L.SetString ("The value has been modified.");} // Write Excel object Wwb.write (); // Off The writable Excel object wwb.close (); // Close the read-only Excel object rw.close (); use this way to build an Excel object, which is complete because of the reason, because the above example is API Main application.
