List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setColumnWidth
@Override public void setColumnWidth(int columnIndex, int width)
The maximum column width for an individual cell is 255 characters.
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 va2s. com 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;//w ww . j av a 2s . 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.smi.travel.migration.MainMigrate.java
public static void ExportTaxinvoiceReport(List reptax) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("TaxInvoice"); 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); HSSFRow row1 = sheet.createRow(0);//from w w w . ja v a2 s . co m HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("Tax Invoice Report"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row2 = sheet.createRow(2); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("ID"); cell20.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("TAX ID"); cell21.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("TAX NO"); cell22.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("TAX DATE"); cell23.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("CODE AP"); cell24.setCellStyle(styleC3Center); sheet.autoSizeColumn(4); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("DESCRIPTION"); cell25.setCellStyle(styleC3Center); sheet.autoSizeColumn(5); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("GROSS AMOUNT"); cell26.setCellStyle(styleC3Center); sheet.autoSizeColumn(6); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("VAT AMOUNT"); cell27.setCellStyle(styleC3Center); sheet.autoSizeColumn(7); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("AMOUNT"); cell28.setCellStyle(styleC3Center); sheet.autoSizeColumn(8); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("FLAG TYPE"); cell29.setCellStyle(styleC3Center); sheet.autoSizeColumn(9); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("INVOICE TYPE"); cell30.setCellStyle(styleC3Center); sheet.autoSizeColumn(10); HSSFCell cell31 = row2.createCell(11); cell31.setCellValue("TAX NO 1"); cell31.setCellStyle(styleC3Center); sheet.autoSizeColumn(11); HSSFCell cell32 = row2.createCell(12); cell32.setCellValue("BRANCH"); cell32.setCellStyle(styleC3Center); sheet.autoSizeColumn(12); HSSFCell cell33 = row2.createCell(13); cell33.setCellValue("BRANCH NO"); cell33.setCellStyle(styleC3Center); sheet.autoSizeColumn(13); int count = 3; 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); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); 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")); for (int i = 0; i < reptax.size(); i++) { ReportTaxInvoice data = (ReportTaxInvoice) reptax.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getId()); cell0.setCellStyle(styleC23); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getTaxid()); cell1.setCellStyle(styleC23); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getTaxno()); cell13.setCellStyle(styleC23); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(String.valueOf(data.getTaxdate())); cell2.setCellStyle(styleC23); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getCodeap()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getDescription().trim()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(!"null".equalsIgnoreCase(String.valueOf(data.getGrossamount())) ? (data.getGrossamount()).doubleValue() : 0); cell5.setCellStyle(styleC25); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(!"null".equalsIgnoreCase(String.valueOf(data.getVatamount())) ? (data.getVatamount()).doubleValue() : 0); cell6.setCellStyle(styleC25); HSSFCell cell7 = row.createCell(8); cell7.setCellValue( !"null".equalsIgnoreCase(String.valueOf(data.getAmount())) ? (data.getAmount()).doubleValue() : 0); cell7.setCellStyle(styleC25); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getFlagtype()); cell8.setCellStyle(styleC23); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getInvoicetype()); cell9.setCellStyle(styleC24); HSSFCell cell10 = row.createCell(11); cell10.setCellValue(data.getTaxno1()); cell10.setCellStyle(styleC24); HSSFCell cell11 = row.createCell(12); cell11.setCellValue(data.getBranch()); cell11.setCellStyle(styleC24); HSSFCell cell12 = row.createCell(13); cell12.setCellValue(data.getBranchno()); cell12.setCellStyle(styleC23); } for (int j = 0; j < 15; j++) { sheet.autoSizeColumn(j); } sheet.setColumnWidth(5, 256 * 40);//27 exportFileExcel("TaxInvoiceReport", wb); }
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportAgentReport(List repAgent) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Agent"); 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); HSSFRow row1 = sheet.createRow(0);/*from w w w . j a va 2 s .c om*/ HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("Agent Report"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row2 = sheet.createRow(2); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("ID"); cell20.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("SYSTEM DATE"); cell21.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("SYSTEM STAFF"); cell22.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("CODE"); cell23.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("NAME"); cell24.setCellStyle(styleC3Center); sheet.autoSizeColumn(4); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("ADDRESS"); cell25.setCellStyle(styleC3Center); sheet.autoSizeColumn(5); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("TEL"); cell26.setCellStyle(styleC3Center); sheet.autoSizeColumn(6); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("FAX"); cell27.setCellStyle(styleC3Center); sheet.autoSizeColumn(7); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("DESCRIPTION"); cell28.setCellStyle(styleC3Center); sheet.autoSizeColumn(8); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("NAME T"); cell29.setCellStyle(styleC3Center); sheet.autoSizeColumn(9); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("DESCRIPTION T"); cell30.setCellStyle(styleC3Center); sheet.autoSizeColumn(10); HSSFCell cell31 = row2.createCell(11); cell31.setCellValue("ADDRESS T"); cell31.setCellStyle(styleC3Center); sheet.autoSizeColumn(11); HSSFCell cell32 = row2.createCell(12); cell32.setCellValue("EMAIL"); cell32.setCellStyle(styleC3Center); sheet.autoSizeColumn(12); HSSFCell cell33 = row2.createCell(13); cell33.setCellValue("WEB"); cell33.setCellStyle(styleC3Center); sheet.autoSizeColumn(13); HSSFCell cell34 = row2.createCell(14); cell34.setCellValue("REMARKS"); cell34.setCellStyle(styleC3Center); sheet.autoSizeColumn(14); HSSFCell cell35 = row2.createCell(15); cell35.setCellValue("WARNING"); cell35.setCellStyle(styleC3Center); sheet.autoSizeColumn(15); HSSFCell cell36 = row2.createCell(16); cell36.setCellValue("REF ID"); cell36.setCellStyle(styleC3Center); sheet.autoSizeColumn(16); HSSFCell cell37 = row2.createCell(17); cell37.setCellValue("BRANCH"); cell37.setCellStyle(styleC3Center); sheet.autoSizeColumn(17); HSSFCell cell38 = row2.createCell(18); cell38.setCellValue("BRANCH NO"); cell38.setCellStyle(styleC3Center); sheet.autoSizeColumn(18); HSSFCell cell39 = row2.createCell(19); cell39.setCellValue("TAX NO"); cell39.setCellStyle(styleC3Center); sheet.autoSizeColumn(19); int count = 3; 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); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); 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")); for (int i = 0; i < repAgent.size(); i++) { ReportAgent data = (ReportAgent) repAgent.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getId()); cell0.setCellStyle(styleC23); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(String.valueOf(data.getSystemdate())); cell1.setCellStyle(styleC23); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getSystemstaff()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getCode()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getName()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getAddress().trim()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getTel()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getFax()); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getDescription().trim()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getNameT()); cell8.setCellStyle(styleC24); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getDescriptionT().trim()); cell9.setCellStyle(styleC24); HSSFCell cell10 = row.createCell(11); cell10.setCellValue(data.getAddressT().trim()); cell10.setCellStyle(styleC24); HSSFCell cell11 = row.createCell(12); cell11.setCellValue(data.getEmail()); cell11.setCellStyle(styleC24); HSSFCell cell12 = row.createCell(13); cell12.setCellValue(data.getWeb()); cell12.setCellStyle(styleC24); HSSFCell cell14 = row.createCell(14); cell14.setCellValue(data.getRemarks()); cell14.setCellStyle(styleC24); HSSFCell cell15 = row.createCell(15); cell15.setCellValue(data.getWarning()); cell15.setCellStyle(styleC24); HSSFCell cell16 = row.createCell(16); cell16.setCellValue(data.getRefid()); cell16.setCellStyle(styleC24); HSSFCell cell17 = row.createCell(17); cell17.setCellValue(data.getBranch()); cell17.setCellStyle(styleC24); HSSFCell cell18 = row.createCell(18); cell18.setCellValue(data.getBranchno()); cell18.setCellStyle(styleC24); HSSFCell cell19 = row.createCell(19); cell19.setCellValue(data.getTaxno()); cell19.setCellStyle(styleC23); } for (int j = 0; j < 20; j++) { sheet.autoSizeColumn(j); } for (int k = 4; k < 21; k++) { if (k != 6 && k != 7 && k != 12 && k != 13 && k < 16) { sheet.setColumnWidth(k, 256 * 35);//27 } else { sheet.setColumnWidth(k, 256 * 20);//27 } } exportFileExcel("AgentReport", wb); }
From source file:com.softtek.mdm.web.admin.IndexController.java
private void exportExcel(String sheetName, String[] headNames, List<OrganizationModel> lists, HttpServletResponse response) {//from w ww . j a v a 2 s . co m OutputStream out = null; try { HSSFWorkbook workbook = new HSSFWorkbook(); // HSSFSheet sheet = workbook.createSheet(sheetName); // // HSSFRow rowm = sheet.createRow(0); HSSFCell cellTiltle = rowm.createCell(0); //sheet??getColumnTopStyle()/getStyle()? - ? - ? HSSFCellStyle columnTopStyle = CommUtil.getColumnTopStyle(workbook);//?? sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (headNames.length - 1))); cellTiltle.setCellStyle(columnTopStyle); cellTiltle.setCellValue(sheetName); // int columnNum = headNames.length; HSSFRow rowRowName = sheet.createRow(2); // 2?() // sheet? for (int n = 0; n < columnNum; n++) { HSSFCell cellRowName = rowRowName.createCell(n); //? cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //?? HSSFRichTextString text = new HSSFRichTextString(headNames[n]); cellRowName.setCellValue(text); //? cellRowName.setCellStyle(columnTopStyle); //?? } //?sheet? HSSFDataFormat format = workbook.createDataFormat(); short formatDate = format.getFormat("yyyy-MM-dd hh:mm:ss"); for (int i = 0; i < lists.size(); i++) { HSSFRow row = sheet.createRow(i + 3);// OrganizationModel obj = lists.get(i);//??? /*row.createCell(0).setCellValue(obj.getOrgType());*/ row.createCell(0).setCellValue(obj.getName()); row.createCell(1).setCellValue(obj.getCreateName()); row.createCell(2).setCellValue(obj.getTotalUsers() == null ? 0 : obj.getTotalUsers()); row.createCell(3).setCellValue(obj.getTotalDevices() == null ? 0 : obj.getTotalDevices()); row.createCell(4).setCellValue(obj.getLicenseCount() == null ? 0 : obj.getLicenseCount()); row.createCell(5).setCellValue(obj.getUseUsers() == null ? 0 : obj.getUseUsers()); HSSFCell cell = row.createCell(6); cell.setCellValue(obj.getCreateTime()); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(formatDate); cell.setCellStyle(cellStyle); } //?? for (int colNum = 0; colNum < columnNum; colNum++) { int columnWidth = sheet.getColumnWidth(colNum) / 256; for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) { HSSFRow currentRow; //? currentRow = (sheet.getRow(rowNum) == null) ? sheet.createRow(rowNum) : sheet.getRow(rowNum); if (currentRow.getCell(colNum) != null) { HSSFCell currentCell = currentRow.getCell(colNum); if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { int length = currentCell.getStringCellValue().getBytes().length; if (columnWidth < length) { columnWidth = length; } } } } sheet.setColumnWidth(colNum, (colNum == 0) ? (columnWidth * 256) : ((columnWidth + 10) * 256)); } if (workbook != null) { try { String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls"; String headStr = "attachment; filename=\"" + fileName + "\""; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", headStr); out = response.getOutputStream(); workbook.write(out); } catch (IOException e) { logger.error(e.getMessage()); } finally { if (out != null) { out.close(); } } } } catch (Exception e) { logger.error(e.getMessage()); } }
From source file:com.square.core.util.poi.DocumentXls.java
License:Open Source License
/** Cree une page. */ private HSSFSheet creerPage() { final HSSFSheet page = classeur.createSheet(); page.setMargin(HSSFSheet.LeftMargin, LARGEUR_MARGE); page.setMargin(HSSFSheet.RightMargin, LARGEUR_MARGE); row = 0;/* w ww. j a va 2s . c om*/ if (entetesWidth != null) { // Dfinition de la largeur des colonnes int col = 0; for (Integer enteteWidth : entetesWidth) { if (enteteWidth != null) { page.setColumnWidth(col, enteteWidth); } col++; } } final HSSFCellStyle styleEntete = classeur.createCellStyle(); final HSSFFont policeEntete = classeur.createFont(); policeEntete.setFontHeightInPoints(ENTETE_FONT_HEIGHT); policeEntete.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); styleEntete.setFont(policeEntete); // Affichage des titres de colonnes ajouterLigne(entetes, styleEntete); return page; }
From source file:com.tm.hiber.service.util.DatabaseUtil.java
/** * * @param objFile/* w w w. j a va 2s . c o m*/ * @param objJTable * @return number of records exported */ public int exportData(File objFile, JTable objJTable) throws FileNotFoundException, IOException { int response = 0; if (objFile == null) { return response; } DefaultTableModel tm = (DefaultTableModel) objJTable.getModel(); Object[] rows = tm.getDataVector().toArray(); JTableHeader columnNames = objJTable.getTableHeader(); TableColumnModel columnModel = columnNames.getColumnModel(); int columnCount = columnModel.getColumnCount(); Vector<String> vecColumnNames = new Vector<String>(); for (int c = 0; c < columnCount; c++) { vecColumnNames.add(columnModel.getColumn(c).getHeaderValue().toString()); } HSSFWorkbook exportReadyWorkbook = new HSSFWorkbook(); HSSFSheet dataSheet = exportReadyWorkbook.createSheet(getExportSheetName()); if (vecColumnNames.size() > 0) { int columnCounter = 0; Row objHSSFColumnName = dataSheet.createRow(0); for (String strColumnName : vecColumnNames) { /* Set Header CSS */ Cell objHSSFCell = objHSSFColumnName.createCell(columnCounter); objHSSFCell.setCellValue(strColumnName); CellStyle csll = exportReadyWorkbook.createCellStyle(); Font objFont = exportReadyWorkbook.createFont(); objFont.setFontName("Calibri"); objFont.setColor(IndexedColors.BLACK.index); objFont.setBold(true); csll.setFont(objFont); csll.setFillBackgroundColor(HSSFColor.YELLOW.index); csll.setFillForegroundColor(HSSFColor.YELLOW.index); csll.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csll.setAlignment(HSSFCellStyle.ALIGN_CENTER); //csll.setWrapText(true); objHSSFCell.setCellStyle(csll); columnCounter++; } } if (rows != null && rows.length > 0) { /* Set Data into Sheet */ for (int i = 0; i < rows.length; i++) { Vector objCellsData = (Vector) rows[i]; Row objHSSFRow = dataSheet.createRow(i + 1); if (objCellsData != null && objCellsData.size() > 0) { for (int j = 0; j < objCellsData.size(); j++) { /* Set Cell Data CSS */ Cell objHSSFCell = objHSSFRow.createCell(j); CellStyle csll = exportReadyWorkbook.createCellStyle(); Font objFont = exportReadyWorkbook.createFont(); objFont.setColor(IndexedColors.BLACK.index); objFont.setBold(false); objFont.setFontName("Calibri"); csll.setFont(objFont); csll.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csll.setFillBackgroundColor(IndexedColors.WHITE.index); csll.setFillForegroundColor(IndexedColors.WHITE.index); csll.setAlignment(HSSFCellStyle.ALIGN_CENTER); //csll.setWrapText(true); csll.setBorderBottom(CellStyle.BORDER_THIN); csll.setBorderTop(CellStyle.BORDER_THIN); csll.setBottomBorderColor(HSSFColor.GREY_25_PERCENT.index); csll.setTopBorderColor(HSSFColor.GREY_50_PERCENT.index); objHSSFCell.setCellStyle(csll); Object cellData = objCellsData.get(j); objHSSFCell.setCellValue((String) cellData); } } } for (int i = 0; i < columnCount; i++) { if (i == 2) { dataSheet.setColumnWidth(i, 30 * 256); } else { dataSheet.autoSizeColumn(i); } } /* Write File */ FileOutputStream objFileOutputStream = new FileOutputStream(objFile); exportReadyWorkbook.write(objFileOutputStream); objFileOutputStream.flush(); objFileOutputStream.close(); response = rows.length; } return response; }
From source file:com.topsec.tsm.sim.asset.web.AssetListController.java
/** * // w w w .jav a2 s .c om * @param request * @param response */ @RequestMapping("exportAssetExcel") public void exportAssetExcel(SID sid, HttpServletRequest request, HttpServletResponse response) { //? List<AssetObject> assetList; SID.setCurrentUser(sid); try { assetList = AssetFacade.getInstance().getAll(); Collections.sort(assetList, IpComparator.getInstance()); } finally { SID.removeCurrentUser(); } String[] column = { "??(*)", "IP(*)", "??(*)", "(*)", "?(*)", "?(*)", "??", "?", "", null, null, null, null, "?", "??" }; // Excel HSSFWorkbook workbook = new HSSFWorkbook(); // Excel??Asset?? HSSFSheet sheet = workbook.createSheet("?"); //? HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // HSSFRow row = sheet.createRow(0); for (int j = 0; j < column.length; j++) { HSSFCell cell = row.createCell(j); if (column[j] == null) { sheet.setColumnHidden(j, true); continue; } sheet.setColumnWidth(j, 22 * 256); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(column[j]); } NodeMgrFacade nodeMgrFacade = (NodeMgrFacade) SpringContextServlet.springCtx.getBean("nodeMgrFacade"); //?? for (int i = 0; i < assetList.size(); i++) { AssetObject ao = assetList.get(i); row = sheet.createRow(i + 1); for (int j = 0; j < column.length; j++) { HSSFCell cell = row.createCell(j); switch (j) { case 0: cell.setCellValue(i + 1); cell.setCellStyle(cellStyle); continue; case 1: cell.setCellValue(ao.getIp()); continue; case 2: cell.setCellValue(ao.getName()); continue; case 3: String deviceType = ao.getDeviceType().split("/")[0]; cell.setCellValue( DeviceTypeShortKeyUtil.getInstance().getShortZhCN(deviceType) + "_" + deviceType); continue; case 4: String vendor = ao.getDeviceType().split("/")[1]; cell.setCellValue(DeviceTypeShortKeyUtil.getInstance().getShortZhCN(vendor) + "_" + vendor); continue; case 5: Node node = nodeMgrFacade.getNodeByNodeId(ao.getScanNodeId()); cell.setCellValue(node != null ? node.getIp() : ""); continue; case 6: cell.setCellValue(ao.getHostName()); continue; case 7: cell.setCellValue(ao.getOs() != null ? ao.getOs().getOsName() : ""); continue; case 8: cell.setCellValue(ao.getSafeRank()); continue; case 9: cell.setCellValue(ao.getAssGroup().getGroupName()); continue; case 10: case 11: case 12: continue; case 13: cell.setCellValue(StringUtil.nvl(ao.getLinkman())); continue; case 14: cell.setCellValue(""); continue; default: cell.setCellValue(""); continue; } } } //?excel? //response.setContentType("application/vnd.ms-excel"); String userAgent = request.getHeader("User-Agent"); String fileName = "?.xls"; if (userAgent.indexOf("Firefox") > 0) { response.setHeader("Content-Disposition", "attachment; filename*=\"utf8' '" + StringUtil.encode(fileName, "UTF-8") + "\""); } else { response.addHeader("Content-Disposition", "attachment; filename=\"" + StringUtil.encode(fileName, "UTF-8") + "\""); } try { workbook.write(response.getOutputStream()); } catch (IOException e) { e.printStackTrace(); } }
From source file:com.ts.excelservlet.UDR_Driver_Excel.java
/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) *//* w ww . jav 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 a va2s . 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"); } }