List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue
public double getNumericCellValue()
From source file:info.vancauwenberge.filedriver.filereader.xls.XlsFileReader.java
License:Mozilla Public License
public Map<String, String> readRecord() throws ReadException { HSSFRow row = currentSheet.getRow(nextRowNumber); if (row != null) { //We have data. Map<String, String> result = new HashMap<String, String>(schema.length); //Do not use the iterator (row.cellIterator()): this will cause to skip empty cells! //Use the schema to loop over the cells for (short i = 0; i < schema.length; i++) { String fieldName = schema[i]; HSSFCell cel = row.getCell(i); if (cel != null) { String value = ""; if (cel.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { // TODO: make this configurable: conversion from double to string value = cel.getNumericCellValue() + ""; } else { value = cel.getStringCellValue(); }//from w ww . j a v a 2 s. co m result.put(fieldName, value); } else { result.put(fieldName, ""); } } nextRowNumber++; return result; } else { return null; } }
From source file:is.idega.idegaweb.egov.fsk.business.FSKBusinessBean.java
License:Open Source License
@Override public Map importExcelFile(UploadFile file, Object coursePK, int column) { Map map = new HashMap(); try {//from w w w. j a v a2 s . c o m Course course = getCourse(coursePK); Group group = course.getGroup(); FileInputStream input = new FileInputStream(file.getRealPath()); HSSFWorkbook wb = new HSSFWorkbook(input); HSSFSheet sheet = wb.getSheetAt(0); NumberFormat format = NumberFormat.getNumberInstance(); format.setGroupingUsed(false); format.setMinimumIntegerDigits(10); Collection imported = new ArrayList(); Collection alreadyImported = new ArrayList(); Collection outsideCommune = new ArrayList(); Collection outsideAgeRange = new ArrayList(); Collection invalidPersonalID = new ArrayList(); Collection noUserFound = new ArrayList(); for (int a = sheet.getFirstRowNum(); a <= sheet.getLastRowNum(); a++) { HSSFRow row = sheet.getRow(a); HSSFCell cell = row.getCell((short) (column - 1)); if (cell == null) { continue; } String personalID = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { personalID = cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { personalID = String.valueOf(new Double(cell.getNumericCellValue()).longValue()); } else { personalID = cell.getStringCellValue(); } try { personalID = format.format(format.parse(personalID.replaceAll("-", ""))); } catch (ParseException e1) { e1.printStackTrace(); continue; } if (SocialSecurityNumber.isValidSocialSecurityNumber(personalID, getDefaultLocale())) { try { User user = getUserBusiness().getUser(personalID); if (!group.hasRelationTo(((Integer) user.getPrimaryKey()).intValue())) { IWTimestamp dateOfBirth = new IWTimestamp(user.getDateOfBirth()); dateOfBirth.setMonth(1); dateOfBirth.setDay(1); Age age = new Age(dateOfBirth.getDate()); if (age.getYears(course.getStartDate()) < 6 || age.getYears(course.getStartDate()) > 18) { outsideAgeRange.add(user); continue; } if (!getUserBusiness().isCitizenOfDefaultCommune(user)) { outsideCommune.add(user); continue; } group.addGroup(user); imported.add(user); } else { alreadyImported.add(user); } } catch (FinderException e) { noUserFound.add(personalID); } } else { invalidPersonalID.add(personalID); } } map.put(FSKConstants.REGISTRATION_CODE_REGISTERED, imported); map.put(FSKConstants.REGISTRATION_CODE_ALREADY_REGISTERED, alreadyImported); map.put(FSKConstants.REGISTRATION_CODE_OUTSIDE_COMMUNE, outsideCommune); map.put(FSKConstants.REGISTRATION_CODE_OUTSIDE_AGE_RANGE, outsideAgeRange); map.put(FSKConstants.REGISTRATION_CODE_INVALID_PERSONAL_ID, invalidPersonalID); map.put(FSKConstants.REGISTRATION_CODE_NO_USER_FOUND, noUserFound); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return map; }
From source file:ispyb.common.util.upload.UploadShipmentUtils.java
License:Open Source License
/** * Converts from Excel Cell contents to a String * // w w w. j a v a 2s . c o m * @param cell * The Cell to convert * @return A String value of the contents of the cell */ public static String cellToString(HSSFCell cell) { String retVal = ""; if (cell == null) { return retVal; } if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { retVal = cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { retVal = String.valueOf(new Double(cell.getNumericCellValue()).intValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { if (new Boolean(cell.getBooleanCellValue()) == Boolean.TRUE) { retVal = "true"; } else { retVal = "false"; } } return retVal; }
From source file:ispyb.common.util.upload.UploadShipmentUtils.java
License:Open Source License
/** * Converts from Excel Cell contents to a double * /*w w w . j a v a 2 s . c o m*/ * @param cell * The Cell to convert * @return The double value contained within the Cell or 0.0 if the Cell is not the correct type or is undefined */ public static double cellToDouble(HSSFCell cell) { Double retVal = new Double(0.0); if (cell == null) { return retVal.doubleValue(); } if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { retVal = new Double(cell.getNumericCellValue()); } return retVal.doubleValue(); }
From source file:ispyb.common.util.upload.UploadShipmentUtils.java
License:Open Source License
/** * Converts from Excel Cell contents to a double * /*from w ww. j a v a 2s.c o m*/ * @param cell * The Cell to convert * @return The double value contained within the Cell or 0.0 if the Cell is not the correct type or is undefined */ public static int cellToInt(HSSFCell cell) { Double retVal = new Double(0); if (cell == null) { return retVal.intValue(); } if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { retVal = new Double(cell.getNumericCellValue()); } return retVal.intValue(); }
From source file:ispyb.common.util.upload.XLSParser.java
License:Open Source License
/** * Converts from Excel Cell contents to a String * //from w w w. ja v a 2s. co m * @param cell * The Cell to convert * @return A String value of the contents of the cell */ public String cellToString(HSSFCell cell) { String retVal = ""; if (cell == null) { return retVal; } if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { retVal = cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { retVal = String.valueOf(new Double(cell.getNumericCellValue()).intValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { if (new Boolean(cell.getBooleanCellValue()) == Boolean.TRUE) { retVal = "true"; } else { retVal = "false"; } } return retVal; }
From source file:ispyb.common.util.upload.XLSParser.java
License:Open Source License
/** * Converts from Excel Cell contents to a double * //from ww w .j a v a 2 s.c om * @param cell * The Cell to convert * @return The double value contained within the Cell or 0.0 if the Cell is not the correct type or is undefined */ public double cellToDouble(HSSFCell cell) { Double retVal = new Double(0.0); if (cell == null) { return retVal.doubleValue(); } if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { retVal = new Double(cell.getNumericCellValue()); } return retVal.doubleValue(); }
From source file:ispyb.common.util.upload.XLSParser.java
License:Open Source License
/** * Converts from Excel Cell contents to a int * /*from ww w . j av a 2 s . c om*/ * @param cell * The Cell to convert * @return The int value contained within the Cell or 0 if the Cell is not the correct type or is undefined */ public int cellToInteger(HSSFCell cell) { Integer retVal = new Integer(0); if (cell == null) { return retVal.intValue(); } if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { Double value = new Double(cell.getNumericCellValue()); retVal = new Integer(value.intValue()); } return retVal.intValue(); }
From source file:it.filippovitale.fineco2qif.logic.ExcelSheetAnalysisLogic.java
License:Apache License
public static String getCellValue(HSSFCell cell) { switch (cell != null ? cell.getCellType() : Integer.MIN_VALUE) { case HSSFCell.CELL_TYPE_STRING: return cell.getStringCellValue(); case HSSFCell.CELL_TYPE_NUMERIC: return String.valueOf(cell.getNumericCellValue()); default:/*w ww . j a v a 2 s .c o m*/ return NULL_RAPPRESENTATION; } }
From source file:LoadExcels.loadPMTCT_FO.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from www . ja v a 2 s . c om*/ year = quarter = mflcode = Numerator = Denominator = checker = missing = added = updated = 0; String linked_art = "0"; String not_linked_art = "0"; String unknown_link = "0"; String not_breastfeeding = "0"; String breastfeeding = "0"; String breastfeeding_unknown = "0"; String care_no_test = "0"; String ltfu = "0"; String died = "0"; String transferred_out = "0"; session = request.getSession(); dbConn conn = new dbConn(); nextpage = "loadExcel.jsp"; String applicationPath = request.getServletContext().getRealPath(""); String uploadFilePath = applicationPath + File.separator + UPLOAD_DIR; session = request.getSession(); File fileSaveDir = new File(uploadFilePath); if (!fileSaveDir.exists()) { fileSaveDir.mkdirs(); } System.out.println("Upload File Directory=" + fileSaveDir.getAbsolutePath()); for (Part part : request.getParts()) { fileName = getFileName(part); part.write(uploadFilePath + File.separator + fileName); System.out.println("file name is : " + fileName); } if (!fileName.endsWith(".xls")) { nextpage = "loadExcel.jsp"; session.setAttribute("upload_success", "<font color=\"red\">Failed to load the excel file. Please choose the correct File.</font>"); } else { full_path = fileSaveDir.getAbsolutePath() + "\\" + fileName; System.out.println("the saved file directory is : " + full_path); // GET DATA FROM THE EXCEL AND AND OUTPUT IT ON THE CONSOLE.................................. FileInputStream fileInputStream = new FileInputStream(full_path); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet("PMTCT-FO"); Iterator rowIterator = worksheet.iterator(); int i = 1, y = 0; while (rowIterator.hasNext()) { HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "loadExcel.jsp"; break; } //Year Quarter County Sub County Health Facility MFL Code Type of support Numerator Denominator HIV-infected:Linked to ART HIV-infected: Not linked to ART HIV-infected : Unknown link HIV-uninfected:Not beastfeeding HIV-uninfected: Still breastfeeeding HIV-uninfected:Breastfeeding unknown Other outcomes: In care but not test done Other outcomes:Lost to follow up Other outcomes : Died Other outcomes:Transferred out int rowcount = 0; HSSFCell cellYear = rowi.getCell((short) rowcount); rowcount++; year = (int) cellYear.getNumericCellValue(); HSSFCell cellQuarter = rowi.getCell((short) rowcount); rowcount = rowcount + 3; quarterName = cellQuarter.getStringCellValue(); HSSFCell cellFacilityName = rowi.getCell((short) rowcount); facilityName = cellFacilityName.getStringCellValue(); rowcount++; //HSSFCell cellMFLCODE = rowi.getCell((short) rowcount); //mflcode = Integer.parseInt(cellMFLCODE.getStringCellValue()); if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount = rowcount + 2; if (cellrow.getCellType() == 0) { //numeric mflcode = (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { mflcode = new Integer(cellrow.getStringCellValue()); } } HSSFCell cellNumerator = rowi.getCell((short) rowcount); rowcount++; Numerator = (int) cellNumerator.getNumericCellValue(); HSSFCell cellDenominator = rowi.getCell((short) rowcount); rowcount++; Denominator = (int) cellDenominator.getNumericCellValue(); //linked_art if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric linked_art = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { linked_art = cellrow.getStringCellValue(); } } //not_linked_art if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric not_linked_art = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { not_linked_art = cellrow.getStringCellValue(); } } //unknown_link if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric unknown_link = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { unknown_link = cellrow.getStringCellValue(); } } // //not_breastfeeding if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric not_breastfeeding = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { not_breastfeeding = cellrow.getStringCellValue(); } } //breastfeeding if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric breastfeeding = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { breastfeeding = cellrow.getStringCellValue(); } } //breastfeeding_unknown if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric breastfeeding_unknown = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { breastfeeding_unknown = cellrow.getStringCellValue(); } } //care_no_test if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric care_no_test = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { care_no_test = cellrow.getStringCellValue(); } } //ltfu if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric ltfu = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { ltfu = cellrow.getStringCellValue(); } } //died if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric died = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { died = cellrow.getStringCellValue(); } } //transferred_out if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric transferred_out = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { transferred_out = cellrow.getStringCellValue(); } } facilityID = ""; checker = 0; String get_id = "SELECT SubPartnerID FROM subpartnera WHERE CentreSanteId=?"; conn.pst = conn.conn.prepareStatement(get_id); conn.pst.setInt(1, mflcode); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { facilityID = conn.rs.getString(1); } if (facilityID.length() > 0) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String getQuarterID = "SELECT id FROM quarter WHERE pmtct_fo_name=?"; conn.pst = conn.conn.prepareStatement(getQuarterID); conn.pst.setString(1, quarterName); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { quarter = conn.rs.getInt(1); } checker = 0; // CHECK IF ALREADY ADDED TO PMTCT_FO TABLE id = year + "_" + quarter + "_" + facilityID; // System.out.println("to add data : "+facilityName+" id : "+facilityID+"mfl code "+mflcode+" year : "+year+" quarter : "+quarter+" numerator : "+Numerator+" denominator : "+Denominator); String checkerExisting = "SELECT id FROM pmtct_fo WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } // // // //,,,,,,,,,, if (checker == 0) { String inserter = "INSERT INTO pmtct_fo (id,SubPartnerID,year,quarter,numerator,denominator,linked_art,not_linked_art,unknown_link,not_breastfeeding,breastfeeding,breastfeeding_unknown,care_no_test,ltfu,died,transferred_out) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, id); conn.pst.setString(2, facilityID); conn.pst.setInt(3, year); conn.pst.setInt(4, quarter); conn.pst.setInt(5, Numerator); conn.pst.setInt(6, Denominator); conn.pst.setString(7, linked_art); conn.pst.setString(8, not_linked_art); conn.pst.setString(9, unknown_link); conn.pst.setString(10, not_breastfeeding); conn.pst.setString(11, breastfeeding); conn.pst.setString(12, breastfeeding_unknown); conn.pst.setString(13, care_no_test); conn.pst.setString(14, ltfu); conn.pst.setString(15, died); conn.pst.setString(16, transferred_out); conn.pst.executeUpdate(); added++; } else { String inserter = "UPDATE pmtct_fo SET SubPartnerID=?,year=?,quarter=?,numerator=?,denominator=? ,linked_art=? ,not_linked_art = ? ,unknown_link = ? ,not_breastfeeding = ? ,breastfeeding = ? ,breastfeeding_unknown = ? ,care_no_test = ? ,ltfu = ? ,died = ? , transferred_out = ? WHERE id=?"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, facilityID); conn.pst.setInt(2, year); conn.pst.setInt(3, quarter); conn.pst.setInt(4, Numerator); conn.pst.setInt(5, Denominator); conn.pst.setString(6, linked_art); conn.pst.setString(7, not_linked_art); conn.pst.setString(8, unknown_link); conn.pst.setString(9, not_breastfeeding); conn.pst.setString(10, breastfeeding); conn.pst.setString(11, breastfeeding_unknown); conn.pst.setString(12, care_no_test); conn.pst.setString(13, ltfu); conn.pst.setString(14, died); conn.pst.setString(15, transferred_out); conn.pst.setString(16, id); conn.pst.executeUpdate(); updated++; } } else { missing++; // missing facilities missingFacility += "facility name : " + facilityName + " mfl code : " + mflcode + " excel row num : " + i + "<br>"; System.out.println(facilityName + "facility is missing mflcode :" + mflcode); } i++; } //a code to loop through all synced records without a last month //the affected tables are "eid_datim","viral_load","pmtct_fo","tb_stat_art" AddLastMonth am = new AddLastMonth(); am.addfirstmonth(); //end of sync last month } } catch (SQLException ex) { Logger.getLogger(loadPMTCT_FO.class.getName()).log(Level.SEVERE, null, ex); } String sessionText = "Data for <b>" + added + "</b> sites newly added. <br/> Data for <b>" + updated + "</b> updated. <br/>Data for <b>" + missing + "</b> sites skipped because they are missing in IMIS"; session.setAttribute("pmtctresponse", sessionText); response.sendRedirect(nextpage); }