List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setFont
public void setFont(HSSFFont font)
From source file:com.ssic.education.provider.controller.ProSupplierController.java
@RequestMapping(value = "/excel") @ResponseBody/*from ww w. java 2 s . c o m*/ public ModelAndView exportExcel(SupplierDto supplierDto, HttpServletRequest request, HttpServletResponse response) { SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME); if (info == null) { return null; } supplierDto.setReceiverId(info.getSupplierId()); SimpleDateFormat sdf = new SimpleDateFormat("yyyy.M.d"); Date date = new Date(); String filename = Tools.date2Str(date, "yyyyMMddHHmmss"); HSSFSheet sheet; HSSFCell cell; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); Workbook workbook = new HSSFWorkbook(); sheet = (HSSFSheet) workbook.createSheet(""); try { List<String> titles = new ArrayList<String>(); titles.add("????"); titles.add("???"); // titles.add("????"); // titles.add("??????"); titles.add("????"); titles.add("???"); // titles.add("?"); // titles.add("???"); titles.add("?"); titles.add("?"); int len = titles.size(); HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont headerFont = (HSSFFont) workbook.createFont(); // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 11); headerStyle.setFont(headerFont); short width = 20, height = 25 * 20; sheet.setDefaultColumnWidth(width); HSSFRow sheetRow = sheet.createRow(0); for (int i = 0; i < len; i++) { // String title = titles.get(i); cell = sheetRow.createCell(i); cell.setCellStyle(headerStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); DataGrid dg = supplierService.findProSupplier(supplierDto, null); List<SupplierDto> expList = dg.getRows(); List<PageData> varList = new ArrayList<PageData>(); if (!CollectionUtils.isEmpty(expList)) { for (int i = 0; i < expList.size(); i++) { PageData vpd = new PageData(); vpd.put("var1", expList.get(i).getSupplierName()); vpd.put("var2", expList.get(i).getAddress()); // vpd.put("var3", expList.get(i).getFoodServiceCode()); // vpd.put("var4", expList.get(i).getFoodBusinessCode()); vpd.put("var3", expList.get(i).getFoodCirculationCode()); vpd.put("var4", expList.get(i).getFoodProduceCode()); // vpd.put("var7", expList.get(i).getBusinessLicense()); // vpd.put("var8", expList.get(i).getSupplierCode()); vpd.put("var5", expList.get(i).getCorporation()); vpd.put("var6", expList.get(i).getContactWay()); varList.add(vpd); } } for (int i = 0; i < varList.size(); i++) { HSSFRow row = sheet.createRow(i + 1); PageData vpd = varList.get(i); for (int j = 0; j < len; j++) { String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : ""; cell = row.createCell(j); HSSFCellStyle cellStyle2 = (HSSFCellStyle) workbook.createCellStyle(); HSSFDataFormat format = (HSSFDataFormat) workbook.createDataFormat(); cellStyle2.setDataFormat(format.getFormat("@")); cell.setCellStyle(cellStyle2); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(varstr); } } OutputStream os = response.getOutputStream(); workbook.write(os); os.flush(); os.close(); } catch (Exception e) { } return null; }
From source file:com.ssic.education.provider.controller.WaresController.java
@RequestMapping(value = "/excel") @ResponseBody/*w ww . ja va 2s. c o m*/ public ModelAndView exportExcel(ProWaresDto proWaresDto, HttpServletRequest request, HttpServletResponse response) { SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME); if (info == null) { return null; } proWaresDto.setSupplierId(info.getSupplierId()); SimpleDateFormat sdf = new SimpleDateFormat("yyyy.M.d"); Date date = new Date(); String filename = Tools.date2Str(date, "yyyyMMddHHmmss"); HSSFSheet sheet; HSSFCell cell; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); Workbook workbook = new HSSFWorkbook(); sheet = (HSSFSheet) workbook.createSheet(""); try { List<String> titles = new ArrayList<String>(); titles.add("??"); titles.add("???"); titles.add(""); titles.add("?"); titles.add("?"); // titles.add("??"); // titles.add("???"); // titles.add("??"); titles.add("?"); titles.add("???"); titles.add(""); int len = titles.size(); HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont headerFont = (HSSFFont) workbook.createFont(); // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 11); headerStyle.setFont(headerFont); short width = 20, height = 25 * 20; sheet.setDefaultColumnWidth(width); HSSFRow sheetRow = sheet.createRow(0); for (int i = 0; i < len; i++) { // String title = titles.get(i); cell = sheetRow.createCell(i); cell.setCellStyle(headerStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); List<ProWaresDto> expList = waresService.findAllWares(proWaresDto, null); List<PageData> varList = new ArrayList<PageData>(); if (!CollectionUtils.isEmpty(expList)) { for (int i = 0; i < expList.size(); i++) { PageData vpd = new PageData(); vpd.put("var1", expList.get(i).getWaresName()); vpd.put("var2", expList.get(i).getAmountUnit()); vpd.put("var3", expList.get(i).getSpec()); vpd.put("var4", ProductClass.getName(expList.get(i).getWaresType())); vpd.put("var5", expList.get(i).getManufacturer()); // vpd.put("var5", expList.get(i).getEnName()); // vpd.put("var6", expList.get(i).getBarCode()); // vpd.put("var7", expList.get(i).getCustomCode()); vpd.put("var6", expList.get(i).getShelfLife()); vpd.put("var7", expList.get(i).getUnit()); vpd.put("var8", expList.get(i).getPlace()); varList.add(vpd); } } for (int i = 0; i < varList.size(); i++) { HSSFRow row = sheet.createRow(i + 1); PageData vpd = varList.get(i); for (int j = 0; j < len; j++) { String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : ""; cell = row.createCell(j); HSSFCellStyle cellStyle2 = (HSSFCellStyle) workbook.createCellStyle(); HSSFDataFormat format = (HSSFDataFormat) workbook.createDataFormat(); cellStyle2.setDataFormat(format.getFormat("@")); cell.setCellStyle(cellStyle2); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(varstr); } } OutputStream os = response.getOutputStream(); workbook.write(os); os.flush(); os.close(); } catch (Exception e) { } return null; }
From source file:com.testmax.util.ExcelSheet.java
License:CDDL license
/** * This method is used to set the styles for all the headers * of the excel sheet./*from ww w . jav a 2 s. co m*/ * @param sampleWorkBook - Name of the workbook. * @return cellStyle - Styles for the Header data of Excel sheet. */ private HSSFCellStyle setHeaderStyle(HSSFWorkbook workBook) { HSSFFont font = workBook.createFont(); font.setFontName(HSSFFont.FONT_ARIAL); font.setColor(IndexedColors.PLUM.getIndex()); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle cellStyle = workBook.createCellStyle(); cellStyle.setFont(font); return cellStyle; }
From source file:com.ts.excelservlet.UDR_Driver_Excel.java
/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) *///from ww w . ja v a2 s . co m protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub DBTransaction dbtranobj = new DBTransaction(); String vehicle_number = request.getParameter("vehicle_number"); HttpSession session = request.getSession(true); String[] select = (String[]) session.getAttribute("id"); String driver_name = request.getParameter("driver_name"); //System.out.println("IMEI : " +imeinumber); //String vehicle_number=""; int index = 2; System.out.println("************** doGet ************"); OutputStream out = null; try { response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Driver_UDR.xls"); Connection con = dbtranobj.connect(); // Class.forName("org.postgresql.Driver").newInstance(); //conn = DriverManager.getConnection("jdbc:postgresql://182.72.167.34:5432/master_database","postgres", "postgres"); ResultSet rs = null; Statement st = null; st = con.createStatement(); rs = st.executeQuery( "SELECT * FROM driver_info WHERE driver_name='" + driver_name + "' order by driver_name"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Driver Sheet"); sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 1)); sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 2)); /* sheet.addMergedRegion(new Region(0,(short)0,0,(short)3)); sheet.addMergedRegion(new Region(0,(short)0,0,(short)4)); sheet.addMergedRegion(new Region(0,(short)0,0,(short)5));*/ HSSFRow rowhead = sheet.createRow((short) 0); rowhead.setHeight((short) 500); /*rowhead.createCell((short) 0).setCellValue("Fuel Information For " +vehicle_number); * */ HSSFCell cell2B = rowhead.createCell(0); cell2B.setCellValue(new HSSFRichTextString("Report For Driver : " + driver_name)); // Style Font in Cell 2B HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle = wb.createCellStyle(); HSSFFont hSSFFont = wb.createFont(); hSSFFont.setFontName(HSSFFont.FONT_ARIAL); hSSFFont.setFontHeightInPoints((short) 14); hSSFFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); hSSFFont.setColor(HSSFColor.BLUE.index); cellStyle.setFont(hSSFFont); cell2B.setCellStyle(cellStyle); HSSFRow rowhead1 = sheet.createRow((short) 1); rowhead1.setHeight((short) 600); Cell cell = rowhead1.createCell((short) 0); HSSFCellStyle cellStyle1 = wb.createCellStyle(); cellStyle1 = wb.createCellStyle(); HSSFFont hSSFFont1 = wb.createFont(); hSSFFont1.setFontName(HSSFFont.FONT_ARIAL); hSSFFont1.setFontHeightInPoints((short) 12); hSSFFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); hSSFFont1.setColor(HSSFColor.BLACK.index); cellStyle1.setFont(hSSFFont1); cell.setCellStyle(cellStyle1); // cell.setCellValue("SNO"); sheet.setColumnWidth(0, 7000); // rowhead.createCell((short) 0).setCellValue("Date"); if (select != null && select.length != 0) { for (int i = 0; i < select.length; i++) { if (select[i].equalsIgnoreCase("doe")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("LICENCE EXPIRY"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("doj")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("JOINED DATE"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("license_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("LICENCE NUMBER "); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("vehicle_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("VEHICLE NUMBER"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("shift_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("SHIFT NUMBER"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("route_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("ROUTE NUMBER"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("address")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("ADDRESS"); sheet.setColumnWidth(i, 7000); } else { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue(select[i]); sheet.setColumnWidth(i, 7000); } } } rs = st.executeQuery( "SELECT * FROM driver_info WHERE driver_name='" + driver_name + "' order by driver_name"); while (rs.next()) { HSSFRow row = sheet.createRow((short) index); row.setHeight((short) 500); if (select != null && select.length != 0) { for (int i = 0; i < select.length; i++) { row.createCell((short) i).setCellValue(rs.getString(select[i])); } } index++; } out = response.getOutputStream(); wb.write(out); } catch (Exception e) { throw new ServletException("Exception in Excel Sample Servlet", e); } finally { if (out != null) out.close(); } }
From source file:com.util.poi.ExcelView.java
License:Open Source License
/** * ?Excel//from w w w. j ava2 s.c o m * * @param model * ? * @param workbook * workbook * @param request * request * @param response * response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); //comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "S" + "H" + "O" + "P" + "+" + "+")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); /* Map<String, Object> map=toHashMap(item); cell.setCellValue(map.get(properties[i]).toString());*/ cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { /*Map<String, Object> map=toHashMap(item); cell.setCellValue(map.get(properties[i]).toString());*/ cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
From source file:com.vportal.portlet.vdoc.service.util.ReportUtil.java
License:Open Source License
public static void createCellBold(HSSFRow row, short column, String value, HSSFWorkbook wb) { HSSFCellStyle style = wb.createCellStyle(); HSSFFont font = wb.createFont();/* w w w . java 2 s . co m*/ font.setColor((short) 0xc); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND); HSSFCell cell = row.getCell(column); if (cell == null) cell = row.createCell(column); //cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(value); cell.setCellStyle(style); }
From source file:com.zrx.authority.util.ObjectExcelView.java
@Override protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // TODO Auto-generated method stub Date date = new Date(); String filename = Tools.date2Str(date, "yyyyMMddHHmmss"); HSSFSheet sheet;/* w ww . j a v a2s . c o m*/ HSSFCell cell; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); sheet = workbook.createSheet("sheet1"); List<String> titles = (List<String>) model.get("titles"); int len = titles.size(); HSSFCellStyle headerStyle = workbook.createCellStyle(); //? headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont headerFont = workbook.createFont(); // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 11); headerStyle.setFont(headerFont); short width = 20, height = 25 * 20; sheet.setDefaultColumnWidth(width); for (int i = 0; i < len; i++) { // String title = titles.get(i); cell = getCell(sheet, 0, i); cell.setCellStyle(headerStyle); setText(cell, title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = workbook.createCellStyle(); //? contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); List<PageData> varList = (List<PageData>) model.get("varList"); int varCount = varList.size(); for (int i = 0; i < varCount; i++) { PageData vpd = varList.get(i); for (int j = 0; j < len; j++) { String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : ""; cell = getCell(sheet, i + 1, j); cell.setCellStyle(contentStyle); setText(cell, varstr); } } }
From source file:Controlador.ControladorCargueMasivo.java
public static HSSFWorkbook obtenerExcel(DataModel contenidoCeldas, DataModel cabecerasCeldas, String nombreHoja) {// w ww .ja v a 2s .co m HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); HSSFSheet hssfSheet = hssfWorkbook.createSheet(nombreHoja); int numeroFila = 0; int numeroColumna = 0; HSSFRow hssfRow = hssfSheet.createRow(numeroFila++); HSSFCellStyle hssfCellStyleCabecera = hssfWorkbook.createCellStyle(); hssfCellStyleCabecera.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); hssfCellStyleCabecera.setFillBackgroundColor(new HSSFColor.BLACK().getIndex()); HSSFFont hssfFont = hssfWorkbook.createFont(); hssfFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); hssfFont.setColor(HSSFColor.WHITE.index); hssfCellStyleCabecera.setFont(hssfFont); String columnaCabecera; HSSFCell hssfCell = null; List cabecerasExcel = (List) cabecerasCeldas.getWrappedData(); for (int i = 0; i < cabecerasExcel.size(); i++) { columnaCabecera = (String) cabecerasExcel.get(i); hssfCell = hssfRow.createCell((short) numeroColumna++); hssfCell.setCellValue(columnaCabecera); hssfCell.setCellStyle(hssfCellStyleCabecera); } List contenidoExcel = (List) contenidoCeldas.getWrappedData(); List fila = null; Object valor; for (int i = 0; i < contenidoExcel.size(); i++) { fila = (List) contenidoExcel.get(i); hssfRow = hssfSheet.createRow(numeroFila++); numeroColumna = 0; for (int x = 0; x < fila.size(); x++) { valor = fila.get(x); hssfCell = hssfRow.createCell((short) numeroColumna++); hssfCell.setCellValue((String) valor); } } return hssfWorkbook; }
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 ww . j a v a 2 s .co 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 w w . j av a2 s . com 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(); }