
Examples  Fibonacci
The Fibonacci example demonstrates some formatting of the workbook with the application of
styles to cells and rows. The CellPointer comes into view. And relative references,
the things you most likely need to construct formulas, are discussed.
This example constructs a workbook with one worksheet. On it a simple table with four columns, showing Fibonacci numbers and their ratio.
Fibonacci class and sourcefile can be found in the directory examples ,
provided with the xelemdistribution.
See: download.
import java.util.Collection; import java.util.Iterator; import nl.fountain.xelem.Address; import nl.fountain.xelem.CellPointer; import nl.fountain.xelem.XSerializer; import nl.fountain.xelem.XelemException; import nl.fountain.xelem.excel.Cell; import nl.fountain.xelem.excel.Workbook; import nl.fountain.xelem.excel.Worksheet; import nl.fountain.xelem.excel.ss.SSCell; import nl.fountain.xelem.excel.ss.XLWorkbook; public class Fibonacci { public static void main(String[] args) { Workbook wb = createTheWorkbook(); try { new XSerializer().serialize(wb); printWarnings(wb); } catch (XelemException e) { e.printStackTrace(); } } private static Workbook createTheWorkbook() { Workbook wb = new XLWorkbook("Fibonacci"); Worksheet sheet = wb.addSheet("ratio of Fibonacci numbers"); sheet.addCell("Ratio of Fibonacci numbers", "title"); // up to row 11 we want a white background for (int i = 1; i < 11; i++) { sheet.getRowAt(i).setStyleID("bg_white"); } // add a heading and construct the formulas CellPointer cp = sheet.getCellPointer(); cp.moveTo(10, 1); Address adrF1 = cp.getAddress(); sheet.addCell("f1", "table_heading"); Address adrF2 = cp.getAddress(); sheet.addCell("f2", "table_heading"); String formula1 = "=" + cp.getRefTo(adrF1) + "/" + cp.getRefTo(adrF2); sheet.addCell("ratio f1/f2", "table_heading"); String formula2 = "=" + cp.getRefTo(adrF2) + "/" + cp.getRefTo(adrF1); sheet.addCell("ratio f2/f1", "table_heading"); // put the relative formulas in cells Cell formulaCell1 = new SSCell(); formulaCell1.setFormula(formula1); Cell formulaCell2 = new SSCell(); formulaCell2.setFormula(formula2); // do the Fibonacci int f1 = 1; int f2 = 1; int f3; while (f1 < 1000000) { cp.moveCRLF(); sheet.addCell(f1); sheet.addCell(f2); sheet.addCell(formulaCell1); sheet.addCell(formulaCell2); f3 = f1 + f2; f1 = f2; f2 = f3; } return wb; } private static void printWarnings(Workbook wb) { Collection warnings = wb.getWarnings(); System.out.println("Created '" + wb.getFileName() + "' with " + warnings.size() + (warnings.size() == 1 ? " warning." : " warnings.")); for (Iterator iter = warnings.iterator(); iter.hasNext();) { System.out.println(iter.next()); } } } The workbook created by Fibonacci.java can be seen here. And this is the same file seen in xmlview. General processIn thecreateTheWorkbook() method a new workbook is constructed.
A worksheet is added and given the name "ratio of Fibonacci numbers".
This is also the data that is set upon the first cell of this worksheet and
the cell is given the styleID "title". The first 10 rows get a background
color "bg_white". On row 10 of the worksheet a columnheading
is constructed and we use the temporary position of the CellPointer to get addresses
and construct relative formulas. In the whileloop Fibonacci numbers and
ratio's are added to consecutive rows of the worksheet.
Finally, in the printWarnings(Workbook) method we will see
if everything went fine.
StylesThe formatting of an xml spreadsheet is done with styleID's. These ID's can be applied to the elements table, column, row and cell. And so it is with xelem. You set a styleID on an instance of SSTable, SSColumn, SSRow and/or SSCell. The definition of the styles you use should be in a file that is parsed by the XFactory during the creation of a document. There is a discussion on this issue in the overview of the xelem API.FormulasUnder// add a heading and construct the formulas we first initiate a variable to point to the CellPointer of the worksheet (cp). CellPointer is a subclass of Address and is, so to speak, a movable address. We move it to row 10, column 1 and then we ask it's Address: Address adrF1 = cp.getAddress();We must do this before we add a cell to the CellPointer's momentary position, because it will be off to the next position after the addCellcall. We also get the address of the position where we'll place the second Fibonacci number and finally we construct two formulas: String formula1 = "=" + cp.getRefTo(adrF1) + "/" + cp.getRefTo(adrF2);That's one of them; the other formula is constructed in a similar way. What we have then is two formulas:
Since the formulas refer to addresses on the same row, we can apply them to consecutive rows of the worksheet. Excel will calculate the ratio's. Offcourse, doing a little headbanging and typing in the formulas as Strings would do just as well: we know to what ranges the formulas refer. But there may be circumstances where addresses and references can only be known at runtime. Under such circumstances working with addresses and their getRefTomethods can be usefull.
12122004 