Excerpt: Detailed use of the JAVA JXL API

Transferred from: http://www.cr173.com/html/10377_1.html

1 Development Research
1.1 Requirements Description
MS's spreadsheet (EXCEL) is an important member of office and is a common format for saving statistics. As an Office document, it is bound to involve the exchange of electronic documents, Excel is a very common in the Enterprise file format, printing and management is more convenient. In a Java application, generating part of the data in Excel format is an important means of seamlessly connecting with other systems.
1.2 Excel developing common open source tools
In the open source world, there are two more influential APIs to use, one for POI and one for Jexcelapi.
1.3 Comparing the pros and cons of open source tools
1.3.1 JXL Advantages and disadvantages
JXL features are described below:
Supports all versions of Excel 95-2000
Generate Excel 2000 Standard format
Supports font, number, date manipulation
Ability to modify cell properties
Support for images and charts
It should be said that the above features have been able to roughly meet our needs. The key is that this API is pure Java, and does not rely on windows, even if running under Linux, it can also correctly handle Excel files. It is also important to note that this set of APIs has limited support for graphics and charts, and only the PNG format is recognized.
1.3.2 POI Pros and cons
Jakarta poi Project and Java Excel API in the open source world can be said to be parallel, but also each has advantages and disadvantages, POI in some details some small bug and do not support writing pictures (POI can actually write pictures, but no jxl to the convenience, more trouble), Other aspects are pretty good, while JXL provides support for images (but only PNG-formatted images), the problem is that support for formulas is not very good, but it provides simple formula reading support. So what kind of third-party plug-ins you choose to use in your project is entirely up to your application. If your software is quite related to finance, it is recommended that you use POI project, as it is not possible to use the calculation formula for my current project, and I would probably need to export the picture, so my choice is JXL.

1.4 Performance comparison and final selection
1.4.1 Memory consumption: (from the network)
Talk about the memory consumption of the JVM virtual machine.
Data volume 3,000 data, 60 columns per bar. JVM Virtual machine memory size 64M.
Use POI: Run to 2,800 or so to report memory overflow.
Use the jxl:3000 strip all out, and the memory has 21M of space.
It is conceivable that the memory consumption of the gap is still quite large.
Maybe it's because JXL is doing a pretty good job of recycling resources.
1.4.2 Speed Efficiency (read Excel data) (from the network)
File POI load time-consuming POI total time JXL load time JXL total time-consuming
File size 57KB 1172 Ms 1172 ms 1265 MS 2250 ms
File size 652KB 2297 ms 2313 ms 4406 ms 9750 ms
File size 2.24M 3109ms 3140ms 16313ms 37453ms

1.4.3 Writing Excel Speed efficiency
JXL inserting data is faster than POI
1.4.4 function Comparison
The JXL is relatively weak compared to the functionality provided. So if you're going to be able to implement a more complex scenario, consider using a poi, but if you just want to generate some big data, consider using JXL, or CSV is a good choice, but CSV is not really excel, However JXL inserting data is faster than POI.

2 JXL Development Guide
2.1 Introduction
JXL operations Excel includes Object Workbook,sheet, Cell.
An Excel corresponds to a Workbook object,
A workbook can have multiple sheet objects
A sheet object can have more than one cell object
2.2 Reading Excel Operations
By Workbook,sheet, the three objects of cell we can realize the reading of Excel file. Let's think about the reading step, regardless of what kind of Excel operating framework must go through
1. Select Excel file to get workbook
2. Select a worksheet
3. Select cell
4. Reading information
2.2.1 Reading a working book
Select Excel file to get workbook workbook
Workbook Workbook = Workbook.getworkbook (New File ("Myfile.xls"));
2.2.2 Reading Worksheets
Select the first worksheet (starting from 0) by workbook's Getsheet method
Sheet Sheet = workbook.getsheet (0);
You can also get sheet by the name of the job
2.2.3 Reading cells
Select a cell with a position of C2 by sheet Getcell method (two parameters starting from 0)
Cell C2 = Sheet.getcell (2,1);

2.2.3.1 reading the value of a cell
2.2.3.2 through the getcontents method of the cell
Read the information in the cell as a character string stringc2 = C2.getcontents ();
The 2.2.3.3 cell provides a GetType method
The ability to return type information for a cell, while JXL provides a Celltype class to preset type information in Excel, and JXL provides some subclasses of the cell class that are used to represent various types of cells, such as Labelcell,numbercell, Datecell cells that represent characters, numbers, and date types, respectively
if (c2.gettype () = = Celltype. LABEL)
{
Labelcell NC = (labelcell) C2;
String number b2 = NC. GetString ();
}
if (c2.gettype () = = Celltype. DATE)
{
Datecell NC = (datecell) C2;
Date number b2 = NC. GetDate ();
}

if (c2.gettype () = = Celltype.number)
{
Numbercell NC = (numbercell) C2;
Double number b2 = Nc.getvalue ();
}
The API provides the following basic types, which correspond to the data format of Excel, as shown in

2.2.4 to release resources: Workbook.close ()
When you have finished working with Excel spreadsheet data, be sure to use the close () method to close the previously created object to free up memory space used during reading of the data table, which is especially important when reading large amounts of data
Finally, don't forget to close workbook to free up resources: Workbook.close ();
2.3 Writing Excel Operations
By Writableworkbook,writablesheet,label These three objects, we can implement the insertion of Excel files. Let's think about it. Insert, no matter what kind of Excel operating framework must be experienced
1. Create a exce work book
2. Create a worksheet
3. Create a cell

2.3.1 Creating a workbook
The API provides two ways to process a writable output stream, one that generates a local file directly, and if the file name does not have a full path, the default files are located in the current directory, and if the file name has a full path, the resulting Excel file is located in the appropriate directory The other is to write the Excel object directly to the output stream, for example: the user accesses the Web server through a browser, and if the HTTP header is set correctly, the browser automatically calls the client's Excel application to display the dynamically generated Excel spreadsheet.
2.3.1.1 Creating a writable Excel workbook
Writableworkbook
WWB = Workbook.createworkbook (new File (targetfile));
2.3.1.2 writes Writableworkbook directly to the output stream
OutputStream OS = new FileOutputStream (targetfile);
Writableworkbook WWB = workbook.createworkbook (OS);
2.3.2 Creating worksheets
Writablesheet ws = Wwb.createsheet ("Contacts", 0);//Create Sheet
2.3.3 Creating cells
2.3.3.1 adding text class cells
Label LABELC = new label (0, 0, "This is a label cell");
2.3.3.2 adding an object with a font formatting
Writablefont wf = new Writablefont (Writablefont.times, Writablefont.bold, true);
Writablecellformat WCFF = new Writablecellformat (WF);
LABELCF = new label (1, 0, "This is a label Cell", WCFF);
Ws.addcell (LABELCF);
2.3.3.3 adding an object with font color formatting
Writablefont WFC = new Writablefont (Writablefont.arial,10,writablefont.no_bold, False,
Underlinestyle.no_underline, Jxl.format.Colour.RED);
Writablecellformat WCFFC = new Writablecellformat (WFC);
Label LABELCFC = new label (1, 0, "This is a label Cell", WCFFC);
Ws.addcell (LABELCF);
2.3.3.4 Adding a Number object
Number Labeln = new Jxl.write.Number (0, 1, 3.1415926);
Ws.addcell (Labeln);
2.3.3.5 adding a Number object with formatting
NumberFormat NF = new NumberFormat ("#.##");
Writablecellformat WCFN = new Writablecellformat (NF);
Number LABELNF = new Number (1, 1, 3.1415926, WCFN);
Ws.addcell (LABELNF);
2.3.3.6 Adding a Boolean object
Boolean Labelb = new Jxl.write.Boolean (0, 2, false);
Ws.addcell (LABELB);
2.3.3.7 Adding a DateTime object
DateTime Labeldt = new DateTime (0, 3, New Java.util.Date ());
Ws.addcell (Labeldt);
2.3.3.8 adding a DateFormat object with formatting
DateFormat df = new DateFormat ("dd MM yyyy hh:mm:ss");
Writablecellformat wcfdf = new Writablecellformat (DF);
DateTime LABELDTF = new DateTime (1, 3, New Date (), WCFDF);
Ws.addcell (LABELDTF);
2.3.3.9 Adding formula Cells
Fornual formual = new Formual (0,11, "Sum (A1:A9)");
Wrb.addcell (formual);
2.3.3.10 Adding images
Writableimage wrimage=new writableimage (1,5,10,10,new File (Imagefilepath));
Wrb.addimage (Wrimage);
Note that only PNG files are supported in the API.
2.3.4 Merging cells
Implemented by writablesheet.mergecells (int x,int y,int m,int N).
Represents the X+1 column, the y+1 row to the M+1 column, the n+1 row merge (four points define two coordinates, the upper-left corner and the lower-right corner) The result is a merged m-x+1 row, n-y+1 column, and the product is the number of merged cells.
Sheet.mergecells (0, 6, 3, 8);
Label = new label (0, 6, "12 cells merged");
Sheet.addcell (label);

