Java Excel API-a Java API to read, write and modify Excel (Collection)

Official Address:
The current maximum version of The http://www.andykhan.com/jexcelapi/ is 2.6.

Authentic:

Http://www.andykhan.com/jexcelapi/download.html
The author's website describes its features as follows:
● Support all versions of Excel-
● Generate standard Excel 2000 format
● Supports font, number, and date operations
● Ability to modify cell attributes
● Images and charts supported
It should be said that the above functions have roughly met our needs. The most important thing is that this API is pure Java and does not depend on Windows. 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.
Build Environment
Unpack the downloaded file, get jxl. jar, and put it into classpath. The installation is complete.
Basic operations
1. Create a file
An Excel file named "Testing Data .xls" will be generated. The first worksheet is named "first page". The general effect is as follows:
Code (Createxls. Java ):
// Generate an Excel class
Import java. Io .*;
Import jxl .*;
Import jxl. Write .*;
Public class createxls
{
Public static void main (string ARGs [])
{
Try
{
// Open the file
Writableworkbook book =
Workbook. createworkbook (New file(development 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 );
/* Generate a cell for saving numbers
The full package path of number must be used; otherwise, the syntax is ambiguous.
The cell position is the second column, the first row, and the value is 789.123 */
Jxl. Write. Number = new jxl. Write. Number (1,0, 789.123 );
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 at the current position.
3. Reading files
Take the Excel file we just created as an example to perform a simple read operation, Program The Code is as follows:
// Read the Excel class
Import java. Io .*;
Import jxl .*;
Public class readxls
{
Public static void main (string ARGs [])
{
Try
{
Workbook book =
Workbook. getworkbook (New file(development 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 perform the same operations as creating an Excel file in addition to opening the file. The following example adds a worksheet to the generated Excel file:
// Modify the Excel class and add a worksheet
Import java. Io .*;
Import jxl .*;
Import jxl. Write .*;
Public class updatexls
{
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(development test .xls "), WB );
// Add a worksheet
Writablesheet sheet = book. createsheet ("second page", 1 );
Sheet. addcell (new label (, "test data on the second page "));
Book. Write ();
Book. Close ();
} Catch (exception E)
{
System. Out. println (E );
}
}
}
Execution result
Advanced 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 are mainly handled by writablefont and writablecellformat. Suppose we use the following statement to generate a cell containing strings. For the sake of convenience, we add numbers for each line of commands:

Writablefont font1 =
New writablefont (writablefont. times, 16, writablefont. bold); or // set the font format to the writablefont font3 = new writablefont (writablefont. createfont (" _ gb2312"), 12, writablefont. no_bold); ① writablecellformat format1 = new writablecellformat (font1); ② label Label = new label (, "data 4 test", format1) ③ where ① specifies the string format: font: Times, font size: 16, bold display. Writablefont has a rich set of constructor for use in different cases. The Java-Doc of jexcelapi has a detailed list, which is not listed here. ② The Code uses the writablecellformat class. This class is very important. It can be used to specify various attributes of cells. More descriptions will be provided in 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 );

// Set automatic line feed
Format1.setwrap (true );

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 );
It specifies the height of line I + 1, for example:
// Set the height of the first row to 200
Sheet. setrowview (0,200 );
Writablesheet. setcolumnview (int I, int width );
It specifies the width of column I + 1, for example:
// Set the width of the first column to 30
Sheet. setcolumnview (0, 30 );
5. Operate Images
Public static void write () throws exception {
Writableworkbook WWB = Workbook. createworkbook (new file ("C:/1.xls "));
Writablesheet Ws = WWB. createsheet ("test sheet 1", 0 );
File file = new file ("C: \ jbproject \ PVS \ webroot \ weekhit \ 1109496996281.png ");
Writableimage image = new writableimage (1, 4, 6, 18, file );
WS. addimage (image );
WWB. Write ();
WWB. Close ();
}
It is very easy to insert cells in the same way, but there are more parameters. The writableimage class inherits draw, which is only one of its constructor methods. The last parameter is unnecessary, the types of the preceding four parameters are double, X, Y, width, and height. Note that the width and height here are not the width and height of the image, but the number of units occupied by the image, because the inherited draw must be of the double type, I haven't looked at how it is implemented in detail :) because of the rush, the function is completed first, and the rest will be studied later. In the future, I will continue to write my experiences in use for you.
Read:
This is an idea for reading. First, use an input stream (inputstream) to get the Excel file, then use the workbook in jxl to get the workbook, and use sheet to get the worksheet from the workbook, use cell to obtain a cell in the worksheet.
Inputstream-> workbook-> Sheet-> cell, the cells in the Excel file are obtained.
Code:
String Path = "C :\\ excel.xls"; // Excel File URL
Inputstream is = new fileinputstream (PATH); // write to fileinputstream
Jxl. Workbook WB = Workbook. getworkbook (is); // get the working thin
Jxl. Sheet ST = WB. getsheet (0); // obtain the first worksheet in the workbook.
Cell cell = ST. getcell (); // obtain the first cell of the worksheet, that is, A1.
String content = cell. getcontents (); // getcontents () convert the characters in the cell into strings
WB. Close (); // close the workbook
Is. Close (); // close the input stream

We can use the getcell (x, y) method of sheet to obtain the coordinates of any cell, x, y, and excel.
For example, A1 corresponds to (0, 0), A2 corresponds to (0, 1), D3 corresponds to (3, 2). The coordinates in Excel start from a, 1, and jxl all start from 0.
You can also use the getrows () and getcolumns () Methods of sheet to obtain the number of rows and columns for loop control and output all contents in a sheet.
Write:
The main content written to excel is the class in the jxl. Write package.
The idea is as follows:
Outputstream <-writableworkbook <-writablesheet <-label
The label indicates the cell location and content written to the sheet.
Code:
Outputstream OS = new fileoutputstream ("C: \ test.xls"); // output Excel File URL
Writableworkbook WWB = Workbook. createworkbook (OS); // create a writeable workbook
Writablesheet Ws = WWB. createsheet ("sheet1", 0); // create a writable Worksheet
Label labelcf = new label (0, 0, "hello"); // create the write location and content
WS. addcell (labelcf); // write the label to the sheet.
The constructor label (int x, int y, string astring) xy of the label refers to the XY of the read, and astring is the written content.
Writablefont WF = new writablefont (writablefont. Times, 12, writablefont. Bold, false); // you can specify the write font.
Writablecellformat wcff = new writablecellformat (WF); // set cellformat
Label labelcf = new label (0, 0, "hello"); // create a write location, content, and format
Another constructor of label (int c, int R, string cont, cellformat st) can format the written content and set the font and other attributes.
Now you can write
WWB. Write ();
Close after writing
WWB. Close ();
Close the output stream.
OS. close;
OK, as long as the read and write are combined, it is more convenient to read data from N Excel files and write it into the new Excel table you want.

The following is an example of a program:

Program code: SQL = "select * From tablename ";
Rs = stmt.exe cutequery (SQL );

// Create an Excel file
String filepath = request. getrealpath ("aaa.xls ");
File myfilepath = new file (filepath );
If (! Myfilepath. exists ())
Myfilepath. createnewfile ();
Filewriter resultfile = new filewriter (myfilepath );
Printwriter myfile = new printwriter (resultfile );
Resultfile. Close ();

// Use jxl to add content to the new file
Outputstream outf = new fileoutputstream (filepath );
Jxl. Write. writableworkbook WWB = Workbook. createworkbook (outf );
Jxl. Write. writablesheet Ws = WWB. createsheet ("sheettest", 0 );

Int I = 0;
Int J = 0;

For (int K = 0; k <Rs. getmetadata (). getcolumncount (); k ++ ){
WS. addcell (new label (K, 0, Rs. getmetadata (). getcolumnname (k + 1 )));
}

While (Rs. Next ()){
Out. println (Rs. getmetadata (). getcolumncount ());

For (int K = 0; k <Rs. getmetadata (). getcolumncount (); k ++ ){
WS. addcell (new label (K, J + I + 1, Rs. getstring (k + 1 )));
}

I ++;
}
WWB. Write ();
WWB. Close ();
} Catch (exception e) {e. printstacktrace ();}
Finally {

Rs. Close ();
Conn. Close ();
}

Response. sendredirect ("aaa.xls ");

Download Page)

Jexcelapi v2.6.2 (1695 Kbytes)

Jexcelapi v2.6.1 (1639 Kbytes)

Jexcelapi v2.6 (1603 Kbytes)

Reprinted:

Http://tmsoft.lsxy.com/index.php? Id = 605 & load = read

Refer:

Http://www.andykhan.com/jexcelapi/

Http://www.andykhan.com/jexcelapi/tutorial.html