List of usage examples for org.apache.poi.ss.usermodel Cell getBooleanCellValue
boolean getBooleanCellValue();
From source file:gov.nih.nci.cananolab.util.ExcelParser.java
License:BSD License
public void printSheet(Sheet sheet) { for (Row row : sheet) { for (Cell cell : row) { CellReference cellRef = new CellReference(cell.getRowIndex(), cell.getColumnIndex()); System.out.print(cellRef.formatAsString()); System.out.print(" - "); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.println(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); } else { System.out.println(cell.getNumericCellValue()); }/*from w ww . j a va 2s . co m*/ break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); break; default: System.out.println(); } } } }
From source file:gov.nih.nci.evs.app.neopl.XLStoXLSX.java
License:Open Source License
/** * @param args/* ww w .j a v a 2s. c o m*/ * @throws InvalidFormatException * @throws IOException */ public static void run(String inputfile, String outputfile) throws IOException { InputStream in = new BufferedInputStream(new FileInputStream(inputfile)); try { Workbook wbIn = new HSSFWorkbook(in); File outFn = new File(outputfile); if (outFn.exists()) { outFn.delete(); } Workbook wbOut = new XSSFWorkbook(); int sheetCnt = wbIn.getNumberOfSheets(); for (int i = 0; i < sheetCnt; i++) { Sheet sIn = wbIn.getSheetAt(0); Sheet sOut = wbOut.createSheet(sIn.getSheetName()); Iterator<Row> rowIt = sIn.rowIterator(); while (rowIt.hasNext()) { Row rowIn = rowIt.next(); Row rowOut = sOut.createRow(rowIn.getRowNum()); Iterator<Cell> cellIt = rowIn.cellIterator(); while (cellIt.hasNext()) { Cell cellIn = cellIt.next(); Cell cellOut = rowOut.createCell(cellIn.getColumnIndex(), cellIn.getCellType()); switch (cellIn.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: cellOut.setCellValue(cellIn.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: cellOut.setCellValue(cellIn.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: cellOut.setCellFormula(cellIn.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: cellOut.setCellValue(cellIn.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cellOut.setCellValue(cellIn.getStringCellValue()); break; } { CellStyle styleIn = cellIn.getCellStyle(); CellStyle styleOut = cellOut.getCellStyle(); styleOut.setDataFormat(styleIn.getDataFormat()); } cellOut.setCellComment(cellIn.getCellComment()); // HSSFCellStyle cannot be cast to XSSFCellStyle // cellOut.setCellStyle(cellIn.getCellStyle()); } } } OutputStream out = new BufferedOutputStream(new FileOutputStream(outFn)); try { wbOut.write(out); } finally { out.close(); } } finally { in.close(); } }
From source file:gov.va.isaac.isaacDbProcessingRules.spreadsheet.SpreadsheetReader.java
License:Apache License
private static String toString(Cell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue() + ""; case Cell.CELL_TYPE_NUMERIC: return cell.getNumericCellValue() + ""; case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_ERROR: return "_ERROR_ " + cell.getErrorCellValue(); case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula() + ""; default:/*from w ww . j a v a 2 s . com*/ throw new RuntimeException("No toString is available for the cell type!"); } }
From source file:graphbuilder.ExcelParser.java
private static Object loadCellData(Cell cell) { Object result = null;//w w w . j av a 2 s . c o m switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: result = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { result = cell.getDateCellValue(); } else { result = cell.getNumericCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; } return result; }
From source file:graphene.hts.file.ExcelXSSFToJSONConverter.java
License:Apache License
private List internalConvert(final Iterator<Row> rowIter) { final List<Map<String, String>> excelSheetConversion = new ArrayList<Map<String, String>>(); final List<String> headerRow = new ArrayList<String>(); if (rowIter.hasNext()) { final Row row = rowIter.next(); final Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { final Cell cell = cellIterator.next(); String cellString = cell.getStringCellValue(); if (!ValidationUtils.isValid(cellString)) { cellString = "Column " + cell.getColumnIndex(); }/*w w w . j a v a2 s . co m*/ headerRow.add(cellString); System.out.println("Header Column: " + cellString); } } while (rowIter.hasNext()) { final Row row = rowIter.next(); final Map<String, String> kvMap = new TreeMap<String, String>(); final Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { final org.apache.poi.ss.usermodel.Cell cell = cellIterator.next(); // System.out.println("Header Columns: " + headerRow); final int ci = cell.getColumnIndex(); String key = "Column " + ci; if (ci < headerRow.size()) { key = headerRow.get(cell.getColumnIndex()); } switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: if (ValidationUtils.isValid(key, cell.getBooleanCellValue())) { kvMap.put(key, new Boolean(cell.getBooleanCellValue()).toString()); } break; case Cell.CELL_TYPE_STRING: if (ValidationUtils.isValid(key, cell.getStringCellValue())) { kvMap.put(key, cell.getStringCellValue()); } break; case Cell.CELL_TYPE_NUMERIC: if (ValidationUtils.isValid(key, cell.getNumericCellValue())) { kvMap.put(key, new Double(cell.getNumericCellValue()).toString()); } break; case Cell.CELL_TYPE_BLANK: break; default: break; } } excelSheetConversion.add(kvMap); } logger.debug("Added sheet to conversion."); return excelSheetConversion; }
From source file:hjow.hgtable.util.XLSXUtil.java
License:Apache License
/** * <p>XLSX ? ? ?? . ? ? ?? ?? , ? ? ?? ? ? ?? ?.</p> * /*w ww .j a v a 2 s . c om*/ * @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:hrytsenko.gscripts.io.XlsFiles.java
License:Apache License
private static String cellValue(Cell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_NUMERIC: DecimalFormat format = new DecimalFormat("0.#"); return format.format(cell.getNumericCellValue()); case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); default:/*from w ww . j ava 2 s .co m*/ return ""; } }
From source file:info.informationsea.tableio.excel.ExcelSheetReader.java
License:Open Source License
@Override protected Object[] readNextRow() { if (sheet.getLastRowNum() < currentRow) return null; Row row = sheet.getRow(currentRow);/*from w w w . j a va2 s . c om*/ Object[] rowObjects = new Object[row.getLastCellNum()]; for (Cell cell : row) { Object value; switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: value = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: default: value = cell.getStringCellValue(); break; } rowObjects[cell.getColumnIndex()] = value; } currentRow += 1; return rowObjects; }
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 w w w . ja va 2 s. c o m 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:io.konig.spreadsheet.CellValue.java
License:Apache License
public static CellValue getValue(Cell cell) { if (cell == null) { return null; }/*ww w . j av a2 s . c o m*/ switch (cell.getCellTypeEnum()) { case BOOLEAN: Boolean booleanValue = cell.getBooleanCellValue(); return new CellValue(booleanValue, null, booleanValue.toString()); case NUMERIC: Double doubleValue = cell.getNumericCellValue(); return new CellValue(null, doubleValue, doubleValue.toString()); case STRING: String stringValue = cell.getStringCellValue(); return new CellValue(null, null, stringValue); default: return null; } }