[ Java 代碼範本 ] JExcel - Writing Spreadsheets

Preface: 
This section describes how to write out simple spreadsheet data without any formatting information via open source library JExcelApi, such as fonts or decimal places. Similarly to reading a spreadsheet, the first step is to create a writable workbook using the factory method on the Workbook class. 

view plaincopy to clipboardprint?

1. import java.io.File;   
2. import java.util.Date;   
3. import jxl.*;   
4. import jxl.write.*;   
5.   
6. ...   
7.   
8. WritableWorkbook workbook = Workbook.createWorkbook(new File("output.xls"));  

This creates the workbook object. The generated file will be located in the current working directory and will be called "output.xls". The API can also be used to send the workbook directly to an output stream eg. from a web server to the user's browser. If the HTTP header is set correctly, then this will launch Excel and display the generated spreadsheet. 

The next stage is to create sheets for the workbook. Again, this is a factory method, which takes the name of the sheet and the position it will occupy in the workbook. The code fragment below creates a sheet called "First Sheet" at the first position: 

view plaincopy to clipboardprint?

1. WritableSheet sheet = workbook.createSheet("First Sheet", 0);  

Now all that remains is to add the cells into the worksheet. This is simply a matter of instantiating cell objects and adding them to the sheet. The following code fragment puts a label in cell A3, and the number 3.14159 in cell D5. 

view plaincopy to clipboardprint?

1. Label label = new Label(0, 2, "A label record");   
2. sheet.addCell(label);   
3.   
4. Number number = new Number(3, 4, 3.1459);   
5. sheet.addCell(number);  

There are a couple of points to note here. Firstly, the cell's location in the sheet is specified as part of the constructor information. Once created, it is not possible to change a cell's location, although the cell's contents may be altered. The other point to note is that the cell's location is specified as (column, row). Both are zero indexed integer values - A1 being represented by (0,0), B1 by (1,0), A2 by (0,1) and so on. 

Once you have finished adding sheets and cells to the workbook, you call write() on the workbook, and then close the file. This final step generates the output file (output.xls in this case) which may be read by Excel. If you call close() without calling write() first, a completely empty file will be generated. 

view plaincopy to clipboardprint?

1. ...   
2. // All sheets and cells added. Now write out the workbook   
3. workbook.write();   
4. workbook.close();  

 

Adding Format Information: 
The previous section illustrates the fundamentals of generating an Excel compatible spreadsheet using the JExcelApi. However, as it stands Excel will render the data in the default font, and will display the numbers to 3 decimal places. In order to supply formatting information to Excel, we must make use of the overloaded constructor, which takes an additional object containing the cell's formatting information (both the font and the style). 

The code fragment below illustrates creating a label cell for an arial 10 point font. 

view plaincopy to clipboardprint?

1. // Create a cell format for Arial 10 point font   
2. WritableFont arial10font = new WritableFont(WritableFont.ARIAL, 10);   
3. WritableCellFormat arial10format = new WritableCellFormat (arial10font);   
4.   
5. // Create the label, specifying content and format   
6. Label label2 = new Label(1,0, "Arial 10 point label", arial10format);   
7. sheet.addCell(label2);  

Cell formats objects are shared, so many cells may use the same format object, eg. 

view plaincopy to clipboardprint?

1. Label label3 = new Label(2, 0, "Another Arial 10 point label", arial10format);   
2. sheet.addCell(label3);  

This creates another label, with the same format, in cell C1. 

Because cell formats are shared, it is not possible to change the contents of a cell format object. (If this were permitted, then changing the contents of the object could have unforeseen repurcussions on the look of the rest of the workbook). In order to change the way a particular cell is displayed, the API does allow you to assign a new format to an individual cell. 

The constructors for the WritableFont object have many overloads. By way of example, the code fragment below creates a label in 16 point Times, bold italic and assigns it to position D1. 

view plaincopy to clipboardprint?

1. // Create a cell format for Times 16, bold and italic   
2. WritableFont times16font = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, true);   
3. WritableCellFormat times16format = new WritableCellFormat (times16font);   
4.   
5. // Create the label, specifying content and format   
6. Label label4 = new Label(3,0, "Times 16 bold italic label", times16format);   
7. sheet.addCell(label4);  

 

Formatting Numbers: 
Number formatting information may be passed to the cell format object by a similar mechanism to that described for fonts. A variety of predefined number formats are defined statically. These may be used to format numerical values as follows: 

view plaincopy to clipboardprint?

1. WritableCellFormat integerFormat = new WritableCellFormat (NumberFormats.INTEGER);   
2. Number number2 = new Number(0, 4, 3.141519, integerFormat);   
3. sheet.addCell(number2);   
4.   
5. WritableCellFormat floatFormat = new WritableCellFormat (NumberFormats.FLOAT);   
6. Number number3 = new Number(1, 4, 3.141519, floatFormat);   
7. sheet.addCell(number3);  

The above code inserts the value 3.14159 into cells A5 and B5, using the preset integer and floating points format respectively. When Excel renders these cells, A5 will display as "3" and B5 will display as "3.14", even though both cells contain the same floating point value. 

It's possible for a user to define their own number formats, by passing in a number format string. The string passed in should be in the same format as that used by thejava.text.DecimalFormat class. To format a number to display up to five decimal places in cell C5, the following code fragment may be used: 

view plaincopy to clipboardprint?

1. NumberFormat fivedps = new NumberFormat("#.#####");   
2. WritableCellFormat fivedpsFormat = new WritableCellFormat(fivedps);   
3. Number number4 = new Number(2, 4, 3.141519, fivedpsFormat);   
4. sheet.addCell(number4);  

It is, of course, also possible to specify font information as well eg. to display the same value in the 16 point times bold font defined earlier we can write: 

view plaincopy to clipboardprint?

1. WritableCellFormat fivedpsFontFormat = new WritableCellFormat (times16font, fivedps);   
2. Number number5 = new Number(3, 4, 3.141519, fivedpsFontFormat);   
3. sheet.addCell(number5);  

 

Formatting Dates: 
Dates are handled similarly to numbers, taking in a format compatible with that used by the java.text.SimpleDateFormat class. In addition, several predefined date formats are specified in the jxl.write.DateFormat class. 

As a brief example, the below code fragment illustrates placing the current date and time in cell A7 using a custom format: 

view plaincopy to clipboardprint?

1. // Get the current date and time from the Calendar object   
2. Date now = Calendar.getInstance().getTime();   
3. DateFormat customDateFormat = new DateFormat ("dd MMM yyyy hh:mm:ss");   
4. WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);   
5. DateTime dateCell = new DateTime(0, 6, now, dateFormat);   
6. sheet.addCell(dateCell);   

 

As with numbers, font information may be used to display the date text by using the overloaded constructors on WritableCellFormat. 


For a more extensive example of writing spreadsheets, the demonstration program Write.java should be studied. In addition to the functionality described above, this program tests out a variety of cell, formatting and font options, as well as displaying cells with different background and foreground colours, shading and boundaries.