Use the jxl component to operate Excel files and export files

Several EXCEL reports are required for the projects involved during this period. The jxl components are used in the project framework, so the detailed usage of the jxl components is summarized.

I. Introduction

Jxl is a Korean-written Java Excel tool. In the open-source world, there are two sets of influential APIs available: poi and jexcelapi. Among them, jexcelapi is weaker than poi. However, jexcelapi provides excellent support for Chinese characters. The API is pure Java and does not depend on Windows systems. Even if it runs in Linux, it can process Excel files correctly. In addition, this API has limited support for graphics and charts and only recognizes PNG formats.

2. Build the environment

Download the jxl. jar package online and import it to the project Lib.

III. Basic operations

1. Create a file

The following figure shows an Excel file named “test.xls. The first worksheet is named "first page ". After compilation, an Excel file is generated.

 //  Generate an Excel class  Import  Java. Io. file;  Import  Jxl. workbook; Import  Jxl. Write. label;  Import  Jxl. Write. writablesheet;  Import  Jxl. Write. writableworkbook;  Public   Class  Createexcel {  Public   Static   Void  Main (string ARGs []) {  Try  {  // Open a file Writableworkbook book = Workbook. createworkbook ( New File ("C:/test.xls" ));  //  Generate a worksheet named "first page". parameter 0 indicates this is the first page. Writablesheet sheet = book. createsheet ("first page", 0 );  //  In the constructor of the label object, the cell position is the first row (0, 0) in the first column)  //  And the cell content is test. Label Label = New Label (0, 0, "test");  //  Add the defined cells to the worksheet  Sheet. addcell (Label );  //  To generate a cell that saves numbers, you must use the full package path of number. Otherwise, the syntax is ambiguous.  //  The cell position is the second column, the first row, and the value is 123.456. Jxl. Write. Number = New Jxl. Write. Number (1, 0,123.456 ); Sheet. addcell (number );  //  Write Data and close files Book. Write (); book. Close ();}  Catch  (Exception e) {system. Out. println (e );}}} 

2. Cell operations

An important part of Excel is Cell operations, such as Row Height, column width, and cell merging. Fortunately, jexcelapi provides these support. These operations are relatively simple. The following describes only related APIs.

Merge Cells

Merging can be either horizontal or vertical. The merged cells cannot be merged again. Otherwise, an exception is triggered.

//The method is to merge all cells from (m, n) to (p, q ).Writablesheet. mergecells (IntM,IntN,IntP,IntQ );//Merge all cells in 1st columns and 1st rows to 3rd ColumnsSheet. mergecells (0, 0, 2, 3);//Merge cells and then add content. And the defined row orientation is in the first row orientation of the merged cell. Otherwise, the content cannot be added, as shown below:Label Label =NewLabel (0, 0, "test"); Sheet. addcell (Label );
 

Row Height and column width

//Specifies the height of line I + 1.Writablesheet. setrowview (IntI,IntHeight );//Set the height of the first row to 200Sheet. setrowview (0,200);//Specifies the width of column I + 1.Writablesheet. setcolumnview (IntI,IntWidth );//Set the width of the first column to 30.Sheet. setcolumnview (0, 30 );
 

3. data formatting

String formatting

Character string formatting involves the font, width, font size, and other elements. These functions are mainly handled by writablefont and writablecellformat.

Writablefont has a rich set of constructor sub-Methods for use in different cases. The Java-Doc of jexcelapi has a detailed list, which is not listed here.

The writablecellformat class is very important. It can be used to specify various attributes of cells. More descriptions will be provided in subsequent cell formatting.

 
//Font Style:; 11; boldWritablefont font1 =NewWritablefont (writablefont. createfont (""), 11, Writablefont. Bold); writablecellformat format1=NewWritablecellformat (font1); label Label=NewLabel (0, 0, "test", format1 );
 

Alignment Mode

In the writablecellformat class, another important method is to specify the Data Alignment mode. For example, for the above instance, you can specify:

//Specify the horizontal alignment to centerFormat1.setalignment (jxl. format. Alignment. centre );//Center the vertical alignmentFormat1.setverticalalignment (jxl. format. verticalalignment. centre );
 

