List of usage examples for org.apache.poi.ss.usermodel Cell getCellFormula
String getCellFormula();
SUM(C4:E4) From source file:hjow.hgtable.util.XLSXUtil.java
License:Apache License
/** * <p>XLSX ? ? ?? . ? ? ?? ?? , ? ? ?? ? ? ?? ?.</p> * //from www. j a va 2 s . co m * @param file : XLSX ? * @return ? ? */ public static List<TableSet> toTableSets(File file) { List<TableSet> tableSets = new Vector<TableSet>(); org.apache.poi.ss.usermodel.Workbook workbook = null; if (file == null) throw new NullPointerException(Manager.applyStringTable("Please select file !!")); if (!file.exists()) throw new NullPointerException(Manager.applyStringTable("File") + " " + file.getAbsolutePath() + " " + Manager.applyStringTable("is not exist")); boolean isHead = true; int rowNum = 0; int cellNum = 0; int cellCount = 0; FileInputStream fileStream = null; try { if (file.getAbsolutePath().endsWith(".xlsx") || file.getAbsolutePath().endsWith(".XLSX")) { workbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook(file); } else if (file.getAbsolutePath().endsWith(".xls") || file.getAbsolutePath().endsWith(".XLS")) { fileStream = new FileInputStream(file); workbook = new org.apache.poi.hssf.usermodel.HSSFWorkbook(fileStream); } org.apache.poi.ss.usermodel.FormulaEvaluator evals = workbook.getCreationHelper() .createFormulaEvaluator(); org.apache.poi.ss.usermodel.Sheet sheet = null; for (int x = 0; x < workbook.getNumberOfSheets(); x++) { TableSet newTableSet = new DefaultTableSet(); newTableSet.setColumns(new Vector<Column>()); sheet = workbook.getSheetAt(x); newTableSet.setName(sheet.getSheetName()); rowNum = 0; isHead = true; String targetData = null; for (org.apache.poi.ss.usermodel.Row row : sheet) { cellNum = 0; for (org.apache.poi.ss.usermodel.Cell cell : row) { try { if (cellNum >= cellCount) { throw new IndexOutOfBoundsException( Manager.applyStringTable("There are some cells not have their heads") + ", " + Manager.applyStringTable("Head count") + " : " + cellCount + ", " + Manager.applyStringTable("Cell Number") + " : " + cellNum); } switch (cell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: if (isHead) { newTableSet.getColumns().add(new Column( cell.getRichStringCellValue().getString(), Column.TYPE_STRING)); } else { targetData = cell.getRichStringCellValue().getString(); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_DATE)); } else { targetData = String.valueOf(cell.getDateCellValue()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } else { if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC)); } else { double values = cell.getNumericCellValue(); double intPart = values - ((double) ((int) values)); if (intPart == 0.0) { targetData = String.valueOf(((int) values)); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_INTEGER); } else { targetData = String.valueOf(values); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_BOOLEAN)); } else { targetData = String.valueOf(cell.getBooleanCellValue()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA: if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC)); } else { if (evals.evaluateFormulaCell(cell) == 0) { targetData = String.valueOf(cell.getNumericCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_NUMERIC); } else if (evals.evaluateFormulaCell(cell) == 1) { targetData = String.valueOf(cell.getStringCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_STRING); } else if (evals.evaluateFormulaCell(cell) == 4) { targetData = String.valueOf(cell.getBooleanCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BOOLEAN); } else { targetData = String.valueOf(cell.getCellFormula()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: if (isHead) { newTableSet.getColumns().add(new Column("", Column.TYPE_STRING)); } else { targetData = ""; newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BLANK); } break; default: if (isHead) { newTableSet.getColumns().add(new Column("", Column.TYPE_STRING)); } else { try { targetData = cell.getStringCellValue(); } catch (Exception e1) { e1.printStackTrace(); } newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; } if (isHead) { cellCount++; } else { while (rowNum > 0 && newTableSet.getColumns().get(cellNum).getData().size() < rowNum) { newTableSet.getColumns().get(cellNum).getData().add(""); } if (targetData != null) newTableSet.getColumns().get(cellNum).getData().add(targetData); else { newTableSet.getColumns().get(cellNum).getData().add(""); } } } catch (ArrayIndexOutOfBoundsException e1) { StringBuffer err = new StringBuffer(""); for (StackTraceElement errEl : e1.getStackTrace()) { err = err.append("\t " + errEl + "\n"); } String cellObject = null; try { cellObject = cell.getStringCellValue(); } catch (Exception e2) { } throw new ArrayIndexOutOfBoundsException( Manager.applyStringTable("Array index out of range") + " <- " + Manager.applyStringTable("Reading xlsx file") + " : " + file.getName() + ", " + sheet.getSheetName() + "\n" + Manager.applyStringTable("On") + " " + Manager.applyStringTable("Row") + " " + rowNum + ", " + Manager.applyStringTable("Cell") + " " + cellNum + ", " + Manager.applyStringTable("Value") + " : " + String.valueOf(cellObject) + "\n " + Manager.applyStringTable("<-\n") + err + "\n " + Manager.applyStringTable("Original Message") + "...\n" + e1.getMessage() + "\n" + Manager.applyStringTable("End")); } cellNum++; } isHead = false; rowNum++; } fillTableSet(newTableSet); newTableSet.removeEmptyColumn(true); tableSets.add(newTableSet); } return tableSets; } catch (Throwable e) { if (Main.MODE >= DebuggingUtil.DEBUG) e.printStackTrace(); Main.logError(e, Manager.applyStringTable("On reading xlsx") + " : " + file + "\n" + Manager.applyStringTable("At rownum") + " " + rowNum + ", " + Manager.applyStringTable("cellnum") + " " + cellNum); return null; } finally { try { workbook.close(); } catch (Throwable e) { } try { if (fileStream != null) fileStream.close(); } catch (Throwable e) { } } }
From source file:invoiceapplication.CopyRowOriginal.java
public static void copyRow(Sheet worksheet, int sourceRowNum, int destRowNum) { // Get the source / new row Row newRow = worksheet.getRow(destRowNum); Row sourceRow = worksheet.getRow(sourceRowNum); // If the row exists in destination, push down all rows by 1 else create a new row if (newRow != null) { worksheet.shiftRows(newRow.getRowNum(), worksheet.getLastRowNum(), 1, true, true); } else {/*from ww w. jav a2s . c om*/ newRow = worksheet.createRow(destRowNum); } copyAnyMergedRegions(worksheet, sourceRow, newRow); // Loops through source column to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { //Grab a copy of the old/new cell Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); // if the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Use old cell style newCell.setCellStyle(oldCell.getCellStyle()); // If there is a cell comment, copy if (newCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellValue(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; } } }
From source file:javacommon.excel.ExcelReader.java
/** * ???//from w w w. ja v a2s .c o m * * @param c ? * @return */ private String getCellStringValue(Cell c) { if (c == null) { return ""; } String value = null; NumberFormat nf = NumberFormat.getInstance(); nf.setGroupingUsed(false); nf.setMaximumFractionDigits(12); switch (c.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = String.valueOf(c.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(c)) { return DateFormatUtils.ISO_DATE_FORMAT.format(c.getDateCellValue()); } else if ("@".equals(c.getCellStyle().getDataFormatString())) { value = nf.format(c.getNumericCellValue()); } else if ("General".equals(c.getCellStyle().getDataFormatString())) { value = nf.format(c.getNumericCellValue()); } else if (ArrayUtils.contains(ExcelConstants.DATE_PATTERNS, c.getCellStyle().getDataFormatString())) { value = DateFormatUtils.format(HSSFDateUtil.getJavaDate(c.getNumericCellValue()), c.getCellStyle().getDataFormatString()); } else { value = nf.format(c.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: value = c.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: value = c.getCellFormula(); break; } return value == null ? "" : value.trim(); }
From source file:javacommon.excel.ExcelReader.java
/** * ???//from w w w .j av a 2 s .co m * * @param c ? * @return */ private String getCellStringFormatValue(Cell c) { if (c == null) { return ""; } String value = null; NumberFormat nf = NumberFormat.getInstance(); nf.setGroupingUsed(false); nf.setMaximumFractionDigits(12); switch (c.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = String.valueOf(c.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(c)) { return DateFormatUtils.ISO_DATE_FORMAT.format(c.getDateCellValue()); } else if ("@".equals(c.getCellStyle().getDataFormatString())) { value = nf.format(c.getNumericCellValue()); } else if ("General".equals(c.getCellStyle().getDataFormatString())) { value = nf.format(c.getNumericCellValue()); } else if (ArrayUtils.contains(ExcelConstants.DATE_PATTERNS, c.getCellStyle().getDataFormatString())) { value = DateFormatUtils.format(HSSFDateUtil.getJavaDate(c.getNumericCellValue()), c.getCellStyle().getDataFormatString()); } else { value = nf.format(c.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: value = c.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: return c.getCellFormula(); } return value == null ? "" : value.trim(); }
From source file:javacommon.excel.ExcelReader.java
/** * ???//from w w w .j a v a 2 s . c om * * @param c ? * @return */ private Object getCellValue(Cell c) { if (c == null) { return null; } switch (c.getCellType()) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return c.getBooleanCellValue(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(c)) { return c.getDateCellValue(); } return c.getNumericCellValue(); case Cell.CELL_TYPE_STRING: return c.getStringCellValue(); case Cell.CELL_TYPE_FORMULA: return c.getCellFormula(); } return null; }
From source file:jexcel4py.Jexcel4py.java
private void copyCell(Cell rdCell, Cell wrCell) { // wrCell.setCellStyle(rdCell.getCellStyle()); // wrCell.setCellType(rdCell.getCellType()); int cellStyle = rdCell.getCellType(); String rdCellValue = rdCell.getStringCellValue(); switch (cellStyle) { case Cell.CELL_TYPE_BLANK: wrCell.setCellValue("heys"); break;// ww w . j a v a2s . c om case Cell.CELL_TYPE_BOOLEAN: wrCell.setCellValue(rdCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: wrCell.setCellValue(rdCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: wrCell.setCellValue(rdCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: wrCell.setCellValue(rdCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: wrCell.setCellValue(rdCell.getStringCellValue()); break; default: wrCell.setCellValue("heys"); break; } }
From source file:jp.ryoyamamoto.poiutils.Cells.java
License:Apache License
private static void copyCellValue(Cell source, Cell target) { switch (source.getCellType()) { case Cell.CELL_TYPE_NUMERIC: target.setCellValue(source.getNumericCellValue()); break;/*from w w w .j av a2 s. c o m*/ case Cell.CELL_TYPE_STRING: target.setCellValue(source.getRichStringCellValue()); break; case Cell.CELL_TYPE_FORMULA: target.setCellFormula(source.getCellFormula()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: target.setCellValue(source.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: target.setCellErrorValue(source.getErrorCellValue()); break; } }
From source file:main.KeywordList.java
private String getCellDate(Cell cell) { switch (cell.getCellType()) { case CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return ForcastUi.dateToString(cell.getDateCellValue()); } else {/*from www. j ava 2 s. co m*/ return "01/01/2000"; } case CELL_TYPE_STRING: return cell.getStringCellValue(); case CELL_TYPE_FORMULA: return cell.getCellFormula(); case CELL_TYPE_BLANK: return ""; case CELL_TYPE_BOOLEAN: return Boolean.toString(cell.getBooleanCellValue()); case CELL_TYPE_ERROR: return Byte.toString(cell.getErrorCellValue()); default: return "01/01/2000"; } }
From source file:main.KeywordList.java
private String getCellValue(Cell cell) { switch (cell.getCellType()) { case CELL_TYPE_NUMERIC: return Double.toString(cell.getNumericCellValue()); case CELL_TYPE_STRING: return cell.getStringCellValue(); case CELL_TYPE_FORMULA: return cell.getCellFormula(); case CELL_TYPE_BLANK: return ""; case CELL_TYPE_BOOLEAN: return Boolean.toString(cell.getBooleanCellValue()); case CELL_TYPE_ERROR: return Byte.toString(cell.getErrorCellValue()); default://from w w w.j ava2s . c om return ""; } }
From source file:midas.sheeco.exceptions.SpreadsheetViolation.java
License:Apache License
private static Object getCellValue(final Cell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); }/*from w w w .j av a2 s . c o m*/ return cell.getNumericCellValue(); case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); case Cell.CELL_TYPE_ERROR: return cell.getErrorCellValue(); case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula(); } throw new UnsupportedOperationException("CellType " + cell.getCellType() + " is invalid"); }