JXL exporting Excel code in Java

JXL is a Korean written by the Java operation of Excel Tools, in the open source world, there are two sets of more influential APIs to use, one is a poi, and the other is JEXCELAPI. Where the function is relatively weak relative to the POI. However, JEXCELAPI support for Chinese is very good, the API is pure Java, and does not rely on the Windows system, 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. Use the following: Build the environment will download the file unpacked, get Jxl.jar, put into the classpath, the installation is complete.

First, create a file

To generate an Excel file named "Test.xls", where the first worksheet is named "first page" with the following general effect:

2. Package COM.LUQIN.JXL;
6. Import Java.io.File;
10. Import JXL. Workbook;
12. Import Jxl.format.UnderlineStyle;
14. Import Jxl.write.Font;
16. Import Jxl.write.Label;
18. Import Jxl.write.WritableFont;
20. Import Jxl.write.WritableSheet;
22. Import Jxl.write.WritableWorkbook;
26. /**
28. * Create a simple instance of Excel
30. *
32. * @author Luqin * @version
34. * @time 2012-6-20 11:12:02
36. *
38. */
40. Public class Createexcel {
42. public static void Main (string[] args) {
44. String excelname = "Table.xls";
46. try {
48. File Excelfile = new File (Excelname);
50. //If the file exists, delete it
52. if (excelfile.exists ())
54. Excelfile.delete ();
56. //Open File
58. Writableworkbook book = Workbook.createworkbook (Excelfile);
60. //Generate a worksheet named "first page", parameter 0 indicates this is the first page
62. Writablesheet sheet = book.createsheet ("First page", 0);
64. //Merge cells
66. Sheet.mergecells (5, 5, 6, 6);
68. //Text style
70. Jxl.write.WritableFont WFC = new Jxl.write.WritableFont (
72. Writablefont.arial, Ten, Writablefont.no_bold, false,
74. Underlinestyle.no_underline, Jxl.format.Colour.RED);
78. Jxl.write.WritableCellFormat WCFFC = new Jxl.write.WritableCellFormat (
80. WFC);
84. //Set cell style
86. Wcffc.setbackground (JXL.FORMAT.COLOUR.GRAY_25); //Cell color
88. Wcffc.setalignment (Jxl.format.Alignment.CENTRE); //Cell Center
92. //In the construction child of the label object, the cell position is the first column, the first row (0,0)
94. //And the contents of the cell are
96. Label label = New Label (0, 0, "Head1", WCFFC);
100. //Add a defined cell to the worksheet
102. Sheet.addcell (label);
106. /**//* 
108. * Generate a cell that holds numbers must use the full package path of number, otherwise there is a syntax ambiguity cell position is the second column, the first row, the value is 789.123
110. */
112. Jxl.write.Number number = new Jxl.write.Number (1, 0, 555.12541);
114. Sheet.addcell (number);
118. //write data and close file
120. Book.write ();
122. Book.close ();
124. System.out.println ("Excel created successfully");
126. } catch (Exception e) {
128. System.out.println (e);
130. }
132. }
136. }

After the compilation is executed, an Excel file is generated.

Third, read the file

Take the Excel file we just created as an example, do a simple read operation, the program code is as follows:

2. Package test;
6. //Read the class of Excel
8. import Java.io.File;
12. import JXL.  Cell;
14. import JXL.  Sheet;
16. import JXL.  Workbook;
20. public class Readexcel {
22. public static void Main (String args[]) {
24. try {
26. Workbook book = Workbook.getworkbook ( new File ( "Test.xls"));
28. //Get first Sheet object
30. Sheet Sheet = Book.getsheet ( 0);
32. //Get the first row of cells in the first column
34. Cell cell1 = Sheet.getcell ( 0, 0);
36. String result = Cell1.getcontents ();
38. SYSTEM.OUT.PRINTLN (result);
40. Book.close ();
42. } catch (Exception e) {
44. System.out.println (e);
46. }
48. }
50. }

Program execution Result: test

