Save This Page
Home » JCraft » jxl » demo » [javadoc | source]
    1   /*********************************************************************
    2   *
    3   *      Copyright (C) 2001 Andrew Khan
    4   *
    5   * This library is free software; you can redistribute it and/or
    6   * modify it under the terms of the GNU Lesser General Public
    7   * License as published by the Free Software Foundation; either
    8   * version 2.1 of the License, or (at your option) any later version.
    9   *
   10   * This library is distributed in the hope that it will be useful,
   11   * but WITHOUT ANY WARRANTY; without even the implied warranty of
   12   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
   13   * Lesser General Public License for more details.
   14   *
   15   * You should have received a copy of the GNU Lesser General Public
   16   * License along with this library; if not, write to the Free Software
   17   * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
   18   ***************************************************************************/
   19   
   20   package jxl.demo;
   21   
   22   import java.io.File;
   23   import java.io.IOException;
   24   import java.net.MalformedURLException;
   25   import java.net.URL;
   26   import java.text.SimpleDateFormat;
   27   import java.util.ArrayList;
   28   import java.util.Calendar;
   29   import java.util.Date;
   30   import java.util.Locale;
   31   import java.util.TimeZone;
   32   
   33   import jxl.CellReferenceHelper;
   34   import jxl.CellView;
   35   import jxl.HeaderFooter;
   36   import jxl.Range;
   37   import jxl.Workbook;
   38   import jxl.WorkbookSettings;
   39   import jxl.format.Alignment;
   40   import jxl.format.Border;
   41   import jxl.format.BorderLineStyle;
   42   import jxl.format.Colour;
   43   import jxl.format.Orientation;
   44   import jxl.format.PageOrder;
   45   import jxl.format.PageOrientation;
   46   import jxl.format.PaperSize;
   47   import jxl.format.ScriptStyle;
   48   import jxl.format.UnderlineStyle;
   49   import jxl.write.Blank;
   50   import jxl.write.Boolean;
   51   import jxl.write.DateFormat;
   52   import jxl.write.DateFormats;
   53   import jxl.write.DateTime;
   54   import jxl.write.Formula;
   55   import jxl.write.Label;
   56   import jxl.write.Number;
   57   import jxl.write.NumberFormat;
   58   import jxl.write.NumberFormats;
   59   import jxl.write.WritableCellFeatures;
   60   import jxl.write.WritableCellFormat;
   61   import jxl.write.WritableFont;
   62   import jxl.write.WritableHyperlink;
   63   import jxl.write.WritableImage;
   64   import jxl.write.WritableSheet;
   65   import jxl.write.WritableWorkbook;
   66   import jxl.write.WriteException;
   67   
   68   
   69   /**
   70    * Demo class which writes a spreadsheet.  This demo illustrates most of the
   71    * features of the JExcelAPI, such as text, numbers, fonts, number formats and
   72    * date formats
   73    */
   74   public class Write
   75   {
   76     /**
   77      * The filename
   78      */
   79     private String filename;
   80   
   81     /**
   82      * The workbook
   83      */
   84     private WritableWorkbook workbook;
   85   
   86     /**
   87      * Constructor
   88      * 
   89      * @param fn 
   90      */
   91     public Write(String fn)
   92     {
   93       filename = fn;
   94     }
   95   
   96     /**
   97      * Uses the JExcelAPI to create a spreadsheet
   98      * 
   99      * @exception IOException
  100      * @exception WriteException
  101      */
  102     public void write() throws IOException, WriteException
  103     {
  104       WorkbookSettings ws = new WorkbookSettings();
  105       ws.setLocale(new Locale("en", "EN"));
  106       workbook = Workbook.createWorkbook(new File(filename), ws);
  107   
  108   
  109       WritableSheet s2 = workbook.createSheet("Number Formats", 0);
  110       WritableSheet s3 = workbook.createSheet("Date Formats", 1);
  111       WritableSheet s1 = workbook.createSheet("Label Formats", 2);
  112       WritableSheet s4 = workbook.createSheet("Borders", 3);
  113       WritableSheet s5 = workbook.createSheet("Labels", 4);
  114       WritableSheet s6 = workbook.createSheet("Formulas", 5);
  115       WritableSheet s7 = workbook.createSheet("Images", 6);
  116       //    WritableSheet s8 = workbook.createSheet
  117       //      ("'Illegal chars in name !*%^?': which exceeds max name length",7);
  118   
  119       // Modify the colour palette to bright red for the lime colour
  120       workbook.setColourRGB(Colour.LIME, 0xff, 0, 0);
  121   
  122       // Add a named range to the workbook
  123       workbook.addNameArea("namedrange", s4, 1, 11, 5, 14);
  124       workbook.addNameArea("validation_range", s1, 4, 65, 9, 65);
  125       workbook.addNameArea("formulavalue", s6, 1, 45, 1, 45);
  126   
  127       // Add a print area to the "Labels" sheet
  128       s5.getSettings().setPrintArea(4,4,15,35);
  129   
  130       writeLabelFormatSheet(s1);
  131       writeNumberFormatSheet(s2);
  132       writeDateFormatSheet(s3);
  133       writeBordersSheet(s4);
  134       writeLabelsSheet(s5);
  135       writeFormulaSheet(s6);
  136       writeImageSheet(s7);
  137   
  138       workbook.write();
  139       workbook.close();
  140     }
  141     
  142     /**
  143      * Writes out a sheet containing the various numerical formats
  144      * 
  145      * @param s 
  146      */
  147     private void writeNumberFormatSheet(WritableSheet s) throws WriteException
  148     {
  149       WritableCellFormat wrappedText = new WritableCellFormat
  150         (WritableWorkbook.ARIAL_10_PT);
  151       wrappedText.setWrap(true);
  152   
  153       s.setColumnView(0,20);
  154       s.setColumnView(4,20);
  155       s.setColumnView(5,20);
  156       s.setColumnView(6,20);
  157   
  158       // Floats
  159       Label l = new Label(0,0,"+/- Pi - default format", wrappedText);
  160       s.addCell(l);
  161   
  162       Number n = new Number(1,0,3.1415926535);
  163       s.addCell(n);
  164   
  165       n = new Number(2,0,-3.1415926535);
  166       s.addCell(n);
  167   
  168       l = new Label(0,1,"+/- Pi - integer format", wrappedText);
  169       s.addCell(l);
  170   
  171       WritableCellFormat cf1 = new WritableCellFormat(NumberFormats.INTEGER);
  172       n = new Number(1,1,3.1415926535,cf1);
  173       s.addCell(n);
  174   
  175       n = new Number(2,1,-3.1415926535, cf1);
  176       s.addCell(n);
  177   
  178       l = new Label(0,2,"+/- Pi - float 2dps", wrappedText);
  179       s.addCell(l);
  180   
  181       WritableCellFormat cf2 = new WritableCellFormat(NumberFormats.FLOAT);
  182       n = new Number(1,2,3.1415926535,cf2);
  183       s.addCell(n);
  184   
  185       n = new Number(2,2,-3.1415926535, cf2);
  186       s.addCell(n);
  187   
  188       l = new Label(0,3,"+/- Pi - custom 3dps", 
  189                     wrappedText);
  190       s.addCell(l);
  191   
  192       NumberFormat dp3 = new NumberFormat("#.###");
  193       WritableCellFormat dp3cell = new WritableCellFormat(dp3);
  194       n = new Number(1,3,3.1415926535,dp3cell);
  195       s.addCell(n);
  196   
  197       n = new Number(2,3,-3.1415926535, dp3cell);
  198       s.addCell(n);
  199   
  200       l = new Label(0,4,"+/- Pi - custom &3.14", 
  201                     wrappedText);
  202       s.addCell(l);
  203   
  204       NumberFormat pounddp2 = new NumberFormat("&#.00");
  205       WritableCellFormat pounddp2cell = new WritableCellFormat(pounddp2);
  206       n = new Number(1,4,3.1415926535,pounddp2cell);
  207       s.addCell(n);
  208   
  209       n = new Number(2,4,-3.1415926535, pounddp2cell);
  210       s.addCell(n);
  211   
  212       l = new Label(0,5,"+/- Pi - custom Text #.### Text", 
  213                     wrappedText);
  214       s.addCell(l);
  215   
  216       NumberFormat textdp4 = new NumberFormat("Text#.####Text");
  217       WritableCellFormat textdp4cell = new WritableCellFormat(textdp4);
  218       n = new Number(1,5,3.1415926535, textdp4cell);
  219       s.addCell(n);
  220   
  221       n = new Number(2,5,-3.1415926535, textdp4cell);
  222       s.addCell(n);
  223   
  224       // Integers
  225       l = new Label(4,0,"+/- Bilko default format");
  226       s.addCell(l);
  227       n = new Number(5, 0, 15042699);
  228       s.addCell(n);
  229       n = new Number(6, 0, -15042699);
  230       s.addCell(n);
  231   
  232       l = new Label(4,1,"+/- Bilko float format");
  233       s.addCell(l);
  234       WritableCellFormat cfi1 = new WritableCellFormat(NumberFormats.FLOAT);
  235       n = new Number(5, 1, 15042699, cfi1);
  236       s.addCell(n);
  237       n = new Number(6, 1, -15042699, cfi1);
  238       s.addCell(n);
  239   
  240       l = new Label(4,2,"+/- Thousands separator");
  241       s.addCell(l);
  242       WritableCellFormat cfi2 = new WritableCellFormat
  243         (NumberFormats.THOUSANDS_INTEGER);
  244       n = new Number(5, 2, 15042699,cfi2 );
  245       s.addCell(n);
  246       n = new Number(6, 2, -15042699, cfi2);
  247       s.addCell(n);
  248   
  249       l = new Label(4,3,"+/- Accounting red - added 0.01");
  250       s.addCell(l);
  251       WritableCellFormat cfi3 = new WritableCellFormat
  252         (NumberFormats.ACCOUNTING_RED_FLOAT);
  253       n = new Number(5, 3, 15042699.01, cfi3);
  254       s.addCell(n);
  255       n = new Number(6, 3, -15042699.01, cfi3);
  256       s.addCell(n);
  257   
  258       l = new Label(4,4,"+/- Percent");
  259       s.addCell(l);
  260       WritableCellFormat cfi4 = new WritableCellFormat
  261         (NumberFormats.PERCENT_INTEGER);
  262       n = new Number(5, 4, 15042699, cfi4);
  263       s.addCell(n);
  264       n = new Number(6, 4, -15042699, cfi4);
  265       s.addCell(n);
  266   
  267       l = new Label(4,5,"+/- Exponential - 2dps");
  268       s.addCell(l);
  269       WritableCellFormat cfi5 = new WritableCellFormat
  270         (NumberFormats.EXPONENTIAL);
  271       n = new Number(5, 5, 15042699, cfi5);
  272       s.addCell(n);
  273       n = new Number(6, 5, -15042699, cfi5);
  274       s.addCell(n);
  275   
  276       l = new Label(4,6,"+/- Custom exponentional - 3dps", wrappedText);
  277       s.addCell(l);
  278       NumberFormat edp3 = new NumberFormat("0.000E0");
  279       WritableCellFormat edp3Cell = new WritableCellFormat(edp3);
  280       n = new Number(5,6,15042699,edp3Cell);
  281       s.addCell(n);
  282       n = new Number(6,6,-15042699,edp3Cell);
  283       s.addCell(n);
  284   
  285       l = new Label(4, 7, "Custom neg brackets", wrappedText);
  286       s.addCell(l);
  287       NumberFormat negbracks = new NumberFormat("#,##0;(#,##0)");
  288       WritableCellFormat negbrackscell = new WritableCellFormat(negbracks);
  289       n = new Number(5,7, 15042699, negbrackscell);
  290       s.addCell(n);
  291       n = new Number(6,7, -15042699, negbrackscell);
  292       s.addCell(n);
  293   
  294       l = new Label(4, 8, "Custom neg brackets 2", wrappedText);
  295       s.addCell(l);
  296       NumberFormat negbracks2 = new NumberFormat("#,##0;(#,##0)a");
  297       WritableCellFormat negbrackscell2 = new WritableCellFormat(negbracks2);
  298       n = new Number(5,8, 15042699, negbrackscell2);
  299       s.addCell(n);
  300       n = new Number(6,8, -15042699, negbrackscell2);
  301       s.addCell(n);
  302   
  303       l = new Label(4, 9, "Custom percent", wrappedText);
  304       s.addCell(l);
  305       NumberFormat cuspercent = new NumberFormat("0.0%");
  306       WritableCellFormat cuspercentf = new WritableCellFormat(cuspercent);
  307       n = new Number(5, 9, 3.14159265, cuspercentf);
  308       s.addCell(n);
  309       
  310   
  311       // Booleans
  312       l = new Label(0,10, "Boolean - TRUE");
  313       s.addCell(l);
  314       Boolean b = new Boolean(1,10, true);
  315       s.addCell(b);
  316   
  317       l = new Label(0,11, "Boolean - FALSE");
  318       s.addCell(l);
  319       b = new Boolean(1,11,false);
  320       s.addCell(b);
  321   
  322       l = new Label(0, 12, "A hidden cell->");
  323       s.addCell(l);
  324       n = new Number(1, 12, 17, WritableWorkbook.HIDDEN_STYLE);
  325       s.addCell(n);
  326   
  327       // Currencies
  328       l = new Label(4, 19, "Currency formats");
  329       s.addCell(l);
  330   
  331       l = new Label(4, 21, "UK Pound");
  332       s.addCell(l);
  333       NumberFormat poundCurrency = 
  334         new NumberFormat(NumberFormat.CURRENCY_POUND + " #,###.00", 
  335                          NumberFormat.COMPLEX_FORMAT);
  336       WritableCellFormat poundFormat = new WritableCellFormat(poundCurrency);
  337       n = new Number(5, 21, 12345, poundFormat);
  338       s.addCell(n);
  339   
  340       l = new Label(4, 22, "Euro 1");
  341       s.addCell(l);
  342       NumberFormat euroPrefixCurrency = 
  343         new NumberFormat(NumberFormat.CURRENCY_EURO_PREFIX + " #,###.00", 
  344                          NumberFormat.COMPLEX_FORMAT);
  345       WritableCellFormat euroPrefixFormat = 
  346         new WritableCellFormat(euroPrefixCurrency);
  347       n = new Number(5, 22, 12345, euroPrefixFormat);
  348       s.addCell(n);
  349   
  350       l = new Label(4, 23, "Euro 2");
  351       s.addCell(l);
  352       NumberFormat euroSuffixCurrency = 
  353         new NumberFormat("#,###.00" + NumberFormat.CURRENCY_EURO_SUFFIX, 
  354                          NumberFormat.COMPLEX_FORMAT);
  355       WritableCellFormat euroSuffixFormat = 
  356         new WritableCellFormat(euroSuffixCurrency);
  357       n = new Number(5, 23, 12345, euroSuffixFormat);
  358       s.addCell(n);
  359   
  360       l = new Label(4, 24, "Dollar");
  361       s.addCell(l);
  362       NumberFormat dollarCurrency = 
  363         new NumberFormat(NumberFormat.CURRENCY_DOLLAR + " #,###.00", 
  364                          NumberFormat.COMPLEX_FORMAT);
  365       WritableCellFormat dollarFormat = 
  366         new WritableCellFormat(dollarCurrency);
  367       n = new Number(5, 24, 12345, dollarFormat);
  368       s.addCell(n);
  369   
  370       l = new Label(4, 25, "Japanese Yen");
  371       s.addCell(l);
  372       NumberFormat japaneseYenCurrency = 
  373         new NumberFormat(NumberFormat.CURRENCY_JAPANESE_YEN + " #,###.00", 
  374                          NumberFormat.COMPLEX_FORMAT);
  375       WritableCellFormat japaneseYenFormat = 
  376         new WritableCellFormat(japaneseYenCurrency);
  377       n = new Number(5, 25, 12345, japaneseYenFormat);
  378       s.addCell(n);
  379   
  380       l = new Label(4, 30, "Fraction formats");
  381       s.addCell(l);
  382   
  383       l = new Label(4,32, "One digit fraction format", wrappedText);
  384       s.addCell(l);
  385   
  386       WritableCellFormat fraction1digitformat = 
  387         new WritableCellFormat(NumberFormats.FRACTION_ONE_DIGIT);
  388       n = new Number(5, 32, 3.18279, fraction1digitformat);
  389       s.addCell(n);
  390   
  391       l = new Label(4,33, "Two digit fraction format", wrappedText);
  392       s.addCell(l);
  393   
  394       WritableCellFormat fraction2digitformat = 
  395         new WritableCellFormat(NumberFormats.FRACTION_TWO_DIGITS);
  396       n = new Number(5, 33, 3.18279, fraction2digitformat);
  397       s.addCell(n);
  398   
  399       l = new Label(4,34, "Three digit fraction format (improper)", wrappedText);
  400       s.addCell(l);
  401   
  402       NumberFormat fraction3digit1 = 
  403         new NumberFormat(NumberFormat.FRACTION_THREE_DIGITS, 
  404                          NumberFormat.COMPLEX_FORMAT);
  405       WritableCellFormat fraction3digitformat1 = 
  406         new WritableCellFormat(fraction3digit1);
  407       n = new Number(5, 34, 3.18927, fraction3digitformat1);
  408       s.addCell(n);
  409   
  410       l = new Label(4,35, "Three digit fraction format (proper)", wrappedText);
  411       s.addCell(l);
  412   
  413       NumberFormat fraction3digit2 = 
  414         new NumberFormat("# " + NumberFormat.FRACTION_THREE_DIGITS, 
  415                          NumberFormat.COMPLEX_FORMAT);
  416       WritableCellFormat fraction3digitformat2 = 
  417         new WritableCellFormat(fraction3digit2);
  418       n = new Number(5, 35, 3.18927, fraction3digitformat2);
  419       s.addCell(n);
  420       
  421       // Lots of numbers
  422       for (int row = 0; row < 100; row++)
  423       {
  424         for (int col = 8; col < 108; col++)
  425         {
  426           n = new Number(col, row, col+row);
  427           s.addCell(n);
  428         }
  429       }
  430   
  431       // Lots of numbers
  432       for (int row = 101; row < 3000; row++)
  433       {
  434         for (int col = 0; col < 25; col++)
  435         {
  436           n = new Number(col, row, col+row);
  437           s.addCell(n);
  438         }
  439       }
  440     }
  441   
  442     /**
  443      * Adds cells to the specified sheet which test the various date formats
  444      * 
  445      * @param s 
  446      */
  447     private void writeDateFormatSheet(WritableSheet s) throws WriteException
  448     {
  449       WritableCellFormat wrappedText = new WritableCellFormat
  450         (WritableWorkbook.ARIAL_10_PT);
  451       wrappedText.setWrap(true);
  452   
  453       s.setColumnView(0, 20);
  454       s.setColumnView(2, 20);
  455       s.setColumnView(3, 20);
  456       s.setColumnView(4, 20);
  457   
  458       s.getSettings().setFitWidth(2);
  459       s.getSettings().setFitHeight(2);
  460   
  461       Calendar c = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
  462       c.set(1975, 4, 31, 15, 21, 45);
  463       c.set(Calendar.MILLISECOND, 660);
  464       Date date = c.getTime();
  465       c.set(1900, 0, 1, 0, 0, 0);
  466       c.set(Calendar.MILLISECOND, 0);
  467   
  468       Date date2 = c.getTime();
  469       c.set(1970, 0, 1, 0, 0, 0);
  470       Date date3 = c.getTime();
  471       c.set(1918, 10, 11, 11, 0, 0);
  472       Date date4 = c.getTime();
  473       c.set(1900, 0, 2, 0, 0, 0);
  474       Date date5 = c.getTime();
  475       c.set(1901, 0, 1, 0, 0, 0);
  476       Date date6 = c.getTime();
  477       c.set(1900, 4, 31, 0, 0, 0);
  478       Date date7 = c.getTime();
  479       c.set(1900, 1, 1, 0, 0, 0);
  480       Date date8 = c.getTime();
  481       c.set(1900, 0, 31, 0, 0, 0);
  482       Date date9 = c.getTime();
  483       c.set(1900, 2, 1, 0, 0, 0);
  484       Date date10 = c.getTime();
  485       c.set(1900, 1, 27, 0, 0, 0);
  486       Date date11 = c.getTime();
  487       c.set(1900, 1, 28, 0, 0, 0);
  488       Date date12 = c.getTime();
  489       c.set(1980, 5, 31, 12, 0, 0);
  490       Date date13 = c.getTime();
  491       c.set(1066, 9, 14, 0, 0, 0);
  492       Date date14 = c.getTime();
  493   
  494       // Built in date formats
  495       SimpleDateFormat sdf = new SimpleDateFormat("dd MMM yyyy HH:mm:ss.SSS");
  496       sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
  497       Label l = new Label(0,0,"All dates are " + sdf.format(date),
  498                           wrappedText);
  499       s.addCell(l);
  500   
  501       l = new Label(0,1,"Built in formats", 
  502                     wrappedText);
  503       s.addCell(l);
  504   
  505       l = new Label(2, 1, "Custom formats");
  506       s.addCell(l);
  507   
  508       WritableCellFormat cf1 = new WritableCellFormat(DateFormats.FORMAT1);
  509       DateTime dt = new DateTime(0,2,date, cf1, DateTime.GMT);
  510       s.addCell(dt);
  511   
  512       cf1 = new WritableCellFormat(DateFormats.FORMAT2);
  513       dt = new DateTime(0,3,date, cf1,DateTime.GMT);
  514       s.addCell(dt);
  515   
  516       cf1 = new WritableCellFormat(DateFormats.FORMAT3);
  517       dt = new DateTime(0,4,date, cf1);
  518       s.addCell(dt);
  519   
  520       cf1 = new WritableCellFormat(DateFormats.FORMAT4);
  521       dt = new DateTime(0,5,date, cf1);
  522       s.addCell(dt);
  523   
  524       cf1 = new WritableCellFormat(DateFormats.FORMAT5);
  525       dt = new DateTime(0,6,date, cf1);
  526       s.addCell(dt);
  527   
  528       cf1 = new WritableCellFormat(DateFormats.FORMAT6);
  529       dt = new DateTime(0,7,date, cf1);
  530       s.addCell(dt);
  531   
  532       cf1 = new WritableCellFormat(DateFormats.FORMAT7);
  533       dt = new DateTime(0,8,date, cf1, DateTime.GMT);
  534       s.addCell(dt);
  535   
  536       cf1 = new WritableCellFormat(DateFormats.FORMAT8);
  537       dt = new DateTime(0,9,date, cf1, DateTime.GMT);
  538       s.addCell(dt);
  539   
  540       cf1 = new WritableCellFormat(DateFormats.FORMAT9);
  541       dt = new DateTime(0,10,date, cf1, DateTime.GMT);
  542       s.addCell(dt);
  543   
  544       cf1 = new WritableCellFormat(DateFormats.FORMAT10);
  545       dt = new DateTime(0,11,date, cf1, DateTime.GMT);
  546       s.addCell(dt);
  547   
  548       cf1 = new WritableCellFormat(DateFormats.FORMAT11);
  549       dt = new DateTime(0,12,date, cf1, DateTime.GMT);
  550       s.addCell(dt);
  551   
  552       cf1 = new WritableCellFormat(DateFormats.FORMAT12);
  553       dt = new DateTime(0,13,date, cf1, DateTime.GMT);
  554       s.addCell(dt);
  555   
  556       // Custom formats
  557       DateFormat df = new DateFormat("dd MM yyyy");
  558       cf1 = new WritableCellFormat(df);
  559       l = new Label(2, 2, "dd MM yyyy");
  560       s.addCell(l);
  561   
  562       dt = new DateTime(3, 2, date, cf1, DateTime.GMT);
  563       s.addCell(dt);
  564   
  565       df = new DateFormat("dd MMM yyyy");
  566       cf1 = new WritableCellFormat(df);
  567       l = new Label(2, 3, "dd MMM yyyy");
  568       s.addCell(l);
  569   
  570       dt = new DateTime(3, 3, date, cf1, DateTime.GMT);
  571       s.addCell(dt);
  572    
  573       df = new DateFormat("hh:mm");
  574       cf1 = new WritableCellFormat(df);
  575       l = new Label(2, 4, "hh:mm");
  576       s.addCell(l);
  577   
  578       dt = new DateTime(3, 4, date, cf1, DateTime.GMT);
  579       s.addCell(dt);
  580   
  581       df = new DateFormat("hh:mm:ss");
  582       cf1 = new WritableCellFormat(df);
  583       l = new Label(2, 5, "hh:mm:ss");
  584       s.addCell(l);
  585   
  586       dt = new DateTime(3, 5, date, cf1, DateTime.GMT);
  587       s.addCell(dt);
  588   
  589       df = new DateFormat("H:mm:ss a");
  590       cf1 = new WritableCellFormat(df);
  591       l = new Label(2, 5, "H:mm:ss a");
  592       s.addCell(l);
  593   
  594       dt = new DateTime(3, 5, date, cf1, DateTime.GMT);
  595       s.addCell(dt);
  596       dt = new DateTime(4, 5, date13, cf1, DateTime.GMT);
  597       s.addCell(dt);
  598   
  599       df = new DateFormat("mm:ss.SSS");
  600       cf1 = new WritableCellFormat(df);
  601       l = new Label(2, 6, "mm:ss.SSS");
  602       s.addCell(l);
  603   
  604       dt = new DateTime(3, 6, date, cf1, DateTime.GMT);
  605       s.addCell(dt);
  606   
  607       df = new DateFormat("hh:mm:ss a");
  608       cf1 = new WritableCellFormat(df);
  609       l = new Label(2, 7, "hh:mm:ss a");
  610       s.addCell(l);
  611   
  612       dt = new DateTime(4, 7, date13, cf1, DateTime.GMT);
  613       s.addCell(dt);
  614   
  615   
  616       // Check out the zero date ie. 1 Jan 1900
  617       l = new Label(0,16,"Zero date " + sdf.format(date2),
  618                     wrappedText);
  619       s.addCell(l);
  620   
  621       cf1 = new WritableCellFormat(DateFormats.FORMAT9);
  622       dt = new DateTime(0,17,date2, cf1, DateTime.GMT);
  623       s.addCell(dt);
  624   
  625       // Check out the zero date + 1 ie. 2 Jan 1900
  626       l = new Label(3,16,"Zero date + 1 " + sdf.format(date5),
  627                     wrappedText);
  628       s.addCell(l);
  629   
  630       cf1 = new WritableCellFormat(DateFormats.FORMAT9);
  631       dt = new DateTime(3,17,date5, cf1, DateTime.GMT);
  632       s.addCell(dt);
  633   
  634       // Check out the 1 Jan 1901
  635       l = new Label(3,19, sdf.format(date6),
  636                     wrappedText);
  637       s.addCell(l);
  638   
  639       cf1 = new WritableCellFormat(DateFormats.FORMAT9);
  640       dt = new DateTime(3,20,date6, cf1, DateTime.GMT);
  641       s.addCell(dt);
  642   
  643       // Check out the 31 May 1900
  644       l = new Label(3,22, sdf.format(date7),
  645                     wrappedText);
  646       s.addCell(l);
  647   
  648       cf1 = new WritableCellFormat(DateFormats.FORMAT9);
  649       dt = new DateTime(3,23, date7, cf1, DateTime.GMT);
  650       s.addCell(dt);
  651   
  652       // Check out 1 Feb 1900
  653       l = new Label(3,25, sdf.format(date8),
  654                     wrappedText);
  655       s.addCell(l);
  656   
  657       cf1 = new WritableCellFormat(DateFormats.FORMAT9);
  658       dt = new DateTime(3,26, date8, cf1, DateTime.GMT);
  659       s.addCell(dt);
  660   
  661       // Check out 31 Jan 1900
  662       l = new Label(3,28, sdf.format(date9),
  663                     wrappedText);
  664       s.addCell(l);
  665   
  666       cf1 = new WritableCellFormat(DateFormats.FORMAT9);
  667       dt = new DateTime(3,29, date9, cf1, DateTime.GMT);
  668       s.addCell(dt);
  669   
  670       // Check out 31 Jan 1900
  671       l = new Label(3,28, sdf.format(date9),
  672                     wrappedText);
  673       s.addCell(l);
  674   
  675       cf1 = new WritableCellFormat(DateFormats.FORMAT9);
  676       dt = new DateTime(3,29, date9, cf1, DateTime.GMT);
  677       s.addCell(dt);
  678   
  679       // Check out 1 Mar 1900
  680       l = new Label(3,31, sdf.format(date10),
  681                     wrappedText);
  682       s.addCell(l);
  683   
  684       cf1 = new WritableCellFormat(DateFormats.FORMAT9);
  685       dt = new DateTime(3,32, date10, cf1, DateTime.GMT);
  686       s.addCell(dt);
  687   
  688       // Check out 27 Feb 1900
  689       l = new Label(3,34, sdf.format(date11),
  690                     wrappedText);
  691       s.addCell(l);
  692   
  693       cf1 = new WritableCellFormat(DateFormats.FORMAT9);
  694       dt = new DateTime(3,35, date11, cf1, DateTime.GMT);
  695       s.addCell(dt);
  696   
  697       // Check out 28 Feb 1900
  698       l = new Label(3,37, sdf.format(date12),
  699                     wrappedText);
  700       s.addCell(l);
  701   
  702       cf1 = new WritableCellFormat(DateFormats.FORMAT9);
  703       dt = new DateTime(3,38, date12, cf1, DateTime.GMT);
  704       s.addCell(dt);
  705   
  706       // Check out the zero date ie. 1 Jan 1970
  707       l = new Label(0,19,"Zero UTC date " + sdf.format(date3),
  708                     wrappedText);
  709       s.addCell(l);
  710   
  711       cf1 = new WritableCellFormat(DateFormats.FORMAT9);
  712       dt = new DateTime(0,20,date3, cf1, DateTime.GMT);
  713       s.addCell(dt);
  714   
  715       // Check out the WWI armistice day ie. 11 am, Nov 11, 1918
  716       l = new Label(0,22,"Armistice date " + sdf.format(date4),
  717                     wrappedText);
  718       s.addCell(l);
  719   
  720       cf1 = new WritableCellFormat(DateFormats.FORMAT9);
  721       dt = new DateTime(0,23,date4, cf1, DateTime.GMT);
  722       s.addCell(dt);
  723   
  724       // Check out the Battle of Hastings date Oct 14th, 1066
  725       l = new Label(0,25, "Battle of Hastings " + sdf.format(date14),
  726                     wrappedText);
  727       s.addCell(l);
  728   
  729       cf1 = new WritableCellFormat(DateFormats.FORMAT2);
  730       dt = new DateTime(0, 26, date14, cf1, DateTime.GMT);
  731       s.addCell(dt);
  732     }
  733   
  734     /**
  735      * Adds cells to the specified sheet which test the various label formatting
  736      * styles, such as different fonts, different sizes and bold, underline etc.
  737      * 
  738      * @param s1 
  739      */
  740     private void writeLabelFormatSheet(WritableSheet s1) throws WriteException
  741     {
  742       s1.setColumnView(0, 60);
  743   
  744       Label lr = new Label(0,0, "Arial Fonts");
  745       s1.addCell(lr);
  746   
  747       lr = new Label(1,0, "10pt");
  748       s1.addCell(lr);
  749   
  750       lr = new Label(2, 0, "Normal");
  751       s1.addCell(lr);
  752   
  753       lr = new Label(3, 0, "12pt");
  754       s1.addCell(lr);
  755   
  756       WritableFont arial12pt = new WritableFont(WritableFont.ARIAL, 12);
  757       WritableCellFormat arial12format = new WritableCellFormat(arial12pt);
  758       arial12format.setWrap(true);
  759       lr = new Label(4, 0, "Normal", arial12format);
  760       s1.addCell(lr);
  761   
  762       WritableFont arial10ptBold = new WritableFont
  763         (WritableFont.ARIAL, 10, WritableFont.BOLD);
  764       WritableCellFormat arial10BoldFormat = new WritableCellFormat
  765         (arial10ptBold);
  766       lr = new Label(2, 2, "BOLD", arial10BoldFormat);
  767       s1.addCell(lr);
  768   
  769       WritableFont arial12ptBold = new WritableFont
  770         (WritableFont.ARIAL, 12, WritableFont.BOLD);
  771       WritableCellFormat arial12BoldFormat = new WritableCellFormat
  772         (arial12ptBold);
  773       lr = new Label(4, 2, "BOLD", arial12BoldFormat);
  774       s1.addCell(lr);
  775   
  776       WritableFont arial10ptItalic = new WritableFont
  777         (WritableFont.ARIAL, 10, WritableFont.NO_BOLD, true);
  778       WritableCellFormat arial10ItalicFormat = new WritableCellFormat
  779         (arial10ptItalic);
  780       lr = new Label(2, 4, "Italic", arial10ItalicFormat);
  781       s1.addCell(lr);
  782   
  783       WritableFont arial12ptItalic = new WritableFont
  784         (WritableFont.ARIAL, 12, WritableFont.NO_BOLD, true);
  785       WritableCellFormat arial12ptItalicFormat = new WritableCellFormat
  786         (arial12ptItalic);
  787       lr = new Label(4, 4, "Italic", arial12ptItalicFormat);
  788       s1.addCell(lr);
  789   
  790       WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
  791       WritableCellFormat times10format = new WritableCellFormat(times10pt);
  792       lr = new Label(0, 7, "Times Fonts", times10format);
  793       s1.addCell(lr);
  794   
  795       lr = new Label(1, 7, "10pt", times10format);
  796       s1.addCell(lr);
  797   
  798       lr = new Label(2, 7, "Normal", times10format);
  799       s1.addCell(lr);
  800   
  801       lr = new Label(3, 7, "12pt", times10format);
  802       s1.addCell(lr);
  803   
  804       WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
  805       WritableCellFormat times12format = new WritableCellFormat(times12pt);
  806       lr = new Label(4, 7, "Normal", times12format);
  807       s1.addCell(lr);
  808   
  809       WritableFont times10ptBold = new WritableFont
  810         (WritableFont.TIMES, 10, WritableFont.BOLD);
  811       WritableCellFormat times10BoldFormat = new WritableCellFormat
  812         (times10ptBold);
  813       lr = new Label(2, 9, "BOLD", times10BoldFormat);
  814       s1.addCell(lr);
  815   
  816       WritableFont times12ptBold = new WritableFont
  817         (WritableFont.TIMES, 12, WritableFont.BOLD);
  818       WritableCellFormat times12BoldFormat = new WritableCellFormat
  819         (times12ptBold);
  820       lr = new Label(4, 9, "BOLD", times12BoldFormat);
  821       s1.addCell(lr);
  822   
  823       // The underline styles
  824       s1.setColumnView(6, 22);
  825       s1.setColumnView(7, 22);
  826       s1.setColumnView(8, 22);
  827       s1.setColumnView(9, 22);
  828   
  829       lr = new Label(0, 11, "Underlining");
  830       s1.addCell(lr);
  831   
  832       WritableFont arial10ptUnderline = new WritableFont
  833         (WritableFont.ARIAL, 
  834          WritableFont.DEFAULT_POINT_SIZE,
  835          WritableFont.NO_BOLD,
  836          false,
  837          UnderlineStyle.SINGLE);
  838       WritableCellFormat arialUnderline = new WritableCellFormat
  839         (arial10ptUnderline);
  840       lr = new Label(6,11, "Underline", arialUnderline);
  841       s1.addCell(lr);
  842   
  843       WritableFont arial10ptDoubleUnderline = new WritableFont
  844         (WritableFont.ARIAL, 
  845          WritableFont.DEFAULT_POINT_SIZE,
  846          WritableFont.NO_BOLD,
  847          false,
  848          UnderlineStyle.DOUBLE);
  849       WritableCellFormat arialDoubleUnderline = new WritableCellFormat
  850         (arial10ptDoubleUnderline);
  851       lr = new Label(7,11, "Double Underline", arialDoubleUnderline);
  852       s1.addCell(lr);
  853   
  854       WritableFont arial10ptSingleAcc = new WritableFont
  855         (WritableFont.ARIAL, 
  856          WritableFont.DEFAULT_POINT_SIZE,
  857          WritableFont.NO_BOLD,
  858          false,
  859          UnderlineStyle.SINGLE_ACCOUNTING);
  860       WritableCellFormat arialSingleAcc = new WritableCellFormat
  861         (arial10ptSingleAcc);
  862       lr = new Label(8,11, "Single Accounting Underline", arialSingleAcc);
  863       s1.addCell(lr);
  864   
  865       WritableFont arial10ptDoubleAcc = new WritableFont
  866         (WritableFont.ARIAL, 
  867          WritableFont.DEFAULT_POINT_SIZE,
  868          WritableFont.NO_BOLD,
  869          false,
  870          UnderlineStyle.DOUBLE_ACCOUNTING);
  871       WritableCellFormat arialDoubleAcc = new WritableCellFormat
  872         (arial10ptDoubleAcc);
  873       lr = new Label(9,11, "Double Accounting Underline", arialDoubleAcc);
  874       s1.addCell(lr);
  875   
  876       WritableFont times14ptBoldUnderline = new WritableFont
  877         (WritableFont.TIMES,
  878          14,
  879          WritableFont.BOLD,
  880          false,
  881          UnderlineStyle.SINGLE);
  882       WritableCellFormat timesBoldUnderline = new WritableCellFormat
  883         (times14ptBoldUnderline);
  884       lr = new Label(6,12, "Times 14 Bold Underline", timesBoldUnderline);
  885       s1.addCell(lr);
  886   
  887       WritableFont arial18ptBoldItalicUnderline = new WritableFont
  888         (WritableFont.ARIAL,
  889          18,
  890          WritableFont.BOLD,
  891          true,
  892          UnderlineStyle.SINGLE);
  893       WritableCellFormat arialBoldItalicUnderline = new WritableCellFormat
  894         (arial18ptBoldItalicUnderline);
  895       lr = new Label(6,13, "Arial 18 Bold Italic Underline", 
  896                      arialBoldItalicUnderline);
  897       s1.addCell(lr);
  898   
  899       lr = new Label(0, 15, "Script styles");
  900       s1.addCell(lr);
  901   
  902       WritableFont superscript = new WritableFont
  903         (WritableFont.ARIAL,
  904          WritableFont.DEFAULT_POINT_SIZE,
  905          WritableFont.NO_BOLD,
  906          false,
  907          UnderlineStyle.NO_UNDERLINE,
  908          Colour.BLACK,
  909          ScriptStyle.SUPERSCRIPT);
  910       WritableCellFormat superscriptFormat = new WritableCellFormat
  911         (superscript);
  912       lr = new Label(1,15, "superscript", superscriptFormat);
  913       s1.addCell(lr);
  914   
  915       WritableFont subscript = new WritableFont
  916         (WritableFont.ARIAL,
  917          WritableFont.DEFAULT_POINT_SIZE,
  918          WritableFont.NO_BOLD,
  919          false,
  920          UnderlineStyle.NO_UNDERLINE,
  921          Colour.BLACK,
  922          ScriptStyle.SUBSCRIPT);
  923       WritableCellFormat subscriptFormat = new WritableCellFormat
  924         (subscript);
  925       lr = new Label(2,15, "subscript", subscriptFormat);
  926       s1.addCell(lr);
  927   
  928       lr = new Label(0, 17, "Colours");
  929       s1.addCell(lr);
  930   
  931       WritableFont red = new WritableFont(WritableFont.ARIAL, 
  932                                           WritableFont.DEFAULT_POINT_SIZE,
  933                                           WritableFont.NO_BOLD,
  934                                           false,
  935                                           UnderlineStyle.NO_UNDERLINE,
  936                                           Colour.RED);
  937       WritableCellFormat redFormat = new WritableCellFormat(red);
  938       lr = new Label(2, 17, "Red", redFormat);
  939       s1.addCell(lr);
  940   
  941       WritableFont blue = new WritableFont(WritableFont.ARIAL, 
  942                                            WritableFont.DEFAULT_POINT_SIZE,
  943                                            WritableFont.NO_BOLD,
  944                                            false,
  945                                            UnderlineStyle.NO_UNDERLINE,
  946                                            Colour.BLUE);
  947       WritableCellFormat blueFormat = new WritableCellFormat(blue);
  948       lr = new Label(2, 18, "Blue", blueFormat);
  949       s1.addCell(lr);
  950   
  951       WritableFont lime = new WritableFont(WritableFont.ARIAL);
  952       lime.setColour(Colour.LIME);
  953       WritableCellFormat limeFormat = new WritableCellFormat(lime);
  954       limeFormat.setWrap(true);
  955       lr = new Label(4, 18, "Modified palette - was lime, now red", limeFormat);
  956       s1.addCell(lr);
  957       
  958       WritableCellFormat greyBackground = new WritableCellFormat();
  959       greyBackground.setWrap(true);
  960       greyBackground.setBackground(Colour.GRAY_50);
  961       lr = new Label(2, 19, "Grey background", greyBackground);
  962       s1.addCell(lr);
  963   
  964       WritableFont yellow = new WritableFont(WritableFont.ARIAL, 
  965                                              WritableFont.DEFAULT_POINT_SIZE,
  966                                              WritableFont.NO_BOLD,
  967                                              false,
  968                                              UnderlineStyle.NO_UNDERLINE,
  969                                              Colour.YELLOW);
  970       WritableCellFormat yellowOnBlue = new WritableCellFormat(yellow);
  971       yellowOnBlue.setWrap(true);
  972       yellowOnBlue.setBackground(Colour.BLUE);
  973       lr = new Label(2, 20, "Blue background, yellow foreground", yellowOnBlue);
  974       s1.addCell(lr);
  975   
  976       WritableCellFormat yellowOnBlack = new WritableCellFormat(yellow);
  977       yellowOnBlack.setWrap(true);
  978       yellowOnBlack.setBackground(Colour.PALETTE_BLACK);
  979       lr = new Label(3, 20, "Black background, yellow foreground",
  980                      yellowOnBlack);
  981       s1.addCell(lr);
  982   
  983       lr = new Label(0, 22, "Null label");
  984       s1.addCell(lr);
  985   
  986       lr = new Label(2, 22, null);
  987       s1.addCell(lr);
  988   
  989       lr = new Label(0, 24, 
  990                      "A very long label, more than 255 characters\012" +
  991                      "Rejoice O shores\012" +
  992                      "Sing O bells\012" + 
  993                      "But I with mournful tread\012" +
  994                      "Walk the deck my captain lies\012" +
  995                      "Fallen cold and dead\012"+
  996                      "Summer surprised, coming over the Starnbergersee\012" +
  997                      "With a shower of rain. We stopped in the Colonnade\012" +
  998                      "A very long label, more than 255 characters\012" +
  999                      "Rejoice O shores\012" +
 1000                      "Sing O bells\012" + 
 1001                      "But I with mournful tread\012" +
 1002                      "Walk the deck my captain lies\012" +
 1003                      "Fallen cold and dead\012"+
 1004                      "Summer surprised, coming over the Starnbergersee\012" +
 1005                      "With a shower of rain. We stopped in the Colonnade\012" +                   "A very long label, more than 255 characters\012" +
 1006                      "Rejoice O shores\012" +
 1007                      "Sing O bells\012" + 
 1008                      "But I with mournful tread\012" +
 1009                      "Walk the deck my captain lies\012" +
 1010                      "Fallen cold and dead\012"+
 1011                      "Summer surprised, coming over the Starnbergersee\012" +
 1012                      "With a shower of rain. We stopped in the Colonnade\012" +                   "A very long label, more than 255 characters\012" +
 1013                      "Rejoice O shores\012" +
 1014                      "Sing O bells\012" + 
 1015                      "But I with mournful tread\012" +
 1016                      "Walk the deck my captain lies\012" +
 1017                      "Fallen cold and dead\012"+
 1018                      "Summer surprised, coming over the Starnbergersee\012" +
 1019                      "With a shower of rain. We stopped in the Colonnade\012" +
 1020                      "And sat and drank coffee an talked for an hour\012",
 1021                      arial12format);
 1022       s1.addCell(lr);
 1023   
 1024       WritableCellFormat vertical = new WritableCellFormat();
 1025       vertical.setOrientation(Orientation.VERTICAL);
 1026       lr = new Label(0, 26, "Vertical orientation", vertical);
 1027       s1.addCell(lr);
 1028       
 1029   
 1030       WritableCellFormat plus_90 = new WritableCellFormat();
 1031       plus_90.setOrientation(Orientation.PLUS_90);
 1032       lr = new Label(1, 26, "Plus 90", plus_90);
 1033       s1.addCell(lr);
 1034   
 1035   
 1036       WritableCellFormat minus_90 = new WritableCellFormat();
 1037       minus_90.setOrientation(Orientation.MINUS_90);
 1038       lr = new Label(2, 26, "Minus 90", minus_90);
 1039       s1.addCell(lr);
 1040   
 1041       lr = new Label(0, 28, "Modified row height");
 1042       s1.addCell(lr);
 1043       s1.setRowView(28, 24*20);
 1044   
 1045       lr = new Label(0, 29, "Collapsed row");
 1046       s1.addCell(lr);
 1047       s1.setRowView(29, true);
 1048   
 1049       // Write hyperlinks
 1050       try
 1051       {
 1052         Label l = new Label(0, 30, "Hyperlink to home page");
 1053         s1.addCell(l);
 1054         
 1055         URL url = new URL("http://www.andykhan.com/jexcelapi");
 1056         WritableHyperlink wh = new WritableHyperlink(0, 30, 8, 31, url);
 1057         s1.addHyperlink(wh);
 1058   
 1059         // The below hyperlink clashes with above
 1060         WritableHyperlink wh2 = new WritableHyperlink(7, 30, 9, 31, url);
 1061         s1.addHyperlink(wh2);
 1062   
 1063         l = new Label(4, 2, "File hyperlink to documentation");
 1064         s1.addCell(l);
 1065   
 1066         File file = new File("../jexcelapi/docs/index.html");
 1067         wh = new WritableHyperlink(0, 32, 8, 32, file, 
 1068                                    "JExcelApi Documentation");
 1069         s1.addHyperlink(wh);
 1070   
 1071         // Add a hyperlink to another cell on this sheet
 1072         wh = new WritableHyperlink(0, 34, 8, 34, 
 1073                                    "Link to another cell",
 1074                                    s1,
 1075                                    0, 180, 1, 181);
 1076         s1.addHyperlink(wh);
 1077   
 1078         file = new File("\\\\localhost\\file.txt");
 1079         wh = new WritableHyperlink(0, 36, 8, 36, file);
 1080         s1.addHyperlink(wh);
 1081   
 1082         // Add a very long hyperlink
 1083         url = new URL("http://www.amazon.co.uk/exec/obidos/ASIN/0571058086"+
 1084                      "/qid=1099836249/sr=1-3/ref=sr_1_11_3/202-6017285-1620664");
 1085         wh = new WritableHyperlink(0, 38, 0, 38, url);
 1086         s1.addHyperlink(wh);
 1087       }
 1088       catch (MalformedURLException e)
 1089       {
 1090         System.err.println(e.toString());
 1091       }
 1092   
 1093       // Write out some merged cells
 1094       Label l = new Label(5, 35, "Merged cells", timesBoldUnderline);
 1095       s1.mergeCells(5, 35, 8, 37);
 1096       s1.addCell(l);
 1097   
 1098       l = new Label(5, 38, "More merged cells");
 1099       s1.addCell(l);
 1100       Range r = s1.mergeCells(5, 38, 8, 41);
 1101       s1.insertRow(40);
 1102       s1.removeRow(39);
 1103       s1.unmergeCells(r);
 1104   
 1105       // Merge cells and centre across them
 1106       WritableCellFormat wcf = new WritableCellFormat();
 1107       wcf.setAlignment(Alignment.CENTRE);
 1108       l = new Label(5, 42, "Centred across merged cells", wcf);
 1109       s1.addCell(l);
 1110       s1.mergeCells(5, 42, 10, 42);
 1111   
 1112       wcf = new WritableCellFormat();
 1113       wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
 1114       wcf.setBackground(Colour.GRAY_25);
 1115       l = new Label(3, 44, "Merged with border", wcf);
 1116       s1.addCell(l);
 1117       s1.mergeCells(3, 44, 4, 46);
 1118   
 1119       // Clash some ranges - the second range will not be added
 1120       // Also merge some cells with two data items in the - the second data
 1121       // item will not be merged
 1122       /*
 1123       l = new Label(5, 16, "merged cells");
 1124       s1.addCell(l);
 1125   
 1126       Label l5 = new Label(7, 17, "this label won't appear");
 1127       s1.addCell(l5);
 1128       s1.mergeCells(5, 16, 8, 18);    
 1129   
 1130       s1.mergeCells(5, 19, 6, 24);
 1131       s1.mergeCells(6, 18, 10, 19);
 1132       */
 1133       
 1134       WritableFont courier10ptFont = new WritableFont(WritableFont.COURIER, 10);
 1135       WritableCellFormat courier10pt = new WritableCellFormat(courier10ptFont);
 1136       l = new Label(0, 49, "Courier fonts", courier10pt);
 1137       s1.addCell(l);
 1138   
 1139       WritableFont tahoma12ptFont = new WritableFont(WritableFont.TAHOMA, 12);
 1140       WritableCellFormat tahoma12pt = new WritableCellFormat(tahoma12ptFont);
 1141       l = new Label(0, 50, "Tahoma fonts", tahoma12pt);
 1142       s1.addCell(l);
 1143   
 1144       WritableFont.FontName wingdingsFont = 
 1145         WritableFont.createFont("Wingdings 2");
 1146       WritableFont wingdings210ptFont = new WritableFont(wingdingsFont, 10);
 1147       WritableCellFormat wingdings210pt = new WritableCellFormat
 1148         (wingdings210ptFont);
 1149       l = new Label(0,51, "Bespoke Windgdings 2", wingdings210pt);
 1150       s1.addCell(l);
 1151   
 1152       WritableCellFormat shrinkToFit = new WritableCellFormat(times12pt);
 1153       shrinkToFit.setShrinkToFit(true);
 1154       l = new Label(3,53, "Shrunk to fit", shrinkToFit);
 1155       s1.addCell(l);
 1156   
 1157       l = new Label(3,55, "Some long wrapped text in a merged cell", 
 1158                     arial12format);
 1159       s1.addCell(l);
 1160       s1.mergeCells(3,55,4,55);
 1161   
 1162       l = new Label(0, 57, "A cell with a comment");
 1163       WritableCellFeatures cellFeatures = new WritableCellFeatures();
 1164       cellFeatures.setComment("the cell comment");
 1165       l.setCellFeatures(cellFeatures);
 1166       s1.addCell(l);
 1167   
 1168       l = new Label(0, 59, 
 1169                     "A cell with a long comment");
 1170       cellFeatures = new WritableCellFeatures();
 1171       cellFeatures.setComment("a very long cell comment indeed that won't " +
 1172                               "fit inside a standard comment box, so a " +
 1173                               "larger comment box is used instead",
 1174                               5, 6);
 1175       l.setCellFeatures(cellFeatures);
 1176       s1.addCell(l);
 1177   
 1178       WritableCellFormat indented = new WritableCellFormat(times12pt);
 1179       indented.setIndentation(4);
 1180       l = new Label(0, 61, "Some indented text", indented);
 1181       s1.addCell(l);
 1182   
 1183       l = new Label(0, 63, "Data validation:  list");
 1184       s1.addCell(l);
 1185       
 1186       Blank b = new Blank(1,63);
 1187       cellFeatures = new WritableCellFeatures();
 1188       ArrayList al = new ArrayList();
 1189       al.add("bagpuss");
 1190       al.add("clangers");
 1191       al.add("ivor the engine");
 1192       al.add("noggin the nog");
 1193       cellFeatures.setDataValidationList(al);
 1194       b.setCellFeatures(cellFeatures);
 1195       s1.addCell(b);
 1196   
 1197       l = new Label(0, 64, "Data validation:  number > 4.5");
 1198       s1.addCell(l);
 1199       
 1200       b = new Blank(1,64);
 1201       cellFeatures = new WritableCellFeatures();
 1202       cellFeatures.setNumberValidation(4.5, WritableCellFeatures.GREATER_THAN);
 1203       b.setCellFeatures(cellFeatures);
 1204       s1.addCell(b);
 1205   
 1206       l = new Label(0, 65, "Data validation:  named range");
 1207       s1.addCell(l);
 1208       
 1209       l = new Label(4, 65, "tiger");
 1210       s1.addCell(l);
 1211       l = new Label(5, 65, "sword");
 1212       s1.addCell(l);
 1213       l = new Label(6, 65, "honour");
 1214       s1.addCell(l);
 1215       l = new Label(7, 65, "company");
 1216       s1.addCell(l);
 1217       l = new Label(8, 65, "victory");
 1218       s1.addCell(l);
 1219       l = new Label(9, 65, "fortress");
 1220       s1.addCell(l);
 1221   
 1222       b = new Blank(1,65);
 1223       cellFeatures = new WritableCellFeatures();
 1224       cellFeatures.setDataValidationRange("validation_range");
 1225       b.setCellFeatures(cellFeatures);
 1226       s1.addCell(b);
 1227   
 1228       // Set the row grouping
 1229       s1.setRowGroup(39, 45, false);
 1230       // s1.setRowGroup(72, 74, true);
 1231   
 1232       l = new Label(0, 66, "Block of cells B67-F71 with data validation");
 1233       s1.addCell(l);
 1234   
 1235       al = new ArrayList();
 1236       al.add("Achilles");
 1237       al.add("Agamemnon");
 1238       al.add("Hector");
 1239       al.add("Odysseus");
 1240       al.add("Patroclus");
 1241       al.add("Nestor");
 1242   
 1243       b = new Blank(1, 66);
 1244       cellFeatures = new WritableCellFeatures();
 1245       cellFeatures.setDataValidationList(al);
 1246       b.setCellFeatures(cellFeatures);
 1247       s1.addCell(b);
 1248       s1.applySharedDataValidation(b, 4,4);
 1249   
 1250       cellFeatures = new WritableCellFeatures();
 1251       cellFeatures.setDataValidationRange("");
 1252       l = new Label(0, 71, "Read only cell using empty data validation");
 1253       l.setCellFeatures(cellFeatures);
 1254       s1.addCell(l);
 1255   
 1256       // Set the row grouping
 1257       s1.setRowGroup(39, 45, false);
 1258       // s1.setRowGroup(72, 74, true);
 1259     }
 1260   
 1261     /**
 1262      * Adds cells to the specified sheet which test the various border
 1263      * styles
 1264      * 
 1265      * @param s
 1266      */
 1267     private void writeBordersSheet(WritableSheet s) throws WriteException
 1268     {
 1269       s.getSettings().setProtected(true);
 1270   
 1271       s.setColumnView(1, 15);
 1272       s.setColumnView(2, 15);
 1273       s.setColumnView(4, 15);
 1274       WritableCellFormat thickLeft = new WritableCellFormat();
 1275       thickLeft.setBorder(Border.LEFT, BorderLineStyle.THICK); 
 1276       Label lr = new Label(1,0, "Thick left", thickLeft);
 1277       s.addCell(lr);
 1278   
 1279       WritableCellFormat dashedRight = new WritableCellFormat();
 1280       dashedRight.setBorder(Border.RIGHT, BorderLineStyle.DASHED);
 1281       lr = new Label(2, 0, "Dashed right", dashedRight);
 1282       s.addCell(lr);
 1283   
 1284       WritableCellFormat doubleTop = new WritableCellFormat();
 1285       doubleTop.setBorder(Border.TOP, BorderLineStyle.DOUBLE);
 1286       lr = new Label(1, 2, "Double top", doubleTop);
 1287       s.addCell(lr);
 1288   
 1289       WritableCellFormat hairBottom = new WritableCellFormat();
 1290       hairBottom.setBorder(Border.BOTTOM, BorderLineStyle.HAIR);
 1291       lr = new Label(2, 2, "Hair bottom", hairBottom);
 1292       s.addCell(lr);
 1293   
 1294       WritableCellFormat allThin = new WritableCellFormat();
 1295       allThin.setBorder(Border.ALL, BorderLineStyle.THIN);
 1296       lr = new Label(4, 2, "All thin", allThin);
 1297       s.addCell(lr);
 1298   
 1299       WritableCellFormat twoBorders = new WritableCellFormat();
 1300       twoBorders.setBorder(Border.TOP, BorderLineStyle.THICK);
 1301       twoBorders.setBorder(Border.LEFT, BorderLineStyle.THICK);
 1302       lr = new Label(6,2, "Two borders", twoBorders);
 1303       s.addCell(lr);
 1304   
 1305       // Create a cell in the middle of nowhere (out of the grow region)
 1306       lr = new Label(20, 20, "Dislocated cell - after a page break");
 1307       s.addCell(lr);
 1308   
 1309       // Set the orientation and the margins
 1310       s.getSettings().setPaperSize(PaperSize.A3);
 1311       s.getSettings().setOrientation(PageOrientation.LANDSCAPE);
 1312       s.getSettings().setPageOrder(PageOrder.DOWN_THEN_RIGHT);
 1313       s.getSettings().setHeaderMargin(2);
 1314       s.getSettings().setFooterMargin(2);
 1315   
 1316       s.getSettings().setTopMargin(3);
 1317       s.getSettings().setBottomMargin(3);
 1318   
 1319       // Add a header and footera
 1320       HeaderFooter header = new HeaderFooter();
 1321       header.getCentre().append("Page Header");
 1322       s.getSettings().setHeader(header);
 1323   
 1324       HeaderFooter footer = new HeaderFooter();
 1325       footer.getRight().append("page ");
 1326       footer.getRight().appendPageNumber();
 1327       s.getSettings().setFooter(footer);
 1328   
 1329       // Add a page break and insert a couple of rows
 1330       s.addRowPageBreak(18);
 1331       s.insertRow(17);
 1332       s.insertRow(17);
 1333       s.removeRow(17);
 1334   
 1335       // Add a page break off the screen
 1336       s.addRowPageBreak(30);
 1337   
 1338       // Add a hidden column
 1339       lr = new Label(10, 1, "Hidden column");
 1340       s.addCell(lr);
 1341   
 1342       lr = new Label(3, 8, "Hidden row");
 1343       s.addCell(lr);
 1344       s.setRowView(8, true);
 1345   
 1346       WritableCellFormat allThickRed = new WritableCellFormat();
 1347       allThickRed.setBorder(Border.ALL, BorderLineStyle.THICK, Colour.RED);
 1348       lr = new Label(1, 5, "All thick red", allThickRed);
 1349       s.addCell(lr);
 1350   
 1351       WritableCellFormat topBottomBlue = new WritableCellFormat();
 1352       topBottomBlue.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLUE);
 1353       topBottomBlue.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLUE);
 1354       lr = new Label(4, 5, "Top and bottom blue", topBottomBlue);
 1355       s.addCell(lr);
 1356     }
 1357   
 1358     /**
 1359      * Write out loads of labels, in order to test the shared string table
 1360      */
 1361     private void writeLabelsSheet(WritableSheet ws) throws WriteException
 1362     {
 1363       ws.getSettings().setProtected(true);
 1364       ws.getSettings().setPassword("jxl");
 1365       ws.getSettings().setVerticalFreeze(5);
 1366       ws.getSettings().setDefaultRowHeight(25*20);
 1367   
 1368       WritableFont wf = new WritableFont(WritableFont.ARIAL, 12);
 1369       wf.setItalic(true);
 1370   
 1371       WritableCellFormat wcf = new WritableCellFormat(wf);
 1372   
 1373       CellView cv = new CellView();
 1374       cv.setSize(25 * 256);
 1375       cv.setFormat(wcf);
 1376       ws.setColumnView(0, cv);
 1377       ws.setColumnView(1, 15);
 1378   
 1379       for (int i =  0; i < 61; i++)
 1380       {
 1381         Label l1 = new Label(0, i, "Common Label");
 1382         Label l2 = new Label(1, i, "Distinct label number " + i);
 1383         ws.addCell(l1);
 1384         ws.addCell(l2);
 1385       }
 1386   
 1387       // Frig this test record - it appears exactly on the boundary of an SST
 1388       // continue record
 1389   
 1390       Label l3 = new Label(0, 61, "Common Label", wcf);
 1391       Label l4 = new Label(1, 61, "1-1234567890", wcf);
 1392       Label l5 = new Label(2, 61, "2-1234567890", wcf);
 1393       ws.addCell(l3);
 1394       ws.addCell(l4);
 1395       ws.addCell(l5);
 1396   
 1397       for (int i =  62; i < 200; i++)
 1398       {
 1399         Label l1 = new Label(0, i, "Common Label");
 1400         Label l2 = new Label(1, i, "Distinct label number " + i);
 1401         ws.addCell(l1);
 1402         ws.addCell(l2);
 1403       }
 1404   
 1405       // Add in a last label which doesn't take the jxl.common.format
 1406       wf = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD);
 1407       wf.setColour(Colour.RED);
 1408       WritableCellFormat wcf2 = new WritableCellFormat(wf);
 1409       wcf2.setWrap(true);
 1410       Label l = new Label(0, 205, "Different format", wcf2);
 1411       ws.addCell(l);
 1412   
 1413       // Add some labels to column 5 for autosizing
 1414       Label l6 = new Label(5, 2, "A column for autosizing", wcf2);
 1415       ws.addCell(l6);
 1416       l6 = new Label(5, 4, "Another label, longer this time and " + 
 1417                      "in a different font");
 1418       ws.addCell(l6);
 1419   
 1420       CellView cf = new CellView();
 1421       cf.setAutosize(true);
 1422       ws.setColumnView(5, cf);
 1423     }
 1424   
 1425     /**
 1426      * Test out the formula parser
 1427      */
 1428     private void writeFormulaSheet(WritableSheet ws) throws WriteException
 1429     {
 1430       // Add some cells to manipulate
 1431       Number nc = new Number(0,0,15);
 1432       ws.addCell(nc);
 1433       
 1434       nc = new Number(0,1,16);
 1435       ws.addCell(nc);
 1436   
 1437       nc = new Number(0,2,10);
 1438       ws.addCell(nc);
 1439       
 1440       nc = new Number(0,3, 12);
 1441       ws.addCell(nc);
 1442   
 1443       ws.setColumnView(2, 20);
 1444       WritableCellFormat wcf = new WritableCellFormat();
 1445       wcf.setAlignment(Alignment.RIGHT);
 1446       wcf.setWrap(true);
 1447       CellView cv = new CellView();
 1448       cv.setSize(25 * 256);
 1449       cv.setFormat(wcf);
 1450       ws.setColumnView(3, cv);
 1451   
 1452       // Add in the formulas
 1453       Formula f = null;
 1454       Label l = null;
 1455   
 1456       f = new Formula(2,0, "A1+A2");
 1457       ws.addCell(f);
 1458       l = new Label(3, 0, "a1+a2");
 1459       ws.addCell(l);
 1460   
 1461       f = new Formula(2,1, "A2 * 3");
 1462       ws.addCell(f);
 1463       l = new Label(3,1, "A2 * 3");
 1464       ws.addCell(l);
 1465   
 1466       f = new Formula(2,2, "A2+A1/2.5");
 1467       ws.addCell(f);
 1468       l = new Label(3,2, "A2+A1/2.5");
 1469       ws.addCell(l);
 1470   
 1471       f = new Formula(2,3, "3+(a1+a2)/2.5");
 1472       ws.addCell(f);
 1473       l = new Label(3,3, "3+(a1+a2)/2.5");
 1474       ws.addCell(l);
 1475   
 1476       f = new Formula(2,4, "(a1+a2)/2.5");
 1477       ws.addCell(f);
 1478       l = new Label(3,4, "(a1+a2)/2.5");
 1479       ws.addCell(l);
 1480   
 1481       f = new Formula(2,5, "15+((a1+a2)/2.5)*17");
 1482       ws.addCell(f);
 1483       l = new Label(3,5, "15+((a1+a2)/2.5)*17");
 1484       ws.addCell(l);
 1485   
 1486       f = new Formula(2, 6, "SUM(a1:a4)");
 1487       ws.addCell(f);
 1488       l = new Label(3, 6, "SUM(a1:a4)");
 1489       ws.addCell(l);
 1490   
 1491       f = new Formula(2, 7, "SUM(a1:a4)/4");
 1492       ws.addCell(f);
 1493       l = new Label(3, 7, "SUM(a1:a4)/4");
 1494       ws.addCell(l);
 1495   
 1496       f = new Formula(2, 8, "AVERAGE(A1:A4)");
 1497       ws.addCell(f);
 1498       l = new Label(3, 8, "AVERAGE(a1:a4)");
 1499       ws.addCell(l);
 1500   
 1501       f = new Formula(2, 9, "MIN(5,4,1,2,3)");
 1502       ws.addCell(f);
 1503       l = new Label(3, 9, "MIN(5,4,1,2,3)");
 1504       ws.addCell(l);
 1505   
 1506       f = new Formula(2, 10, "ROUND(3.14159265, 3)");
 1507       ws.addCell(f);
 1508       l = new Label(3, 10, "ROUND(3.14159265, 3)");
 1509       ws.addCell(l);
 1510   
 1511       f = new Formula(2, 11, "MAX(SUM(A1:A2), A1*A2, POWER(A1, 2))");
 1512       ws.addCell(f);
 1513       l = new Label(3, 11, "MAX(SUM(A1:A2), A1*A2, POWER(A1, 2))");
 1514       ws.addCell(l);
 1515   
 1516       f = new Formula(2,12, "IF(A2>A1, \"A2 bigger\", \"A1 bigger\")");
 1517       ws.addCell(f);
 1518       l = new Label(3,12, "IF(A2>A1, \"A2 bigger\", \"A1 bigger\")");
 1519       ws.addCell(l);
 1520   
 1521       f = new Formula(2,13, "IF(A2<=A1, \"A2 smaller\", \"A1 smaller\")");
 1522       ws.addCell(f);
 1523       l = new Label(3,13, "IF(A2<=A1, \"A2 smaller\", \"A1 smaller\")");
 1524       ws.addCell(l);
 1525   
 1526       f = new Formula(2,14, "IF(A3<=10, \"<= 10\")");
 1527       ws.addCell(f);
 1528       l = new Label(3,14, "IF(A3<=10, \"<= 10\")");
 1529       ws.addCell(l);
 1530   
 1531       f = new Formula(2, 15, "SUM(1,2,3,4,5)");
 1532       ws.addCell(f);
 1533       l = new Label(3, 15, "SUM(1,2,3,4,5)");
 1534       ws.addCell(l);
 1535   
 1536       f = new Formula(2, 16, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", \"JExcelApi Home Page\")");
 1537       ws.addCell(f);
 1538       l = new Label(3, 16, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", \"JExcelApi Home Page\")");
 1539       ws.addCell(l);
 1540   
 1541       f = new Formula(2, 17, "3*4+5");
 1542       ws.addCell(f);
 1543       l = new Label(3, 17, "3*4+5");
 1544       ws.addCell(l);
 1545   
 1546       f = new Formula(2, 18, "\"Plain text formula\"");
 1547       ws.addCell(f);
 1548       l = new Label(3, 18, "Plain text formula");
 1549       ws.addCell(l);
 1550   
 1551       f = new Formula(2, 19, "SUM(a1,a2,-a3,a4)");
 1552       ws.addCell(f);
 1553       l = new Label(3, 19, "SUM(a1,a2,-a3,a4)");
 1554       ws.addCell(l);
 1555   
 1556       f = new Formula(2, 20, "2*-(a1+a2)");
 1557       ws.addCell(f);
 1558       l = new Label(3, 20, "2*-(a1+a2)");
 1559       ws.addCell(l);
 1560   
 1561       f = new Formula(2, 21, "'Number Formats'!B1/2");
 1562       ws.addCell(f);
 1563       l = new Label(3, 21, "'Number Formats'!B1/2");
 1564       ws.addCell(l);
 1565   
 1566       f = new Formula(2, 22, "IF(F22=0, 0, F21/F22)");
 1567       ws.addCell(f);
 1568       l = new Label(3, 22, "IF(F22=0, 0, F21/F22)");
 1569       ws.addCell(l);
 1570   
 1571       f = new Formula(2, 23, "RAND()");
 1572       ws.addCell(f);
 1573       l = new Label(3, 23, "RAND()");
 1574       ws.addCell(l);
 1575   
 1576       StringBuffer buf = new StringBuffer();
 1577       buf.append("'");
 1578       buf.append(workbook.getSheet(0).getName());
 1579       buf.append("'!");
 1580       buf.append(CellReferenceHelper.getCellReference(9, 18));
 1581       buf.append("*25");
 1582       f = new Formula(2, 24, buf.toString());
 1583       ws.addCell(f);
 1584       l = new Label(3, 24, buf.toString());
 1585       ws.addCell(l);
 1586   
 1587       wcf = new WritableCellFormat(DateFormats.DEFAULT);
 1588       f = new Formula(2, 25, "NOW()", wcf);
 1589       ws.addCell(f);
 1590       l = new Label(3, 25, "NOW()");
 1591       ws.addCell(l);
 1592   
 1593       f = new Formula(2, 26, "$A$2+A3");
 1594       ws.addCell(f);
 1595       l = new Label(3, 26, "$A$2+A3");
 1596       ws.addCell(l);
 1597   
 1598       f = new Formula(2, 27, "IF(COUNT(A1:A9,B1:B9)=0,\"\",COUNT(A1:A9,B1:B9))");
 1599       ws.addCell(f);
 1600       l = new Label(3, 27, "IF(COUNT(A1:A9,B1:B9)=0,\"\",COUNT(A1:A9,B1:B9))");
 1601       ws.addCell(l);
 1602   
 1603       f = new Formula(2, 28, "SUM(A1,A2,A3,A4)");
 1604       ws.addCell(f);
 1605       l = new Label(3, 28, "SUM(A1,A2,A3,A4)");
 1606       ws.addCell(l);
 1607   
 1608       l = new Label(1, 29, "a1");
 1609       ws.addCell(l);
 1610       f = new Formula(2, 29, "SUM(INDIRECT(ADDRESS(2,29)):A4)");
 1611       ws.addCell(f);
 1612       l = new Label(3, 29, "SUM(INDIRECT(ADDRESS(2,29):A4)");
 1613       ws.addCell(l);
 1614   
 1615       f = new Formula(2, 30, "COUNTIF(A1:A4, \">=12\")");
 1616       ws.addCell(f);
 1617       l = new Label(3, 30, "COUNTIF(A1:A4, \">=12\")");
 1618       ws.addCell(l);
 1619   
 1620       f = new Formula(2, 31, "MAX($A$1:$A$4)");
 1621       ws.addCell(f);
 1622       l = new Label(3, 31, "MAX($A$1:$A$4)");
 1623       ws.addCell(l);
 1624   
 1625       f = new Formula(2, 32, "OR(A1,TRUE)");
 1626       ws.addCell(f);
 1627       l = new Label(3, 32, "OR(A1,TRUE)");
 1628       ws.addCell(l);
 1629   
 1630       f = new Formula(2, 33, "ROWS(A1:C14)");
 1631       ws.addCell(f);
 1632       l = new Label(3, 33, "ROWS(A1:C14)");
 1633       ws.addCell(l);
 1634   
 1635       f = new Formula(2, 34, "COUNTBLANK(A1:C14)");
 1636       ws.addCell(f);
 1637       l = new Label(3, 34, "COUNTBLANK(A1:C14)");
 1638       ws.addCell(l);
 1639   
 1640       f = new Formula(2, 35, "IF(((F1=\"Not Found\")*(F2=\"Not Found\")*(F3=\"\")*(F4=\"\")*(F5=\"\")),1,0)");
 1641       ws.addCell(f);
 1642       l = new Label(3, 35, "IF(((F1=\"Not Found\")*(F2=\"Not Found\")*(F3=\"\")*(F4=\"\")*(F5=\"\")),1,0)");
 1643       ws.addCell(l);
 1644   
 1645       f = new Formula(2, 36, 
 1646          "HYPERLINK(\"http://www.amazon.co.uk/exec/obidos/ASIN/0571058086qid=1099836249/sr=1-3/ref=sr_1_11_3/202-6017285-1620664\",  \"Long hyperlink\")");
 1647       ws.addCell(f);
 1648   
 1649       f = new Formula(2, 37, "1234567+2699");
 1650       ws.addCell(f);
 1651       l = new Label(3, 37, "1234567+2699");
 1652       ws.addCell(l);
 1653   
 1654       f = new Formula(2, 38, "IF(ISERROR(G25/G29),0,-1)");
 1655       ws.addCell(f);
 1656       l = new Label(3, 38, "IF(ISERROR(G25/G29),0,-1)");
 1657       ws.addCell(l);
 1658   
 1659       f = new Formula(2, 39, "SEARCH(\"C\",D40)");
 1660       ws.addCell(f);
 1661       l = new Label(3, 39, "SEARCH(\"C\",D40)");
 1662       ws.addCell(l);
 1663   
 1664       f = new Formula(2, 40, "#REF!");
 1665       ws.addCell(f);
 1666       l = new Label(3, 40, "#REF!");
 1667       ws.addCell(l);
 1668   
 1669       nc = new Number (1,41, 79);
 1670       ws.addCell(nc);
 1671       f = new Formula(2, 41, "--B42");
 1672   		ws.addCell(f);
 1673       l = new Label(3, 41, "--B42");
 1674       ws.addCell(l);
 1675   
 1676       f = new Formula(2, 42, "CHOOSE(3,A1,A2,A3,A4");
 1677       ws.addCell(f);
 1678       l = new Label(3,42, "CHOOSE(3,A1,A2,A3,A4");
 1679       ws.addCell(l);
 1680   
 1681       f = new Formula(2, 43, "A4-A3-A2");
 1682       ws.addCell(f);
 1683       l = new Label(3,43, "A4-A3-A2");
 1684       ws.addCell(l);
 1685   
 1686       f = new Formula(2, 44, "F29+F34+F41+F48+F55+F62+F69+F76+F83+F90+F97+F104+F111+F118+F125+F132+F139+F146+F153+F160+F167+F174+F181+F188+F195+F202+F209+F216+F223+F230+F237+F244+F251+F258+F265+F272+F279+F286+F293+F300+F305+F308");
 1687       ws.addCell(f);
 1688       l = new Label(3, 44, "F29+F34+F41+F48+F55+F62+F69+F76+F83+F90+F97+F104+F111+F118+F125+F132+F139+F146+F153+F160+F167+F174+F181+F188+F195+F202+F209+F216+F223+F230+F237+F244+F251+F258+F265+F272+F279+F286+F293+F300+F305+F308");
 1689       ws.addCell(l);
 1690   
 1691       nc = new Number(1,45, 17);
 1692       ws.addCell(nc);
 1693       f = new Formula(2, 45, "formulavalue+5");
 1694       ws.addCell(f);
 1695       l = new Label(3, 45, "formulavalue+5");
 1696       ws.addCell(l);
 1697       
 1698       // Errors
 1699       /*
 1700       f = new Formula(2, 25, "PLOP(15)"); // unknown function
 1701       ws.addCell(f);
 1702   
 1703       f = new Formula(2, 26, "SUM(15,3"); // unmatched parentheses
 1704       ws.addCell(f);
 1705   
 1706       f = new Formula(2, 27, "SUM15,3)"); // missing opening parentheses
 1707       ws.addCell(f);
 1708   
 1709       f = new Formula(2, 28, "ROUND(3.14159)"); // missing args
 1710       ws.addCell(f);
 1711   
 1712       f = new Formula(2, 29, "NONSHEET!A1"); // sheet not found
 1713       ws.addCell(f);
 1714       */
 1715     }
 1716   
 1717     /**
 1718      * Write out the images
 1719      */
 1720     private void writeImageSheet(WritableSheet ws) throws WriteException
 1721     {
 1722       Label l = new Label(0, 0, "Weald & Downland Open Air Museum, Sussex");
 1723       ws.addCell(l);
 1724   
 1725       WritableImage wi = new WritableImage
 1726         (0, 3, 5, 7, new File("resources/wealdanddownland.png"));
 1727       ws.addImage(wi);
 1728   
 1729       l = new Label(0, 12, "Merchant Adventurers Hall, York");
 1730       ws.addCell(l);
 1731   
 1732       wi = new WritableImage(5, 12, 4, 10, 
 1733                              new File("resources/merchantadventurers.png"));
 1734       ws.addImage(wi);
 1735   
 1736       // An unsupported file type
 1737       /*
 1738         wi = new WritableImage(0, 60, 5, 5, new File("resources/somefile.gif"));
 1739         ws.addImage(wi);
 1740       */
 1741     }
 1742   }
 1743   
 1744   
 1745   
 1746   
 1747   
 1748   
 1749   
 1750   

Save This Page
Home » JCraft » jxl » demo » [javadoc | source]