List of usage examples for org.apache.poi.ss.usermodel Sheet getRow
Row getRow(int rownum);
From source file:Report_PRCR_New_ETF_Excel_File_Generator.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed try {//from w w w. j av 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", "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:Report_PRCR_New_EPF_Excel_File_Generator.java
private void view1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_view1ActionPerformed try {/* w w w.j a va 2s .c om*/ 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 {// w w w . j a v a 2 s. c om 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 w w w. j a va2s. co m */ 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: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(); }//w w w. ja v a2 s . c o m return null; }
From source file:CatalogMain.java
License:BSD License
public void createClassObject() { storeClassObjects.clear();// w ww. j ava 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); } } } } }
From source file:adams.data.io.input.ExcelSpreadSheetReader.java
License:Open Source License
/** * Reads the spreadsheet content from the specified file. * * @param in the input stream to read from * @return the spreadsheets or null in case of an error *//*from w w w. j a v a 2 s.c om*/ @Override protected List<SpreadSheet> doReadRange(InputStream in) { List<SpreadSheet> result; int[] indices; Workbook workbook; Sheet sheet; SpreadSheet spsheet; Row exRow; Cell exCell; adams.data.spreadsheet.Row spRow; int i; int n; int cellType; DateFormat dformat; boolean numeric; int dataRowStart; int firstRow; int lastRow; List<String> header; result = new ArrayList<>(); workbook = null; dformat = DateUtils.getTimestampFormatter(); try { workbook = WorkbookFactory.create(in); m_SheetRange.setMax(workbook.getNumberOfSheets()); indices = m_SheetRange.getIntIndices(); firstRow = m_FirstRow - 1; dataRowStart = getNoHeader() ? firstRow : firstRow + 1; for (int index : indices) { if (m_Stopped) break; spsheet = m_SpreadSheetType.newInstance(); spsheet.setDataRowClass(m_DataRowType.getClass()); result.add(spsheet); if (isLoggingEnabled()) getLogger().info("sheet: " + (index + 1)); sheet = workbook.getSheetAt(index); if (sheet.getLastRowNum() == 0) { getLogger().severe("No rows in sheet #" + index); return null; } spsheet.setName(sheet.getSheetName()); // header if (isLoggingEnabled()) getLogger().info("header row"); exRow = sheet.getRow(firstRow); if (exRow == null) { getLogger().warning("No data in sheet #" + (index + 1) + "?"); } else if (exRow != null) { spRow = spsheet.getHeaderRow(); m_TextColumns.setMax(exRow.getLastCellNum()); if (getNoHeader()) { header = SpreadSheetUtils.createHeader(exRow.getLastCellNum(), m_CustomColumnHeaders); for (i = 0; i < header.size(); i++) spRow.addCell("" + (i + 1)).setContent(header.get(i)); } else { if (!m_CustomColumnHeaders.trim().isEmpty()) { header = SpreadSheetUtils.createHeader(exRow.getLastCellNum(), m_CustomColumnHeaders); for (i = 0; i < header.size(); i++) spRow.addCell("" + (i + 1)).setContent(header.get(i)); } else { for (i = 0; i < exRow.getLastCellNum(); i++) { if (m_Stopped) break; exCell = exRow.getCell(i); if (exCell == null) { spRow.addCell("" + (i + 1)).setMissing(); continue; } numeric = !m_TextColumns.isInRange(i); switch (exCell.getCellType()) { case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: spRow.addCell("" + (i + 1)).setContent("column-" + (i + 1)); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(exCell)) spRow.addCell("" + (i + 1)).setContent(new DateTime( HSSFDateUtil.getJavaDate(exCell.getNumericCellValue()))); else if (numeric) spRow.addCell("" + (i + 1)).setContent(exCell.getNumericCellValue()); else spRow.addCell("" + (i + 1)).setContentAsString(numericToString(exCell)); break; default: spRow.addCell("" + (i + 1)).setContentAsString(exCell.getStringCellValue()); } } } } } // data if (spsheet.getColumnCount() > 0) { if (m_NumRows < 1) lastRow = sheet.getLastRowNum(); else lastRow = Math.min(firstRow + m_NumRows - 1, sheet.getLastRowNum()); for (i = dataRowStart; i <= lastRow; i++) { if (m_Stopped) break; if (isLoggingEnabled()) getLogger().info("data row: " + (i + 1)); spRow = spsheet.addRow("" + spsheet.getRowCount()); exRow = sheet.getRow(i); if (exRow == null) continue; for (n = 0; n < exRow.getLastCellNum(); n++) { // too few columns in header? if ((n >= spsheet.getHeaderRow().getCellCount()) && m_AutoExtendHeader) spsheet.insertColumn(spsheet.getColumnCount(), ""); m_TextColumns.setMax(spsheet.getHeaderRow().getCellCount()); exCell = exRow.getCell(n); if (exCell == null) { spRow.addCell(n).setMissing(); continue; } cellType = exCell.getCellType(); if (cellType == Cell.CELL_TYPE_FORMULA) cellType = exCell.getCachedFormulaResultType(); numeric = !m_TextColumns.isInRange(n); switch (cellType) { case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: if (m_MissingValue.isEmpty()) spRow.addCell(n).setMissing(); else spRow.addCell(n).setContent(""); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(exCell)) spRow.addCell(n).setContent( dformat.format(HSSFDateUtil.getJavaDate(exCell.getNumericCellValue()))); else if (numeric) spRow.addCell(n).setContent(exCell.getNumericCellValue()); else spRow.addCell(n).setContentAsString(numericToString(exCell)); break; default: if (m_MissingValue.isMatch(exCell.getStringCellValue())) spRow.addCell(n).setMissing(); else spRow.addCell(n).setContentAsString(exCell.getStringCellValue()); } } } } } } catch (Exception ioe) { getLogger().log(Level.SEVERE, "Failed to read range '" + m_SheetRange + "':", ioe); result = null; m_LastError = "Failed to read range '" + m_SheetRange + "' from stream!\n" + Utils.throwableToString(ioe); } return result; }
From source file:ar.edu.unrc.gametictactoe.performanceandtraining.configurations.StatisticExperiment.java
License:Open Source License
/** * * @param filePath/*from w w w . j a va 2s . c om*/ * @param backupFiles * @param resultsPerFile * @param resultsRandom * @param randomPerceptronFile <p> * @throws IOException * @throws InvalidFormatException */ public void exportToExcel(String filePath, List<File> backupFiles, Map<File, StatisticForCalc> resultsPerFile, Map<File, StatisticForCalc> resultsRandom, File randomPerceptronFile) throws IOException, InvalidFormatException { InputStream inputXLSX = this.getClass() .getResourceAsStream("/ar/edu/unrc/gametictactoe/resources/EstadisticasTicTacToe.xlsx"); Workbook wb = WorkbookFactory.create(inputXLSX); try (FileOutputStream outputXLSX = new FileOutputStream( filePath + "_" + dateFormater.format(dateForFileName) + "_STATISTICS" + ".xlsx")) { //============= imptimimos en la hoja de % Of Games Won =================== Sheet sheet = wb.getSheetAt(0); //Estilo par los titulos de las tablas int rowStartTitle = 0; int colStartTitle = 2; int rowStart = 1; int colStart = 3; Row rowPlayer1; Row rowPlayer2; Row rowDraw; // Luego creamos el objeto que se encargar de aplicar el estilo a la celda Font fontCellTitle = wb.createFont(); fontCellTitle.setFontHeightInPoints((short) 10); fontCellTitle.setFontName("Arial"); fontCellTitle.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle CellStyleTitle = wb.createCellStyle(); CellStyleTitle.setWrapText(true); CellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER); CellStyleTitle.setVerticalAlignment(CellStyle.VERTICAL_TOP); CellStyleTitle.setFont(fontCellTitle); // Establecemos el tipo de sombreado de nuestra celda CellStyleTitle.setFillBackgroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); CellStyleTitle.setFillPattern(CellStyle.SOLID_FOREGROUND); loadTitle(rowStartTitle, colStartTitle, sheet, backupFiles.size(), CellStyleTitle); //estilo titulo finalizado //Estilo de celdas con los valores de las estadisticas CellStyle cellStyle = wb.createCellStyle(); cellStyle.setWrapText(true); /* We are now ready to set borders for this style */ /* Draw a thin left border */ cellStyle.setBorderLeft(CellStyle.BORDER_THIN); /* Add medium right border */ cellStyle.setBorderRight(CellStyle.BORDER_THIN); /* Add dashed top border */ cellStyle.setBorderTop(CellStyle.BORDER_THIN); /* Add dotted bottom border */ cellStyle.setBorderBottom(CellStyle.BORDER_THIN); //estilo celdas finalizado //loadTitle(rowStartTitle, colStartTitle, sheet, backupFiles.size(), CellStyleTitle); rowPlayer1 = sheet.getRow(rowStart); rowPlayer2 = sheet.getRow(rowStart + 1); rowDraw = sheet.getRow(rowStart + 2); for (int file = 0; file < backupFiles.size(); file++) { Cell cellPlayer1 = rowPlayer1.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC); Cell cellPlayer2 = rowPlayer2.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC); Cell cellDraw = rowDraw.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC); cellPlayer1.setCellStyle(cellStyle); cellPlayer2.setCellStyle(cellStyle); cellDraw.setCellStyle(cellStyle); Double cellValuePlayer1 = resultsPerFile.get(backupFiles.get(file)).getWinRatePlayer1(); Double cellValuePlayer2 = resultsPerFile.get(backupFiles.get(file)).getWinRatePlayer2(); Double cellValueDraw = resultsPerFile.get(backupFiles.get(file)).getDrawRate(); assert cellValuePlayer1 <= 100 && cellValuePlayer1 >= 0; assert cellValuePlayer2 <= 100 && cellValuePlayer2 >= 0; assert cellValueDraw <= 100 && cellValueDraw >= 0; //assert cellValueDraw + cellValuePlayer1 + cellValuePlayer2 == 100; cellDraw.setCellValue(cellValueDraw); cellPlayer1.setCellValue(cellValuePlayer1); cellPlayer2.setCellValue(cellValuePlayer2); } if (!resultsRandom.isEmpty()) { int file = 0;//hay que ir a buscar el randomfile Cell cellDraw = rowDraw.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC); Cell cellPlayer1 = rowPlayer1.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC); Cell cellPlayer2 = rowPlayer2.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC); cellDraw.setCellStyle(cellStyle); cellPlayer1.setCellStyle(cellStyle); cellPlayer2.setCellStyle(cellStyle); // StatisticForCalc get = resultsRandom.get(randomPerceptronFile); // Double cellValuePlayer1 = get.getWinRatePlayer1(); Double cellValuePlayer1 = resultsRandom.get(randomPerceptronFile).getWinRatePlayer1(); Double cellValuePlayer2 = resultsRandom.get(randomPerceptronFile).getWinRatePlayer2(); Double cellValueDraw = resultsRandom.get(randomPerceptronFile).getDrawRate(); //assert cellValueDraw + cellValuePlayer1 + cellValuePlayer2 == 100; cellPlayer1.setCellValue(cellValuePlayer1); cellPlayer2.setCellValue(cellValuePlayer2); cellDraw.setCellValue(cellValueDraw); } wb.write(outputXLSX); } }
From source file:ar.edu.unrc.gametictactoe.performanceandtraining.configurations.StatisticExperiment.java
License:Open Source License
/** * * @param rowStartTitle/* w w w. j a va2 s.co m*/ * @param colStartTitle * @param sheet * @param backupFilesSize * @param CellStyleTitle */ public void loadTitle(int rowStartTitle, int colStartTitle, Sheet sheet, int backupFilesSize, CellStyle CellStyleTitle) { int total_juegos = saveBackupEvery; Row row1 = sheet.getRow(rowStartTitle); for (int file = 1; file <= backupFilesSize; file++) { Cell cell = row1.createCell(file + colStartTitle, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(CellStyleTitle); Integer value = total_juegos * file; String valueStr = value.toString(); String cellV = valueStr; if (valueStr.length() > 3) { cellV = valueStr.substring(0, valueStr.length() - 3) + "K"; } cell.setCellValue(cellV); } }
From source file:athena.Controller.java
License:Open Source License
private void convertExceltoCSV(String inputFile, String outputFilePath) { InputStream inp = null;/*from ww w . j ava 2 s .co m*/ try { inp = new FileInputStream(inputFile); wb = new XSSFWorkbook(inp); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet thisSheet = wb.getSheetAt(i); int rowEnd = Math.max(1400, thisSheet.getLastRowNum()); view.setOutput("Writting.. " + thisSheet.getSheetName()); String csvRawString = ""; String outputFileName = outputFilePath + thisSheet.getSheetName() + ".csv"; try { OutputStream os; File testFile = new File(outputFileName); if (testFile.exists() && !testFile.isDirectory()) { os = new FileOutputStream(outputFilePath + thisSheet.getSheetName() + "(1).csv"); } else { os = new FileOutputStream(outputFileName); } PrintWriter w = new PrintWriter(new OutputStreamWriter(os, "UTF-8")); for (int j = 0; j < rowEnd; j++) { Row row = thisSheet.getRow(j); if (row != null) { for (int k = 0; k < row.getLastCellNum(); k++) { if (k == (row.getLastCellNum() - 1)) { if (row.getCell(k) != null) { csvRawString = csvRawString + row.getCell(k); } } else { if (row.getCell(k) == null) { csvRawString = csvRawString + ","; } else { csvRawString = csvRawString + row.getCell(k) + ","; } } } } else { csvRawString = csvRawString + ","; } csvRawString = csvRawString + "\n"; w.print(csvRawString); w.flush(); csvRawString = ""; } w.close(); view.setOutput("Done.. " + thisSheet.getSheetName()); } catch (FileNotFoundException e) { view.setOutput("I'm confused.. File not found!"); } catch (UnsupportedEncodingException e) { view.setOutput("Call 911.. or Jake"); } } } catch (IOException e) { view.setOutput("Uh oh.. Fail to read file!"); } finally { try { inp.close(); view.setOutput("Done conversion.. " + model.getInputFilePath() + "\n"); model.setInputFilePath(null); model.setOutputFilePath(null); view.refreshIntputPath(); view.refreshOutputPath(); } catch (IOException e) { view.setOutput("Damn input stream.."); } } }