List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue
public double getNumericCellValue()
From source file:cn.fql.template.poi.MergeInvoiceSheet.java
License:Open Source License
private static void writePrecOfSubActivity(HSSFSheet templateSheet, List months, String newUserName, Map userHours, HSSFRow row, int rowIndex) { for (int j = 4, k = 0; j < 1000;) { HSSFCell detailCell = row.getCell((short) (j)); if (detailCell == null) { if (row.getCell((short) (j + 1)) == null) { break; }//from w ww .ja v a2 s . c om } else { percentageStyle.setFont(templateSheet.getRow(rowIndex - 1).getCell((short) (j + 5)).getCellStyle() .getFont(templateWbk)); HSSFCell precOfType = row.getCell((short) (j + 2)); HSSFCell precOfSubActivity = row.getCell((short) (j + 3)); try { double prec = precOfType.getNumericCellValue(); String key = newUserName + "." + months.get(k); TimeUsage timeUsage = (TimeUsage) userHours.get(key); if (prec > 0) { if (timeUsage.getHours() > 0) { precOfSubActivity.setCellStyle(percentageStyle); precOfSubActivity.setCellValue(prec); } } } catch (Exception e) { } j += 6; k++; } } }
From source file:cn.fql.template.poi.MergeInvoiceSheet.java
License:Open Source License
private static Map getHoursInfo() { HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage (1)"); boolean fillMap = false; Map userHours = new HashMap(); List months = new ArrayList(); for (int i = 4; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } else { fillMap = false;//w w w .ja v a 2 s . c o m } } else { HSSFCell userNameCell = row.getCell((short) 2); if (userNameCell == null) { continue; } String userNameCellValue = userNameCell.getStringCellValue(); if ("Month".equals(userNameCellValue.trim())) { months = new ArrayList(); for (int j = 3; j < 1000; j++) { HSSFCell monthCell = row.getCell((short) j); if (monthCell == null) { if (row.getCell((short) (j + 1)) == null) { break; } } else { String monthValue = monthCell.getStringCellValue(); if (monthValue != null && !monthValue.equals("")) { months.add(monthCell.getStringCellValue()); } } } continue; } if ("Normal Working day of a Month".equals(userNameCellValue.trim())) { continue; } if ("User Name".equals(userNameCellValue)) { fillMap = true; } else if (fillMap) { for (int j = 3, k = 0; j < 1000; j++) { HSSFCell detailCell = row.getCell((short) j); if (detailCell == null) { break; } else { if (j % 2 == 1) { TimeUsage timeUsage = new TimeUsage(); String hoursStr = detailCell.getStringCellValue().trim(); String month = (String) months.get(k); userHours.put(userNameCellValue + "." + month, timeUsage); if (hoursStr != null && !hoursStr.equals("")) { double hours = (new BigDecimal(hoursStr)).doubleValue(); timeUsage.setHours(hours); } } else { String month = (String) months.get(k); TimeUsage timeUsage = (TimeUsage) userHours.get(userNameCellValue + "." + month); timeUsage.setPercentage(detailCell.getNumericCellValue()); k++; } } } } } } return userHours; }
From source file:cn.fql.template.poi.MergeInvoiceSheet.java
License:Open Source License
private static void writeEffortTotal() { HSSFSheet templateSheet = templateWbk.getSheet("Effort Total"); String lastProjectName = null; Date startDateValue = null;/* w ww. j ava 2 s . c o m*/ Date endDateValue = null; double totalDaysValue = 0; for (int i = 4; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } } else { HSSFCell projectNameCell = row.getCell((short) 1); HSSFCell startDateCell = row.getCell((short) 2); HSSFCell endDateCell = row.getCell((short) 3); HSSFCell totalDaysCell = row.getCell((short) 4); if (totalDaysCell == null) { break; } String newProjectName = projectNameCell.getStringCellValue(); Date _startDateValue = startDateCell.getDateCellValue(); Date _endDateValue = endDateCell.getDateCellValue(); double _totalDaysValue = totalDaysCell.getNumericCellValue(); if (lastProjectName == null) { lastProjectName = newProjectName; startDateValue = _startDateValue; endDateValue = _endDateValue; totalDaysValue = _totalDaysValue; } else { if (newProjectName.equals(lastProjectName)) { totalDaysValue += _totalDaysValue; templateSheet.getRow(i - 1).getCell((short) 4).setCellValue(totalDaysValue); if (startDateValue.compareTo(_startDateValue) > 0) { startDateValue = _startDateValue; templateSheet.getRow(i - 1).getCell((short) 2).setCellValue(startDateValue); } if (endDateValue.compareTo(_endDateValue) < 0) { endDateValue = _endDateValue; templateSheet.getRow(i - 1).getCell((short) 3).setCellValue(endDateValue); } templateSheet.removeRow(row); templateSheet.shiftRows(i + 1, 109, -1); i--; } else { lastProjectName = newProjectName; startDateValue = _startDateValue; endDateValue = _endDateValue; totalDaysValue = _totalDaysValue; } } } } }
From source file:cn.vlabs.umt.common.xls.UserXLSParser.java
License:Apache License
private String readCellValue(HSSFCell cell) { if (cell != null) { String cellvalue = null;/*from w ww . j a v a 2s . c o m*/ switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: cellvalue = Integer.toString((int) cell.getNumericCellValue()); break; default: cellvalue = cell.getRichStringCellValue().getString(); break; } return cellvalue; } else { return null; } }
From source file:com.afrisoftech.lib.ExportData.java
public static Vector read(String fileName) { Vector cellVectorHolder = new Vector(); try {//from w ww . ja v a 2 s . c o m FileInputStream myInput = new FileInputStream(fileName); POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); HSSFSheet mySheet = myWorkBook.getSheetAt(0); Iterator rowIter = mySheet.rowIterator(); while (rowIter.hasNext()) { HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); Vector cellStoreVector = new Vector(); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); //Object obj=myCell.getStringCellValue(); System.out.print(myCell.getCellType() + " -"); if (myCell.getCellType() == 0) { cellStoreVector.addElement(myCell.getNumericCellValue()); } else if (myCell.getCellType() == 1) { cellStoreVector.addElement(myCell.getStringCellValue()); } //cellStoreVector.addElement(myCell.getStringCellValue()); } System.out.println(); cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { e.printStackTrace(); } return cellVectorHolder; }
From source file:com.alkacon.opencms.excelimport.CmsExcelContent.java
License:Open Source License
/** * Reads the excel file row by row. Fills the excel import internal structure which is column wise.<p> * /*from w ww . j a va 2 s. c om*/ * @param in the document input stream * * @throws IOException if something goes wring */ private void readExcelColumnContents(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); HSSFSheet sheet = excelWb.getSheetAt(0); int rowsNumber = sheet.getPhysicalNumberOfRows(); if (rowsNumber > 0) { // loop over all rows from excel // do not read first column, because here are only user raw names for (int rowCounter = 1; rowCounter < sheet.getPhysicalNumberOfRows(); rowCounter++) { HSSFRow row = sheet.getRow(rowCounter); if ((row != null)) { // get number of rows in excel if ((rowCounter) > m_rowNumber) { m_rowNumber = rowCounter; } // loop over all columns in this row for (int columnCounter = 0; columnCounter < row.getLastCellNum(); columnCounter++) { CmsExcelColumn cmsExcelCol = (CmsExcelColumn) m_colContents.get(new Integer(columnCounter)); if (cmsExcelCol != null) { // read cell HSSFCell cell = row.getCell((short) columnCounter); if (cell != null) { String text = null; try { // read cell content from excel switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case Cell.CELL_TYPE_NUMERIC: // check for date if (DateUtil.isCellDateFormatted(cell) || DateUtil.isValidExcelDate(cell.getNumericCellValue())) { // valid date Date date = DateUtil.getJavaDate(cell.getNumericCellValue()); text = new Long(date.getTime()).toString(); } else { // no valid date text = Double.toString(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } // add to column list cmsExcelCol.addNewCellValue(rowCounter, text); m_colContents.put(new Integer(columnCounter), cmsExcelCol); } catch (Exception e) { if (LOG.isErrorEnabled()) { LOG.error(e.toString()); } } } else { // add to column list cmsExcelCol.addNewCellValue(rowCounter, ""); m_colContents.put(new Integer(columnCounter), cmsExcelCol); } } } } } } }
From source file:com.asakusafw.testtools.excel.ExcelUtils.java
License:Apache License
/** * ?????????()??/*from ww w. j ava 2s. com*/ * @param sheet ? * @param item ?? * @param row ? * @return ?? */ private String getStringCellValue(HSSFSheet sheet, ConditionSheetItem item, HSSFRow row) { HSSFCell cell = getCell(item, row); String ret; if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { double dval = cell.getNumericCellValue(); ret = Double.toString(dval); ret = ret.replaceAll("\\.0*$", ""); } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { ret = ""; } else if (cell.getCellType() != Cell.CELL_TYPE_STRING) { String fmt = "Excel???????????? file = %s, sheet = %s, row = %d, col = %d"; int rownum = row.getRowNum() + 1; int col = item.getCol() + 1; String msg = String.format(fmt, filename, sheet.getSheetName(), rownum, col); throw new InvalidExcelBookException(msg); } else { ret = cell.getStringCellValue(); } return ret; }
From source file:com.asakusafw.testtools.excel.ExcelUtils.java
License:Apache License
private Double getDubleCellValue(HSSFSheet sheet, ConditionSheetItem item, HSSFRow row) { HSSFCell cell = getCell(item, row); Double ret;/*from ww w .jav a2 s . com*/ if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String str = cell.getStringCellValue(); if (str == null || str.length() == 0) { ret = null; } else { try { ret = Double.parseDouble(str); } catch (NumberFormatException e) { String fmt = "Excel???????????? file = %s, sheet = %s, row = %d, col = %d"; int rownum = row.getRowNum() + 1; int col = item.getCol() + 1; String msg = String.format(fmt, filename, sheet.getSheetName(), rownum, col); throw new InvalidExcelBookException(msg); } } } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { ret = null; } else if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) { String fmt = "Excel???????????? file = %s, sheet = %s, row = %d, col = %d"; int rownum = row.getRowNum() + 1; int col = item.getCol() + 1; String msg = String.format(fmt, filename, sheet.getSheetName(), rownum, col); throw new InvalidExcelBookException(msg); } else { ret = cell.getNumericCellValue(); } return ret; }
From source file:com.asakusafw.testtools.excel.ExcelUtils.java
License:Apache License
/** * ?StringOption??//from www . j av a2s.co m * @param cell ? * @return ? */ private StringOption getStringOption(HSSFCell cell) { String str; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: str = null; break; case Cell.CELL_TYPE_BOOLEAN: if (cell.getBooleanCellValue()) { str = "1"; } else { str = "0"; } break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { double d = cell.getNumericCellValue(); Date date = DateUtil.getJavaDate(d); str = dateTimeFormat.format(date); } else { double d = cell.getNumericCellValue(); str = Double.toString(d); str = str.replaceAll("\\.0*$", ""); } break; case Cell.CELL_TYPE_STRING: str = cell.getStringCellValue(); break; case Cell.CELL_TYPE_ERROR: case Cell.CELL_TYPE_FORMULA: default: String msg = createCellTypeMismatchExceptionMsg(cell, ""); throw new CellTypeMismatchException(msg); } StringOption stringOption = new StringOption(); stringOption.modify(str); return stringOption; }
From source file:com.asakusafw.testtools.excel.ExcelUtils.java
License:Apache License
/** * ?DecimalOption??/*from w ww . j a va2 s . c o m*/ * @param cell ? * @return ? */ private DecimalOption getDecimalOption(HSSFCell cell) { BigDecimal bigDecimal; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: bigDecimal = null; break; case Cell.CELL_TYPE_BOOLEAN: if (cell.getBooleanCellValue()) { bigDecimal = new BigDecimal(1); } else { bigDecimal = new BigDecimal(0); } break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { String msg = createCellTypeMismatchExceptionMsg(cell, "DECIMAL"); throw new CellTypeMismatchException(msg); } else { double d = cell.getNumericCellValue(); if (d < EXCEL_MIN_LONG || EXCEL_MAX_LONG < d) { String msg = createExceptionMsg(cell, "????(" + d + ")"); throw new NumberFormatException(msg); } long l = (long) d; if (l != d) { String msg = createExceptionMsg(cell, "???DECIMAL???????"); throw new NumberFormatException(msg); } String str = Double.toString(d); str = str.replaceAll("\\.0*$", ""); bigDecimal = new BigDecimal(str); } break; case Cell.CELL_TYPE_STRING: String str = cell.getStringCellValue(); try { bigDecimal = new BigDecimal(str); } catch (NumberFormatException e) { String msg = createExceptionMsg(cell, "DECIMAL???????"); throw new NumberFormatException(msg); } break; case Cell.CELL_TYPE_ERROR: case Cell.CELL_TYPE_FORMULA: default: String msg = createCellTypeMismatchExceptionMsg(cell, "DECIMAL"); throw new CellTypeMismatchException(msg); } DecimalOption decimalOption = new DecimalOption(); decimalOption.modify(bigDecimal); return decimalOption; }