/**
* Adds cells to the specified sheet which test the various label formatting
* styles, such as different fonts, different sizes and bold, underline etc.
*
* @param s1
*/
private void writeLabelFormatSheet(WritableSheet s1)
{
s1.setColumnView(0, 60);
Label lr = new Label(0, 0, "Arial Fonts");
s1.addCell(lr);
lr = new Label(1, 0, "10pt");
s1.addCell(lr);
lr = new Label(2, 0, "Normal");
s1.addCell(lr);
lr = new Label(3, 0, "12pt");
s1.addCell(lr);
WritableFont arial12pt = new WritableFont(WritableFont.ARIAL, 12);
WritableCellFormat arial12format = new WritableCellFormat(arial12pt);
arial12format.setWrap(true);
lr = new Label(4, 0, "Normal", arial12format);
s1.addCell(lr);
WritableFont arial10ptBold = new WritableFont
(WritableFont.ARIAL, 10, WritableFont.BOLD);
WritableCellFormat arial10BoldFormat = new WritableCellFormat
(arial10ptBold);
lr = new Label(2, 2, "BOLD", arial10BoldFormat);
s1.addCell(lr);
WritableFont arial12ptBold = new WritableFont
(WritableFont.ARIAL, 12, WritableFont.BOLD);
WritableCellFormat arial12BoldFormat = new WritableCellFormat
(arial12ptBold);
lr = new Label(4, 2, "BOLD", arial12BoldFormat);
s1.addCell(lr);
WritableFont arial10ptItalic = new WritableFont
(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, true);
WritableCellFormat arial10ItalicFormat = new WritableCellFormat
(arial10ptItalic);
lr = new Label(2, 4, "Italic", arial10ItalicFormat);
s1.addCell(lr);
WritableFont arial12ptItalic = new WritableFont
(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, true);
WritableCellFormat arial12ptItalicFormat = new WritableCellFormat
(arial12ptItalic);
lr = new Label(4, 4, "Italic", arial12ptItalicFormat);
s1.addCell(lr);
WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
WritableCellFormat times10format = new WritableCellFormat(times10pt);
lr = new Label(0, 7, "Times Fonts", times10format);
s1.addCell(lr);
lr = new Label(1, 7, "10pt", times10format);
s1.addCell(lr);
lr = new Label(2, 7, "Normal", times10format);
s1.addCell(lr);
lr = new Label(3, 7, "12pt", times10format);
s1.addCell(lr);
WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
WritableCellFormat times12format = new WritableCellFormat(times12pt);
lr = new Label(4, 7, "Normal", times12format);
s1.addCell(lr);
WritableFont times10ptBold = new WritableFont
(WritableFont.TIMES, 10, WritableFont.BOLD);
WritableCellFormat times10BoldFormat = new WritableCellFormat
(times10ptBold);
lr = new Label(2, 9, "BOLD", times10BoldFormat);
s1.addCell(lr);
WritableFont times12ptBold = new WritableFont
(WritableFont.TIMES, 12, WritableFont.BOLD);
WritableCellFormat times12BoldFormat = new WritableCellFormat
(times12ptBold);
lr = new Label(4, 9, "BOLD", times12BoldFormat);
s1.addCell(lr);
// The underline styles
s1.setColumnView(6, 22);
s1.setColumnView(7, 22);
s1.setColumnView(8, 22);
s1.setColumnView(9, 22);
lr = new Label(0, 11, "Underlining");
s1.addCell(lr);
WritableFont arial10ptUnderline = new WritableFont
(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.SINGLE);
WritableCellFormat arialUnderline = new WritableCellFormat
(arial10ptUnderline);
lr = new Label(6, 11, "Underline", arialUnderline);
s1.addCell(lr);
WritableFont arial10ptDoubleUnderline = new WritableFont
(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.DOUBLE);
WritableCellFormat arialDoubleUnderline = new WritableCellFormat
(arial10ptDoubleUnderline);
lr = new Label(7, 11, "Double Underline", arialDoubleUnderline);
s1.addCell(lr);
WritableFont arial10ptSingleAcc = new WritableFont
(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.SINGLE_ACCOUNTING);
WritableCellFormat arialSingleAcc = new WritableCellFormat
(arial10ptSingleAcc);
lr = new Label(8, 11, "Single Accounting Underline", arialSingleAcc);
s1.addCell(lr);
WritableFont arial10ptDoubleAcc = new WritableFont
(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.DOUBLE_ACCOUNTING);
WritableCellFormat arialDoubleAcc = new WritableCellFormat
(arial10ptDoubleAcc);
lr = new Label(9, 11, "Double Accounting Underline", arialDoubleAcc);
s1.addCell(lr);
WritableFont times14ptBoldUnderline = new WritableFont
(WritableFont.TIMES,
14,
WritableFont.BOLD,
false,
UnderlineStyle.SINGLE);
WritableCellFormat timesBoldUnderline = new WritableCellFormat
(times14ptBoldUnderline);
lr = new Label(6, 12, "Times 14 Bold Underline", timesBoldUnderline);
s1.addCell(lr);
WritableFont arial18ptBoldItalicUnderline = new WritableFont
(WritableFont.ARIAL,
18,
WritableFont.BOLD,
true,
UnderlineStyle.SINGLE);
WritableCellFormat arialBoldItalicUnderline = new WritableCellFormat
(arial18ptBoldItalicUnderline);
lr = new Label(6, 13, "Arial 18 Bold Italic Underline",
arialBoldItalicUnderline);
s1.addCell(lr);
lr = new Label(0, 15, "Script styles");
s1.addCell(lr);
WritableFont superscript = new WritableFont
(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
Colour.BLACK,
ScriptStyle.SUPERSCRIPT);
WritableCellFormat superscriptFormat = new WritableCellFormat
(superscript);
lr = new Label(1, 15, "superscript", superscriptFormat);
s1.addCell(lr);
WritableFont subscript = new WritableFont
(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
Colour.BLACK,
ScriptStyle.SUBSCRIPT);
WritableCellFormat subscriptFormat = new WritableCellFormat
(subscript);
lr = new Label(2, 15, "subscript", subscriptFormat);
s1.addCell(lr);
lr = new Label(0, 17, "Colours");
s1.addCell(lr);
WritableFont red = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
Colour.RED);
WritableCellFormat redFormat = new WritableCellFormat(red);
lr = new Label(2, 17, "Red", redFormat);
s1.addCell(lr);
WritableFont blue = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
Colour.BLUE);
WritableCellFormat blueFormat = new WritableCellFormat(blue);
lr = new Label(2, 18, "Blue", blueFormat);
s1.addCell(lr);
WritableFont lime = new WritableFont(WritableFont.ARIAL);
lime.setColour(Colour.LIME);
WritableCellFormat limeFormat = new WritableCellFormat(lime);
limeFormat.setWrap(true);
lr = new Label(4, 18, "Modified palette - was lime, now red", limeFormat);
s1.addCell(lr);
WritableCellFormat greyBackground = new WritableCellFormat();
greyBackground.setWrap(true);
greyBackground.setBackground(Colour.GRAY_50);
lr = new Label(2, 19, "Grey background", greyBackground);
s1.addCell(lr);
WritableFont yellow = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
Colour.YELLOW);
WritableCellFormat yellowOnBlue = new WritableCellFormat(yellow);
yellowOnBlue.setWrap(true);
yellowOnBlue.setBackground(Colour.BLUE);
lr = new Label(2, 20, "Blue background, yellow foreground", yellowOnBlue);
s1.addCell(lr);
WritableCellFormat yellowOnBlack = new WritableCellFormat(yellow);
yellowOnBlack.setWrap(true);
yellowOnBlack.setBackground(Colour.PALETTE_BLACK);
lr = new Label(3, 20, "Black background, yellow foreground",
yellowOnBlack);
s1.addCell(lr);
lr = new Label(0, 22, "Null label");
s1.addCell(lr);
lr = new Label(2, 22, null);
s1.addCell(lr);
lr = new Label(0, 24,
"A very long label, more than 255 characters\012" +
"Rejoice O shores\012" +
"Sing O bells\012" +
"But I with mournful tread\012" +
"Walk the deck my captain lies\012" +
"Fallen cold and dead\012" +
"Summer surprised, coming over the Starnbergersee\012" +
"With a shower of rain. We stopped in the Colonnade\012" +
"A very long label, more than 255 characters\012" +
"Rejoice O shores\012" +
"Sing O bells\012" +
"But I with mournful tread\012" +
"Walk the deck my captain lies\012" +
"Fallen cold and dead\012" +
"Summer surprised, coming over the Starnbergersee\012" +
"With a shower of rain. We stopped in the Colonnade\012" + "A very long label, more than 255 characters\012" +
"Rejoice O shores\012" +
"Sing O bells\012" +
"But I with mournful tread\012" +
"Walk the deck my captain lies\012" +
"Fallen cold and dead\012" +
"Summer surprised, coming over the Starnbergersee\012" +
"With a shower of rain. We stopped in the Colonnade\012" + "A very long label, more than 255 characters\012" +
"Rejoice O shores\012" +
"Sing O bells\012" +
"But I with mournful tread\012" +
"Walk the deck my captain lies\012" +
"Fallen cold and dead\012" +
"Summer surprised, coming over the Starnbergersee\012" +
"With a shower of rain. We stopped in the Colonnade\012" +
"And sat and drank coffee an talked for an hour\012",
arial12format);
s1.addCell(lr);
WritableCellFormat vertical = new WritableCellFormat();
vertical.setOrientation(Orientation.VERTICAL);
lr = new Label(0, 26, "Vertical orientation", vertical);
s1.addCell(lr);
WritableCellFormat plus_90 = new WritableCellFormat();
plus_90.setOrientation(Orientation.PLUS_90);
lr = new Label(1, 26, "Plus 90", plus_90);
s1.addCell(lr);
WritableCellFormat minus_90 = new WritableCellFormat();
minus_90.setOrientation(Orientation.MINUS_90);
lr = new Label(2, 26, "Minus 90", minus_90);
s1.addCell(lr);
lr = new Label(0, 28, "Modified row height");
s1.addCell(lr);
s1.setRowView(28, 24 * 20);
lr = new Label(0, 29, "Collapsed row");
s1.addCell(lr);
s1.setRowView(29, true);
// Write hyperlinks
Label l;
try
{
l = new Label(0, 30, "Hyperlink to home page");
s1.addCell(l);
Uri url = new Uri("http://www.andykhan.com/jexcelapi");
WritableHyperlink wh = new WritableHyperlink(0, 30, 8, 31, url);
s1.addHyperlink(wh);
// The below hyperlink clashes with above
WritableHyperlink wh2 = new WritableHyperlink(7, 30, 9, 31, url);
s1.addHyperlink(wh2);
l = new Label(4, 2, "File hyperlink to documentation");
s1.addCell(l);
FileInfo file = new FileInfo("../jexcelapi/docs/index.html");
wh = new WritableHyperlink(0, 32, 8, 32, file, "JExcelApi Documentation");
s1.addHyperlink(wh);
// Add a hyperlink to another cell on this sheet
wh = new WritableHyperlink(0, 34, 8, 34,
"Link to another cell",
s1,
0, 180, 1, 181);
s1.addHyperlink(wh);
file = new FileInfo("\\\\localhost\\file.txt");
wh = new WritableHyperlink(0, 36, 8, 36, file);
s1.addHyperlink(wh);
// Add a very long hyperlink
url = new Uri("http://www.amazon.co.uk/exec/obidos/ASIN/0571058086" +
"/qid=1099836249/sr=1-3/ref=sr_1_11_3/202-6017285-1620664");
wh = new WritableHyperlink(0, 38, 0, 38, url);
s1.addHyperlink(wh);
}
catch (Exception e)
{
Console.WriteLine(e);
}
// Write out some merged cells
l = new Label(5, 35, "Merged cells", timesBoldUnderline);
s1.mergeCells(5, 35, 8, 37);
s1.addCell(l);
l = new Label(5, 38, "More merged cells");
s1.addCell(l);
Range r = s1.mergeCells(5, 38, 8, 41);
s1.insertRow(40);
s1.removeRow(39);
s1.unmergeCells(r);
// Merge cells and centre across them
WritableCellFormat wcf = new WritableCellFormat();
wcf.setAlignment(Alignment.CENTRE);
l = new Label(5, 42, "Centred across merged cells", wcf);
s1.addCell(l);
s1.mergeCells(5, 42, 10, 42);
wcf = new WritableCellFormat();
wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
wcf.setBackground(Colour.GRAY_25);
l = new Label(3, 44, "Merged with border", wcf);
s1.addCell(l);
s1.mergeCells(3, 44, 4, 46);
// Clash some ranges - the second range will not be added
// Also merge some cells with two data items in the - the second data
// item will not be merged
/*
l = new Label(5, 16, "merged cells");
s1.addCell(l);
Label l5 = new Label(7, 17, "this label won't appear");
s1.addCell(l5);
s1.mergeCells(5, 16, 8, 18);
s1.mergeCells(5, 19, 6, 24);
s1.mergeCells(6, 18, 10, 19);
*/
WritableFont courier10ptFont = new WritableFont(WritableFont.COURIER, 10);
WritableCellFormat courier10pt = new WritableCellFormat(courier10ptFont);
l = new Label(0, 49, "Courier fonts", courier10pt);
s1.addCell(l);
WritableFont tahoma12ptFont = new WritableFont(WritableFont.TAHOMA, 12);
WritableCellFormat tahoma12pt = new WritableCellFormat(tahoma12ptFont);
l = new Label(0, 50, "Tahoma fonts", tahoma12pt);
s1.addCell(l);
WritableFont.FontName wingdingsFont =
WritableFont.createFont("Wingdings 2");
WritableFont wingdings210ptFont = new WritableFont(wingdingsFont, 10);
WritableCellFormat wingdings210pt = new WritableCellFormat
(wingdings210ptFont);
l = new Label(0, 51, "Bespoke Windgdings 2", wingdings210pt);
s1.addCell(l);
WritableCellFormat shrinkToFit = new WritableCellFormat(times12pt);
shrinkToFit.setShrinkToFit(true);
l = new Label(3, 53, "Shrunk to fit", shrinkToFit);
s1.addCell(l);
l = new Label(3, 55, "Some long wrapped text in a merged cell",
arial12format);
s1.addCell(l);
s1.mergeCells(3, 55, 4, 55);
l = new Label(0, 57, "A cell with a comment");
WritableCellFeatures cellFeatures = new WritableCellFeatures();
cellFeatures.setComment("the cell comment");
l.setCellFeatures(cellFeatures);
s1.addCell(l);
l = new Label(0, 59,
"A cell with a long comment");
cellFeatures = new WritableCellFeatures();
cellFeatures.setComment("a very long cell comment indeed that won't " +
"fit inside a standard comment box, so a " +
"larger comment box is used instead",
5, 6);
l.setCellFeatures(cellFeatures);
s1.addCell(l);
WritableCellFormat indented = new WritableCellFormat(times12pt);
indented.setIndentation(4);
l = new Label(0, 61, "Some indented text", indented);
s1.addCell(l);
l = new Label(0, 63, "Data validation: list");
s1.addCell(l);
Blank b = new Blank(1, 63);
cellFeatures = new WritableCellFeatures();
ArrayList al = new ArrayList();
al.Add("bagpuss");
al.Add("clangers");
al.Add("ivor the engine");
al.Add("noggin the nog");
cellFeatures.setDataValidationList(al);
b.setCellFeatures(cellFeatures);
s1.addCell(b);
l = new Label(0, 64, "Data validation: number > 4.5");
s1.addCell(l);
b = new Blank(1, 64);
cellFeatures = new WritableCellFeatures();
cellFeatures.setNumberValidation(4.5, WritableCellFeatures.GREATER_THAN);
b.setCellFeatures(cellFeatures);
s1.addCell(b);
l = new Label(0, 65, "Data validation: named range");
s1.addCell(l);
l = new Label(4, 65, "tiger");
s1.addCell(l);
l = new Label(5, 65, "sword");
s1.addCell(l);
l = new Label(6, 65, "honour");
s1.addCell(l);
l = new Label(7, 65, "company");
s1.addCell(l);
l = new Label(8, 65, "victory");
s1.addCell(l);
l = new Label(9, 65, "fortress");
s1.addCell(l);
b = new Blank(1, 65);
cellFeatures = new WritableCellFeatures();
cellFeatures.setDataValidationRange("validation_range");
b.setCellFeatures(cellFeatures);
s1.addCell(b);
// Set the row grouping
s1.setRowGroup(39, 45, false);
// s1.setRowGroup(72, 74, true);
l = new Label(0, 66, "Block of cells B67-F71 with data validation");
s1.addCell(l);
al = new ArrayList();
al.Add("Achilles");
al.Add("Agamemnon");
al.Add("Hector");
al.Add("Odysseus");
al.Add("Patroclus");
al.Add("Nestor");
b = new Blank(1, 66);
cellFeatures = new WritableCellFeatures();
cellFeatures.setDataValidationList(al);
b.setCellFeatures(cellFeatures);
s1.addCell(b);
s1.applySharedDataValidation(b, 4, 4);
cellFeatures = new WritableCellFeatures();
cellFeatures.setDataValidationRange("");
l = new Label(0, 71, "Read only cell using empty data validation");
l.setCellFeatures(cellFeatures);
s1.addCell(l);
// Set the row grouping
s1.setRowGroup(39, 45, false);
// s1.setRowGroup(72, 74, true);
}
/**
* Test out the formula parser
*/
private void writeFormulaSheet(WritableSheet ws)
{
// Add some cells to manipulate
Number nc = new Number(0, 0, 15);
ws.addCell(nc);
nc = new Number(0, 1, 16);
ws.addCell(nc);
nc = new Number(0, 2, 10);
ws.addCell(nc);
nc = new Number(0, 3, 12);
ws.addCell(nc);
ws.setColumnView(2, 20);
WritableCellFormat wcf = new WritableCellFormat();
wcf.setAlignment(Alignment.RIGHT);
wcf.setWrap(true);
CellView cv = new CellView();
cv.setSize(25 * 256);
cv.setFormat(wcf);
ws.setColumnView(3, cv);
// Add in the formulas
Formula f = null;
Label l = null;
f = new Formula(2, 0, "A1+A2");
ws.addCell(f);
l = new Label(3, 0, "a1+a2");
ws.addCell(l);
f = new Formula(2, 1, "A2 * 3");
ws.addCell(f);
l = new Label(3, 1, "A2 * 3");
ws.addCell(l);
f = new Formula(2, 2, "A2+A1/2.5");
ws.addCell(f);
l = new Label(3, 2, "A2+A1/2.5");
ws.addCell(l);
f = new Formula(2, 3, "3+(a1+a2)/2.5");
ws.addCell(f);
l = new Label(3, 3, "3+(a1+a2)/2.5");
ws.addCell(l);
f = new Formula(2, 4, "(a1+a2)/2.5");
ws.addCell(f);
l = new Label(3, 4, "(a1+a2)/2.5");
ws.addCell(l);
f = new Formula(2, 5, "15+((a1+a2)/2.5)*17");
ws.addCell(f);
l = new Label(3, 5, "15+((a1+a2)/2.5)*17");
ws.addCell(l);
f = new Formula(2, 6, "SUM(a1:a4)");
ws.addCell(f);
l = new Label(3, 6, "SUM(a1:a4)");
ws.addCell(l);
f = new Formula(2, 7, "SUM(a1:a4)/4");
ws.addCell(f);
l = new Label(3, 7, "SUM(a1:a4)/4");
ws.addCell(l);
f = new Formula(2, 8, "AVERAGE(A1:A4)");
ws.addCell(f);
l = new Label(3, 8, "AVERAGE(a1:a4)");
ws.addCell(l);
f = new Formula(2, 9, "MIN(5,4,1,2,3)");
ws.addCell(f);
l = new Label(3, 9, "MIN(5,4,1,2,3)");
ws.addCell(l);
f = new Formula(2, 10, "ROUND(3.14159265, 3)");
ws.addCell(f);
l = new Label(3, 10, "ROUND(3.14159265, 3)");
ws.addCell(l);
f = new Formula(2, 11, "MAX(SUM(A1:A2), A1*A2, POWER(A1, 2))");
ws.addCell(f);
l = new Label(3, 11, "MAX(SUM(A1:A2), A1*A2, POWER(A1, 2))");
ws.addCell(l);
f = new Formula(2, 12, "IF(A2>A1, \"A2 bigger\", \"A1 bigger\")");
ws.addCell(f);
l = new Label(3, 12, "IF(A2>A1, \"A2 bigger\", \"A1 bigger\")");
ws.addCell(l);
f = new Formula(2, 13, "IF(A2<=A1, \"A2 smaller\", \"A1 smaller\")");
ws.addCell(f);
l = new Label(3, 13, "IF(A2<=A1, \"A2 smaller\", \"A1 smaller\")");
ws.addCell(l);
f = new Formula(2, 14, "IF(A3<=10, \"<= 10\")");
ws.addCell(f);
l = new Label(3, 14, "IF(A3<=10, \"<= 10\")");
ws.addCell(l);
f = new Formula(2, 15, "SUM(1,2,3,4,5)");
ws.addCell(f);
l = new Label(3, 15, "SUM(1,2,3,4,5)");
ws.addCell(l);
f = new Formula(2, 16, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", \"JExcelApi Home Page\")");
ws.addCell(f);
l = new Label(3, 16, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", \"JExcelApi Home Page\")");
ws.addCell(l);
f = new Formula(2, 17, "3*4+5");
ws.addCell(f);
l = new Label(3, 17, "3*4+5");
ws.addCell(l);
f = new Formula(2, 18, "\"Plain text formula\"");
ws.addCell(f);
l = new Label(3, 18, "Plain text formula");
ws.addCell(l);
f = new Formula(2, 19, "SUM(a1,a2,-a3,a4)");
ws.addCell(f);
l = new Label(3, 19, "SUM(a1,a2,-a3,a4)");
ws.addCell(l);
f = new Formula(2, 20, "2*-(a1+a2)");
ws.addCell(f);
l = new Label(3, 20, "2*-(a1+a2)");
ws.addCell(l);
f = new Formula(2, 21, "'Number Formats'!B1/2");
ws.addCell(f);
l = new Label(3, 21, "'Number Formats'!B1/2");
ws.addCell(l);
f = new Formula(2, 22, "IF(F22=0, 0, F21/F22)");
ws.addCell(f);
l = new Label(3, 22, "IF(F22=0, 0, F21/F22)");
ws.addCell(l);
f = new Formula(2, 23, "RAND()");
ws.addCell(f);
l = new Label(3, 23, "RAND()");
ws.addCell(l);
StringBuilder buf = new StringBuilder();
buf.Append("'");
buf.Append(workbook.getSheet(0).getName());
buf.Append("'!");
buf.Append(CellReferenceHelper.getCellReference(9, 18));
buf.Append("*25");
f = new Formula(2, 24, buf.ToString());
ws.addCell(f);
l = new Label(3, 24, buf.ToString());
ws.addCell(l);
wcf = new WritableCellFormat(DateFormats.DEFAULT);
f = new Formula(2, 25, "NOW()", wcf);
ws.addCell(f);
l = new Label(3, 25, "NOW()");
ws.addCell(l);
f = new Formula(2, 26, "$A$2+A3");
ws.addCell(f);
l = new Label(3, 26, "$A$2+A3");
ws.addCell(l);
f = new Formula(2, 27, "IF(COUNT(A1:A9,B1:B9)=0,\"\",COUNT(A1:A9,B1:B9))");
ws.addCell(f);
l = new Label(3, 27, "IF(COUNT(A1:A9,B1:B9)=0,\"\",COUNT(A1:A9,B1:B9))");
ws.addCell(l);
f = new Formula(2, 28, "SUM(A1,A2,A3,A4)");
ws.addCell(f);
l = new Label(3, 28, "SUM(A1,A2,A3,A4)");
ws.addCell(l);
l = new Label(1, 29, "a1");
ws.addCell(l);
f = new Formula(2, 29, "SUM(INDIRECT(ADDRESS(2,29)):A4)");
ws.addCell(f);
l = new Label(3, 29, "SUM(INDIRECT(ADDRESS(2,29):A4)");
ws.addCell(l);
f = new Formula(2, 30, "COUNTIF(A1:A4, \">=12\")");
ws.addCell(f);
l = new Label(3, 30, "COUNTIF(A1:A4, \">=12\")");
ws.addCell(l);
f = new Formula(2, 31, "MAX($A$1:$A$4)");
ws.addCell(f);
l = new Label(3, 31, "MAX($A$1:$A$4)");
ws.addCell(l);
f = new Formula(2, 32, "OR(A1,TRUE)");
ws.addCell(f);
l = new Label(3, 32, "OR(A1,TRUE)");
ws.addCell(l);
f = new Formula(2, 33, "ROWS(A1:C14)");
ws.addCell(f);
l = new Label(3, 33, "ROWS(A1:C14)");
ws.addCell(l);
f = new Formula(2, 34, "COUNTBLANK(A1:C14)");
ws.addCell(f);
l = new Label(3, 34, "COUNTBLANK(A1:C14)");
ws.addCell(l);
f = new Formula(2, 35, "IF(((F1=\"Not Found\")*(F2=\"Not Found\")*(F3=\"\")*(F4=\"\")*(F5=\"\")),1,0)");
ws.addCell(f);
l = new Label(3, 35, "IF(((F1=\"Not Found\")*(F2=\"Not Found\")*(F3=\"\")*(F4=\"\")*(F5=\"\")),1,0)");
ws.addCell(l);
f = new Formula(2, 36,
"HYPERLINK(\"http://www.amazon.co.uk/exec/obidos/ASIN/0571058086qid=1099836249/sr=1-3/ref=sr_1_11_3/202-6017285-1620664\", \"Long hyperlink\")");
ws.addCell(f);
f = new Formula(2, 37, "1234567+2699");
ws.addCell(f);
l = new Label(3, 37, "1234567+2699");
ws.addCell(l);
f = new Formula(2, 38, "IF(ISERROR(G25/G29),0,-1)");
ws.addCell(f);
l = new Label(3, 38, "IF(ISERROR(G25/G29),0,-1)");
ws.addCell(l);
f = new Formula(2, 39, "SEARCH(\"C\",D40)");
ws.addCell(f);
l = new Label(3, 39, "SEARCH(\"C\",D40)");
ws.addCell(l);
f = new Formula(2, 40, "#REF!");
ws.addCell(f);
l = new Label(3, 40, "#REF!");
ws.addCell(l);
nc = new Number(1, 41, 79);
ws.addCell(nc);
f = new Formula(2, 41, "--B42");
ws.addCell(f);
l = new Label(3, 41, "--B42");
ws.addCell(l);
f = new Formula(2, 42, "CHOOSE(3,A1,A2,A3,A4");
ws.addCell(f);
l = new Label(3, 42, "CHOOSE(3,A1,A2,A3,A4");
ws.addCell(l);
f = new Formula(2, 43, "A4-A3-A2");
ws.addCell(f);
l = new Label(3, 43, "A4-A3-A2");
ws.addCell(l);
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");
ws.addCell(f);
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");
ws.addCell(l);
nc = new Number(1, 45, 17);
ws.addCell(nc);
f = new Formula(2, 45, "formulavalue+5");
ws.addCell(f);
l = new Label(3, 45, "formulavalue+5");
ws.addCell(l);
// Errors
/*
f = new Formula(2, 25, "PLOP(15)"); // unknown function
ws.addCell(f);
f = new Formula(2, 26, "SUM(15,3"); // unmatched parentheses
ws.addCell(f);
f = new Formula(2, 27, "SUM15,3)"); // missing opening parentheses
ws.addCell(f);
f = new Formula(2, 28, "ROUND(3.14159)"); // missing args
ws.addCell(f);
f = new Formula(2, 29, "NONSHEET!A1"); // sheet not found
ws.addCell(f);
*/
}
