Java tutorial
import java.awt.event.KeyEvent; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.sql.SQLException; import org.apache.commons.io.FileUtils; import java.awt.Desktop; import java.io.*; import java.sql.ResultSet; import org.apache.commons.io.FileUtils; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ /** * * @author Pramo */ public class Report_PRCR_New_ETF_Excel_File_Generator extends javax.swing.JPanel { /** * */ public Report_PRCR_New_ETF_Excel_File_Generator() { initComponents(); } DateChooser_text datechooser = new DateChooser_text(); Date_Handler datehandler = new Date_Handler(); Report_gen generate = new Report_gen(); UserAccountControl user = new UserAccountControl(); DatabaseManager dbm = DatabaseManager.getDbCon(); public void focus() { //dayfield.requestFocus(); //dayfield.selectAll(); } /** * This method is called from within the constructor to initialize the form. * WARNING: Do NOT modify this code. The content of this method is always * regenerated by the Form Editor. */ @SuppressWarnings("unchecked") // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents private void initComponents() { jPanel2 = new javax.swing.JPanel(); datepanel = new javax.swing.JPanel(); monthfield = new javax.swing.JTextField(); yearfield = new javax.swing.JTextField(); datePicker1 = new com.michaelbaranov.microba.calendar.DatePicker(); jLabel1 = new javax.swing.JLabel(); jLabel4 = new javax.swing.JLabel(); jButton1 = new javax.swing.JButton(); setBorder(javax.swing.BorderFactory.createLineBorder(new java.awt.Color(0, 0, 0), 6)); jPanel2.setBorder(javax.swing.BorderFactory.createLineBorder(new java.awt.Color(0, 51, 153), 2)); datepanel.setBorder(javax.swing.BorderFactory.createEtchedBorder()); monthfield.setText(datehandler.get_today_month()); monthfield.addKeyListener(new java.awt.event.KeyAdapter() { public void keyPressed(java.awt.event.KeyEvent evt) { monthfieldKeyPressed(evt); } }); yearfield.setText(datehandler.get_today_year()); yearfield.addKeyListener(new java.awt.event.KeyAdapter() { public void keyPressed(java.awt.event.KeyEvent evt) { yearfieldKeyPressed(evt); } }); datePicker1.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { datePicker1ActionPerformed(evt); } }); javax.swing.GroupLayout datepanelLayout = new javax.swing.GroupLayout(datepanel); datepanel.setLayout(datepanelLayout); datepanelLayout .setHorizontalGroup(datepanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(datepanelLayout.createSequentialGroup().addContainerGap() .addComponent(monthfield, javax.swing.GroupLayout.PREFERRED_SIZE, 43, javax.swing.GroupLayout.PREFERRED_SIZE) .addGap(0, 0, 0) .addComponent(yearfield, javax.swing.GroupLayout.PREFERRED_SIZE, 44, javax.swing.GroupLayout.PREFERRED_SIZE) .addGap(0, 0, 0) .addComponent(datePicker1, javax.swing.GroupLayout.PREFERRED_SIZE, 42, javax.swing.GroupLayout.PREFERRED_SIZE) .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))); datepanelLayout.setVerticalGroup(datepanelLayout .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(datepanelLayout.createSequentialGroup().addGap(0, 0, 0).addGroup(datepanelLayout .createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING) .addComponent(datePicker1, javax.swing.GroupLayout.PREFERRED_SIZE, 30, javax.swing.GroupLayout.PREFERRED_SIZE) .addGroup(datepanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE) .addComponent(monthfield, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE) .addComponent(yearfield, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))) .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))); jLabel1.setText("Year Month"); javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2); jPanel2.setLayout(jPanel2Layout); jPanel2Layout.setHorizontalGroup(jPanel2Layout .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel2Layout.createSequentialGroup() .addGap(23, 23, 23).addComponent(jLabel1).addGap(50, 50, 50) .addComponent(datepanel, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE) .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))); jPanel2Layout.setVerticalGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(jPanel2Layout.createSequentialGroup().addGap(24, 24, 24).addComponent(jLabel1) .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)) .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel2Layout.createSequentialGroup() .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addComponent(datepanel, javax.swing.GroupLayout.PREFERRED_SIZE, 40, javax.swing.GroupLayout.PREFERRED_SIZE) .addContainerGap())); jLabel4.setFont(new java.awt.Font("Tahoma", 1, 24)); // NOI18N jLabel4.setText("ETF Excel File Generator"); jButton1.setText("Generate ETF File"); jButton1.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { jButton1ActionPerformed(evt); } }); javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this); this.setLayout(layout); layout.setHorizontalGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(layout.createSequentialGroup().addContainerGap(55, Short.MAX_VALUE).addComponent(jLabel4) .addGap(38, 38, 38)) .addGroup(layout.createSequentialGroup().addContainerGap() .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addComponent(jButton1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addComponent(jPanel2, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)) .addContainerGap())); layout.setVerticalGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(layout.createSequentialGroup().addContainerGap() .addComponent(jLabel4, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addGap(40, 40, 40) .addComponent(jPanel2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 84, Short.MAX_VALUE) .addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 40, javax.swing.GroupLayout.PREFERRED_SIZE) .addGap(27, 27, 27))); }// </editor-fold>//GEN-END:initComponents private void monthfieldKeyPressed(java.awt.event.KeyEvent evt) {//GEN-FIRST:event_monthfieldKeyPressed if (monthfield.getText().equals("Jan")) { if (evt.getKeyCode() == KeyEvent.VK_DOWN) { monthfield.setText("Dec"); int yr = Integer.parseInt(yearfield.getText()); yearfield.setText("" + (yr - 1)); monthfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_UP) { monthfield.setText("Feb"); monthfield.selectAll(); } } else if (monthfield.getText().equals("Feb")) { if (evt.getKeyCode() == KeyEvent.VK_DOWN) { monthfield.setText("Jan"); int yr = Integer.parseInt(yearfield.getText()); monthfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_UP) { monthfield.setText("Mar"); monthfield.selectAll(); } } else if (monthfield.getText().equals("Mar")) { if (evt.getKeyCode() == KeyEvent.VK_DOWN) { monthfield.setText("Feb"); int yr = Integer.parseInt(yearfield.getText()); monthfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_UP) { monthfield.setText("Apr"); monthfield.selectAll(); } } else if (monthfield.getText().equals("Apr")) { if (evt.getKeyCode() == KeyEvent.VK_DOWN) { monthfield.setText("Mar"); int yr = Integer.parseInt(yearfield.getText()); monthfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_UP) { monthfield.setText("May"); monthfield.selectAll(); } } else if (monthfield.getText().equals("May")) { if (evt.getKeyCode() == KeyEvent.VK_DOWN) { monthfield.setText("Apr"); int yr = Integer.parseInt(yearfield.getText()); monthfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_UP) { monthfield.setText("Jun"); monthfield.selectAll(); } } else if (monthfield.getText().equals("Jun")) { if (evt.getKeyCode() == KeyEvent.VK_DOWN) { monthfield.setText("May"); int yr = Integer.parseInt(yearfield.getText()); monthfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_UP) { monthfield.setText("Jul"); monthfield.selectAll(); } } else if (monthfield.getText().equals("Jul")) { if (evt.getKeyCode() == KeyEvent.VK_DOWN) { monthfield.setText("Jun"); int yr = Integer.parseInt(yearfield.getText()); monthfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_UP) { monthfield.setText("Aug"); monthfield.selectAll(); } } else if (monthfield.getText().equals("Aug")) { if (evt.getKeyCode() == KeyEvent.VK_DOWN) { monthfield.setText("Jul"); int yr = Integer.parseInt(yearfield.getText()); monthfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_UP) { monthfield.setText("Sep"); monthfield.selectAll(); } } else if (monthfield.getText().equals("Sep")) { if (evt.getKeyCode() == KeyEvent.VK_DOWN) { monthfield.setText("Aug"); int yr = Integer.parseInt(yearfield.getText()); monthfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_UP) { monthfield.setText("Oct"); monthfield.selectAll(); } } else if (monthfield.getText().equals("Oct")) { if (evt.getKeyCode() == KeyEvent.VK_DOWN) { monthfield.setText("Sep"); int yr = Integer.parseInt(yearfield.getText()); monthfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_UP) { monthfield.setText("Nov"); monthfield.selectAll(); } } else if (monthfield.getText().equals("Nov")) { if (evt.getKeyCode() == KeyEvent.VK_DOWN) { monthfield.setText("Oct"); int yr = Integer.parseInt(yearfield.getText()); monthfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_UP) { monthfield.setText("Dec"); monthfield.selectAll(); } } else if (monthfield.getText().equals("Dec")) { if (evt.getKeyCode() == KeyEvent.VK_DOWN) { monthfield.setText("Nov"); int yr = Integer.parseInt(yearfield.getText()); monthfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_UP) { monthfield.setText("Jan"); int yr = Integer.parseInt(yearfield.getText()); yearfield.setText("" + (yr + 1)); monthfield.selectAll(); } } if (evt.getKeyCode() == KeyEvent.VK_LEFT) { // dayfield.requestFocus(); // dayfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_RIGHT) { yearfield.requestFocus(); yearfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_ENTER) { ////// ChaNGE focus on enter//////////////// // dayfield2.requestFocus(); // dayfield2.selectAll(); } }//GEN-LAST:event_monthfieldKeyPressed private void yearfieldKeyPressed(java.awt.event.KeyEvent evt) {//GEN-FIRST:event_yearfieldKeyPressed if (evt.getKeyCode() == KeyEvent.VK_UP) { yearfield.setText("" + (Integer.parseInt(yearfield.getText()) + 1)); yearfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_DOWN) { yearfield.setText("" + (Integer.parseInt(yearfield.getText()) - 1)); yearfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_LEFT) { monthfield.requestFocus(); monthfield.selectAll(); } if (evt.getKeyCode() == KeyEvent.VK_ENTER) { ////// ChaNGE focus on enter//////////////// // dayfield2.requestFocus(); // dayfield2.selectAll(); } }//GEN-LAST:event_yearfieldKeyPressed private void datePicker1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_datePicker1ActionPerformed java.sql.Date datef = new java.sql.Date(datePicker1.getDate().getTime()); // dayfield.setText(datehandler.get_day(datef)); monthfield.setText(datehandler.get_month(datef)); yearfield.setText(datehandler.get_year(datef)); // dayfield2.requestFocus(); // dayfield2.selectAll(); }//GEN-LAST:event_datePicker1ActionPerformed private static void copyFileUsingApacheCommonsIO(File source, File dest) throws IOException { FileUtils.copyFile(source, dest); } private static String[] split_name(String name) { int i = 0; int check = 0; String return_name[] = new String[2]; for (i = 0; i < name.length(); i++) { if (name.charAt(i) != ' ' && name.charAt(i) != '.') { check++; } else { check = 0; } if (check == 2) { if (i - 2 <= 0) { return_name[0] = " "; return_name[1] = name; } else { return_name[0] = name.substring(0, i - 2); return_name[1] = name.substring(i - 1, name.length()); return_name[0] = return_name[0].replaceAll("\\.", " "); } break; } } return return_name; } MessageBox msg = new MessageBox(); Main_versioning mv = new Main_versioning(); private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed try { 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"); } }//GEN-LAST:event_jButton1ActionPerformed public void duplicateTable(String original_table_name, String table_copy_name) { DatabaseManager dbCon = DatabaseManager.getDbCon(); try { if (dbCon.TableExistence(table_copy_name)) { dbCon.insert("DROP TABLE " + table_copy_name + ""); } dbCon.insert("CREATE TABLE " + table_copy_name + " LIKE " + original_table_name + ""); dbCon.insert("INSERT " + table_copy_name + " SELECT * FROM " + original_table_name + ""); } catch (SQLException ex) { MessageBox.showMessage(ex.getMessage(), "SQL Error", "error"); } } public String getST(String month, String year) { String st; if (datehandler.return_index(month) < 10) { st = year + "_0" + datehandler.return_index(month); } else { st = year + "_" + datehandler.return_index(month); } return st; } // Variables declaration - do not modify//GEN-BEGIN:variables private com.michaelbaranov.microba.calendar.DatePicker datePicker1; private javax.swing.JPanel datepanel; private javax.swing.JButton jButton1; private javax.swing.JLabel jLabel1; private javax.swing.JLabel jLabel4; private javax.swing.JPanel jPanel2; private javax.swing.JTextField monthfield; private javax.swing.JTextField yearfield; // End of variables declaration//GEN-END:variables }