List of usage examples for org.apache.poi.xssf.usermodel XSSFCell getCellFormula
@Override
public String getCellFormula()
SUM(C4:E4) From source file:mx.infotec.dads.arq.excel.ImplementExcel.java
public String getStringCellValue(XSSFCell cell) { String value;/*ww w . j a va 2 s.com*/ switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue() + " "; break; case XSSFCell.CELL_TYPE_NUMERIC: Double n = cell.getNumericCellValue(); if (HSSFDateUtil.isCellDateFormatted(cell)) { value = cell.getDateCellValue().toString(); } else { value = n + " "; } break; case XSSFCell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue() + " "; break; case XSSFCell.CELL_TYPE_FORMULA: value = cell.getCellFormula(); break; case XSSFCell.CELL_TYPE_ERROR: value = cell.getErrorCellString(); break; default: value = "error"; break; } return value; }
From source file:mx.infotec.dads.arq.excel.ImplementExcel.java
public Object getCellValue(XSSFCell cell) { Object value;/*from w w w.j a v a2 s . c om*/ switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { value = cell.getDateCellValue(); } else { value = cell.getNumericCellValue(); } break; case XSSFCell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case XSSFCell.CELL_TYPE_FORMULA: value = cell.getCellFormula(); break; case XSSFCell.CELL_TYPE_ERROR: value = cell.getErrorCellString(); break; default: value = "error"; break; } return value; }
From source file:net.mcnewfamily.rmcnew.shared.Util.java
License:Open Source License
public static void copyXSSFCell(XSSFCell srcCell, XSSFCell destCell) { if (srcCell != null && destCell != null) { switch (srcCell.getCellType()) { case Cell.CELL_TYPE_STRING: destCell.setCellType(Cell.CELL_TYPE_STRING); destCell.setCellValue(srcCell.getRichStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: destCell.setCellType(Cell.CELL_TYPE_NUMERIC); if (DateUtil.isCellDateFormatted(srcCell)) { destCell.setCellValue(srcCell.getDateCellValue()); } else { destCell.setCellValue(srcCell.getNumericCellValue()); }//from www .ja va 2s. c o m break; case Cell.CELL_TYPE_BOOLEAN: destCell.setCellType(Cell.CELL_TYPE_BOOLEAN); destCell.setCellValue(srcCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: destCell.setCellType(Cell.CELL_TYPE_FORMULA); destCell.setCellValue(srcCell.getCellFormula()); break; } copyXSSFCellStyle(srcCell, destCell); } else { throw new IllegalArgumentException("Cannot copy from / to null XSSFCell!"); } }
From source file:net.openchrom.xxd.processor.supplier.rscripting.ui.jobs.LoadXlsxExcelJob.java
License:Open Source License
private void loadExcel(final String file) { final File fil = new File(file); if (fil.exists()) { canRead = true;//from w w w. ja v a 2 s. c om if (grid != null) { try { InputStream inp = new FileInputStream(file); try { wb = new XSSFWorkbook(inp); } catch (Exception e) { MsgDialog.message("Wrong format!\nOnly Excel *.xlsx (2007-2010) is supported!"); canRead = false; e.printStackTrace(); } // wb = new HSSFWorkbook(inp); } catch (IOException ex) { ex.printStackTrace(); } if (canRead) { for (s = 0; s < wb.getNumberOfSheets(); s++) { Display display = PlatformUI.getWorkbench().getDisplay(); display.syncExec(new Runnable() { public void run() { String name = fil.getName(); grid = new Spread().spread(SampleView.getTabFolder(), 0, 0, name); SampleView.setGrid(grid); XSSFSheet sheet = wb.getSheetAt(s); int colCount = grid.getColumnCount(); int rowCount = grid.getItemCount(); int exelRow = endOfRow(sheet); int exelColumn = endOfColumn(sheet); // System.out.println(exelRow + " " + exelColumn // + "---" + sheet.getPhysicalNumberOfRows() + // " " + // sheet.getRow(0).getPhysicalNumberOfCells()); if (colCount < exelColumn) { int diff = exelColumn - colCount; for (int i = 0; i < diff; i++) { GridColumn column = new GridColumn(grid, SWT.NONE); column.setText("C " + (i + 1 + colCount)); column.setWidth(50); } } if (rowCount < exelRow) { int diff = exelRow - rowCount; for (int i = 0; i < diff; i++) { new GridItem(grid, SWT.NONE).setHeight(16); } } // Iterate over each row in the sheet int rows = sheet.getPhysicalNumberOfRows(); for (int i = 0; i < exelRow; i++) { XSSFRow row = sheet.getRow(i); if (row == null) { for (int u = 0; u < exelColumn; u++) { grid.getItem(i).setText(u, " "); } } else { for (int u = 0; u < exelColumn; u++) { XSSFCell cell = row.getCell(u); if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: String val = String.valueOf(cell.getNumericCellValue()); grid.getItem(i).setText(u, val); break; case XSSFCell.CELL_TYPE_STRING: XSSFRichTextString st = cell.getRichStringCellValue(); String val2 = st.getString(); grid.getItem(i).setText(u, val2); break; case XSSFCell.CELL_TYPE_FORMULA: try { String val3 = String.valueOf(cell.getRawValue()); grid.getItem(i).setText(u, val3); } catch (Exception e) { // System.out.println(e.getMessage()); String s2 = cell.getCellFormula(); grid.getItem(i).setText(u, s2); } break; case XSSFCell.CELL_TYPE_BLANK: grid.getItem(i).setText(u, " "); break; case XSSFCell.CELL_TYPE_BOOLEAN: boolean s4 = cell.getBooleanCellValue(); if (s4) { grid.getItem(i).setText(u, "TRUE"); } else { grid.getItem(i).setText(u, "FALSE"); } break; default: break; } } else { grid.getItem(i).setText(u, " "); } } } } } }); } wb = null; } } } else { MsgDialog.message("File not found!"); } }
From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java
License:Apache License
private void copyRow(XSSFRow sourceRow, XSSFRow targetRow, XSSFCreationHelper factory, XSSFDrawing patriarch) { for (int j = 0; j < sourceRow.getPhysicalNumberOfCells(); j++) { XSSFCell cell = sourceRow.getCell(j); if (cell != null) { XSSFCell newCell = targetRow.createCell(j); int cellType = cell.getCellType(); newCell.setCellType(cellType); switch (cellType) { case XSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(cell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(cell.getErrorCellValue()); break; case XSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(cell.getCellFormula()); break; case XSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: newCell.setCellValue(cell.getRichStringCellValue()); break; default: newCell.setCellValue(formatter.formatCellValue(cell)); }/* www . j a v a2 s .co m*/ if (cell.getCellComment() != null) { XSSFClientAnchor anchor = factory.createClientAnchor(); anchor.setDx1(100); anchor.setDx2(100); anchor.setDy1(100); anchor.setDy2(100); anchor.setCol1(newCell.getColumnIndex()); anchor.setCol2(newCell.getColumnIndex() + 4); anchor.setRow1(newCell.getRowIndex()); anchor.setRow2(newCell.getRowIndex() + 4); anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE); XSSFComment comment = patriarch.createCellComment(anchor); comment.setString(cell.getCellComment().getString()); newCell.setCellComment(comment); } newCell.setCellStyle(cell.getCellStyle()); newCell.getSheet().setColumnWidth(newCell.getColumnIndex(), cell.getSheet().getColumnWidth(cell.getColumnIndex())); } } }
From source file:org.exoplatform.services.document.impl.MSXExcelDocumentReader.java
License:Open Source License
/** * Returns only a text from .xlsx file content. * /*from w ww. j ava 2s . c o m*/ * @param is an input stream with .xls file content. * @return The string only with text from file content. */ public String getContentAsText(final InputStream is) throws IOException, DocumentReadException { if (is == null) { throw new IllegalArgumentException("InputStream is null."); } StringBuilder builder = new StringBuilder(""); SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT); try { if (is.available() == 0) { return ""; } XSSFWorkbook wb; try { wb = SecurityHelper.doPrivilegedIOExceptionAction(new PrivilegedExceptionAction<XSSFWorkbook>() { public XSSFWorkbook run() throws Exception { return new XSSFWorkbook(is); } }); } catch (IOException e) { throw new DocumentReadException("Can't open spreadsheet.", e); } catch (OpenXML4JRuntimeException e) { return builder.toString(); } for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { XSSFSheet sheet = wb.getSheetAt(sheetNum); if (sheet != null) { for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) { XSSFRow row = sheet.getRow(rowNum); if (row != null) { int lastcell = row.getLastCellNum(); for (int k = 0; k < lastcell; k++) { XSSFCell cell = row.getCell(k); if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: { double d = cell.getNumericCellValue(); if (isCellDateFormatted(cell)) { Date date = HSSFDateUtil.getJavaDate(d); String cellText = dateFormat.format(date); builder.append(cellText).append(" "); } else { builder.append(d).append(" "); } break; } case XSSFCell.CELL_TYPE_FORMULA: builder.append(cell.getCellFormula().toString()).append(" "); break; case XSSFCell.CELL_TYPE_BOOLEAN: builder.append(cell.getBooleanCellValue()).append(" "); break; case XSSFCell.CELL_TYPE_ERROR: builder.append(cell.getErrorCellValue()).append(" "); break; case XSSFCell.CELL_TYPE_STRING: builder.append(cell.getStringCellValue().toString()).append(" "); break; default: break; } } } } } } } } finally { if (is != null) { try { is.close(); } catch (IOException e) { if (LOG.isTraceEnabled()) { LOG.trace("An exception occurred: " + e.getMessage()); } } } } return builder.toString(); }
From source file:org.kuali.test.runner.output.PoiHelper.java
License:Educational Community License
private void copyCell(XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) { if (styleMap != null) { int stHashCode = oldCell.getCellStyle().hashCode(); XSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCellStyle.setFont(oldCell.getCellStyle().getFont()); styleMap.put(stHashCode, newCellStyle); }/*from w ww .j av a 2 s .c om*/ newCell.setCellStyle(newCellStyle); } switch (oldCell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case XSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case XSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case XSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
From source file:org.talend.dataprep.qa.util.ExcelComparator.java
License:Open Source License
public static boolean compareTwoCells(XSSFCell cell1, XSSFCell cell2) { if ((cell1 == null) && (cell2 == null)) { return true; } else if ((cell1 == null) || (cell2 == null)) { return false; }// ww w . ja v a2 s .c o m boolean equalCells = false; int type1 = cell1.getCellTypeEnum().getCode(); int type2 = cell2.getCellTypeEnum().getCode(); if (type1 == type2) { if (cell1.getCellStyle().equals(cell2.getCellStyle())) { // Compare cells based on its type switch (cell1.getCellTypeEnum().getCode()) { case HSSFCell.CELL_TYPE_FORMULA: if (cell1.getCellFormula().equals(cell2.getCellFormula())) { equalCells = true; } break; case HSSFCell.CELL_TYPE_NUMERIC: if (cell1.getNumericCellValue() == cell2.getNumericCellValue()) { equalCells = true; } break; case HSSFCell.CELL_TYPE_STRING: if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) { equalCells = true; } break; case HSSFCell.CELL_TYPE_BLANK: if (cell2.getCellType() == HSSFCell.CELL_TYPE_BLANK) { equalCells = true; } break; case HSSFCell.CELL_TYPE_BOOLEAN: if (cell1.getBooleanCellValue() == cell2.getBooleanCellValue()) { equalCells = true; } break; case HSSFCell.CELL_TYPE_ERROR: if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) { equalCells = true; } break; default: if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) { equalCells = true; } break; } } else { return false; } } else { return false; } return equalCells; }
From source file:ReadExcel.HSSFReadWrite.java
License:Apache License
private static void startReadXlsxFile(String fileName) { try {//from www . ja v a 2s . c o m XSSFWorkbook wb = HSSFReadWrite.readxlsxFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { XSSFSheet 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++) { XSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { XSSFCell cell = row.getCell(c); String value = 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; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } wb.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:sv.com.mined.sieni.controller.GestionNotasController.java
public static void copyRow(XSSFSheet worksheetSource, XSSFSheet worksheetDestination, int sourceRowNum, int destinationRowNum) { // Get the source / new row XSSFRow origen = worksheetSource.getRow(sourceRowNum); XSSFRow destino = worksheetDestination.createRow(destinationRowNum); // Loop through source columns to add to new row for (int i = 0; i < origen.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = origen.getCell(i); XSSFCell newCell = destino.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null;// www . ja va 2 s . c o m continue; } //Ajustar tamaos columnas worksheetDestination.setColumnWidth(i, worksheetSource.getColumnWidth(i)); // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.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: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } }