List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setAlignment
@Override public void setAlignment(HorizontalAlignment align)
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function checkHosoByNHOMTTHCID * Version: 1.0/*from ww w . ja v a2s . c om*/ * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelTonghopTongquat(ResourceRequest req, ResourceResponse res) { try { String thang = req.getParameter("thang"); String nam = req.getParameter("nam"); String id_donvi = req.getParameter("id_donvi"); String id_linhvuc = req.getParameter("id_linhvuc"); String coQuanQuanLyId = req.getParameter("id_coquanquanly"); CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); if (coQuanQuanLyId != null && !coQuanQuanLyId.equals("0")) { coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId)); } List<ReportTongHop> listtonghop = ReportTongHopLocalServiceUtil .getListReportTongHopGroupByIDNhomThuTuc(id_donvi, id_linhvuc, thang, nam); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Thongke"); // define a cell style HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 5000); HSSFFont ngaythangFont = (HSSFFont) workbook.createFont(); ngaythangFont.setItalic(true); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 5000); titleFont.setFontHeightInPoints((short) 15); HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle(); styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellHeader.setFont(tableHeaderFont); styleCellHeader.setWrapText(true); HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle(); styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNgaythang.setFont(ngaythangFont); styleCellNgaythang.setWrapText(true); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); styleCellRight.setFont(tableHeaderFont); HSSFCellStyle styleCellRightNobold = (HSSFCellStyle) workbook.createCellStyle(); styleCellRightNobold.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRightNobold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRightNobold.setWrapText(true); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle(); styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNhomTTHC.setFont(tableHeaderFont); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // end style int rowNum = 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowUBND = sheet.createRow(rowNum); rowUBND.createCell(0).setCellValue(""); rowUBND.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 14)); rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn")); rowUBND.getCell(2).setCellStyle(styleCellHeader); rowNum++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowTenDonVi = sheet.createRow(rowNum); rowTenDonVi.createCell(0).setCellValue(coQuanQuanLy.getTen()); rowTenDonVi.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 14)); rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp")); rowTenDonVi.getCell(2).setCellStyle(styleCellHeader); rowNum++; String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaotonghop.tieude"); String nhomtthc = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaotonghop.nhomtthc"); String tong = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tong"); String tiepnhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tiepnhan"); String kytruocchuyenqua = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.kytruocchuyenqua"); String hosodagiaiquyet = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosodagiaiquyet"); String hosodanggiaiquyet = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosodanggiaiquyet"); String cdruthoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.cdruthoso"); String hosotralai = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosotralai"); String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.ghichu"); String sohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.sohoso"); String som = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.som"); String dung = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.dung"); String tre = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tre"); String chuadenhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.chuadenhan"); String daquahan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.daquahan"); String[] header = { "STT", nhomtthc, tong, tiepnhan, kytruocchuyenqua, sohoso, som, dung, tre, sohoso, chuadenhan, daquahan, cdruthoso, hosotralai, ghichu }; rowNum++; int[] width = { 1500, 10000, 2000, 2000, 3000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 3000, 3000, 3000 }; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 14)); HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styleTitle); titleCell.setCellValue(tieude); rowNum += 2; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 6)); rowNum = rowNum + 2; HSSFRow headerRow1 = (HSSFRow) sheet.createRow(rowNum); HSSFRow headerRow2 = (HSSFRow) sheet.createRow(rowNum + 1); sheet.addMergedRegion(new Region(rowNum, (short) 5, rowNum, (short) 8)); sheet.addMergedRegion(new Region(rowNum, (short) 9, rowNum, (short) 11)); for (int i = 0; i < header.length; i++) { if (i < 5 || i > 11) { sheet.addMergedRegion(new Region(rowNum, (short) i, rowNum + 1, (short) i)); } HSSFCell cell = headerRow1.createCell(i); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); HSSFCell cell2 = headerRow2.createCell(i); cell2.setCellStyle(styleHeader); cell2.setCellValue(header[i]); if (i == 5) { cell.setCellValue(hosodagiaiquyet); } if (i == 9) { cell.setCellValue(hosodanggiaiquyet); } sheet.setColumnWidth(i, width[i]); } rowNum += 2; int tongNhan = 0; int tongTiepNhan = 0; int tongTonDau = 0; int tongDaGiaiQuyet = 0; int tongSom = 0; int tongDung = 0; int tongTre = 0; int tongDangGiaiQuyet = 0; int tongChuaDenHan = 0; int tongDaQuaHan = 0; int tongRutHoSo = 0; int tongKhongHopLe = 0; int tongTonCuoi = 0; int stt = 1; for (int a = 0; a < listtonghop.size(); a++) { tongNhan += listtonghop.get(a).getTONGNHAN(); tongTiepNhan += listtonghop.get(a).getTIEPNHAN(); tongTonDau += listtonghop.get(a).getTONDAU(); tongDaGiaiQuyet += listtonghop.get(a).getTONGDAGIAIQUYET(); tongSom += listtonghop.get(a).getSOM(); tongDung += listtonghop.get(a).getDUNG(); tongTre += listtonghop.get(a).getTRE(); tongDangGiaiQuyet += listtonghop.get(a).getTONGDANGGIAIQUYET(); tongChuaDenHan += listtonghop.get(a).getCHUADENHAN(); tongDaQuaHan += listtonghop.get(a).getQUAHAN(); tongRutHoSo += listtonghop.get(a).getRUTHOSO(); tongKhongHopLe += listtonghop.get(a).getKHONGHOPLE(); tongTonCuoi += listtonghop.get(a).getTONCUOI(); // khai bao dong va cac cell HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); HSSFCell cell13 = row.createCell(13); HSSFCell cell14 = row.createCell(14); // set style cho cac cell cell.setCellStyle(styleCellLeft); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); cell13.setCellStyle(styleCellCenter); cell14.setCellStyle(styleCellCenter); // set gia tri cho cac cell cell.setCellValue(stt); //cell1.setCellValue(""); cell1.setCellValue(NhomThuTucHanhChinhLocalServiceUtil .getNhomThuTucHanhChinh(listtonghop.get(a).getNHOMTHUTUCHANHCHINHID()).getTen()); cell2.setCellValue(listtonghop.get(a).getTONGNHAN()); cell3.setCellValue(listtonghop.get(a).getTIEPNHAN()); cell4.setCellValue(listtonghop.get(a).getTONDAU()); cell5.setCellValue(listtonghop.get(a).getTONGDAGIAIQUYET()); cell6.setCellValue(listtonghop.get(a).getSOM()); cell7.setCellValue(listtonghop.get(a).getDUNG()); cell8.setCellValue(listtonghop.get(a).getTRE()); cell9.setCellValue(listtonghop.get(a).getTONGDANGGIAIQUYET()); cell10.setCellValue(listtonghop.get(a).getCHUADENHAN()); cell11.setCellValue(listtonghop.get(a).getQUAHAN()); cell12.setCellValue(listtonghop.get(a).getRUTHOSO()); cell13.setCellValue(listtonghop.get(a).getKHONGHOPLE()); cell14.setCellValue(listtonghop.get(a).getTONCUOI()); rowNum++; stt++; } //row tong sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); HSSFCell cell13 = row.createCell(13); HSSFCell cell14 = row.createCell(14); // set style cho cac cell cell.setCellStyle(styleHeader); cell1.setCellStyle(styleHeader); cell2.setCellStyle(styleHeader); cell3.setCellStyle(styleHeader); cell4.setCellStyle(styleHeader); cell5.setCellStyle(styleHeader); cell6.setCellStyle(styleHeader); cell7.setCellStyle(styleHeader); cell8.setCellStyle(styleHeader); cell9.setCellStyle(styleHeader); cell10.setCellStyle(styleHeader); cell11.setCellStyle(styleHeader); cell12.setCellStyle(styleHeader); cell13.setCellStyle(styleHeader); cell14.setCellStyle(styleHeader); // set gia tri cho cac cell cell.setCellValue("Tng"); //cell1.setCellValue(""); cell2.setCellValue(tongNhan); cell3.setCellValue(tongTiepNhan); cell4.setCellValue(tongTonDau); cell5.setCellValue(tongDaGiaiQuyet); cell6.setCellValue(tongSom); cell7.setCellValue(tongDung); cell8.setCellValue(tongTre); cell9.setCellValue(tongDangGiaiQuyet); cell10.setCellValue(tongChuaDenHan); cell11.setCellValue(tongDaQuaHan); cell12.setCellValue(tongRutHoSo); cell13.setCellValue(tongKhongHopLe); cell14.setCellValue(tongTonCuoi); //ket thuc row tong //ket thuc noi dung bao cao res.setContentType("application/vnd.ms-excel"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename='Baocaotonghop.xls'"); OutputStream out = res.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function checkHosoByNHOMTTHCID * Version: 1.0//from www .java 2 s . co m * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelThongke(ResourceRequest req, ResourceResponse res) { try { String thang = req.getParameter("thang"); String nam = req.getParameter("nam"); String coQuanQuanLyId = req.getParameter("id_coquanquanly"); // CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); // if(coQuanQuanLyId!=null && !coQuanQuanLyId.equals("0")){ // coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId)); // } List<ReportThongKe> listthongke = ReportThongKeLocalServiceUtil .getListReportThongKeGroupByIDCoQuan(coQuanQuanLyId, thang, nam); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Thongke"); // define a cell style HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 5000); HSSFFont ngaythangFont = (HSSFFont) workbook.createFont(); ngaythangFont.setItalic(true); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 5000); titleFont.setFontHeightInPoints((short) 15); HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle(); styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellHeader.setFont(tableHeaderFont); styleCellHeader.setWrapText(true); HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle(); styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNgaythang.setFont(ngaythangFont); styleCellNgaythang.setWrapText(true); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); styleCellRight.setFont(tableHeaderFont); HSSFCellStyle styleCellRightNobold = (HSSFCellStyle) workbook.createCellStyle(); styleCellRightNobold.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRightNobold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRightNobold.setWrapText(true); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle(); styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNhomTTHC.setFont(tableHeaderFont); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // end style int rowNum = 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowUBND = sheet.createRow(rowNum); rowUBND.createCell(0).setCellValue(""); rowUBND.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 14)); rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn")); rowUBND.getCell(2).setCellStyle(styleCellHeader); rowNum++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowTenDonVi = sheet.createRow(rowNum); rowTenDonVi.createCell(0).setCellValue(""); rowTenDonVi.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 14)); rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp")); rowTenDonVi.getCell(2).setCellStyle(styleCellHeader); rowNum++; String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tieude"); String coquan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.coquan"); String tong = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tong"); String tiepnhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tiepnhan"); String kytruocchuyenqua = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.kytruocchuyenqua"); String hosodagiaiquyet = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosodagiaiquyet"); String hosodanggiaiquyet = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosodanggiaiquyet"); String cdruthoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.cdruthoso"); String hosotralai = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosotralai"); String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.ghichu"); String sohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.sohoso"); String som = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.som"); String dung = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.dung"); String tre = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tre"); String chuadenhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.chuadenhan"); String daquahan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.daquahan"); String[] header = { "STT", coquan, tong, tiepnhan, kytruocchuyenqua, sohoso, som, dung, tre, sohoso, chuadenhan, daquahan, cdruthoso, hosotralai, ghichu }; rowNum++; int[] width = { 1500, 10000, 2000, 2000, 3000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 3000, 3000, 3000 }; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 14)); HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styleTitle); titleCell.setCellValue(tieude); rowNum += 2; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 6)); rowNum = rowNum + 2; HSSFRow headerRow1 = (HSSFRow) sheet.createRow(rowNum); HSSFRow headerRow2 = (HSSFRow) sheet.createRow(rowNum + 1); sheet.addMergedRegion(new Region(rowNum, (short) 5, rowNum, (short) 8)); sheet.addMergedRegion(new Region(rowNum, (short) 9, rowNum, (short) 11)); for (int i = 0; i < header.length; i++) { if (i < 5 || i > 11) { sheet.addMergedRegion(new Region(rowNum, (short) i, rowNum + 1, (short) i)); } HSSFCell cell = headerRow1.createCell(i); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); HSSFCell cell2 = headerRow2.createCell(i); cell2.setCellStyle(styleHeader); cell2.setCellValue(header[i]); if (i == 5) { cell.setCellValue(hosodagiaiquyet); } if (i == 9) { cell.setCellValue(hosodanggiaiquyet); } sheet.setColumnWidth(i, width[i]); } rowNum += 2; long tongnhan = 0; long tongtiepnhan = 0; long tondau = 0; long dagiaiquyet = 0; long tongsom = 0; long tongdung = 0; long tongtre = 0; long danggiaiquyet = 0; long tongchuadenhan = 0; long quahan = 0; long ruthoso = 0; long khonghople = 0; long toncuoi = 0; int stt = 1; for (int a = 0; a < listthongke.size(); a++) { ReportThongKe thongke = listthongke.get(a); tongnhan += thongke.getTONGNHAN(); tongtiepnhan += thongke.getTIEPNHAN(); tondau += thongke.getTONDAU(); dagiaiquyet += thongke.getTONGDAGIAIQUYET(); tongsom += thongke.getSOM(); tongdung += thongke.getDUNG(); tongtre += thongke.getTRE(); danggiaiquyet += thongke.getTONGDANGGIAIQUYET(); tongchuadenhan += thongke.getCHUADENHAN(); quahan += thongke.getQUAHAN(); ruthoso += thongke.getRUTHOSO(); khonghople += thongke.getKHONGHOPLE(); toncuoi += thongke.getTONCUOI(); // khai bao dong va cac cell HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); HSSFCell cell13 = row.createCell(13); HSSFCell cell14 = row.createCell(14); // set style cho cac cell cell.setCellStyle(styleCellLeft); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); cell13.setCellStyle(styleCellCenter); cell14.setCellStyle(styleCellCenter); // set gia tri cho cac cell cell.setCellValue(stt); //cell1.setCellValue(""); cell1.setCellValue(CoQuanQuanLyLocalServiceUtil .getCoQuanQuanLy(listthongke.get(a).getCOQUANQUANLYID()).getTen()); cell2.setCellValue(listthongke.get(a).getTONGNHAN()); cell3.setCellValue(listthongke.get(a).getTIEPNHAN()); cell4.setCellValue(listthongke.get(a).getTONDAU()); cell5.setCellValue(listthongke.get(a).getTONGDAGIAIQUYET()); cell6.setCellValue(listthongke.get(a).getSOM()); cell7.setCellValue(listthongke.get(a).getDUNG()); cell8.setCellValue(listthongke.get(a).getTRE()); cell9.setCellValue(listthongke.get(a).getTONGDANGGIAIQUYET()); cell10.setCellValue(listthongke.get(a).getCHUADENHAN()); cell11.setCellValue(listthongke.get(a).getQUAHAN()); cell12.setCellValue(listthongke.get(a).getRUTHOSO()); cell13.setCellValue(listthongke.get(a).getKHONGHOPLE()); cell14.setCellValue(listthongke.get(a).getTONCUOI()); rowNum++; stt++; } sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); HSSFCell cell13 = row.createCell(13); HSSFCell cell14 = row.createCell(14); // set style cho cac cell //cell.setCellStyle(styleCellCenter); cell.setCellStyle(styleCellCenter); cell1.setCellStyle(styleCellCenter); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); cell13.setCellStyle(styleCellCenter); cell14.setCellStyle(styleCellCenter); // set gia tri cho cac cell //cell1.setCellValue(""); cell.setCellValue("Tng cng"); cell2.setCellValue(tongnhan); cell3.setCellValue(tongtiepnhan); cell4.setCellValue(tondau); cell5.setCellValue(dagiaiquyet); cell6.setCellValue(tongsom); cell7.setCellValue(tongdung); cell8.setCellValue(tongtre); cell9.setCellValue(danggiaiquyet); cell10.setCellValue(tongchuadenhan); cell11.setCellValue(quahan); cell12.setCellValue(ruthoso); cell13.setCellValue(khonghople); cell14.setCellValue(toncuoi); res.setContentType("application/vnd.ms-excel"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename='Baocaothongke.xls'"); OutputStream out = res.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function excelLephichitiet/*from w w w. j a va 2s .co m*/ * Version: 1.0 * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelLephichitiet(ResourceRequest req, ResourceResponse res) { try { String coQuanQuanLyId = req.getParameter("coQuanQuanLyId"); CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); if (coQuanQuanLyId != null && !coQuanQuanLyId.equals("0")) { coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId)); } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Tinh_hinh_thu_le_phi_chi_tiet"); // define a cell style HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 5000); HSSFFont ngaythangFont = (HSSFFont) workbook.createFont(); ngaythangFont.setItalic(true); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 5000); titleFont.setFontHeightInPoints((short) 15); HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle(); styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellHeader.setFont(tableHeaderFont); styleCellHeader.setWrapText(true); HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle(); styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNgaythang.setFont(ngaythangFont); styleCellNgaythang.setWrapText(true); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); styleCellRight.setFont(tableHeaderFont); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle(); styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNhomTTHC.setFont(tableHeaderFont); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // end style String tungay = ParamUtil.getString(req, "tungay"); String denngay = ParamUtil.getString(req, "denngay"); int rowNum = 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowUBND = sheet.createRow(rowNum); rowUBND.createCell(0).setCellValue(""); rowUBND.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 9)); rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn")); rowUBND.getCell(2).setCellStyle(styleCellHeader); rowNum++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowTenDonVi = sheet.createRow(rowNum); rowTenDonVi.createCell(0).setCellValue(coQuanQuanLy.getTen()); rowTenDonVi.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 9)); rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp")); rowTenDonVi.getCell(2).setCellStyle(styleCellHeader); rowNum++; List<NhomThuTucHanhChinh> listnhomtt = NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinhs(0, NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinhsCount()); List<Lephi_chitiet> listlephichitiet = Lephi_chitietLocalServiceUtil .lephichitiet(ParamUtil.getString(req, "id_loaihoso"), tungay, denngay); String loaihoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.loaihoso"); String nguoinop = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.nguoinop"); String chuhoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.chuhoso"); String diachi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.diachi"); String sobohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.sobohoso"); String lephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.lephi"); String phihoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.phihoso"); String tonglephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.tonglephi"); String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.ghichu"); String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.tieude"); String[] header = { "STT", loaihoso, nguoinop, chuhoso, diachi, sobohoso, lephi, phihoso, tonglephi, ghichu }; rowNum++; int[] width = { 1500, 15000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000 }; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 9)); HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styleTitle); titleCell.setCellValue(tieude); rowNum += 2; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 9)); HSSFRow ngaythangRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell ngaythangCell = ngaythangRow.createCell(0); ngaythangCell.setCellStyle(styleCellNgaythang); ngaythangCell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tungay") + " " + tungay + " " + ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.denngay") + " " + denngay); rowNum = rowNum + 3; HSSFRow headerRow = (HSSFRow) sheet.createRow(rowNum); int colNum = 0; for (int i = 0; i < header.length; i++) { HSSFCell cell = headerRow.createCell(colNum); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); sheet.setColumnWidth(i, width[i]); colNum++; } rowNum++; long tong = 0; int stt = 0; for (int j = 0; j < listnhomtt.size(); j++) { if (ActionUtil.checkHosoByNHOMTTHCID(listnhomtt.get(j).getId(), listlephichitiet)) { sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 9)); HSSFRow rowNhomTTHC = (HSSFRow) sheet.createRow(rowNum); HSSFCell cellNhomTTHC = rowNhomTTHC.createCell(0); cellNhomTTHC.setCellValue(listnhomtt.get(j).getTen()); cellNhomTTHC.setCellStyle(styleCellNhomTTHC); rowNhomTTHC.createCell(9).setCellStyle(styleCellNhomTTHC); rowNum++; for (int a = 0; a < listlephichitiet.size(); a++) { // khai bao dong va cac cell if (listnhomtt.get(j).getId() == listlephichitiet.get(a).getNHOMTHUTUCHANHCHINHID()) { stt++; HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); // set style cho cac cell cell.setCellStyle(styleCellCenter); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); // set gia tri cho cac cell cell.setCellValue(stt); cell1.setCellValue(listlephichitiet.get(a).getTENHOSO()); cell2.setCellValue(listlephichitiet.get(a).getNGUOINOP()); cell3.setCellValue(listlephichitiet.get(a).getCHUHOSO()); cell4.setCellValue(listlephichitiet.get(a).getDIACHI()); cell5.setCellValue(listlephichitiet.get(a).getSOBOHOSO()); cell6.setCellValue(listlephichitiet.get(a).getLEPHI()); cell7.setCellValue(listlephichitiet.get(a).getPHIHOSO()); cell8.setCellValue(listlephichitiet.get(a).getTONGLEPHI()); tong = tong + Long.valueOf(listlephichitiet.get(a).getTONGLEPHI()); rowNum++; } } } } sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 7)); HSSFRow rowTong = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = rowTong.createCell(0); HSSFCell cell1 = rowTong.createCell(1); HSSFCell cell2 = rowTong.createCell(2); HSSFCell cell3 = rowTong.createCell(3); HSSFCell cell4 = rowTong.createCell(4); HSSFCell cell5 = rowTong.createCell(5); HSSFCell cell6 = rowTong.createCell(6); HSSFCell cell7 = rowTong.createCell(7); HSSFCell cell8 = rowTong.createCell(8); HSSFCell cell9 = rowTong.createCell(9); // set style cho cac cell cell.setCellStyle(styleCellRight); cell1.setCellStyle(styleCellCenter); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); // set value for cells of rowtong cell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tongcong")); cell8.setCellValue(tong); res.setContentType("application/vnd.ms-excel"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaotinhhinhlephi.xls"); OutputStream out = res.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function excelLephilinhvuc/* www. j ava 2s . c om*/ * Version: 1.0 * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelLephilinhvuc(ResourceRequest req, ResourceResponse res) { try { String coQuanQuanLyId = req.getParameter("coQuanQuanLyId"); CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); if (coQuanQuanLyId != null && !coQuanQuanLyId.equals("0")) { coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId)); } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Tinh_hinh_thu_le_phi_theo_linh_vuc"); // define a cell style HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 5000); HSSFFont ngaythangFont = (HSSFFont) workbook.createFont(); ngaythangFont.setItalic(true); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 5000); titleFont.setFontHeightInPoints((short) 15); HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle(); styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellHeader.setFont(tableHeaderFont); styleCellHeader.setWrapText(true); HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle(); styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNgaythang.setFont(ngaythangFont); styleCellNgaythang.setWrapText(true); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); styleCellRight.setFont(tableHeaderFont); HSSFCellStyle styleCellRightNobold = (HSSFCellStyle) workbook.createCellStyle(); styleCellRightNobold.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRightNobold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRightNobold.setWrapText(true); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle(); styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNhomTTHC.setFont(tableHeaderFont); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // end style String tungay = ParamUtil.getString(req, "tungay"); String denngay = ParamUtil.getString(req, "denngay"); int rowNum = 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowUBND = sheet.createRow(rowNum); rowUBND.createCell(0).setCellValue(""); rowUBND.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 6)); rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn")); rowUBND.getCell(2).setCellStyle(styleCellHeader); rowNum++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowTenDonVi = sheet.createRow(rowNum); rowTenDonVi.createCell(0).setCellValue(coQuanQuanLy.getTen()); rowTenDonVi.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 6)); rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp")); rowTenDonVi.getCell(2).setCellStyle(styleCellHeader); rowNum++; List<Lephi_linhvuc> listlephilinhvuc = Lephi_linhvucLocalServiceUtil .lephilinhvuc(ParamUtil.getString(req, "id_linhvuc"), tungay, denngay); String linhvuc = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.linhvuc"); String tongsohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tongsohoso"); String lephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.lephi"); String phihoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.phihoso"); String tonglephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tonglephi"); String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.ghichu"); String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tieude"); String[] header = { "STT", linhvuc, tongsohoso, lephi, phihoso, tonglephi, ghichu }; rowNum++; int[] width = { 1500, 15000, 5000, 5000, 5000, 5000, 5000, 5000, 5000 }; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 6)); HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styleTitle); titleCell.setCellValue(tieude); rowNum += 2; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 6)); HSSFRow ngaythangRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell ngaythangCell = ngaythangRow.createCell(0); ngaythangCell.setCellStyle(styleCellNgaythang); ngaythangCell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tungay") + " " + tungay + " " + ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.denngay") + " " + denngay); rowNum = rowNum + 3; HSSFRow headerRow = (HSSFRow) sheet.createRow(rowNum); int colNum = 0; for (int i = 0; i < header.length; i++) { HSSFCell cell = headerRow.createCell(colNum); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); sheet.setColumnWidth(i, width[i]); colNum++; } rowNum++; long tong = 0; int stt = 0; for (int a = 0; a < listlephilinhvuc.size(); a++) { // khai bao dong va cac cell stt++; HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); // set style cho cac cell cell.setCellStyle(styleCellCenter); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellRightNobold); cell4.setCellStyle(styleCellRightNobold); cell5.setCellStyle(styleCellRightNobold); cell6.setCellStyle(styleCellRightNobold); // set gia tri cho cac cell cell.setCellValue(stt); cell1.setCellValue(NhomThuTucHanhChinhLocalServiceUtil .getNhomThuTucHanhChinh(listlephilinhvuc.get(a).getNHOMTHUTUCHANHCHINHID()).getTen()); cell2.setCellValue(listlephilinhvuc.get(a).getTONGHOSO()); cell3.setCellValue(listlephilinhvuc.get(a).getLEPHIHOSO()); cell4.setCellValue(listlephilinhvuc.get(a).getPHIHOSO()); cell5.setCellValue(listlephilinhvuc.get(a).getTONGLEPHI()); tong = tong + Long.valueOf(listlephilinhvuc.get(a).getTONGLEPHI()); rowNum++; } sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 4)); HSSFRow rowTong = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = rowTong.createCell(0); HSSFCell cell1 = rowTong.createCell(1); HSSFCell cell2 = rowTong.createCell(2); HSSFCell cell3 = rowTong.createCell(3); HSSFCell cell4 = rowTong.createCell(4); HSSFCell cell5 = rowTong.createCell(5); HSSFCell cell6 = rowTong.createCell(6); // set style cho cac cell cell.setCellStyle(styleCellRight); cell1.setCellStyle(styleCellCenter); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellRightNobold); cell6.setCellStyle(styleCellCenter); // set value for cells of rowtong cell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tongcong")); cell5.setCellValue(tong); res.setContentType("application/vnd.ms-excel"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaotinhhinhlephi.xls"); OutputStream out = res.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function excelLephitonghop/*www .j av a 2 s .c om*/ * Version: 1.0 * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelLephitonghop(ResourceRequest req, ResourceResponse res) { try { String coQuanQuanLyId = req.getParameter("coQuanQuanLyId"); CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); if (coQuanQuanLyId != null && !coQuanQuanLyId.equals("0")) { coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId)); } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Tinh_hinh_thu_le_phi_tong_hop"); // define a cell style HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 5000); HSSFFont ngaythangFont = (HSSFFont) workbook.createFont(); ngaythangFont.setItalic(true); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 5000); titleFont.setFontHeightInPoints((short) 15); HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle(); styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellHeader.setFont(tableHeaderFont); styleCellHeader.setWrapText(true); HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle(); styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNgaythang.setFont(ngaythangFont); styleCellNgaythang.setWrapText(true); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); styleCellRight.setFont(tableHeaderFont); HSSFCellStyle styleCellRightNobold = (HSSFCellStyle) workbook.createCellStyle(); styleCellRightNobold.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRightNobold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRightNobold.setWrapText(true); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle(); styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNhomTTHC.setFont(tableHeaderFont); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // end style String tungay = ParamUtil.getString(req, "tungay"); String denngay = ParamUtil.getString(req, "denngay"); int rowNum = 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowUBND = sheet.createRow(rowNum); rowUBND.createCell(0).setCellValue(""); rowUBND.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 6)); rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn")); rowUBND.getCell(2).setCellStyle(styleCellHeader); rowNum++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowTenDonVi = sheet.createRow(rowNum); rowTenDonVi.createCell(0).setCellValue(coQuanQuanLy.getTen()); rowTenDonVi.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 6)); rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp")); rowTenDonVi.getCell(2).setCellStyle(styleCellHeader); rowNum++; List<NhomThuTucHanhChinh> listnhomtt = NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinhs(0, NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinhsCount()); List<Lephi_tonghop> listlephitonghop = Lephi_tonghopLocalServiceUtil .lephitonghop(ParamUtil.getString(req, "id_loaihoso"), tungay, denngay); String linhvuc = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.linhvuc"); String tongsohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tongsohoso"); String lephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.lephi"); String phihoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.phihoso"); String tonglephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tonglephi"); String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.ghichu"); String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephitonghop.tieude"); String[] header = { "STT", linhvuc, tongsohoso, lephi, phihoso, tonglephi, ghichu }; rowNum++; int[] width = { 1500, 15000, 5000, 5000, 5000, 5000, 5000, 5000, 5000 }; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 6)); HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styleTitle); titleCell.setCellValue(tieude); rowNum += 2; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 6)); HSSFRow ngaythangRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell ngaythangCell = ngaythangRow.createCell(0); ngaythangCell.setCellStyle(styleCellNgaythang); ngaythangCell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tungay") + " " + tungay + " " + ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.denngay") + " " + denngay); rowNum = rowNum + 3; HSSFRow headerRow = (HSSFRow) sheet.createRow(rowNum); int colNum = 0; for (int i = 0; i < header.length; i++) { HSSFCell cell = headerRow.createCell(colNum); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); sheet.setColumnWidth(i, width[i]); colNum++; } rowNum++; long tong = 0; int stt = 0; for (int j = 0; j < listnhomtt.size(); j++) { if (ActionUtil.checkLePhiTongHopByNHOMTTHCID(listnhomtt.get(j).getId(), listlephitonghop)) { sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 6)); HSSFRow rowNhomTTHC = (HSSFRow) sheet.createRow(rowNum); HSSFCell cellNhomTTHC = rowNhomTTHC.createCell(0); cellNhomTTHC.setCellValue(listnhomtt.get(j).getTen()); cellNhomTTHC.setCellStyle(styleCellNhomTTHC); rowNhomTTHC.createCell(6).setCellStyle(styleCellNhomTTHC); rowNum++; for (int a = 0; a < listlephitonghop.size(); a++) { // khai bao dong va cac cell if (listnhomtt.get(j).getId() == listlephitonghop.get(a).getNHOMTHUTUCHANHCHINHID()) { stt++; HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); // set style cho cac cell cell.setCellStyle(styleCellCenter); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellRightNobold); cell4.setCellStyle(styleCellRightNobold); cell5.setCellStyle(styleCellRightNobold); cell6.setCellStyle(styleCellRightNobold); // set gia tri cho cac cell cell.setCellValue(stt); cell1.setCellValue( ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephitonghop.thang") + " " + listlephitonghop.get(a).getTHANGNHAN()); cell2.setCellValue(listlephitonghop.get(a).getTONGHOSO()); cell3.setCellValue(listlephitonghop.get(a).getLEPHI()); cell4.setCellValue(listlephitonghop.get(a).getPHIHOSO()); cell5.setCellValue(listlephitonghop.get(a).getTONGLEPHI()); tong = tong + Long.valueOf(listlephitonghop.get(a).getTONGLEPHI()); rowNum++; } } } } sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 4)); HSSFRow rowTong = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = rowTong.createCell(0); HSSFCell cell1 = rowTong.createCell(1); HSSFCell cell2 = rowTong.createCell(2); HSSFCell cell3 = rowTong.createCell(3); HSSFCell cell4 = rowTong.createCell(4); HSSFCell cell5 = rowTong.createCell(5); HSSFCell cell6 = rowTong.createCell(6); // set style cho cac cell cell.setCellStyle(styleCellRight); cell1.setCellStyle(styleCellCenter); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellRightNobold); cell6.setCellStyle(styleCellCenter); // set value for cells of rowtong cell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tongcong")); cell5.setCellValue(tong); res.setContentType("application/vnd.ms-excel"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaotinhhinhlephi.xls"); OutputStream out = res.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function excel BaoCaochungthuc * Version: 1.0/*www . java2s .co m*/ * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelBaocaochungthuc(ResourceRequest req, ResourceResponse res) { try { DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); String coQuanQuanLyId = req.getParameter("coQuanQuanLyId"); CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); if (coQuanQuanLyId != null && !coQuanQuanLyId.equals("0")) { coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId)); } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Baocaohosochungthuc"); // define a cell style HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 5000); HSSFFont ngaythangFont = (HSSFFont) workbook.createFont(); ngaythangFont.setItalic(true); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 5000); titleFont.setFontHeightInPoints((short) 15); HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle(); styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellHeader.setFont(tableHeaderFont); styleCellHeader.setWrapText(true); HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle(); styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNgaythang.setFont(ngaythangFont); styleCellNgaythang.setWrapText(true); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); styleCellRight.setFont(tableHeaderFont); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle(); styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNhomTTHC.setFont(tableHeaderFont); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // end style String tungay = ParamUtil.getString(req, "tungay"); String denngay = ParamUtil.getString(req, "denngay"); int rowNum = 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowUBND = sheet.createRow(rowNum); rowUBND.createCell(0).setCellValue(""); rowUBND.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 11)); rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn")); rowUBND.getCell(2).setCellStyle(styleCellHeader); rowNum++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowTenDonVi = sheet.createRow(rowNum); rowTenDonVi.createCell(0).setCellValue(coQuanQuanLy.getTen()); rowTenDonVi.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 11)); rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp")); rowTenDonVi.getCell(2).setCellStyle(styleCellHeader); rowNum++; String str_loaihoso = ParamUtil.getString(req, "id_loaihoso"); List<HoSoChungThuc> listhosochungthuc = ActionUtil.listhsct(str_loaihoso, tungay, denngay); String sochungthuc = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.sochungthuc"); String nguoinop = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.nguoinop"); String diachi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.diachi"); String noidungcongviec = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.noidungcongviec"); String loaigiayto = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.loaigiayto"); String sobo = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.sobo"); String sotrang = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.sotrang"); String ngaynhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.ngaynhan"); String ngaytraketqua = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.ngaytraketqua"); String lephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.lephi"); String nguoithuchien = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.nguoithuchien"); String nguoiky = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.nguoiky"); String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.tieude"); String[] header = { "STT", sochungthuc, nguoinop, diachi, noidungcongviec, loaigiayto, sobo, sotrang, ngaynhan, ngaytraketqua, lephi, nguoithuchien, nguoiky }; rowNum++; int[] width = { 1500, 2000, 5000, 5000, 5000, 5000, 2000, 2000, 3000, 3000, 2000, 4000, 4000 }; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 12)); HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styleTitle); titleCell.setCellValue(tieude); rowNum += 2; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 12)); HSSFRow ngaythangRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell ngaythangCell = ngaythangRow.createCell(0); ngaythangCell.setCellStyle(styleCellNgaythang); ngaythangCell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tungay") + " " + tungay + " " + ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.denngay") + " " + denngay); rowNum = rowNum + 3; HSSFRow headerRow = (HSSFRow) sheet.createRow(rowNum); int colNum = 0; for (int i = 0; i < header.length; i++) { HSSFCell cell = headerRow.createCell(colNum); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); sheet.setColumnWidth(i, width[i]); colNum++; } rowNum++; long tong = 0; int stt = 0; for (int a = 0; a < listhosochungthuc.size(); a++) { // khai bao dong va cac cell stt++; HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); // set style cho cac cell cell.setCellStyle(styleCellCenter); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); // set gia tri cho cac cell String diachithuongtru = listhosochungthuc.get(a).getDiaChiThuongTruNguoiNop() != null ? listhosochungthuc.get(a).getDiaChiThuongTruNguoiNop() : ""; String dienthoaididong = listhosochungthuc.get(a).getSoDienThoaiDiDongNguoiNop() != null ? "T:" + listhosochungthuc.get(a).getSoDienThoaiDiDongNguoiNop() : ""; cell.setCellValue(stt); cell1.setCellValue(listhosochungthuc.get(a).getSoChungThuc() != null ? listhosochungthuc.get(a).getSoChungThuc() : ""); cell2.setCellValue(listhosochungthuc.get(a).getHoTenNguoiNopHoSo() != null ? listhosochungthuc.get(a).getHoTenNguoiNopHoSo() : ""); cell3.setCellValue(diachithuongtru + "\n" + dienthoaididong); cell4.setCellValue(listhosochungthuc.get(a).getTenChungThuc()); cell5.setCellValue( listhosochungthuc.get(a).getThuTucHanhChinhId() != null ? ThuTucHanhChinhLocalServiceUtil .getThuTucHanhChinh(listhosochungthuc.get(a).getThuTucHanhChinhId()).getTen() : ""); cell6.setCellValue(listhosochungthuc.get(a).getSoBoHoSo()); cell7.setCellValue(listhosochungthuc.get(a).getSoTo()); cell8.setCellValue(listhosochungthuc.get(a).getNgayNopHoSo() != null ? df.format(listhosochungthuc.get(a).getNgayNopHoSo()) : ""); cell9.setCellValue(listhosochungthuc.get(a).getNgayTraKetQua() != null ? df.format(listhosochungthuc.get(a).getNgayTraKetQua()) : ""); cell10.setCellValue(listhosochungthuc.get(a).getLePhi()); cell11.setCellValue( listhosochungthuc.get(a).getCanBoTiepNhanId() != null ? CongChucLocalServiceUtil .fetchCongChuc(listhosochungthuc.get(a).getCanBoTiepNhanId()).getHoVaTen() : ""); cell12.setCellValue( listhosochungthuc.get(a).getTenCanBoKy() != null ? listhosochungthuc.get(a).getTenCanBoKy() : ""); rowNum++; } res.setContentType("application/vnd.ms-excel"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaohosochungthuc.xls"); OutputStream out = res.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function excel report detail//from w w w .ja v a2 s .c o m * Version: 1.0 * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param resourceRequest * @param resourceResponse */ public static void excelbaocaochitiet(ResourceRequest resourceRequest, ResourceResponse resourceResponse) { try { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("new sheet"); HSSFFont Row2_font = workbook.createFont(); Row2_font.setFontName(HSSFFont.FONT_ARIAL); Row2_font.setFontHeightInPoints((short) 13); Row2_font.setUnderline(HSSFFont.U_SINGLE); HSSFFont ngaythang_font = workbook.createFont(); ngaythang_font.setFontName(HSSFFont.FONT_ARIAL); ngaythang_font.setFontHeightInPoints((short) 13); ngaythang_font.setItalic(true); HSSFFont style_font = workbook.createFont(); style_font.setFontHeightInPoints((short) 13); HSSFFont trangthai_font = workbook.createFont(); trangthai_font.setFontName(HSSFFont.FONT_ARIAL); trangthai_font.setFontHeightInPoints((short) 11); trangthai_font.setItalic(true); /*define a cell style*/ HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 3500); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 3500); titleFont.setFontHeightInPoints((short) 13); HSSFFont titleTongStyle_font = (HSSFFont) workbook.createFont(); titleTongStyle_font.setFontHeightInPoints((short) 13); titleTongStyle_font.setBoldweight((short) 3500); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); HSSFCellStyle titleTongStyle = (HSSFCellStyle) workbook.createCellStyle(); titleTongStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleTongStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleTongStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleTongStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleTongStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); titleTongStyle.setFont(titleTongStyle_font); HSSFCellStyle titleLinhvucStyle = (HSSFCellStyle) workbook.createCellStyle(); titleLinhvucStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleLinhvucStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleLinhvucStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleLinhvucStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleLinhvucStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); titleLinhvucStyle.setFont(titleTongStyle_font); HSSFCellStyle styleQuochieu = (HSSFCellStyle) workbook.createCellStyle(); styleQuochieu.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleQuochieu.setFont(style_font); HSSFCellStyle styleRow2 = (HSSFCellStyle) workbook.createCellStyle(); styleRow2.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleRow2.setFont(Row2_font); HSSFCellStyle ngaythang_style = (HSSFCellStyle) workbook.createCellStyle(); ngaythang_style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); ngaythang_style.setFont(ngaythang_font); HSSFCellStyle style_trangthai = (HSSFCellStyle) workbook.createCellStyle(); style_trangthai.setAlignment(HSSFCellStyle.ALIGN_CENTER); style_trangthai.setFont(trangthai_font); HSSFCellStyle style_tungay_denngay = (HSSFCellStyle) workbook.createCellStyle(); style_tungay_denngay.setAlignment(HSSFCellStyle.ALIGN_CENTER); style_tungay_denngay.setFont(trangthai_font); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); /*end style*/ int rowNum = 0; String[] header = { "STT", "Lnh vc", LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.tenhoso"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.sohoso"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.trangthai"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.diachi"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.dienthoai"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.nguoinophoso"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.chuhoso"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.ngaynhan"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.ngaytra"), "Tnh trng\n h s", LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.ghichu") }; int[] width = { 1200, 3750, 3750, 4200, 3650, 3650, 3650, 3650, 3750, 3750, 3750, 3750, 3750 }; String[] listloaihoso = null; if (Validator.isNotNull(ParamUtil.getParameterValues(resourceRequest, "chon_hoso"))) { listloaihoso = ParamUtil.getParameterValues(resourceRequest, "chon_hoso"); } String ngaynhan_tungay = ""; if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "ngaynhan_tungay"))) { ngaynhan_tungay = ParamUtil.getString(resourceRequest, "ngaynhan_tungay"); } String ngaynhan_denngay = ""; if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "ngaynhan_denngay"))) { ngaynhan_denngay = ParamUtil.getString(resourceRequest, "ngaynhan_denngay"); } String trangthai_hoso = ""; if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "trangthai_hoso"))) { trangthai_hoso = ParamUtil.getString(resourceRequest, "trangthai_hoso"); } String tinhtrang = ""; if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "tinhtrang_hoso"))) { tinhtrang = ParamUtil.getString(resourceRequest, "tinhtrang_hoso"); } String loaidangky = ""; if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "loaidangky_hoso"))) { loaidangky = ParamUtil.getString(resourceRequest, "loaidangky_hoso"); } String ten_donvi = ""; if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "chon_donvi"))) { ten_donvi = ParamUtil.getString(resourceRequest, "chon_donvi"); } List<Baocaochitiet> list = BaocaochitietLocalServiceUtil.hienthiDanhsachBaocao(listloaihoso, ngaynhan_tungay, ngaynhan_denngay, trangthai_hoso, tinhtrang, loaidangky, QueryUtil.ALL_POS, QueryUtil.ALL_POS); sheet.addMergedRegion(new Region(rowNum, (short) 0, (short) 0, (short) 2)); HSSFRow Quochieu = sheet.createRow(rowNum); HSSFCell Quochieucell_1 = Quochieu.createCell(0); HSSFCell Quochieucell_2 = Quochieu.createCell(3); Quochieucell_1.setCellStyle(styleQuochieu); Quochieucell_2.setCellStyle(styleQuochieu); Quochieucell_1.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.qlc")); sheet.addMergedRegion(new Region(rowNum, (short) 3, (short) 0, (short) 12)); Quochieucell_2.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.chxhcnvn")); rowNum = rowNum + 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, (short) 1, (short) 2)); HSSFRow Row_2 = sheet.createRow(rowNum); //HSSFCell Quan = Row_2.createCell(0); //Quan.setCellValue(LanguageUtil.get((PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG) ,resourceRequest.getLocale(),"vn.dtt.cmon.report.excel.qlc")); //Quan.setCellStyle(styleRow2); sheet.addMergedRegion(new Region(rowNum, (short) 3, (short) 1, (short) 12)); HSSFCell Tieungu = Row_2.createCell(3); Tieungu.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.dltdhp")); Tieungu.setCellStyle(styleRow2); rowNum = rowNum + 1; Calendar dateTime = Calendar.getInstance(); SimpleDateFormat ngay_format = new SimpleDateFormat("dd"); SimpleDateFormat thang_format = new SimpleDateFormat("MM"); SimpleDateFormat nam_format = new SimpleDateFormat("yyyy"); Date time = dateTime.getTime(); String day = ngay_format.format(time); String month = thang_format.format(time); String year = nam_format.format(time); sheet.addMergedRegion(new Region(rowNum, (short) 0, (short) 2, (short) 10)); HSSFRow Row_3 = sheet.createRow(rowNum); HSSFCell ngaythang = Row_3.createCell(0); ngaythang.setCellValue(ten_donvi + ", " + LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.ngay") + " " + day + " " + LanguageUtil .get((PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.thang") + " " + month + " " + LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.nam") + " " + year); ngaythang.setCellStyle(ngaythang_style); rowNum = rowNum + 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10)); HSSFRow titleRow = sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.tieude")); titleCell.setCellStyle(styleTitle); if (ngaynhan_tungay != "" && ngaynhan_denngay != "") { rowNum = rowNum + 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10)); HSSFRow TimeReportRow = sheet.createRow(rowNum); HSSFCell TimeReportCell = TimeReportRow.createCell(0); TimeReportCell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.tungay") + ": " + ngaynhan_tungay + " " + LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.denngay") + ": " + ngaynhan_denngay); TimeReportCell.setCellStyle(style_tungay_denngay); } else if (ngaynhan_tungay != "" && ngaynhan_denngay == "") { rowNum = rowNum + 1; ngaynhan_denngay = "01/12/" + ngaynhan_tungay.substring(6, 10); sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10)); HSSFRow TimeReportRow = sheet.createRow(rowNum); HSSFCell TimeReportCell = TimeReportRow.createCell(0); TimeReportCell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.tungay") + ": " + ngaynhan_tungay + " " + LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.denngay") + ": " + ngaynhan_denngay); TimeReportCell.setCellStyle(style_tungay_denngay); } else if (ngaynhan_tungay == "" && ngaynhan_denngay != "") { rowNum = rowNum + 1; ngaynhan_tungay = "01/01/" + ngaynhan_denngay.substring(6, 10); sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10)); HSSFRow TimeReportRow = sheet.createRow(rowNum); HSSFCell TimeReportCell = TimeReportRow.createCell(0); TimeReportCell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.tungay") + ": " + ngaynhan_tungay + " " + LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.denngay") + ": " + ngaynhan_denngay); TimeReportCell.setCellStyle(style_tungay_denngay); } else { rowNum = rowNum + 1; ngaynhan_tungay = "01/01/" + year; ngaynhan_denngay = "01/12/" + year; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10)); HSSFRow TimeReportRow = sheet.createRow(rowNum); HSSFCell TimeReportCell = TimeReportRow.createCell(0); TimeReportCell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.tungay") + ": " + ngaynhan_tungay + " " + LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.denngay") + ": " + ngaynhan_denngay); TimeReportCell.setCellStyle(style_tungay_denngay); } if (!trangthai_hoso.equals("")) { rowNum = rowNum + 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10)); HSSFRow TimeReportRow = sheet.createRow(rowNum); HSSFCell TimeReportCell = TimeReportRow.createCell(0); TimeReportCell .setCellValue("(" + LanguageUtil.get( (PortletConfig) resourceRequest .getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.trangthai") + ": " + TrangThaiHoSoLocalServiceUtil .getTrangThaiHoSo(Long.parseLong(trangthai_hoso)).getTrangThai() + ")"); TimeReportCell.setCellStyle(style_trangthai); } rowNum = rowNum + 3; HSSFRow headerRow = (HSSFRow) sheet.createRow(rowNum); int colNum = 0; for (int i = 0; i < header.length; i++) { HSSFCell cell = headerRow.createCell(colNum); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); sheet.setColumnWidth(i, width[i]); colNum++; } rowNum++; long count = 0; if (list != null && list.size() > 0) { long linhvucId = 0L; for (int a = 0; a < list.size(); a++) { String tinh_trang = ""; if (Validator.isNotNull(list.get(a).getNGAYTRAKETQUA()) && Validator.isNotNull(list.get(a).getNGAYHENTRAKETQUA())) { DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date ngaytra = df.parse(list.get(a).getNGAYTRAKETQUA()); Date ngayhentra = df.parse(list.get(a).getNGAYHENTRAKETQUA()); if (ngaytra.before(ngayhentra)) { tinh_trang = "Sm hn"; } else if (ngaytra.equals(ngayhentra)) { tinh_trang = "ng hn"; } else if (ngaytra.after(ngayhentra)) { tinh_trang = "Tr hn"; } } //khai bao dong va cac cell HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); //set style cho cac cell cell.setCellStyle(styleCellCenter); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); //set gia tri cho cac cell cell.setCellValue((a + 1)); cell1.setCellValue(NhomThuTucHanhChinhLocalServiceUtil .getNhomThuTucHanhChinh(list.get(a).getLINHVUCID()).getTen()); cell2.setCellValue(list.get(a).getTEN()); cell3.setCellValue(list.get(a).getMASOHOSO()); cell4.setCellValue(list.get(a).getTRANGTHAI()); cell5.setCellValue(list.get(a).getDIACHIHIENNAY()); cell6.setCellValue(list.get(a).getDIENTHOAIDIDONG()); cell7.setCellValue(list.get(a).getHOTENNGUOINOPHOSO()); cell8.setCellValue(list.get(a).getNGUOIDAIDIENPHAPLUAT()); cell9.setCellValue(list.get(a).getNGAYNHANHOSO()); cell10.setCellValue(list.get(a).getNGAYTRAKETQUA()); cell11.setCellValue(tinh_trang); cell12.setCellValue(list.get(a).getGHICHU()); count = a; rowNum++; } count = count + 1; } else { //khai bao dong va cac cell HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); //set style cho cac cell cell.setCellStyle(styleCellCenter); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); //set gia tri cho cac cell cell.setCellValue(""); cell1.setCellValue(""); cell2.setCellValue(""); cell3.setCellValue(""); cell4.setCellValue(""); cell5.setCellValue(""); cell6.setCellValue(""); cell7.setCellValue(""); cell8.setCellValue(""); cell9.setCellValue(""); cell10.setCellValue(""); cell11.setCellValue(""); cell12.setCellValue(""); count = 0; rowNum++; } sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 11)); HSSFRow rowTong = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = rowTong.createCell(0); HSSFCell cell1 = rowTong.createCell(1); HSSFCell cell2 = rowTong.createCell(2); HSSFCell cell3 = rowTong.createCell(3); HSSFCell cell4 = rowTong.createCell(4); HSSFCell cell5 = rowTong.createCell(5); HSSFCell cell6 = rowTong.createCell(6); HSSFCell cell7 = rowTong.createCell(7); HSSFCell cell8 = rowTong.createCell(8); HSSFCell cell9 = rowTong.createCell(9); HSSFCell cell10 = rowTong.createCell(10); HSSFCell cell11 = rowTong.createCell(11); HSSFCell cell12 = rowTong.createCell(12); //set style cho cac cell cell.setCellStyle(titleTongStyle); cell1.setCellStyle(styleCellCenter); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); //set value for cells of rowtong cell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.tonghoso")); cell12.setCellValue(count); rowNum = rowNum + 2; sheet.addMergedRegion(new Region(rowNum, (short) 1, rowNum, (short) 2)); HSSFRow nguoilap_baocaoRow = sheet.createRow(rowNum); HSSFCell nguoilap_Cell = nguoilap_baocaoRow.createCell(1); nguoilap_Cell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.nguoilap")); nguoilap_Cell.setCellStyle(styleTitle); sheet.addMergedRegion(new Region(rowNum, (short) 7, rowNum, (short) 10)); HSSFCell title_nguoibaocao_Cell = nguoilap_baocaoRow.createCell(7); title_nguoibaocao_Cell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.nguoibaocao")); title_nguoibaocao_Cell.setCellStyle(styleTitle); rowNum = rowNum + 4; if (Validator.isNotNull(resourceRequest.getAttribute("THEME_DISPLAY"))) { CongChuc congchuc = new CongChucImpl(); ThemeDisplay themdisplay = (ThemeDisplay) resourceRequest.getAttribute("THEME_DISPLAY"); User user = themdisplay.getUser(); TaiKhoanNguoiDung taikhoan = new TaiKhoanNguoiDungImpl(); taikhoan = TaiKhoanNguoiDungLocalServiceUtil.findByTaiKhoanNguoiDungId(user.getUserId()); congchuc = ActionUtil.getCongchucByTaiKhoanNguoiDungId(taikhoan.getId()); sheet.addMergedRegion(new Region(rowNum, (short) 1, rowNum, (short) 2)); HSSFRow nguoi_baocaoRow = sheet.createRow(rowNum); HSSFCell nguoibaocao_Cell = nguoi_baocaoRow.createCell(1); nguoibaocao_Cell.setCellValue(congchuc.getHoVaTen()); nguoibaocao_Cell.setCellStyle(styleTitle); } resourceResponse.setContentType("application/vnd.ms-excel;charset=utf-8"); resourceResponse.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); resourceResponse.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaochitiet.xls"); OutputStream out = resourceResponse.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { } }
From source file:org.ofbiz.webtools.ExcelConversionFilter.java
License:Open Source License
void applyStylesToSheet(Map<String, ? extends Object> context, HSSFWorkbook workBook, HSSFSheet sheet, ServletRequest request) {// www .ja v a2 s . com List headerKeys = new ArrayList(); int noOfheads = 0; headerKeys = UtilMisc.toList("mainHeader1", "mainHeader2", "mainHeader3", "mainHeader4", "mainHeader5"); Map<String, Object> stylesMap = FastMap.newInstance(); stylesMap = (Map) context.get("stylesMap"); ArrayList allRowAndColData = (ArrayList) context.get("allRowAndColData"); Integer mainHeadingCell = 5; Integer mainHeadercellHeight = null; String mainHeaderFontName = null; Integer mainHeaderFontSize = null; Boolean mainHeaderBold = true; Integer columnHeaderCellHeight = null; Boolean columnHeaderBold = true; Boolean columnHeaderBgColor = null; String columnHeaderFontName = null; Boolean autoSizeCell = true; Integer columnHeaderFontSize = null; mainHeadercellHeight = (Integer) stylesMap.get("mainHeadercellHeight"); mainHeaderFontName = (String) stylesMap.get("mainHeaderFontName"); mainHeaderFontSize = (Integer) stylesMap.get("mainHeaderFontSize"); if (stylesMap.get("mainHeaderBold") != null) { mainHeaderBold = (Boolean) stylesMap.get("mainHeaderBold"); } if (stylesMap.get("columnHeaderBold") != null) { columnHeaderBold = (Boolean) stylesMap.get("columnHeaderBold"); } if (stylesMap.get("autoSizeCell") != null) { autoSizeCell = (Boolean) stylesMap.get("autoSizeCell"); } if (stylesMap.get("mainHeadingCell") != null) { mainHeadingCell = (Integer) stylesMap.get("mainHeadingCell"); } columnHeaderCellHeight = (Integer) stylesMap.get("columnHeaderCellHeight"); columnHeaderBgColor = (Boolean) stylesMap.get("columnHeaderBgColor"); columnHeaderFontName = (String) stylesMap.get("columnHeaderFontName"); columnHeaderFontSize = (Integer) stylesMap.get("columnHeaderFontSize"); ArrayList styles = new ArrayList(stylesMap.keySet()); for (int i = 0; i < styles.size(); i++) { ArrayList tempArrayList = new ArrayList<String>(); if (headerKeys.contains(styles.get(i))) { ArrayList<?> innerData = (ArrayList<?>) allRowAndColData.get(i); tempArrayList.add(stylesMap.get(styles.get(i))); for (int j = 0; j < innerData.size() - 1; j++) { tempArrayList.add(""); } allRowAndColData.add(i, tempArrayList); ++noOfheads; } } try { for (int i = 0; i < allRowAndColData.size(); i++) { HSSFCellStyle style = workBook.createCellStyle(); HSSFFont font = workBook.createFont(); ArrayList<?> ardata = (ArrayList<?>) allRowAndColData.get(i); HSSFRow row = sheet.createRow(i); for (int k = 0; k < ardata.size(); k++) { HSSFCell cell = row.createCell(k); if (k == mainHeadingCell && i <= noOfheads) { if (UtilValidate.isNotEmpty(mainHeadercellHeight)) { row.setHeight((short) mainHeadercellHeight.shortValue()); } else { row.setHeight((short) 400); } if (UtilValidate.isNotEmpty(mainHeaderFontName)) { font.setFontName(mainHeaderFontName); } if (UtilValidate.isNotEmpty(mainHeaderBold) && mainHeaderBold) { font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); } if (UtilValidate.isNotEmpty(mainHeaderFontSize)) { font.setFontHeightInPoints((short) mainHeaderFontSize.shortValue()); } else { font.setFontHeightInPoints((short) 12); //default value } style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setFont(font); cell.setCellValue((ardata.get(0).toString()).replaceAll("\"", "")); cell.setCellStyle(style); } else if (i == noOfheads + 1) { if (UtilValidate.isNotEmpty(columnHeaderCellHeight)) { row.setHeight((short) columnHeaderCellHeight.shortValue()); } else { row.setHeight((short) 300); } if (UtilValidate.isNotEmpty(columnHeaderBold) && columnHeaderBold) { font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); } if (UtilValidate.isNotEmpty(columnHeaderFontSize)) { font.setFontHeightInPoints((short) columnHeaderFontSize.shortValue()); } else { font.setFontHeightInPoints((short) 9); } if (UtilValidate.isNotEmpty(columnHeaderFontName)) { font.setFontName(columnHeaderFontName); } if (UtilValidate.isNotEmpty(columnHeaderBgColor) && columnHeaderBgColor) { style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); } style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setFont(font); cell.setCellValue((ardata.get(k).toString()).replaceAll("\"", "")); cell.setCellStyle(style); } else if (i > noOfheads) { cell.setCellValue((ardata.get(k).toString()).replaceAll("\"", "")); } if (UtilValidate.isNotEmpty(autoSizeCell) && autoSizeCell) { sheet.autoSizeColumn(k); } } } } catch (Exception e) { Debug.logInfo(e.getMessage(), module); request.setAttribute("_ERROR_MESSAGE_", e.getMessage()); } }
From source file:org.openmrs.module.kenyaemr.export.ExportFillManager.java
License:Open Source License
/** * Fills the report with content//ww w . j a v a 2s . c o m * * @param worksheet * @param startRowIndex * starting row offset * @param startColIndex * starting column offset * @param datasource * the data source */ public static void fillReport(HSSFSheet worksheet, int startRowIndex, int startColIndex, List<ReportModel> datasource) { // Row offset startRowIndex += 2; // Create cell style for the body HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle(); bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER); bodyCellStyle.setWrapText(true); // Create body for (int i = startRowIndex; i + startRowIndex - 2 < datasource.size() + 2; i++) { // Create a new row HSSFRow row = worksheet.createRow((short) i + 1); // Retrieve the Accepted Date HSSFCell cell1 = row.createCell(startColIndex + 0); cell1.setCellValue(""); cell1.setCellStyle(bodyCellStyle); // Retrieve the Patient Identifier HSSFCell cell2 = row.createCell(startColIndex + 1); cell2.setCellValue(""); cell2.setCellStyle(bodyCellStyle); // Retrieve the Name of Patient HSSFCell cell3 = row.createCell(startColIndex + 2); cell3.setCellValue(""); cell3.setCellStyle(bodyCellStyle); // Retrieve the Age of Patient HSSFCell cell4 = row.createCell(startColIndex + 3); cell4.setCellValue(""); cell4.setCellStyle(bodyCellStyle); // Retrieve the Gender of Patient HSSFCell cell5 = row.createCell(startColIndex + 4); cell5.setCellValue(""); cell5.setCellStyle(bodyCellStyle); // Retrieve the Sample Id HSSFCell cell6 = row.createCell(startColIndex + 5); cell6.setCellValue(""); cell6.setCellStyle(bodyCellStyle); // Retrieve the Name of Investigation HSSFCell cell7 = row.createCell(startColIndex + 6); cell7.setCellValue(""); cell7.setCellStyle(bodyCellStyle); // Retrieve the Name of Test HSSFCell cell8 = row.createCell(startColIndex + 7); cell8.setCellValue(""); cell8.setCellStyle(bodyCellStyle); // Retrieve the Name of Test name HSSFCell cell9 = row.createCell(startColIndex + 8); cell9.setCellValue(""); cell9.setCellStyle(bodyCellStyle); // Retrieve the Test Result HSSFCell cell10 = row.createCell(startColIndex + 9); cell10.setCellValue(""); cell10.setCellStyle(bodyCellStyle); } }
From source file:org.openmrs.module.kenyaemr.export.ExportLayouter.java
License:Open Source License
/** * Builds the report title and the date header * //from w ww .jav a2s.c om * @param worksheet * @param startRowIndex * starting row offset * @param startColIndex * starting column offset */ public static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // Create font style for the report title Font fontTitle = worksheet.getWorkbook().createFont(); fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD); fontTitle.setFontHeight((short) 280); // Create cell style for the report title HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle(); cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER); cellStyleTitle.setWrapText(true); cellStyleTitle.setFont(fontTitle); // Create report title HSSFRow rowTitle = worksheet.createRow((short) startRowIndex); rowTitle.setHeight((short) 500); HSSFCell cellTitle = rowTitle.createCell(startColIndex); cellTitle.setCellValue("Patient Lab Result Report"); cellTitle.setCellStyle(cellStyleTitle); // Create merged region for the report title worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); // Create date header HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1); HSSFCell cellDate = dateTitle.createCell(startColIndex); //ghanshyam 27-sept-2012 Support #393 [Laboratory]Export to Excel option in print worklist (note: changed day,date and time format inside excel report sheet) String months[] = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" }; String days[] = { "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat" }; GregorianCalendar gcalendar = new GregorianCalendar(); String dayName = days[gcalendar.get(Calendar.DAY_OF_WEEK) - 1]; String date = (gcalendar.get(Calendar.DATE) + "-" + months[gcalendar.get(Calendar.MONTH)] + "-" + gcalendar.get(Calendar.YEAR)).toString(); Date d = new Date(); cellDate.setCellValue("This report was generated on " + dayName + " " + date + " " + "at" + " " + d.getHours() + ":" + d.getMinutes() + ":" + d.getSeconds()); }