/** * 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()); }
/** * Writes out a sheet containing the various numerical formats * * @param s */ private void writeNumberFormatSheet(WritableSheet s) { WritableCellFormat wrappedText = new WritableCellFormat(WritableWorkbook.ARIAL_10_PT); wrappedText.setWrap(true); s.setColumnView(0, 20); s.setColumnView(4, 20); s.setColumnView(5, 20); s.setColumnView(6, 20); // Floats Label l = new Label(0, 0, "+/- Pi - default format", wrappedText); s.addCell(l); Number n = new Number(1, 0, 3.1415926535); s.addCell(n); n = new Number(2, 0, -3.1415926535); s.addCell(n); l = new Label(0, 1, "+/- Pi - integer format", wrappedText); s.addCell(l); WritableCellFormat cf1 = new WritableCellFormat(NumberFormats.INTEGER); n = new Number(1, 1, 3.1415926535, cf1); s.addCell(n); n = new Number(2, 1, -3.1415926535, cf1); s.addCell(n); l = new Label(0, 2, "+/- Pi - float 2dps", wrappedText); s.addCell(l); WritableCellFormat cf2 = new WritableCellFormat(NumberFormats.FLOAT); n = new Number(1, 2, 3.1415926535, cf2); s.addCell(n); n = new Number(2, 2, -3.1415926535, cf2); s.addCell(n); l = new Label(0, 3, "+/- Pi - custom 3dps", wrappedText); s.addCell(l); NumberFormat dp3 = new NumberFormat("#.###"); WritableCellFormat dp3cell = new WritableCellFormat(dp3); n = new Number(1, 3, 3.1415926535, dp3cell); s.addCell(n); n = new Number(2, 3, -3.1415926535, dp3cell); s.addCell(n); l = new Label(0, 4, "+/- Pi - custom &3.14", wrappedText); s.addCell(l); NumberFormat pounddp2 = new NumberFormat("&#.00"); WritableCellFormat pounddp2cell = new WritableCellFormat(pounddp2); n = new Number(1, 4, 3.1415926535, pounddp2cell); s.addCell(n); n = new Number(2, 4, -3.1415926535, pounddp2cell); s.addCell(n); l = new Label(0, 5, "+/- Pi - custom Text #.### Text", wrappedText); s.addCell(l); NumberFormat textdp4 = new NumberFormat("Text#.####Text"); WritableCellFormat textdp4cell = new WritableCellFormat(textdp4); n = new Number(1, 5, 3.1415926535, textdp4cell); s.addCell(n); n = new Number(2, 5, -3.1415926535, textdp4cell); s.addCell(n); // Integers l = new Label(4, 0, "+/- Bilko default format"); s.addCell(l); n = new Number(5, 0, 15042699); s.addCell(n); n = new Number(6, 0, -15042699); s.addCell(n); l = new Label(4, 1, "+/- Bilko float format"); s.addCell(l); WritableCellFormat cfi1 = new WritableCellFormat(NumberFormats.FLOAT); n = new Number(5, 1, 15042699, cfi1); s.addCell(n); n = new Number(6, 1, -15042699, cfi1); s.addCell(n); l = new Label(4, 2, "+/- Thousands separator"); s.addCell(l); WritableCellFormat cfi2 = new WritableCellFormat (NumberFormats.THOUSANDS_INTEGER); n = new Number(5, 2, 15042699, cfi2); s.addCell(n); n = new Number(6, 2, -15042699, cfi2); s.addCell(n); l = new Label(4, 3, "+/- Accounting red - added 0.01"); s.addCell(l); WritableCellFormat cfi3 = new WritableCellFormat (NumberFormats.ACCOUNTING_RED_FLOAT); n = new Number(5, 3, 15042699.01, cfi3); s.addCell(n); n = new Number(6, 3, -15042699.01, cfi3); s.addCell(n); l = new Label(4, 4, "+/- Percent"); s.addCell(l); WritableCellFormat cfi4 = new WritableCellFormat (NumberFormats.PERCENT_INTEGER); n = new Number(5, 4, 15042699, cfi4); s.addCell(n); n = new Number(6, 4, -15042699, cfi4); s.addCell(n); l = new Label(4, 5, "+/- Exponential - 2dps"); s.addCell(l); WritableCellFormat cfi5 = new WritableCellFormat (NumberFormats.EXPONENTIAL); n = new Number(5, 5, 15042699, cfi5); s.addCell(n); n = new Number(6, 5, -15042699, cfi5); s.addCell(n); l = new Label(4, 6, "+/- Custom exponentional - 3dps", wrappedText); s.addCell(l); NumberFormat edp3 = new NumberFormat("0.000E0"); WritableCellFormat edp3Cell = new WritableCellFormat(edp3); n = new Number(5, 6, 15042699, edp3Cell); s.addCell(n); n = new Number(6, 6, -15042699, edp3Cell); s.addCell(n); l = new Label(4, 7, "Custom neg brackets", wrappedText); s.addCell(l); NumberFormat negbracks = new NumberFormat("#,##0;(#,##0)"); WritableCellFormat negbrackscell = new WritableCellFormat(negbracks); n = new Number(5, 7, 15042699, negbrackscell); s.addCell(n); n = new Number(6, 7, -15042699, negbrackscell); s.addCell(n); l = new Label(4, 8, "Custom neg brackets 2", wrappedText); s.addCell(l); NumberFormat negbracks2 = new NumberFormat("#,##0;(#,##0)a"); WritableCellFormat negbrackscell2 = new WritableCellFormat(negbracks2); n = new Number(5, 8, 15042699, negbrackscell2); s.addCell(n); n = new Number(6, 8, -15042699, negbrackscell2); s.addCell(n); l = new Label(4, 9, "Custom percent", wrappedText); s.addCell(l); NumberFormat cuspercent = new NumberFormat("0.0%"); WritableCellFormat cuspercentf = new WritableCellFormat(cuspercent); n = new Number(5, 9, 3.14159265, cuspercentf); s.addCell(n); // Booleans l = new Label(0, 10, "Boolean - TRUE"); s.addCell(l); CSharpJExcel.Jxl.Write.Boolean b = new CSharpJExcel.Jxl.Write.Boolean(1, 10, true); s.addCell(b); l = new Label(0, 11, "Boolean - FALSE"); s.addCell(l); b = new CSharpJExcel.Jxl.Write.Boolean(1, 11, false); s.addCell(b); l = new Label(0, 12, "A hidden cell->"); s.addCell(l); n = new Number(1, 12, 17, WritableWorkbook.HIDDEN_STYLE); s.addCell(n); // Currencies l = new Label(4, 19, "Currency formats"); s.addCell(l); l = new Label(4, 21, "UK Pound"); s.addCell(l); NumberFormat poundCurrency = new NumberFormat(NumberFormat.CURRENCY_POUND + " #,###.00", NumberFormat.COMPLEX_FORMAT); WritableCellFormat poundFormat = new WritableCellFormat(poundCurrency); n = new Number(5, 21, 12345, poundFormat); s.addCell(n); l = new Label(4, 22, "Euro 1"); s.addCell(l); NumberFormat euroPrefixCurrency = new NumberFormat(NumberFormat.CURRENCY_EURO_PREFIX + " #,###.00", NumberFormat.COMPLEX_FORMAT); WritableCellFormat euroPrefixFormat = new WritableCellFormat(euroPrefixCurrency); n = new Number(5, 22, 12345, euroPrefixFormat); s.addCell(n); l = new Label(4, 23, "Euro 2"); s.addCell(l); NumberFormat euroSuffixCurrency = new NumberFormat("#,###.00" + NumberFormat.CURRENCY_EURO_SUFFIX, NumberFormat.COMPLEX_FORMAT); WritableCellFormat euroSuffixFormat = new WritableCellFormat(euroSuffixCurrency); n = new Number(5, 23, 12345, euroSuffixFormat); s.addCell(n); l = new Label(4, 24, "Dollar"); s.addCell(l); NumberFormat dollarCurrency = new NumberFormat(NumberFormat.CURRENCY_DOLLAR + " #,###.00", NumberFormat.COMPLEX_FORMAT); WritableCellFormat dollarFormat = new WritableCellFormat(dollarCurrency); n = new Number(5, 24, 12345, dollarFormat); s.addCell(n); l = new Label(4, 25, "Japanese Yen"); s.addCell(l); NumberFormat japaneseYenCurrency = new NumberFormat(NumberFormat.CURRENCY_JAPANESE_YEN + " #,###.00", NumberFormat.COMPLEX_FORMAT); WritableCellFormat japaneseYenFormat = new WritableCellFormat(japaneseYenCurrency); n = new Number(5, 25, 12345, japaneseYenFormat); s.addCell(n); l = new Label(4, 30, "Fraction formats"); s.addCell(l); l = new Label(4, 32, "One digit fraction format", wrappedText); s.addCell(l); WritableCellFormat fraction1digitformat = new WritableCellFormat(NumberFormats.FRACTION_ONE_DIGIT); n = new Number(5, 32, 3.18279, fraction1digitformat); s.addCell(n); l = new Label(4, 33, "Two digit fraction format", wrappedText); s.addCell(l); WritableCellFormat fraction2digitformat = new WritableCellFormat(NumberFormats.FRACTION_TWO_DIGITS); n = new Number(5, 33, 3.18279, fraction2digitformat); s.addCell(n); l = new Label(4, 34, "Three digit fraction format (improper)", wrappedText); s.addCell(l); NumberFormat fraction3digit1 = new NumberFormat(NumberFormat.FRACTION_THREE_DIGITS, NumberFormat.COMPLEX_FORMAT); WritableCellFormat fraction3digitformat1 = new WritableCellFormat(fraction3digit1); n = new Number(5, 34, 3.18927, fraction3digitformat1); s.addCell(n); l = new Label(4, 35, "Three digit fraction format (proper)", wrappedText); s.addCell(l); NumberFormat fraction3digit2 = new NumberFormat("# " + NumberFormat.FRACTION_THREE_DIGITS, NumberFormat.COMPLEX_FORMAT); WritableCellFormat fraction3digitformat2 = new WritableCellFormat(fraction3digit2); n = new Number(5, 35, 3.18927, fraction3digitformat2); s.addCell(n); // Lots of numbers for (int row = 0; row < 100; row++) { for (int col = 8; col < 108; col++) { n = new Number(col, row, col + row); s.addCell(n); } } // Lots of numbers for (int row = 101; row < 3000; row++) { for (int col = 0; col < 25; col++) { n = new Number(col, row, col + row); s.addCell(n); } } }