List of usage examples for org.apache.poi.ss.usermodel Cell getRichStringCellValue
RichTextString getRichStringCellValue();
For numeric cells we throw an exception.
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 w ww . j a v a2 s. c o m*/ // 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.openstreetmap.josm.plugins.opendata.core.io.tabular.XlsReader.java
License:GNU General Public License
@Override protected String[] readLine(ProgressMonitor progressMonitor) throws IOException { if (sheet != null) { Row row = sheet.getRow(rowIndex++); if (row != null) { List<String> result = new ArrayList<>(); for (Cell cell : row) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: result.add(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { result.add(cell.getDateCellValue().toString()); } else { result.add(Double.toString(cell.getNumericCellValue())); }/* ww w . j a v a2 s. co m*/ break; case Cell.CELL_TYPE_BOOLEAN: result.add(Boolean.toString(cell.getBooleanCellValue())); break; case Cell.CELL_TYPE_FORMULA: result.add(cell.getCellFormula()); break; default: result.add(""); } } return result.toArray(new String[0]); } } return null; }
From source file:org.pentaho.reporting.ui.datasources.table.ImportFromFileTask.java
License:Open Source License
private void importFromFile(final File file, final boolean firstRowIsHeader) { final ByteArrayOutputStream bout = new ByteArrayOutputStream(Math.max(8192, (int) file.length())); try {//from w ww. j a v a2s. c o m final InputStream fin = new FileInputStream(file); try { IOUtils.getInstance().copyStreams(new BufferedInputStream(fin), bout); } finally { fin.close(); } if (Thread.currentThread().isInterrupted()) { return; } final Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bout.toByteArray())); int sheetIndex = 0; if (workbook.getNumberOfSheets() > 1) { final SheetSelectorDialog selectorDialog = new SheetSelectorDialog(workbook, parent); if (selectorDialog.performSelection()) { sheetIndex = selectorDialog.getSelectedIndex(); } else { return; } } final TypedTableModel tableModel = new TypedTableModel(); final Sheet sheet = workbook.getSheetAt(sheetIndex); final Iterator rowIterator = sheet.rowIterator(); if (firstRowIsHeader) { if (rowIterator.hasNext()) { final Row headerRow = (Row) rowIterator.next(); final short cellCount = headerRow.getLastCellNum(); for (short colIdx = 0; colIdx < cellCount; colIdx++) { final Cell cell = headerRow.getCell(colIdx); if (cell != null) { while (colIdx > tableModel.getColumnCount()) { tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column", String.valueOf(tableModel.getColumnCount())), Object.class); } final RichTextString string = cell.getRichStringCellValue(); if (string != null) { tableModel.addColumn(string.getString(), Object.class); } else { tableModel.addColumn( Messages.getString("TableDataSourceEditor.Column", String.valueOf(colIdx)), Object.class); } } } } } Object[] rowData = null; while (rowIterator.hasNext()) { final Row row = (Row) rowIterator.next(); final short cellCount = row.getLastCellNum(); if (cellCount == -1) { continue; } if (rowData == null || rowData.length != cellCount) { rowData = new Object[cellCount]; } for (short colIdx = 0; colIdx < cellCount; colIdx++) { final Cell cell = row.getCell(colIdx); final Object value; if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { final RichTextString string = cell.getRichStringCellValue(); if (string != null) { value = string.getString(); } else { value = null; } } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { final CellStyle hssfCellStyle = cell.getCellStyle(); final short dataFormat = hssfCellStyle.getDataFormat(); final String dataFormatString = hssfCellStyle.getDataFormatString(); if (isDateFormat(dataFormat, dataFormatString)) { value = cell.getDateCellValue(); } else { value = cell.getNumericCellValue(); } } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { value = cell.getBooleanCellValue(); } else { value = cell.getStringCellValue(); } } else { value = null; } if (value != null && "".equals(value) == false) { while (colIdx >= tableModel.getColumnCount()) { tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column", String.valueOf(tableModel.getColumnCount())), Object.class); } } rowData[colIdx] = value; } if (Thread.currentThread().isInterrupted()) { return; } tableModel.addRow(rowData); } final int colCount = tableModel.getColumnCount(); final int rowCount = tableModel.getRowCount(); for (int col = 0; col < colCount; col++) { Class type = null; for (int row = 0; row < rowCount; row += 1) { final Object value = tableModel.getValueAt(row, col); if (value == null) { continue; } if (type == null) { type = value.getClass(); } else if (type != Object.class) { if (type.isInstance(value) == false) { type = Object.class; } } } if (Thread.currentThread().isInterrupted()) { return; } if (type != null) { tableModel.setColumnType(col, type); } } parent.importComplete(tableModel); } catch (Exception e) { parent.importFailed(e); logger.error("Failed to import spreadsheet", e); // NON-NLS } }
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(); }//from w ww . j a v a 2s . com 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.riflemansd.businessprofit.excel.MyExcelDocument.java
License:Open Source License
public String getString(int nsheet, int nrow, int ncolumn) { String value = ""; org.apache.poi.ss.usermodel.Cell cell = getCell(nsheet, nrow, ncolumn); value = cell.getRichStringCellValue().getString(); return value; }
From source file:org.seasar.fisshplate.core.parser.handler.CellParserHandler.java
License:Apache License
private String getCellValue(CellWrapper cell) { Cell hssfCell = cell.getHSSFCell(); String value = null;//from ww w . java 2 s .c om if (hssfCell.getCellType() == Cell.CELL_TYPE_STRING) { value = hssfCell.getRichStringCellValue().getString(); } else if (hssfCell.getCellType() == Cell.CELL_TYPE_FORMULA) { value = hssfCell.getCellFormula(); } return value; }
From source file:org.seasar.fisshplate.util.FPPoiUtil.java
License:Apache License
/** * ?????????// w ww. j a v a 2 s . c o m * @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.seasar.fisshplate.util.FPPoiUtil.java
License:Apache License
/** *????????/*from www. j av a2s . c o m*/ *????????null??? * @param hssfCell * @return ? */ public static String getStringValue(Cell hssfCell) { if (!isStringCell(hssfCell)) { return null; } RichTextString richVal = hssfCell.getRichStringCellValue(); if (richVal == null) { return null; } return richVal.getString(); }
From source file:org.shareok.data.documentProcessor.ExcelHandler.java
/** * Reads out the data in an excel file and stores data in a hashmap<p> * The cell data has the ending of "--type" to label the data type * /* w w w. j av a2s . c o m*/ * @throws Exception */ @Override public void readData() { String name = fileName; Sheet sheet = null; try { if (null == name || "".equals(name)) { throw new FileNameException("File name is not specified!"); } FileInputStream file = new FileInputStream(new File(name)); String extension = DocumentProcessorUtil.getFileExtension(name); String[] excelTypes = router.loadOfficeFileType("excel"); if (null == excelTypes || excelTypes.length == 0) { throw new FileTypeException("The file types are empty!"); } HashMap<String, String> typeMap = new HashMap<>(); for (String s : excelTypes) { typeMap.put(s, s); } if (typeMap.containsKey(extension)) { if (extension.equals("xlsx")) { } } sheet = getWorkbookSheet(extension, file); int maxNumOfCells = sheet.getRow(0).getLastCellNum(); Iterator<Row> rowIterator = sheet.iterator(); DateFormat df = new SimpleDateFormat("MM/dd/yyyy"); int rowCount = 0; //int colCount = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); //while(cellIterator.hasNext()) { for (int colCount = 0; colCount < maxNumOfCells; colCount++) { //Cell cell = cellIterator.next(); Cell cell = row.getCell(colCount); if (null == cell) { cell = row.createCell(colCount); } String key = Integer.toString(rowCount) + "-" + Integer.toString(colCount); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: data.put(key, Boolean.toString(cell.getBooleanCellValue()) + "---bool"); break; case Cell.CELL_TYPE_NUMERIC: if (isCellDateFormatted(cell)) { data.put(key, df.format(cell.getDateCellValue()) + "---dat"); } else { data.put(key, Double.toString(cell.getNumericCellValue()) + "---num"); } break; case Cell.CELL_TYPE_STRING: data.put(key, cell.getStringCellValue() + "---str"); break; case Cell.CELL_TYPE_BLANK: data.put(key, ""); break; case Cell.CELL_TYPE_ERROR: data.put(key, "ERROR_VALUE"); break; case Cell.CELL_TYPE_FORMULA: FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper() .createFormulaEvaluator(); //handleCell(cell.getCachedFormulaResultType(), cell, evaluator); data.put(key, String.valueOf(cell.getCachedFormulaResultType())); break; default: data.put(key, cell.getRichStringCellValue() + "---def"); break; } // colCount++; } rowCount++; //colCount = 0; } file.close(); } catch (FileNotFoundException ex) { Logger.getLogger(ExcelHandler.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ExcelHandler.class.getName()).log(Level.SEVERE, null, ex); } catch (Exception ex) { Logger.getLogger(ExcelHandler.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:org.shareok.data.msofficedata.ExcelHandler.java
/** * * @throws Exception/*from ww w. java 2 s. c o m*/ */ @Override public void readData() { String name = fileName; Sheet sheet = null; try { if (null == name || "".equals(name)) { throw new Exception("File name is not specified!"); } FileInputStream file = new FileInputStream(new File(name)); String extension = FileUtil.getFileExtension(name); String[] excelTypes = router.loadOfficeFileType("excel"); if (null == excelTypes || excelTypes.length == 0) { throw new Exception("The file types are empty!"); } HashMap<String, String> typeMap = new HashMap<>(); for (String s : excelTypes) { typeMap.put(s, s); } if (typeMap.containsKey(extension)) { if (extension.equals("xlsx")) { } } sheet = getWorkbookSheet(extension, file); Iterator<Row> rowIterator = sheet.iterator(); DateFormat df = new SimpleDateFormat("MM/dd/yyyy"); int rowCount = 0; int colCount = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String key = Integer.toString(rowCount) + "-" + Integer.toString(colCount); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: data.put(key, Boolean.toString(cell.getBooleanCellValue()) + "---bool"); break; case Cell.CELL_TYPE_NUMERIC: if (isCellDateFormatted(cell)) { data.put(key, df.format(cell.getDateCellValue()) + "---dat"); } else { data.put(key, Double.toString(cell.getNumericCellValue()) + "---num"); } break; case Cell.CELL_TYPE_STRING: data.put(key, cell.getStringCellValue() + "---str"); break; default: data.put(key, cell.getRichStringCellValue() + "---def"); break; } colCount++; } rowCount++; colCount = 0; } file.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException ex) { Logger.getLogger(ExcelHandler.class.getName()).log(Level.SEVERE, null, ex); } catch (Exception ex) { Logger.getLogger(ExcelHandler.class.getName()).log(Level.SEVERE, null, ex); } }