Jxl. Jar

Jxl. Jar jxl. Jar
Use Java to operate the tool library of an Excel table
All versions of Excel-are supported.
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. If the package cannot be loaded normally, you can put the downloaded jxl. jar package into D:/Java/JRE/lib/EXT (my name is jdk1.6. I believe that three related packages need to be put here When configuring tomcat.
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. The program 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 );
}
}
}
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 (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 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, not to mention the last parameter, 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 location and content of the cell where the sheet is written.
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. You only need to combine reading and writing, and then you can read data from N Excel files and write it into your desired new Excel table, which is more convenient.
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 ");