List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue
public double getNumericCellValue()
From source file:org.terrier.indexing.MSExcelDocument.java
License:Mozilla Public License
/** Get the reader appropriate for this InputStream. This involves converting the Excel document to a stream of words. On failure returns null and sets EOD to true, so no terms can be read from the object. //from w w w.ja v a 2 s .c o m Uses the property <tt>indexing.excel.maxfilesize.mb</tt> to determine if the file is too big to open @param docStream */ @SuppressWarnings("unchecked") //poi version used is for Java 1.4. protected Reader getReader(InputStream docStream) { if (MAXFILESIZE > 0 && (filename == null || new File(filename).length() > MAXFILESIZE)) { logger.warn("WARNING: Excel document " + filename + " is too large for POI. Ignoring."); EOD = true; return null; } try { CharArrayWriter writer = new CharArrayWriter(); //opening the file system POIFSFileSystem fs = new POIFSFileSystem(docStream); //opening the work book HSSFWorkbook workbook = new HSSFWorkbook(fs); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { //got the i-th sheet from the work book HSSFSheet sheet = workbook.getSheetAt(i); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: String num = Double.toString(cell.getNumericCellValue()).trim(); if (num.length() > 0) { writer.write(num + " "); } break; case HSSFCell.CELL_TYPE_STRING: String text = cell.getStringCellValue().trim(); if (text.length() > 0) { writer.write(text + " "); } break; } } } } return new CharArrayReader(writer.toCharArray()); } catch (Exception e) { logger.warn("WARNING: Problem converting excel document" + e); EOD = true; return null; } }
From source file:org.testeditor.core.importer.ExcelFileImporter.java
License:Open Source License
/** * gets the testData from a numeric cell. * //ww w . j a v a 2 s . c om * @param testDataRow * TestDataRow * @param cell * HSSFCell */ protected void getTestDataNumericCell(TestDataRow testDataRow, HSSFCell cell) { if (HSSFDateUtil.isCellDateFormatted(cell)) { double value = cell.getNumericCellValue(); if (HSSFDateUtil.isValidExcelDate(value)) { Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()); DateFormat format = new SimpleDateFormat(JAVA_TOSTRING); testDataRow.add(format.format(date)); } } else { DataFormatter df = new DataFormatter(); Format cellFormat = df.createFormat(cell); if (cellFormat instanceof DecimalFormat) { String pattern = ((DecimalFormat) cellFormat).toPattern(); DecimalFormat dFormatter = new DecimalFormat(pattern); testDataRow.add(dFormatter.format(cell.getNumericCellValue())); } else { testDataRow.add(String.valueOf(cell.getNumericCellValue())); } } }
From source file:org.wso2.ws.dataservice.DBUtils.java
License:Apache License
private static OMElement getExcelResult(HSSFWorkbook wb, OMElement queryElement, AxisService axisService) throws AxisFault { OMElement resultElement = null;//from ww w.ja v a 2 s .c o m //OMElement excelElement = queryElement.getFirstChildWithName(new QName("excel")); ExcelQuery excelQuery = new ExcelQuery(axisService, queryElement); OMElement result = queryElement.getFirstChildWithName(new QName("result")); String wrapperElementName = result.getAttributeValue(new QName("element")); String rowElementName = result.getAttributeValue(new QName("rowName")); String columnDefalut = result.getAttributeValue(new QName("columnDefault")); OMFactory fac = OMAbstractFactory.getOMFactory(); OMNamespace omNs = fac.createOMNamespace(axisService.getTargetNamespace(), "data"); resultElement = fac.createOMElement(wrapperElementName, omNs); if (!axisService.isElementFormDefault()) { omNs = fac.createOMNamespace("", "data"); } //Retrieve the sheet name, if user has set it in configuration file String sheetName = excelQuery.getWorkBookName(); //this is used to skip header columns, the spread sheet int startReadingFromRow = excelQuery.getStartingRow(); if (startReadingFromRow >= 0) { //rows start from 0 startReadingFromRow = startReadingFromRow - 1; } int maxRowCount = excelQuery.getMaxRowCount(); HSSFSheet sheet = wb.getSheet(sheetName); int rowCount = sheet.getPhysicalNumberOfRows(); //If hasHeaders is set to true, we need first row object in later stage. HSSFRow firstRow = null; if (excelQuery.hasHeaders()) { //assumption : first row is the header row firstRow = sheet.getRow(0); } int processedRowCount = 0; for (int r = 0; r < rowCount; r++) { if (r >= startReadingFromRow) { if (processedRowCount == maxRowCount) { break; } HSSFRow hssfRow = sheet.getRow(r); OMElement row = fac.createOMElement(rowElementName, omNs); if (rowElementName == null) { row = resultElement; } Iterator elements = result.getChildElements(); while (elements.hasNext()) { OMElement element = (OMElement) elements.next(); if (element.getLocalName().equals("element")) { String displayTagName = element.getAttributeValue(new QName("name")); String columnValue = element.getAttributeValue(new QName("column")); short a = 1; short columnNumber; if (excelQuery.hasHeaders()) { //if hasHeaders is set to true, column Names should be specified //get the column number using specified name columnNumber = getExcelColumnNumber(columnValue, firstRow); } else { try { columnNumber = (short) (Short.valueOf(columnValue).shortValue() - a); } catch (NumberFormatException e) { log.error("Column value for element : " + displayTagName + " should be a number.", e); throw new AxisFault( "Column value for element : " + displayTagName + " should be a number."); } } HSSFCell hssfCell = hssfRow.getCell(columnNumber); String elementValue = ""; if (hssfCell != null) { if (HSSFCell.CELL_TYPE_STRING == hssfCell.getCellType()) { elementValue = hssfCell.getRichStringCellValue().getString(); } else if (HSSFCell.CELL_TYPE_BLANK == hssfCell.getCellType()) { //do nothing } else if (HSSFCell.CELL_TYPE_BOOLEAN == hssfCell.getCellType()) { elementValue = String.valueOf(hssfCell.getBooleanCellValue()); } else if (HSSFCell.CELL_TYPE_FORMULA == hssfCell.getCellType()) { elementValue = "{formula}"; } else if (HSSFCell.CELL_TYPE_NUMERIC == hssfCell.getCellType()) { elementValue = String.valueOf(hssfCell.getNumericCellValue()); } } if (columnDefalut == null || columnDefalut.equals("element")) { OMElement rowElement = fac.createOMElement(displayTagName, omNs); rowElement.addChild(fac.createOMText(rowElement, elementValue)); row.addChild(rowElement); } else if (columnDefalut.equals("attribute")) { row.addAttribute(displayTagName, elementValue, omNs); } } } if (rowElementName != null) { resultElement.addChild(row); processedRowCount++; } } //end of if( k >= startReadingFromRow) } //for (int r = 0; r < rowCount; r++) return resultElement; }
From source file:org.wso2.ws.dataservice.DBUtils.java
License:Apache License
private static short getExcelColumnNumber(String columnName, HSSFRow headerRow) throws AxisFault { int noOfCells = headerRow.getPhysicalNumberOfCells(); short columnNo = -1; for (int a = 0; a < noOfCells; a++) { HSSFCell cell = headerRow.getCell((short) a); if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) { if (columnName.equals(cell.getRichStringCellValue().getString())) { columnNo = (short) a; break; }//from w w w. ja v a 2 s .co m } else if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { try { double columnNameInDouble = Double.valueOf(columnName).doubleValue(); if (columnNameInDouble == cell.getNumericCellValue()) { columnNo = (short) a; break; } } catch (NumberFormatException e) { log.error("Numeric value expected for Column Name : " + columnName, e); throw new AxisFault("Numeric value expected for Column Name : " + columnName, e); } } } return columnNo; }
From source file:org.xianairlines.action.staffs.StaffsImportAction.java
private String readCell(HSSFCell cell) { if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { return cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { return String.valueOf(cell.getNumericCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { }//from w ww . ja v a2s.c om return null; }
From source file:org.yccheok.jstock.file.Statements.java
License:Open Source License
/** * Construct Statements based on given Excel File. * * @param file Given Excel File/*from w w w . ja v a 2 s . c o m*/ * @return the List of constructed Statements. Empty list if fail. */ public static List<Statements> newInstanceFromExcelFile(File file) { FileInputStream fileInputStream = null; final List<Statements> statementsList = new ArrayList<Statements>(); try { fileInputStream = new FileInputStream(file); final POIFSFileSystem fs = new POIFSFileSystem(fileInputStream); final HSSFWorkbook wb = new HSSFWorkbook(fs); final int numberOfSheets = wb.getNumberOfSheets(); for (int k = 0; k < numberOfSheets; k++) { final HSSFSheet sheet = wb.getSheetAt(k); final int startRow = sheet.getFirstRowNum(); final int endRow = sheet.getLastRowNum(); // If there are 3 rows, endRow will be 2. // We must have at least 2 rows. (endRow = 1) if (startRow != 0 || endRow <= startRow) { continue; } final HSSFRow row = sheet.getRow(startRow); if (row == null) { continue; } final int startCell = row.getFirstCellNum(); final int endCell = row.getLastCellNum(); // If there are 2 cols, endCell will be 2. // We must have at least 1 col. (endCell = 1) if (startCell != 0 || endCell <= startCell) { continue; } final List<String> types = new ArrayList<String>(); for (int i = startCell; i < endCell; i++) { final HSSFCell cell = row.getCell(i); if (cell == null) { continue; } // Exception may be thrown here, as cell may be numerical value. final String type = cell.getRichStringCellValue().getString(); if (type != null) { types.add(type); } } if (types.isEmpty()) { continue; } if (types.size() != (endCell - startCell)) { continue; } final Statement.What what = Statement.what(types); Statements s = new Statements(what.type, what.guiBundleWrapper); for (int i = startRow + 1; i <= endRow; i++) { final HSSFRow r = sheet.getRow(i); if (r == null) { continue; } final List<Atom> atoms = new ArrayList<Atom>(); for (int j = startCell; j < endCell; j++) { final HSSFCell cell = r.getCell(j); if (cell == null) { continue; } Object value = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { final HSSFRichTextString richString = cell.getRichStringCellValue(); if (richString != null) { value = richString.getString(); } else { value = ""; } } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { try { value = new Double(cell.getNumericCellValue()); } catch (NumberFormatException ex) { log.error(null, ex); value = new Double(0.0); } } else { } if (null == value) { continue; } atoms.add(new Atom(value, types.get(j - startCell))); } final Statement statement = new Statement(atoms); if (s.getType() != statement.getType()) { // Give up. s = null; break; } s.statements.add(statement); } // for (int i = startRow + 1; i <= endRow; i++) if (s != null) { statementsList.add(s); } } /* for(int k = 0; k < numberOfSheets; k++) */ } catch (Exception ex) { log.error(null, ex); } finally { org.yccheok.jstock.gui.Utils.close(fileInputStream); } return statementsList; }
From source file:org.zilverline.extractors.ExcelExtractor.java
License:Open Source License
/** * Extract the content from the given Excel file. As a side effect the type is set too. * /*w w w . jav a2 s. com*/ * @see org.zilverline.extractors.AbstractExtractor#getContent(java.io.File) */ public final Reader getContent(final File f) { Reader reader = null; setType("EXCEL"); try { CharArrayWriter writer = new CharArrayWriter(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(f)); HSSFWorkbook workbook = new HSSFWorkbook(fs); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: String num = Double.toString(cell.getNumericCellValue()).trim(); if (num.length() > 0) { writer.write(num + " "); } break; case HSSFCell.CELL_TYPE_STRING: String text = cell.getStringCellValue().trim(); if (text.length() > 0) { writer.write(text + " "); } break; default: // skip } } } } setSummary(getSummaryFromContent(writer.toString())); return new CharArrayReader(writer.toCharArray()); } catch (Exception e) { log.warn("Can't extract contents for: " + f.getName(), e); } return reader; }
From source file:org.zilverline.extractors.ExcelExtractor.java
License:Open Source License
/** * Extract the content from the given Excel file. As a side effect the type is set too. * //from w w w . j a v a2s . c o m * @see org.zilverline.extractors.AbstractExtractor#getContent(java.io.File) */ public final String getContent(final InputStream is) { try { CharArrayWriter writer = new CharArrayWriter(); POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook workbook = new HSSFWorkbook(fs); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: String num = Double.toString(cell.getNumericCellValue()).trim(); if (num.length() > 0) { writer.write(num + " "); } break; case HSSFCell.CELL_TYPE_STRING: String text = cell.getStringCellValue().trim(); if (text.length() > 0) { writer.write(text + " "); } break; default: // skip } } } } return new String(writer.toCharArray()); } catch (Exception e) { log.warn("Can't extract contents", e); } return ""; }
From source file:payroll.tasks.TaskFactory.java
private static String getStringCellValue(HSSFCell inCell) { HSSFCell cell = inCell; String value;//from w w w . j a va 2 s. c o m // Check to see if cell is numeric (Receiver) or not (Could be anything) int cellType = cell.getCellType(); // If cell is numeric if (cellType == 0) { // Get numeric cell's value as a Double Double cellValue = cell.getNumericCellValue(); // Convert double value to string to be able to instantiate task value = Double.toString(cellValue); } // Else get cell's value as String else { value = cell.getStringCellValue(); } return value; }
From source file:poi.hssf.usermodel.examples.HSSFReadWrite.java
License:Apache License
/** * Method main// w w w . jav a 2 s . co m * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } else if (args.length == 2) { if (args[1].toLowerCase().equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); wb.write(stream); stream.close(); } } else if (args.length == 3 && args[2].toLowerCase().equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); } } catch (Exception e) { e.printStackTrace(); } }