List of usage examples for org.apache.poi.hssf.usermodel HSSFFont setBold
public void setBold(boolean bold)
From source file:br.com.nfsconsultoria.azcontrole.bean.VendaBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);//from www. j av a2s. c om HSSFRow header = sheet.getRow(0); HSSFFont font = wb.createFont(); font.setBold(true); font.setColor(HSSFColor.WHITE.index); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setWrapText(true); cellStyle.setAlignment(CellStyle.ALIGN_JUSTIFY); cellStyle.setFont(font); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
From source file:com.commander4j.util.JExcel.java
License:Open Source License
public void exportToExcel(String filename, ResultSet rs) { try {//from w w w . j a v a 2s . c o m ResultSetMetaData rsmd = rs.getMetaData(); int numberOfColumns = rsmd.getColumnCount(); int columnType = 0; String columnTypeName = ""; int recordNumber = 0; int passwordCol = -1; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFCellStyle cellStyle_varchar = workbook.createCellStyle(); cellStyle_varchar.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_nvarchar = workbook.createCellStyle(); cellStyle_nvarchar.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_varchar2 = workbook.createCellStyle(); cellStyle_varchar2.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_title = workbook.createCellStyle(); cellStyle_title.setAlignment(HorizontalAlignment.CENTER); HSSFCellStyle cellStyle_char = workbook.createCellStyle(); cellStyle_char.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_date = workbook.createCellStyle(); cellStyle_date.setAlignment(HorizontalAlignment.CENTER); cellStyle_date.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); HSSFCellStyle cellStyle_timestamp = workbook.createCellStyle(); cellStyle_timestamp.setAlignment(HorizontalAlignment.CENTER); cellStyle_timestamp.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); HSSFCellStyle cellStyle_decimal = workbook.createCellStyle(); cellStyle_decimal.setAlignment(HorizontalAlignment.RIGHT); HSSFFont font_title = workbook.createFont(); font_title.setColor((short) 0xc); font_title.setBold(true); ; font_title.setItalic(true); font_title.setUnderline(HSSFFont.U_DOUBLE); cellStyle_title.setFont(font_title); HSSFCell cell; HSSFRow row; // rs.beforeFirst(); while (rs.next()) { recordNumber++; if (recordNumber == 1) { row = sheet.createRow((int) 0); for (int column = 1; column <= numberOfColumns; column++) { cell = row.createCell((int) (column - 1)); String columnName = rsmd.getColumnLabel(column); columnName = columnName.replace("_", " "); columnName = JUtility.capitalize(columnName); cell.setCellStyle(cellStyle_title); cell.setCellValue(columnName); if (columnName.equals("Password")) { passwordCol = column; } } } row = sheet.createRow((int) recordNumber); for (int column = 1; column <= numberOfColumns; column++) { columnType = rsmd.getColumnType(column); columnTypeName = rsmd.getColumnTypeName(column); cell = row.createCell((int) (column - 1)); try { switch (columnType) { case java.sql.Types.NVARCHAR: HSSFRichTextString rtf_nvarchar; if (column == passwordCol) { rtf_nvarchar = new HSSFRichTextString("*****"); } else { rtf_nvarchar = new HSSFRichTextString(rs.getString(column)); } cell.setCellStyle(cellStyle_nvarchar); cell.setCellValue(rtf_nvarchar); break; case java.sql.Types.VARCHAR: HSSFRichTextString rtf_varchar; if (column == passwordCol) { rtf_varchar = new HSSFRichTextString("*****"); } else { rtf_varchar = new HSSFRichTextString(rs.getString(column)); } cell.setCellStyle(cellStyle_varchar); cell.setCellValue(rtf_varchar); break; case java.sql.Types.CHAR: HSSFRichTextString rtf_char = new HSSFRichTextString(rs.getString(column)); cell.setCellStyle(cellStyle_char); cell.setCellValue(rtf_char); break; case java.sql.Types.DATE: try { cell.setCellValue(rs.getTimestamp(column)); cell.setCellStyle(cellStyle_date); } catch (Exception ex) { } break; case java.sql.Types.TIMESTAMP: try { cell.setCellValue(rs.getTimestamp(column)); cell.setCellStyle(cellStyle_timestamp); } catch (Exception ex) { } break; case java.sql.Types.DECIMAL: HSSFRichTextString rtf_decimal = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_decimal); break; case java.sql.Types.NUMERIC: HSSFRichTextString rtf_decimaln = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_decimaln); break; case java.sql.Types.BIGINT: HSSFRichTextString rtf_bigint = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_bigint); break; case java.sql.Types.INTEGER: HSSFRichTextString rtf_int = new HSSFRichTextString(String.valueOf(rs.getInt(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_int); break; case java.sql.Types.FLOAT: HSSFRichTextString rtf_float = new HSSFRichTextString( String.valueOf(rs.getFloat(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_float); break; case java.sql.Types.DOUBLE: HSSFRichTextString rtf_double = new HSSFRichTextString( String.valueOf(rs.getDouble(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_double); break; default: cell.setCellValue(new HSSFRichTextString(columnTypeName)); break; } } catch (Exception ex) { String errormessage = ex.getLocalizedMessage(); HSSFRichTextString rtf_exception = new HSSFRichTextString(errormessage); cell.setCellStyle(cellStyle_varchar); cell.setCellValue(rtf_exception); break; } } if (recordNumber == 65535) { break; } } for (int column = 1; column <= numberOfColumns; column++) { sheet.autoSizeColumn((int) (column - 1)); } if (recordNumber > 0) { try { FileOutputStream fileOut = new FileOutputStream(filename.toLowerCase()); workbook.write(fileOut); fileOut.close(); } catch (Exception ex) { setErrorMessage(ex.getMessage()); } } try { workbook.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (SQLException e) { setErrorMessage(e.getMessage()); } }
From source file:com.creativity.controller.PesquisaFichasBean.java
public void posProcessarXls(Object documento) { HSSFWorkbook planilha = (HSSFWorkbook) documento; HSSFSheet folha = planilha.getSheetAt(0); HSSFRow cabecalho = folha.getRow(0); HSSFCellStyle estiloCelula = planilha.createCellStyle(); HSSFFont fonteCabecalho = planilha.createFont(); fonteCabecalho.setColor(IndexedColors.BLACK.getIndex()); fonteCabecalho.setBold(true); fonteCabecalho.setFontHeightInPoints((short) 10); estiloCelula.setFont(fonteCabecalho); estiloCelula.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); estiloCelula.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < cabecalho.getPhysicalNumberOfCells(); i++) { cabecalho.getCell(i).setCellStyle(estiloCelula); }/*from w w w . j a va2 s .com*/ }
From source file:com.learn.core.utils.HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells.// www. j a v a2 s. co m */ private static void testCreateSampleSheet(String outputFilename) throws IOException { try (HSSFWorkbook wb = new HSSFWorkbook()) { HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBold(true); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBold(true); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(BorderStyle.THIN); cs2.setFillPattern(FillPatternType.SOLID_FOREGROUND); cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); int rownum; for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue( rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(BorderStyle.THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet try (FileOutputStream out = new FileOutputStream(outputFilename)) { wb.write(out); } } }
From source file:controladores4.controladorReportes.java
public void GenerarLibroRemuneraciones() { DateFormat date2 = new SimpleDateFormat("dd-MMMM-yyyy"); NumberFormat FORMAT = NumberFormat.getCurrencyInstance(); DecimalFormatSymbols dfs = new DecimalFormatSymbols(); Thread runnable;// w w w . j a v a 2s . c o m runnable = new Thread() { public void run() { try { DateFormat yearDate = new SimpleDateFormat("yyyy"); DateFormat monthDate = new SimpleDateFormat("M"); String month = monthDate.format(new Date()); String year = yearDate.format(new Date()); String per = perDate.format(new Date()); String fecha = formatDate.format(new Date()); controladores.controladorPrincipal miControlador = new controladorPrincipal(); modelos3.modeloRemuneraciones remu = new modeloRemuneraciones(); double uf = remu.obtenerUF(); double utm = remu.obtenerUTM(); String[][] imp2cat = remu.obtenerTablaImpuesto(); //Listas para detalle afp y salud HashMap<String, ArrayList<AfpInfo>> mapAfp = new HashMap<>(); HashMap<String, ArrayList<SaludInfo>> mapSalud = new HashMap<>(); List<AfpInfo> listaAfp = new ArrayList<>(); List<SaludInfo> listaSalud = new ArrayList<>(); modelos.modeloEmpleados emp = new modeloEmpleados(); String[][] data = emp.obtenerRemuneraciones2(getMes(), getYear()); String path = "Libro de remuneraciones - " + per + ".xls"; File file = new File(path); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBold(true); font.setFontName("Calibri"); font.setFontHeightInPoints((short) 11); style.setFont(font); HSSFSheet sheet_rem = workbook.createSheet("Libro_Remuneraciones"); HSSFSheet sheet_afp = workbook.createSheet("Detalle_AFP"); HSSFSheet sheet_salud = workbook.createSheet("Detalle_Salud"); HSSFRow rowhead; //J para recorrer los datos de los trabajadores int j = 0; int numTrab = data.length; for (int i = 0; i < 2; i++) { //Primera linea rowhead = sheet_rem.createRow((short) 0 + i * 34); rowhead.createCell(0).setCellValue("parte:1"); rowhead.createCell(3) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(3).setCellStyle(style); rowhead.createCell(11).setCellValue("pag:" + (i + 1)); rowhead.createCell(12).setCellValue("parte:2"); rowhead.createCell(15) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(15).setCellStyle(style); rowhead.createCell(23).setCellValue("pag:" + (i + 1)); rowhead.createCell(24).setCellValue("parte:3"); rowhead.createCell(27) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(27).setCellStyle(style); rowhead.createCell(35).setCellValue("pag:" + (i + 1)); //Segunda linea rowhead = sheet_rem.createRow((short) 1 + (i * 34)); rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(2).setCellValue("Mes: " + month); rowhead.createCell(4).setCellValue("Ao: " + year); rowhead.createCell(6).setCellValue("UF: $" + uf); rowhead.createCell(9).setCellValue("UTM: $" + utm); rowhead.createCell(12).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(14).setCellValue("Mes: " + month); rowhead.createCell(16).setCellValue("Ao: " + year); rowhead.createCell(18).setCellValue("UF: $" + uf); rowhead.createCell(21).setCellValue("UTM: $" + utm); rowhead.createCell(24).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(26).setCellValue("Mes: " + month); rowhead.createCell(28).setCellValue("Ao: " + year); rowhead.createCell(30).setCellValue("UF: $" + uf); rowhead.createCell(33).setCellValue("UTM: $" + utm); //Tercera linea rowhead = sheet_rem.createRow((short) 4 + (i * 34)); rowhead.createCell(0).setCellValue("rut_trabajador"); rowhead.createCell(1).setCellValue("nombre_trabajador"); rowhead.createCell(2).setCellValue("dias_trab"); rowhead.createCell(3).setCellValue("sueldo_base_p"); rowhead.createCell(4).setCellValue("gratificacion"); rowhead.createCell(5).setCellValue("bono_aos"); rowhead.createCell(6).setCellValue("bono_horas"); rowhead.createCell(7).setCellValue("bono_asig_vol"); rowhead.createCell(8).setCellValue("bono_ad"); rowhead.createCell(9).setCellValue("otros_bonos"); rowhead.createCell(10).setCellValue("horas_extra"); rowhead.createCell(11).setCellValue("tot_h_imp"); rowhead.createCell(12).setCellValue("rut_trabajador"); rowhead.createCell(13).setCellValue("nombre_trabajador"); rowhead.createCell(14).setCellValue("colacion"); rowhead.createCell(15).setCellValue("movilizacion"); rowhead.createCell(16).setCellValue("asig_familiar"); rowhead.createCell(17).setCellValue("tot_h_no_imp"); rowhead.createCell(18).setCellValue("tot_haberes"); rowhead.createCell(19).setCellValue("desc_afp"); rowhead.createCell(20).setCellValue("desc_salud"); rowhead.createCell(21).setCellValue("afc_trab"); rowhead.createCell(22).setCellValue("afc_empl"); rowhead.createCell(23).setCellValue("tot_desc_leg"); rowhead.createCell(24).setCellValue("rut_trabajador"); rowhead.createCell(25).setCellValue("nombre_trabajador"); rowhead.createCell(26).setCellValue("imp_renta"); rowhead.createCell(27).setCellValue("caja_comp"); rowhead.createCell(28).setCellValue("anticipo"); rowhead.createCell(29).setCellValue("adelanto"); rowhead.createCell(30).setCellValue("prestamo"); rowhead.createCell(31).setCellValue("tot_desc_men"); rowhead.createCell(32).setCellValue("tot_desc"); rowhead.createCell(33).setCellValue("total_a_pago"); rowhead.createCell(34).setCellValue("sis"); //Cuarta linea for (int k = 0; k < 28; k++, j++) { if (j == numTrab) { break; } int base = Integer.parseInt(data[j][2]) * Integer.parseInt(data[j][28]) / 30; //GRATIFICACION int grat = (int) (base * 0.25); //BONO ANTIGUEDAD int bonoAnt = miControlador.obtenerBonoAnt(data[j][5]); //BONO 300 int totalBon300 = Integer.parseInt(data[j][27]); //BONO ADICIONAL int bonoAd = Integer.parseInt(data[j][11]); //BONO RESPONSABILIDAD int bonoResp = 0; //BONO ADICIONAL double bonoCol1 = Double.parseDouble(data[j][8]); double bonoCol30 = Double.parseDouble(data[j][9]); double bonoCol = bonoCol1 + bonoCol30 / 2; int totalBonCol = (int) Math.round(((double) base * 0.0077777) * bonoCol); //HORAS EXTRA double horasExNor = Double.parseDouble(data[j][12]); double horasExFes = Double.parseDouble(data[j][13]); double cantHorEx = 0; //total de horas extras normales = 1; festivas = 2 double totalHorex = 0; double resHorEx = 0; if (horasExNor > 45) { cantHorEx = 45; totalHorex = 45; resHorEx = horasExNor - 45; } else { cantHorEx = horasExNor; totalHorex = cantHorEx; } if (cantHorEx + horasExFes > 45) { resHorEx = resHorEx + (horasExFes - 45 + cantHorEx) * 2; totalHorex = 45 - cantHorEx; cantHorEx = 45; } else { cantHorEx += horasExFes; totalHorex += horasExFes * 2; } //BONO ASIGNACION VOLUNTARIA double totalBonoAV = base * 0.0077777 * resHorEx; double valorHorEx = (int) ((double) base * 0.0077777 * totalHorex); //TOTAL IMPONIBLE double totImp = base + grat + bonoAnt + bonoAd + bonoResp + totalBonoAV + totalBonCol + totalBon300 + valorHorEx; //DESCUENTO AFP int descAFP = Integer.parseInt(data[j][21]); int totalAFP = (int) (totImp * ((double) descAFP / 10000)); int sis = (int) (totImp * 0.0141); //DESCUENTO SALUD double descSalud = 0, totalSalud = 0; String salud; if (data[j][4].toLowerCase().compareTo("fonasa") == 0) { salud = "FONASA"; descSalud = Integer.parseInt(data[j][22]); totalSalud = (int) (totImp * ((double) descSalud / 10000)); } else { if (data[i][23].compareTo("") == 0) { salud = data[j][4]; } else { salud = data[j][23]; } descSalud = ((double) Integer.parseInt(data[j][24]) / 1000) * uf; totalSalud = descSalud; } //DESCUENTO CESANTIA int ces = (int) (totImp * 0.006); int cesEmp = (int) (totImp * 0.024); //DESCUENTOS LEGALES double descLegales = ces + totalSalud + totalAFP; //TOTAL TRIBUTABLE double totTrib = totImp - totalAFP - totalSalud - ces; int descRenta = 0; double totAux = 0; for (String[] imp2cat1 : imp2cat) { if (totTrib > Float.parseFloat(imp2cat1[0]) / 10 && totTrib <= Float.parseFloat(imp2cat1[1]) / 10) { descRenta = (int) (totTrib * Float.parseFloat(imp2cat1[2]) / 1000 - Float.parseFloat(imp2cat1[3]) / 100); totAux = totTrib - descRenta; break; } } //CAJA COMPENSACION int caja = Integer.parseInt(data[j][15]); //ASIGNACION FAMILIAR int af = Integer.parseInt(data[j][16]); //LIQ ALCANZADO double liqAl = totAux - caja; //COLACION int col = Integer.parseInt(data[j][6]); //TRANSPORTE int trans = Integer.parseInt(data[j][7]); //TOTAL NO IMPONIBLE int noImp = trans + col + af; //ANTICIPO ADELANTO PRESTAMOS int antic = Integer.parseInt(data[j][17]); int adel = Integer.parseInt(data[j][18]); int pres = Integer.parseInt(data[j][19]); int cuo = Integer.parseInt(data[j][20]); int cuoPres = 0; int cuores = Math.max(0, Integer.parseInt(data[j][26]) - 1); if (cuo != 0) { cuoPres = pres / cuo; } //DESCUENTOS MENSUALES int descMensuales = caja + antic + adel + cuoPres + descRenta; //TOTAL HABERES double totalHaberes = noImp + totImp; //TOTAL DESCUENTOS int totDesc = antic + adel + cuoPres + caja; //LIQUIDO double liq = liqAl + col + trans + af - antic - adel - cuoPres; //Agrego los datos de afp a listaAfp String afp = data[j][3]; if (mapAfp.get(afp) == null) { mapAfp.put(afp, new ArrayList<AfpInfo>()); } if (mapSalud.get(salud.toUpperCase()) == null) { mapSalud.put(salud.toUpperCase(), new ArrayList<SaludInfo>()); } //cambie descAfp por totalAfp AfpInfo info = new AfpInfo(data[j][0], data[j][1], totImp, (int) totalAFP, sis, totalAFP); mapAfp.get(afp).add(info); //cambie descSalud por totalSalud SaludInfo sInfo = new SaludInfo(data[j][0], data[j][1], totImp, (int) totalSalud); mapSalud.get(salud.toUpperCase()).add(sInfo); rowhead = sheet_rem.createRow((short) 5 + k + (i * 34)); rowhead.createCell(0).setCellValue(data[j][0]); rowhead.createCell(1).setCellValue(data[j][1]); rowhead.createCell(2).setCellValue(Integer.parseInt(data[j][28])); rowhead.createCell(3).setCellValue(base); rowhead.createCell(4).setCellValue(grat); rowhead.createCell(5).setCellValue(bonoAnt); rowhead.createCell(6).setCellValue(totalBon300); rowhead.createCell(7).setCellValue(totalBonoAV); rowhead.createCell(8).setCellValue(totalBonCol); rowhead.createCell(9).setCellValue(bonoAd); rowhead.createCell(10).setCellValue(valorHorEx); rowhead.createCell(11).setCellValue(totImp); rowhead.createCell(12).setCellValue(data[j][0]); rowhead.createCell(13).setCellValue(data[j][1]); rowhead.createCell(14).setCellValue(col); rowhead.createCell(15).setCellValue(trans); rowhead.createCell(16).setCellValue(af); rowhead.createCell(17).setCellValue(noImp); rowhead.createCell(18).setCellValue(totalHaberes); rowhead.createCell(19).setCellValue(totalAFP); rowhead.createCell(20).setCellValue(totalSalud); rowhead.createCell(21).setCellValue(ces); rowhead.createCell(22).setCellValue(cesEmp); rowhead.createCell(23).setCellValue(descLegales); rowhead.createCell(24).setCellValue(data[j][0]); rowhead.createCell(25).setCellValue(data[j][1]); rowhead.createCell(26).setCellValue(descRenta); rowhead.createCell(27).setCellValue(caja); rowhead.createCell(28).setCellValue(antic); rowhead.createCell(29).setCellValue(adel); rowhead.createCell(30).setCellValue(cuoPres); rowhead.createCell(31).setCellValue(descMensuales); rowhead.createCell(32).setCellValue(totDesc); rowhead.createCell(33).setCellValue(liq); rowhead.createCell(34).setCellValue(sis); } } //Totales rowhead = sheet_rem.createRow(66); rowhead.createCell(2).setCellFormula("SUM(C6:C34,C40:C66)"); rowhead.createCell(3).setCellFormula("SUM(D6:D34,D40:D66)"); rowhead.createCell(4).setCellFormula("SUM(E6:E34,E40:E66)"); rowhead.createCell(5).setCellFormula("SUM(F6:F34,F40:F66)"); rowhead.createCell(6).setCellFormula("SUM(G6:G34,G40:G66)"); rowhead.createCell(7).setCellFormula("SUM(H6:H34,H40:H66)"); rowhead.createCell(8).setCellFormula("SUM(I6:I34,I40:I66)"); rowhead.createCell(9).setCellFormula("SUM(J6:J34,J40:J66)"); rowhead.createCell(10).setCellFormula("SUM(K6:K34,K40:K66)"); rowhead.createCell(11).setCellFormula("SUM(L6:L34,L40:L66)"); rowhead.createCell(14).setCellFormula("SUM(O6:O34,O40:O66)"); rowhead.createCell(15).setCellFormula("SUM(P6:P34,P40:P66)"); rowhead.createCell(16).setCellFormula("SUM(Q6:Q34,Q40:Q66)"); rowhead.createCell(17).setCellFormula("SUM(R6:R34,R40:R66)"); rowhead.createCell(18).setCellFormula("SUM(S6:S34,S40:S66)"); rowhead.createCell(19).setCellFormula("SUM(T6:T34,T40:T66)"); rowhead.createCell(20).setCellFormula("SUM(U6:U34,U40:U66)"); rowhead.createCell(21).setCellFormula("SUM(V6:V34,V40:V66)"); rowhead.createCell(22).setCellFormula("SUM(W6:W34,W40:W66)"); rowhead.createCell(23).setCellFormula("SUM(X6:X34,X40:X66)"); rowhead.createCell(26).setCellFormula("SUM(AA6:AA34,AA40:AA66)"); rowhead.createCell(27).setCellFormula("SUM(AB6:AB34,AB40:AB66)"); rowhead.createCell(28).setCellFormula("SUM(AC6:AC34,AC40:AC66)"); rowhead.createCell(29).setCellFormula("SUM(AD6:AD34,AD40:AD66)"); rowhead.createCell(30).setCellFormula("SUM(AE6:AE34,AE40:AE66)"); rowhead.createCell(31).setCellFormula("SUM(AF6:AF34,AF40:AF66)"); rowhead.createCell(32).setCellFormula("SUM(AG6:AG34,AG40:AG66)"); rowhead.createCell(33).setCellFormula("SUM(AH6:AH34,AH40:AH66)"); rowhead.createCell(34).setCellFormula("SUM(AI6:AI34,AI40:AI66)"); //Hoja detalle afp j = 0; //Primera linea rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(3) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(3).setCellStyle(style); rowhead.createCell(11).setCellValue("pag:1"); //Segunda linea rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(2).setCellValue("Mes: " + month); rowhead.createCell(4).setCellValue("Ao: " + year); rowhead.createCell(6).setCellValue("UF: $" + uf); rowhead.createCell(9).setCellValue("UTM: $" + utm); j += 2; for (String key : mapAfp.keySet()) { List<AfpInfo> aux = mapAfp.get(key); rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(5).setCellValue("AFP " + key); rowhead.getCell(5).setCellStyle(style); rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(0).setCellValue("rut"); rowhead.createCell(1).setCellValue("nom_trab"); rowhead.createCell(2).setCellValue("tot_h_imp"); rowhead.createCell(3).setCellValue("desc_afp"); rowhead.createCell(4).setCellValue("sis"); rowhead.createCell(5).setCellValue("tot_afp"); rowhead.createCell(8).setCellValue("afiliados: " + aux.size()); for (AfpInfo usuario : aux) { rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(0).setCellValue(usuario.getRut()); rowhead.createCell(1).setCellValue(usuario.getNombre()); rowhead.createCell(2).setCellValue(usuario.getTotalImp()); rowhead.createCell(3).setCellValue(usuario.getDescAfp()); rowhead.createCell(4).setCellValue(usuario.getSis()); rowhead.createCell(5).setCellValue(usuario.getTotAfp()); } rowhead = sheet_afp.createRow((short) j++); int totalTrab = aux.size(); rowhead.createCell(2).setCellFormula("SUM(C" + (j - totalTrab) + ":C" + (j - 1) + ")"); rowhead.createCell(3).setCellFormula("SUM(D" + (j - totalTrab) + ":D" + (j - 1) + ")"); rowhead.createCell(4).setCellFormula("SUM(E" + (j - totalTrab) + ":E" + (j - 1) + ")"); rowhead.createCell(5).setCellFormula("SUM(F" + (j - totalTrab) + ":F" + (j - 1) + ")"); j++; } //Hoja detalle salud j = 0; //Primera linea rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(3) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(3).setCellStyle(style); rowhead.createCell(11).setCellValue("pag:1"); //Segunda linea rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(2).setCellValue("Mes: " + month); rowhead.createCell(4).setCellValue("Ao: " + year); rowhead.createCell(6).setCellValue("UF: $" + uf); rowhead.createCell(9).setCellValue("UTM: $" + utm); j += 2; for (String key : mapSalud.keySet()) { List<SaludInfo> aux = mapSalud.get(key); rowhead = sheet_salud.createRow((short) j++); if (key.toLowerCase().compareTo("fonasa") == 0 || key.toLowerCase().compareTo("sin info") == 0) { rowhead.createCell(5).setCellValue(key); } else { rowhead.createCell(5).setCellValue("Isapre " + key); } rowhead.getCell(5).setCellStyle(style); rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(0).setCellValue("rut"); rowhead.createCell(1).setCellValue("nom_trab"); rowhead.createCell(2).setCellValue("tot_h_imp"); rowhead.createCell(3).setCellValue("desc_salud"); rowhead.createCell(8).setCellValue("afiliados: " + aux.size()); for (SaludInfo usuario : aux) { rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(0).setCellValue(usuario.getRut()); rowhead.createCell(1).setCellValue(usuario.getNombre()); rowhead.createCell(2).setCellValue(usuario.getTotImp()); rowhead.createCell(3).setCellValue(usuario.getDescSalud()); } rowhead = sheet_salud.createRow((short) j++); int totalTrab = aux.size(); rowhead.createCell(2).setCellFormula("SUM(C" + (j - totalTrab) + ":C" + (j - 1) + ")"); rowhead.createCell(3).setCellFormula("SUM(D" + (j - totalTrab) + ":D" + (j - 1) + ")"); j++; } FileOutputStream fileOut; fileOut = new FileOutputStream(file); workbook.write(fileOut); fileOut.close(); JOptionPane.showMessageDialog(null, "Libro de remuneraciones generado con xito", "Operacin exitosa", JOptionPane.INFORMATION_MESSAGE); } catch (IOException ie) { JOptionPane.showMessageDialog(null, "El archivo est siendo ocupado\nCierre el archivo y vuelva a intentarlo", "Error", JOptionPane.INFORMATION_MESSAGE); ie.printStackTrace(); } catch (Exception e) { JOptionPane.showMessageDialog(null, "Error al crear libro de remuneraciones", "Error", JOptionPane.INFORMATION_MESSAGE); e.printStackTrace(); } } }; runnable.start(); }
From source file:controladores4.controladorReportes.java
public void GenerarLibroRemuneracionesAtrasado(final String year, final String month) { DateFormat date2 = new SimpleDateFormat("dd-MMMM-yyyy"); NumberFormat FORMAT = NumberFormat.getCurrencyInstance(); DecimalFormatSymbols dfs = new DecimalFormatSymbols(); Thread runnable;/*from w ww . j av a 2 s . c o m*/ runnable = new Thread() { public void run() { try { DateFormat yearDate = new SimpleDateFormat("yyyy"); DateFormat monthDate = new SimpleDateFormat("M"); // String month2 = monthDate.format(new Date()); // String year2 = yearDate.format(new Date()); String per = perDate.format(new Date()); String fecha = formatDate.format(new Date()); controladores.controladorPrincipal miControlador = new controladorPrincipal(); modelos3.modeloRemuneraciones remu = new modeloRemuneraciones(); double uf = remu.obtenerUF(); double utm = remu.obtenerUTM(); String[][] imp2cat = remu.obtenerTablaImpuesto(); //Listas para detalle afp y salud HashMap<String, ArrayList<AfpInfo>> mapAfp = new HashMap<>(); HashMap<String, ArrayList<SaludInfo>> mapSalud = new HashMap<>(); List<AfpInfo> listaAfp = new ArrayList<>(); List<SaludInfo> listaSalud = new ArrayList<>(); modelos.modeloEmpleados emp = new modeloEmpleados(); String[][] data = emp.obtenerRemuneraciones2(Integer.parseInt(month), Integer.parseInt(year)); String path = "Libro de remuneraciones - " + month + "-" + year + ".xls"; File file = new File(path); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBold(true); font.setFontName("Calibri"); font.setFontHeightInPoints((short) 11); style.setFont(font); HSSFSheet sheet_rem = workbook.createSheet("Libro_Remuneraciones"); HSSFSheet sheet_afp = workbook.createSheet("Detalle_AFP"); HSSFSheet sheet_salud = workbook.createSheet("Detalle_Salud"); HSSFRow rowhead; //J para recorrer los datos de los trabajadores int j = 0; int numTrab = data.length; for (int i = 0; i < 2; i++) { //Primera linea rowhead = sheet_rem.createRow((short) 0 + i * 34); rowhead.createCell(0).setCellValue("parte:1"); rowhead.createCell(3) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(3).setCellStyle(style); rowhead.createCell(11).setCellValue("pag:" + (i + 1)); rowhead.createCell(12).setCellValue("parte:2"); rowhead.createCell(15) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(15).setCellStyle(style); rowhead.createCell(23).setCellValue("pag:" + (i + 1)); rowhead.createCell(24).setCellValue("parte:3"); rowhead.createCell(27) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(27).setCellStyle(style); rowhead.createCell(35).setCellValue("pag:" + (i + 1)); //Segunda linea rowhead = sheet_rem.createRow((short) 1 + (i * 34)); rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(2).setCellValue("Mes: " + month); rowhead.createCell(4).setCellValue("Ao: " + year); rowhead.createCell(6).setCellValue("UF: $" + uf); rowhead.createCell(9).setCellValue("UTM: $" + utm); rowhead.createCell(12).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(14).setCellValue("Mes: " + month); rowhead.createCell(16).setCellValue("Ao: " + year); rowhead.createCell(18).setCellValue("UF: $" + uf); rowhead.createCell(21).setCellValue("UTM: $" + utm); rowhead.createCell(24).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(26).setCellValue("Mes: " + month); rowhead.createCell(28).setCellValue("Ao: " + year); rowhead.createCell(30).setCellValue("UF: $" + uf); rowhead.createCell(33).setCellValue("UTM: $" + utm); //Tercera linea rowhead = sheet_rem.createRow((short) 4 + (i * 34)); rowhead.createCell(0).setCellValue("rut_trabajador"); rowhead.createCell(1).setCellValue("nombre_trabajador"); rowhead.createCell(2).setCellValue("dias_trab"); rowhead.createCell(3).setCellValue("sueldo_base_p"); rowhead.createCell(4).setCellValue("gratificacion"); rowhead.createCell(5).setCellValue("bono_aos"); rowhead.createCell(6).setCellValue("bono_horas"); rowhead.createCell(7).setCellValue("bono_asig_vol"); rowhead.createCell(8).setCellValue("bono_ad"); rowhead.createCell(9).setCellValue("otros_bonos"); rowhead.createCell(10).setCellValue("horas_extra"); rowhead.createCell(11).setCellValue("tot_h_imp"); rowhead.createCell(12).setCellValue("rut_trabajador"); rowhead.createCell(13).setCellValue("nombre_trabajador"); rowhead.createCell(14).setCellValue("colacion"); rowhead.createCell(15).setCellValue("movilizacion"); rowhead.createCell(16).setCellValue("asig_familiar"); rowhead.createCell(17).setCellValue("tot_h_no_imp"); rowhead.createCell(18).setCellValue("tot_haberes"); rowhead.createCell(19).setCellValue("desc_afp"); rowhead.createCell(20).setCellValue("desc_salud"); rowhead.createCell(21).setCellValue("afc_trab"); rowhead.createCell(22).setCellValue("afc_empl"); rowhead.createCell(23).setCellValue("tot_desc_leg"); rowhead.createCell(24).setCellValue("rut_trabajador"); rowhead.createCell(25).setCellValue("nombre_trabajador"); rowhead.createCell(26).setCellValue("imp_renta"); rowhead.createCell(27).setCellValue("caja_comp"); rowhead.createCell(28).setCellValue("anticipo"); rowhead.createCell(29).setCellValue("adelanto"); rowhead.createCell(30).setCellValue("prestamo"); rowhead.createCell(31).setCellValue("tot_desc_men"); rowhead.createCell(32).setCellValue("tot_desc"); rowhead.createCell(33).setCellValue("total_a_pago"); rowhead.createCell(34).setCellValue("sis"); //Cuarta linea for (int k = 0; k < 28; k++, j++) { if (j == numTrab) { break; } int base = Integer.parseInt(data[j][2]) * Integer.parseInt(data[j][28]) / 30; //GRATIFICACION int grat = (int) (base * 0.25); //BONO ANTIGUEDAD int bonoAnt = miControlador.obtenerBonoAnt(data[j][5]); //BONO 300 int totalBon300 = Integer.parseInt(data[j][27]); //BONO ADICIONAL int bonoAd = Integer.parseInt(data[j][11]); //BONO RESPONSABILIDAD int bonoResp = 0; //BONO ADICIONAL double bonoCol1 = Double.parseDouble(data[j][8]); double bonoCol30 = Double.parseDouble(data[j][9]); double bonoCol = bonoCol1 + bonoCol30 / 2; int totalBonCol = (int) Math.round(((double) base * 0.0077777) * bonoCol); //HORAS EXTRA double horasExNor = Double.parseDouble(data[j][12]); double horasExFes = Double.parseDouble(data[j][13]); double cantHorEx = 0; //total de horas extras normales = 1; festivas = 2 double totalHorex = 0; double resHorEx = 0; if (horasExNor > 45) { cantHorEx = 45; totalHorex = 45; resHorEx = horasExNor - 45; } else { cantHorEx = horasExNor; totalHorex = cantHorEx; } if (cantHorEx + horasExFes > 45) { resHorEx = resHorEx + (horasExFes - 45 + cantHorEx) * 2; totalHorex = 45 - cantHorEx; cantHorEx = 45; } else { cantHorEx += horasExFes; totalHorex += horasExFes * 2; } //BONO ASIGNACION VOLUNTARIA double totalBonoAV = base * 0.0077777 * resHorEx; double valorHorEx = (int) ((double) base * 0.0077777 * totalHorex); //TOTAL IMPONIBLE double totImp = base + grat + bonoAnt + bonoAd + bonoResp + totalBonoAV + totalBonCol + totalBon300 + valorHorEx; //DESCUENTO AFP int descAFP = Integer.parseInt(data[j][21]); int totalAFP = (int) (totImp * ((double) descAFP / 10000)); int sis = (int) (totImp * 0.0141); //DESCUENTO SALUD double descSalud = 0, totalSalud = 0; String salud; if (data[j][4].toLowerCase().compareTo("fonasa") == 0) { salud = "FONASA"; descSalud = Integer.parseInt(data[j][22]); totalSalud = (int) (totImp * ((double) descSalud / 10000)); } else { if (data[i][23].compareTo("") == 0) { salud = data[j][4]; } else { salud = data[j][23]; } descSalud = ((double) Integer.parseInt(data[j][24]) / 1000) * uf; totalSalud = descSalud; } //DESCUENTO CESANTIA int ces = (int) (totImp * 0.006); int cesEmp = (int) (totImp * 0.024); //DESCUENTOS LEGALES double descLegales = ces + totalSalud + totalAFP; //TOTAL TRIBUTABLE double totTrib = totImp - totalAFP - totalSalud - ces; int descRenta = 0; double totAux = 0; for (String[] imp2cat1 : imp2cat) { if (totTrib > Float.parseFloat(imp2cat1[0]) / 10 && totTrib <= Float.parseFloat(imp2cat1[1]) / 10) { descRenta = (int) (totTrib * Float.parseFloat(imp2cat1[2]) / 1000 - Float.parseFloat(imp2cat1[3]) / 100); totAux = totTrib - descRenta; break; } } //CAJA COMPENSACION int caja = Integer.parseInt(data[j][15]); //ASIGNACION FAMILIAR int af = Integer.parseInt(data[j][16]); //LIQ ALCANZADO double liqAl = totAux - caja; //COLACION int col = Integer.parseInt(data[j][6]); //TRANSPORTE int trans = Integer.parseInt(data[j][7]); //TOTAL NO IMPONIBLE int noImp = trans + col + af; //ANTICIPO ADELANTO PRESTAMOS int antic = Integer.parseInt(data[j][17]); int adel = Integer.parseInt(data[j][18]); int pres = Integer.parseInt(data[j][19]); int cuo = Integer.parseInt(data[j][20]); int cuoPres = 0; int cuores = Math.max(0, Integer.parseInt(data[j][26]) - 1); if (cuo != 0) { cuoPres = pres / cuo; } //DESCUENTOS MENSUALES int descMensuales = caja + antic + adel + cuoPres + descRenta; //TOTAL HABERES double totalHaberes = noImp + totImp; //TOTAL DESCUENTOS int totDesc = antic + adel + cuoPres + caja; //LIQUIDO double liq = liqAl + col + trans + af - antic - adel - cuoPres; //Agrego los datos de afp a listaAfp String afp = data[j][3]; if (mapAfp.get(afp) == null) { mapAfp.put(afp, new ArrayList<AfpInfo>()); } if (mapSalud.get(salud.toUpperCase()) == null) { mapSalud.put(salud.toUpperCase(), new ArrayList<SaludInfo>()); } AfpInfo info = new AfpInfo(data[j][0], data[j][1], totImp, descAFP, sis, totalAFP); mapAfp.get(afp).add(info); SaludInfo sInfo = new SaludInfo(data[j][0], data[j][1], totImp, descSalud); mapSalud.get(salud.toUpperCase()).add(sInfo); rowhead = sheet_rem.createRow((short) 5 + k + (i * 34)); rowhead.createCell(0).setCellValue(data[j][0]); rowhead.createCell(1).setCellValue(data[j][1]); rowhead.createCell(2).setCellValue(Integer.parseInt(data[j][28])); rowhead.createCell(3).setCellValue(base); rowhead.createCell(4).setCellValue(grat); rowhead.createCell(5).setCellValue(bonoAnt); rowhead.createCell(6).setCellValue(totalBon300); rowhead.createCell(7).setCellValue(totalBonoAV); rowhead.createCell(8).setCellValue(totalBonCol); rowhead.createCell(9).setCellValue(bonoAd); rowhead.createCell(10).setCellValue(valorHorEx); rowhead.createCell(11).setCellValue(totImp); rowhead.createCell(12).setCellValue(data[j][0]); rowhead.createCell(13).setCellValue(data[j][1]); rowhead.createCell(14).setCellValue(col); rowhead.createCell(15).setCellValue(trans); rowhead.createCell(16).setCellValue(af); rowhead.createCell(17).setCellValue(noImp); rowhead.createCell(18).setCellValue(totalHaberes); rowhead.createCell(19).setCellValue(totalAFP); rowhead.createCell(20).setCellValue(totalSalud); rowhead.createCell(21).setCellValue(ces); rowhead.createCell(22).setCellValue(cesEmp); rowhead.createCell(23).setCellValue(descLegales); rowhead.createCell(24).setCellValue(data[j][0]); rowhead.createCell(25).setCellValue(data[j][1]); rowhead.createCell(26).setCellValue(descRenta); rowhead.createCell(27).setCellValue(caja); rowhead.createCell(28).setCellValue(antic); rowhead.createCell(29).setCellValue(adel); rowhead.createCell(30).setCellValue(cuoPres); rowhead.createCell(31).setCellValue(descMensuales); rowhead.createCell(32).setCellValue(totDesc); rowhead.createCell(33).setCellValue(liq); rowhead.createCell(34).setCellValue(sis); } } //Totales rowhead = sheet_rem.createRow(66); rowhead.createCell(2).setCellFormula("SUM(C6:C34,C40:C66)"); rowhead.createCell(3).setCellFormula("SUM(D6:D34,D40:D66)"); rowhead.createCell(4).setCellFormula("SUM(E6:E34,E40:E66)"); rowhead.createCell(5).setCellFormula("SUM(F6:F34,F40:F66)"); rowhead.createCell(6).setCellFormula("SUM(G6:G34,G40:G66)"); rowhead.createCell(7).setCellFormula("SUM(H6:H34,H40:H66)"); rowhead.createCell(8).setCellFormula("SUM(I6:I34,I40:I66)"); rowhead.createCell(9).setCellFormula("SUM(J6:J34,J40:J66)"); rowhead.createCell(10).setCellFormula("SUM(K6:K34,K40:K66)"); rowhead.createCell(11).setCellFormula("SUM(L6:L34,L40:L66)"); rowhead.createCell(14).setCellFormula("SUM(O6:O34,O40:O66)"); rowhead.createCell(15).setCellFormula("SUM(P6:P34,P40:P66)"); rowhead.createCell(16).setCellFormula("SUM(Q6:Q34,Q40:Q66)"); rowhead.createCell(17).setCellFormula("SUM(R6:R34,R40:R66)"); rowhead.createCell(18).setCellFormula("SUM(S6:S34,S40:S66)"); rowhead.createCell(19).setCellFormula("SUM(T6:T34,T40:T66)"); rowhead.createCell(20).setCellFormula("SUM(U6:U34,U40:U66)"); rowhead.createCell(21).setCellFormula("SUM(V6:V34,V40:V66)"); rowhead.createCell(22).setCellFormula("SUM(W6:W34,W40:W66)"); rowhead.createCell(23).setCellFormula("SUM(X6:X34,X40:X66)"); rowhead.createCell(26).setCellFormula("SUM(AA6:AA34,AA40:AA66)"); rowhead.createCell(27).setCellFormula("SUM(AB6:AB34,AB40:AB66)"); rowhead.createCell(28).setCellFormula("SUM(AC6:AC34,AC40:AC66)"); rowhead.createCell(29).setCellFormula("SUM(AD6:AD34,AD40:AD66)"); rowhead.createCell(30).setCellFormula("SUM(AE6:AE34,AE40:AE66)"); rowhead.createCell(31).setCellFormula("SUM(AF6:AF34,AF40:AF66)"); rowhead.createCell(32).setCellFormula("SUM(AG6:AG34,AG40:AG66)"); rowhead.createCell(33).setCellFormula("SUM(AH6:AH34,AH40:AH66)"); rowhead.createCell(34).setCellFormula("SUM(AI6:AI34,AI40:AI66)"); //Hoja detalle afp j = 0; //Primera linea rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(3) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(3).setCellStyle(style); rowhead.createCell(11).setCellValue("pag:1"); //Segunda linea rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(2).setCellValue("Mes: " + month); rowhead.createCell(4).setCellValue("Ao: " + year); rowhead.createCell(6).setCellValue("UF: $" + uf); rowhead.createCell(9).setCellValue("UTM: $" + utm); j += 2; for (String key : mapAfp.keySet()) { List<AfpInfo> aux = mapAfp.get(key); rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(5).setCellValue("AFP " + key); rowhead.getCell(5).setCellStyle(style); rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(0).setCellValue("rut"); rowhead.createCell(1).setCellValue("nom_trab"); rowhead.createCell(2).setCellValue("tot_h_imp"); rowhead.createCell(3).setCellValue("desc_afp"); rowhead.createCell(4).setCellValue("sis"); rowhead.createCell(5).setCellValue("tot_afp"); rowhead.createCell(8).setCellValue("afiliados: " + aux.size()); for (AfpInfo usuario : aux) { rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(0).setCellValue(usuario.getRut()); rowhead.createCell(1).setCellValue(usuario.getNombre()); rowhead.createCell(2).setCellValue(usuario.getTotalImp()); rowhead.createCell(3).setCellValue(usuario.getDescAfp()); rowhead.createCell(4).setCellValue(usuario.getSis()); rowhead.createCell(5).setCellValue(usuario.getTotAfp()); } rowhead = sheet_afp.createRow((short) j++); int totalTrab = aux.size(); rowhead.createCell(2).setCellFormula("SUM(C" + (j - totalTrab) + ":C" + (j - 1) + ")"); rowhead.createCell(3).setCellFormula("SUM(D" + (j - totalTrab) + ":D" + (j - 1) + ")"); rowhead.createCell(4).setCellFormula("SUM(E" + (j - totalTrab) + ":E" + (j - 1) + ")"); rowhead.createCell(5).setCellFormula("SUM(F" + (j - totalTrab) + ":F" + (j - 1) + ")"); j++; } //Hoja detalle salud j = 0; //Primera linea rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(3) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(3).setCellStyle(style); rowhead.createCell(11).setCellValue("pag:1"); //Segunda linea rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(2).setCellValue("Mes: " + month); rowhead.createCell(4).setCellValue("Ao: " + year); rowhead.createCell(6).setCellValue("UF: $" + uf); rowhead.createCell(9).setCellValue("UTM: $" + utm); j += 2; for (String key : mapSalud.keySet()) { List<SaludInfo> aux = mapSalud.get(key); rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(5).setCellValue("Isapre " + key); rowhead.getCell(5).setCellStyle(style); rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(0).setCellValue("rut"); rowhead.createCell(1).setCellValue("nom_trab"); rowhead.createCell(2).setCellValue("tot_h_imp"); rowhead.createCell(3).setCellValue("desc_salud"); rowhead.createCell(8).setCellValue("afiliados: " + aux.size()); for (SaludInfo usuario : aux) { rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(0).setCellValue(usuario.getRut()); rowhead.createCell(1).setCellValue(usuario.getNombre()); rowhead.createCell(2).setCellValue(usuario.getTotImp()); rowhead.createCell(3).setCellValue(usuario.getDescSalud()); } rowhead = sheet_salud.createRow((short) j++); int totalTrab = aux.size(); rowhead.createCell(2).setCellFormula("SUM(C" + (j - totalTrab) + ":C" + (j - 1) + ")"); rowhead.createCell(3).setCellFormula("SUM(D" + (j - totalTrab) + ":D" + (j - 1) + ")"); j++; } FileOutputStream fileOut; fileOut = new FileOutputStream(file); workbook.write(fileOut); fileOut.close(); JOptionPane.showMessageDialog(null, "Libro de remuneraciones generado con xito", "Operacin exitosa", JOptionPane.INFORMATION_MESSAGE); } catch (IOException ie) { JOptionPane.showMessageDialog(null, "El archivo est siendo ocupado\nCierre el archivo y vuelva a intentarlo", "Error", JOptionPane.INFORMATION_MESSAGE); ie.printStackTrace(); } catch (Exception e) { JOptionPane.showMessageDialog(null, "Error al crear libro de remuneraciones", "Error", JOptionPane.INFORMATION_MESSAGE); e.printStackTrace(); } } }; runnable.start(); }
From source file:de.viaboxx.nlstools.formats.MBExcelPersistencer.java
License:Apache License
private void initStyles(HSSFWorkbook wb) { // cache styles used to write text into cells HSSFCellStyle style = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBold(true); style.setFont(font);/* w ww .ja v a 2 s. co m*/ styles.put(STYLE_BOLD, style); style = wb.createCellStyle(); font = wb.createFont(); font.setItalic(true); style.setFont(font); styles.put(STYLE_ITALIC, style); style = wb.createCellStyle(); font = wb.createFont(); font.setItalic(true); font.setColor(Font.COLOR_RED); style.setFont(font); styles.put(STYLE_REVIEW, style); style = wb.createCellStyle(); style.setFillPattern(FillPatternType.FINE_DOTS); style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex()); style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex()); styles.put(STYLE_MISSING, style); style = wb.createCellStyle(); style.setFillPattern(FillPatternType.FINE_DOTS); style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex()); style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex()); style.setFont(font); styles.put(STYLE_MISSING_REVIEW, style); style = wb.createCellStyle(); HSSFCreationHelper createHelper = wb.getCreationHelper(); style.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-dd-mm hh:mm")); styles.put(STYLE_DATETIME, style); }
From source file:excel.PoiWriteExcelFile.java
public static int generarReporte() { //Calendar cal=Calendar.getInstance(); Calendar cal = WorkMonitorUI.instante; try {//from w w w .j av a 2s . c om FileOutputStream fileOut = new FileOutputStream("HH_" + instante.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()) .toUpperCase() + "_" + persona.getNombre().toUpperCase().charAt(0) + "." + persona.getApellido().toUpperCase() + "_" + instante.get(Calendar.YEAR) + ".xls"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet worksheet = workbook.createSheet( cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()).toUpperCase() + "-" + cal.get(Calendar.YEAR)); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Calibri"); font.setItalic(false); font.setBold(true); font.setColor(HSSFColor.BLACK.index); cellStyle.setFont(font); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle diasStyle = workbook.createCellStyle(); diasStyle.setFillForegroundColor(HSSFColor.SEA_GREEN.index); diasStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setFontName("Calibri"); font.setItalic(false); font.setBold(true); font.setColor(HSSFColor.WHITE.index); diasStyle.setFont(font); diasStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); diasStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); diasStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle schedStyle = workbook.createCellStyle(); schedStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); schedStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font3 = workbook.createFont(); font3.setFontHeightInPoints((short) 11); font3.setFontName("Calibri"); font3.setItalic(false); font3.setColor(HSSFColor.BLACK.index); schedStyle.setFont(font3); schedStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); schedStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); schedStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle workdayStyle = workbook.createCellStyle(); //workdayStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); workdayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); workdayStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); workdayStyle.setWrapText(true); HSSFFont font2 = workbook.createFont(); font2.setFontHeightInPoints((short) 8); font2.setFontName("Serif"); font2.setItalic(false); //font2.setColor(HSSFColor.YELLOW.index); workdayStyle.setFont(font2); workdayStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle weekendStyle = workbook.createCellStyle(); weekendStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); weekendStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); weekendStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); weekendStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); weekendStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle horarioStyle = workbook.createCellStyle(); horarioStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); horarioStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); horarioStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); horarioStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); horarioStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font4 = workbook.createFont(); font4.setFontHeightInPoints((short) 10); font4.setFontName("Serif"); font4.setItalic(false); font4.setBold(true); //font2.setColor(HSSFColor.YELLOW.index); horarioStyle.setFont(font4); // index from 0,0... cell A1 is cell(0,0) HSSFRow row1 = worksheet.createRow((short) 0); row1.setHeight((short) 500); //System.out.println("cal.get(Calendar.YEAR)="+cal.get(Calendar.YEAR)); HSSFCell cellA1 = row1.createCell((short) 0); cellA1.setCellValue( cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()).toUpperCase() + "-" + cal.get(Calendar.YEAR)); cellA1.setCellStyle(cellStyle); HSSFRow row2 = worksheet.createRow((short) 1); HSSFCell cellA4 = row2.createCell((short) 0); cellA4.setCellValue("Horario"); cellA4.setCellStyle(horarioStyle); //row2.setHeight((short)500); HSSFRow row3 = worksheet.createRow((short) 2); HSSFCell cellA3 = row3.createCell((short) 0); cellA3.setCellValue("Inicio - Trmino"); cellA3.setCellStyle(diasStyle); Calendar hora = Calendar.getInstance(); hora.set(Calendar.HOUR_OF_DAY, 9); hora.set(Calendar.MINUTE, 0); hora.set(Calendar.SECOND, 0); SimpleDateFormat sdf = new SimpleDateFormat("HH:mm"); HSSFCell cellXn; for (int i = 0; i < 29; ++i) { HSSFRow row = worksheet.createRow((short) i + 3); row.setHeight((short) 500); cellXn = row.createCell((short) 0); String horaIni = sdf.format(hora.getTime()); hora.add(Calendar.MINUTE, 30); String horaFin = sdf.format(hora.getTime()); cellXn.setCellValue(horaIni + " - " + horaFin); cellXn.setCellStyle(schedStyle); } System.out.println("cal.get(Calendar.MONTH)1=" + cal.get(Calendar.MONTH)); cal.add(Calendar.DAY_OF_MONTH, -cal.get(Calendar.DAY_OF_MONTH) + 1); int diasMes = cal.getActualMaximum(Calendar.DAY_OF_MONTH); System.out.println("cal.get(Calendar.MONTH)2=" + cal.get(Calendar.MONTH)); sdf = new SimpleDateFormat("EEEE d"); System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)1=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); for (int i = 0; i < diasMes; ++i) { cellXn = row2.createCell((short) i + 1); String dia = sdf.format(cal.getTime()); dia = Character.toUpperCase(dia.charAt(0)) + dia.substring(1); cellXn.setCellValue(dia); cellXn.setCellStyle(horarioStyle); //System.out.println("cal.get(Calendar.DAY_OF_MONTH)="+cal.get(Calendar.DAY_OF_MONTH)); cal.add(Calendar.DAY_OF_MONTH, 1); } for (int i = 0; i < diasMes; ++i) { cellXn = row3.createCell((short) i + 1); cellXn.setCellValue("Descripcin"); cellXn.setCellStyle(diasStyle); } System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)2=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); // Retroceder mes para que quede como estaba cal.add(Calendar.MONTH, -1); //cal.add(Calendar.DAY_OF_MONTH, -1); System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)3=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); HhDao hhDao = new HhDao(); Object[][] hh = new Object[29][cal.getActualMaximum(Calendar.DAY_OF_MONTH)]; hh = hhDao.getByMes(WorkMonitorUI.persona.getId(), cal.getTime()); cal.set(Calendar.DAY_OF_MONTH, 1); Sheet sheet = workbook.getSheetAt(0); sdf = new SimpleDateFormat("EEEE"); HSSFPatriarch _drawing = (HSSFPatriarch) sheet.createDrawingPatriarch(); CreationHelper factory = workbook.getCreationHelper(); for (int i = 0; i < 29; ++i) { Row r = sheet.getRow(i + 3); for (int j = 0; j < diasMes; ++j) { if (hh[i][j].toString() != "") { cellXn = (HSSFCell) r.createCell((short) j + 1); Hh _hh = (Hh) hh[i][j]; cellXn.setCellValue( _hh.getTarea().getNombre().trim() + ": " + _hh.getActividad().getNombre().trim()); HSSFAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5); org.apache.poi.ss.usermodel.Comment comment = _drawing.createComment(anchor); String comentario = _hh.getTarea().getComentario().toLowerCase(); if (_hh.getComentario() != null) comentario = comentario + _hh.getComentario().toLowerCase(); RichTextString str = factory.createRichTextString(comentario); comment.setString(str); cellXn.setCellComment(comment); } else { cellXn = (HSSFCell) r.createCell((short) j + 1); cellXn.setCellValue(""); } //System.out.println("sdf.format(cal.getTime())="+sdf.format(cal.getTime())); if (Arrays.asList("sbado", "domingo").contains(sdf.format(cal.getTime()))) cellXn.setCellStyle(weekendStyle); else cellXn.setCellStyle(workdayStyle); sheet.setColumnWidth(j, 5000); cal.add(Calendar.DAY_OF_MONTH, 1); //sheet.autoSizeColumn(j); } // Retroceder mes para que quede como estaba cal.add(Calendar.MONTH, -1); System.out.println("cal.get(Calendar.MONTH)3=" + cal.get(Calendar.MONTH)); cal.set(Calendar.DAY_OF_MONTH, 1); } sheet.setColumnWidth(diasMes, 5000); WorkMonitorUI.instante = Calendar.getInstance(); sheet.setColumnWidth(0, 5000); sheet.createFreezePane(1, 3); // Freeze just one row //sheet.createFreezePane( 0, 1, 0, 1 ); workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); return -1; } catch (IOException e) { e.printStackTrace(); return -2; } return 1; }
From source file:io.vertigo.quarto.plugins.export.xls.XLSExporter.java
License:Apache License
private static HSSFCellStyle createHeaderCellStyle(final HSSFWorkbook workbook) { final HSSFCellStyle cellStyle = workbook.createCellStyle(); final HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName("Arial"); font.setBold(true); cellStyle.setFont(font);/*from w ww . j av a2 s .c o m*/ cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setVerticalAlignment(VerticalAlignment.JUSTIFY); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(HSSFColorPredefined.GREY_40_PERCENT.getIndex()); cellStyle.setAlignment(HorizontalAlignment.CENTER); return cellStyle; }
From source file:net.algem.planning.export.PlanningExportService.java
License:Open Source License
/** * * @param wb workbook/*from w ww . jav a 2s.co m*/ * @return a map, each key-value composed of a style name and a cell style */ private Map<String, CellStyle> createStyles(HSSFWorkbook wb) { Map<String, CellStyle> styles = new HashMap<>(); HSSFFont nf = wb.createFont(); nf.setFontName("monospace"); HSSFFont bf = wb.createFont(); bf.setFontName("monospace"); bf.setBold(true); HSSFFont sf = wb.createFont(); sf.setFontHeightInPoints((short) 8); sf.setFontName("monospace"); CellStyle style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setWrapText(true); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styles.put("header", style); //LEFT HEADER style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setWrapText(true); style.setFont(bf); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); style.setBorderBottom(CellStyle.BORDER_DOTTED); styles.put("hour", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setWrapText(false); style.setFont(sf); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_DOTTED); style.setBorderBottom(CellStyle.BORDER_THIN); styles.put("hour-quarter", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setWrapText(false); style.setFont(sf); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_DASHED); style.setBorderBottom(CellStyle.BORDER_THIN); styles.put("hour-half", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setWrapText(true); style.setFont(nf); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); style.setBorderBottom(CellStyle.BORDER_THIN); styles.put("hour-last", style); return styles; }