List of usage examples for org.apache.poi.ss.usermodel Row getCell
Cell getCell(int cellnum);
From source file:TestCaseReaderTest.java
public void testSimple() throws Exception { //public List<TestScriptTemplate> readExcel(){ FileInputStream file = new FileInputStream( new File("C:\\Users\\nbret00\\Documents\\SeleniumSmokeTest\\TestCases.xlsx")); List<TestScriptTemplate> tstList = new ArrayList<TestScriptTemplate>(); //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(); List<TestScriptTemplate> TestScriptTemplateList = new ArrayList(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //skip until row 5 if (row.getRowNum() > 4) { TestScriptTemplate tscripttemp = new TestScriptTemplate(); //Cell appcode = row.getCell(0); //this should be the item # on the list //System.out.println("application name #: "+itemnum.getStringCellValue()); tscripttemp.setAppCode(getCellValueStr(row.getCell(1))); tscripttemp.setAppURL(getCellValueStr(row.getCell(2))); tscripttemp.setAppUserID(getCellValueStr(row.getCell(3))); tscripttemp.setAppPassword(getCellValueStr(row.getCell(4))); tscripttemp.setHomePageTitle(getCellValueStr(row.getCell(5))); tscripttemp.setHomePageElement(getCellValueStr(row.getCell(6))); tscripttemp.setLevel1URL(getCellValueStr(row.getCell(7))); tscripttemp.setLevel1PageTitle(getCellValueStr(row.getCell(8))); tscripttemp.setLevel1Element(getCellValueStr(row.getCell(9))); //tscripttemp.setAppUserID(row.getCell(3).getStringCellValue()); //tscripttemp.setAppPassword(row.getCell(3).getStringCellValue()); TestScriptTemplateList.add(tscripttemp); System.out.println("this to string: " + tscripttemp.toString()); }/*from ww w . j av a 2s. c o m*/ } file.close(); }
From source file:ExcelConverter.java
public List<ScheduleClass> Converter() throws FileNotFoundException, IOException { ArrayList<ScheduleClass> scheduleList = new ArrayList<>(); FileInputStream fis = new FileInputStream(pathFile); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0);/*from w w w. ja va 2 s . c o m*/ Iterator<Row> rowIterator = sheet.iterator(); CellRangeAddress add; int colNoIdx = 0; ArrayList<String> dosen = new ArrayList<>(); ArrayList<Integer> idxDosen = new ArrayList<>(); ArrayList<Integer> colDosen = new ArrayList<>(); ArrayList<String> location = new ArrayList<>(); int idxNumber = 0; ArrayList<Integer> locationIdx = new ArrayList<>(); outerloop: for (int j = 0; j < sheet.getLastRowNum(); j++) { row = sheet.getRow(j); for (int f = 0; f < row.getLastCellNum(); f++) { Cell cell = row.getCell(j); if (cell.getStringCellValue().contains("No.")) { rowNoIdx = j; colNoIdx = cell.getColumnIndex(); break outerloop; } } } outerloop2: for (int i = 0; i < sheet.getLastRowNum(); i++) { row = sheet.getRow(i); outerloop: for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { i = sheet.getLastRowNum(); break outerloop2; } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 1)) { String delims = "[,. ]"; String[] sumary = cell.getStringCellValue().split(delims); for (int l = 0; l < sumary.length; l++) { if (sumary[l].equalsIgnoreCase("Mrt")) { sumary[l] = "3"; } } lc = LocalDate.of(Integer.parseInt(sumary[5]), Integer.parseInt(sumary[3]), Integer.parseInt(sumary[2])); } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 2)) { if (cell.getStringCellValue().equalsIgnoreCase("LIBUR")) { i = i + 1; break outerloop; } else { String delimsJam = "[-]"; String[] arrJam = cell.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } lt = LocalTime.parse(arrJam[0]); } } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 5)) { subject = cell.getStringCellValue(); } if (cell.getRowIndex() > rowNoIdx && cell.getColumnIndex() >= colNoIdx + 6 && cell.getColumnIndex() < row.getLastCellNum()) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add(splt2[l].trim()); location.add("Lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); if (!cell.getStringCellValue().isEmpty()) { dosen.add(cell.getStringCellValue().trim()); location.add(String.valueOf((int) c.getNumericCellValue()).trim()); } } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add("".trim()); location.add(""); } } else { if (!c.getStringCellValue().isEmpty()) { dosen.add(""); location.add(""); } } } } } for (int j = 0; j < dosen.size(); j++) { scheduleList .add(new ScheduleClass(lc, lt, lt.plusHours(2), subject, dosen.get(j), location.get(j))); } dosen.clear(); location.clear(); } return Mergering(scheduleList); }
From source file:Report_PRCR_New_ETF_Excel_File_Generator.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed try {//w w w .ja va 2s. c o m DatabaseManager dbm = DatabaseManager.getDbCon(); Date_Handler dt = new Date_Handler(); String year = yearfield.getText(); String month = dt.return_month_as_num(monthfield.getText()); String employee_detail_file_location; if (mv.SoftwareVersion() == 1) { employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "13", "location") + "/" + year + month + ".xls"; System.out.println(employee_detail_file_location); copyFileUsingApacheCommonsIO( new File(dbm.checknReturnData("file_locations", "id", "12", "location")), new File(employee_detail_file_location)); } else if (mv.SoftwareVersion() == 2) { employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "14", "location") + "/" + year + month + ".xls"; System.out.println(employee_detail_file_location); copyFileUsingApacheCommonsIO( new File(dbm.checknReturnData("file_locations", "id", "13", "location")), new File(employee_detail_file_location)); } else { employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "13", "location") + "/" + year + month + ".xls"; System.out.println(employee_detail_file_location); copyFileUsingApacheCommonsIO( new File(dbm.checknReturnData("file_locations", "id", "12", "location")), new File(employee_detail_file_location)); } InputStream inp = new FileInputStream(employee_detail_file_location); Workbook wb = WorkbookFactory.create(inp); org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); String payment_date_year_month_date = null; String table_name = "pr_workdata_" + year + "_" + month; String previous_table_name = null; if (Integer.parseInt(month) == 1) { previous_table_name = "pr_workdata_" + (Integer.parseInt(year) - 1) + "_" + 12; } else { if ((Integer.parseInt(month) - 1) < 10) { previous_table_name = "pr_workdata_" + year + "_0" + (Integer.parseInt(month) - 1); } else { previous_table_name = "pr_workdata_" + year + "_" + (Integer.parseInt(month) - 1); } } double employee_contribution_percentage = Math .round(Double.parseDouble( dbm.checknReturnData("prcr_new_epf_details", "name", "etf_rate", "value")) * 100.0) / 100.0; String nic = null; String surname = null; String initials = null; int member_no = 0; /*double tot_contribution = 0; double employers_contribution = 0;*/ double member_contribution = 0; double tot_earnings = 0; /*String member_status = null; String zone = null; int employer_number = 0; int contribution_period = 0; int data_submission_no = 0; double no_of_days_worked = 0; int occupation_classification_grade = 0; int payment_mode = 0; int payment_date = 0; String payment_reference = null; int d_o_code = 0; member_status = "E"; zone = dbm.checknReturnData("prcr_new_epf_details", "name", "zone", "value"); employer_number = Integer.parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "employer_number", "value")); contribution_period = Integer.parseInt(year + month); data_submission_no = 1; occupation_classification_grade = Integer.parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "occupation_classification_grade", "value")); int normal_days = 0; int sundays = 0; double ot_before = 0; double ot_after = 0; double hours_as_decimal = 0; int count = 0; double total_member_contribution = 0; int need_both_reports = 1; if (chk.isSelected()) { need_both_reports = 0; } else { need_both_reports = 1; } */ int count = 0; ResultSet query = dbm .query("SELECT * FROM `" + table_name + "` WHERE `register_or_casual` = 1 AND `total_pay` > 0"); while (query.next()) { ResultSet query1 = dbm .query("SELECT * FROM `personal_info` WHERE `code` = '" + query.getInt("code") + "' "); while (query1.next()) { nic = query1.getString("nic").replaceAll("\\s+", ""); surname = split_name(query1.getString("name"))[1]; initials = split_name(query1.getString("name"))[0]; member_no = Integer.parseInt(query1.getString("code")); //occupation_classification_grade = Integer.parseInt(query1.getString("occupation_grade")); //d_o_code = Integer.parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "d_o_code", "value")); tot_earnings = Math.round(query.getDouble("total_pay") * 100.0) / 100.0; //employers_contribution = Math.round(tot_earnings * employer_contribution_percentage * 100.0) / 100.0; //member_contribution = Math.round(tot_earnings * employee_contribution_percentage * 100.0) / 100.0; //tot_contribution = employers_contribution + member_contribution; //total_member_contribution = total_member_contribution + tot_contribution; member_contribution = Math.round(tot_earnings * employee_contribution_percentage * 100.0) / 100.0; //normal_days = query.getInt("normal_days"); //sundays = query.getInt("sundays"); /* ot_before = query.getDouble("ot_before_hours"); ot_after = query.getDouble("ot_after_hours"); if ((ot_before + ot_after) > 0) { hours_as_decimal = (ot_before + ot_after) / 100; } else { hours_as_decimal = 0; } if ((normal_days + sundays + hours_as_decimal) > 0) { no_of_days_worked = Math.round((normal_days + sundays + hours_as_decimal) * 100.0) / 100.0; } else { no_of_days_worked = 0; } */ // no_of_days_worked = normal_days + sundays; /* if (dbm.checkWhetherDataExists(previous_table_name, "code", query1.getString("code")) == 1) { member_status = "E"; } else { member_status = "N"; } */ Row row = sheet.getRow(1 + count); if (row == null) { row = sheet.createRow(1 + count); } for (int k = 0; k < 5; k++) { Cell cell = row.getCell(k); switch (k) { case 0: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(member_no); break; case 1: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(initials); break; case 2: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(surname); break; case 3: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(nic); break; case 4: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(member_contribution); break; default: break; } } count++; } query1.close(); } query.close(); FileOutputStream fileOut = new FileOutputStream(employee_detail_file_location); wb.write(fileOut); fileOut.close(); Desktop.getDesktop().open(new File(employee_detail_file_location)); } catch (Exception ex) { System.out.println(ex); msg.showMessage( "Problem Occured.Check whether the Excel file is alredy opened.Please close it and try again..", "Error", "error"); } }
From source file:ImporteerExcelsheet.java
public void importeerExcelsheet(String filePath) { try {// w w w .j ava 2 s . c om FileInputStream file = new FileInputStream(new File(filePath)); if (filePath.toLowerCase().indexOf(xlsx.toLowerCase()) != -1) { XSSFWorkbook wbXlsx = new XSSFWorkbook(file); sheet = wbXlsx.getSheetAt(0); Row row = sheet.getRow(3); Cell cell = row.getCell(1); try { // convert String dd-mm-yyyy naar Date yyyy-mm-dd String excelDatum1 = cell.getStringCellValue(); Date date = new SimpleDateFormat("dd-MM-yyyy").parse(excelDatum1); this.excelDatum = this.formatter.format(date); } catch (Exception e) { double date1 = cell.getNumericCellValue(); //convert excel double naar datum Date date2 = DateUtil.getJavaDate((double) date1); this.excelDatum = new SimpleDateFormat("yyyy-MM-dd").format(date2); } //Iterate through each rows one by one rowIterator = sheet.iterator(); } else { HSSFWorkbook wbXls = new HSSFWorkbook(file); sheet1 = wbXls.getSheetAt(0); Row row = sheet1.getRow(3); Cell cell = row.getCell(1); // convert String dd-mm-yyyy naar Date yyyy-mm-dd try { String excelDatum1 = cell.getStringCellValue(); Date date = new SimpleDateFormat("dd-MM-yyyy").parse(excelDatum1); this.excelDatum = this.formatter.format(date); } catch (Exception e) { double date1 = cell.getNumericCellValue(); //convert excel double naar datum Date date2 = DateUtil.getJavaDate((double) date1); this.excelDatum = new SimpleDateFormat("yyyy-MM-dd").format(date2); } //Iterate through each rows one by one rowIterator = sheet1.iterator(); } //Iterate through each rows one by one while (rowIterator.hasNext()) { Row row = rowIterator.next(); //skip first 5 rows if (row.getRowNum() == 0 || row.getRowNum() == 1 || row.getRowNum() == 2 || row.getRowNum() == 3 || row.getRowNum() == 4) { continue; } //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // System.out.print(cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_STRING: // System.out.print(cell.getStringCellValue() + "\t"); break; case Cell.CELL_TYPE_BLANK: // System.out.print(cell.getStringCellValue() + "\t"); break; case Cell.CELL_TYPE_FORMULA: // System.out.print(cell.getStringCellValue() + "\t"); break; } } // Tabel client int kaartnummer = (int) row.getCell(0).getNumericCellValue(); String naam = row.getCell(1).getStringCellValue(); String naamPartner = row.getCell(2).getStringCellValue(); String telefoonnummer = row.getCell(3).getStringCellValue(); String email = row.getCell(4).getStringCellValue(); String mobiel = row.getCell(5).getStringCellValue(); int aantalPersonen = (int) row.getCell(6).getNumericCellValue(); int aantalPersonenInDeNorm = (int) row.getCell(7).getNumericCellValue(); double gebruikInMaanden = (double) row.getCell(8).getNumericCellValue(); String idSoort = row.getCell(9).getStringCellValue(); //convert excel double naar datum double datumUitgifteId1 = row.getCell(10).getNumericCellValue(); if (datumUitgifteId1 == 0.0) { this.datumUitgifteId = null; } else { Date datumUitgifteId2 = DateUtil.getJavaDate((double) datumUitgifteId1); this.datumUitgifteId = new SimpleDateFormat("yyyy-MM-dd").format(datumUitgifteId2); } //////////////////////////////////////////////////////////////////////////////////// String idNummer = row.getCell(11).getStringCellValue(); String plaatsUitgifteId = row.getCell(12).getStringCellValue(); String adres = row.getCell(13).getStringCellValue(); String postcode = row.getCell(14).getStringCellValue(); String plaats = row.getCell(15).getStringCellValue(); String status = row.getCell(16).getStringCellValue(); // Tabel intake // kaartnummer gaat hier ook in de query String intaker = row.getCell(17).getStringCellValue(); double intakeDatum1 = row.getCell(18).getNumericCellValue(); //convert excel double naar datum Date intakeDatum2 = DateUtil.getJavaDate((double) intakeDatum1); String intakeDatum = new SimpleDateFormat("yyyy-MM-dd").format(intakeDatum2); ///////////////////////////////////////////////////////////////////////// double startDatumUitgifte1 = row.getCell(19).getNumericCellValue(); //convert excel double naar datum Date startDatumUitgifte2 = DateUtil.getJavaDate((double) startDatumUitgifte1); String startDatumUitgifte = new SimpleDateFormat("yyyy-MM-dd").format(startDatumUitgifte2); ////////////////////////////////////////////////////////////////////////////////// double datumHerintake1 = row.getCell(20).getNumericCellValue(); //convert excel double naar datum Date datumHerintake2 = DateUtil.getJavaDate((double) datumHerintake1); String datumHerintake = new SimpleDateFormat("yyyy-MM-dd").format(datumHerintake2); ///////////////////////////////////////////////////////////////////////////////// // Tabel Stopt double datumStopzetting1 = row.getCell(21).getNumericCellValue(); //convert excel numbers naar datum Date datumStopzetting2 = DateUtil.getJavaDate((double) datumStopzetting1); String datumStopzetting = new SimpleDateFormat("yyyy-MM-dd").format(datumStopzetting2); ////////////////////////////////////////////////////////////////////////////////////// String redenStopzetting = row.getCell(22).getStringCellValue(); // Tabel verwijzer String verwijzerNaam = row.getCell(23).getStringCellValue(); String verwijzersDoorContactpersoon1 = row.getCell(24).getStringCellValue(); String verwijzersDoorContactpersoon = verwijzersDoorContactpersoon1.toLowerCase(); String verwijzersDoorTelefoonnummer = row.getCell(25).getStringCellValue(); String verwijzersDoorEmail = row.getCell(26).getStringCellValue(); String verwijzersNaar = row.getCell(27).getStringCellValue(); String verwijzersNaarContactpersoon = row.getCell(28).getStringCellValue(); String verwijzersNaarTelefoonnummer = row.getCell(29).getStringCellValue(); String verwijzersNaarEmail = row.getCell(30).getStringCellValue(); // Uitgiftepunt gaat in de tabel intake String uitgiftepunt = row.getCell(31).getStringCellValue(); // PakketSoort gaat in de tabel client String pakketSoort = row.getCell(32).getStringCellValue(); // Query's om de excelsheet in de database te krijgen SQLExcelSheetInsert excelSQL = new SQLExcelSheetInsert(); SQLget getSQL = new SQLget(); // Losse query uit de tabel om bepaalde gegevens te krijgen om te controleren of die al bestaat of niet int Verwijzer = getSQL.getVerwijzernr(verwijzerNaam, verwijzersDoorContactpersoon); int checkKaartnr = getSQL.getCheckKaartnummer(kaartnummer); int checkUitgiftepunt = getSQL.getUitgiftepunt(uitgiftepunt); if (Verwijzer == 0) { excelSQL.insertExcelVerwijzer(verwijzerNaam, verwijzersDoorContactpersoon, verwijzersDoorTelefoonnummer, verwijzersDoorEmail, verwijzersNaar, verwijzersNaarContactpersoon, verwijzersNaarTelefoonnummer, verwijzersNaarEmail); } if (checkUitgiftepunt == 0) { int maxVolgorde = getSQL.getCheckVolgordeLijst(); excelSQL.insertUitgiftepunt(uitgiftepunt, maxVolgorde); } int Verwijzer2 = getSQL.getVerwijzernr(verwijzerNaam, verwijzersDoorContactpersoon); /// Wanneer er geen kaartnummer is die bekend gaat die door naar insert if (checkKaartnr == 0) { // Wanneer kaartnummer alles behalve 0 is insert die if (kaartnummer > 0) { excelSQL.insertExcelClient(kaartnummer, naam, naamPartner, telefoonnummer, email, mobiel, aantalPersonen, aantalPersonenInDeNorm, gebruikInMaanden, idSoort, this.datumUitgifteId, idNummer, plaatsUitgifteId, adres, postcode, plaats, status, pakketSoort, Verwijzer2); } else { break; } } else { excelSQL.updateExcelClient(kaartnummer, naam, naamPartner, telefoonnummer, email, mobiel, aantalPersonen, aantalPersonenInDeNorm, gebruikInMaanden, idSoort, this.datumUitgifteId, idNummer, plaatsUitgifteId, adres, postcode, plaats, status, pakketSoort, Verwijzer2); } int checkIntake = getSQL.getCheckIntake(intakeDatum, startDatumUitgifte, datumHerintake, kaartnummer); if (checkIntake == 0) { excelSQL.insertExcelIntake(intaker, intakeDatum, startDatumUitgifte, datumHerintake, kaartnummer, uitgiftepunt); int intakeId = getSQL.getIntakeId(kaartnummer); excelSQL.insertExcelStopt(datumStopzetting, redenStopzetting, intakeId); } // Kan upgedate worden aan een knop voor een query aan status in de table voedselpakket wel of niet opgehaald String status1 = null; String checkStatus = getSQL.getStatus(kaartnummer); this.pakketAantal = getSQL.getPakketAantal(kaartnummer); int intakeId = getSQL.getIntakeId(kaartnummer); int checkPakket = getSQL.getPakket(this.excelDatum, intakeId); if (checkPakket == 0) { if (checkStatus != null) { if (checkStatus.equals("Actief")) { int intakeId2 = getSQL.getIntakeId(kaartnummer); excelSQL.insertVoedselpakket(this.excelDatum, this.pakketAantal, status1, intakeId2, uitgiftepunt); } } } } file.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:Report_PRCR_New_EPF_Excel_File_Generator.java
private void view1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_view1ActionPerformed try {/*from w ww.j a v a2 s . co m*/ DatabaseManager dbm = DatabaseManager.getDbCon(); Date_Handler dt = new Date_Handler(); String year = yearfield.getText(); String month = dt.return_month_as_num(monthfield.getText()); String employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "7", "location") + "/" + year + month + ".xls"; System.out.println(employee_detail_file_location); copyFileUsingApacheCommonsIO(new File(dbm.checknReturnData("file_locations", "id", "6", "location")), new File(employee_detail_file_location)); InputStream inp = new FileInputStream(employee_detail_file_location); Workbook wb = WorkbookFactory.create(inp); org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); String payment_date_year_month_date = null; String table_name = "pr_workdata_" + year + "_" + month; String epf_backup_year_month = year + "_" + month; String previous_table_name = null; if (Integer.parseInt(month) == 1) { previous_table_name = "pr_workdata_" + (Integer.parseInt(year) - 1) + "_" + 12; } else { if ((Integer.parseInt(month) - 1) < 10) { previous_table_name = "pr_workdata_" + year + "_0" + (Integer.parseInt(month) - 1); } else { previous_table_name = "pr_workdata_" + year + "_" + (Integer.parseInt(month) - 1); } } double employee_contribution_percentage = Math.round(Double.parseDouble( dbm.checknReturnData("prcr_new_epf_details", "name", "employee_contribution", "value")) * 100.0) / 100.0; double employer_contribution_percentage = Math.round(Double.parseDouble( dbm.checknReturnData("prcr_new_epf_details", "name", "employer_contribution", "value")) * 100.0) / 100.0; String nic = null; String surname = null; String initials = null; int member_no = 0; double tot_contribution = 0; double employers_contribution = 0; double member_contribution = 0; double tot_earnings = 0; String member_status = null; String zone = null; int employer_number = 0; int contribution_period = 0; int data_submission_no = 0; double no_of_days_worked = 0; int occupation_classification_grade = 0; int payment_mode = 0; int payment_date = 0; String payment_reference = null; int d_o_code = 0; member_status = "E"; zone = dbm.checknReturnData("prcr_new_epf_details", "name", "zone", "value"); employer_number = Integer .parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "employer_number", "value")); contribution_period = Integer.parseInt(year + month); data_submission_no = 1; occupation_classification_grade = Integer.parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "occupation_classification_grade", "value")); int normal_days = 0; int sundays = 0; double ot_before = 0; double ot_after = 0; double hours_as_decimal = 0; int count = 0; double total_member_contribution = 0; int need_both_reports = 1; if (chk.isSelected()) { need_both_reports = 0; } else { need_both_reports = 1; } ResultSet query = dbm .query("SELECT * FROM `" + table_name + "` WHERE `register_or_casual` = 1 AND `total_pay` > 0"); while (query.next()) { ResultSet query1 = dbm .query("SELECT * FROM `personal_info` WHERE `code` = '" + query.getInt("code") + "' "); while (query1.next()) { nic = query1.getString("nic").replaceAll("\\s+", ""); surname = split_name(query1.getString("name"))[1]; initials = split_name(query1.getString("name"))[0]; member_no = Integer.parseInt(query1.getString("code")); occupation_classification_grade = Integer.parseInt(query1.getString("occupation_grade")); tot_earnings = Math.round(query.getDouble("total_pay") * 100.0) / 100.0; if (dbm.checkWhetherDataExistsTwoColumns("prcr_epf_etf_backup", "month", epf_backup_year_month, "code", member_no) == 1) { tot_earnings = tot_earnings + Double.parseDouble(dbm.checknReturnDatafor2checks("prcr_epf_etf_backup", "month", epf_backup_year_month, "code", member_no, "total_pay")); } employers_contribution = Math.round(tot_earnings * employer_contribution_percentage * 100.0) / 100.0; member_contribution = Math.round(tot_earnings * employee_contribution_percentage * 100.0) / 100.0; tot_contribution = employers_contribution + member_contribution; total_member_contribution = total_member_contribution + tot_contribution; normal_days = query.getInt("normal_days"); sundays = query.getInt("sundays"); ot_before = query.getDouble("ot_before_hours"); ot_after = query.getDouble("ot_after_hours"); if ((ot_before + ot_after) > 0) { hours_as_decimal = (ot_before + ot_after) / 100; } else { hours_as_decimal = 0; } if ((normal_days + sundays + hours_as_decimal) > 0) { no_of_days_worked = Math.round((normal_days + sundays + hours_as_decimal) * 100.0) / 100.0; } else { no_of_days_worked = 0; } if (dbm.checkWhetherDataExists(previous_table_name, "code", query1.getString("code")) == 1) { member_status = "E"; } else { member_status = "N"; } Row row = sheet.getRow(4 + count); if (row == null) { row = sheet.createRow(4 + count); } for (int k = 0; k < 15; k++) { Cell cell = row.getCell(k); switch (k) { case 0: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(nic); break; case 1: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(surname); break; case 2: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(initials); break; case 3: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(member_no); break; case 4: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(tot_contribution); break; case 5: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(employers_contribution); break; case 6: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(member_contribution); break; case 7: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(tot_earnings); break; case 8: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(member_status); break; case 9: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(zone); break; case 10: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(employer_number); break; case 11: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(contribution_period); break; case 12: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(data_submission_no); break; case 13: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(no_of_days_worked); break; case 14: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(occupation_classification_grade); break; default: break; } } count++; } query1.close(); } query.close(); FileOutputStream fileOut = new FileOutputStream(employee_detail_file_location); wb.write(fileOut); fileOut.close(); Desktop.getDesktop().open(new File(employee_detail_file_location)); if (need_both_reports == 1) { if (Integer.parseInt(dayfield.getText()) < 10) { payment_date_year_month_date = yearfield1.getText() + dt.return_month_as_num(monthfield1.getText()) + "0" + dayfield.getText(); } else { payment_date_year_month_date = yearfield1.getText() + dt.return_month_as_num(monthfield1.getText()) + dayfield.getText(); } payment_date = Integer.parseInt(payment_date_year_month_date); payment_mode = payment_mode_combo.getSelectedIndex() + 1; payment_reference = payment_referrence_textFiield.getText(); d_o_code = Integer .parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "d_o_code", "value")); String total_contribution_file_location = dbm.checknReturnData("file_locations", "id", "9", "location") + "/" + year + month + "_total_contribution.xls"; copyFileUsingApacheCommonsIO( new File(dbm.checknReturnData("file_locations", "id", "8", "location")), new File(total_contribution_file_location)); InputStream inp2 = new FileInputStream(total_contribution_file_location); Workbook wb2 = WorkbookFactory.create(inp2); org.apache.poi.ss.usermodel.Sheet sheet2 = wb2.getSheetAt(0); Row row = sheet2.getRow(17); if (row == null) { row = sheet.createRow(17); } for (int k = 0; k < 10; k++) { Cell cell = row.getCell(k); switch (k) { case 0: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(zone); break; case 1: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(employer_number); break; case 2: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(contribution_period); break; case 3: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(data_submission_no); break; case 4: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(total_member_contribution); break; case 5: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(count); break; case 6: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(payment_mode); break; case 7: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(payment_reference); break; case 8: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(payment_date); break; case 9: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(d_o_code); break; default: break; } } FileOutputStream fileOut2 = new FileOutputStream(total_contribution_file_location); wb2.write(fileOut2); fileOut2.close(); Desktop.getDesktop().open(new File(total_contribution_file_location)); } } catch (Exception ex) { System.out.println(ex); msg.showMessage( "Problem Occured.Check whether the Excel file is alredy opened.Please close it and try again..", "Error", "error"); } }
From source file:Report_PRCR_New_EPF_Excel_File_Generator.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed try {//from w w w. jav a2 s.c o m DatabaseManager dbm = DatabaseManager.getDbCon(); Date_Handler dt = new Date_Handler(); String year = yearfield.getText(); String month = dt.return_month_as_num(monthfield.getText()); String employee_detail_file_location; if (mv.SoftwareVersion() == 1) { employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "11", "location") + "/" + year + month + ".xls"; System.out.println(employee_detail_file_location); copyFileUsingApacheCommonsIO( new File(dbm.checknReturnData("file_locations", "id", "10", "location")), new File(employee_detail_file_location)); } else if (mv.SoftwareVersion() == 2) { employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "12", "location") + "/" + year + month + ".xls"; System.out.println(employee_detail_file_location); copyFileUsingApacheCommonsIO( new File(dbm.checknReturnData("file_locations", "id", "11", "location")), new File(employee_detail_file_location)); } else { employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "11", "location") + "/" + year + month + ".xls"; System.out.println(employee_detail_file_location); copyFileUsingApacheCommonsIO( new File(dbm.checknReturnData("file_locations", "id", "10", "location")), new File(employee_detail_file_location)); } InputStream inp = new FileInputStream(employee_detail_file_location); Workbook wb = WorkbookFactory.create(inp); org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); String payment_date_year_month_date = null; String table_name = "pr_workdata_" + year + "_" + month; String epf_backup_year_month = year + "_" + month; String previous_table_name = null; if (Integer.parseInt(month) == 1) { previous_table_name = "pr_workdata_" + (Integer.parseInt(year) - 1) + "_" + 12; } else { if ((Integer.parseInt(month) - 1) < 10) { previous_table_name = "pr_workdata_" + year + "_0" + (Integer.parseInt(month) - 1); } else { previous_table_name = "pr_workdata_" + year + "_" + (Integer.parseInt(month) - 1); } } double employee_contribution_percentage = Math.round(Double.parseDouble( dbm.checknReturnData("prcr_new_epf_details", "name", "employee_contribution", "value")) * 100.0) / 100.0; double employer_contribution_percentage = Math.round(Double.parseDouble( dbm.checknReturnData("prcr_new_epf_details", "name", "employer_contribution", "value")) * 100.0) / 100.0; String nic = null; String surname = null; String initials = null; int member_no = 0; double tot_contribution = 0; double employers_contribution = 0; double member_contribution = 0; double tot_earnings = 0; String member_status = null; String zone = null; int employer_number = 0; int contribution_period = 0; int data_submission_no = 0; double no_of_days_worked = 0; int occupation_classification_grade = 0; int payment_mode = 0; int payment_date = 0; String payment_reference = null; int d_o_code = 0; member_status = "E"; zone = dbm.checknReturnData("prcr_new_epf_details", "name", "zone", "value"); employer_number = Integer .parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "employer_number", "value")); contribution_period = Integer.parseInt(year + month); data_submission_no = 1; occupation_classification_grade = Integer.parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "occupation_classification_grade", "value")); int normal_days = 0; int sundays = 0; double ot_before = 0; double ot_after = 0; double hours_as_decimal = 0; int count = 0; double total_member_contribution = 0; int need_both_reports = 1; if (chk.isSelected()) { need_both_reports = 0; } else { need_both_reports = 1; } ResultSet query = dbm.query("SELECT * FROM `" + table_name + "` WHERE `register_or_casual` = 1 "); // AND `total_pay` > 0"); while (query.next()) { if (query.getDouble("total_pay") <= 0 && dbm.checkWhetherDataExistsTwoColumns("prcr_epf_etf_backup", "month", epf_backup_year_month, "code", query.getInt("code")) != 1) { continue; } ResultSet query1 = dbm .query("SELECT * FROM `personal_info` WHERE `code` = '" + query.getInt("code") + "' "); while (query1.next()) { nic = query1.getString("nic").replaceAll("\\s+", ""); surname = split_name(query1.getString("name"))[1]; initials = split_name(query1.getString("name"))[0]; member_no = Integer.parseInt(query1.getString("code")); occupation_classification_grade = Integer.parseInt(query1.getString("occupation_grade")); d_o_code = Integer .parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "d_o_code", "value")); tot_earnings = Math.round(query.getDouble("total_pay") * 100.0) / 100.0; if (dbm.checkWhetherDataExistsTwoColumns("prcr_epf_etf_backup", "month", epf_backup_year_month, "code", member_no) == 1) { tot_earnings = tot_earnings + Double.parseDouble(dbm.checknReturnDatafor2checks("prcr_epf_etf_backup", "month", epf_backup_year_month, "code", member_no, "total_pay")); } employers_contribution = Math.round(tot_earnings * employer_contribution_percentage * 100.0) / 100.0; member_contribution = Math.round(tot_earnings * employee_contribution_percentage * 100.0) / 100.0; tot_contribution = employers_contribution + member_contribution; total_member_contribution = total_member_contribution + tot_contribution; normal_days = query.getInt("normal_days"); sundays = query.getInt("sundays"); /* ot_before = query.getDouble("ot_before_hours"); ot_after = query.getDouble("ot_after_hours"); if ((ot_before + ot_after) > 0) { hours_as_decimal = (ot_before + ot_after) / 100; } else { hours_as_decimal = 0; } if ((normal_days + sundays + hours_as_decimal) > 0) { no_of_days_worked = Math.round((normal_days + sundays + hours_as_decimal) * 100.0) / 100.0; } else { no_of_days_worked = 0; } */ no_of_days_worked = normal_days + sundays; if (dbm.checkWhetherDataExists(previous_table_name, "code", query1.getString("code")) == 1) { member_status = "E"; } else { member_status = "N"; } Row row = sheet.getRow(1 + count); if (row == null) { row = sheet.createRow(1 + count); } for (int k = 0; k < 16; k++) { Cell cell = row.getCell(k); switch (k) { case 0: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(nic); break; case 1: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(surname); break; case 2: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(initials); break; case 3: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(member_no); break; case 4: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(tot_contribution); break; case 5: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(employers_contribution); break; case 6: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(member_contribution); break; case 7: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(tot_earnings); break; case 8: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(member_status); break; case 9: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(zone); break; case 10: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(employer_number); break; case 11: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(contribution_period); break; case 12: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(data_submission_no); break; case 13: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(no_of_days_worked); break; case 14: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(occupation_classification_grade); break; case 15: cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(d_o_code); break; default: break; } } count++; } query1.close(); } query.close(); FileOutputStream fileOut = new FileOutputStream(employee_detail_file_location); wb.write(fileOut); fileOut.close(); Desktop.getDesktop().open(new File(employee_detail_file_location)); } catch (Exception ex) { System.out.println(ex); msg.showMessage( "Problem Occured.Check whether the Excel file is alredy opened.Please close it and try again..", "Error", "error"); } }
From source file:StateContentTest.java
/** * Reads the first sheet of the specified Excel spreadsheet into a Hashtable, * where the value in the first column of each row is the Key and the following columns are added to a String[] of size columns * @param filename The name of the file to open including extension. * @param rows The number of rows to read. * @param columns The number of columns to read. * @param rowStart The row to start reading from. * @param colStart The column to start reading from. * @throws IOException//from ww w .j a v a 2 s .com */ private void readExcel(String filename, int rows, int columns, int rowStart, int colStart) throws IOException { FileInputStream inputStream = new FileInputStream(new File("./data/" + filename)); Workbook workbook = new XSSFWorkbook(inputStream); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); iterator.next(); iterator.next(); for (int i = rowStart; i < rows; i++) { Row row = firstSheet.getRow(i); String[] copy = new String[columns]; String stateName = ""; for (int j = colStart; j < columns - 1; j++) { if (j == colStart) stateName = row.getCell(j).getStringCellValue(); else copy[j] = row.getCell(j).getStringCellValue().replaceAll("\\u2022", "") .replaceAll("(?m)^[ \t]*\r?\n", "").trim(); } copyMatrix.put(stateName, copy); } workbook.close(); inputStream.close(); }
From source file:ExampleClass.java
public static void main(String[] args) throws Exception { File src = new File( "C:\\Users\\Ariq\\Documents\\NetBeansProjects\\Skripsi-Jadwal-Mengawas-Ujian\\Contoh File\\Jadwal_Pengawas_ Ujian_Pak_ Pascal.xlsx"); //File src = new File("D:\\\\Skripsi\\\\Data Baru\\\\Daftar Dosen.xlsx"); FileInputStream fis = new FileInputStream(src); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet1 = wb.getSheetAt(0); // Iterator< Row> rowIterator = sheet1.iterator(); int colIndex = 0; int ex = 0;/*from w w w . ja v a 2s . com*/ int lastCol = sheet1.getLastRowNum(); int i = 0; int idx = 0; CellRangeAddress add; // while (rowIterator.hasNext()) { // row = (XSSFRow) rowIterator.next(); // Iterator< Cell> cellIterator = row.cellIterator(); // //System.out.println("i = "+i+", ex:"+ex); // // if (row.getRowNum() > 53) { // break; // } //// if(lastCol-(ex+1) == i) break; // while (cellIterator.hasNext()) { // Cell cell = cellIterator.next(); // for (int f = 0; f < sheet1.getNumMergedRegions(); f++) { // add = sheet1.getMergedRegion(f); // // int col = add.getFirstColumn(); // int rowNum = add.getFirstRow(); // if (rowNum != 0 && rowNum == cell.getRowIndex() && colIndex == cell.getColumnIndex()) { // System.out.println("col:"+col+" "+",row :"+rowNum); // String b = String.valueOf(sheet1.getRow(rowNum).getCell(col)); // System.out.println(b); // // } // // } // switch (cell.getCellType()) // { // case Cell.CELL_TYPE_FORMULA: // ex++; // switch (cell.getCachedFormulaResultType()) // { // case Cell.CELL_TYPE_NUMERIC: // i = (int)cell.getNumericCellValue(); // System.out.print( // (int)cell.getNumericCellValue() + " \t\t " ); // // // break; // } // break; // case Cell.CELL_TYPE_NUMERIC: // if (cell.getColumnIndex() >= 6) // { // System.out.print( // (int)cell.getNumericCellValue() + " \t\t " ); // } // break; // case Cell.CELL_TYPE_STRING: // add = sheet1.getMergedRegion(cell.getRowIndex()); // // if (cell.getStringCellValue().contentEquals("No.")) // { // colIndex = cell.getColumnIndex(); // } // if (cell.getColumnIndex() == 1) // { // System.out.print( // cell.getStringCellValue() + " \t\t " ); // } // break; // // } // } // // System.out.println(); // } for (int j = 0; j < sheet1.getLastRowNum(); j++) { row = sheet1.getRow(j); for (int k = 0; k < row.getLastCellNum(); k++) { Cell cell = row.getCell(k); // if (cell.getColumnIndex() == 1) // { // System.out.println(cell.getStringCellValue()); // } FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == 0 && j > 3 && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { System.exit(k); } if (cell.getColumnIndex() >= 6 && cell.getColumnIndex() <= 11) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.print((int) cell.getNumericCellValue() + " "); } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { System.out.println(splt2[l] + "= lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet1.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); System.out.print( cell.getStringCellValue() + " Ruang =" + (int) c.getNumericCellValue() + " "); } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet1.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet1.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { System.out.println(splt2[l] + "= lab"); } } else { System.out.print( c.getStringCellValue() + " Ruang = " + (int) c2.getNumericCellValue() + " "); } } } } System.out.println(""); } System.out.println(colIndex); System.out.println(idx); fis.close(); }
From source file:CatalogMain.java
License:BSD License
public String getCatalogueCellA1() { if (workbook.getSheet("Layers") != null) { Sheet layersSheet = workbook.getSheet("Layers"); Row firstRow = layersSheet.getRow(0); return firstRow.getCell(0).toString(); }//from w w w. j a va2 s. c om return null; }
From source file:CatalogMain.java
License:BSD License
public void createClassObject() { storeClassObjects.clear();/* w w w .java 2s . c o m*/ Sheet layersSheet = workbook.getSheet("Layers"); // Adds all rows as class objects for (int rowIndex = 4; rowIndex <= layersSheet.getLastRowNum(); rowIndex++) { Row row = layersSheet.getRow(rowIndex); if ((row.getCell(1) != null && row.getCell(1).getCellType() != Cell.CELL_TYPE_BLANK) && (row.getCell(11) != null && row.getCell(11).getCellType() != Cell.CELL_TYPE_BLANK)) { classObjects = new LayersClassObject(row.getCell(0).toString(), row.getCell(1).toString(), row.getCell(2).toString(), String.valueOf(rowIndex + 1), Double.parseDouble(row.getCell(11).toString())); } else if ((row.getCell(1) != null && row.getCell(1).getCellType() != Cell.CELL_TYPE_BLANK) && (row.getCell(11) == null || row.getCell(11).getCellType() == Cell.CELL_TYPE_BLANK)) { classObjects = new LayersClassObject(row.getCell(0).toString(), row.getCell(1).toString(), row.getCell(2).toString(), String.valueOf(rowIndex + 1), 1); } else if ((row.getCell(1) == null || row.getCell(1).getCellType() == Cell.CELL_TYPE_BLANK) && (row.getCell(11) != null && row.getCell(11).getCellType() != Cell.CELL_TYPE_BLANK)) { classObjects = new LayersClassObject(row.getCell(0).toString(), null, row.getCell(2).toString(), String.valueOf(rowIndex + 1), Double.parseDouble(row.getCell(11).toString())); } else { classObjects = new LayersClassObject(row.getCell(0).toString(), null, row.getCell(2).toString(), String.valueOf(rowIndex + 1), 1); } storeClassObjects.add(classObjects); } // Sets true to objects which class has duplicates if (storeClassObjects.isEmpty() == false) { for (int count = 0; count < storeClassObjects.size(); count++) { String tempString = storeClassObjects.get(count).getClassName(); for (int count1 = count + 1; count1 < storeClassObjects.size(); count1++) { if (storeClassObjects.get(count1).getClassName().equalsIgnoreCase(tempString)) { storeClassObjects.get(count).setHaveSame(true); storeClassObjects.get(count1).setHaveSame(true); } } } } }