2.3.5 Adding cell Styles
The main change is the cell background, font, color and so on.
Writablecellformat WC = new Writablecellformat ();
Wc.setalignment (Alignment.centre); Set Center
Wc.setborder (Border.all, Borderlinestyle.thin); Set Border lines
Wc.setbackground (Jxl.format.Colour.RED); Set the background color of a cell
Label = new label (1, 5, "Font", WC);
Sheet.addcell (label);
2.3.6 Setting the cell font
Writablefont Wfont =
New Writablefont (Writablefont.createfont ("Regular script"), 20);
Writablecellformat font = new Writablecellformat (Wfont);
Label = new Label (2, 6, "Regular script", font);
Sheet.addcell (label);
2.3.7 writing to a file
Wwb.write ();//Write Data
Wwb.close ();//Close file

2.4 Copy, update Excel workbook
Create a read-only Excel Workbook object
Jxl. Workbook RW = jxl. Workbook.getworkbook (New File (sourcefile));
Create a writable Excel workbook object
Writableworkbook Wwb=workbook.createworkbook (New File (targetfile), rw);
Read the first sheet
Jxl.write.WritableSheet ws = Wwb.getsheet (0);
Get the first Cell object
Jxl.write.WritableCell WC = Ws.getwritablecell (0, 0);
Determine the type of cell and make a corresponding conversion
if (wc.gettype () = = Celltype.label)
{
Label L = (label) WC;
L.setstring ("The value has been modified.");
}
Writing to an Excel object
Wwb.write ();
To close a writable Excel object
Wwb.close ();
Close a read-only Excel object
Rw.close ();
To improve performance, when reading a worksheet, some output information related to the data, all formatting information, such as font, color, etc., is not processed, because our goal is to get the value of the row data, so that there is no adornment, and does not affect the value of the row data. The only disadvantage is that two identical worksheets are saved in memory, so that when the worksheet is larger, it takes up quite a bit of memory, but now it seems that the size of the memory is not a key factor.
Once the writable sheet object has been obtained, we can update the cell object, where we do not have to invoke the Add () method provided by the API, because the cell is already in the worksheet, so we just need to call the corresponding setxxx () method to complete the update operation.
The original formatting of the cell is not removed, we can still add the new cell decoration, so that the contents of the cell in a different form of expression.
The newly generated sheet object is writable, and we can add new cells to the worksheet in addition to updating the original cells.
Finally, don't forget to call the Write () method, write the updated content to the file, and then close the workbook, where two workbook objects are closed, one is read-only, and the other is writable.

