List of usage examples for org.apache.poi.ss.usermodel Sheet createRow
Row createRow(int rownum);
From source file:Account.java
public void writetoexcel() throws FileNotFoundException, IOException { refresh();//from w w w . ja va 2 s .c o m selection(); DefaultTableModel dtm = (DefaultTableModel) jTable1.getModel(); DefaultTableModel dt = (DefaultTableModel) jTable2.getModel(); DefaultTableModel d = (DefaultTableModel) jTable3.getModel(); Workbook wb = new HSSFWorkbook(); //XSSFWorkbook wb = new XSSFWorkbook(); CreationHelper createhelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); Row row = null; Cell cell = null; row = sheet.createRow(0); cell = row.createCell(0); cell.setCellValue(jTextField1.getText()); cell = row.createCell(6); cell.setCellValue("Replacement"); row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue("Date"); cell = row.createCell(6); cell.setCellValue("Date"); cell = row.createCell(1); cell.setCellValue("Goods"); cell = row.createCell(2); cell.setCellValue("Quantity"); cell = row.createCell(3); cell.setCellValue("Rate"); cell = row.createCell(4); cell.setCellValue("Total"); cell = row.createCell(7); cell.setCellValue("Goods"); cell = row.createCell(8); cell.setCellValue("Quantity"); cell = row.createCell(9); cell.setCellValue("Rate"); cell = row.createCell(10); cell.setCellValue("Total"); int t = dtm.getRowCount(); int m = dt.getRowCount(); int y = d.getRowCount(); int i, z; if (dtm.getValueAt(t - 1, 1) != null) t++; if (dt.getValueAt(m - 1, 1) != null) m++; if (d.getValueAt(y - 1, 0) != null) y++; if (t < m) { z = m; for (i = 0; i < t - 1; i++) { row = sheet.createRow(i + 2); for (int j = 0; j < dtm.getColumnCount(); j++) { cell = row.createCell(j); if (j == 4) cell.setCellValue((Long) dtm.getValueAt(i, j)); else if (j == 0 || j == 1) cell.setCellValue((String) dtm.getValueAt(i, j)); else cell.setCellValue((Integer) dtm.getValueAt(i, j)); cell = row.createCell(j + 6); if (j == 4) cell.setCellValue((Long) dt.getValueAt(i, j)); else if (j == 0 || j == 1) cell.setCellValue((String) dt.getValueAt(i, j)); else cell.setCellValue(Integer.parseInt(dt.getValueAt(i, j).toString())); } } //i--; row = sheet.createRow(t + 2); cell = row.createCell(2); cell.setCellValue(jTextField3.getText()); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField4.getText())); row = sheet.createRow(t + 3); cell = row.createCell(2); cell.setCellValue(jTextField5.getText()); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField6.getText())); row = sheet.createRow(t + 4); cell = row.createCell(2); cell.setCellValue(jTextField7.getText()); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField8.getText())); row = sheet.createRow(t + 5); cell = row.createCell(2); cell.setCellValue("Total Replacement"); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField10.getText())); row = sheet.createRow(t + 6); cell = row.createCell(2); cell.setCellValue("Total"); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField13.getText())); row = sheet.createRow(t + 7); cell = row.createCell(2); cell.setCellValue("Payment Total"); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField16.getText())); row = sheet.createRow(t + 8); cell = row.createCell(2); cell.setCellValue("New Grand Total"); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField20.getText())); int k = i; while (i <= m - 1) { row = sheet.createRow(i + 2); if (i == k + 1) { cell = row.createCell(2); cell.setCellValue(jTextField3.getText()); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField4.getText())); } if (i == k + 2) { cell = row.createCell(2); cell.setCellValue(jTextField5.getText()); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField6.getText())); } if (i == k + 3) { cell = row.createCell(2); cell.setCellValue(jTextField7.getText()); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField8.getText())); } if (i == k + 4) { cell = row.createCell(2); cell.setCellValue("Total Replacement"); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField10.getText())); } if (i == k + 5) { cell = row.createCell(2); cell.setCellValue("Total"); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField13.getText())); } if (i == k + 6) { cell = row.createCell(2); cell.setCellValue("Payment Total"); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField16.getText())); } if (i == k + 7) { cell = row.createCell(2); cell.setCellValue("New Grand Total"); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField20.getText())); } if (i == m - 1) { cell = row.createCell(8); cell.setCellValue("Total Replacement"); cell = row.createCell(10); cell.setCellValue(Long.parseLong(jTextField10.getText())); } else { for (int j = 0; j < dtm.getColumnCount(); j++) { cell = row.createCell(j + 6); if (j == 4) cell.setCellValue((Long) dt.getValueAt(i, j)); else if (j == 0 || j == 1) cell.setCellValue((String) dt.getValueAt(i, j)); else cell.setCellValue(Integer.parseInt(dt.getValueAt(i, j).toString())); } } i++; } row = sheet.createRow(m + 9); cell = row.createCell(0); cell.setCellValue("Payment"); row = sheet.createRow(m + 10); cell = row.createCell(0); cell.setCellValue("Date"); cell = row.createCell(1); cell.setCellValue("Payments"); for (int u = 0; u < y - 1; u++) { row = sheet.createRow(u + m + 11); for (int j = 0; j < d.getColumnCount(); j++) { cell = row.createCell(j); if (j == 0) cell.setCellValue((String) d.getValueAt(u, j)); else cell.setCellValue(Integer.parseInt(d.getValueAt(u, j).toString())); } } row = sheet.createRow(m + 11 + y); cell = row.createCell(0); cell.setCellValue("Payment Total"); cell = row.createCell(1); cell.setCellValue(Long.parseLong(jTextField16.getText())); } else { for (i = 0; i < m - 1; i++) { row = sheet.createRow(i + 2); for (int j = 0; j < dtm.getColumnCount(); j++) { cell = row.createCell(j); if (j == 4) cell.setCellValue((Long) dtm.getValueAt(i, j)); else if (j == 0 || j == 1) cell.setCellValue((String) dtm.getValueAt(i, j)); else cell.setCellValue((Integer) dtm.getValueAt(i, j)); cell = row.createCell(j + 6); if (j == 4) cell.setCellValue((Long) dt.getValueAt(i, j)); else if (j == 0 || j == 1) cell.setCellValue((String) dt.getValueAt(i, j)); else cell.setCellValue(Integer.parseInt(dt.getValueAt(i, j).toString())); } } //i--; int k = i; while (i <= t - 1) { row = sheet.createRow(i + 2); if (i == k) { cell = row.createCell(8); cell.setCellValue("Total Replacement"); cell = row.createCell(10); cell.setCellValue(Long.parseLong(jTextField10.getText())); } if (i != t - 1) { for (int j = 0; j < dtm.getColumnCount(); j++) { cell = row.createCell(j); if (j == 4) cell.setCellValue((Long) dtm.getValueAt(i, j)); else if (j == 0 || j == 1) cell.setCellValue((String) dtm.getValueAt(i, j)); else cell.setCellValue((Integer) dtm.getValueAt(i, j)); } } i++; } row = sheet.createRow(t + 2); cell = row.createCell(2); cell.setCellValue(jTextField3.getText()); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField4.getText())); row = sheet.createRow(t + 3); cell = row.createCell(2); cell.setCellValue(jTextField5.getText()); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField6.getText())); row = sheet.createRow(t + 4); cell = row.createCell(2); cell.setCellValue(jTextField7.getText()); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField8.getText())); row = sheet.createRow(t + 5); cell = row.createCell(2); cell.setCellValue("Total Replacement"); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField10.getText())); row = sheet.createRow(t + 6); cell = row.createCell(2); cell.setCellValue("Total"); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField13.getText())); row = sheet.createRow(t + 7); cell = row.createCell(2); cell.setCellValue("Payment Total"); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField16.getText())); row = sheet.createRow(t + 8); cell = row.createCell(2); cell.setCellValue("New Grand Total"); cell = row.createCell(4); cell.setCellValue(Long.parseLong(jTextField20.getText())); row = sheet.createRow(t + 10); cell = row.createCell(0); cell.setCellValue("Payment"); row = sheet.createRow(t + 11); cell = row.createCell(0); cell.setCellValue("Date"); cell = row.createCell(1); cell.setCellValue("Payments"); for (int u = 0; u < y - 1; u++) { row = sheet.createRow(u + t + 12); for (int j = 0; j < d.getColumnCount(); j++) { cell = row.createCell(j); if (j == 0) cell.setCellValue((String) d.getValueAt(u, j)); else cell.setCellValue(Integer.parseInt(d.getValueAt(u, j).toString())); } } row = sheet.createRow(t + y + 12); cell = row.createCell(0); cell.setCellValue("Payment Total"); cell = row.createCell(1); cell.setCellValue(Long.parseLong(jTextField16.getText())); } FileOutputStream out = new FileOutputStream( chooser.getSelectedFile() + "\\" + jTextField1.getText() + ".xls"); wb.write(out); out.close(); }
From source file:Account.java
public void writereplacement() throws FileNotFoundException, IOException { DefaultTableModel dtm = (DefaultTableModel) jTable2.getModel(); Workbook wb = new HSSFWorkbook(); //XSSFWorkbook wb = new XSSFWorkbook(); CreationHelper createhelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); Row row = null;/*from ww w . j a va 2 s . c om*/ Cell cell = null; row = sheet.createRow(0); cell = row.createCell(6); cell.setCellValue("Replacement"); row = sheet.createRow(1); cell = row.createCell(6); cell.setCellValue("Date"); cell = row.createCell(7); cell.setCellValue("Goods"); cell = row.createCell(8); cell.setCellValue("Quantity"); cell = row.createCell(9); cell.setCellValue("Rate"); cell = row.createCell(10); cell.setCellValue("Total"); int t = dtm.getRowCount(); if (dtm.getValueAt(t - 1, 1) != null) t++; for (int i = 0; i < t - 1; i++) { row = sheet.createRow(i + 2); for (int j = 0; j < dtm.getColumnCount(); j++) { cell = row.createCell(j + 6); if (j == 4) cell.setCellValue((Long) dtm.getValueAt(i, j)); else if (j == 0 || j == 1) cell.setCellValue((String) dtm.getValueAt(i, j)); else cell.setCellValue((Integer) dtm.getValueAt(i, j)); } } row = sheet.createRow(t + 2); cell = row.createCell(2 + 6); cell.setCellValue("Replacement Total"); cell = row.createCell(4 + 6); cell.setCellValue(Long.parseLong(jTextField10.getText())); FileOutputStream out = new FileOutputStream("D:\\workbook.xls"); wb.write(out); out.close(); }
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 ww . ja va 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", "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:PlacasClientes.java
private void EnviarExcel(ResultSet rs) throws IOException { String rutaArchivo = System.getProperty("user.home") + "/ejemploExcelJava.xls"; /*Se crea el objeto de tipo File con la ruta del archivo*/ File archivoXLS = new File(rutaArchivo); /*Si el archivo existe se elimina*/ if (archivoXLS.exists()) archivoXLS.delete();/* w ww . j a v a 2 s.c om*/ /*Se crea el archivo*/ archivoXLS.createNewFile(); /*Se crea el libro de excel usando el objeto de tipo Workbook*/ Workbook libro = new HSSFWorkbook(); CreationHelper createhelper = libro.getCreationHelper(); CellStyle cellStyle = libro.createCellStyle(); cellStyle.setDataFormat(createhelper.createDataFormat().getFormat("dd/mm/yyyy")); CellStyle cellStyle2 = libro.createCellStyle(); /*Se inicializa el flujo de datos con el archivo xls*/ FileOutputStream archivo = new FileOutputStream(archivoXLS); /*Utilizamos la clase Sheet para crear una nueva hoja de trabajo dentro del libro que creamos anteriormente*/ Sheet hoja = libro.createSheet("ClientesPlacas"); Font fuente = libro.createFont(); fuente.setFontHeightInPoints((short) 13); fuente.setFontName("Arial"); fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); Font fuente2 = libro.createFont(); fuente.setFontHeightInPoints((short) 13); fuente.setFontName("Arial"); //fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); Row Enc = hoja.createRow(2); Cell celda = Enc.createCell(0); celda.setCellValue("PLACA DE VEHICULOS POR CLIENTES"); }
From source file:Inicio.java
public void CrearExcel() { File archivoXLS = new File(rutaArchivo); if (archivoXLS.exists()) archivoXLS.delete();/*from w w w. j a va 2s. c o m*/ try { archivoXLS.createNewFile(); Workbook libro = new HSSFWorkbook(); //se crea el objeto FileOutputStream archivo = new FileOutputStream(archivoXLS); Sheet hoja = libro.createSheet("Datos");// crea la hoja de Trabajo Random rnd = new Random(); for (int f = 0; f < 100; f++) { Row fila = hoja.createRow(f); for (int c = 0; c < 5; c++) { Cell celda = fila.createCell(c); if (f == 0) { celda.setCellValue("Encabezado #" + c); } else { celda.setCellValue(rnd.nextInt(999) + 1); //genera un numero entre 1 y 100 } } //end for } //end For libro.write(archivo); archivo.close(); //Para que lo abra en el programa predeterminado... Runtime.getRuntime().exec("cmd /c " + rutaArchivo); System.out.println("Archivo " + rutaArchivo + " Creado"); } catch (IOException ex) { System.err.println("Error creando el archivo " + ex.getMessage()); } }
From source file:Inicio.java
public void Oracle_Excel() { String nombres, apellidos, id, tdoc, genero, edad, historia; int i = 1;// www . j a va2s. co m int c = 0; ResultSet reg = Database.consultar("SELECT * FROM PACIENTES"); File archivoXLS = new File(rutaArchivo); if (archivoXLS.exists()) archivoXLS.delete(); try { archivoXLS.createNewFile(); Workbook libro = new HSSFWorkbook(); //se crea el objeto FileOutputStream archivo = new FileOutputStream(archivoXLS); Sheet hoja = libro.createSheet("Datos");// crea la hoja de Trabajo Row fila = hoja.createRow(0); Cell celda = fila.createCell(0); celda.setCellValue("DNI"); celda = fila.createCell(1); celda.setCellValue("TIPO"); celda = fila.createCell(2); celda.setCellValue("NOMBRES"); celda = fila.createCell(3); celda.setCellValue("APELLIDOS"); celda = fila.createCell(4); celda.setCellValue("GENERO"); celda = fila.createCell(5); celda.setCellValue("EDAD"); celda = fila.createCell(6); celda.setCellValue("HISTORIACL"); while (reg.next()) { fila = hoja.createRow(i); id = reg.getString("DNI"); tdoc = reg.getString("TIPODOCUMENTO"); nombres = reg.getString("NOMBRE1") + " " + reg.getString("NOMBRE2"); apellidos = reg.getString("APELLIDO1") + " " + reg.getString("APELLIDO2"); tdoc = reg.getString("TIPODOCUMENTO"); genero = reg.getString("SEXO"); edad = reg.getString("EDAD"); historia = reg.getString("NUMERO_HISTORIA"); celda = fila.createCell(0); celda.setCellValue(id); celda = fila.createCell(1); celda.setCellValue(tdoc); celda = fila.createCell(2); celda.setCellValue(nombres); celda = fila.createCell(3); celda.setCellValue(apellidos); celda = fila.createCell(4); celda.setCellValue(genero); celda = fila.createCell(5); celda.setCellValue(edad); celda = fila.createCell(6); celda.setCellValue(historia); i++; } libro.write(archivo); archivo.close(); Runtime.getRuntime().exec("cmd /c " + rutaArchivo); System.out.println("Archivo " + rutaArchivo + " Creado"); } catch (SQLException ex) { System.err.println("Error creando el archivo " + ex.getMessage()); } catch (IOException ex) { System.err.println("Error creando el archivo " + ex.getMessage()); } }
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 ww w. jav a2 s .com 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 ww . j a v a 2s.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; 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:adams.data.io.output.ExcelSpreadSheetWriter.java
License:Open Source License
/** * Performs the actual writing. The caller must ensure that the writer gets * closed.//w w w .ja v a2s . c om * * @param content the spreadsheet to write * @param out the writer to write the spreadsheet to * @return true if successfully written */ @Override protected boolean doWrite(SpreadSheet[] content, OutputStream out) { boolean result; Workbook workbook; Sheet sheet; Row row; adams.data.spreadsheet.Row spRow; adams.data.spreadsheet.Cell spCell; Cell cell; int i; int n; int count; CellStyle styleDate; CellStyle styleDateTime; CellStyle styleTime; HashSet<String> names; String name; result = true; try { if (getWriteOOXML()) workbook = new XSSFWorkbook(); else workbook = new HSSFWorkbook(); styleDate = ExcelHelper.getDateCellStyle(workbook, Constants.DATE_FORMAT); styleDateTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIMESTAMP_FORMAT); styleTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIME_FORMAT); count = 0; names = new HashSet<>(); for (SpreadSheet cont : content) { if (m_Stopped) return false; sheet = workbook.createSheet(); if (cont.getName() != null) { name = cont.getName().replace("'", ""); if (names.contains(name)) name += (count + 1); } else { name = m_SheetPrefix + (count + 1); } names.add(name); workbook.setSheetName(count, name); // header row = sheet.createRow(0); for (i = 0; i < cont.getColumnCount(); i++) { cell = row.createCell(i); cell.setCellValue(cont.getHeaderRow().getCell(i).getContent()); } // data for (n = 0; n < cont.getRowCount(); n++) { if (m_Stopped) return false; row = sheet.createRow(n + 1); spRow = cont.getRow(n); for (i = 0; i < cont.getColumnCount(); i++) { cell = row.createCell(i); spCell = spRow.getCell(i); if ((spCell == null) || spCell.isMissing()) { if (m_MissingValue.length() > 0) cell.setCellValue(m_MissingValue); else cell.setCellType(Cell.CELL_TYPE_BLANK); continue; } if (spCell.isFormula() && !m_OutputAsDisplayed) { cell.setCellFormula(spCell.getFormula().substring(1)); } else { if (spCell.isDate()) { cell.setCellValue(spCell.toDate()); cell.setCellStyle(styleDate); } else if (spCell.isTime()) { cell.setCellValue(spCell.toTime()); cell.setCellStyle(styleTime); } else if (spCell.isDateTime()) { cell.setCellValue(spCell.toDateTime()); cell.setCellStyle(styleDateTime); } else if (spCell.isNumeric()) { cell.setCellValue(Utils.toDouble(spCell.getContent())); } else { cell.setCellValue(spCell.getContent()); } } } } // next sheet count++; } // save workbook.write(out); } catch (Exception e) { result = false; getLogger().log(Level.SEVERE, "Failed writing spreadsheet data", e); } return result; }
From source file:adams.data.io.output.ExcelStreamingSpreadSheetWriter.java
License:Open Source License
/** * Performs the actual writing. The caller must ensure that the writer gets * closed.//from w ww .jav a2 s .co m * * @param content the spreadsheet to write * @param out the writer to write the spreadsheet to * @return true if successfully written */ @Override protected boolean doWrite(SpreadSheet[] content, OutputStream out) { boolean result; SXSSFWorkbook workbook; Sheet sheet; Row row; adams.data.spreadsheet.Row spRow; adams.data.spreadsheet.Cell spCell; Cell cell; int i; int n; int count; CellStyle styleDate; CellStyle styleDateTime; CellStyle styleTime; HashSet<String> names; String name; result = true; try { workbook = new SXSSFWorkbook(m_MaxRows); styleDate = ExcelHelper.getDateCellStyle(workbook, Constants.DATE_FORMAT); styleDateTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIMESTAMP_FORMAT); styleTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIME_FORMAT); count = 0; names = new HashSet<>(); for (SpreadSheet cont : content) { if (m_Stopped) return false; sheet = workbook.createSheet(); if (cont.getName() != null) { name = cont.getName().replace("'", ""); if (names.contains(name)) name += (count + 1); } else { name = m_SheetPrefix + (count + 1); } names.add(name); workbook.setSheetName(count, name); // header row = sheet.createRow(0); for (i = 0; i < cont.getColumnCount(); i++) { cell = row.createCell(i); cell.setCellValue(cont.getHeaderRow().getCell(i).getContent()); } // data for (n = 0; n < cont.getRowCount(); n++) { if (m_Stopped) return false; row = sheet.createRow(n + 1); spRow = cont.getRow(n); for (i = 0; i < cont.getColumnCount(); i++) { cell = row.createCell(i); spCell = spRow.getCell(i); if ((spCell == null) || spCell.isMissing()) { if (m_MissingValue.length() > 0) cell.setCellValue(m_MissingValue); else cell.setCellType(Cell.CELL_TYPE_BLANK); continue; } if (spCell.isFormula() && !m_OutputAsDisplayed) { cell.setCellFormula(spCell.getFormula().substring(1)); } else { if (spCell.isDate()) { cell.setCellValue(spCell.toDate()); cell.setCellStyle(styleDate); } else if (spCell.isTime()) { cell.setCellValue(spCell.toTime()); cell.setCellStyle(styleTime); } else if (spCell.isDateTime()) { cell.setCellValue(spCell.toDateTime()); cell.setCellStyle(styleDateTime); } else if (spCell.isNumeric()) { cell.setCellValue(Utils.toDouble(spCell.getContent())); } else { cell.setCellValue(spCell.getContent()); } } } } // next sheet count++; } // save workbook.write(out); } catch (Exception e) { result = false; getLogger().log(Level.SEVERE, "Failed writing spreadsheet data", e); } return result; }