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 currently released stability is V2.0, providing the following functions: read data from the Formats such as Excel 95, 97, 2000; read the Excel formula (the formula after reading Excel 97); generate an 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 do not support the following features, but will soon provide: Read chart information; readable, but cannot generate formulas, any type of formula last calculated value can be read; Application Example 1, read data sheet from the Excel file Java Excel API can either from a local file system (. XLS), you can also 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 is explained how to operate: (full code see Excelreading.java)
Import java.io. *; import jxl. *; ... ... ... ... ... ... Try {// Build a Workbook object, read-only Workbook object // Create Workbook directly from the local file to create WorkbookInputStream IS = New FileInputStream (Sourcefile) JXL.Workbook RWB = Workbook.getWorkbook (IS); (Exception E) {E.PrintStackTrace ();}
Once you have created Workbook, we can access the Excel Sheet by it (Terminology: Worksheet). Refer to the following code segment:
// Get the first Sheet table Sheet RS = rwb.getsheet (0);
We can access it through the name of Sheet, or you can access it by 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 segment:
/ / Get the first line, the value of the first column Cell C00 = rs.getcell (0, 0); string strc00 = c00.getContents (); // Get the first line, the value Cell C10 = RS of the second column. getcell (1, 0); string strc10 = c10.getContents (); // Get the second line, the second column of the 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 only the value of Cell is obtained, we can easily return any type of CELL value as a string. 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 the cell can be obtained by getType () method, and then match the basic type provided by the API, forcibly converting into a corresponding type, and finally call the corresponding value method getxxx (), you can get it. Type value. The API provides the following basic types, corresponding to the data format of Excel, as shown below:
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 ();
The Java Excel API provides a number of ways to access the Excel data sheet, here I only briefly introduce several common methods, other methods, please refer to Java Excel API Document in the appendix.
