List of usage examples for org.apache.poi.ss.usermodel Cell getErrorCellValue
byte getErrorCellValue();
From source file:com.kybelksties.excel.ExcelSheetTableModel.java
License:Open Source License
/** * Insert a row at a given index.// w ww. ja v a 2s.co m * * @param createAtIndex row-number of the cell at which to create a new row * @param sourceRow the row to insert */ public void insertRowAt(int createAtIndex, Row sourceRow) { Row newRow = getRow(createAtIndex); if (newRow != null) { // shift all rows >= createAtIndex up by one getSheet().shiftRows(createAtIndex, getSheet().getLastRowNum(), 1); } else { newRow = getSheet().createRow(createAtIndex); } // Loop through source columns 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) { continue; } // Copy style from old cell and apply to new cell CellStyle newCellStyle = workbook.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; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < getSheet().getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = getSheet().getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); getSheet().addMergedRegion(newCellRangeAddress); } } }
From source file:com.mimp.controllers.reporte.java
private static void copyRow(Sheet worksheet, int sourceRowNum, int destinationRowNum) { // Coge la fila antigua y nueva Row newRow = worksheet.getRow(destinationRowNum); Row sourceRow = worksheet.getRow(sourceRowNum); //Si existe una fila en el detino, pasa todas las filas 1 ms abajo antes de crear la nueva columna if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {//from w w w. j av a 2 s . c om newRow = worksheet.createRow(destinationRowNum); } // Hace un loop entre las celdas de cada columna para aadir una por una a la nueva for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Copia la antigua y nueva celda Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); // Si la anterior celda es null, evalua la siguiente celda defrente if (oldCell == null) { newCell = null; continue; } // Usa el estilo de la celda antigua newCell.setCellStyle(oldCell.getCellStyle()); // Establece el tipo de valor de la celda newCell.setCellType(oldCell.getCellType()); // Establece el valor de la celda switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: 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; } } }
From source file:com.miraisolutions.xlconnect.data.ColumnBuilder.java
License:Open Source License
public void addCell(Cell c) { // In case the cell does not exist ... if (c == null) { this.addMissing(); return;/* ww w . j ava 2 s . c o m*/ } String msg; /* * The following is to handle error cells (before they have been evaluated * to a CellValue) and cells which are formulas but have cached errors. */ if (c.getCellType() == Cell.CELL_TYPE_ERROR || (c.getCellType() == Cell.CELL_TYPE_FORMULA && c.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR)) { msg = "Error detected in cell " + CellUtils.formatAsString(c) + " - " + CellUtils.getErrorMessage(c.getErrorCellValue()); cellError(msg); return; } CellValue cv; // Try to evaluate cell; // report an error if this fails try { cv = getCellValue(c); } catch (Exception e) { msg = "Error when trying to evaluate cell " + CellUtils.formatAsString(c) + " - " + e.getMessage(); cellError(msg); return; } // Not sure if this case should ever happen; // let's be sure anyway if (cv == null) { addMissing(); return; } handleCell(c, cv); }
From source file:com.miraisolutions.xlconnect.data.ColumnBuilder.java
License:Open Source License
protected CellValue getCachedCellValue(Cell cell) { int valueType = cell.getCellType(); if (valueType == Cell.CELL_TYPE_FORMULA) { valueType = cell.getCachedFormulaResultType(); }/*from w w w . j av a 2 s. com*/ switch (valueType) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: if (cell.getBooleanCellValue()) { return CellValue.TRUE; } else { return CellValue.FALSE; } case Cell.CELL_TYPE_NUMERIC: return new CellValue(cell.getNumericCellValue()); case Cell.CELL_TYPE_STRING: return new CellValue(cell.getStringCellValue()); case Cell.CELL_TYPE_ERROR: return CellValue.getError(cell.getErrorCellValue()); default: String msg = String.format("Could not extract value from cell with cached value type %d", valueType); throw new RuntimeException(msg); } }
From source file:com.nc.common.utils.ExcelUtil.java
License:Open Source License
/** * <pre>/* w ww .j a va 2 s .c o m*/ * 1. : POI Util * 2. : Excel ? ?(*.xls, *.xlsx ? ) * </pre> * * @method Name : readExcel * @param strFullFilePath, serviceType * @return List<Map<String, Object>> * @throws Exception * */ @SuppressWarnings("deprecation") public static List<Map<String, Object>> readExcel(String strFullFilePath, String serviceType) throws Exception { String tmpFile = strFullFilePath; File wbfile = new File(tmpFile); Workbook wb = null; FileInputStream file = null; List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); Map<String, Object> map = null; try { file = new FileInputStream(wbfile); wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); Version change */ /* Sheet ? , ? ?? */ /* for (int sheetIdx=0; sheetIdx<wb.getNumberOfSheets(); sheetIdx++) { */ for (int sheetIdx = 0; sheetIdx < 1; sheetIdx++) { /* 1 ? */ Sheet sheet = wb.getSheetAt(sheetIdx); /* ?? ? ?, */ /* row ? */ int cellCount = 0; for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) { Row row = sheet.getRow(rowIdx); cellCount = row.getLastCellNum(); map = new HashMap<String, Object>(); if (row != null) { // cell ? for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) { Cell cell = row.getCell(cellIdx); if (cell != null) { int cellType = cell.getCellType(); String value = ""; // WBS ? ? ?? if (serviceType.equals("order")) { switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: //?? value = cell.getStringCellValue();//cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC://? if (HSSFDateUtil.isCellDateFormatted(cell)) { SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); value = formatter.format(cell.getDateCellValue()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); value = cell.getStringCellValue(); } break; case HSSFCell.CELL_TYPE_STRING: //? value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //BYTE value = cell.getErrorCellValue() + ""; break; default: ; } } else { switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: //?? value = cell.getStringCellValue();//cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC://? value = cell.getNumericCellValue() + ""; break; case HSSFCell.CELL_TYPE_STRING: //? value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //BYTE value = cell.getErrorCellValue() + ""; break; default: } } map.put("colName" + cellIdx, value); } else { map.put("colName" + cellIdx, ""); } } list.add(map); } } } } catch (Exception e) { e.printStackTrace(); if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= Excel File Reading ... Error : [{}] =", e); log.debug( "=========================================================================================="); } throw new NCException("ExcelUtil > readExcel ?"); } finally { /* ? ?? */ file.close(); wb.close(); } return list; }
From source file:com.nc.common.utils.ExcelUtil.java
License:Open Source License
/** * <pre>//from w ww. ja va 2 s.c o m * 1. : POI Util * 2. : Excel ? ?(*.xls, *.xlsx ? ), sheet ? * </pre> * * @method Name : readExcelMulti * @param strFullFilePath, serviceType, sheetNo * @return * @throws * */ @SuppressWarnings("deprecation") public static List<LinkedHashMap<String, Object>> readExcelMulti(String strFullFilePath, String serviceType, int sheetNo) throws Exception { String tmpFile = strFullFilePath; File wbfile = new File(tmpFile); Workbook wb = null; FileInputStream file = null; List<LinkedHashMap<String, Object>> list = new ArrayList<LinkedHashMap<String, Object>>(); LinkedHashMap<String, Object> map = null; try { if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= file path : {} =", strFullFilePath); log.debug("= tmp file : {} =", tmpFile); log.debug( "=========================================================================================="); } file = new FileInputStream(wbfile); wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); */ Sheet sheet = wb.getSheetAt(sheetNo); /* row ? */ int cellCount = 0; for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) { Row row = sheet.getRow(rowIdx); cellCount = row.getLastCellNum(); map = new LinkedHashMap<String, Object>(); if (rowIdx == 0) { if (log.isDebugEnabled()) { log.debug( "=================================================================================="); log.debug("= sheet no : {} =", sheetNo); log.debug("= row count : {} =", sheet.getLastRowNum()); log.debug("= col count : {} =", cellCount); log.debug( "=================================================================================="); } } if (row != null) { // cell ? for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) { Cell cell = row.getCell(cellIdx); if (cell != null) { int cellType = cell.getCellType(); String value = ""; switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: //?? value = cell.getStringCellValue();//cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC://? value = cell.getNumericCellValue() + ""; break; case HSSFCell.CELL_TYPE_STRING: //? value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //BYTE value = cell.getErrorCellValue() + ""; break; default: } map.put("item" + String.valueOf(cellIdx), value); } else { map.put("item" + String.valueOf(cellIdx), ""); } } if (log.isDebugEnabled()) { log.debug( "=================================================================================="); log.debug("= map : {} =", map); log.debug( "=================================================================================="); } list.add(map); } } } catch (Exception e) { e.printStackTrace(); if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= Excel File Reading ... Error : [{}] =", e); log.debug( "=========================================================================================="); } throw new NCException("ExcelUtil > readExcel ?"); } finally { /* ? ?? */ file.close(); wb.close(); } return list; }
From source file:com.ncc.excel.test.ExcelUtil.java
License:Apache License
/*** * ?? /*from w ww. ja v a2s. co m*/ * * @Title: getCellValue * @Date : 2014-9-11 ?10:52:07 * @param cell * @return */ private String getCellValue(Cell cell) { Object result = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: result = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; case Cell.CELL_TYPE_ERROR: result = cell.getErrorCellValue(); break; case Cell.CELL_TYPE_BLANK: break; default: break; } } return result.toString(); }
From source file:com.plugin.excel.util.ExcelUtil.java
License:Apache License
/** * @param oldCell//from ww w . j a va 2s. c o m * @param newCell * @param styleMap */ public static void copyCell(Cell oldCell, Cell newCell, Map<Integer, CellStyle> styleMap) { if (styleMap != null) { if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) { newCell.setCellStyle(oldCell.getCellStyle()); } else { int stHashCode = oldCell.getCellStyle().hashCode(); CellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, newCellStyle); } newCell.setCellStyle(newCellStyle); } } switch (oldCell.getCellType()) { case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_BLANK: newCell.setCellType(Cell.CELL_TYPE_BLANK); 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; default: break; } }
From source file:com.qihang.winter.poi.util.PoiSheetUtility.java
License:Apache License
private static void cloneCell(Cell cNew, Cell cOld) { cNew.setCellComment(cOld.getCellComment()); cNew.setCellStyle(cOld.getCellStyle()); switch (cNew.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: { cNew.setCellValue(cOld.getBooleanCellValue()); break;/*from ww w .ja v a 2s . com*/ } case Cell.CELL_TYPE_NUMERIC: { cNew.setCellValue(cOld.getNumericCellValue()); break; } case Cell.CELL_TYPE_STRING: { cNew.setCellValue(cOld.getStringCellValue()); break; } case Cell.CELL_TYPE_ERROR: { cNew.setCellValue(cOld.getErrorCellValue()); break; } case Cell.CELL_TYPE_FORMULA: { cNew.setCellFormula(cOld.getCellFormula()); break; } } }
From source file:com.qualogy.qafe.service.DocumentServiceImpl.java
License:Apache License
String getCellValue(Cell cell) { String value = null;/* ww w .java 2 s .c om*/ switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: case Cell.CELL_TYPE_STRING: value = cell.toString(); break; // case Cell.CELL_TYPE_NUMERIC: // value = cell.getNumericCellValue() + ""; // break; // case Cell.CELL_TYPE_STRING: // value = cell.getStringCellValue(); // break; case Cell.CELL_TYPE_FORMULA: value = cell.getCellFormula(); break; case Cell.CELL_TYPE_BLANK: ; break; case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue() + ""; break; case Cell.CELL_TYPE_ERROR: value = cell.getErrorCellValue() + ""; break; } return value != null ? value.trim() : value; }