List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getCellFormula
public String getCellFormula()
From source file:com.example.selenium.util.ApachePOIXLSReader.java
private static Object cellToObject(HSSFCell cell) { Object result;/*from www . ja v a 2 s. c om*/ switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: result = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; default: throw new RuntimeException("Unknown Cell Type"); } return result; }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
protected void updateFormulas(Area templateArea, Area dependentResultArea) { HSSFSheet templateSheet = getTemplateSheetForRangeName(templateWorkbook, templateArea.getName()); HSSFSheet resultSheet = templateToResultSheetsMapping.get(templateSheet); AreaReference area = dependentResultArea.toAreaReference(); for (CellReference cell : area.getAllReferencedCells()) { HSSFCell resultCell = getCellFromReference(cell, resultSheet); if (resultCell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { Ptg[] ptgs = HSSFFormulaParser.parse(resultCell.getCellFormula(), resultWorkbook); for (Ptg ptg : ptgs) { if (ptg instanceof AreaPtg) { areaDependencyManager.updateAreaPtg(templateArea, dependentResultArea, (AreaPtg) ptg); } else if (ptg instanceof RefPtg) { areaDependencyManager.updateRefPtg(templateArea, dependentResultArea, (RefPtg) ptg); }/* w ww. j a v a2 s .co m*/ } String calculatedFormula = HSSFFormulaParser.toFormulaString(templateWorkbook, ptgs); resultCell.setCellFormula(calculatedFormula); } } }
From source file:com.jitendrasinghnz.excelreadutility.ExcelReadStringArrayXSL.java
License:Open Source License
public String convertCellToString(HSSFCell cell) { int type;//from w ww. jav a 2 s. c om if (cell == null) { type = Cell.CELL_TYPE_BLANK; } else { type = cell.getCellType(); } Object result; switch (type) { case Cell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: result = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_BLANK: result = ""; break; case Cell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: result = cell.getErrorCellValue(); break; case Cell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; default: throw new RuntimeException("There are no support fot the cell type"); } return result.toString(); }
From source file:com.learn.core.utils.HSSFReadWrite.java
License:Apache License
/** * Method main//from w w w. j ava2 s .c o m * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out .println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } System.out.println("\nROW " + row.getRowNum() + " has " + row.getPhysicalNumberOfCells() + " cell(s)."); for (int c = 0; c < row.getLastCellNum(); c++) { HSSFCell cell = row.getCell(c); String value; if (cell != null) { switch (cell.getCellTypeEnum()) { case FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case STRING: value = "STRING value=" + cell.getStringCellValue(); break; case BLANK: value = "<BLANK>"; break; case BOOLEAN: value = "BOOLEAN value-" + cell.getBooleanCellValue(); break; case ERROR: value = "ERROR value=" + cell.getErrorCellValue(); break; default: value = "UNKNOWN value of type " + cell.getCellTypeEnum(); } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } } } else if (args.length == 2) { if (args[1].toLowerCase(Locale.ROOT).equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { try (FileOutputStream stream = new FileOutputStream(args[1])) { wb.write(stream); } } } } else if (args.length == 3 && args[2].equalsIgnoreCase("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); try (FileOutputStream stream = new FileOutputStream(args[1])) { wb.write(stream); } } } } catch (Exception e) { e.printStackTrace(); } }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
/** * This is a helper method to retrieve the value of a cell regardles of its * type, which will be converted into a String. * //from w w w.j a v a2 s .c om * @param cell * @return */ private Object getCellValue(HSSFCell cell) { if (cell == null) { return null; } Object result = null; int cellType = cell.getCellType(); switch (cellType) { case HSSFCell.CELL_TYPE_BLANK: result = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE; break; case HSSFCell.CELL_TYPE_ERROR: result = "ERROR: " + cell.getErrorCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: HSSFCellStyle cellStyle = cell.getCellStyle(); short dataFormat = cellStyle.getDataFormat(); // assumption is made that dataFormat = 14, // when cellType is HSSFCell.CELL_TYPE_NUMERIC // is equal to a DATE format. if (dataFormat == 164) { result = cell.getDateCellValue(); } else { result = cell.getNumericCellValue(); } break; case HSSFCell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; default: break; } if (result instanceof Double) { return String.valueOf(((Double) result).longValue()); } if (result instanceof Date) { return result; } return result.toString(); }
From source file:com.siva.javamultithreading.ExcelUtil.java
public static void copyCell(HSSFWorkbook newWorkbook, HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) { if (styleMap != null) { int stHashCode = oldCell.getCellStyle().hashCode(); HSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newWorkbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, newCellStyle); }/*from w ww .j a v a 2 s . c om*/ newCell.setCellStyle(newCellStyle); } switch (oldCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
From source file:com.xhsoft.framework.common.file.ExcelHandle.java
License:Open Source License
/** * ????.xls?//from w w w . j ava2 s. c o m * @params {:,:} * @return String * @author lijiangwei * @since 2012-11-12 */ private String getCellValue(HSSFCell xls_cell) { String value = ""; switch (xls_cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: value = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: value = String.valueOf(xls_cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: break; case HSSFCell.CELL_TYPE_FORMULA: String.valueOf(xls_cell.getCellFormula()); break; case HSSFCell.CELL_TYPE_NUMERIC: value = String.valueOf(xls_cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_STRING: value = String.valueOf(xls_cell.getStringCellValue()); break; default: break; } return value; }
From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java
License:Open Source License
/** * Exportiert die bergebene excel-Datei in eine Liste mit zweidimensionalen Arrays fr jeweils * ein sheet in der excel-Datei.//from ww w .ja va2 s . c o m * * @param excelSheet * Die excel-Datei. * @return Gibt eine Liste mit zweidimensionalen Arrays fr jeweils ein sheet in der excel-Datei * zurck. * @throws IOException * Fals ein Fehler beim Lesen aufgetreten ist. * @throws FileNotFoundException * Fals die excel-Datei nicht gefunden wurde. */ public static List<String[][]> exportWorkbook(final File excelSheet) throws IOException, FileNotFoundException { final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); final HSSFWorkbook wb = new HSSFWorkbook(fs); final int numberOfSheets = wb.getNumberOfSheets(); final List<String[][]> sheetList = new ArrayList<>(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); final int rows = sheet.getLastRowNum(); final int columns = sheet.getRow(0).getLastCellNum(); String[][] excelSheetInTDArray = null; excelSheetInTDArray = new String[rows + 1][columns]; for (int i = 0; i <= rows; i++) { final HSSFRow row = sheet.getRow(i); if (null != row) { for (int j = 0; j < columns; j++) { final HSSFCell cell = row.getCell(j); if (null == cell) { excelSheetInTDArray[i][j] = ""; } else { final int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_BLANK) { excelSheetInTDArray[i][j] = ""; } else if (cellType == Cell.CELL_TYPE_BOOLEAN) { excelSheetInTDArray[i][j] = Boolean.toString(cell.getBooleanCellValue()); } else if (cellType == Cell.CELL_TYPE_ERROR) { excelSheetInTDArray[i][j] = ""; } else if (cellType == Cell.CELL_TYPE_FORMULA) { excelSheetInTDArray[i][j] = cell.getCellFormula(); } else if (cellType == Cell.CELL_TYPE_NUMERIC) { excelSheetInTDArray[i][j] = Double.toString(cell.getNumericCellValue()); } else if (cellType == Cell.CELL_TYPE_STRING) { excelSheetInTDArray[i][j] = cell.getRichStringCellValue().getString(); } } } } } sheetList.add(excelSheetInTDArray); } wb.close(); return sheetList; }
From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java
License:Open Source License
/** * Exportiert die bergebene excel-Datei in eine geschachtelte Liste mit Listen von sheets und * Listen von den Zeilen der sheets von der excel-Datei. * * @param excelSheet//from w ww . ja va 2 s.c om * Die excel-Datei. * @return Gibt eine Liste mit Listen von den sheets in der excel-Datei zurck. Die Listen mit * den sheets beinhalten weitere Listen mit String die jeweils eine Zeile * reprsentieren. * @throws IOException * Fals ein Fehler beim Lesen aufgetreten ist. * @throws FileNotFoundException * Fals die excel-Datei nicht gefunden wurde. */ public static List<List<List<String>>> exportWorkbookAsStringList(final File excelSheet) throws IOException, FileNotFoundException { final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); final HSSFWorkbook wb = new HSSFWorkbook(fs); final int numberOfSheets = wb.getNumberOfSheets(); final List<List<List<String>>> sl = new ArrayList<>(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); final int rows = sheet.getLastRowNum(); final int columns = sheet.getRow(0).getLastCellNum(); final List<List<String>> excelSheetList = new ArrayList<>(); for (int i = 0; i <= rows; i++) { final HSSFRow row = sheet.getRow(i); if (null != row) { final List<String> reihe = new ArrayList<>(); for (int j = 0; j < columns; j++) { final HSSFCell cell = row.getCell(j); if (null == cell) { reihe.add(""); } else { final int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_BLANK) { reihe.add(""); } else if (cellType == Cell.CELL_TYPE_BOOLEAN) { reihe.add(Boolean.toString(cell.getBooleanCellValue())); } else if (cellType == Cell.CELL_TYPE_ERROR) { reihe.add(""); } else if (cellType == Cell.CELL_TYPE_FORMULA) { reihe.add(cell.getCellFormula()); } else if (cellType == Cell.CELL_TYPE_NUMERIC) { reihe.add(Double.toString(cell.getNumericCellValue())); } else if (cellType == Cell.CELL_TYPE_STRING) { reihe.add(cell.getRichStringCellValue().getString()); } } } excelSheetList.add(reihe); } } sl.add(excelSheetList); } wb.close(); return sl; }
From source file:edu.fcps.hutchison.LabelBuilder.java
License:Open Source License
protected String getCellValStr(HSSFCell cell) { if (cell == null) { return ""; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { return Boolean.toString(cell.getBooleanCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { return Double.toString(cell.getNumericCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { return cell.getRichStringCellValue().toString(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { return ""; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { return cell.getCellFormula(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR) { return String.valueOf(cell.getErrorCellValue()); } else {/*from w w w.ja v a 2 s .c om*/ return ""; } }