Jxl (http://www.andykhan.com/jexcelapi/index.html) is a Korean written Java Excel operating tool, in the Open Source world, there are two sets of more influential APIs available, one is poi (http://poi.apache.org /), one is 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 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 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 (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 location of the cell is the second column, the first row, and 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 sheet = book. getsheet (0 );
// Obtain the cell in the first row of the first column
Cell cell1 = sheet. 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 {
// Obtain the file in Excel
Workbook WB = Workbook. getworkbook (new file ("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 ("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 alignment to center
Format1.setalignment (jxl. format. Alignment. centre );
// Center the vertical alignment
Format1.setverticalalignment (jxl. format. verticalalignment. 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
Sheet. 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:
// Set the height of the first row 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 sheet = book. getsheet (0 );
// Obtain the cell in the first row of the first column
Int columnum = sheet. getcolumns (); // obtain 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 ();
Jxl Excel operations
