/* public static void copy(MultipartFile file,String savePath,String newname) throws Exception { try { File targetFile = new File(savePath,newname); if (!targetFile.exists()) { //判断文件夹是否存在,不存在就创建 targetFile.mkdirs(); } file.transferTo(targetFile); } catch (Exception e) { e.printStackTrace(); } } */ private static String getCellValue(HSSFCell cell) { DecimalFormat df = new DecimalFormat("#"); String cellValue = null; if (cell == null) return null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); break; } cellValue = df.format(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_STRING: cellValue = String.valueOf(cell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: cellValue = String.valueOf(cell.getCellFormula()); break; case HSSFCell.CELL_TYPE_BLANK: cellValue = null; break; case HSSFCell.CELL_TYPE_BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: cellValue = String.valueOf(cell.getErrorCellValue()); break; } if (cellValue != null && cellValue.trim().length() <= 0) { cellValue = null; } return cellValue; }
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); }
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); }
/** * 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 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()))); } } }
/** * 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)); }
/** * Use this to create new cells within the row and return it. * * <p>The cell that is returned is a CELL_TYPE_BLANK. The type can be changed either through * calling setCellValue or setCellType. * * @param column - the column number this cell represents * @return HSSFCell a high level representation of the created cell. */ public HSSFCell createCell(short column, int type) { HSSFCell cell = new HSSFCell(book, sheet, getRowNum(), column, type); addCell(cell); sheet.addValueRecord(getRowNum(), cell.getCellValueRecord()); return cell; }
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 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); } } } }
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()))); }
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); }
/** used internally to add a cell. */ private void addCell(HSSFCell cell) { short column = cell.getCellNum(); if (row.getFirstCol() == -1) { row.setFirstCol(column); } if (row.getLastCol() == -1) { row.setLastCol(column); } if (column >= cells.length) { HSSFCell[] oldCells = cells; int newSize = oldCells.length * 2; if (newSize < column + 1) newSize = column + 1; cells = new HSSFCell[newSize]; System.arraycopy(oldCells, 0, cells, 0, oldCells.length); } cells[column] = cell; if (column < row.getFirstCol()) { row.setFirstCol(column); } if (column > row.getLastCol()) { row.setLastCol(column); } }
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()); }
/** * remove the HSSFCell from this row. * * @param cell to remove */ public void removeCell(HSSFCell cell) { CellValueRecordInterface cval = cell.getCellValueRecord(); sheet.removeValueRecord(getRowNum(), cval); short column = cell.getCellNum(); if (cell != null && column < cells.length) { cells[column] = null; } if (cell.getCellNum() == row.getLastCol()) { row.setLastCol(findLastCell(row.getLastCol())); } if (cell.getCellNum() == row.getFirstCol()) { row.setFirstCol(findFirstCell(row.getFirstCol())); } }
/** 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()); } }
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++; } }
/** 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()); }
/** * 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()); }
/** * 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); }
/** 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 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 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 testSquareMacro() { HSSFWorkbook w = openSample("SquareMacro.xls"); HSSFSheet s0 = w.getSheetAt(0); HSSFRow[] r = {s0.getRow(0), s0.getRow(1)}; HSSFCell a1 = r[0].getCell(0); assertEquals("square(1)", a1.getCellFormula()); assertEquals(1d, a1.getNumericCellValue(), 1e-9); HSSFCell a2 = r[1].getCell(0); assertEquals("square(2)", a2.getCellFormula()); assertEquals(4d, a2.getNumericCellValue(), 1e-9); HSSFCell b1 = r[0].getCell(1); assertEquals("IF(TRUE,square(1))", b1.getCellFormula()); assertEquals(1d, b1.getNumericCellValue(), 1e-9); HSSFCell b2 = r[1].getCell(1); assertEquals("IF(TRUE,square(2))", b2.getCellFormula()); assertEquals(4d, b2.getNumericCellValue(), 1e-9); HSSFCell c1 = r[0].getCell(2); assertEquals("square(square(1))", c1.getCellFormula()); assertEquals(1d, c1.getNumericCellValue(), 1e-9); HSSFCell c2 = r[1].getCell(2); assertEquals("square(square(2))", c2.getCellFormula()); assertEquals(16d, c2.getNumericCellValue(), 1e-9); HSSFCell d1 = r[0].getCell(3); assertEquals("square(one())", d1.getCellFormula()); assertEquals(1d, d1.getNumericCellValue(), 1e-9); HSSFCell d2 = r[1].getCell(3); assertEquals("square(two())", d2.getCellFormula()); assertEquals(4d, d2.getNumericCellValue(), 1e-9); }
public void testStringFormulaRead() { HSSFWorkbook w = openSample("StringFormulas.xls"); HSSFCell c = w.getSheetAt(0).getRow(0).getCell(0); assertEquals("String Cell value", "XYZ", c.getRichStringCellValue().getString()); }
/** MissingArgPtg */ public void testMissingArgPtg() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFCell cell = wb.createSheet("Sheet1").createRow(4).createCell(0); cell.setCellFormula("IF(A1=\"A\",1,)"); }