Iv. Modification of documents
Using JEXCELAPI can modify an existing Excel file, when you modify the Excel file, in addition to opening the file in a different way,
The other actions are the same as creating Excel. The following example adds a worksheet to the Excel file that we have generated:

2. Package test;
6. Import Java.io.File;
10. Import JXL. Workbook;
12. Import Jxl.write.Label;
14. Import Jxl.write.WritableSheet;
16. Import Jxl.write.WritableWorkbook;
20. Public class Updateexcel {
22. public static void Main (String args[]) {
24. try {
26. //Excel Get File
28. Workbook wb = Workbook.getworkbook ( new File ( "Test.xls"));
30. //Open a copy of a file and specify the data to write back to the original file
32. Writableworkbook book = Workbook.createworkbook ( new File ( "Test.xls"),
34. WB);
36. //Add a worksheet
38. Writablesheet sheet = book.createsheet ( "second page", 1);
40. Sheet.addcell ( new Label ( 0, 0, "test data for the second page"));
42. Book.write ();
44. Book.close ();
46. } catch (Exception e) {
48. System.out.println (e);
50. }
52. }

Other operations

First, data formatting

There are no complex data types involved in Excel, and the ability to handle strings, numbers, and dates is a good fit for general applications.

1. String formatting

The formatting of strings involves elements such as font, weight, and font size, which are mainly composed of Writablefont and
Writablecellformat class to be responsible. Suppose we use the following statement when generating a cell containing a string,
For the convenience of narration, we add a number for each line of the command:

1. Writablefont font1 =
2. New Writablefont (Writablefont.times, writablefont.bold); ①
4. Writablecellformat format1 = new Writablecellformat (font1); ②
6. Label label = New Label ( 0, 0, "Data 4 test", FORMAT1) ③

Where ① specifies the string format: times, font size 16, bold display. Writablefont has a very rich
constructors, for different situations, have a detailed list of Jexcelapi's Java-doc, which are no longer listed here.

The ② code uses the Writablecellformat class, which is very important, which allows you to specify a variety of cells
property, there are more descriptions in subsequent cell formatting.

The ③ uses the constructor of the label class, specifying that the string is given that format.

In the Writablecellformat class, there is also an important way to specify the alignment of the data, such as for our
The above instance, you can specify:

1. Assign Horizontal alignment to center
2. Format1.setalignment (Jxl.format.Alignment.CENTRE);
4. //Assign vertical alignment to center
5. Format1.setverticalalignment (Jxl.format.VerticalAlignment.CENTRE);

Second, cell operation

An important part of Excel is the operation of cells, such as row height, column width, cell merging, etc., fortunately Jexcelapi
These support are provided. These operations are relatively straightforward and only the relevant APIs are described below.

1. Merging cells

1. Writablesheet.mergecells (int m, int n, int p, int q);
3. The function is to merge all the cells from (M,n) to (P,Q), such as:
4. Writablesheet sheet = book.createsheet ("first page", 0);
6. Merge all cells from the first row to the first row of column sixth
7. Sheet.mergecells (0, 0, 5, 0);

A merge can be either horizontal or vertical. Merged cells cannot be merged again, otherwise an exception is triggered.

2, Row height and column width

Writablesheet.setrowview (int i,int height);

The function is to specify 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);

The function is to specify the width of column i+1, for example:

Set the width of the first column to 30
Sheet.setcolumnview (0,30);

JEXCELAPI also has other features, such as inserting images, which are not introduced here and can be explored by the reader.

Where: If you are reading an Excel, you need to know how many rows and how many columns it has, such as the following:

Workbook book = Workbook.getworkbook (New File ("Test 1.xls"));
Get the first sheet object
Sheet Sheet = book.getsheet (0);
Get the first row of cells in the first column
int columnum = Sheet.getcolumns ();//Get column number
int rownum = Sheet.getrows ();//Gets the number of rows
System.out.println (Columnum);
System.out.println (rownum);
for (int i = 0; i < rownum; i++)//loop for Read and write
{
            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 ();

Exporting Excel code in Java with JXL