List of usage examples for org.apache.poi.ss.usermodel Cell getNumericCellValue
double getNumericCellValue();
From source file:it.dontesta.liferay.example.portal.client.ImportUsersToLiferay.java
License:Open Source License
/** * Read users to import from excel file. * /*from w w w .j a v a2 s . co m*/ * @return Returns a list of users who are ready for import. */ private static List<UserToImport> getUsersToImportFromExcel() { String fileName = (System.getProperty("fileToImport") != null) ? System.getProperty("fileToImport") : FILE_TO_IMPORT_USERS; InputStream inp = null; List<UserToImport> usersList = new ArrayList<UserToImport>(); UserToImport user = null; boolean readyForImport = true; try { inp = new FileInputStream(fileName); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); for (Row row : sheet) { LOGGER.debug("Processing row index {}...", row.getRowNum()); if (row.getRowNum() == 0) { LOGGER.debug("First row is the header. Skip this row"); continue; } else { user = new UserToImport(); } for (Cell cell : row) { LOGGER.debug("Processing cell index {}...", cell.getColumnIndex()); switch (cell.getColumnIndex()) { case 0: if (!cell.getStringCellValue().isEmpty()) { user.setTitle(cell.getStringCellValue()); LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(), cell.getStringCellValue()); } LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex()); break; case 1: if (!cell.getStringCellValue().isEmpty()) { user.setScreenName(cell.getStringCellValue()); LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(), cell.getStringCellValue()); } else { LOGGER.warn("The username attribute is not null. Row skipped"); LOGGER.warn("Processing cell index {}...[FAILED]", cell.getColumnIndex()); break; } LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex()); break; case 2: if (!cell.getStringCellValue().isEmpty()) { user.setEmail(cell.getStringCellValue()); LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(), cell.getStringCellValue()); } else { LOGGER.warn("The email attribute is not null. Row skipped"); LOGGER.warn("Processing cell index {}...[FAILED]", cell.getColumnIndex()); break; } LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex()); break; case 3: if (!cell.getStringCellValue().isEmpty()) { user.setFirstName(cell.getStringCellValue()); LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(), cell.getStringCellValue()); } LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex()); break; case 4: if (!cell.getStringCellValue().isEmpty()) { user.setMiddleName(cell.getStringCellValue()); LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(), cell.getStringCellValue()); } LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex()); break; case 5: if (!cell.getStringCellValue().isEmpty()) { user.setLastName(cell.getStringCellValue()); LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(), cell.getStringCellValue()); } LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex()); break; case 6: if (!cell.getStringCellValue().isEmpty()) { user.setGender(cell.getStringCellValue()); LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(), cell.getStringCellValue()); } LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex()); break; case 7: if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { Calendar calBirthDate = Calendar.getInstance(); calBirthDate.setTime(cell.getDateCellValue()); user.setBirthDate(calBirthDate); LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(), cell.getDateCellValue()); } else { LOGGER.warn("Value cell index {} {}", cell.getColumnIndex(), cell.getNumericCellValue()); LOGGER.warn("Value cell index {} not contain a date type format", cell.getColumnIndex()); } } LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex()); break; case 8: if (!cell.getStringCellValue().isEmpty()) { user.setJobTitle(cell.getStringCellValue()); LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(), cell.getStringCellValue()); } break; case 9: if (!cell.getStringCellValue().isEmpty()) { user.setSiteName(cell.getStringCellValue().split(",")); LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(), Arrays.toString(cell.getStringCellValue().split(","))); } LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex()); break; case 10: if (!cell.getStringCellValue().isEmpty()) { user.setRoleName(cell.getStringCellValue().split(",")); LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(), Arrays.toString(cell.getStringCellValue().split(","))); } LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex()); break; case 11: if (!cell.getStringCellValue().isEmpty()) { user.setLanguageId(cell.getStringCellValue()); LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(), cell.getStringCellValue()); } LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex()); break; case 12: if (!cell.getStringCellValue().isEmpty()) { user.setTimeZoneId(cell.getStringCellValue()); LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(), cell.getStringCellValue()); } LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex()); break; case 13: if (!cell.getStringCellValue().isEmpty()) { user.setAccountId(cell.getStringCellValue()); LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(), cell.getStringCellValue()); } LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex()); break; default: break; } } if (user.getScreenName() == null) { LOGGER.warn("The username attribute can not be null for rowId {}", row.getRowNum()); readyForImport = false; } if (user.getEmail() == null) { LOGGER.warn("The email attribute can not be null for rowId {}", row.getRowNum()); readyForImport = false; } if (readyForImport) { LOGGER.info("Add user object {} to user list...", user.toString()); usersList.add(user); } } } catch (FileNotFoundException e) { LOGGER.error(e.getMessage()); } catch (IllegalStateException e) { LOGGER.error(e.getMessage()); } catch (InvalidFormatException e) { LOGGER.error(e.getMessage()); } catch (IOException e) { LOGGER.error(e.getMessage()); } return usersList; }
From source file:it.redev.parco.utils.ExcelUtils.java
License:Open Source License
public static String getCellValue(Cell cell) { String value = null;/*from w w w .j av a 2 s .c o m*/ switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = new Boolean(cell.getBooleanCellValue()).toString(); break; case Cell.CELL_TYPE_NUMERIC: value = new BigDecimal(cell.getNumericCellValue()).toPlainString(); break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; } return value; }
From source file:it.smartcommunitylab.ungiorno.importer.Importer.java
License:Apache License
private String getCellValue(Cell cell, String key) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_NUMERIC: if (key != null && numFields.contains(key.toUpperCase())) { double v = cell.getNumericCellValue(); if (v - Math.floor(v) > 0) return "" + v; return "" + Math.round(v); }//from w ww. j a v a 2s. c o m Date date = cell.getDateCellValue(); Calendar cal = Calendar.getInstance(); cal.setTime(date); if (cal.get(Calendar.YEAR) <= 1970) return TIME_FORMAT.format(date); return DATE_FORMAT.format(date); // TODO revise date management // String value = null; // Calendar cal = new GregorianCalendar(); // cal.setTime(cell.getDateCellValue()); // if (cal.get(Calendar.YEAR) < 2014) { // value = cal.get(Calendar.HOUR_OF_DAY) + ":" + (cal.get(Calendar.MINUTE) + "0").substring(0, 2); // } else { // String month = "0" + (1 + cal.get(Calendar.MONTH)); // String day = "0" + cal.get(Calendar.DAY_OF_MONTH); // value = cal.get(Calendar.YEAR) + "-" + month.substring(month.length() - 2, month.length()) + "-" + day.substring(day.length() - 2, day.length()); // } // return value; } return ""; }
From source file:it.unitn.elisco.utils.Utilities.java
public static List<Long> getApprovedQuestionListFromExcel(InputStream fileStream) throws IOException { // Build the document from stream Workbook workbook = new XSSFWorkbook(fileStream); // Get the sheet with data Sheet sheet = workbook.getSheetAt(0); // Create a list for results List<Long> approvedQuestions = new ArrayList<>(); int rownum = 1; Row row;/* www . jav a2s . c om*/ Cell approvedCell; Cell idCell; while (sheet.getRow(rownum) != null) { row = sheet.getRow(rownum); rownum++; approvedCell = row.getCell(3); idCell = row.getCell(0); if (approvedCell != null && idCell != null && approvedCell.getStringCellValue().equalsIgnoreCase("SI")) { approvedQuestions.add(new Double(idCell.getNumericCellValue()).longValue()); } } return approvedQuestions; }
From source file:it.vige.greenarea.file.ImportaXLSFile.java
License:Apache License
private void aggiungiCampoARichiestaXML(RichiestaXML richiestaXML, Cell cell, int posizione) throws Exception { switch (posizione) { case 0:// ww w . jav a 2 s .c o m richiestaXML.setShipmentId((long) cell.getNumericCellValue() + ""); break; case 1: richiestaXML.setIdStop(cell.getStringCellValue()); break; case 2: richiestaXML.setDepot(cell.getStringCellValue()); break; case 3: richiestaXML.setPieces((int) cell.getNumericCellValue()); break; case 4: richiestaXML.setWeight(cell.getNumericCellValue()); break; case 5: richiestaXML.setVolume(cell.getNumericCellValue()); break; case 6: int data = (int) cell.getNumericCellValue(); Date dataFormattata = yyyyMMdd.parse(data + ""); richiestaXML.setDataEarlestPu(dataFormattata); break; case 7: data = (int) cell.getNumericCellValue(); dataFormattata = yyyyMMdd.parse(data + ""); richiestaXML.setDataLatestPu(dataFormattata); break; case 8: data = (int) cell.getNumericCellValue(); dataFormattata = yyyyMMdd.parse(data + ""); richiestaXML.setDataEarlestDelivery(dataFormattata); break; case 9: data = (int) cell.getNumericCellValue(); dataFormattata = yyyyMMdd.parse(data + ""); richiestaXML.setDataLatestDelivery(dataFormattata); break; case 10: richiestaXML.setTimeFromPu(cell.getNumericCellValue()); break; case 11: richiestaXML.setTimeToPu(cell.getNumericCellValue()); break; case 12: richiestaXML.setTimeFromDelivery(cell.getNumericCellValue()); break; case 13: richiestaXML.setTimeToDelivery(cell.getNumericCellValue()); break; case 14: richiestaXML.setHandlingType(cell.getStringCellValue()); break; case 15: richiestaXML.setPackageType(cell.getStringCellValue().charAt(0)); break; case 16: richiestaXML.setCustomer(cell.getStringCellValue()); break; case 17: richiestaXML.setAddress(cell.getStringCellValue()); break; case 18: richiestaXML.setZipCode((int) cell.getNumericCellValue()); break; case 19: richiestaXML.setProvince(cell.getStringCellValue()); break; case 20: richiestaXML.setCity(cell.getStringCellValue()); break; case 21: richiestaXML.setCountry(cell.getStringCellValue()); break; case 22: richiestaXML.setShoppingCentre((int) cell.getNumericCellValue()); break; case 23: richiestaXML.setRoundCode(cell.getStringCellValue()); break; case 24: richiestaXML.setTntType(cell.getStringCellValue()); break; case 25: richiestaXML.setFlagInternational(cell.getStringCellValue()); break; case 26: richiestaXML.setDomesticLdv(cell.getStringCellValue()); break; case 27: richiestaXML.setTaskField(cell.getStringCellValue()); break; case 28: richiestaXML.setTel(cell.getStringCellValue()); break; case 29: richiestaXML.setEmail(cell.getStringCellValue()); break; case 30: richiestaXML.setHandlingClass((int) cell.getNumericCellValue()); break; case 31: richiestaXML.setLocExtId(cell.getStringCellValue()); break; case 32: richiestaXML.setStatus(new Integer(cell.getStringCellValue())); break; } }
From source file:jasco.Jasco.java
private List<List<String>> convertSheetToArrayList(int y, int height, int x, int width, Sheet sheet1) { List<List<String>> rows = new ArrayList<>(); for (int row = y; row < y + height; row++) { ArrayList<String> rowData = new ArrayList<>(); for (int col = x; col < x + width; col++) { Cell cell = sheet1.getRow(row).getCell(col); switch (cell.getCellType()) { case (Cell.CELL_TYPE_STRING): RichTextString str = cell.getRichStringCellValue(); rowData.add(str.toString()); break; case (Cell.CELL_TYPE_NUMERIC): rowData.add("" + cell.getNumericCellValue()); break; case (Cell.CELL_TYPE_BOOLEAN): rowData.add("" + cell.getBooleanCellValue()); break; case (Cell.CELL_TYPE_FORMULA): switch (cell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: rowData.add("" + cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: rowData.add("" + cell.getRichStringCellValue()); break; }// ww w . j a v a2s . c o m break; case (Cell.CELL_TYPE_BLANK): rowData.add(""); break; default: System.out.println("unknown cell type" + cell.getCellType()); } rows.add(rowData); } } return rows; }
From source file:javaapp.CompareOpenClose.java
public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "S9/GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheet(name); //String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; while (iterator.hasNext()) { Row nextRow = iterator.next();/*from w w w .jav a 2s. c o m*/ Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0; cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) { rpps = rec + "-" + pay + "-" + per + "-" + svc; //System.out.println(rpps+"|"+dval+"|"+cval); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }
From source file:javaapp.ExReadSample.java
public static void main(String[] args) throws IOException { String excelFilePath = "GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); Workbook workbook = new XSSFWorkbook(inputStream); /*/*from w w w . j av a 2 s . com*/ ArrayList<String> open = parseReport(5,1,2,5,8,44,46,"open"); ArrayList<String> close = parseReport(24,1,2,5,8,44,46,"close"); ArrayList<String> rinvoice = parseReport(34,0,1,2,3,5,6,"rinvoice"); ArrayList<String> correction = parseReport(14,0,1,4,5,10,10,"correction"); ArrayList<String> adjust = parseReport(18,0,4,7,8,11,11,"adjust"); ArrayList<String> o1cf = parseReport(22,1,2,8,5,44,46,"o1cf"); ArrayList<String> cdata = parseReport(36,0,1,2,3,7,7,"cdata"); */ //ArrayList<String> sheet_names = {"Uninv Opening Position","Uninv Closing Position","Debtor Reconciled Invoices","Uninv Debtor Data Corrections","Uninv Debtor Adjustments","Uninv One1Clear Features","Debtor Control Data"}; String sheet_names[] = { "Uninv Opening Position", "Uninv Closing Position", "Debtor Reconciled Invoices", "Uninv Debtor Data Corrections", "Uninv Debtor Adjustments", "Uninv One1Clear Features", "Debtor Control Data" }; int sheet_no; for (String str : sheet_names) { sheet_no = workbook.getSheetIndex(str); Sheet wb_sheet = workbook.getSheetAt(sheet_no); String sheet_name = str; Iterator<Row> iterator = wb_sheet.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; int j = 0; while (iterator.hasNext()) { j++; System.out.println(sheet_name + "----->row" + j); if (j == 10) { j = 0; break; } Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0; cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 1 || cell.getColumnIndex() == 2 || cell.getColumnIndex() == 5 || cell.getColumnIndex() == 8 || cell.getColumnIndex() == 44 || cell.getColumnIndex() == 46) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == 1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == 2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == 5) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == 8) { per = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == 44) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == 46) { cval = cell.getNumericCellValue(); } break; } } } if (rec.length() == 5 || rec.length() == 8) { rpps = rec + "-" + pay + "-" + per + "-" + svc; System.out.println(rpps + "|" + dval + "|" + cval); //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } } }
From source file:javaapp.ParseCreditorTransactionsData.java
public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "S9/GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheet(name); //String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; String filter = ""; while (iterator.hasNext()) { Row nextRow = iterator.next();/*w w w . j a v a 2s . c o m*/ Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0; cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 32 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } if (cell.getColumnIndex() == 32) { filter = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) { rpps = rec + "-" + pay + "-" + per + "-" + svc; if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) { if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled") || filter.equalsIgnoreCase("") || filter.equalsIgnoreCase("-")) { System.out.print(rpps); System.out.println(filter); continue; } System.out.print(filter); System.out.println(rpps); } // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){ //System.out.println(rpps+"|"+dval+"|"+cval); //} //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }
From source file:javaapp.ParseCreditorTransactionsDataEE.java
public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "EE/GBRMECOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheet(name); //String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; String filter = ""; while (iterator.hasNext()) { rec = ""; pay = ""; Row nextRow = iterator.next();/* w ww. j a v a2 s .com*/ Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0; cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 23 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } if (cell.getColumnIndex() == 23) { filter = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) { rpps = rec + "-" + pay + "-" + per + "-" + svc; //System.out.print(rpps); //System.out.println("--->"+tbl); if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) { if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled") || filter.equalsIgnoreCase("")) { continue; } } // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){ //System.out.println(rpps+"|"+dval+"|"+cval); //} //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }