Java operation of Excel
Bromon original copyright
MS's spreadsheet (Excel) is an important member of Office, which is a common format for saving statistics. As a office document, the exchange of electronic documents to be involved, Excel is a very common file format, printing and management in the enterprise. In a Java application, part of the data generates an Excel format is an important means of seamless connection with other systems.
In the open source world, there are two more affected APIs available, one is POI, one is Jexcelapi. Among them, Jexcelapi is a Korean programmer, although there is no POI's low bloodline, but in the process of the author, it feels simple and convenient, and it is very good for Chinese support, and the function is also relatively powerful. Its download address is: http://www.andykhan.com/jexcelapi/ The current highest version is 2.4. The features of the author's website have the following description:
● All versions of Excel 95-2000 ● Generate Excel 2000 Standard Format ● Support font, numbers, date operation ● Can modify cell properties ● Support images and charts
It should be said that the above functions have been able to generally meet our needs. The most critical is that this API is pure Java, does not rely on the Windows system, even if it is running under Linux, it can also process the Excel file correctly. It is also necessary to explain that this API supports the support of graphics and charts, and only recognizes the PNG format.
Build an environment
Unpack the downloaded file, get JXL.jar, put it in ClassPath, and complete it.
Basic operation
First, create a file
It is proposed to generate an Excel file called "Test Data .xls", where the first worksheet is named "First Page", the rough effect is as follows:
Createxls.java: // Generate Excel's class import java.io. *; import jxl. *; Import jxl.write. *;
Public class createxls {public static void main (string args []) {Try {// Open file WritableWorkbook book = workbook.createworkbook (new file ("test.xls")); // Born named "first page" Worksheet, parameter 0 means this is the first page Writablesheet Sheet = BOOK.CREATESHEET ("First Page", 0); // In the constructor of the Label object, the list element is the first row (0, " 0) // and cell content is Test label label = new label (0,0, "test");
// Tim the defined cell to Sheet.Addcell (Label) in the worksheet;
/ * Generate a single-saving cell must use Number's full packet path, otherwise a speech-missed cell position is the second column, the first line, the value is 789.123 * / jxl.write.Number Number = new jxl.write. Number (1,0,789.123); sheet.addcell (number);
// Write the data and close the file book.write (); book.close ();
} catch (exception e) {system.out.println (e);}}}
After compiling execution, an Excel file will be generated in the current location.
Third, read the file
Take a simple reading operation with the Excel file we have created, make a simple read operation, the program code is as follows: // reads Excel's class import java.io. *; importxl. *; Public class readxls {public static void main String args []) {Try {workbook book = workbook.getworkbook (new file ("Test .xls")); // Get the first worksheet object Sheet Sheet = BOOK.GETSHEET (0);
// Get the cell Cell Cell1 = Sheet.Getcell (0); string result = cell1.getContents (); system.out.println (result);
Book.close ();
} catch (exception e) {system.out.println (e);}}}
Program execution results: TEST
Fourth, modify the file
Using JEXcelapi to modify existing Excel files, when modifying the Excel file, other operations and creation of Excel are the same in addition to opening the file. The following example is to add a worksheet to the Excel file we have generated:
// Modify the class of Excel, add a worksheet Import java.io. *; import jxl. *; Import jxl.write. *;
Public class updatexls {public static void main (string args []) {Try {// Excel gets fileworkbook wb = workbook.getworkbook (new file ("test.xls")); // Open a copy of a file, and specify Data Write Back to Original File WritableWorkbook Book = Workbook.createWorkbook (New File "), WB); // Add a worksheet Writablesheet Sheet = Book.createsheet (" Page II, 1);
Sheet.Addcell (New Label (0, "Test Data"); book.write (); book.close ();} catch (exception e) {system.out.println (e); }}}
The execution results are as shown:
Advanced operation
First, data formatting
Does not involving complex data types in Excel, which can be more good to process strings, numbers, and dates to meet the general applications.
1, string formatting
The format of the string involves elements such as fonts, thickness, and font numbers, which are primarily responsible by WritableFont and WritablecellFormat classes. Suppose we use the following statement when generating a string-containing cell, for convenient narrative, we have added numbers for each line of command:
Writablefont font1 = new WritableFont (Writablefont.Times, 16, WritableFont.bold); 1
WritablecellFormat Format1 = New WritableCellFormat (font1); 2
Label label = new label (0, 0, "Data 4 Test", Format1) 3
Where 1 specifies the string format: the font is Times, the font size 16, bold display. Writablefont has a very rich structure for use in different situations, and there is a detailed list in JEXCELAPI's Java-DOC, which is no longer listed here. 2 The code uses the WritablecellFormat class. This class is very important. It can specify the various properties of the cell, and there will be more descriptions in the following cell format.
3 The structure of the Label class is used, and the string is assigned to the format.
In the WritablecellFormat class, there is a very important way to specify the data alignment, such as for our above instance, you can specify:
/ / Specify horizontally aligned as home format1.setAlignment (jxl.format.Alignment.centre);
/ / Specify the vertical alignment as the hidden format1.setVerticalAlaLNment (jxl.format.vertageAlignment.centre);
Second, cell operation
Part of Excel is the operation of cells, such as row, column width, cell merge, etc., Fortunately, Jexcelapi provides these support. These operations are relatively simple, and only the associated API is described below.
1, combined unit
Writablesheet.mergecells (int M, int N, int p, int tent Q);
The role is from (m, n) to (p, q) unit all merge, such as Writablesheet Sheet = BOOK.CREATESHEET ("First Page", 0);
// Combine all the cells of the first line of the first line to the first line of the first line Sheet.Mergecells (0, 0, 5, 0);
The combination can either transverse or longitudinal. The merged cells cannot be merged again, otherwise an exception is triggered.
2, travel high and column width
Writablesheet.setrowView (int I, int hotht);
The role is to specify the height of the i 1 line, such as:
// Set the height of the first line to 200 Sheet.setrowView (0,200);
Writablesheet.SetColumnView (int I, int width);
The role is to specify the width of the i 1 column, such as:
// Set the width of the first column to 30 Sheet.SetColumnView (0, 30);
Jexcelapi has other features, such as inserting images, etc., this is no longer introduced, readers can explore themselves.
Sichuan Mianyang original published in "Development Master" 0405
