List of usage examples for org.apache.poi.xssf.usermodel XSSFFormulaEvaluator XSSFFormulaEvaluator
public XSSFFormulaEvaluator(XSSFWorkbook workbook)
From source file:cn.study.innerclass.Test.java
License:Open Source License
public static void main(String[] args) throws EncryptedDocumentException, InvalidFormatException, IOException { /*Exsample ex = new Exsample(); Exsample.PublicInnerClass pub = ex.new PublicInnerClass(); pub.setPuAge(111);//w w w . j ava2s. c o m pub.test();*/ /*Workbook book = new HSSFWorkbook(); Cell cell = book.createSheet().createRow(1).createCell(1); cell.setCellValue(5l); System.out.println(cell.getCellType());*/ FileInputStream fin = new FileInputStream("E:/test.xlsx"); Workbook book = WorkbookFactory.create(fin); Sheet sheet = book.getSheetAt(0); ; PoiUtil.getCellData(sheet.getRow(0).getCell(0), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(0).getCell(1), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(0).getCell(2), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(1).getCell(0), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(1).getCell(1), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(1).getCell(2), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(2).getCell(0), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(2).getCell(1), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(2).getCell(2), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(3).getCell(0), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(3).getCell(1), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(3).getCell(2), new XSSFFormulaEvaluator((XSSFWorkbook) book)); }
From source file:com.celtris.exparse.parser.ExcelReader.java
License:Apache License
public List<SheetData<T>> readExcel(String absolutePath, Class<T> excelModelClass, boolean headerExtraction) throws IOException, InstantiationException, IllegalAccessException { FileInputStream file = new FileInputStream(new File(absolutePath)); // Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); DataFormatter objDefaultFormat = new DataFormatter(); FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator(workbook); Iterator<Sheet> sheetIterator = workbook.iterator(); List<SheetData<T>> sheetDataList = new ArrayList<SheetData<T>>(workbook.getNumberOfSheets()); int sheetCount = 0; while (sheetIterator.hasNext()) { sheetCount++;/*from w ww . j a v a2 s .c o m*/ ExcelParser<T> excelParser = new ExcelParser<T>(headerExtraction, excelModelClass); Sheet sheet = sheetIterator.next(); Iterator<Row> rowIterator = sheet.iterator(); int rowCount = 0; // Evaluating header if (headerExtraction) { if (rowIterator.hasNext()) { rowCount++; Field[] fields = excelModelClass.getFields(); List<String> heaaderStr = new ArrayList<String>(fields.length); Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellStrValue = cell.getStringCellValue(); heaaderStr.add(cellStrValue); } excelParser.processFieldAccordingToHeader(heaaderStr, sheet.getSheetName()); } } while (rowIterator.hasNext()) { rowCount++; Row row = rowIterator.next(); // For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); List<String> rowStr = new ArrayList<String>(excelParser.parameterCount()); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellStrValue = ""; switch (cell.getCellTypeEnum()) { case STRING: cellStrValue = cell.getStringCellValue(); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { objFormulaEvaluator.evaluate(cell); cellStrValue = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator); } else { cellStrValue = Double.toString(cell.getNumericCellValue()); } break; case BOOLEAN: cellStrValue = Boolean.toString(cell.getBooleanCellValue()); break; case FORMULA: cellStrValue = cell.getStringCellValue(); break; case BLANK: default: break; } rowStr.add(cellStrValue); } excelParser.processRow(rowStr, rowCount, sheet.getSheetName()); } SheetData<T> sheetData = new SheetData<T>(excelParser.getParsedObject(), sheet.getSheetName(), sheetCount); sheetDataList.add(sheetData); } file.close(); workbook.close(); return sheetDataList; }
From source file:fll.util.ExcelCellReader.java
License:Open Source License
/** * Read an excel file from the specified stream. * // w w w. j av a 2 s . c o m * @param file where to read the excel file from, this is read into memory and * then can be closed after the constructor is finished * @param sheetName the sheet to read * @throws IOException * @throws InvalidFormatException */ public ExcelCellReader(final InputStream file, final String sheetName) throws IOException, InvalidFormatException { workbook = createWorkbook(file); if (workbook instanceof HSSFWorkbook) { formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook); } else if (workbook instanceof XSSFWorkbook) { formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook); } else { throw new RuntimeException("Unknown wookbook class: " + workbook.getClass().getName()); } sheet = workbook.getSheet(sheetName); formatter = new DataFormatter(); }
From source file:nl.architolk.ldt.processors.ExcelConverter.java
License:Open Source License
public void generateData(PipelineContext context, ContentHandler contentHandler) throws SAXException { try {//from w ww.j av a2 s.c om // Read binary content of Excel file ByteArrayOutputStream os = new ByteArrayOutputStream(); Base64XMLReceiver base64ContentHandler = new Base64XMLReceiver(os); readInputAsSAX(context, INPUT_DATA, base64ContentHandler); final byte[] fileContent = os.toByteArray(); final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent); // Create workbook XSSFWorkbook workbook = new XSSFWorkbook(bais); DataFormatter formatter = new DataFormatter(); XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); contentHandler.startDocument(); contentHandler.startElement("", "workbook", "workbook", new AttributesImpl()); for (int s = 0; s < workbook.getNumberOfSheets(); s++) { XSSFSheet sheet = workbook.getSheetAt(s); AttributesImpl sheetAttr = new AttributesImpl(); sheetAttr.addAttribute("", "name", "name", "CDATA", sheet.getSheetName()); contentHandler.startElement("", "sheet", "sheet", sheetAttr); for (int r = 0; r <= sheet.getLastRowNum(); r++) { XSSFRow row = sheet.getRow(r); if (row != null) { AttributesImpl rowAttr = new AttributesImpl(); rowAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(r)); contentHandler.startElement("", "row", "row", rowAttr); for (int c = 0; c < row.getLastCellNum(); c++) { XSSFCell cell = row.getCell(c); if (cell != null) { try { String cellvalue = formatter.formatCellValue(cell, evaluator); if (cellvalue != "") { AttributesImpl columnAttr = new AttributesImpl(); columnAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(cell.getColumnIndex())); contentHandler.startElement("", "column", "column", columnAttr); contentHandler.characters(cellvalue.toCharArray(), 0, cellvalue.length()); contentHandler.endElement("", "column", "column"); } } catch (Exception e) { } } } contentHandler.endElement("", "row", "row"); } } contentHandler.endElement("", "sheet", "sheet"); } contentHandler.endElement("", "workbook", "workbook"); contentHandler.endDocument(); } catch (IOException e) { throw new OXFException(e); } }
From source file:org.tridas.io.formats.ooxml.OOXMLReader.java
License:Apache License
private Double getCellValueAsDouble(Cell cell) { if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { return null; } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return null; } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { return null; } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { try {/*from w ww.ja v a 2 s. co m*/ XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb); return evaluator.evaluate(cell).getNumberValue(); } catch (Exception e) { return null; } } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return cell.getNumericCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { try { Double dbl = Double.valueOf(cell.getStringCellValue()); return dbl; } catch (NumberFormatException e) { return null; } } return null; }
From source file:org.wise.portal.presentation.web.controllers.run.MergeSpreadsheetsController.java
License:Open Source License
@RequestMapping(method = RequestMethod.POST) protected ModelAndView onSubmit(@RequestParam("uploadFile") MultipartFile uploadFile, @RequestParam("mergeColumnTitle") String mergeColumnTitle, HttpServletResponse response) throws Exception { // TODO: this line is saving uploadFile to home directory. Can we do without saving to home directory? File file = multipartToFile(uploadFile); String mergedResultFileName = "merged_" + file.getName(); FileInputStream fis = new FileInputStream(file); // Finds the workbook instance of XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fis); DataFormatter objDefaultFormat = new DataFormatter(); FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook); // number of sheets in the workbook int numberOfSheets = workbook.getNumberOfSheets(); // contains all values of the merge column across all sheets ArrayList<String> mergeColumnValues = new ArrayList<String>(); // maps mergeColumn value to a Map<SheetIndex, ArrayList<Row>> HashMap<String, HashMap<Integer, ArrayList<Row>>> mergeColumnValueToSheetRows = new HashMap<String, HashMap<Integer, ArrayList<Row>>>(); // maps sheet index to the headers in that sheet HashMap<Integer, ArrayList<String>> sheetIndexToSheetColumnHeaders = new HashMap<Integer, ArrayList<String>>(); // how many copies of headers need to be created for each sheet HashMap<Integer, Integer> sheetIndexToMaxSheetRowCount = new HashMap<Integer, Integer>(); // loop through the sheets in the workbook and populate the variables for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) { XSSFSheet sheet = workbook.getSheetAt(sheetIndex); int mergeColumnIndex = -1; // index of the merge column in this sheet int rowIteratorIndex = 0; // index of current row iteration // collect all of the merge column rows in each sheet Iterator<Row> rowIterator = sheet.rowIterator(); int maxSheetRowCountForCurrentSheet = 0; while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); if (rowIteratorIndex == 0) { // for the very first row in this sheet, go through all the cells in the top row and add to sheetColumnHeaders // and add it to sheetIndexToSheetColumnHeaders ArrayList<String> sheetColumnHeaders = new ArrayList<String>(); int rowCellIteratorIndex = 0; Iterator<Cell> topRowCellIterator = row.cellIterator(); while (topRowCellIterator.hasNext()) { Cell topRowCell = topRowCellIterator.next(); String topRowCellString = topRowCell.toString(); if (!topRowCellString.isEmpty()) { sheetColumnHeaders.add(topRowCellString); }//from www .jav a 2 s. c o m if (!topRowCellString.isEmpty() && topRowCellString.equals(mergeColumnTitle)) { // this is the mergeColumn. Remember the column index if (mergeColumnIndex == -1) { mergeColumnIndex = rowCellIteratorIndex; } else { // there are multiple mergeColumnTitles in this sheet. Let the user know and exit ModelAndView mav = new ModelAndView("/admin/run/mergespreadsheets"); mav.addObject("errorMsg", "You have multiple columns titled \"" + mergeColumnTitle + "\" in worksheet #" + (sheetIndex + 1) + ". You can have only one merge column per worksheet. Please fix and try again."); return mav; } } rowCellIteratorIndex++; } sheetIndexToSheetColumnHeaders.put(sheetIndex, sheetColumnHeaders); } else { // for rows that are not the top row (header) // 1. get all the mergeColumnValues // 2. populate mergeColumnValueToSheetRows // 3. calculate sheetIndexToMaxSheetRowCount Cell mergeColumnValueCell = row.getCell(mergeColumnIndex); if (mergeColumnValueCell != null && !mergeColumnValueCell.toString().isEmpty()) { objFormulaEvaluator.evaluate(mergeColumnValueCell); String mergeColumnValueString = objDefaultFormat.formatCellValue(mergeColumnValueCell, objFormulaEvaluator); HashMap<Integer, ArrayList<Row>> sheetIndexToSheetRows = mergeColumnValueToSheetRows .get(mergeColumnValueString); if (sheetIndexToSheetRows == null) { sheetIndexToSheetRows = new HashMap<Integer, ArrayList<Row>>(); mergeColumnValueToSheetRows.put(mergeColumnValueString, sheetIndexToSheetRows); } ArrayList<Row> sheetRows = sheetIndexToSheetRows.get(sheetIndex); if (sheetRows == null) { sheetRows = new ArrayList<>(); sheetIndexToSheetRows.put(sheetIndex, sheetRows); } sheetRows.add(row); if (sheetRows.size() > maxSheetRowCountForCurrentSheet) { maxSheetRowCountForCurrentSheet = sheetRows.size(); } Iterator<Cell> rowCellIterator = row.cellIterator(); int rowCellIteratorIndex = 0; while (rowCellIterator.hasNext()) { Cell rowCell = rowCellIterator.next(); if (rowCellIteratorIndex == mergeColumnIndex) { // this is a merge column cell, so add its value to mergeColumnValues if (!rowCell.toString().isEmpty()) { objFormulaEvaluator.evaluate(rowCell); String rowCellValueString = objDefaultFormat.formatCellValue(rowCell, objFormulaEvaluator); if (!mergeColumnValues.contains(rowCellValueString)) { mergeColumnValues.add(rowCellValueString); } } } rowCellIteratorIndex++; } } } rowIteratorIndex++; } sheetIndexToMaxSheetRowCount.put(sheetIndex, maxSheetRowCountForCurrentSheet); } // Now we are ready to make the merge sheet. We will be writing one row at a time. Workbook wb = new XSSFWorkbook(); // new output workbook Sheet mergedSheet = wb.createSheet("merged"); // output merged result in "merged" sheet // make the header row Row headerRow = mergedSheet.createRow(0); // (0,0) will be the merge cell header. Column 0 will contain mergeColumnValues. Cell mergeColumnHeaderCell = headerRow.createCell(0); mergeColumnHeaderCell.setCellValue(mergeColumnTitle); // current column index "cursor" where we will be writing to int cellIndexWithoutMergeColumn = 1; // make the header row for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) { Integer maxSheetRowCount = sheetIndexToMaxSheetRowCount.get(sheetIndex); ArrayList<String> sheetColumnHeaders = sheetIndexToSheetColumnHeaders.get(sheetIndex); XSSFSheet sheet = workbook.getSheetAt(sheetIndex); String sheetName = sheet.getSheetName(); for (int i = 0; i < maxSheetRowCount; i++) { for (int sheetColumnHeaderIndex = 0; sheetColumnHeaderIndex < sheetColumnHeaders .size(); sheetColumnHeaderIndex++) { String sheetColumnHeader = sheetColumnHeaders.get(sheetColumnHeaderIndex); if (!sheetColumnHeader.isEmpty() && !sheetColumnHeader.equals(mergeColumnTitle)) { String newSheetColumnHeader = sheetColumnHeader + " ( " + sheetName + " " + (i + 1) + " ) "; Cell headerCell = headerRow.createCell(cellIndexWithoutMergeColumn); headerCell.setCellValue(newSheetColumnHeader); cellIndexWithoutMergeColumn++; } } } } // now make all the non-header rows for (int mergeColumnValueIndex = 0; mergeColumnValueIndex < mergeColumnValues .size(); mergeColumnValueIndex++) { String mergeColumnValue = mergeColumnValues.get(mergeColumnValueIndex); HashMap<Integer, ArrayList<Row>> mergeColumnValueSheetRow = mergeColumnValueToSheetRows .get(mergeColumnValue); if (mergeColumnValueSheetRow == null) { System.out.println("Null mergeColumnValueSheetRow, continuing. mergeColumnValueIndex: " + mergeColumnValueIndex + " mergeColumnValue: " + mergeColumnValue); continue; } Row row = mergedSheet.createRow(mergeColumnValueIndex + 1); // + 1 is to account for the header row; // reset current cursor as we make each row cellIndexWithoutMergeColumn = 0; // first column will be the merge column value Cell mergeColumnCell = row.createCell(0); mergeColumnCell.setCellValue(mergeColumnValue); cellIndexWithoutMergeColumn++; for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) { ArrayList<Row> sheetRows = mergeColumnValueSheetRow.get(sheetIndex); int currentSheetSheetRowIndex = 0; ArrayList<String> sheetHeaders = sheetIndexToSheetColumnHeaders.get(sheetIndex); if (sheetRows != null) { for (int sheetRowIndex = 0; sheetRowIndex < sheetRows.size(); sheetRowIndex++) { Row sheetRow = sheetRows.get(sheetRowIndex); for (int sheetHeaderIndex = 0; sheetHeaderIndex < sheetHeaders.size(); sheetHeaderIndex++) { String sheetHeader = sheetHeaders.get(sheetHeaderIndex); if (!sheetHeader.equals(mergeColumnTitle)) { Cell cell = sheetRow.getCell(sheetHeaderIndex); Cell exportCell = row.createCell(cellIndexWithoutMergeColumn); objFormulaEvaluator.evaluate(cell); String cellString = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator); exportCell.setCellValue(cellString); cellIndexWithoutMergeColumn++; } } currentSheetSheetRowIndex++; } } // some columns do not have any values to populate, so populate them with empty cells Integer maxSheetRowCount = sheetIndexToMaxSheetRowCount.get(sheetIndex); while (currentSheetSheetRowIndex < maxSheetRowCount) { for (int i = 0; i < sheetHeaders.size(); i++) { String sheetHeader = sheetHeaders.get(i); if (!sheetHeader.isEmpty() && !sheetHeader.equals(mergeColumnTitle)) { Cell exportCell = row.createCell(cellIndexWithoutMergeColumn); exportCell.setCellValue(""); cellIndexWithoutMergeColumn++; } } currentSheetSheetRowIndex++; } } } // write to response output response.setHeader("Content-Disposition", "attachment; filename=\"" + mergedResultFileName + "\""); ServletOutputStream outputStream = response.getOutputStream(); wb.write(outputStream); fis.close(); return null; }
From source file:ru.icc.cells.ssdc.DataLoader.java
License:Apache License
public void loadWorkbook(File excelFile) throws IOException { FileInputStream fin = new FileInputStream(excelFile); workbook = new XSSFWorkbook(fin); formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook); sourceWorkbookFile = excelFile;//from w w w.ja v a2 s . com }