public void testFullColumnRefs() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Sheet1"); HSSFRow row = sheet.createRow(0); HSSFCell cell0 = row.createCell(0); cell0.setCellFormula("sum(D:D)"); HSSFCell cell1 = row.createCell(1); cell1.setCellFormula("sum(D:E)"); // some values in column D setValue(sheet, 1, 3, 5.0); setValue(sheet, 2, 3, 6.0); setValue(sheet, 5, 3, 7.0); setValue(sheet, 50, 3, 8.0); // some values in column E setValue(sheet, 1, 4, 9.0); setValue(sheet, 2, 4, 10.0); setValue(sheet, 30000, 4, 11.0); // some other values setValue(sheet, 1, 2, 100.0); setValue(sheet, 2, 5, 100.0); setValue(sheet, 3, 6, 100.0); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); assertEquals(26.0, fe.evaluate(cell0).getNumberValue(), 0.0); assertEquals(56.0, fe.evaluate(cell1).getNumberValue(), 0.0); }
/** * All multi-binomial operator tests use this to create a worksheet with a huge set of x operator * y formulas. Next we call binomialVerify and verify that they are all how we expect. */ private static void binomialOperator(String operator) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null; HSSFCell c = null; // get our minimum values r = s.createRow(0); c = r.createCell(1); c.setCellFormula(1 + operator + 1); for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) { r = s.createRow(x); for (int y = 1; y < 256 && y > 0; y++) { c = r.createCell(y); c.setCellFormula("" + x + operator + y); } } // make sure we do the maximum value of the Int operator if (s.getLastRowNum() < Short.MAX_VALUE) { r = s.getRow(0); c = r.createCell(0); c.setCellFormula("" + Short.MAX_VALUE + operator + Short.MAX_VALUE); } wb = HSSFTestDataSamples.writeOutAndReadBack(wb); binomialVerify(operator, wb); }
private static void floatTest(String operator) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null; HSSFCell c = null; // get our minimum values r = s.createRow(0); c = r.createCell(1); c.setCellFormula("" + Float.MIN_VALUE + operator + Float.MIN_VALUE); for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) { r = s.createRow(x); for (int y = 1; y < 256 && y > 0; y = (short) (y + 2)) { c = r.createCell(y); c.setCellFormula("" + x + "." + y + operator + y + "." + x); } } if (s.getLastRowNum() < Short.MAX_VALUE) { r = s.createRow(0); c = r.createCell(0); c.setCellFormula("" + Float.MAX_VALUE + operator + Float.MAX_VALUE); } wb = HSSFTestDataSamples.writeOutAndReadBack(wb); floatVerify(operator, wb); }
public void testSheetFunctions() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet("A"); HSSFRow r = null; HSSFCell c = null; r = s.createRow(0); c = r.createCell(0); c.setCellValue(1); c = r.createCell(1); c.setCellValue(2); s = wb.createSheet("B"); r = s.createRow(0); c = r.createCell(0); c.setCellFormula("AVERAGE(A!A1:B1)"); c = r.createCell(1); c.setCellFormula("A!A1+A!B1"); c = r.createCell(2); c.setCellFormula("A!$A$1+A!$B1"); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); s = wb.getSheet("B"); r = s.getRow(0); c = r.getCell(0); assertTrue( "expected: AVERAGE(A!A1:B1) got: " + c.getCellFormula(), ("AVERAGE(A!A1:B1)").equals(c.getCellFormula())); c = r.getCell(1); assertTrue( "expected: A!A1+A!B1 got: " + c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula())); }
public void testAbsRefs() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r; HSSFCell c; r = s.createRow(0); c = r.createCell(0); c.setCellFormula("A3+A2"); c = r.createCell(1); c.setCellFormula("$A3+$A2"); c = r.createCell(2); c.setCellFormula("A$3+A$2"); c = r.createCell(3); c.setCellFormula("$A$3+$A$2"); c = r.createCell(4); c.setCellFormula("SUM($A$3,$A$2)"); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); s = wb.getSheetAt(0); r = s.getRow(0); c = r.getCell(0); assertTrue("A3+A2", ("A3+A2").equals(c.getCellFormula())); c = r.getCell(1); assertTrue("$A3+$A2", ("$A3+$A2").equals(c.getCellFormula())); c = r.getCell(2); assertTrue("A$3+A$2", ("A$3+A$2").equals(c.getCellFormula())); c = r.getCell(3); assertTrue("$A$3+$A$2", ("$A$3+$A$2").equals(c.getCellFormula())); c = r.getCell(4); assertTrue("SUM($A$3,$A$2)", ("SUM($A$3,$A$2)").equals(c.getCellFormula())); }
/** Writes a function then tests to see if its correct */ public void refAreaArrayFunctionTest(String function) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null; HSSFCell c = null; r = s.createRow(0); c = r.createCell(0); c.setCellFormula(function + "(A2:A4,B2:B4)"); c = r.createCell(1); c.setCellFormula(function + "($A$2:$A4,B$2:B4)"); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); s = wb.getSheetAt(0); r = s.getRow(0); c = r.getCell(0); assertTrue( "function =" + function + "(A2:A4,B2:B4)", ((function + "(A2:A4,B2:B4)").equals(c.getCellFormula()))); c = r.getCell(1); assertTrue( "function =" + function + "($A$2:$A4,B$2:B4)", ((function + "($A$2:$A4,B$2:B4)").equals(c.getCellFormula()))); }
public void remover(int ID) { for (int i = 1; i <= planilha.getSheet("Jogos").getLastRowNum(); i++) { if (planilha.getSheet("Jogos").getRow(i).getCell(0).getNumericCellValue() == ID) { HSSFRow novaRow = this.planilha.getSheet("Jogos").createRow(i); novaRow.createCell(0).setCellValue(0); novaRow.createCell(1).setCellValue(""); novaRow.createCell(2).setCellValue(""); novaRow.createCell(3).setCellValue(""); novaRow.createCell(4).setCellValue(""); novaRow.createCell(5).setCellValue(""); novaRow.createCell(6).setCellValue(""); FileOutputStream nFile = null; try { nFile = new FileOutputStream("Jogos.xls"); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { this.planilha.write(nFile); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { nFile.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
public void atualizar(Jogo jogo) { for (int i = 1; i <= planilha.getSheet("Jogos").getLastRowNum(); i++) { if (planilha.getSheet("Jogos").getRow(i).getCell(0).getNumericCellValue() == jogo.getID()) { HSSFRow novaRow = this.planilha.getSheet("Jogos").createRow(i); novaRow.createCell(1).setCellValue(jogo.getNome()); novaRow.createCell(2).setCellValue(jogo.getConsole()); novaRow.createCell(3).setCellValue(jogo.getPrecoCompra()); novaRow.createCell(4).setCellValue(jogo.getPrecoVenda()); novaRow.createCell(5).setCellValue(jogo.getQuantidade()); novaRow.createCell(6).setCellValue(jogo.getGenero()); FileOutputStream nFile = null; try { nFile = new FileOutputStream("Jogos.xls"); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { this.planilha.write(nFile); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { nFile.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
public void testDateFormulas() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet("testSheet1"); HSSFRow r = null; HSSFCell c = null; r = s.createRow(0); c = r.createCell(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); c.setCellValue(new Date()); c.setCellStyle(cellStyle); // assertEquals("Checking hour = " + hour, date.getTime().getTime(), // HSSFDateUtil.getJavaDate(excelDate).getTime()); for (int k = 1; k < 100; k++) { r = s.createRow(k); c = r.createCell(0); c.setCellFormula("A" + (k) + "+1"); c.setCellStyle(cellStyle); } HSSFTestDataSamples.writeOutAndReadBack(wb); }
private static void operationRefTest(String operator) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null; HSSFCell c = null; // get our minimum values r = s.createRow(0); c = r.createCell(1); c.setCellFormula("A2" + operator + "A3"); for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) { r = s.createRow(x); for (int y = 1; y < 256 && y > 0; y++) { String ref = null; String ref2 = null; short refx1 = 0; short refy1 = 0; short refx2 = 0; short refy2 = 0; if (x + 50 < Short.MAX_VALUE) { refx1 = (short) (x + 50); refx2 = (short) (x + 46); } else { refx1 = (short) (x - 4); refx2 = (short) (x - 3); } if (y + 50 < 255) { refy1 = (short) (y + 50); refy2 = (short) (y + 49); } else { refy1 = (short) (y - 4); refy2 = (short) (y - 3); } c = r.getCell(y); CellReference cr = new CellReference(refx1, refy1, false, false); ref = cr.formatAsString(); cr = new CellReference(refx2, refy2, false, false); ref2 = cr.formatAsString(); c = r.createCell(y); c.setCellFormula("" + ref + operator + ref2); } } // make sure we do the maximum value of the Int operator if (s.getLastRowNum() < Short.MAX_VALUE) { r = s.getRow(0); c = r.createCell(0); c.setCellFormula("" + "B1" + operator + "IV255"); } wb = HSSFTestDataSamples.writeOutAndReadBack(wb); operationalRefVerify(operator, wb); }
/** * It is responsible for creating a cell within the row. * * @param fila: row where create the cell * @param position: Determines the position where anger cell within the row. * @param value: Sets the value that will be created within the cell. */ private void createCell(HSSFRow fila, int position, String value) { HSSFCell cell; cell = fila.createCell( (short) position); // Se crea una cell dentro de la fila cell.setCellValue(new HSSFRichTextString(value)); }
private static void setValue(HSSFSheet sheet, int rowIndex, int colIndex, double value) { HSSFRow row = sheet.getRow(rowIndex); if (row == null) { row = sheet.createRow(rowIndex); } row.createCell(colIndex).setCellValue(value); }
private void writeData2Excel(HSSFSheet sheet, HSSFCellStyle dataStyle, HSSFCellStyle headerStyle) throws ContainerException { RowSet searchResult = executeFrm.getSearchResult(); if (searchResult != null && !searchResult.isEmpty()) { List<ReportViewFrm> definedViews = executeFrm.getDefinedViews(); int fieldCount = definedViews.size(); int dataCount = searchResult.getSize(); int xlsRowNum = 0; for (int i = 0; i < dataCount; i++) { Row row = searchResult.getRow(i); xlsRowNum = i; if (isFirstFile) { xlsRowNum++; } HSSFRow xlsRow = sheet.createRow(xlsRowNum); xlsRow.setHeightInPoints(18); for (short j = 0; j < fieldCount; j++) { ReportViewFrm viewFrm = definedViews.get(j); String fieldName = viewFrm.getFieldName(); HSSFCell xlsCell = xlsRow.createCell(j); xlsCell.setCellStyle(dataStyle); HSSFRichTextString richText = new HSSFRichTextString(row.getStrValue(fieldName)); xlsCell.setCellValue(richText); } } } }
private void writeSheet( List<Map<String, String>> valueMaps, String worksheetName, HSSFWorkbook wb) { // Set column widths HSSFSheet sheet = wb.createSheet(worksheetName); sheet.setColumnWidth(Short.parseShort("0"), Short.parseShort("15000")); sheet.setColumnWidth(Short.parseShort("1"), Short.parseShort("30000")); // header style HSSFCellStyle headerStyle; HSSFFont headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 11); headerStyle = wb.createCellStyle(); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); headerStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerStyle.setFont(headerFont); headerStyle.setWrapText(true); // header row HSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(30); HSSFCell headerCell0 = headerRow.createCell((short) 0); HSSFCell headerCell1 = headerRow.createCell((short) 1); headerCell0.setCellStyle(headerStyle); setText(headerCell0, "Layer Name"); headerCell1.setCellStyle(headerStyle); setText(headerCell1, "Message"); int counter = 1; for (Map<String, String> valueMap : valueMaps) { HSSFRow dataRow = sheet.createRow(counter); String layer = valueMap.get("layer"); String status = valueMap.get("status"); status = HtmlUtils.htmlUnescape(status); HSSFCell currentCell0 = dataRow.createCell((short) 0); HSSFCell currentCell1 = dataRow.createCell((short) 1); setText(currentCell0, layer); setText(currentCell1, status); counter++; } }
/** Test creation / evaluation of formulas with sheet-level names */ public void testSheetLevelFormulas() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFRow row; HSSFSheet sh1 = wb.createSheet("Sheet1"); HSSFName nm1 = wb.createName(); nm1.setNameName("sales_1"); nm1.setSheetIndex(0); nm1.setRefersToFormula("Sheet1!$A$1"); row = sh1.createRow(0); row.createCell(0).setCellValue(3); row.createCell(1).setCellFormula("sales_1"); row.createCell(2).setCellFormula("sales_1*2"); HSSFSheet sh2 = wb.createSheet("Sheet2"); HSSFName nm2 = wb.createName(); nm2.setNameName("sales_1"); nm2.setSheetIndex(1); nm2.setRefersToFormula("Sheet2!$A$1"); row = sh2.createRow(0); row.createCell(0).setCellValue(5); row.createCell(1).setCellFormula("sales_1"); row.createCell(2).setCellFormula("sales_1*3"); // check that NamePtg refers to the correct NameRecord Ptg[] ptgs1 = HSSFFormulaParser.parse("sales_1", wb, FormulaType.CELL, 0); NamePtg nPtg1 = (NamePtg) ptgs1[0]; assertSame(nm1, wb.getNameAt(nPtg1.getIndex())); Ptg[] ptgs2 = HSSFFormulaParser.parse("sales_1", wb, FormulaType.CELL, 1); NamePtg nPtg2 = (NamePtg) ptgs2[0]; assertSame(nm2, wb.getNameAt(nPtg2.getIndex())); // check that the formula evaluator returns the correct result HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); assertEquals(3.0, evaluator.evaluate(sh1.getRow(0).getCell(1)).getNumberValue(), 0.0); assertEquals(6.0, evaluator.evaluate(sh1.getRow(0).getCell(2)).getNumberValue(), 0.0); assertEquals(5.0, evaluator.evaluate(sh2.getRow(0).getCell(1)).getNumberValue(), 0.0); assertEquals(15.0, evaluator.evaluate(sh2.getRow(0).getCell(2)).getNumberValue(), 0.0); }
public void testStringFormulas() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet("A"); HSSFRow r = null; HSSFCell c = null; r = s.createRow(0); c = r.createCell(1); c.setCellFormula("UPPER(\"abc\")"); c = r.createCell(2); c.setCellFormula("LOWER(\"ABC\")"); c = r.createCell(3); c.setCellFormula("CONCATENATE(\" my \",\" name \")"); HSSFTestDataSamples.writeOutAndReadBack(wb); wb = openSample("StringFormulas.xls"); s = wb.getSheetAt(0); r = s.getRow(0); c = r.getCell(0); assertEquals("UPPER(\"xyz\")", c.getCellFormula()); }
public void testRVAoperands() throws Exception { File file = TempFile.createTempFile("testFormulaRVA", ".xls"); FileOutputStream out = new FileOutputStream(file); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null; HSSFCell c = null; r = s.createRow(0); c = r.createCell(0); c.setCellFormula("A3+A2"); c = r.createCell(1); c.setCellFormula("AVERAGE(A3,A2)"); c = r.createCell(2); c.setCellFormula("ROW(A3)"); c = r.createCell(3); c.setCellFormula("AVERAGE(A2:A3)"); c = r.createCell(4); c.setCellFormula("POWER(A2,A3)"); c = r.createCell(5); c.setCellFormula("SIN(A2)"); c = r.createCell(6); c.setCellFormula("SUM(A2:A3)"); c = r.createCell(7); c.setCellFormula("SUM(A2,A3)"); r = s.createRow(1); c = r.createCell(0); c.setCellValue(2.0); r = s.createRow(2); c = r.createCell(0); c.setCellValue(3.0); wb.write(out); out.close(); assertTrue("file exists", file.exists()); }
public void testOptimiseStylesCheckActualStyles() { HSSFWorkbook wb = new HSSFWorkbook(); // Several styles assertEquals(21, wb.getNumCellStyles()); HSSFCellStyle cs1 = wb.createCellStyle(); cs1.setBorderBottom(HSSFCellStyle.BORDER_THICK); HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setBorderBottom(HSSFCellStyle.BORDER_DASH_DOT); HSSFCellStyle cs3 = wb.createCellStyle(); // = cs1 cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); assertEquals(24, wb.getNumCellStyles()); // Use them HSSFSheet s = wb.createSheet(); HSSFRow r = s.createRow(0); r.createCell(0).setCellStyle(cs1); r.createCell(1).setCellStyle(cs2); r.createCell(2).setCellStyle(cs3); assertEquals(21, r.getCell(0).getCellValueRecord().getXFIndex()); assertEquals(22, r.getCell(1).getCellValueRecord().getXFIndex()); assertEquals(23, r.getCell(2).getCellValueRecord().getXFIndex()); // Optimise HSSFOptimiser.optimiseCellStyles(wb); // Check assertEquals(23, wb.getNumCellStyles()); assertEquals(HSSFCellStyle.BORDER_THICK, r.getCell(0).getCellStyle().getBorderBottom()); assertEquals(HSSFCellStyle.BORDER_DASH_DOT, r.getCell(1).getCellStyle().getBorderBottom()); assertEquals(HSSFCellStyle.BORDER_THICK, r.getCell(2).getCellStyle().getBorderBottom()); }
public RepositorioJogosArquivo() { boolean arquivoExiste = true; this.planilha = new HSSFWorkbook(); FileInputStream arquivo = null; try { arquivo = new FileInputStream("Jogos.xls"); } catch (FileNotFoundException e) { arquivoExiste = false; HSSFRow inicioJogos = this.planilha.createSheet("Jogos").createRow(0); this.planilha.getSheet("Jogos").createRow(1).createCell(0).setCellValue(0); FileOutputStream novoArquivo = null; try { novoArquivo = new FileOutputStream("Jogos.xls"); } catch (FileNotFoundException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } inicioJogos.createCell(0).setCellValue("ID"); inicioJogos.createCell(1).setCellValue("nome"); inicioJogos.createCell(2).setCellValue("console"); inicioJogos.createCell(3).setCellValue("preço compra"); inicioJogos.createCell(4).setCellValue("preço venda"); inicioJogos.createCell(5).setCellValue("quantidade"); inicioJogos.createCell(6).setCellValue("genero"); try { this.planilha.write(novoArquivo); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } try { novoArquivo.close(); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } if (arquivoExiste) { try { this.planilha = new HSSFWorkbook(arquivo); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
/** * When evaluating defined names, POI has to decide whether it is capable. Currently (May2009) POI * only supports simple cell and area refs.<br> * The sample spreadsheet (bugzilla attachment 23508) had a name flagged as 'complex' which * contained a simple area ref. It is not clear what the 'complex' flag is used for but POI should * look elsewhere to decide whether it can evaluate the name. */ public void testDefinedNameWithComplexFlag_bug47048() { // Mock up a spreadsheet to match the critical details of the sample HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Input"); HSSFName definedName = wb.createName(); definedName.setNameName("Is_Multicar_Vehicle"); definedName.setRefersToFormula("Input!$B$17:$G$17"); // Set up some data and the formula HSSFRow row17 = sheet.createRow(16); row17.createCell(0).setCellValue(25.0); row17.createCell(1).setCellValue(1.33); row17.createCell(2).setCellValue(4.0); HSSFRow row = sheet.createRow(0); HSSFCell cellA1 = row.createCell(0); cellA1.setCellFormula("SUM(Is_Multicar_Vehicle)"); // Set the complex flag - POI doesn't usually manipulate this flag NameRecord nameRec = TestHSSFName.getNameRecord(definedName); nameRec.setOptionFlag((short) 0x10); // 0x10 -> complex HSSFFormulaEvaluator hsf = new HSSFFormulaEvaluator(wb); CellValue value; try { value = hsf.evaluate(cellA1); } catch (RuntimeException e) { if (e.getMessage().equals("Don't now how to evalate name 'Is_Multicar_Vehicle'")) { throw new AssertionFailedError("Identified bug 47048a"); } throw e; } assertEquals(Cell.CELL_TYPE_NUMERIC, value.getCellType()); assertEquals(5.33, value.getNumberValue(), 0.0); }
public void testLogicalFormulas() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet("A"); HSSFRow r = null; HSSFCell c = null; r = s.createRow(0); c = r.createCell(1); c.setCellFormula("IF(A1<A2,B1,B2)"); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); s = wb.getSheetAt(0); r = s.getRow(0); c = r.getCell(1); assertEquals("Formula in cell 1 ", "IF(A1<A2,B1,B2)", c.getCellFormula()); }
/** tests order wrting out == order writing in for a given formula */ private static void orderTest(String formula) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null; HSSFCell c = null; // get our minimum values r = s.createRow(0); c = r.createCell(1); c.setCellFormula(formula); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); s = wb.getSheetAt(0); // get our minimum values r = s.getRow(0); c = r.getCell(1); assertTrue("minval Formula is as expected", formula.equals(c.getCellFormula())); }
/** Add 1+1 -- WHoohoo! */ public void testBasicAddIntegers() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r = null; HSSFCell c = null; // get our minimum values r = s.createRow(1); c = r.createCell(1); c.setCellFormula(1 + "+" + 1); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); s = wb.getSheetAt(0); r = s.getRow(1); c = r.getCell(1); assertTrue("Formula is as expected", ("1+1".equals(c.getCellFormula()))); }
private void writeDataHeader(HSSFSheet sheet, HSSFCellStyle headerStyle) throws ReportException { try { sheet.setDisplayGridlines(false); // 不显示网格线 HSSFRow xlsRow = sheet.createRow(0); xlsRow.setHeightInPoints(18); List<ReportViewFrm> definedViews = executeFrm.getDefinedViews(); int fieldCount = definedViews.size(); for (short j = 0; j < fieldCount; j++) { ReportViewFrm viewFrm = definedViews.get(j); HSSFCell xlsCell = xlsRow.createCell(j); xlsCell.setCellStyle(headerStyle); HSSFRichTextString richText = new HSSFRichTextString(viewFrm.getFieldDesc()); xlsCell.setCellValue(richText); } } catch (Throwable ex) { Logger.logError(ex); throw new ReportException(ex.getMessage()); } }
public void testDoesNoHarmIfNothingToDo() { HSSFWorkbook wb = new HSSFWorkbook(); // New files start with 4 built in fonts, and 21 built in styles assertEquals(4, wb.getNumberOfFonts()); assertEquals(21, wb.getNumCellStyles()); // Create a test font and style, and use them HSSFFont f = wb.createFont(); f.setFontName("Testing"); HSSFCellStyle s = wb.createCellStyle(); s.setFont(f); HSSFSheet sheet = wb.createSheet(); HSSFRow row = sheet.createRow(0); row.createCell(0).setCellStyle(s); // Should have one more than the default of each assertEquals(5, wb.getNumberOfFonts()); assertEquals(22, wb.getNumCellStyles()); // Optimise fonts HSSFOptimiser.optimiseFonts(wb); assertEquals(5, wb.getNumberOfFonts()); assertEquals(22, wb.getNumCellStyles()); assertEquals(f, s.getFont(wb)); // Optimise styles HSSFOptimiser.optimiseCellStyles(wb); assertEquals(5, wb.getNumberOfFonts()); assertEquals(22, wb.getNumCellStyles()); assertEquals(f, s.getFont(wb)); }
/** Test for bug due to attempt to convert a cached formula error result to a boolean */ public void testUpdateCachedFormulaResultFromErrorToNumber_bug46479() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Sheet1"); HSSFRow row = sheet.createRow(0); HSSFCell cellA1 = row.createCell(0); HSSFCell cellB1 = row.createCell(1); cellB1.setCellFormula("A1+1"); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); cellA1.setCellErrorValue((byte) HSSFErrorConstants.ERROR_NAME); fe.evaluateFormulaCell(cellB1); cellA1.setCellValue(2.5); fe.notifyUpdateCell(cellA1); try { fe.evaluateInCell(cellB1); } catch (IllegalStateException e) { if (e.getMessage().equals("Cannot get a numeric value from a error formula cell")) { throw new AssertionFailedError("Identified bug 46479a"); } } assertEquals(3.5, cellB1.getNumericCellValue(), 0.0); }
public void generateExcel(OutputStream out, XLSCallBack<T> xlscaCallBack) throws RuntimeException, IOException { HSSFWorkbook workbook = new HSSFWorkbook(); // create a sheet with specified name HSSFSheet sheet = workbook.createSheet(xlscaCallBack.getSheetName()); for (int i = 0; i < xlscaCallBack.getColumnWidth().length; i++) { sheet.setColumnWidth(i, xlscaCallBack.getColumnWidth()[i] * 256); } // create a title for sheet title sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, xlscaCallBack.getHeaders().length - 1)); HSSFCellStyle titleCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont titleFont = workbook.createFont(); // set font titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); titleFont.setFontName("宋体"); titleFont.setFontHeight((short) (240)); titleFont.setColor(HSSFColor.AUTOMATIC.index); titleCellStyle.setFont(titleFont); titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); HSSFRow title = sheet.createRow(0); title.setHeight((short) 300); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title HSSFCell cell = title.createCell(i); cell.setCellValue(xlscaCallBack.getTitle()); cell.setCellStyle(titleCellStyle); } HSSFCellStyle headerCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont headerFont = workbook.createFont(); // set font headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontName("幼圆"); headerFont.setColor(HSSFColor.AUTOMATIC.index); headerCellStyle.setFont(headerFont); headerCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border headerCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); HSSFRow header = sheet.createRow(1); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for header HSSFCell cell = header.createCell(i); cell.setCellValue(xlscaCallBack.getHeaders()[i]); cell.setCellStyle(headerCellStyle); } HSSFCellStyle rowCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell rowCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont rowFont = workbook.createFont(); // set font rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); rowFont.setFontName("幼圆"); rowFont.setColor(HSSFColor.AUTOMATIC.index); rowCellStyle.setFont(rowFont); rowCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border rowCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); rowCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); rowCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); for (int i = 0; i < lists.size(); i++) { HSSFRow row = sheet.createRow(i + 2); String values[] = xlscaCallBack.getValue(lists.get(i)); for (int j = 0; j < values.length; j++) { HSSFCell cell = row.createCell(j); cell.setCellValue(values[j]); cell.setCellStyle(rowCellStyle); } } HSSFCellStyle bottomCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell bottomCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont bottomFont = workbook.createFont(); // set font bottomFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); bottomFont.setFontName("幼圆"); bottomFont.setColor(HSSFColor.AUTOMATIC.index); bottomCellStyle.setFont(bottomFont); bottomCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border bottomCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); sheet.addMergedRegion( new CellRangeAddress( lists.size() + 2, lists.size() + 2, 0, xlscaCallBack.getHeaders().length - 1)); // creaet bootom for xls HSSFRow bottomRow = sheet.createRow(lists.size() + 2); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title HSSFCell cell = bottomRow.createCell(i); SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); cell.setCellValue("共计导出 " + lists.size() + " 条记录 ,导出日期:" + sf.format(new Date())); cell.setCellStyle(bottomCellStyle); } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("create Excel failed due to some unkonw reasion~"); } }
public void testOptimiseFonts() { HSSFWorkbook wb = new HSSFWorkbook(); // Add 6 fonts, some duplicates HSSFFont f1 = wb.createFont(); f1.setFontHeight((short) 11); f1.setFontName("Testing"); HSSFFont f2 = wb.createFont(); f2.setFontHeight((short) 22); f2.setFontName("Also Testing"); HSSFFont f3 = wb.createFont(); f3.setFontHeight((short) 33); f3.setFontName("Unique"); HSSFFont f4 = wb.createFont(); f4.setFontHeight((short) 11); f4.setFontName("Testing"); HSSFFont f5 = wb.createFont(); f5.setFontHeight((short) 22); f5.setFontName("Also Testing"); HSSFFont f6 = wb.createFont(); f6.setFontHeight((short) 66); f6.setFontName("Also Unique"); // Use all three of the four in cell styles assertEquals(21, wb.getNumCellStyles()); HSSFCellStyle cs1 = wb.createCellStyle(); cs1.setFont(f1); assertEquals(5, cs1.getFontIndex()); HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setFont(f4); assertEquals(8, cs2.getFontIndex()); HSSFCellStyle cs3 = wb.createCellStyle(); cs3.setFont(f5); assertEquals(9, cs3.getFontIndex()); HSSFCellStyle cs4 = wb.createCellStyle(); cs4.setFont(f6); assertEquals(10, cs4.getFontIndex()); assertEquals(25, wb.getNumCellStyles()); // And three in rich text HSSFSheet s = wb.createSheet(); HSSFRow r = s.createRow(0); HSSFRichTextString rtr1 = new HSSFRichTextString("Test"); rtr1.applyFont(0, 2, f1); rtr1.applyFont(3, 4, f2); r.createCell(0).setCellValue(rtr1); HSSFRichTextString rtr2 = new HSSFRichTextString("AlsoTest"); rtr2.applyFont(0, 2, f3); rtr2.applyFont(3, 5, f5); rtr2.applyFont(6, 8, f6); r.createCell(1).setCellValue(rtr2); // Check what we have now assertEquals(10, wb.getNumberOfFonts()); assertEquals(25, wb.getNumCellStyles()); // Optimise HSSFOptimiser.optimiseFonts(wb); // Check font count assertEquals(8, wb.getNumberOfFonts()); assertEquals(25, wb.getNumCellStyles()); // Check font use in cell styles assertEquals(5, cs1.getFontIndex()); assertEquals(5, cs2.getFontIndex()); // duplicate of 1 assertEquals(6, cs3.getFontIndex()); // duplicate of 2 assertEquals(8, cs4.getFontIndex()); // two have gone // And in rich text // RTR 1 had f1 and f2, unchanged assertEquals(5, r.getCell(0).getRichStringCellValue().getFontAtIndex(0)); assertEquals(5, r.getCell(0).getRichStringCellValue().getFontAtIndex(1)); assertEquals(6, r.getCell(0).getRichStringCellValue().getFontAtIndex(3)); assertEquals(6, r.getCell(0).getRichStringCellValue().getFontAtIndex(4)); // RTR 2 had f3 (unchanged), f5 (=f2) and f6 (moved down) assertEquals(7, r.getCell(1).getRichStringCellValue().getFontAtIndex(0)); assertEquals(7, r.getCell(1).getRichStringCellValue().getFontAtIndex(1)); assertEquals(6, r.getCell(1).getRichStringCellValue().getFontAtIndex(3)); assertEquals(6, r.getCell(1).getRichStringCellValue().getFontAtIndex(4)); assertEquals(8, r.getCell(1).getRichStringCellValue().getFontAtIndex(6)); assertEquals(8, r.getCell(1).getRichStringCellValue().getFontAtIndex(7)); }
public void testOptimiseStyles() { HSSFWorkbook wb = new HSSFWorkbook(); // Two fonts assertEquals(4, wb.getNumberOfFonts()); HSSFFont f1 = wb.createFont(); f1.setFontHeight((short) 11); f1.setFontName("Testing"); HSSFFont f2 = wb.createFont(); f2.setFontHeight((short) 22); f2.setFontName("Also Testing"); assertEquals(6, wb.getNumberOfFonts()); // Several styles assertEquals(21, wb.getNumCellStyles()); HSSFCellStyle cs1 = wb.createCellStyle(); cs1.setFont(f1); HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setFont(f2); HSSFCellStyle cs3 = wb.createCellStyle(); cs3.setFont(f1); HSSFCellStyle cs4 = wb.createCellStyle(); cs4.setFont(f1); cs4.setAlignment((short) 22); HSSFCellStyle cs5 = wb.createCellStyle(); cs5.setFont(f2); cs5.setAlignment((short) 111); HSSFCellStyle cs6 = wb.createCellStyle(); cs6.setFont(f2); assertEquals(27, wb.getNumCellStyles()); // Use them HSSFSheet s = wb.createSheet(); HSSFRow r = s.createRow(0); r.createCell(0).setCellStyle(cs1); r.createCell(1).setCellStyle(cs2); r.createCell(2).setCellStyle(cs3); r.createCell(3).setCellStyle(cs4); r.createCell(4).setCellStyle(cs5); r.createCell(5).setCellStyle(cs6); r.createCell(6).setCellStyle(cs1); r.createCell(7).setCellStyle(cs2); assertEquals(21, r.getCell(0).getCellValueRecord().getXFIndex()); assertEquals(26, r.getCell(5).getCellValueRecord().getXFIndex()); assertEquals(21, r.getCell(6).getCellValueRecord().getXFIndex()); // Optimise HSSFOptimiser.optimiseCellStyles(wb); // Check assertEquals(6, wb.getNumberOfFonts()); assertEquals(25, wb.getNumCellStyles()); // cs1 -> 21 assertEquals(21, r.getCell(0).getCellValueRecord().getXFIndex()); // cs2 -> 22 assertEquals(22, r.getCell(1).getCellValueRecord().getXFIndex()); assertEquals(22, r.getCell(1).getCellStyle().getFont(wb).getFontHeight()); // cs3 = cs1 -> 21 assertEquals(21, r.getCell(2).getCellValueRecord().getXFIndex()); // cs4 --> 24 -> 23 assertEquals(23, r.getCell(3).getCellValueRecord().getXFIndex()); // cs5 --> 25 -> 24 assertEquals(24, r.getCell(4).getCellValueRecord().getXFIndex()); // cs6 = cs2 -> 22 assertEquals(22, r.getCell(5).getCellValueRecord().getXFIndex()); // cs1 -> 21 assertEquals(21, r.getCell(6).getCellValueRecord().getXFIndex()); // cs2 -> 22 assertEquals(22, r.getCell(7).getCellValueRecord().getXFIndex()); // Add a new duplicate, and two that aren't used HSSFCellStyle csD = wb.createCellStyle(); csD.setFont(f1); r.createCell(8).setCellStyle(csD); HSSFFont f3 = wb.createFont(); f3.setFontHeight((short) 23); f3.setFontName("Testing 3"); HSSFFont f4 = wb.createFont(); f4.setFontHeight((short) 24); f4.setFontName("Testing 4"); HSSFCellStyle csU1 = wb.createCellStyle(); csU1.setFont(f3); HSSFCellStyle csU2 = wb.createCellStyle(); csU2.setFont(f4); // Check before the optimise assertEquals(8, wb.getNumberOfFonts()); assertEquals(28, wb.getNumCellStyles()); // Optimise, should remove the two un-used ones and the one duplicate HSSFOptimiser.optimiseCellStyles(wb); // Check assertEquals(8, wb.getNumberOfFonts()); assertEquals(25, wb.getNumCellStyles()); // csD -> cs1 -> 21 assertEquals(21, r.getCell(8).getCellValueRecord().getXFIndex()); }
public void testSumIf() { String function = "SUMIF(A1:A5,\">4000\",B1:B5)"; HSSFWorkbook wb = openSample("sumifformula.xls"); HSSFSheet s = wb.getSheetAt(0); HSSFRow r = s.getRow(0); HSSFCell c = r.getCell(2); assertEquals(function, c.getCellFormula()); wb = new HSSFWorkbook(); s = wb.createSheet(); r = s.createRow(0); c = r.createCell(0); c.setCellValue(1000); c = r.createCell(1); c.setCellValue(1); r = s.createRow(1); c = r.createCell(0); c.setCellValue(2000); c = r.createCell(1); c.setCellValue(2); r = s.createRow(2); c = r.createCell(0); c.setCellValue(3000); c = r.createCell(1); c.setCellValue(3); r = s.createRow(3); c = r.createCell(0); c.setCellValue(4000); c = r.createCell(1); c.setCellValue(4); r = s.createRow(4); c = r.createCell(0); c.setCellValue(5000); c = r.createCell(1); c.setCellValue(5); r = s.getRow(0); c = r.createCell(2); c.setCellFormula(function); HSSFTestDataSamples.writeOutAndReadBack(wb); }