/**
* 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);
*/
}
/**
* If the inputFile was the test spreadsheet, then it modifies certain fields
* of the writable copy
*
* @param w
*/
private void modify(WritableWorkbook w)
{
Console.WriteLine("Modifying...");
WritableSheet sheet = w.getSheet("modified");
WritableCell cell = null;
CellFormat cf = null;
Label l = null;
WritableCellFeatures wcf = null;
// Change the format of cell B4 to be emboldened
cell = sheet.getWritableCell(1, 3);
WritableFont bold = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.BOLD);
cf = new WritableCellFormat(bold);
cell.setCellFormat(cf);
// Change the format of cell B5 to be underlined
cell = sheet.getWritableCell(1, 4);
WritableFont underline = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.SINGLE);
cf = new WritableCellFormat(underline);
cell.setCellFormat(cf);
// Change the point size of cell B6 to be 10 point
cell = sheet.getWritableCell(1, 5);
WritableFont tenpoint = new WritableFont(WritableFont.ARIAL, 10);
cf = new WritableCellFormat(tenpoint);
cell.setCellFormat(cf);
// Change the contents of cell B7 to read "Label - mod"
cell = sheet.getWritableCell(1, 6);
if (cell.getType() == CellType.LABEL)
{
Label lc = (Label)cell;
lc.setString(lc.getString() + " - mod");
}
// Change cell B10 to display 7 dps
cell = sheet.getWritableCell(1, 9);
NumberFormat sevendps = new NumberFormat("#.0000000");
cf = new WritableCellFormat(sevendps);
cell.setCellFormat(cf);
// Change cell B11 to display in the format 1e4
cell = sheet.getWritableCell(1, 10);
NumberFormat exp4 = new NumberFormat("0.####E0");
cf = new WritableCellFormat(exp4);
cell.setCellFormat(cf);
// Change cell B12 to be normal display
cell = sheet.getWritableCell(1, 11);
cell.setCellFormat(WritableWorkbook.NORMAL_STYLE);
// Change the contents of cell B13 to 42
cell = sheet.getWritableCell(1, 12);
if (cell.getType() == CellType.NUMBER)
{
Number n2 = (Number)cell;
n2.setValue(42);
}
// Add 0.1 to the contents of cell B14
cell = sheet.getWritableCell(1, 13);
if (cell.getType() == CellType.NUMBER)
{
Number n3 = (Number)cell;
n3.setValue(n3.getValue() + 0.1);
}
// Change the date format of cell B17 to be a custom format
cell = sheet.getWritableCell(1, 16);
DateFormat df = new DateFormat("dd MMM yyyy HH:mm:ss");
cf = new WritableCellFormat(df);
cell.setCellFormat(cf);
// Change the date format of cell B18 to be a standard format
cell = sheet.getWritableCell(1, 17);
cf = new WritableCellFormat(DateFormats.FORMAT9);
cell.setCellFormat(cf);
// Change the date in cell B19 to be 18 Feb 1998, 11:23:28
cell = sheet.getWritableCell(1, 18);
if (cell.getType() == CellType.DATE)
{
// TODO: fix this....
//DateTime dt = (DateTime)cell;
//Calendar cal = Calendar.getInstance();
//cal.set(1998, 1, 18, 11, 23, 28);
//Date d = cal.getTime();
//dt.setDate(d);
}
// Change the value in B23 to be 6.8. This should recalculate the
// formula
cell = sheet.getWritableCell(1, 22);
if (cell.getType() == CellType.NUMBER)
{
Number n1 = (Number)cell;
n1.setValue(6.8);
}
// Change the label in B30. This will have the effect of making
// the original string unreferenced
cell = sheet.getWritableCell(1, 29);
if (cell.getType() == CellType.LABEL)
{
l = (Label)cell;
l.setString("Modified string contents");
}
// Insert a new row (number 35)
sheet.insertRow(34);
// Delete row 38 (39 after row has been inserted)
sheet.removeRow(38);
// Insert a new column (J)
sheet.insertColumn(9);
// Remove a column (L - M after column has been inserted)
sheet.removeColumn(11);
// Remove row 44 (contains a hyperlink), and then insert an empty
// row just to keep the numbers consistent
sheet.removeRow(43);
sheet.insertRow(43);
// Modify the hyperlinks
WritableHyperlink[] hyperlinks = sheet.getWritableHyperlinks();
for (int i = 0; i < hyperlinks.Length; i++)
{
WritableHyperlink wh = hyperlinks[i];
if (wh.getColumn() == 1 && wh.getRow() == 39)
{
try
{
// Change the hyperlink that begins in cell B40 to be a different API
wh.setURL(new Uri("http://www.andykhan.com/jexcelapi/index.html"));
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
else if (wh.getColumn() == 1 && wh.getRow() == 40)
{
wh.setFile(new FileInfo("../jexcelapi/docs/overview-summary.html"));
}
else if (wh.getColumn() == 1 && wh.getRow() == 41)
{
wh.setFile(new FileInfo("d:/home/jexcelapi/docs/jxl/package-summary.html"));
}
else if (wh.getColumn() == 1 && wh.getRow() == 44)
{
// Remove the hyperlink at B45
sheet.removeHyperlink(wh);
}
}
// Change the background of cell F31 from blue to red
WritableCell c = sheet.getWritableCell(5, 30);
WritableCellFormat newFormat = new WritableCellFormat(c.getCellFormat());
newFormat.setBackground(Colour.RED);
c.setCellFormat(newFormat);
// Modify the contents of the merged cell
l = new Label(0, 49, "Modified merged cells");
sheet.addCell(l);
// Modify the chart data
Number n = (Number)sheet.getWritableCell(0, 70);
n.setValue(9);
n = (Number)sheet.getWritableCell(0, 71);
n.setValue(10);
n = (Number)sheet.getWritableCell(0, 73);
n.setValue(4);
// Add in a cross sheet formula
Formula f = new Formula(1, 80, "ROUND(COS(original!B10),2)");
sheet.addCell(f);
// Add in a formula from the named cells
f = new Formula(1, 83, "value1+value2");
sheet.addCell(f);
// Add in a function formula using named cells
f = new Formula(1, 84, "AVERAGE(value1,value1*4,value2)");
sheet.addCell(f);
// Copy sheet 1 to sheet 3
// w.copySheet(0, "copy", 2);
// Use the cell deep copy feature
Label label = new Label(0, 88, "Some copied cells", cf);
sheet.addCell(label);
label = new Label(0, 89, "Number from B9");
sheet.addCell(label);
WritableCell wc = sheet.getWritableCell(1, 9).copyTo(1, 89);
sheet.addCell(wc);
label = new Label(0, 90, "Label from B4 (modified format)");
sheet.addCell(label);
wc = sheet.getWritableCell(1, 3).copyTo(1, 90);
sheet.addCell(wc);
label = new Label(0, 91, "Date from B17");
sheet.addCell(label);
wc = sheet.getWritableCell(1, 16).copyTo(1, 91);
sheet.addCell(wc);
label = new Label(0, 92, "Boolean from E16");
sheet.addCell(label);
wc = sheet.getWritableCell(4, 15).copyTo(1, 92);
sheet.addCell(wc);
label = new Label(0, 93, "URL from B40");
sheet.addCell(label);
wc = sheet.getWritableCell(1, 39).copyTo(1, 93);
sheet.addCell(wc);
// Add some numbers for the formula copy
for (int i = 0; i < 6; i++)
{
Number number = new Number(1, 94 + i, i + 1 + i / 8.0);
sheet.addCell(number);
}
label = new Label(0, 100, "Formula from B27");
sheet.addCell(label);
wc = sheet.getWritableCell(1, 26).copyTo(1, 100);
sheet.addCell(wc);
label = new Label(0, 101, "A brand new formula");
sheet.addCell(label);
Formula formula = new Formula(1, 101, "SUM(B94:B96)");
sheet.addCell(formula);
label = new Label(0, 102, "A copy of it");
sheet.addCell(label);
wc = sheet.getWritableCell(1, 101).copyTo(1, 102);
sheet.addCell(wc);
// Remove the second image from the sheet
WritableImage wi = sheet.getImage(1);
sheet.removeImage(wi);
wi = new WritableImage(1, 116, 2, 9, new FileInfo("resources/littlemoretonhall.png"));
sheet.addImage(wi);
// Add a list data validations
label = new Label(0, 151, "Added drop down validation");
sheet.addCell(label);
Blank b = new Blank(1, 151);
wcf = new WritableCellFeatures();
ArrayList al = new ArrayList();
al.Add("The Fellowship of the Ring");
al.Add("The Two Towers");
al.Add("The Return of the King");
wcf.setDataValidationList(al);
b.setCellFeatures(wcf);
sheet.addCell(b);
// Add a number data validation
label = new Label(0, 152, "Added number validation 2.718 < x < 3.142");
sheet.addCell(label);
b = new Blank(1, 152);
wcf = new WritableCellFeatures();
wcf.setNumberValidation(2.718, 3.142, WritableCellFeatures.BETWEEN);
b.setCellFeatures(wcf);
sheet.addCell(b);
// Modify the text in the first cell with a comment
cell = sheet.getWritableCell(0, 156);
l = (Label)cell;
l.setString("Label text modified");
cell = sheet.getWritableCell(0, 157);
wcf = cell.getWritableCellFeatures();
wcf.setComment("modified comment text");
cell = sheet.getWritableCell(0, 158);
wcf = cell.getWritableCellFeatures();
wcf.removeComment();
// Modify the validation contents of the row 173
cell = sheet.getWritableCell(0, 172);
wcf = cell.getWritableCellFeatures();
Range r = wcf.getSharedDataValidationRange();
Cell botright = r.getBottomRight();
sheet.removeSharedDataValidation(cell);
al = new ArrayList();
al.Add("Stanley Featherstonehaugh Ukridge");
al.Add("Major Plank");
al.Add("Earl of Ickenham");
al.Add("Sir Gregory Parsloe-Parsloe");
al.Add("Honoria Glossop");
al.Add("Stiffy Byng");
al.Add("Bingo Little");
wcf.setDataValidationList(al);
cell.setCellFeatures(wcf);
sheet.applySharedDataValidation(cell, botright.getColumn() - cell.getColumn(), 1);//botright.getRow() - cell.getRow());
}
/**
* Copy constructor
*
* @param c the column
* @param r the row
* @param f the record to copy
*/
protected Formula(int c,int r,Formula f)
: base(c,r,f)
{
}