List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue
public String getStringCellValue()
From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java
License:Open Source License
private String getCellData(final HSSFCell cell) { if (cell == null) return null; int colspan = 1; if (colIndex == mergeStart) { // First cell in the merging region - set colspan. colspan = mergeEnd - mergeStart + 1; } else if (colIndex == mergeEnd) { // Last cell in the merging region - no more skipped cells. mergeStart = -1;//from w w w. ja v a 2 s .c om mergeEnd = -1; return null; } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) { // Within the merging region - skip the cell. return null; } //StringBuffer buf = new StringBuffer(); String val = ""; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: // POI does not distinguish between integer and double, thus: final double original = cell.getNumericCellValue(), rounded = Math.round(original); if (Math.abs(rounded - original) < 0.00000000000000001) { val = String.valueOf((int) rounded); } else { val = String.valueOf(original); } break; case HSSFCell.CELL_TYPE_FORMULA: final CellValue cv = evaluator.evaluate(cell); if (cv == null) return null; switch (cv.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: out.append(cv.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: out.append(cv.getNumberValue()); break; case Cell.CELL_TYPE_STRING: out.append(cv.getStringValue()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: break; default: break; } break; default: // Neither string or number? Could be a date. try { val = sdf.format(cell.getDateCellValue()); } catch (final Exception e1) { } } } catch (final Exception e) { val = e.getMessage(); } if ("null".equals(val)) { val = ""; } return val; }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java
License:BSD License
/** * return label of list cell , as oppoed to its int value * @param sheet/*from ww w .j a va 2 s. co m*/ * @param row * @param col * @return */ public static String getLabel(HSSFSheet sheet, int row, short col) { HSSFRow hssfRow = getRow(sheet, row); if (hssfRow == null) { return null; } HSSFCell cell = getRow(sheet, row).getCell(col); if (isNull(cell)) { return null; } return cell.getStringCellValue(); }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java
License:BSD License
public static String getString(HSSFSheet sheet, int row, short col) { HSSFRow hssfRow = getRow(sheet, row); if (hssfRow == null) { return null; }/* w w w . j a v a 2 s .com*/ HSSFCell cell = getRow(sheet, row).getCell(col); if (isNull(cell)) { return null; } return cell.getStringCellValue(); }
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. ja v a 2 s . c om*/ 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:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java
License:BSD License
public static Double getFormulaResultAsDouble(HSSFCell cell) { String val = cell.getStringCellValue(); throw new RuntimeException("FormaulaString:" + val); }
From source file:gr.abiss.calipso.domain.ExcelFile.java
License:Open Source License
public ExcelFile(InputStream is) { POIFSFileSystem fs = null;//from w ww.ja v a2 s. co 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:Import.Utils.XSSFConvert.java
/** * @param oldCell//from ww w . j a v a 2 s. c o m * @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:Importers.ExcelImporter.java
License:Apache License
@Override public DefaultMutableTreeNode readFile(File file) { System.out.println("==ExcelImporter=readFile: " + file.getAbsolutePath()); DefaultMutableTreeNode root = new DefaultMutableTreeNode("vulns"); try {//from www.j ava 2 s. com POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int tmp = 0; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } for (int r = 1; r < rows; r++) { row = sheet.getRow(r); if (row != null) { // Create a new vuln Vulnerability vuln = new Vulnerability(); vuln.setTitle("NEW"); vuln.setIs_custom_risk(true); vuln.setRisk_category("None"); for (int c = 0; c < cols; c++) { cell = row.getCell(c); if (cell != null) { // Your code here String value = cell.getStringCellValue(); switch (c) { case 1:// title vuln.setTitle(value); break; case 2: // Risk CellStyle style = cell.getCellStyle(); short colorIdx = style.getFillForegroundColor(); HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette(); HSSFColor color = palette.getColor(colorIdx); String cc = color.getHexString(); System.out.println(cc); if (cc.equalsIgnoreCase("8080:8080:0")) { vuln.setRisk_category("Critical"); } else if (cc.equalsIgnoreCase("FFFF:0:0")) { vuln.setRisk_category("High"); } else if (cc.equalsIgnoreCase("FFFF:6666:0")) { vuln.setRisk_category("Medium"); } else if (cc.equalsIgnoreCase("F2F2:ECEC:0")) { vuln.setRisk_category("Low"); } else if (cc.equalsIgnoreCase("0:0:FFFF")) { vuln.setRisk_category("Info"); } break; case 3:// cvss string System.out.println(value); if (value.equalsIgnoreCase("No CVSS Vector")) { vuln.setIs_custom_risk(true); } else { vuln.setIs_custom_risk(false); vuln.setCvss_vector_string("CVSS2#" + value); } break; case 4://Description vuln.setDescription(value); break; case 5://Recommendation vuln.setRecommendation(value); break; case 6://Affected Hosts try { String[] lines = value.split("\n"); for (String line : lines) { String[] bits = line.split(" "); Host host = new Host(); host.setIp_address(bits[0]); String portprotocol = bits[2]; host.setPortnumber(portprotocol.split("/")[0]); host.setProtocol(portprotocol.split("/")[1]); vuln.addAffectedHost(host); } } catch (Exception ex) { ; } break; } } } System.out.println(vuln); root.add(new DefaultMutableTreeNode(vuln)); } } } catch (Exception ex) { ex.printStackTrace(); } return root; }
From source file:in.igsa.upload.FileUploadAction.java
License:Apache License
public String upload() throws Exception { try {/*from w ww. j ava 2 s .c o 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);/* ww w.j a v a 2 s . c o m*/ } 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); } }