List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getCellFormula
public String getCellFormula()
From source file:gda.hrpd.data.ExcelWorkbook.java
License:Open Source License
/** * gets value from the specified cell and return it as String. * //from ww w .j a v a2 s .com * @param cell * @return value from cell as a String */ public String getCellValue(HSSFCell cell) { // If the cell is null return an empty string if (cell == null) { return ""; } String value = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { value = cell.getRichStringCellValue().toString(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { value = String.valueOf(cell.getNumericCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { value = " "; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { value = String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR) { value = String.valueOf(cell.getErrorCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { value = cell.getCellFormula(); } return value; }
From source file:gov.nih.nci.evs.browser.utils.ExcelUtil.java
License:Open Source License
public static String getHSSFHeader(String file, int sheet) { StringBuffer buf = new StringBuffer(); try {//w ww . ja v a 2 s . c o m FileInputStream fis = new FileInputStream(new File(file)); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(fis); try { fis.close(); } catch (Exception ex) { ex.printStackTrace(); } //Get first sheet from the workbook HSSFSheet hSSFSheet = workbook.getSheetAt(sheet); HSSFRow row = hSSFSheet.getRow(0); int cells = row.getPhysicalNumberOfCells(); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; default: } buf.append(value); if (c < cells - 1) { buf.append("|"); } } } catch (Exception ex) { ex.printStackTrace(); } return buf.toString(); }
From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java
License:Open Source License
private void table(final HSSFSheet sheet, int startIndex, int col, String code, boolean cdisc) { resolvedValueSetList = new ArrayList(); if (sheet == null) { return;/*from w w w. j ava 2 s . c om*/ } if (sheet.getDrawingPatriarch() != null) { final List<HSSFShape> shapes = sheet.getDrawingPatriarch().getChildren(); for (int i = 0; i < shapes.size(); ++i) { if (shapes.get(i) instanceof HSSFPicture) { try { // Gain access to private field anchor. final HSSFShape pic = shapes.get(i); final Field f = HSSFShape.class.getDeclaredField("anchor"); f.setAccessible(true); final HSSFClientAnchor anchor = (HSSFClientAnchor) f.get(pic); // Store picture cell row, column and picture data. if (!pix.containsKey(anchor.getRow1())) { pix.put(anchor.getRow1(), new HashMap<Short, List<HSSFPictureData>>()); } if (!pix.get(anchor.getRow1()).containsKey(anchor.getCol1())) { pix.get(anchor.getRow1()).put(anchor.getCol1(), new ArrayList<HSSFPictureData>()); } pix.get(anchor.getRow1()).get(anchor.getCol1()) .add(book.getAllPictures().get(((HSSFPicture) pic).getPictureIndex())); } catch (final Exception e) { throw new RuntimeException(e); } } } } out.append("<table id=\"" + "rvs_table" + "\" width=\"915\" class=\"mt\">\n"); tr(sheet.getRow(0)); StringBuffer buf = new StringBuffer(); tr(sheet.getRow(0), buf); String t = buf.toString(); resolvedValueSetList.add(t); int rows = sheet.getPhysicalNumberOfRows(); for (int i = startIndex; i <= rows; i++) { HSSFRow row = sheet.getRow(i); if (row != null) { if (col != -1) { HSSFCell cell = row.getCell(col); if (cell != null) { String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; default: } if ((cdisc && i == startIndex) || (value != null && value.compareTo(code) == 0)) { tr(row); buf = new StringBuffer(); tr(row, buf); t = buf.toString(); resolvedValueSetList.add(t); } } } else { tr(row); buf = new StringBuffer(); tr(row, buf); t = buf.toString(); resolvedValueSetList.add(t); } } } out.append("</table>\n"); resolvedValueSetIterator = resolvedValueSetList.listIterator(); }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java
License:BSD License
public static Object getObject(HSSFSheet sheet, int row, short col) { HSSFRow hssfRow = getRow(sheet, row); if (hssfRow == null) { return null; }//from w w w . j a v a 2s .co m HSSFCell cell = getRow(sheet, row).getCell(col); if (cell == null) { return null; } try { String val = cell.getStringCellValue(); if (val != null && val.equalsIgnoreCase("(null)")) { return null; } } catch (Exception t) { } int type = cell.getCellType(); switch (type) { case HSSFCell.CELL_TYPE_BLANK: return ""; case HSSFCell.CELL_TYPE_BOOLEAN: return new Boolean(cell.getBooleanCellValue()); case HSSFCell.CELL_TYPE_ERROR: return new Byte(cell.getErrorCellValue()); case HSSFCell.CELL_TYPE_FORMULA: return cell.getCellFormula(); case HSSFCell.CELL_TYPE_NUMERIC: return new Double(cell.getNumericCellValue()); case HSSFCell.CELL_TYPE_STRING: return cell.getStringCellValue(); default: return null; } }
From source file:Import.Utils.XSSFConvert.java
/** * @param oldCell//from w w w . j a v a 2s .c om * @param newCell * @param styleMap */ public static void copyCell(HSSFCell oldCell, XSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) { if (styleMap != null) { int stHashCode = oldCell.getCellStyle().hashCode(); HSSFCellStyle sourceCellStyle = styleMap.get(stHashCode); XSSFCellStyle destnCellStyle = newCell.getCellStyle(); if (sourceCellStyle == null) { sourceCellStyle = oldCell.getSheet().getWorkbook().createCellStyle(); } destnCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, sourceCellStyle); newCell.setCellStyle(destnCellStyle); } switch (oldCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
From source file:net.openchrom.xxd.processor.supplier.rscripting.ui.jobs.LoadExcelJob.java
License:Open Source License
private void loadExcel(final String file) { final File fil = new File(file); if (fil.exists()) { canRead = true;//w w w .ja v a 2 s .c o m if (grid != null) { try { InputStream inp = new FileInputStream(file); try { wb = new HSSFWorkbook(inp); } catch (Exception e) { MsgDialog.message("Wrong format!\nOnly Excel 97-2007 is supported!"); canRead = false; } } 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); HSSFSheet 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++) { HSSFRow 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++) { HSSFCell cell = row.getCell(u); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: String val = String.valueOf(cell.getNumericCellValue()); grid.getItem(i).setText(u, val); break; case HSSFCell.CELL_TYPE_STRING: HSSFRichTextString st = cell.getRichStringCellValue(); String val2 = st.getString(); grid.getItem(i).setText(u, val2); break; case HSSFCell.CELL_TYPE_FORMULA: try { String val3 = String.valueOf(cell.getNumericCellValue()); grid.getItem(i).setText(u, val3); } catch (Exception e) { String s2 = cell.getCellFormula(); grid.getItem(i).setText(u, s2); } break; case HSSFCell.CELL_TYPE_BLANK: grid.getItem(i).setText(u, " "); break; case HSSFCell.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:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java
License:Apache License
/** * Exportiert die bergebene excel-Datei in eine Liste mit zweidimensionalen Arrays fr jeweils * ein sheet in der excel-Datei.//from w ww. j a v a2s . co m * * @param excelSheet * Die excel-Datei. * @return Gibt eine Liste mit zweidimensionalen Arrays fr jeweils ein sheet in der excel-Datei * zurck. * @throws IOException * Fals ein Fehler beim Lesen aufgetreten ist. * @throws FileNotFoundException * Fals die excel-Datei nicht gefunden wurde. */ public static List<String[][]> exportWorkbook(File excelSheet) throws IOException, FileNotFoundException { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); HSSFWorkbook wb = new HSSFWorkbook(fs); int numberOfSheets = wb.getNumberOfSheets(); List<String[][]> sheetList = new ArrayList<>(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); int rows = sheet.getLastRowNum(); int columns = sheet.getRow(0).getLastCellNum(); String[][] excelSheetInTDArray = null; excelSheetInTDArray = new String[rows][columns]; for (int i = 0; i < rows; i++) { HSSFRow row = sheet.getRow(i); if (null != row) { for (int j = 0; j < columns; j++) { HSSFCell cell = row.getCell(j); if (null == cell) { excelSheetInTDArray[i][j] = ""; } else { int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_BLANK) { excelSheetInTDArray[i][j] = ""; } else if (cellType == Cell.CELL_TYPE_BOOLEAN) { excelSheetInTDArray[i][j] = Boolean.toString(cell.getBooleanCellValue()); } else if (cellType == Cell.CELL_TYPE_ERROR) { excelSheetInTDArray[i][j] = ""; } else if (cellType == Cell.CELL_TYPE_FORMULA) { excelSheetInTDArray[i][j] = cell.getCellFormula(); } else if (cellType == Cell.CELL_TYPE_NUMERIC) { excelSheetInTDArray[i][j] = Double.toString(cell.getNumericCellValue()); } else if (cellType == Cell.CELL_TYPE_STRING) { excelSheetInTDArray[i][j] = cell.getRichStringCellValue().getString(); } } } } } sheetList.add(excelSheetInTDArray); } return sheetList; }
From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java
License:Apache License
/** * Exportiert die bergebene excel-Datei in eine geschachtelte Liste mit Listen von sheets und * Listen von den Zeilen der sheets von der excel-Datei. * * @param excelSheet//from w w w . j av a2s . co m * Die excel-Datei. * @return Gibt eine Liste mit Listen von den sheets in der excel-Datei zurck. Die Listen mit * den sheets beinhalten weitere Listen mit String die jeweils eine Zeile * reprsentieren. * @throws IOException * Fals ein Fehler beim Lesen aufgetreten ist. * @throws FileNotFoundException * Fals die excel-Datei nicht gefunden wurde. */ public static List<List<List<String>>> exportWorkbookAsStringList(File excelSheet) throws IOException, FileNotFoundException { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet)); HSSFWorkbook wb = new HSSFWorkbook(fs); int numberOfSheets = wb.getNumberOfSheets(); List<List<List<String>>> sl = new ArrayList<>(); for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) { HSSFSheet sheet = null; sheet = wb.getSheetAt(sheetNumber); int rows = sheet.getLastRowNum(); int columns = sheet.getRow(0).getLastCellNum(); List<List<String>> excelSheetList = new ArrayList<>(); for (int i = 0; i < rows; i++) { HSSFRow row = sheet.getRow(i); if (null != row) { List<String> reihe = new ArrayList<>(); for (int j = 0; j < columns; j++) { HSSFCell cell = row.getCell(j); if (null == cell) { reihe.add(""); } else { int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_BLANK) { reihe.add(""); } else if (cellType == Cell.CELL_TYPE_BOOLEAN) { reihe.add(Boolean.toString(cell.getBooleanCellValue())); } else if (cellType == Cell.CELL_TYPE_ERROR) { reihe.add(""); } else if (cellType == Cell.CELL_TYPE_FORMULA) { reihe.add(cell.getCellFormula()); } else if (cellType == Cell.CELL_TYPE_NUMERIC) { reihe.add(Double.toString(cell.getNumericCellValue())); } else if (cellType == Cell.CELL_TYPE_STRING) { reihe.add(cell.getRichStringCellValue().getString()); } } } excelSheetList.add(reihe); } } sl.add(excelSheetList); } return sl; }
From source file:org.amanzi.splash.importer.ExcelImporter.java
License:Open Source License
/** * Creates a child spreadsheet inside parent spreadsheet * * @param sheet sheet to import/*from www . j av a2s.c o m*/ * @param sheetName name of sheet * @param monitor monitor */ @SuppressWarnings(value = { "deprecation", "unchecked" }) private void createSheet(HSSFSheet sheet, String sheetName, Transaction transaction) { spreadsheetNode = null; spreadsheetName = sheetName; createSpreadsheet(rootSpreadsheet); try { Iterator<HSSFRow> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = rows.next(); //display row number in the console. LOGGER.debug("Row No.: " + row.getRowNum()); //once get a row its time to iterate through cells. Iterator<HSSFCell> cells = row.cellIterator(); int R = row.getRowNum(); if ((R % 20) == 0) { updateTransaction(transaction); } while (cells.hasNext()) { HSSFCell cell = cells.next(); LOGGER.debug("Cell No.: " + cell.getCellNum()); int C = cell.getCellNum(); /* * Now we will get the cell type and display the values * accordingly. */ switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { // cell type numeric. LOGGER.debug("===================================================="); LOGGER.debug("Numeric value: " + cell.getNumericCellValue()); LOGGER.debug("===================================================="); String def = Double.toString(cell.getNumericCellValue()); Cell c = new Cell(R, C, def, def, new CellFormat()); //TODO: interpet!!!!!! //Cell c = model.interpret(def, R, C); saveCell(c); break; } case HSSFCell.CELL_TYPE_STRING: { // cell type string. HSSFRichTextString richTextString = cell.getRichStringCellValue(); LOGGER.debug("===================================================="); LOGGER.debug("String value: " + richTextString.getString()); LOGGER.debug("===================================================="); Cell c = new Cell(R, C, richTextString.getString(), richTextString.getString(), new CellFormat()); saveCell(c); break; } case HSSFCell.CELL_TYPE_FORMULA: // cell type string. String cellFormula = "=" + cell.getCellFormula().toLowerCase(); Cell c = new Cell(R, C, cellFormula, cellFormula, new CellFormat()); //TODO: interpet!!!!!! //Cell c = model.interpret(def, R, C); saveCell(c); LOGGER.debug("===================================================="); LOGGER.debug("Formula value: " + cellFormula); LOGGER.debug("===================================================="); break; default: { // types other than String and Numeric. LOGGER.debug("Type not supported."); break; } } } } } finally { updateTransaction(transaction); } }
From source file:org.exoplatform.services.document.impl.MSExcelDocumentReader.java
License:Open Source License
/** * Returns only a text from .xls file content. * /*from www.j a va 2 s. c om*/ * @param is an input stream with .xls file content. * @return The string only with text from file content. */ public String getContentAsText(InputStream is) throws IOException, DocumentReadException { if (is == null) { throw new IllegalArgumentException("InputStream is null."); } final StringBuilder builder = new StringBuilder(""); SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT); try { if (is.available() == 0) { return ""; } HSSFWorkbook wb; try { wb = new HSSFWorkbook(is); } catch (IOException e) { throw new DocumentReadException("Can't open spreadsheet.", e); } for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { HSSFSheet sheet = wb.getSheetAt(sheetNum); if (sheet != null) { for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) { HSSFRow row = sheet.getRow(rowNum); if (row != null) { int lastcell = row.getLastCellNum(); for (int k = 0; k < lastcell; k++) { final HSSFCell cell = row.getCell((short) k); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.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 HSSFCell.CELL_TYPE_FORMULA: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getCellFormula().toString()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_BOOLEAN: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getBooleanCellValue()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_ERROR: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getErrorCellValue()).append(" "); return null; } }); break; case HSSFCell.CELL_TYPE_STRING: SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() { public Void run() { builder.append(cell.getStringCellValue().toString()).append(" "); return null; } }); 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(); }