4. Read files

 //  Excel Reading Class  Import  Java. Io. file;  Import  Jxl. cell;  Import  Jxl. sheet;  Import  Jxl. workbook; Public   Class  Readexcel {  Public   Static   Void  Main (string ARGs []) {  Try  {Workbook book = Workbook. getworkbook ( New File ("C:/test.xls" ));  //  Obtain the first worksheet object Sheet sheet = book. getsheet (0 ); //  Obtain the cell in the first row of the first column. Cell cell = sheet. getcell (0, 0 ); String Contents = Cell. getcontents (); //  Get cell content  System. Out. println (contents); book. Close ();}  Catch  (Exception e) {system. Out. println (e );}}} 

ProgramThe output result is: test.

The cell interface can also obtain attributes such as row and column positions of cells and whether cells are hidden. For details, refer to the jxl API.

5. modify files

In addition to opening an Excel file, modifying an Excel file is the same as creating an Excel file.

 // Modify the Excel class  Import  Java. Io. file;  Import  Jxl. workbook;  Import  Jxl. Write. label;  Import  Jxl. Write. writablesheet;  Import  Jxl. Write. writableworkbook;  Public   Class  Updateexcel {  Public   Static  Void  Main (string ARGs []) {  Try  {  //  Obtain an Excel file Workbook WB = Workbook. getworkbook ( New File ("C:/test.xls" ));  //  Open a copy of the file and specify the data to be written back to the original file. Writableworkbook book = Workbook. createworkbook ( New File ("C:/test.xls" ), WB ); //  Modify original worksheet data Writablesheet sheet1 = book. getsheet (0 ); Sheet1.addcell (  New Label (0, 0, "overwrite the original test" ));  //  Add a new worksheet Writablesheet sheet2 = book. createsheet ("Page 2", 1 ); Sheet2.addcell (  New Label (0, 0, "test data on the second page" ); Book. Write (); book. Close ();}  Catch (Exception e) {system. Out. println (e );}}} 

4. Export files

Attached is an example of an exported file.

JSCode

$ ( Function  (){  /*  * Report export button  */  $ ( '# Exportbtn'). Click ( Function  (){  If (! $ ('# Frm'). validationengine ('validate' )) Return   False  ; $ ( '# Frm') [0]. Action =' $ {CTX}/exportaction. do? M = exportexcel' ; $ ( '# Frm') [0 ]. Submit (); $ (  This ). ATTR ('Disabled ', True  ); Window. setTimeout (  Function  () {Document. getelementbyid ( 'Portbtn '). Disabled = False  ;}, 5000);});}); 

Java code

 /**  Convert to the Chinese supported by the current Browser Based on the browser type  */  String filename = "Excel worksheet" ;  /**  Header browser key  */  String useragent = Request. getheader ("User-Agent" ). Touppercase ();  If (Useragent! = Null & Useragent. Length ()! = 0 & filename! =Null & Filename. Length ()! = 0 ){  /**  Header IE  */      If (-1! = Useragent. indexof ("MSIE" ) Filename = Urlencoder. encode (filename, "UTF-8" );  /**  Header Mozilla  */      Else   If (-1! = Useragent. indexof ("Mozilla") Filename = New String (filename. getbytes (), "ISO8859-1" );  /**  Header Safari  */      Else   If (-1! = Useragent. indexof ("safari" ) Filename = New String (filename. getbytes (), "ISO8859-1" );  /**  Header Opera */      Else   If (-1! = Useragent. indexof ("Opera" ) Filename = New String (filename. getbytes (), "ISO8859-1" );  /**  Header other kernel browsers  */      Else  Filename = New String (filename. getbytes (), "ISO8859-1" );} Response. setcharacterencoding ( UTF-8"); Response. setcontenttype ( "Application/vnd. MS-excel" ); Response. setheader ( "Content-disposition ", New Stringbuffer ("attachment"). append ("; filename ="). append (filename). append (". xls" ). Tostring (); writableworkbook = Workbook. createworkbook (response. getoutputstream (); writablesheet Sheet = Book. createsheet ("Excel worksheet", 0 );  //  ...  Book. Write (); book. Close ();