List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue
public double getNumericCellValue()
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; }// ww w . j ava 2 s .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:gr.abiss.calipso.domain.ExcelFile.java
License:Open Source License
public ExcelFile(InputStream is) { POIFSFileSystem fs = null;// w w w .jav a 2 s . c o m HSSFWorkbook wb = null; try { fs = new POIFSFileSystem(is); wb = new HSSFWorkbook(fs); } catch (Exception e) { throw new RuntimeException(e); } HSSFSheet sheet = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; int row = 0; int col = 0; columns = new ArrayList<Column>(); //========================== HEADER ==================================== r = sheet.getRow(row); while (true) { c = r.getCell((short) col); if (c == null) { break; } String value = c.getStringCellValue(); if (value == null || value.trim().length() == 0) { break; } Column column = new Column(value.trim()); columns.add(column); col++; } //============================ DATA ==================================== rows = new ArrayList<List<Cell>>(); while (true) { row++; r = sheet.getRow(row); if (r == null) { break; } List rowData = new ArrayList(columns.size()); boolean isEmptyRow = true; for (col = 0; col < columns.size(); col++) { c = r.getCell((short) col); Object value = null; switch (c.getCellType()) { case (HSSFCell.CELL_TYPE_STRING): value = c.getStringCellValue(); break; case (HSSFCell.CELL_TYPE_NUMERIC): // value = c.getDateCellValue(); value = c.getNumericCellValue(); break; case (HSSFCell.CELL_TYPE_BLANK): break; default: // do nothing } if (value != null && value.toString().length() > 0) { isEmptyRow = false; rowData.add(new Cell(value)); } else { rowData.add(null); } } if (isEmptyRow) { break; } rows.add(rowData); } }
From source file:guineu.data.parser.impl.GCGCParserXLS.java
License:Open Source License
/** * Reads lipid information of one row.//from w w w. j av a 2 s . com * @param row * @param numberCols * @return */ public void readRow(HSSFRow row) { HSSFCell cell; SimplePeakListRowGCGC metabolite = new SimplePeakListRowGCGC(); for (int i = 0; i < row.getLastCellNum(); i++) { try { String title = head.get(i); if (title == null) { continue; } cell = row.getCell(i); boolean isfound = false; for (GCGCColumnName field : GCGCColumnName.values()) { if (title.matches(field.getRegularExpression())) { metabolite.setVar(field.getSetFunctionName(), this.getType(cell.toString(), field.getType())); isfound = true; break; } } if (!isfound) { try { metabolite.setPeak(title, cell.getNumericCellValue()); } catch (Exception e) { metabolite.setPeak(title, 0.0); } } if (metabolite.getName() == null) { metabolite.setName("unknown"); } } catch (Exception exception) { //exception.printStackTrace(); } } this.dataset.addRow(metabolite); }
From source file:guineu.data.parser.impl.LCMSParserXLS.java
License:Open Source License
/** * Reads lipid information of one row.//from w w w . j av a2 s. c o m * @param row * @param numberCols * @return */ public void readRow(HSSFRow row) { HSSFCell cell; SimplePeakListRowLCMS lipid = new SimplePeakListRowLCMS(); for (int i = 0; i < row.getLastCellNum(); i++) { try { String title = head.get(i); if (title == null) { continue; } cell = row.getCell((short) i); boolean isfound = false; for (LCMSColumnName field : LCMSColumnName.values()) { if (title.matches(field.getRegularExpression())) { if (field == LCMSColumnName.RT) { double rt = cell.getNumericCellValue(); if (rt < 20) { rt *= 60; lipid.setVar(field.getSetFunctionName(), rt); } else { lipid.setVar(field.getSetFunctionName(), cell.getNumericCellValue()); } } else { lipid.setVar(field.getSetFunctionName(), this.getType(cell.toString(), field.getType())); } isfound = true; break; } } if (!isfound) { try { lipid.setPeak(title, cell.getNumericCellValue()); } catch (Exception e) { if (cell.toString().matches(".*null.*|.*NA.*|.*N/A.*")) { lipid.setPeak(title, 0.0); } else if (cell.toString() != null) { lipid.setPeak(title, cell.toString()); } } } if (i == 0 && (cell.getCellStyle().getFillForegroundColor() == 13)) { lipid.setStandard(1); } int DataType = this.v_type(book, row, cell); if (DataType == 0) { lipid.setControl(false); lipid.setName("z-non valid"); } else { lipid.setControl(true); } if (lipid.getName() == null) { lipid.setName("unknown"); } lipid.setLipidClass(String.valueOf(this.LipidClassLib.get_class(lipid.getName()))); } catch (Exception exception) { //exception.printStackTrace(); } } this.dataset.addRow(lipid); }
From source file:Import.Utils.XSSFConvert.java
/** * @param oldCell//w w w . j a v a 2 s. 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:in.igsa.upload.FileUploadAction.java
License:Apache License
public String upload() throws Exception { try {/*from ww w. ja v a 2 s .co m*/ Map<String, Object> session = ActionContext.getContext().getSession(); List<String> regionNames = new ArrayList<String>(); List<String> modelNames = new ArrayList<String>(); List<String> unitNames = new ArrayList<String>(); List<String> variableNames = new ArrayList<String>(); String emailError = ""; regionNames = service.getRegionNames(); modelNames = service.getModelNames(); unitNames = service.getUnitNames(); variableNames = service.getVariableNames(); String dateTime; Date date = new Date(); dateTime = DateTime.getDateTime1(date); String filePath = servletRequest.getSession().getServletContext().getRealPath("/") + "/files/"; File theFile; parent = new ArrayList<FileUploadVo>(); rowHeader = new FileUploadVo(); System.out.println(" contentType : " + contentType); boolean row_header = true; if ("application/vnd.ms-excel".equalsIgnoreCase(contentType)) { try { theFile = new File(filePath, DateTime.getFileForUpload(date) + ".xls"); if (theFile.exists()) { System.out.println(" file esist "); } else { System.out.println(" null file"); } fileNameForUpload = DateTime.getFileForUpload(date) + ".xls"; FileUtils.copyFile(upload, theFile); FileInputStream file = new FileInputStream(theFile); //new File("\\files\\howtodoinjava_demo.xls")); //Create Workbook instance holding reference to .xls file HSSFWorkbook workbook = new HSSFWorkbook(file); //Get first/desired sheet from the workbook (.xls) HSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { HSSFRow row = (HSSFRow) rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); child = new FileUploadVo(); yearVal = new ArrayList<String>(); year = new ArrayList<String>(); int i = 1; if (row_header) { while (cellIterator.hasNext()) { HSSFCell cell = (HSSFCell) cellIterator.next(); //Check the cell type and format accordingly if (i > 5) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: mapHeader.put(i + "", cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: mapHeader.put(i + "", cell.getStringCellValue()); break; } } i++; } row_header = false; } else { while (cellIterator.hasNext()) { HSSFCell cell = (HSSFCell) cellIterator.next(); //Check the cell type and format accordingly if (i == 1) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setModel(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setModel(cell.getStringCellValue()); break; } } else if (i == 2) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setScenario(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setScenario(cell.getStringCellValue()); break; } } else if (i == 3) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setRegion(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setRegion(cell.getStringCellValue()); break; } } else if (i == 4) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setVariable(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setVariable(cell.getStringCellValue()); break; } } else if (i == 5) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setUnit(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setUnit(cell.getStringCellValue()); break; } } else { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: yearVal.add(cell.getNumericCellValue() + ""); year.add(mapHeader.get(i + "").toString()); break; case Cell.CELL_TYPE_STRING: yearVal.add(cell.getStringCellValue() + ""); year.add(mapHeader.get(i + "").toString()); break; } } //parent.add(child); //child.setValue(value); i++; } child.setVal(yearVal); child.setYear(year); child.setDateTime(dateTime); child.setUploadedBy(session.get("user_id").toString()); child.setFilePath("files/" + fileNameForUpload); parent.add(child); } } file.close(); } catch (Exception e) { e.printStackTrace(); } service.deleteModelSceRegion(child); for (FileUploadVo aa : parent) { boolean flag = true; if (!modelNames.contains(aa.getModel())) { emailError = emailError + "ERROR : Model name <i>" + aa.getModel() + "</i> not in list of valid Model name. <br>"; flag = false; } if (!regionNames.contains(aa.getRegion())) { emailError = emailError + "ERROR : Region name <i>" + aa.getRegion() + "</i> not in list of valid Region name. <br>"; flag = false; } if (!variableNames.contains(aa.getVariable())) { emailError = emailError + "ERROR : Variable name <i>" + aa.getVariable() + "</i> not in list of valid Variable name. <br>"; flag = false; } if (!unitNames.contains(aa.getUnit())) { emailError = emailError + "ERROR : Unit name <i>" + aa.getUnit() + "</i> not in list of valid Unit name. <br>"; flag = false; } if (flag) service.insertFileUpload(aa); else emailError = emailError + "********************************************<br>"; } } else if ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet".equals(contentType)) { try { theFile = new File(filePath, DateTime.getFileForUpload(date) + ".xlsx"); fileNameForUpload = DateTime.getFileForUpload(date) + ".xlsx"; FileUtils.copyFile(upload, theFile); FileInputStream file = new FileInputStream(theFile); //new File("\\files\\howtodoinjava_demo.xls")); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { XSSFRow row = (XSSFRow) rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); child = new FileUploadVo(); yearVal = new ArrayList<String>(); year = new ArrayList<String>(); int i = 1; if (row_header) { while (cellIterator.hasNext()) { XSSFCell cell = (XSSFCell) cellIterator.next(); //Check the cell type and format accordingly if (i > 5) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: mapHeader.put(i + "", cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: mapHeader.put(i + "", cell.getStringCellValue()); break; } } i++; } row_header = false; } else { while (cellIterator.hasNext()) { XSSFCell cell = (XSSFCell) cellIterator.next(); //Check the cell type and format accordingly if (i == 1) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setModel(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setModel(cell.getStringCellValue()); break; } } else if (i == 2) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setScenario(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setScenario(cell.getStringCellValue()); break; } } else if (i == 3) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setRegion(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setRegion(cell.getStringCellValue()); break; } } else if (i == 4) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setVariable(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setVariable(cell.getStringCellValue()); break; } } else if (i == 5) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setUnit(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setUnit(cell.getStringCellValue()); break; } } else { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: yearVal.add(cell.getNumericCellValue() + ""); year.add(mapHeader.get(i + "").toString()); break; case Cell.CELL_TYPE_STRING: yearVal.add(cell.getStringCellValue() + ""); year.add(mapHeader.get(i + "").toString()); break; } } //parent.add(child); //child.setValue(value); i++; } child.setVal(yearVal); child.setYear(year); child.setDateTime(dateTime); child.setUploadedBy(session.get("user_id").toString()); child.setFilePath("files/" + fileNameForUpload); parent.add(child); System.out.println("\n"); } } file.close(); } catch (Exception e) { e.printStackTrace(); } service.deleteModelSceRegion(child); for (FileUploadVo aa : parent) { boolean flag = true; if (!modelNames.contains(aa.getModel())) { emailError = emailError + "ERROR : Model name " + aa.getModel() + " not in list of valid Model name. <br>"; flag = false; } if (!regionNames.contains(aa.getRegion())) { emailError = emailError + "ERROR : Region name " + aa.getRegion() + " not in list of valid Region name. <br>"; flag = false; } if (!variableNames.contains(aa.getVariable())) { emailError = emailError + "ERROR : Variable name " + aa.getVariable() + " not in list of valid Variable name. <br>"; flag = false; } if (!unitNames.contains(aa.getUnit())) { emailError = emailError + "ERROR : Unit name " + aa.getUnit() + " not in list of valid Unit name. <br>"; flag = false; } if (flag) service.insertFileUpload(aa); else emailError = emailError + "********************************************<br>"; } } else { addActionError(" File Format Should be xls or xlsx "); scenarioReport = service.getScenarioReport(); return INPUT; } String subject = "Error : undefined parameters"; String content = "Dear " + session.get("user_id").toString() + " <br> <br> here's a brief report about your scenarios data upload to the SSP database." + "Please do open and carefully check the attached log file to find out whether the import was successful.<br><br>Regards,<br>SSP database admin Summary <br><br><br>"; if (emailError != "") SendEmail.send("4igsalabs@gmail.com", session.get("user_email").toString(), subject, content + emailError); } catch (Exception e) { addActionError(e.getCause().getLocalizedMessage()); scenarioReport = service.getScenarioReport(); linkAction(scenarioReport); return INPUT; } scenarioReport = service.getScenarioReport(); linkAction(scenarioReport); return SUCCESS; }
From source file:include.excel_import.Outter.java
License:Open Source License
private void pump(Vector vector, HSSFCell hssfcell) { String s = getCellType(hssfcell); if (s.equals("INT")) { DecimalFormat decimalformat = new DecimalFormat("##"); String s1 = String.valueOf(decimalformat.format(hssfcell.getNumericCellValue())); vector.addElement(s1);//from w ww. j av a 2 s . c om } else if (s.equals("DOUBLE")) { DecimalFormat decimalformat1 = new DecimalFormat("##.##"); String s2 = String.valueOf(decimalformat1.format(hssfcell.getNumericCellValue())); vector.addElement(s2); } else if (s.equals("STRING")) vector.addElement(hssfcell.getStringCellValue()); else if (s.equals("DATE")) { SimpleDateFormat simpledateformat = new SimpleDateFormat("yyyy-MM-dd"); String s3 = String.valueOf(simpledateformat.format(hssfcell.getDateCellValue())); vector.addElement(s3); } }
From source file:include.excel_import.Outter.java
License:Open Source License
private void pump(Vector vector, HSSFCell hssfcell, String s) { String s1 = getCellType(hssfcell); if (s1.equals("INT")) { DecimalFormat decimalformat = new DecimalFormat("##"); String s2 = String.valueOf(decimalformat.format(hssfcell.getNumericCellValue())); vector.addElement(s2);/* www . j av a 2 s . co m*/ } else if (s1.equals("INT1")) { DecimalFormat decimalformat1 = new DecimalFormat("##"); String s3 = String.valueOf(decimalformat1.format(hssfcell.getNumericCellValue())); vector.addElement(s3); } else if (s1.equals("DOUBLE")) { DecimalFormat decimalformat2 = new DecimalFormat("##.##"); String s4 = String.valueOf(decimalformat2.format(hssfcell.getNumericCellValue())); vector.addElement(s4); } else if (s1.equals("STRING")) vector.addElement(hssfcell.getStringCellValue()); else if (s1.equals("DATE")) { SimpleDateFormat simpledateformat = new SimpleDateFormat("yyyy-MM-dd"); String s5 = String.valueOf(simpledateformat.format(hssfcell.getDateCellValue())); vector.addElement(s5); } else if (s1.equals("BLANK")) if (s.equals("STRING")) vector.addElement(" "); else if (s.equals("INT")) vector.addElement(new Integer(0)); else if (s.equals("DOUBLE")) vector.addElement(new Double(0.0D)); else if (s.equals("DATE")) vector.addElement("0000-00-00"); else if (s.equals("UNKNOWN")) vector.addElement(" "); }
From source file:include.excel_import.XlsInfo.java
License:Open Source License
private void dump(Vector result, HSSFCell cell, String itemType) { String type = getCellDataType(cell); String num, date = null;//from w w w . jav a2s. c o m if (type.equals("INT")) { num = String.valueOf(intFormat.format(cell.getNumericCellValue())); result.addElement(num); } else if (type.equals("STRING")) { result.addElement(cell.getStringCellValue()); } else if (type.equals("DATE")) { date = String.valueOf(yyyymmddFormat.format(cell.getDateCellValue())); result.addElement(date); } else if (type.equals("DOUBLE")) { num = String.valueOf(doubleFormat.format(cell.getNumericCellValue())); result.addElement(num); } else if (type.equals("INT1")) { num = String.valueOf(intFormat.format(cell.getNumericCellValue())); result.addElement(num); } else if (type.equals("BLANK")) { if (itemType.equals("STRING")) { result.addElement(" "); } else if (itemType.equals("INT")) { result.addElement(new Integer(0)); } else if (itemType.equals("DOUBLE")) { result.addElement(new Double(0.00)); } else if (itemType.equals("DATE")) { result.addElement("0000-00-00"); } else if (itemType.equals("UNKNOWN")) { result.addElement(" "); } } type = null; num = null; date = null; }
From source file:info.jtrac.domain.ExcelFile.java
License:Apache License
public ExcelFile(InputStream is) { POIFSFileSystem fs = null;/*from w w w .j ava 2 s . com*/ HSSFWorkbook wb = null; try { fs = new POIFSFileSystem(is); wb = new HSSFWorkbook(fs); } catch (Exception e) { throw new RuntimeException(e); } HSSFSheet sheet = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; int row = 0; int col = 0; columns = new ArrayList<Column>(); //========================== HEADER ==================================== // column headings are important, this routine assumes that the first // row is a header row and that reaching an empty cell means end of data r = sheet.getRow(row); while (true) { c = r.getCell((short) col); if (c == null) { break; } String value = c.getStringCellValue(); if (value == null || value.trim().length() == 0) { break; } Column column = new Column(value.trim()); columns.add(column); col++; } //============================ DATA ==================================== rows = new ArrayList<List<Cell>>(); while (true) { row++; r = sheet.getRow(row); if (r == null) { break; } List<Cell> rowData = new ArrayList<>(columns.size()); boolean isEmptyRow = true; for (col = 0; col < columns.size(); col++) { c = r.getCell((short) col); Object value = null; switch (c.getCellType()) { case (HSSFCell.CELL_TYPE_STRING): value = c.getStringCellValue(); break; case (HSSFCell.CELL_TYPE_NUMERIC): // value = c.getDateCellValue(); value = c.getNumericCellValue(); break; case (HSSFCell.CELL_TYPE_BLANK): break; default: // do nothing } if (value != null && value.toString().length() > 0) { isEmptyRow = false; rowData.add(new Cell(value)); } else { rowData.add(new Cell(null)); } } if (isEmptyRow) { break; } rows.add(rowData); } }