Jxl Excel operations

Jxl Excel operations

Jxl is a Korean-written Java Excel tool. In the open-source world, there are two sets of influential APIs available: poi and jexcelapi. The function is weaker than that of 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.

Use:

Build Environment

Unpack the downloaded file, get jxl. jar, and put it into classpath. The installation is complete.

Basic operations

1. Create a file

To generate an Excel file named “test.xls, the first worksheet is named
The general effect of "Page 1" is as follows:

Package test; // generate the 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 the file writableworkbook book = workbook. createworkbook (new file ("test.xls"); // generate a worksheet named "first page". The parameter 0 indicates that 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 () of the first column) // and the cell content is test label Label = new label (0, 0, "test"); // Add the defined cell to the sheet. addcell (Label);/** // ** the complete package path of number must be used to generate a cell that saves numbers. Otherwise, the location of the cell in syntax ambiguity is the second column, the first row, the value is 789.123 */jxl. write. number = new jxl. write. number (1, 0,555.12541); sheet. addcell (number); // write data and close the file book. write (); book. close ();} catch (exception e) {system. out. println (e );}}}

After compilation, an Excel file is generated.

3. Reading files

Take the Excel file we just created as an example to perform a simple read operation. The program code is as follows:

Package test; // read the Excel 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 ("test.xls"); // obtain the first worksheet object sheet = book. getsheet (0); // obtain the cell cell1 = sheet in the first row of the first column. getcell (0, 0); string result = cell1.getcontents (); system. out. println (result); book. close ();} catch (exception e) {system. out. println (e );}}}

Program Execution result: Test

4. modify files
You can use jexcelapi to modify an existing Excel file. When modifying an Excel file, you can open the file in different ways,
Other operations are the same as creating an Excel file. The following example adds a worksheet to the generated Excel file:

Package test; 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 {// Excel file workbook WB = workbook. getworkbook (new file ("test.xls"); // open a copy of the file and specify that the data is written back to the original file writableworkbook book = workbook. createworkbook (new file ("test.xls"), WB); // Add a worksheet writablesheet sheet = book. createsheet ("Page 2", 1); sheet. addcell (new label (0, 0, "test data on the second page"); book. write (); book. close ();} catch (exception e) {system. out. println (e );}}}

Other operations

I. data formatting

Excel does not involve complex data types. It can be used to process strings, numbers, and dates.

1. String formatting

Character string formatting involves the font, width, font size, and other elements. These functions mainly include writablefont and
Writablecellformat class. Suppose we use the following statement to generate a cell containing strings,
To facilitate the description, we added the number for each line of command:

 WritableFont font1 =   new  WritableFont(WritableFont.TIMES, 16 ,WritableFont.BOLD); ① WritableCellFormat format1 = new  WritableCellFormat(font1); ② Label label = new  Label( 0 , 0 ,”data  4  test”,format1) ③

① Specifies the string format: The font is times, the font size is 16, and the string format is bold. Writablefont has a rich set
Constructor is used in different cases. The Java-Doc of jexcelapi has a detailed list, which is not listed here.

② The writablecellformat class is used in the Code. This class is very important. It can be used to specify various types of cells.
Attribute. More descriptions are available in the subsequent cell formatting.

③ The constructor of the label class is used to specify the format that the string is given.

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 aligment as the center format1.setalignment (jxl. format. Alignment. centre); // specify the vertical alignment as the center format1.setverticalignment (jxl. format. verticalignment. centre );

Ii. 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.

1. Merge Cells

Writablesheet. mergecells (int m, int N, int P, int Q); // The function is to merge all cells from (m, n) to (p, q), for example: writablesheet sheet = book. createsheet ("first page", 0); // merge all cells in the first row to the sixth row of the first column. mergecells (0, 0, 5, 0 );

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

2. Row Height and column width

Writablesheet. setrowview (int I, int height); // specifies the height of row I + 1, for example: // sets the height of row I to 200 sheet. setrowview (0,200); writablesheet. setcolumnview (int I, int width); // specifies the width of column I + 1. For example: // set the width of the first column to 30 sheet. setcolumnview (0, 30 );

Jexcelapi also has some other functions, such as inserting images. Here we will not introduce them one by one. You can explore them yourself.

If you want to read an Excel file, you need to know the number of rows and columns:

Workbook book = workbook. getworkbook (new file ("test 1.xls"); // obtain the first worksheet object sheet = book. getsheet (0); // get the cell int columnum = sheet in the first row of the first column. getcolumns (); // get the number of columns int rownum = sheet. getrows (); // get the number of rows system. out. println (columnum); system. out. println (rownum); For (INT I = 0; I <rownum; I ++) // read and write cyclically {for (Int J = 0; j <columnum; j ++) {Cell cell1 = sheet. getcell (J, I); string result = cell1.getcontents (); system. out. print (result); system. out. print ("\ t");} system. out. println ();} book. close ();