List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setFillPattern
@Override public void setFillPattern(FillPatternType fp)
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportTravoxReport(List<MainMigrateModel> list) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setDataFormat(currency.getFormat("#,##0.00")); HSSFSheet sheet = wb.createSheet("TravoxReport"); HSSFRow row2 = sheet.createRow(0);//from w w w .j a va2s . c om HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("GJ"); cell20.setCellStyle(styleC3Center); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("PAY NO"); cell21.setCellStyle(styleC3Center); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("NAME"); cell22.setCellStyle(styleC3Center); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("AP CODE"); cell23.setCellStyle(styleC3Center); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("REFDOC"); cell24.setCellStyle(styleC3Center); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("SYSTEM_DATE"); cell25.setCellStyle(styleC3Center); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("DUE DATE"); cell26.setCellStyle(styleC3Center); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("INVOICE NUM"); cell27.setCellStyle(styleC3Center); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("MAIN DESCRIPTION"); cell28.setCellStyle(styleC3Center); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("CODE"); cell29.setCellStyle(styleC3Center); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("TYPE PRODUCT"); cell30.setCellStyle(styleC3Center); HSSFCell cell31 = row2.createCell(11); cell31.setCellValue("DESCRIPTION"); cell31.setCellStyle(styleC3Center); HSSFCell cell32 = row2.createCell(12); cell32.setCellValue("TOTAL AMOUNT"); cell32.setCellStyle(styleC3Center); HSSFCell cell33 = row2.createCell(13); cell33.setCellValue("TOTAL VAT"); cell33.setCellStyle(styleC3Center); HSSFCell cell34 = row2.createCell(14); cell34.setCellValue("CUR"); cell34.setCellStyle(styleC3Center); HSSFCell cell35 = row2.createCell(15); cell35.setCellValue("AMOUNT"); cell35.setCellStyle(styleC3Center); HSSFCell cell36 = row2.createCell(16); cell36.setCellValue("DEPARTMENT"); cell36.setCellStyle(styleC3Center); HSSFCell cell37 = row2.createCell(17); cell37.setCellValue("ACC NO"); cell37.setCellStyle(styleC3Center); HSSFCell cell38 = row2.createCell(18); cell38.setCellValue("EXPENSE DATE"); cell38.setCellStyle(styleC3Center); HSSFCell cell39 = row2.createCell(19); cell39.setCellValue("VOUCHER NO"); cell39.setCellStyle(styleC3Center); HSSFCell cell40 = row2.createCell(20); cell40.setCellValue("VOUCHER AMOUNT"); cell40.setCellStyle(styleC3Center); if (list != null) { int count = 1; for (int i = 0; i < list.size(); i++) { MainMigrateModel data = (MainMigrateModel) list.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getGj()); cell0.setCellStyle(styleC24); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getPayno()); cell1.setCellStyle(styleC24); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getName()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getApcode()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getRefdoc()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getSystemdate()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getDuedate()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getInvoicenum()); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getMaindescription()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getCode()); cell8.setCellStyle(styleC24); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getTypeproduct()); cell9.setCellStyle(styleC24); HSSFCell cell11 = row.createCell(11); cell11.setCellValue(data.getDescription()); cell11.setCellStyle(styleC24); HSSFCell cell12 = row.createCell(12); cell12.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getTotalamount())) ? (new BigDecimal(data.getTotalamount())).doubleValue() : 0); cell12.setCellStyle(styleC25); HSSFCell cell013 = row.createCell(13); cell013.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getTotalvat())) ? (new BigDecimal(data.getTotalvat())).doubleValue() : 0); cell013.setCellStyle(styleC25); HSSFCell cell14 = row.createCell(14); cell14.setCellValue(data.getCur()); cell14.setCellStyle(styleC23); HSSFCell cell15 = row.createCell(15); cell15.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getAmount())) ? (new BigDecimal(data.getAmount())).doubleValue() : 0); cell15.setCellStyle(styleC25); HSSFCell cell16 = row.createCell(16); cell16.setCellValue(data.getDepartment()); cell16.setCellStyle(styleC24); HSSFCell cell17 = row.createCell(17); cell17.setCellValue(data.getAccno()); cell17.setCellStyle(styleC24); HSSFCell cell18 = row.createCell(18); cell18.setCellValue(data.getExpensedate()); cell18.setCellStyle(styleC24); HSSFCell cell19 = row.createCell(19); cell19.setCellValue(data.getVoucherno()); cell19.setCellStyle(styleC24); HSSFCell cell020 = row.createCell(20); cell020.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getVoucheramount())) ? (new BigDecimal(data.getVoucheramount())).doubleValue() : 0); cell020.setCellStyle(styleC25); } } for (int x = 0; x < 21; x++) { sheet.autoSizeColumn(x); } sheet.setColumnWidth(2, 256 * 30); sheet.setColumnWidth(8, 256 * 30); sheet.setColumnWidth(11, 256 * 30); exportFileExcel("TravoxReport", wb); }
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportInvoiceReport(List<MainMigrateModel> listInv) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setDataFormat(currency.getFormat("#,##0.00")); HSSFSheet sheet = wb.createSheet("Invoice"); HSSFRow row2 = sheet.createRow(0);//from w w w . j ava 2 s . c o m HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("ID"); cell20.setCellStyle(styleC3Center); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("INV NO"); cell21.setCellStyle(styleC3Center); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("NAME"); cell22.setCellStyle(styleC3Center); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("INV DATE"); cell23.setCellStyle(styleC3Center); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("GRAND TOTAL"); cell24.setCellStyle(styleC3Center); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("GRAND TOTAL GROSS"); cell25.setCellStyle(styleC3Center); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("GRAND TOTAL VAT"); cell26.setCellStyle(styleC3Center); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("CUR"); cell27.setCellStyle(styleC3Center); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("DEPARTMENT"); cell28.setCellStyle(styleC3Center); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("ACC NO"); cell29.setCellStyle(styleC3Center); if (listInv != null) { int count = 1; for (int i = 0; i < listInv.size(); i++) { MainMigrateModel data = (MainMigrateModel) listInv.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getId()); cell0.setCellStyle(styleC24); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getInvno()); cell1.setCellStyle(styleC24); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getName()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getInvdate()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); // cell3.setCellValue(data.getGrandtotal()); cell3.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getGrandtotal())) ? (new BigDecimal(data.getGrandtotal())).doubleValue() : 0); cell3.setCellStyle(styleC25); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getGrandtotalgross())) ? (new BigDecimal(data.getGrandtotalgross())).doubleValue() : 0); cell4.setCellStyle(styleC25); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(!"".equalsIgnoreCase(String.valueOf(data.getGrandtotalvat())) ? (new BigDecimal(data.getGrandtotalvat())).doubleValue() : 0); cell5.setCellStyle(styleC25); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getCur()); cell6.setCellStyle(styleC23); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getDepartment()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getAccno()); cell8.setCellStyle(styleC24); } } for (int x = 0; x < 10; x++) { sheet.autoSizeColumn(x); } sheet.setColumnWidth(2, 256 * 30); exportFileExcel("Invoice", wb); }
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportDeptorInvoiceReport(List<MainMigrateModel> listInv) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); // styleC25.setDataFormat(currency.getFormat("#,##0.00")); HSSFSheet sheet = wb.createSheet("DeptorInvoice"); HSSFRow row2 = sheet.createRow(0);/*from ww w .j ava 2s . com*/ HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("INV NO"); cell20.setCellStyle(styleC3Center); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("DATE"); cell21.setCellStyle(styleC3Center); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("NAME"); cell22.setCellStyle(styleC3Center); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("DETAIL"); cell23.setCellStyle(styleC3Center); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("INV AMOUNT"); cell24.setCellStyle(styleC3Center); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("RECEIVE NO"); cell25.setCellStyle(styleC3Center); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("RECEIVE AMOUNT"); cell26.setCellStyle(styleC3Center); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("REMAIN AMOUNT"); cell27.setCellStyle(styleC3Center); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("CODE"); cell28.setCellStyle(styleC3Center); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("DEPARTMENT"); cell29.setCellStyle(styleC3Center); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("GRAND TOTAL VATAMT"); cell30.setCellStyle(styleC3Center); if (listInv != null) { int count = 1; for (int i = 0; i < listInv.size(); i++) { MainMigrateModel data = (MainMigrateModel) listInv.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getInvoiceno()); cell0.setCellStyle(styleC24); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getInvoicedate()); cell1.setCellStyle(styleC24); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getInvoicename()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getInvoicedetail()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getInvoiceamount()); cell3.setCellStyle(styleC25); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getReceiveno()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getReceiveamount()); cell5.setCellStyle(styleC25); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getRemainamount()); cell6.setCellStyle(styleC25); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getCode()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getDepartment()); cell8.setCellStyle(styleC24); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getGrandtotal()); cell9.setCellStyle(styleC25); } } for (int x = 0; x < 11; x++) { sheet.autoSizeColumn(x); } sheet.setColumnWidth(2, 256 * 30); exportFileExcel("DeptorInvoice", wb); }
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportAPReport(List<MainMigrateModel> listAP) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); String datetemp = ""; if (listAP != null) { HSSFSheet sheet = wb.createSheet(listAP.get(0).getPaydate().substring(3, 10).replaceAll("-", "")); int count = 1; for (int i = 0; i < listAP.size(); i++) { MainMigrateModel data = (MainMigrateModel) listAP.get(i); if (!"".equalsIgnoreCase(datetemp) && !datetemp.equalsIgnoreCase(data.getPaydate().substring(3, 10))) { sheet = wb.createSheet(data.getPaydate().substring(3, 10).replaceAll("-", "")); HSSFRow row2 = sheet.createRow(0); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("PAYID"); cell20.setCellStyle(styleC3Center); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("PAY NO"); cell21.setCellStyle(styleC3Center); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("AP CODE"); cell22.setCellStyle(styleC3Center); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("NAME"); cell23.setCellStyle(styleC3Center); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("PAY DATE"); cell24.setCellStyle(styleC3Center); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("DEPARTMENT"); cell25.setCellStyle(styleC3Center); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("VAT TYPE"); cell26.setCellStyle(styleC3Center); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("TAX NO"); cell27.setCellStyle(styleC3Center); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("BRANCH"); cell28.setCellStyle(styleC3Center); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("BRANCH NO"); cell29.setCellStyle(styleC3Center); count = 1;/*from w w w.j a va2 s . co m*/ sheet.setColumnWidth(0, 256 * 15); sheet.setColumnWidth(1, 256 * 15); sheet.setColumnWidth(2, 256 * 15); sheet.setColumnWidth(3, 256 * 25); sheet.setColumnWidth(4, 256 * 15); sheet.setColumnWidth(5, 256 * 15); sheet.setColumnWidth(6, 256 * 15); sheet.setColumnWidth(7, 256 * 15); sheet.setColumnWidth(8, 256 * 15); sheet.setColumnWidth(9, 256 * 15); } else if ("".equalsIgnoreCase(datetemp)) { HSSFRow row2 = sheet.createRow(0); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("PAYID"); cell20.setCellStyle(styleC3Center); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("PAY NO"); cell21.setCellStyle(styleC3Center); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("AP CODE"); cell22.setCellStyle(styleC3Center); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("NAME"); cell23.setCellStyle(styleC3Center); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("PAY DATE"); cell24.setCellStyle(styleC3Center); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("DEPARTMENT"); cell25.setCellStyle(styleC3Center); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("VAT TYPE"); cell26.setCellStyle(styleC3Center); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("TAX NO"); cell27.setCellStyle(styleC3Center); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("BRANCH"); cell28.setCellStyle(styleC3Center); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("BRANCH NO"); cell29.setCellStyle(styleC3Center); sheet.setColumnWidth(0, 256 * 15); sheet.setColumnWidth(1, 256 * 15); sheet.setColumnWidth(2, 256 * 15); sheet.setColumnWidth(3, 256 * 25); sheet.setColumnWidth(4, 256 * 15); sheet.setColumnWidth(5, 256 * 15); sheet.setColumnWidth(6, 256 * 15); sheet.setColumnWidth(7, 256 * 15); sheet.setColumnWidth(8, 256 * 15); sheet.setColumnWidth(9, 256 * 15); } HSSFRow row = sheet.createRow(count); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getPayid()); cell0.setCellStyle(styleC24); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getPayno()); cell1.setCellStyle(styleC24); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getApCode()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(String.valueOf(data.getApname())); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(String.valueOf(data.getPaydate())); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getDepartment()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getVattype()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getTaxno()); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getBranch()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getBranchno()); cell8.setCellStyle(styleC24); datetemp = data.getPaydate().substring(3, 10); count++; } } exportFileExcel("APReport", wb); }
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportARReport(List<MainMigrateModel> listAR) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleC3Center.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); String datetemp = ""; if (listAR != null) { // HSSFSheet sheet = wb.createSheet(listAR.get(0).getInvdate().substring(3,10).replaceAll("-", "")); HSSFSheet sheet = wb.createSheet("ARReport"); int count = 1; for (int i = 0; i < listAR.size(); i++) { MainMigrateModel data = (MainMigrateModel) listAR.get(i); if (!"".equalsIgnoreCase(datetemp) && !datetemp.equalsIgnoreCase(data.getInvdate().substring(3, 10))) { sheet = wb.createSheet(data.getInvdate().substring(3, 10).replaceAll("-", "")); HSSFRow row2 = sheet.createRow(0); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("CODE"); cell20.setCellStyle(styleC3Center); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("INV NAME"); cell21.setCellStyle(styleC3Center); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("INV NO"); cell22.setCellStyle(styleC3Center); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("INV DATE"); cell23.setCellStyle(styleC3Center); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("TAX NO"); cell24.setCellStyle(styleC3Center); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("BRANCH"); cell25.setCellStyle(styleC3Center); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("BRANCH NO"); cell26.setCellStyle(styleC3Center); count = 1;/*from w w w . j a v a 2 s . c o m*/ sheet.setColumnWidth(0, 256 * 15); sheet.setColumnWidth(1, 256 * 25); sheet.setColumnWidth(2, 256 * 15); sheet.setColumnWidth(3, 256 * 15); sheet.setColumnWidth(4, 256 * 15); sheet.setColumnWidth(5, 256 * 15); sheet.setColumnWidth(6, 256 * 15); } else if ("".equalsIgnoreCase(datetemp)) { HSSFRow row2 = sheet.createRow(0); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("CODE"); cell20.setCellStyle(styleC3Center); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("INV NAME"); cell21.setCellStyle(styleC3Center); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("INV NO"); cell22.setCellStyle(styleC3Center); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("INV DATE"); cell23.setCellStyle(styleC3Center); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("TAX NO"); cell24.setCellStyle(styleC3Center); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("BRANCH"); cell25.setCellStyle(styleC3Center); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("BRANCH NO"); cell26.setCellStyle(styleC3Center); sheet.setColumnWidth(0, 256 * 15); sheet.setColumnWidth(1, 256 * 25); sheet.setColumnWidth(2, 256 * 15); sheet.setColumnWidth(3, 256 * 15); sheet.setColumnWidth(4, 256 * 15); sheet.setColumnWidth(5, 256 * 15); sheet.setColumnWidth(6, 256 * 15); } HSSFRow row = sheet.createRow(count); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getCode()); cell0.setCellStyle(styleC24); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getInvname()); cell1.setCellStyle(styleC24); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getInvno()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(String.valueOf(data.getInvdate())); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getTaxno()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getBranch()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getBranchno()); cell5.setCellStyle(styleC24); // datetemp = data.getInvdate().substring(3,10); count++; } } exportFileExcel("ARReport", wb); }
From source file:com.util.poi.ExcelView.java
License:Open Source License
/** * ?Excel/*from w w w . jav a 2s . c om*/ * * @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.util.tools.FeraExporter.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);/*from w w w. j av a 2 s . c o m*/ HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
From source file:Compras.Conciliacion.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed // TODO add your handling code here: try {/*www .j a va 2 s. co m*/ javax.swing.JFileChooser archivo = new javax.swing.JFileChooser(); archivo.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" })); String ruta = null; if (archivo.showSaveDialog(null) == archivo.APPROVE_OPTION) { ruta = archivo.getSelectedFile().getAbsolutePath(); if (ruta != null) { File archivoXLS = new File(ruta + ".xls"); File plantilla = new File("imagenes/plantillaConciliacion.xls"); Session session = HibernateUtil.getSessionFactory().openSession(); ArrayList datos = new ArrayList(); Query query = session.createSQLQuery( "select compania.nombre, orden.tipo_nombre, orden.modelo, orden.no_serie, clientes.nombre as nombres,orden.id_orden \n" + "from orden inner join compania on compania.id_compania=orden.id_compania inner join clientes on clientes.id_clientes=orden.id_cliente\n" + "where orden.id_orden=" + Integer.parseInt(orden) + ""); query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); datos = (ArrayList) query.list(); // Path FROM = Paths.get("imagenes/plantillaConciliacion.xls"); Path TO = Paths.get(ruta + ".xls"); //sobreescribir el fichero de destino, si existe, y copiar // los atributos, incluyendo los permisos rwx CopyOption[] options = new CopyOption[] { StandardCopyOption.REPLACE_EXISTING, StandardCopyOption.COPY_ATTRIBUTES }; Files.copy(FROM, TO, options); FileInputStream miPlantilla = new FileInputStream(archivoXLS); POIFSFileSystem fsFileSystem = new POIFSFileSystem(miPlantilla); HSSFWorkbook libro = new HSSFWorkbook(fsFileSystem); libro.getSheet("Conciliacion").getRow(0).getCell(6) .setCellValue("CONCILIACIN PARA FACTURACIN"); for (int i = 0; i < datos.size(); i++) { java.util.HashMap map = (java.util.HashMap) datos.get(i); libro.getSheet("Conciliacion").getRow(1).getCell(2) .setCellValue(map.get("nombre").toString()); libro.getSheet("Conciliacion").getRow(2).getCell(2) .setCellValue(map.get("tipo_nombre").toString()); libro.getSheet("Conciliacion").getRow(3).getCell(2) .setCellValue(map.get("modelo").toString()); libro.getSheet("Conciliacion").getRow(4).getCell(2) .setCellValue(map.get("no_serie").toString()); libro.getSheet("Conciliacion").getRow(5).getCell(2) .setCellValue(map.get("nombres").toString()); libro.getSheet("Conciliacion").getRow(2).getCell(12) .setCellValue(map.get("id_orden").toString()); } HSSFCellStyle borde_d = libro.createCellStyle(); borde_d.setBorderBottom(CellStyle.BORDER_THIN); borde_d.setBorderTop(CellStyle.BORDER_THIN); borde_d.setBorderRight(CellStyle.BORDER_THIN); borde_d.setBorderLeft(CellStyle.BORDER_THIN); borde_d.setAlignment(CellStyle.ALIGN_RIGHT); HSSFCellStyle borde_i = libro.createCellStyle(); borde_i.setBorderBottom(CellStyle.BORDER_THIN); borde_i.setBorderTop(CellStyle.BORDER_THIN); borde_i.setBorderRight(CellStyle.BORDER_THIN); borde_i.setBorderLeft(CellStyle.BORDER_THIN); borde_i.setAlignment(CellStyle.ALIGN_LEFT); HSSFCellStyle borde_c = libro.createCellStyle(); borde_c.setBorderBottom(CellStyle.BORDER_THIN); borde_c.setBorderTop(CellStyle.BORDER_THIN); borde_c.setBorderRight(CellStyle.BORDER_THIN); borde_c.setBorderLeft(CellStyle.BORDER_THIN); borde_c.setAlignment(CellStyle.ALIGN_CENTER); HSSFCellStyle borde_dr = libro.createCellStyle(); borde_dr.setBorderBottom(CellStyle.BORDER_THIN); borde_dr.setBorderTop(CellStyle.BORDER_THIN); borde_dr.setBorderRight(CellStyle.BORDER_THIN); borde_dr.setBorderLeft(CellStyle.BORDER_THIN); borde_dr.setAlignment(CellStyle.ALIGN_RIGHT); borde_dr.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_dr.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_dr.setFillForegroundColor(HSSFColor.YELLOW.index); HSSFCellStyle borde_ir = libro.createCellStyle(); borde_ir.setBorderBottom(CellStyle.BORDER_THIN); borde_ir.setBorderTop(CellStyle.BORDER_THIN); borde_ir.setBorderRight(CellStyle.BORDER_THIN); borde_ir.setBorderLeft(CellStyle.BORDER_THIN); borde_ir.setAlignment(CellStyle.ALIGN_LEFT); borde_ir.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_ir.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_ir.setFillForegroundColor(HSSFColor.YELLOW.index); HSSFCellStyle borde_cr = libro.createCellStyle(); borde_cr.setBorderBottom(CellStyle.BORDER_THIN); borde_cr.setBorderTop(CellStyle.BORDER_THIN); borde_cr.setBorderRight(CellStyle.BORDER_THIN); borde_cr.setBorderLeft(CellStyle.BORDER_THIN); borde_cr.setAlignment(CellStyle.ALIGN_CENTER); borde_cr.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_cr.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_cr.setFillForegroundColor(HSSFColor.YELLOW.index); DecimalFormat formatoDecimal = new DecimalFormat("####0.0"); DecimalFormat formatoPorcentaje = new DecimalFormat("#,##0.00"); int miRenglon = 9; for (int i = 0; i < t_datos.getRowCount(); i++) { for (int j = 0; j < 4; j++) { int renglon = 0; switch (j) { case 0: renglon = 8; break; case 1: renglon = 10; break; case 2: renglon = 11; break; case 3: renglon = 12; break; } if ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && t_datos.getValueAt(i, 9).toString().compareTo("N") == 0) || ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && renglon >= 10)) || (renglon == 8 && Double.parseDouble(t_datos.getValueAt(i, 10).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 11).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 12).toString()) <= 0)) { if ((boolean) t_datos.getValueAt(i, 3) == true || (boolean) t_datos.getValueAt(i, 4) == true) { libro.getSheet("Conciliacion").createRow(miRenglon); //columna0 if (t_datos.getValueAt(i, 5) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(t_datos.getValueAt(i, 5).toString()); } //columna1 if (t_datos.getValueAt(i, 6) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(t_datos.getValueAt(i, 6).toString()); } //columna2 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(2) .setCellValue(t_datos.getValueAt(i, renglon).toString()); //columna3 if (t_datos.getValueAt(i, 14) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(t_datos.getValueAt(i, 14).toString()); } //columna4 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(4) .setCellValue(t_datos.getValueAt(i, 2).toString()); //columna5 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue(""); else { switch (renglon) { case 8: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("N"); break; case 10: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("D"); break; case 11: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("R"); break; case 12: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("M"); break; } } //columna6 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(6) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 15))); //columna7 $tot aut. double n; n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 15).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(7) .setCellValue(formatoPorcentaje.format(n)); //columna8 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(8) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 16))); //columna9 $tot com n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 16).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(9) .setCellValue(formatoPorcentaje.format(n)); //columna10 11 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(""); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(""); } else { switch (renglon) { case 8: n = BigDecimal.valueOf( Double.parseDouble(t_datos.getValueAt(i, 16).toString()) / 0.9d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 10: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.72d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 11: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 12: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; } } //columna12 if (t_datos.getValueAt(i, 18) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(t_datos.getValueAt(i, 18).toString()); } //columna13 if (t_datos.getValueAt(i, 19) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(t_datos.getValueAt(i, 19).toString()); } //columna14 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(14) .setCellValue("V"); if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_c); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_d); } else { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_cr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_dr); } miRenglon++; } } } } //font1.setColor(BaseColor.WHITE); libro.getSheet("Conciliacion").createRow(miRenglon); libro.getSheet("Conciliacion") .addMergedRegion(new CellRangeAddress(miRenglon, miRenglon, 0, 14)); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue("Faltante en Vales"); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0).setCellStyle(borde_c); miRenglon++; for (int i = 0; i < t_datos.getRowCount(); i++) { for (int j = 0; j < 4; j++) { int renglon = 0; switch (j) { case 0: renglon = 8; break; case 1: renglon = 10; break; case 2: renglon = 11; break; case 3: renglon = 12; break; } if ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && t_datos.getValueAt(i, 9).toString().compareTo("N") == 0) || ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && renglon >= 10)) || (renglon == 8 && Double.parseDouble(t_datos.getValueAt(i, 10).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 11).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 12).toString()) <= 0)) { if ((boolean) t_datos.getValueAt(i, 3) == false && (boolean) t_datos.getValueAt(i, 4) == false && t_datos.getValueAt(i, 5) != null) { libro.getSheet("Conciliacion").createRow(miRenglon); //columna0 if (t_datos.getValueAt(i, 5) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(t_datos.getValueAt(i, 5).toString()); } //columna1 if (t_datos.getValueAt(i, 6) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(t_datos.getValueAt(i, 6).toString()); } //columna2 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(2) .setCellValue(t_datos.getValueAt(i, renglon).toString()); //columna3 if (t_datos.getValueAt(i, 14) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(t_datos.getValueAt(i, 14).toString()); } //columna4 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(4) .setCellValue(t_datos.getValueAt(i, 2).toString()); //columna5 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue(""); else { switch (renglon) { case 8: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("N"); break; case 10: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("D"); break; case 11: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("R"); break; case 12: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("M"); break; } } //columna6 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(6) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 15))); //columna7 $tot aut. double n; n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 15).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(7) .setCellValue(formatoPorcentaje.format(n)); //columna8 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(8) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 16))); //columna9 $tot com n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 16).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(9) .setCellValue(formatoPorcentaje.format(n)); //columna10 11 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(""); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(""); } else { switch (renglon) { case 8: n = BigDecimal.valueOf( Double.parseDouble(t_datos.getValueAt(i, 16).toString()) / 0.9d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 10: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.72d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 11: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 12: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; } } //columna12 if (t_datos.getValueAt(i, 18) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(t_datos.getValueAt(i, 18).toString()); } //columna13 if (t_datos.getValueAt(i, 19) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(t_datos.getValueAt(i, 19).toString()); } //columna14 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(14) .setCellValue(""); if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_c); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_d); } else { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_cr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_dr); } miRenglon++; } } } } FileOutputStream archivo1 = new FileOutputStream(archivoXLS); libro.write(archivo1); archivo1.close(); Desktop.getDesktop().open(archivoXLS); } } } catch (Exception e) { e.printStackTrace(); } }
From source file:Controlador.ControladorCargueMasivo.java
public static HSSFWorkbook obtenerExcel(DataModel contenidoCeldas, DataModel cabecerasCeldas, String nombreHoja) {// w ww .ja v a 2 s . c o 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:das.pf.io.IOExcel.java
License:Open Source License
private HSSFCellStyle getHSSFCellStyleValue(Workbook worbook, TypeValues typeValue, TypeUnits typeUnit) { HSSFCellStyle style = (HSSFCellStyle) worbook.createCellStyle(); HSSFFont font = (HSSFFont) worbook.createFont(); HSSFPalette palette = ((HSSFWorkbook) worbook).getCustomPalette(); font.setColor(HSSFColor.WHITE.index); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); switch (typeValue) { case VALUES:/*ww w . j a va 2 s.c o m*/ switch (typeUnit) { case MTH: style.setFillForegroundColor(HSSFColor.DARK_BLUE.index); break; case QRT: style.setFillForegroundColor(HSSFColor.DARK_BLUE.index); palette.setColorAtIndex(HSSFColor.DARK_BLUE.index, (byte) 0, (byte) 0, (byte) 102); break; case YTD: style.setFillForegroundColor(HSSFColor.BLUE.index); break; case MAT: style.setFillForegroundColor(HSSFColor.AQUA.index); break; } break; case UNITS: switch (typeUnit) { case MTH: style.setFillForegroundColor(HSSFColor.RED.index); break; case QRT: style.setFillForegroundColor(HSSFColor.DARK_RED.index); break; case YTD: style.setFillForegroundColor(HSSFColor.ORANGE.index); break; case MAT: style.setFillForegroundColor(HSSFColor.YELLOW.index); break; } break; case U_E: switch (typeUnit) { case MTH: style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); break; case QRT: style.setFillForegroundColor(HSSFColor.DARK_GREEN.index); break; case YTD: style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); break; case MAT: style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); break; } break; } style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFont(font); return style; }