Full mining Java Excel API usage method

Excel's friends with Windows operating systems must not be unfamiliar with Microsoft, but it's not easy to manipulate Excel files using the Java language. Today, with the growing popularity of Web applications, the need to manipulate Excel files through the Web is growing, and the more popular operation is to create a CSV (comma separated values) file in a JSP or servlet and mime,text/the file to The CSV type is returned to the browser, then the browser 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, to introduce an open source project, Java Excel API, you can easily manipulate Excel files. Java Excel API Introduction Java Excel is an open source project through which Java developers can read the contents of Excel files, create new Excel files, and update existing Excel files. Using this API non-Windows operating systems can also work with Excel data tables through a pure Java application. Because it is written in Java, we can use the JSP, servlet to invoke the API to implement the access to Excel datasheet in the Web application. The stable version now released is V2.0, which provides the following features: reading data from files in Excel 95, 97, 2000, and so on;
Read the Excel formula (you can read the formula after Excel 97);
Generate Excel Datasheet (formatted as Excel 97);
Support the format of fonts, numbers and dates;
Support for Cell shadow operation, and color operation;
Modify the existing data table; The following features are not supported, but will soon be available: Unable to read the chart information;
Can be read, but cannot generate formulas, the final calculation of any type of formula can be read out;
　
Apply Example 1, read a datasheet from an Excel file the Java Excel API can read Excel data tables from either a file on the local file system (. xls) or from an input stream. The first step in reading an Excel datasheet is to create a workbook (term: Workbook), and the following code snippet illustrates how it should be done: (see Excelreading.java) Import java.io.* (complete code);
Import jxl.*;
... ... ... ...
Try
{
Building Workbook objects, read-only Workbook objects
Create workbook directly from local file
Create a workbook from an input stream
InputStream is = new FileInputStream (sourcefile);
JXL. Workbook RWB = Workbook.getworkbook (IS);
}
catch (Exception e)
{
E.printstacktrace ();
Once workbook is created, we can access the Excel Sheet (term: worksheets) through it. Refer to the following code fragment://Get the first sheet of sheet
Sheet rs = rwb.getsheet (0); We can access it either through the name of the sheet or through the subscript. If you are accessing by subscript, note that the subscript starts at 0, just like an array. Once we get the sheet, we can access the Excel cell (term: cell) through it. Refer to the following code fragment://Get the first row, the value of the first column
Cell c00 = Rs.getcell (0, 0);
String strc00 = c00.getcontents ()//Get first row, value of second column
Cell C10 = Rs.getcell (1, 0);
String STRC10 = c10.getcontents ()//Get second row, value of 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 we just get the value of the cell, we can easily pass the getcontents () method, which can return any type of cell value as a string. In the example code, the cell (0, 0) is a literal type, the cell (1, 0) is a numeric type, the cell (1,1) is a date type, and the return value of three types is a character type by getcontents (). 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 fragment: 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 a cell object is obtained, the type of the cell can be obtained by the GetType () method, which is then matched to the basic type provided by the API, cast to the appropriate type, and finally called the corresponding value method GetXXX (), so that the value of the type can be determined. The API provides the following basic types, which correspond to the data format of Excel, as shown in the following illustration: For each type of specific meaning, see Java Excel API document. When you have finished processing Excel spreadsheet data, be sure to use the close () method to turn off previously created objects to free up memory space in the process of reading the data table, which is especially important when reading large amounts of data. Refer to the following code fragment://When the operation is complete, close the object and release the memory space occupied
Rwb.close (); The Java Excel API provides a number of ways to access Excel datasheets, where I'll just briefly introduce a few common methods, and refer to the Java Excel API document in the appendix for other methods. The workbook class provides method 1. int getnumberofsheets () gets 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 worksheets (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 that is being used, as if it doesn't have much effect. JXL. Workbook RWB = Jxl. Workbook.getworkbook (New File (sourcefile));
String apiversion = Rwb.getversion (); The sheet interface provides method 1) String GetName () to 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 () Gets 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 () Gets 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 in a row and returns an array of cell objects, as shown in 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, note 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 row and column combinations. 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 workbook The following code is mainly to show you how to generate a simple Excel worksheet, where the contents of the cell are without any modification (such as font, color, etc.), all content is written as a string. (The complete code, see Excelwriting.java) is similar to reading an Excel worksheet by first creating a writable Workbook (Workbook) object using the factory method of the workbook class, which is to be noted here Workbook can only be created through the factory method provided by the API, not with the Writableworkbook constructor, because the class Writableworkbook constructor is protected type. Sample code snippet is as follows: Import java.io.*;
Import jxl.*;
Import jxl.write.*;
... ... ... ...
Try
{
Building Workbook objects, read-only Workbook objects
Method 1: Create a writable Excel workbook
Jxl.write.WritableWorkbook WWB = Workbook.createworkbook (new File (targetfile))//method 2: Writes the 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 handle writable output streams, one that generates local files directly, and if the file name does not have a full path, the default files are positioned in the current directory, and if the file name has a full path, the resulting Excel file is positioned 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 the browser, and if the HTTP header is set correctly, the browser automatically invokes the client's Excel application to display the dynamically generated Excel spreadsheet. The next step is to create the worksheet, the method of creating a worksheet is almost the same as creating a workbook, and the same is achieved by using the Factory mode method, which requires two parameters, one is the name of the worksheet, the other is the position of the worksheet in the workbook, and the following code fragment is referenced:// Create an Excel worksheet
Jxl.write.WritableSheet ws = Wwb.createsheet ("Test Sheet 1", 0); " This pot is also good, the material is also ready, you can start to fry! "All you have to do now is to instantiate the Excel basic data types provided by the API and add them to the worksheet, and refer to the following code fragment:
1. Add Label Object
Jxl.write.Label LABELC = new Jxl.write.Label (0, 0, "This is a Label cell");
Ws.addcell (LABELC);//Add object with 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.addcell (LABELCF)//Add object with font color formatting
Jxl.write.WritableFont WFC = new Jxl.write.WritableFont (Writablefont.arial, 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 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 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); Here are two points for everyone to draw attention to. 1th, when you construct a cell, the position of the cell on 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 started from 0, for example, A1 is stored in (0, 0), and B1 is stored in (1, 0). Finally, don't forget to close the open Excel workbook to free up memory consumption, see the following code fragment://write to Exel Worksheet
Wwb.write ()//Close Excel Workbook object
Wwb.close (); This may be a little different than 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 the action's contents are saved in the file by this method. If you close the Excel object first, you can only get an empty workbook. 3, copy, update Excel workbook Next briefly describes 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 step is to create a new writable Excel workbook with the Excel workbook you've created, Refer to the following code fragment: (full code see EXCELMODIFYING.JAVA)//Create an object for a read-only Excel workbook
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 first Cell object
Jxl.write.WritableCell WC = Ws.getwritablecell (0, 0);//judge the type of cell, make the corresponding transformation
if (wc.gettype () = = Celltype.label)
{
Label L = (label) WC;
L.setstring ("The value has been modified.");
}//Write to Excel objects
Wwb.write ()//Close writable Excel object
Wwb.close ();//close read-only Excel objects
Rw.close (); The reason for building Excel objects in this way is simply because of the efficiency, because the above example is the main application of the API. To improve performance, when reading a worksheet, some of the output information related to the data, all the format information, such as: font, color, etc., is not processed, because our goal is to obtain the value of the row data, so that there is no adornment, and do not have any effect on the value of the row data. The only downside is that two of the same worksheets are saved in memory at the same time, which can take up quite a bit of memory when the worksheet is larger, but now it seems like the size of the memory is not a key factor. Once you have a writable worksheet object, we can update the cell object, where we don't 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 appropriate setxxx () method to complete the update operation. We can add new cell modifications to the cells so that the contents of the cell are displayed in a different form, as the original formatting modification of the cell cannot be removed. The newly generated worksheet object is writable, and we can add new cells to the worksheet in addition to updating the original cell, which is exactly the same as the operation in Example 2. Finally, don't forget to call the Write () method, write the updated content to a file, and then close the Workbook object, where two workbook objects are closed, one is read-only, and the other is writable.