2009. 10. 14. 19:18
Programming/Java
Writing Spreadsheets
import java.io.File;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.
import java.util.Date;
import jxl.*;
import jxl.write.*;
...
WritableWorkbook workbook = Workbook.createWorkbook(new File("output.xls"));
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.
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.
Label label = new Label(0, 2, "A label record");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.
sheet.addCell(label);
Number number = new Number(3, 4, 3.1459);
sheet.addCell(number);
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.
...
// All sheets and cells added. Now write out the workbook
workbook.write();
workbook.close();
// Create a cell format for Arial 10 point fontCell formats objects are shared, so many cells may use the same format object, eg.
WritableFont arial10font = new WritableFont(WritableFont.ARIAL, 10);
WritableCellFormat arial10format = new WritableCellFormat (arial10font);
// Create the label, specifying content and format
Label label2 = new Label(1,0, "Arial 10 point label", arial10format);
sheet.addCell(label2);
Label label3 = new Label(2, 0, "Another Arial 10 point label", arial10format);This creates another label, with the same format, in cell C1.
sheet.addCell(label3);
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.
// Create a cell format for Times 16, bold and italic
WritableFont times16font = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, true);
WritableCellFormat times16format = new WritableCellFormat (times16font);
// Create the label, specifying content and format
Label label4 = new Label(3,0, "Times 16 bold italic label", times16format);
sheet.addCell(label4);
WritableCellFormat integerFormat = new WritableCellFormat (NumberFormats.INTEGER);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.
Number number2 = new Number(0, 4, 3.141519, integerFormat);
sheet.addCell(number2);
WritableCellFormat floatFormat = new WritableCellFormat (NumberFormats.FLOAT);
Number number3 = new Number(1, 4, 3.141519, floatFormat);
sheet.addCell(number3);
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 the java.text.DecimalFormat class. To format a number to display up to five decimal places in cell C5, the following code fragment may be used:
NumberFormat fivedps = new NumberFormat("#.#####");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
WritableCellFormat fivedpsFormat = new WritableCellFormat(fivedps);
Number number4 = new Number(2, 4, 3.141519, fivedpsFormat);
sheet.addCell(number4);
WritableCellFormat fivedpsFontFormat = new WritableCellFormat (times16font, fivedps);
Number number5 = new Number(3, 4, 3.141519, fivedpsFontFormat);
sheet.addCell(number5);
// Get the current date and time from the Calendar objectAs with numbers, font information may be used to display the date text by using the overloaded constructors on WritableCellFormat.
Date now = Calendar.getInstance().getTime();
DateFormat customDateFormat = new DateFormat ("dd MMM yyyy hh:mm:ss");
WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);
DateTime dateCell = new DateTime(0, 6, now, dateFormat);
sheet.addCell(dateCell);
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.
