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())); }
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())); }
/** 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()))); }
/** * Opens the sheet we wrote out by binomialOperator and makes sure the formulas all match what we * expect (x operator y) */ private static void binomialVerify(String operator, HSSFWorkbook wb) { HSSFSheet s = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; // get our minimum values r = s.getRow(0); c = r.getCell(1); assertTrue( "minval Formula is as expected 1" + operator + "1 != " + c.getCellFormula(), (("1" + operator + "1").equals(c.getCellFormula()))); for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) { r = s.getRow(x); for (int y = 1; y < 256 && y > 0; y++) { c = r.getCell(y); assertTrue( "loop Formula is as expected " + x + operator + y + "!=" + c.getCellFormula(), (("" + x + operator + y).equals(c.getCellFormula()))); } } // test our maximum values r = s.getRow(0); c = r.getCell(0); assertTrue( "maxval Formula is as expected", (("" + Short.MAX_VALUE + operator + Short.MAX_VALUE).equals(c.getCellFormula()))); }
/** * Opens the sheet we wrote out by binomialOperator and makes sure the formulas all match what we * expect (x operator y) */ private static void operationalRefVerify(String operator, HSSFWorkbook wb) { HSSFSheet s = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; // get our minimum values r = s.getRow(0); c = r.getCell(1); // get our minimum values assertTrue( "minval Formula is as expected A2" + operator + "A3 != " + c.getCellFormula(), (("A2" + operator + "A3").equals(c.getCellFormula()))); for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) { r = s.getRow(x); for (int y = 1; y < 256 && y > 0; y++) { int refx1; int refy1; int refx2; int refy2; if (x + 50 < Short.MAX_VALUE) { refx1 = x + 50; refx2 = x + 46; } else { refx1 = x - 4; refx2 = x - 3; } if (y + 50 < 255) { refy1 = y + 50; refy2 = y + 49; } else { refy1 = y - 4; refy2 = y - 3; } c = r.getCell(y); CellReference cr = new CellReference(refx1, refy1, false, false); String ref = cr.formatAsString(); ref = cr.formatAsString(); cr = new CellReference(refx2, refy2, false, false); String ref2 = cr.formatAsString(); assertTrue( "loop Formula is as expected " + ref + operator + ref2 + "!=" + c.getCellFormula(), (("" + ref + operator + ref2).equals(c.getCellFormula()))); } } // test our maximum values r = s.getRow(0); c = r.getCell(0); assertEquals("B1" + operator + "IV255", c.getCellFormula()); }
private static void floatVerify(String operator, HSSFWorkbook wb) { HSSFSheet s = wb.getSheetAt(0); // don't know how to check correct result .. for the moment, we just verify that the file can be // read. for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) { HSSFRow r = s.getRow(x); for (int y = 1; y < 256 && y > 0; y = (short) (y + 2)) { HSSFCell c = r.getCell(y); assertTrue("got a formula", c.getCellFormula() != null); assertTrue( "loop Formula is as expected " + x + "." + y + operator + y + "." + x + "!=" + c.getCellFormula(), (("" + x + "." + y + operator + y + "." + x).equals(c.getCellFormula()))); } } }
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); }
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 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); }
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()); }
/** 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()))); }
/** 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())); }
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 Jogo procurar(int ID) { Jogo resposta = null; for (int i = 1; i <= planilha.getSheet("Jogos").getLastRowNum(); i++) { if (planilha.getSheet("Jogos").getRow(i).getCell(0).getNumericCellValue() == ID) { HSSFRow Row = this.planilha.getSheet("Jogos").getRow(i); String nome = Row.getCell(1).getStringCellValue(); String console = Row.getCell(2).getStringCellValue(); double precoCompra = Row.getCell(3).getNumericCellValue(); double precoVenda = Row.getCell(4).getNumericCellValue(); int quantidade = (int) Row.getCell(5).getNumericCellValue(); String genero = Row.getCell(6).getStringCellValue(); resposta = new Jogo(ID, nome, console, precoCompra, precoVenda, quantidade, genero); } } return resposta; }
public Iterator getIterator() { Jogo[] jogo = new Jogo[planilha.getSheet("Jogos").getLastRowNum()]; int indice = 0; for (int i = 1; i <= planilha.getSheet("Jogos").getLastRowNum(); i++) { if (planilha.getSheet("Jogos").getRow(i).getCell(0).getNumericCellValue() != 0) { HSSFRow Row = this.planilha.getSheet("Jogos").getRow(i); int id = (int) Row.getCell(0).getNumericCellValue(); String nome = Row.getCell(1).getStringCellValue(); String console = Row.getCell(2).getStringCellValue(); double precoCompra = Row.getCell(3).getNumericCellValue(); double precoVenda = Row.getCell(4).getNumericCellValue(); int quantidade = (int) Row.getCell(5).getNumericCellValue(); String genero = Row.getCell(6).getStringCellValue(); Jogo resposta = new Jogo(id, nome, console, precoCompra, precoVenda, quantidade, genero); jogo[indice] = resposta; indice = indice + 1; } } return new IteratorArquivo(jogo); }
/** * 读取97-2003格式 * * @param filePath 文件路径 * @throws java.io.IOException */ @SuppressWarnings("rawtypes") public static List<Map> readExcel2003(String filePath) throws IOException { // 返回结果集 List<Map> valueList = new ArrayList<Map>(); FileInputStream fis = null; try { fis = new FileInputStream(filePath); HSSFWorkbook wookbook = new HSSFWorkbook(fis); // 创建对Excel工作簿文件的引用 HSSFSheet sheet = wookbook.getSheetAt(0); // 在Excel文档中,第一张工作表的缺省索引是0 int rows = sheet.getPhysicalNumberOfRows(); // 获取到Excel文件中的所有行数 Map<Integer, String> keys = new HashMap<Integer, String>(); int cells = 0; // 遍历行(第1行 表头) 准备Map里的key HSSFRow firstRow = sheet.getRow(0); if (firstRow != null) { // 获取到Excel文件中的所有的列 cells = firstRow.getPhysicalNumberOfCells(); // 遍历列 for (int j = 0; j < cells; j++) { // 获取到列的值 try { HSSFCell cell = firstRow.getCell(j); String cellValue = getCellValue(cell); keys.put(j, cellValue); } catch (Exception e) { e.printStackTrace(); } } } // 遍历行(从第二行开始) for (int i = 1; i < rows; i++) { // 读取左上端单元格(从第二行开始) HSSFRow row = sheet.getRow(i); // 行不为空 if (row != null) { // 准备当前行 所储存值的map Map<String, Object> val = new HashMap<String, Object>(); boolean isValidRow = false; // 遍历列 for (int j = 0; j < cells; j++) { // 获取到列的值 try { HSSFCell cell = row.getCell(j); String cellValue = getCellValue(cell); val.put(keys.get(j), cellValue); if (!isValidRow && cellValue != null && cellValue.trim().length() > 0) { isValidRow = true; } } catch (Exception e) { e.printStackTrace(); } } // 第I行所有的列数据读取完毕,放入valuelist if (isValidRow) { valueList.add(val); } } } } catch (IOException e) { e.printStackTrace(); } finally { fis.close(); } return valueList; }
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 testIfFormulas() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet("testSheet1"); HSSFRow r = null; HSSFCell c = null; r = s.createRow(0); c = r.createCell(1); c.setCellValue(1); c = r.createCell(2); c.setCellValue(2); c = r.createCell(3); c.setCellFormula("MAX(A1:B1)"); c = r.createCell(4); c.setCellFormula("IF(A1=D1,\"A1\",\"B1\")"); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); s = wb.getSheetAt(0); r = s.getRow(0); c = r.getCell(4); assertTrue( "expected: IF(A1=D1,\"A1\",\"B1\") got " + c.getCellFormula(), ("IF(A1=D1,\"A1\",\"B1\")").equals(c.getCellFormula())); wb = openSample("IfFormulaTest.xls"); s = wb.getSheetAt(0); r = s.getRow(3); c = r.getCell(0); assertTrue( "expected: IF(A3=A1,\"A1\",\"A2\") got " + c.getCellFormula(), ("IF(A3=A1,\"A1\",\"A2\")").equals(c.getCellFormula())); // c = r.getCell((short)1); // assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), // ("A!A1+A!B1").equals(c.getCellFormula())); wb = new HSSFWorkbook(); s = wb.createSheet("testSheet1"); r = null; c = null; r = s.createRow(0); c = r.createCell(0); c.setCellFormula("IF(1=1,0,1)"); HSSFTestDataSamples.writeOutAndReadBack(wb); wb = new HSSFWorkbook(); s = wb.createSheet("testSheet1"); r = null; c = null; r = s.createRow(0); c = r.createCell(0); c.setCellValue(1); c = r.createCell(1); c.setCellValue(3); HSSFCell formulaCell = r.createCell(3); r = s.createRow(1); c = r.createCell(0); c.setCellValue(3); c = r.createCell(1); c.setCellValue(7); formulaCell.setCellFormula("IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))"); HSSFTestDataSamples.writeOutAndReadBack(wb); }
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()); }