List of usage examples for org.apache.poi.hssf.usermodel HSSFFormulaEvaluator HSSFFormulaEvaluator
public HSSFFormulaEvaluator(HSSFWorkbook workbook)
From source file:blueprint.sdk.experimental.util.XlsReader.java
License:Open Source License
/** * Opens Migration Plan (*.xls) file//from w ww.j a v a 2 s . c o m * * @throws IOException Failed to open or read *.xls file */ public void open() throws IOException { InputStream input = new FileInputStream(filePath); wbook = new HSSFWorkbook(new POIFSFileSystem(input)); evaluator = new HSSFFormulaEvaluator(wbook); }
From source file:cn.trymore.core.util.excel.PoiExcelParser.java
License:Open Source License
public Object getCellContent(Object cell) { if (cell != null) { HSSFCell cel = (HSSFCell) cell;//from w ww .jav a 2 s . c om HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(this.book); CellValue cellValue = evaluator.evaluate(cel); switch (cel.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cel)) { return UtilDate.parseTime(cel.getDateCellValue(), "yyyy-MM-dd HH:mm:ss"); } else { return Double.valueOf(cellValue.getNumberValue()); } case HSSFCell.CELL_TYPE_STRING: return cellValue.getStringValue(); case HSSFCell.CELL_TYPE_BOOLEAN: return Boolean.valueOf(cellValue.getBooleanValue()); case HSSFCell.CELL_TYPE_ERROR: return Byte.valueOf(cellValue.getErrorValue()); case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_FORMULA: return cellValue.formatAsString(); default: return null; } } return null; }
From source file:com.krawler.esp.servlets.XLSDataExtractor.java
License:Open Source License
public JSONObject parseXLS(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; int noOfRowsDisplayforSample = 20; if (noOfRowsDisplayforSample > sheet.getLastRowNum()) { noOfRowsDisplayforSample = sheet.getLastRowNum(); }//w ww .j a v a 2s.c o m JSONArray jArr = new JSONArray(); for (int i = 0; i <= noOfRowsDisplayforSample; i++) { Row row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { jArr.put(obj); continue; } if (maxCol < row.getLastCellNum()) maxCol = row.getLastCellNum(); for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); if (cell == null) continue; String colHeader = new CellReference(i, j).getCellRefParts()[2]; String val = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: val = Double.toString(cell.getNumericCellValue()); if (HSSFDateUtil.isCellDateFormatted(cell)) { java.util.Date df = HSSFDateUtil.getJavaDate(Double.parseDouble(val)); String df_full = "yyyy-MM-dd"; DateFormat sdf = new SimpleDateFormat(df_full); val = sdf.format(df); } break; case Cell.CELL_TYPE_STRING: val = cell.getRichStringCellValue().getString(); break; } if (i == 0) { // List of Headers (Consider first row as Headers) jtemp1 = new JSONObject(); jtemp1.put("header", val); jtemp1.put("index", j); jobj.append("Header", jtemp1); } obj.put(colHeader, val); } jArr.put(obj); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "Image has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.esp.servlets.XLSDataExtractor.java
License:Open Source License
public JSONObject parseXLS1(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); ArrayList<String> arr = new ArrayList<String>(); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; JSONArray jArr = new JSONArray(); try {//from w ww .j a v a2 s . co m for (int i = 0; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { jArr.put(obj); continue; } if (maxCol < row.getLastCellNum()) maxCol = row.getLastCellNum(); for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); String val = null; if (cell == null) { arr.add(val); continue; } ; String colHeader = new CellReference(i, j).getCellRefParts()[2]; switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: val = Double.toString(cell.getNumericCellValue()); if (HSSFDateUtil.isCellDateFormatted(cell)) { java.util.Date df = HSSFDateUtil.getJavaDate(Double.parseDouble(val)); String df_full = "yyyy-MM-dd"; DateFormat sdf = new SimpleDateFormat(df_full); val = sdf.format(df); } break; case Cell.CELL_TYPE_STRING: val = cell.getRichStringCellValue().getString(); break; } if (i == 0) { // List of Headers (Consider first row as Headers) jtemp1 = new JSONObject(); jtemp1.put("header", val); jtemp1.put("index", j); jobj.append("Header", jtemp1); obj.put(colHeader, val); arr.add(val); } else { if (arr.get(j) != null) obj.put(arr.get(j), val); } } jArr.put(obj); } } catch (Exception ex) { Logger.getLogger(XLSDataExtractor.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "Image has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.spring.importFunctionality.ImportHandler.java
License:Open Source License
public JSONObject parseXLS(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; int noOfRowsDisplayforSample = 20; if (noOfRowsDisplayforSample > sheet.getLastRowNum()) { noOfRowsDisplayforSample = sheet.getLastRowNum(); }// w ww . jav a 2 s. com JSONArray jArr = new JSONArray(); try { for (int i = 0; i <= noOfRowsDisplayforSample; i++) { HSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i == 0) { maxCol = row.getLastCellNum(); } for (int cellcount = 0; cellcount < maxCol; cellcount++) { HSSFCell cell = row.getCell(cellcount); CellReference cref = new CellReference(i, cellcount); String colHeader = cref.getCellRefParts()[2]; String val = null; if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = Double.toString(cell.getNumericCellValue()); java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val)); DateFormat sdf = new SimpleDateFormat(df); val = sdf.format(date1); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = cleanHTML(cell.getRichStringCellValue().getString()); break; } } if (i == 0) { // List of Headers (Consider first row as Headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val == null ? "" : val); jtemp1.put("index", cellcount); jobj.append("Header", jtemp1); } } obj.put(colHeader, val); } // if(obj.length()>0){ //Don't show blank row in preview grid[SK] jArr.put(obj); // } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLS has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.spring.importFunctionality.ImportHandler.java
License:Open Source License
public JSONObject parseXLS1(String filename, int sheetNo, int startindex) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); ArrayList<String> arr = new ArrayList<String>(); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; JSONArray jArr = new JSONArray(); try {// w w w. j a v a 2s . c om for (int i = startindex; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i == startindex) { maxCol = row.getLastCellNum(); } for (int j = 0; j < maxCol; j++) { HSSFCell cell = row.getCell(j); String val = null; if (cell == null) { arr.add(val); continue; } ; String colHeader = new CellReference(i, j).getCellRefParts()[2]; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = Double.toString(cell.getNumericCellValue()); java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val)); DateFormat sdf = new SimpleDateFormat(df); val = sdf.format(date1); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = cleanHTML(cell.getRichStringCellValue().getString()); break; } if (i == startindex) { // List of Headers (consider startindex row as a headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val); jtemp1.put("index", j); jobj.append("Header", jtemp1); obj.put(colHeader, val); } arr.add(val); } else { if (arr.get(j) != null) obj.put(arr.get(j), val); } } if (obj.length() > 0) { jArr.put(obj); } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLS has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.spring.importFunctionality.ImportUtil.java
License:Open Source License
/** * Generate the preview of the xls grid//from w ww. j av a2s . co m * @param filename * @param sheetNo * @return * @throws FileNotFoundException * @throws IOException * @throws JSONException */ public static JSONObject parseXLS(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); //DateFormat sdf = new SimpleDateFormat(df); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; int maxSheetColCnt = 0; int noOfRowsDisplayforSample = 20; if (noOfRowsDisplayforSample > sheet.getLastRowNum()) { noOfRowsDisplayforSample = sheet.getLastRowNum(); } int firstValidRec = 0; JSONArray jArr = new JSONArray(); try { for (int i = 0; i <= noOfRowsDisplayforSample; i++) { HSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i != 0 && firstValidRec == 0 && !jobj.has("Header")) // get first valid row which having some columns with data as a header firstValidRec = i; // if(i==0) { maxCol = row.getLastCellNum(); if (maxSheetColCnt < maxCol) // get max column count maxSheetColCnt = maxCol; // } for (int cellcount = 0; cellcount < maxCol; cellcount++) { HSSFCell cell = row.getCell(cellcount); CellReference cref = new CellReference(i, cellcount); String colHeader = cref.getCellRefParts()[2]; String val = null; if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = cell.toString();//Long.toString(cell.getDateCellValue().getTime()); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString()); break; } } if (i == firstValidRec) { // List of Headers (Consider first row as Headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val == null ? "" : val); jtemp1.put("index", cellcount); jobj.append("Header", jtemp1); } } obj.put(colHeader, val); } // if(obj.length()>0){ //Don't show blank row in preview grid[SK] jArr.put(obj); // } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxSheetColCnt); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLS has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:de.ingrid.iplug.excel.service.SheetsService.java
License:EUPL
/** * Create sheets.//from w w w . j a v a2s. com * * @param inputStream * @return Created sheets. * @throws IOException */ public static Sheets createSheets(final InputStream inputStream) throws IOException { // sheets final Sheets sheets = new Sheets(); // create workbook final Workbook workbook = new HSSFWorkbook(inputStream); final FormulaEvaluator eval = new HSSFFormulaEvaluator((HSSFWorkbook) workbook); for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { final org.apache.poi.ss.usermodel.Sheet poiSheet = workbook.getSheetAt(sheetNum); // ingrid sheet final Sheet sheet = new Sheet(); sheet.setSheetIndex(sheetNum); sheets.addSheet(sheet); final Values values = new Values(); sheet.setValues(values); for (final org.apache.poi.ss.usermodel.Row poiRow : poiSheet) { boolean hasValues = false; final Map<Point, Comparable<? extends Object>> valuesInCell = new HashMap<Point, Comparable<? extends Object>>(); for (final Cell poiCell : poiRow) { Comparable<? extends Object> value = null; switch (poiCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = new Boolean(poiCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(poiCell)) { value = getFormattedDateString(poiCell); } else { value = new Double(poiCell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: value = poiCell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: value = calculateFormula(poiCell, eval); break; default: value = ""; break; } // trim strings if (value instanceof String) { value = ((String) value).trim(); } // only add if at least one value does exist in row if (!value.equals("")) { hasValues = true; // ingrid column if (sheet.getColumn(poiCell.getColumnIndex()) == null) { final Column column = new Column(poiCell.getColumnIndex()); sheet.addColumn(column); } } // ingrid point and value final Point point = new Point(poiCell.getColumnIndex(), poiCell.getRowIndex()); valuesInCell.put(point, value); } // ingrid row // ! only add if at least one value does exist if (hasValues) { final Row row = new Row(poiRow.getRowNum()); sheet.addRow(row); for (final Point point : valuesInCell.keySet()) { // if (sheet.getColumn(point.getX()) != null) { values.addValue(point, valuesInCell.get(point)); } } } } } return sheets; }
From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ImportWorkbook.java
License:Open Source License
/** * Initially reads the complete Excel workbook and calculates all formulaCells. After that, we can * be sure that no cell contains formulas any longer, but they all contain values (or nothing). *///from ww w . ja v a 2 s. co m protected void calculateAllFormulas() { FormulaEvaluator evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) getWb()); // iterate over all sheets, their rows, and all their cells for (int sheetNum = 0; sheetNum < getWb().getNumberOfSheets(); sheetNum++) { Sheet sheet = getWb().getSheetAt(sheetNum); for (Row row : sheet) { for (Cell cell : row) { if ((cell.getCellType() == Cell.CELL_TYPE_FORMULA) && (cell.getCellFormula() != null)) { try { evaluator.evaluateInCell(cell); } catch (Exception e) { getProcessingLog().error("Error calculating the formula in cell [" + ExcelImportUtilities.getCellRef(cell) + "] on sheet '" + sheet.getSheetName() + "', using cached value instead: " + e.getLocalizedMessage()); } } } } } }
From source file:fll.util.ExcelCellReader.java
License:Open Source License
/** * Read an excel file from the specified stream. * // w w w. j av a2 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(); }