Fibonacci

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 xelem-distribution. 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 xml-view.

General process

In the createTheWorkbook()-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 while-loop 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.

Styles

The 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.

Formulas

Under // 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 addCell-call.
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:

=RC[-2]/RC[-1] and =RC[-2]/RC[-3]

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 getRefTo-methods can be usefull.

12-12-2004