List of usage examples for org.apache.poi.ss.usermodel Cell getErrorCellValue
byte getErrorCellValue();
From source file:org.openmrs.module.spreadsheetimport.SpreadsheetImportUtil.java
License:Open Source License
public static File importTemplate(SpreadsheetImportTemplate template, MultipartFile file, String sheetName, List<String> messages, boolean rollbackTransaction) throws Exception { if (file.isEmpty()) { messages.add("file must not be empty"); return null; }/*from www. j av a 2 s. com*/ // Open file Workbook wb = WorkbookFactory.create(file.getInputStream()); Sheet sheet; if (!StringUtils.hasText(sheetName)) { sheet = wb.getSheetAt(0); } else { sheet = wb.getSheet(sheetName); } // Header row Row firstRow = sheet.getRow(0); if (firstRow == null) { messages.add("Spreadsheet header row must not be null"); return null; } List<String> columnNames = new Vector<String>(); for (Cell cell : firstRow) { columnNames.add(cell.getStringCellValue()); } if (log.isDebugEnabled()) { log.debug("Column names: " + columnNames.toString()); } // Required column names List<String> columnNamesOnlyInTemplate = new Vector<String>(); columnNamesOnlyInTemplate.addAll(template.getColumnNamesAsList()); columnNamesOnlyInTemplate.removeAll(columnNames); if (columnNamesOnlyInTemplate.isEmpty() == false) { messages.add("required column names not present: " + toString(columnNamesOnlyInTemplate)); return null; } // Extra column names? List<String> columnNamesOnlyInSheet = new Vector<String>(); columnNamesOnlyInSheet.addAll(columnNames); columnNamesOnlyInSheet.removeAll(template.getColumnNamesAsList()); if (columnNamesOnlyInSheet.isEmpty() == false) { messages.add( "Extra column names present, these will not be processed: " + toString(columnNamesOnlyInSheet)); } // Process rows boolean skipThisRow = true; for (Row row : sheet) { if (skipThisRow == true) { skipThisRow = false; } else { boolean rowHasData = false; Map<UniqueImport, Set<SpreadsheetImportTemplateColumn>> rowData = template .getMapOfUniqueImportToColumnSetSortedByImportIdx(); for (UniqueImport uniqueImport : rowData.keySet()) { Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport); for (SpreadsheetImportTemplateColumn column : columnSet) { int idx = columnNames.indexOf(column.getName()); Cell cell = row.getCell(idx); Object value = null; // check for empty cell (new Encounter) if (cell == null) { rowHasData = true; column.setValue(""); continue; } switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = new Boolean(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: value = new Byte(cell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { java.util.Date date = cell.getDateCellValue(); value = "'" + new java.sql.Timestamp(date.getTime()).toString() + "'"; } else { value = cell.getNumericCellValue(); } break; case Cell.CELL_TYPE_STRING: // Escape for SQL value = "'" + cell.getRichStringCellValue() + "'"; break; } if (value != null) { rowHasData = true; column.setValue(value); } else column.setValue(""); } } for (UniqueImport uniqueImport : rowData.keySet()) { Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport); boolean isFirst = true; for (SpreadsheetImportTemplateColumn column : columnSet) { if (isFirst) { // Should be same for all columns in unique import // System.out.println("SpreadsheetImportUtil.importTemplate: column.getColumnPrespecifiedValues(): " + column.getColumnPrespecifiedValues().size()); if (column.getColumnPrespecifiedValues().size() > 0) { Set<SpreadsheetImportTemplateColumnPrespecifiedValue> columnPrespecifiedValueSet = column .getColumnPrespecifiedValues(); for (SpreadsheetImportTemplateColumnPrespecifiedValue columnPrespecifiedValue : columnPrespecifiedValueSet) { // System.out.println(columnPrespecifiedValue.getPrespecifiedValue().getValue()); } } } } } if (rowHasData) { Exception exception = null; try { DatabaseBackend.validateData(rowData); String encounterId = DatabaseBackend.importData(rowData, rollbackTransaction); if (encounterId != null) { for (UniqueImport uniqueImport : rowData.keySet()) { Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport); for (SpreadsheetImportTemplateColumn column : columnSet) { if ("encounter".equals(column.getTableName())) { int idx = columnNames.indexOf(column.getName()); Cell cell = row.getCell(idx); if (cell == null) cell = row.createCell(idx); cell.setCellValue(encounterId); } } } } } catch (SpreadsheetImportTemplateValidationException e) { messages.add("Validation failed: " + e.getMessage()); return null; } catch (SpreadsheetImportDuplicateValueException e) { messages.add("found duplicate value for column " + e.getColumn().getName() + " with value " + e.getColumn().getValue()); return null; } catch (SpreadsheetImportSQLSyntaxException e) { messages.add("SQL syntax error: \"" + e.getSqlErrorMessage() + "\".<br/>Attempted SQL Statement: \"" + e.getSqlStatement() + "\""); return null; } catch (Exception e) { exception = e; } if (exception != null) { throw exception; } } } } // write back Excel file to a temp location File returnFile = File.createTempFile("sim", ".xls"); FileOutputStream fos = new FileOutputStream(returnFile); wb.write(fos); fos.close(); return returnFile; }
From source file:org.opentestsystem.delivery.testreg.upload.ExcelUtils.java
License:Open Source License
private Object[] getCellValues(final Row row, final MissingCellPolicy policy) { final List<Object> excelColumnList = new ArrayList<Object>(); final DataFormatter dataFormat = new DataFormatter(); final int lastCellNo = row.getLastCellNum(); for (int cellNum = 0; cellNum < lastCellNo; cellNum++) { final Cell cell = row.getCell(cellNum, policy); if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: excelColumnList.add(cell.getStringCellValue()); break; case XSSFCell.CELL_TYPE_BOOLEAN: cell.setCellType(XSSFCell.CELL_TYPE_STRING); excelColumnList.add(cell.getStringCellValue()); break; case XSSFCell.CELL_TYPE_NUMERIC: excelColumnList.add(dataFormat.formatCellValue(cell)); break; case XSSFCell.CELL_TYPE_BLANK: excelColumnList.add(""); break; case XSSFCell.CELL_TYPE_FORMULA: excelColumnList.add(cell.getCellFormula()); break; case XSSFCell.CELL_TYPE_ERROR: excelColumnList.add(cell.getErrorCellValue()); break; default: excelColumnList.add(cell.toString()); }/*w w w . j av a 2s. c o m*/ } } return excelColumnList.toArray(new Object[excelColumnList.size()]); }
From source file:org.paxml.table.excel.ExcelFile.java
License:Open Source License
public Object getCellValue(Cell cell) { CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return cellValue.getBooleanValue(); case Cell.CELL_TYPE_NUMERIC: return cellValue.getNumberValue(); case Cell.CELL_TYPE_STRING: return cellValue.getStringValue(); case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_ERROR: return cellValue.getError(cell.getErrorCellValue()).getStringValue(); // CELL_TYPE_FORMULA will never happen case Cell.CELL_TYPE_FORMULA: throw new PaxmlRuntimeException("Internal error: invalid case"); default:/*from ww w. j a va 2 s .c o m*/ return null; } }
From source file:org.projectforge.excel.ExportSheet.java
License:Open Source License
private static Row copyRow(Sheet worksheet, int rowNum) { Row sourceRow = worksheet.getRow(rowNum); //Save the text of any formula before they are altered by row shifting String[] formulasArray = new String[sourceRow.getLastCellNum()]; for (int i = 0; i < sourceRow.getLastCellNum(); i++) { if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA) formulasArray[i] = sourceRow.getCell(i).getCellFormula(); }//www.ja v a 2 s .c o m worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1); Row newRow = sourceRow; //Now sourceRow is the empty line, so let's rename it sourceRow = worksheet.getRow(rowNum + 1); //Now the source row is at rowNum+1 // 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; // If the old cell is null jump to next cell if (oldCell == null) { continue; } else { newCell = newRow.createCell(i); } // Copy style from old cell and apply to new cell CellStyle newCellStyle = worksheet.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: 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(formulasArray[i]); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; default: break; } } // If there are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } return newRow; }
From source file:org.ramadda.data.docs.TabularOutputHandler.java
License:Apache License
/** * _more_// w w w.j a v a2s. co m * * @param request _more_ * @param entry _more_ * @param suffix _more_ * @param inputStream _more_ * @param visitInfo _more_ * @param visitor _more_ * * @throws Exception _more_ */ private void visitXls(Request request, Entry entry, String suffix, InputStream inputStream, TextReader visitInfo, TabularVisitor visitor) throws Exception { // System.err.println("visitXls: making workbook"); Workbook wb = makeWorkbook(suffix, inputStream); // System.err.println("visitXls:" + visitInfo.getSkip() + " max rows:" + visitInfo.getMaxRows()+ " #sheets:" + wb.getNumberOfSheets()); int maxRows = visitInfo.getMaxRows(); for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) { if (!visitInfo.okToShowSheet(sheetIdx)) { continue; } Sheet sheet = wb.getSheetAt(sheetIdx); // System.err.println("\tsheet:" + sheet.getSheetName() + " #rows:" + sheet.getLastRowNum()); List<List<Object>> rows = new ArrayList<List<Object>>(); int sheetSkip = visitInfo.getSkip(); for (int rowIdx = sheet.getFirstRowNum(); (rows.size() < maxRows) && (rowIdx <= sheet.getLastRowNum()); rowIdx++) { if (sheetSkip-- > 0) { continue; } Row row = sheet.getRow(rowIdx); if (row == null) { continue; } List<Object> cols = new ArrayList<Object>(); short firstCol = row.getFirstCellNum(); for (short col = firstCol; (col < MAX_COLS) && (col < row.getLastCellNum()); col++) { Cell cell = row.getCell(col); if (cell == null) { break; } Object value = null; int type = cell.getCellType(); if (type == cell.CELL_TYPE_NUMERIC) { value = new Double(cell.getNumericCellValue()); } else if (type == cell.CELL_TYPE_BOOLEAN) { value = new Boolean(cell.getBooleanCellValue()); } else if (type == cell.CELL_TYPE_ERROR) { value = "" + cell.getErrorCellValue(); } else if (type == cell.CELL_TYPE_BLANK) { value = ""; } else if (type == cell.CELL_TYPE_FORMULA) { value = cell.getCellFormula(); } else { value = cell.getStringCellValue(); } cols.add(value); } /** * ** TODO * org.ramadda.util.text.Row row = new Row(cols); * * if ( !visitInfo.rowOk(row)) { * if (rows.size() == 0) { * //todo: check for the header line * } else { * continue; * } * } */ rows.add(cols); } if (!visitor.visit(visitInfo, sheet.getSheetName(), rows)) { break; } } }
From source file:org.ramadda.data.docs.TabularOutputHandler.java
License:Apache License
/** * _more_/*from w ww . j ava2 s .c o m*/ * * @param args _more_ * * @throws Exception _more_ */ public static void main(String[] args) throws Exception { Workbook wb = makeWorkbook(IOUtil.getFileExtension(args[0]), new FileInputStream(args[0])); for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) { Sheet sheet = wb.getSheetAt(sheetIdx); System.err.println(sheet.getSheetName()); for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) { Row row = sheet.getRow(rowIdx); if (row == null) { continue; } short firstCol = row.getFirstCellNum(); int colCnt = 0; for (short col = firstCol; col < row.getLastCellNum(); col++) { Cell cell = row.getCell(col); if (cell == null) { break; } Object value = null; int type = cell.getCellType(); if (type == cell.CELL_TYPE_NUMERIC) { value = new Double(cell.getNumericCellValue()); } else if (type == cell.CELL_TYPE_BOOLEAN) { value = new Boolean(cell.getBooleanCellValue()); } else if (type == cell.CELL_TYPE_ERROR) { value = "" + cell.getErrorCellValue(); } else if (type == cell.CELL_TYPE_BLANK) { value = ""; } else if (type == cell.CELL_TYPE_FORMULA) { value = cell.getCellFormula(); } else { value = cell.getStringCellValue(); } if (colCnt++ > 0) System.out.print(","); System.out.print(value); } System.out.println(""); } } }
From source file:org.ramadda.plugins.media.TabularOutputHandler.java
License:Open Source License
/** * _more_//from w ww. j a v a 2 s . c om * * @param request _more_ * @param entry _more_ * @param suffix _more_ * @param inputStream _more_ * @param visitInfo _more_ * @param visitor _more_ * * @throws Exception _more_ */ private void visitXls(Request request, Entry entry, String suffix, InputStream inputStream, Visitor visitInfo, TabularVisitor visitor) throws Exception { // System.err.println("visitXls: making workbook"); Workbook wb = makeWorkbook(suffix, inputStream); // System.err.println("visitXls:" + visitInfo.getSkip() + " max rows:" + visitInfo.getMaxRows()+ " #sheets:" + wb.getNumberOfSheets()); int maxRows = visitInfo.getMaxRows(); for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) { if (!visitInfo.okToShowSheet(sheetIdx)) { continue; } Sheet sheet = wb.getSheetAt(sheetIdx); // System.err.println("\tsheet:" + sheet.getSheetName() + " #rows:" + sheet.getLastRowNum()); List<List<Object>> rows = new ArrayList<List<Object>>(); int sheetSkip = visitInfo.getSkip(); for (int rowIdx = sheet.getFirstRowNum(); (rows.size() < maxRows) && (rowIdx <= sheet.getLastRowNum()); rowIdx++) { if (sheetSkip-- > 0) { continue; } Row row = sheet.getRow(rowIdx); if (row == null) { continue; } List<Object> cols = new ArrayList<Object>(); short firstCol = row.getFirstCellNum(); for (short col = firstCol; (col < MAX_COLS) && (col < row.getLastCellNum()); col++) { Cell cell = row.getCell(col); if (cell == null) { break; } Object value = null; int type = cell.getCellType(); if (type == cell.CELL_TYPE_NUMERIC) { value = new Double(cell.getNumericCellValue()); } else if (type == cell.CELL_TYPE_BOOLEAN) { value = new Boolean(cell.getBooleanCellValue()); } else if (type == cell.CELL_TYPE_ERROR) { value = "" + cell.getErrorCellValue(); } else if (type == cell.CELL_TYPE_BLANK) { value = ""; } else if (type == cell.CELL_TYPE_FORMULA) { value = cell.getCellFormula(); } else { value = cell.getStringCellValue(); } cols.add(value); } /** * ** TODO * org.ramadda.util.text.Row row = new Row(cols); * * if ( !visitInfo.rowOk(row)) { * if (rows.size() == 0) { * //todo: check for the header line * } else { * continue; * } * } */ rows.add(cols); } if (!visitor.visit(visitInfo, sheet.getSheetName(), rows)) { break; } } }
From source file:org.seasar.fisshplate.util.FPPoiUtil.java
License:Apache License
/** * ?????????/*w w w . j a v a 2s .c om*/ * @param hssfCell * @return ? */ public static Object getCellValueAsObject(Cell hssfCell) { if (hssfCell == null) { return null; } int cellType = hssfCell.getCellType(); Object ret = null; switch (cellType) { case HSSFCell.CELL_TYPE_NUMERIC: ret = getValueFromNumericCell(hssfCell); break; case HSSFCell.CELL_TYPE_STRING: ret = hssfCell.getRichStringCellValue().getString(); break; case HSSFCell.CELL_TYPE_BOOLEAN: ret = Boolean.valueOf(hssfCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: ret = hssfCell.getCellFormula(); break; case HSSFCell.CELL_TYPE_ERROR: ret = new Byte(hssfCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: break; default: return null; } return ret; }
From source file:org.specrunner.source.excel.SourceFactoryExcel.java
License:Open Source License
/** * Extract the value of a given cell./*from w ww . j a v a 2s . c o m*/ * * @param cell * The cell. * @return The corresponding object. */ protected Object extractVal(Cell cell) { if (cell == null) { return ""; } Object val = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: val = null; break; case Cell.CELL_TYPE_BOOLEAN: val = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: val = cell.getErrorCellValue(); break; case Cell.CELL_TYPE_FORMULA: val = cell.getCellFormula(); break; case Cell.CELL_TYPE_NUMERIC: double d = cell.getNumericCellValue(); String tmp = String.valueOf(d); if (tmp.endsWith(".0")) { val = tmp.substring(0, tmp.lastIndexOf('.')); } else { val = d; } break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; default: } return val; }
From source file:org.unhcr.eg.odk.utilities.xlsform.controller.SheetProcessor.java
public static Object getCelValue(Cell cell) { Object cellValue = null;/*from ww w. j a v a 2s . c om*/ switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellValue = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { cellValue = cell.getDateCellValue(); } else { cellValue = cell.getNumericCellValue(); } break; case Cell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BLANK: cellValue = new String(""); break; case Cell.CELL_TYPE_ERROR: cellValue = cell.getErrorCellValue(); break; case Cell.CELL_TYPE_FORMULA: cellValue = cell.getCellFormula(); break; } return cellValue; }