3 JXL to read and write Excel files (from the network)
3.1 Example One
Import Java.io.FileOutputStream;
Import Java.io.OutputStream;
Import Java.text.SimpleDateFormat;
Import java.util.ArrayList;
Import Java.util.Date;
Import java.util.List;
Import jxl.*;
Import jxl.format.Alignment;
Import Jxl.format.Border;
Import Jxl.format.BorderLineStyle;
Import Jxl.format.CellFormat;
Import Jxl.write.Boolean;
Import Jxl.write.Label;
Import Jxl.write.Number;
Import Jxl.write.WritableCellFormat;
Import Jxl.write.WritableFont;
Import Jxl.write.WritableSheet;
Import Jxl.write.WritableWorkbook;
public class Jxlexample {

/**
*
* @author Smart *
*/
public static void Main (string[] args) {
Prepare to set the title of an Excel worksheet
String[] title = {"Number", "Product name", "Product price", "Product Quantity", "Production Date", "origin", "whether export"};
try {
Get Start time
Long start = System.currenttimemillis ();
The path of the output Excel
String FilePath = "C:\\test.xls";
Create an Excel workbook
Writableworkbook WWB;
Create a new JXL file, which is generated under C disk Test.xls
OutputStream OS = new FileOutputStream (FilePath);
Wwb=workbook.createworkbook (OS);
Add the first worksheet and set the first sheet name
Writablesheet sheet = wwb.createsheet ("Product List", 0);
Label label;
for (int i=0;i<title.length;i++) {
Label = new label (I,0,title[i]);
Add a defined cell to a worksheet
Sheet.addcell (label);
}
/*
* Save the numbers to the cell, you need to use Jxl.write.Number
* Must use its full path, otherwise an error will occur
* */
Fill Product number
Jxl.write.Number number = new Jxl.write.Number (0,1,20071001);
Sheet.addcell (number);
Populate Product Name
Label = new label (1, 1, "Golden Pigeon melon Seeds");
Sheet.addcell (label);
/*
* Defines the public format for displaying amounts
* JXL will automatically be rounded
* For example 2.456 will be formatted as 2.46, 2.454 will be formatted as 2.45
* */
Jxl.write.NumberFormat NF = new Jxl.write.NumberFormat ("#.##");
Jxl.write.WritableCellFormat WCF = new Jxl.write.WritableCellFormat (NF);
Fill the price of the product
Jxl.write.Number NB = new Jxl.write.Number (2,1,2.45,WCF);
Sheet.addcell (NB);
Number of products filled
Jxl.write.Number numb = new Jxl.write.Number (3,1,200);
Sheet.addcell (numb);
/*
* Define a common format for displaying dates
* such as: Yyyy-mm-dd hh:mm
* */
SimpleDateFormat SDF = new SimpleDateFormat ("Yyyy-mm-dd");
String newdate = Sdf.format (New Date ());
Fill production Date
Label = new label (4,1,newdate);
Sheet.addcell (label);
Fill the origin
Label = new label (5,1, "Xian, Shaanxi");
Sheet.addcell (label);
/*
* Display Boolean values
* */
jxl.write.Boolean bool = new Jxl.write.Boolean (6,1,true);
Sheet.addcell (BOOL);
/*
* Merge Cells
* Through writablesheet.mergecells (int x,int y,int m,int n);
* Indicates that it will be merged from column x+1, y+1 row to m+1 column, n+1 row
*
* */
Sheet.mergecells (0,3,2,3);
Label = new label (0,3, "merged three cells");
Sheet.addcell (label);
/*
*
* Define common font formats
* By getting the style of a font as a template
* First to get the first sheet through Web.getsheet (0)
* Then get the second column of the first sheet, the first line is the font of "product name"
* */
CellFormat CF = Wwb.getsheet (0). Getcell (1, 0). Getcellformat ();
Writablecellformat WC = new Writablecellformat ();
Set Center
Wc.setalignment (Alignment.centre);
Set Border lines
Wc.setborder (Border.all, Borderlinestyle.thin);
Set the background color of a cell
Wc.setbackground (Jxl.format.Colour.RED);
Label = new label (1, 5, "Font", WC);
Sheet.addcell (label);
Set font
Writablefont wfont=new Writablefont (Writablefont.createfont ("Official script"), 20);
Writablecellformat font = new Writablecellformat (Wfont);
Label = new label (2,6, "script", font);
Sheet.addcell (label);
Write Data
Wwb.write ();
Close File
Wwb.close ();
Long end = System.currenttimemillis ();
SYSTEM.OUT.PRINTLN ("----The total time to complete this operation is:" + (End-start)/1000);
} catch (Exception e) {
SYSTEM.OUT.PRINTLN ("---abnormal---");
E.printstacktrace ();
}
}

}
3.2 Example Two

Package com.test;
Import Java.io.File;
Import java.io.IOException;
Import JXL. Cell;
Import JXL. Sheet;
Import JXL. Workbook;
Import Jxl.format.Border;
Import Jxl.format.BorderLineStyle;
Import Jxl.format.Colour;
Import jxl.read.biff.BiffException;
Import Jxl.write.Label;
Import Jxl.write.WritableCellFormat;
Import Jxl.write.WritableFont;
Import Jxl.write.WritableSheet;
Import Jxl.write.WritableWorkbook;
Import jxl.write.WriteException;
Import jxl.write.biff.RowsExceededException;

public class Jexcelsample {
/**
* Write Excel file
*
*/
public void Writeexc (File filename) {
Writableworkbook WWB = null;
Try
{
WWB = Workbook.createworkbook (filename);
}
catch (Exception e) {
E.printstacktrace ();
}

Create an Excel worksheet
Writablesheet ws = Wwb.createsheet ("Contacts", 0);//Create Sheet
try {
Ws.mergecells (0, 0, 2, 1);//merge cells (left column, left row, right column, right row) from 1th row 1th column to 2nd row 3rd column
Label Header = new label (0, 0, "Contacts (191026 shifts)", getheader ());
Ws.addcell (header);//write Header
Label L = new label (0, 2, "name", GetTitle ());//Line 3rd
Ws.addcell (l);
L = new Label (1, 2, "Phone", GetTitle ());
Ws.addcell (l);
L = new Label (2, 2, "Address", GetTitle ());
Ws.addcell (l);
L = new Label (0, 3, "small Wish", Getnormolcell ());//Line 4th
Ws.addcell (l);
L = new Label (1, 3, "1314***0974", Getnormolcell ());
Ws.addcell (l);
L = new Label (2, 3, "Wuhan Wuchang", Getnormolcell ());
Ws.addcell (l);
L = new Label (0, 4, "Xiao Shi", Getnormolcell ());//Line 5th
Ws.addcell (l);
L = new Label (1, 4, "1347***5057", Getnormolcell ());
Ws.addcell (l);
L = new Label (2, 4, "Wuhan Wuchang", Getnormolcell ());
Ws.addcell (l);
Ws.setcolumnview (0,20);//Set column width
Ws.setcolumnview (1,20);
Ws.setcolumnview (2,40);
Ws.setrowview (0,400);//Set row height
Ws.setrowview (1,400);
Ws.setrowview (2,500);
Ws.setrowview (3,500);
Ws.setrowview (4,500);
} catch (Rowsexceededexception E1) {
E1.printstacktrace ();
} catch (WriteException E1) {
E1.printstacktrace ();
}

Output stream
try {
Wwb.write ();
} catch (IOException ex) {
TODO automatically generates catch blocks
Ex.printstacktrace ();
}
Close the stream
try {
Wwb.close ();
} catch (WriteException ex) {
TODO automatically generates catch blocks
Ex.printstacktrace ();
} catch (IOException ex) {
TODO automatically generates catch blocks
Ex.printstacktrace ();
}
Outstream.close ();
System.out.println ("Write succeeded! \ n ");
}

public void Readexc (File filename) throws Biffexception, ioexception{
Workbook WB = workbook.getworkbook (filename);
Sheet s = wb.getsheet (0);//1th Sheet
Cell c = null;
int row = S.getrows ();//total number of rows
int col = S.getcolumns ();//Total number of columns
for (int i=0;i<row;i++) {
for (int j=0;j<col;j++) {
c = S.getcell (j,i);
System.out.print (c.getcontents () + "");
}
System.out.println ();
}
}

/**
* Set the style of the header
* @return
*/
public static Writablecellformat GetHeader () {
Writablefont font = new Writablefont (writablefont.times, writablefont.bold);//define Font
try {
Font.setcolour (Colour.blue);//Blue Font
} catch (WriteException E1) {
TODO automatically generates catch blocks
E1.printstacktrace ();
}
Writablecellformat format = new Writablecellformat (font);
try {
Format.setalignment (Jxl.format.Alignment.CENTRE);//center Around
Format.setverticalalignment (Jxl.format.VerticalAlignment.CENTRE);//Up/down Center
Format.setborder (border.all,borderlinestyle.thin,colour.black);//Black border
Format.setbackground (Colour.yellow);//Yellow background
} catch (WriteException e) {
TODO automatically generates catch blocks
E.printstacktrace ();
}
return format;
}

/**
* Set heading Style
* @return
*/
public static Writablecellformat GetTitle () {
Writablefont font = new Writablefont (writablefont.times, 14);
try {
Font.setcolour (Colour.blue);//Blue Font
} catch (WriteException E1) {
TODO automatically generates catch blocks
E1.printstacktrace ();
}
Writablecellformat format = new Writablecellformat (font);

try {
Format.setalignment (Jxl.format.Alignment.CENTRE);
Format.setverticalalignment (Jxl.format.VerticalAlignment.CENTRE);
Format.setborder (Border.all,borderlinestyle.thin,colour.black);
} catch (WriteException e) {
TODO automatically generates catch blocks
E.printstacktrace ();
}
return format;
}

/**
* Set Other cell styles
* @return
*/
public static Writablecellformat Getnormolcell () {//12th font, center up or down, with black border
Writablefont font = new Writablefont (Writablefont.times, 12);
Writablecellformat format = new Writablecellformat (font);
try {
Format.setalignment (Jxl.format.Alignment.CENTRE);
Format.setverticalalignment (Jxl.format.VerticalAlignment.CENTRE);
Format.setborder (Border.all,borderlinestyle.thin,colour.black);
} catch (WriteException e) {
TODO automatically generates catch blocks
E.printstacktrace ();
}
return format;
}

public static void Main (string[] args) throws IOException, biffexception{
Jexcelsample js = new Jexcelsample ();
File F = new file ("D:\\address.xls");
F.createnewfile ();
Js.writeexc (f);
Js.readexc (f);
}
}
The resulting Excel table is as follows:


4 JXL Common API
4.1.1 1, the Workbook class provides methods
int getnumberofsheets () Gets the total number of worksheets
Sheet[] Getsheets () gets the array-type worksheet
Sheet Getsheet (String name);//Work table with this corresponding name
4.1.2 2, Sheet interface provides the method
String GetName () Gets the name of the worksheet
int GetColumns () Gets the total number of columns contained in the sheet table
Cell[] GetColumn (int column) gets all the cells of a column,
Returns an array of cell objects
int getRows () Gets the total number of rows contained in the sheet table
Cell[] GetRow (int row) gets all the cells of a row and returns an array of cell objects
Cell Getcell (int column, int row) Gets the object reference for the specified cell, noting that it has two parameters, the first is the number of columns, and the second is the number of rows, which is somewhat different from the usual row and column combinations
Writablesheet.setrowview (int i,int height); Specify the height of line i+1
Writablesheet.setcolumnview (int i,int width); Specify the width of column i+1

Excerpt: Detailed use of the JAVA JXL API