/** * 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()); }
/** * Adds cells to the specified sheet which test the various date formats * * @param s */ private void writeDateFormatSheet(WritableSheet s) { WritableCellFormat wrappedText = new WritableCellFormat(WritableWorkbook.ARIAL_10_PT); wrappedText.setWrap(true); s.setColumnView(0, 20); s.setColumnView(2, 20); s.setColumnView(3, 20); s.setColumnView(4, 20); s.getSettings().setFitWidth(2); s.getSettings().setFitHeight(2); // TODO: CML - fix this with DateTime support //Calendar c = Calendar.getInstance(TimeZone.getTimeZone("GMT")); //c.set(1975, 4, 31, 15, 21, 45); //c.set(Calendar.MILLISECOND, 660); //Date date = c.getTime(); //c.set(1900, 0, 1, 0, 0, 0); //c.set(Calendar.MILLISECOND, 0); //Date date2 = c.getTime(); //c.set(1970, 0, 1, 0, 0, 0); //Date date3 = c.getTime(); //c.set(1918, 10, 11, 11, 0, 0); //Date date4 = c.getTime(); //c.set(1900, 0, 2, 0, 0, 0); //Date date5 = c.getTime(); //c.set(1901, 0, 1, 0, 0, 0); //Date date6 = c.getTime(); //c.set(1900, 4, 31, 0, 0, 0); //Date date7 = c.getTime(); //c.set(1900, 1, 1, 0, 0, 0); //Date date8 = c.getTime(); //c.set(1900, 0, 31, 0, 0, 0); //Date date9 = c.getTime(); //c.set(1900, 2, 1, 0, 0, 0); //Date date10 = c.getTime(); //c.set(1900, 1, 27, 0, 0, 0); //Date date11 = c.getTime(); //c.set(1900, 1, 28, 0, 0, 0); //Date date12 = c.getTime(); //c.set(1980, 5, 31, 12, 0, 0); //Date date13 = c.getTime(); //c.set(1066, 9, 14, 0, 0, 0); //Date date14 = c.getTime(); //// Built in date formats //SimpleDateFormat sdf = new SimpleDateFormat("dd MMM yyyy HH:mm:ss.SSS"); //sdf.setTimeZone(TimeZone.getTimeZone("GMT")); //Label l = new Label(0, 0, "All dates are " + sdf.format(date), wrappedText); //s.addCell(l); //l = new Label(0, 1, "Built in formats", // wrappedText); //s.addCell(l); //l = new Label(2, 1, "Custom formats"); //s.addCell(l); DateTime date = DateTime.UtcNow; WritableCellFormat cf1 = new WritableCellFormat(DateFormats.FORMAT1); ExcelDateTime dt = new ExcelDateTime(0, 2, date, cf1, ExcelDateTime.GMT); s.addCell(dt); cf1 = new WritableCellFormat(DateFormats.FORMAT2); dt = new ExcelDateTime(0, 3, date, cf1, ExcelDateTime.GMT); s.addCell(dt); cf1 = new WritableCellFormat(DateFormats.FORMAT3); dt = new ExcelDateTime(0, 4, date, cf1); s.addCell(dt); cf1 = new WritableCellFormat(DateFormats.FORMAT4); dt = new ExcelDateTime(0, 5, date, cf1); s.addCell(dt); cf1 = new WritableCellFormat(DateFormats.FORMAT5); dt = new ExcelDateTime(0, 6, date, cf1); s.addCell(dt); cf1 = new WritableCellFormat(DateFormats.FORMAT6); dt = new ExcelDateTime(0, 7, date, cf1); s.addCell(dt); cf1 = new WritableCellFormat(DateFormats.FORMAT7); dt = new ExcelDateTime(0, 8, date, cf1, ExcelDateTime.GMT); s.addCell(dt); cf1 = new WritableCellFormat(DateFormats.FORMAT8); dt = new ExcelDateTime(0, 9, date, cf1, ExcelDateTime.GMT); s.addCell(dt); cf1 = new WritableCellFormat(DateFormats.FORMAT9); dt = new ExcelDateTime(0, 10, date, cf1, ExcelDateTime.GMT); s.addCell(dt); cf1 = new WritableCellFormat(DateFormats.FORMAT10); dt = new ExcelDateTime(0, 11, date, cf1, ExcelDateTime.GMT); s.addCell(dt); cf1 = new WritableCellFormat(DateFormats.FORMAT11); dt = new ExcelDateTime(0, 12, date, cf1, ExcelDateTime.GMT); s.addCell(dt); cf1 = new WritableCellFormat(DateFormats.FORMAT12); dt = new ExcelDateTime(0, 13, date, cf1, ExcelDateTime.GMT); s.addCell(dt); // Custom formats DateFormat df = new DateFormat("dd MM yyyy"); cf1 = new WritableCellFormat(df); Label l = new Label(2, 2, "dd MM yyyy"); s.addCell(l); dt = new ExcelDateTime(3, 2, date, cf1, ExcelDateTime.GMT); s.addCell(dt); df = new DateFormat("dd MMM yyyy"); cf1 = new WritableCellFormat(df); l = new Label(2, 3, "dd MMM yyyy"); s.addCell(l); dt = new ExcelDateTime(3, 3, date, cf1, ExcelDateTime.GMT); s.addCell(dt); df = new DateFormat("hh:mm"); cf1 = new WritableCellFormat(df); l = new Label(2, 4, "hh:mm"); s.addCell(l); dt = new ExcelDateTime(3, 4, date, cf1, ExcelDateTime.GMT); s.addCell(dt); df = new DateFormat("hh:mm:ss"); cf1 = new WritableCellFormat(df); l = new Label(2, 5, "hh:mm:ss"); s.addCell(l); dt = new ExcelDateTime(3, 5, date, cf1, ExcelDateTime.GMT); s.addCell(dt); df = new DateFormat("H:mm:ss a"); cf1 = new WritableCellFormat(df); l = new Label(2, 5, "H:mm:ss a"); s.addCell(l); dt = new ExcelDateTime(3, 5, date, cf1, ExcelDateTime.GMT); s.addCell(dt); dt = new ExcelDateTime(4, 5, date, cf1, ExcelDateTime.GMT); // dt = new ExcelDateTime(4, 5, date13, cf1, ExcelDateTime.GMT); s.addCell(dt); df = new DateFormat("mm:ss.SSS"); cf1 = new WritableCellFormat(df); l = new Label(2, 6, "mm:ss.SSS"); s.addCell(l); dt = new ExcelDateTime(3, 6, date, cf1, ExcelDateTime.GMT); s.addCell(dt); df = new DateFormat("hh:mm:ss a"); cf1 = new WritableCellFormat(df); l = new Label(2, 7, "hh:mm:ss a"); s.addCell(l); // dt = new ExcelDateTime(4, 7, date13, cf1, ExcelDateTime.GMT); dt = new ExcelDateTime(4, 7, date, cf1, ExcelDateTime.GMT); s.addCell(dt); // Check out the zero date ie. 1 Jan 1900 // l = new Label(0, 16, "Zero date " + sdf.format(date2),wrappedText); // s.addCell(l); cf1 = new WritableCellFormat(DateFormats.FORMAT9); //dt = new ExcelDateTime(0, 17, date2, cf1, ExcelDateTime.GMT); //s.addCell(dt); // Check out the zero date + 1 ie. 2 Jan 1900 //l = new Label(3, 16, "Zero date + 1 " + sdf.format(date5), // wrappedText); //s.addCell(l); //cf1 = new WritableCellFormat(DateFormats.FORMAT9); //dt = new ExcelDateTime(3, 17, date5, cf1, ExcelDateTime.GMT); //s.addCell(dt); //// Check out the 1 Jan 1901 //l = new Label(3, 19, sdf.format(date6), // wrappedText); //s.addCell(l); //cf1 = new WritableCellFormat(DateFormats.FORMAT9); //dt = new ExcelDateTime(3, 20, date6, cf1, ExcelDateTime.GMT); //s.addCell(dt); //// Check out the 31 May 1900 //l = new Label(3, 22, sdf.format(date7), // wrappedText); //s.addCell(l); //cf1 = new WritableCellFormat(DateFormats.FORMAT9); //dt = new ExcelDateTime(3, 23, date7, cf1, ExcelDateTime.GMT); //s.addCell(dt); //// Check out 1 Feb 1900 //l = new Label(3, 25, sdf.format(date8), // wrappedText); //s.addCell(l); //cf1 = new WritableCellFormat(DateFormats.FORMAT9); //dt = new ExcelDateTime(3, 26, date8, cf1, ExcelDateTime.GMT); //s.addCell(dt); //// Check out 31 Jan 1900 //l = new Label(3, 28, sdf.format(date9), // wrappedText); //s.addCell(l); //cf1 = new WritableCellFormat(DateFormats.FORMAT9); //dt = new ExcelDateTime(3, 29, date9, cf1, ExcelDateTime.GMT); //s.addCell(dt); //// Check out 31 Jan 1900 //l = new Label(3, 28, sdf.format(date9), // wrappedText); //s.addCell(l); //cf1 = new WritableCellFormat(DateFormats.FORMAT9); //dt = new ExcelDateTime(3, 29, date9, cf1, ExcelDateTime.GMT); //s.addCell(dt); //// Check out 1 Mar 1900 //l = new Label(3, 31, sdf.format(date10), // wrappedText); //s.addCell(l); //cf1 = new WritableCellFormat(DateFormats.FORMAT9); //dt = new ExcelDateTime(3, 32, date10, cf1, ExcelDateTime.GMT); //s.addCell(dt); //// Check out 27 Feb 1900 //l = new Label(3, 34, sdf.format(date11), // wrappedText); //s.addCell(l); //cf1 = new WritableCellFormat(DateFormats.FORMAT9); //dt = new ExcelDateTime(3, 35, date11, cf1, ExcelDateTime.GMT); //s.addCell(dt); //// Check out 28 Feb 1900 //l = new Label(3, 37, sdf.format(date12), // wrappedText); //s.addCell(l); //cf1 = new WritableCellFormat(DateFormats.FORMAT9); //dt = new ExcelDateTime(3, 38, date12, cf1, ExcelDateTime.GMT); //s.addCell(dt); //// Check out the zero date ie. 1 Jan 1970 //l = new Label(0, 19, "Zero UTC date " + sdf.format(date3), // wrappedText); //s.addCell(l); //cf1 = new WritableCellFormat(DateFormats.FORMAT9); //dt = new ExcelDateTime(0, 20, date3, cf1, ExcelDateTime.GMT); //s.addCell(dt); //// Check out the WWI armistice day ie. 11 am, Nov 11, 1918 //l = new Label(0, 22, "Armistice date " + sdf.format(date4), // wrappedText); //s.addCell(l); //cf1 = new WritableCellFormat(DateFormats.FORMAT9); //dt = new ExcelDateTime(0, 23, date4, cf1, ExcelDateTime.GMT); //s.addCell(dt); //// Check out the Battle of Hastings date Oct 14th, 1066 //l = new Label(0, 25, "Battle of Hastings " + sdf.format(date14), // wrappedText); //s.addCell(l); //cf1 = new WritableCellFormat(DateFormats.FORMAT2); //dt = new ExcelDateTime(0, 26, date14, cf1, ExcelDateTime.GMT); //s.addCell(dt); }