List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setFont
public void setFont(HSSFFont font)
From source file:org.jxstar.report.studio.ExportXlsBO.java
/** * ?/*from w ww. j av a 2s. c o m*/ * @param wb -- ? * @return */ public HSSFCellStyle createHeadStyle(HSSFWorkbook wb) { // HSSFFont cellFont = wb.createFont(); cellFont.setFontName(""); cellFont.setFontHeightInPoints((short) 9); cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //? HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle.setFont(cellFont); // cellStyle.setBorderBottom((short) 1); cellStyle.setBorderLeft((short) 1); cellStyle.setBorderRight((short) 1); cellStyle.setBorderTop((short) 1); return cellStyle; }
From source file:org.jxstar.report.studio.ExportXlsBO.java
/** * ?//from w ww .j a va 2 s . c o m * @param wb -- ? * @return */ public HSSFCellStyle createCellStyle(HSSFWorkbook wb) { // HSSFFont cellFont = wb.createFont(); cellFont.setFontName(""); cellFont.setFontHeightInPoints((short) 9); //? HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle.setFont(cellFont); // cellStyle.setBorderBottom((short) 1); cellStyle.setBorderLeft((short) 1); cellStyle.setBorderRight((short) 1); cellStyle.setBorderTop((short) 1); return cellStyle; }
From source file:org.metaeffekt.core.inventory.processor.writer.InventoryWriter.java
License:Apache License
private HSSFCellStyle createHeaderStyle(HSSFWorkbook myWorkBook) { Font headerFont = myWorkBook.createFont(); headerFont.setColor(Font.COLOR_NORMAL); HSSFPalette palette = myWorkBook.getCustomPalette(); HSSFColor headerColor = palette.findSimilarColor((byte) 149, (byte) 179, (byte) 215); HSSFCellStyle headerStyle = myWorkBook.createCellStyle(); headerStyle.setFillForegroundColor(headerColor.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setFont(headerFont); headerStyle.setWrapText(true);//from w ww .j av a 2 s .c om return headerStyle; }
From source file:org.mili.core.text.transformation.ExcelTransformator.java
License:Apache License
/** * Transforms./*from ww w . j a v a 2s. c o m*/ * * @param from from table * @param params the params * @return the HSSF workbook */ public HSSFWorkbook transform(Table from, Object... params) { if (from.getRowSize() == 0) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFDataFormat format = wb.createDataFormat(); HSSFSheet sheet = wb.createSheet(getSheetName(null)); HSSFRow r = sheet.createRow(0); HSSFCell c = r.createCell((short) (0)); c.setCellValue("Keine Daten vorhanden !"); return wb; } String fsInt = "#,###,##0"; String fsFloat = "#,###,##0.000"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFDataFormat format = wb.createDataFormat(); HSSFSheet sheet = wb.createSheet(getSheetName(null)); HSSFFont headFont = wb.createFont(); headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headStyle = wb.createCellStyle(); headStyle.setFont(headFont); HSSFCellStyle numberStyle = wb.createCellStyle(); HSSFRow headerRow = sheet.createRow(0); short z = 0; for (int i = 0, n = from.getColSize(); i < n; i++) { Col col = from.getCol(i); HSSFCell headerCell = headerRow.createCell((short) (z)); headerCell.setCellStyle(headStyle); headerCell.setCellValue(col.getName()); z++; } for (int i = 0, n = from.getRowSize(); i < n; i++) { Row row = from.getRow(i); HSSFRow contentRow = sheet.createRow(i + 1); short j = 0; for (int ii = 0, nn = from.getColSize(); ii < nn; ii++) { Col col = from.getCol(ii); Object o = row.getValue(ii); HSSFCell contentCell = contentRow.createCell((short) (j)); String value = o == null ? "" : String.valueOf(o); if (o instanceof Number) { if (o instanceof Integer) { numberStyle.setDataFormat(format.getFormat(fsInt)); contentCell.setCellValue(Integer.parseInt(value)); contentCell.setCellStyle(numberStyle); } else if (o instanceof Float) { numberStyle.setDataFormat(format.getFormat(fsFloat)); contentCell.setCellValue(Float.parseFloat(value)); contentCell.setCellStyle(numberStyle); } } else { contentCell.setCellValue(value); } j++; } } return wb; }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function excelTongHopChiTiet/*from ww w . j ava 2 s. c o m*/ * Version: 1.0 * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelTongHopChiTiet(ResourceRequest req, ResourceResponse res) { try { String id_donvi = req.getParameter("id_donvi"); String id_loaihoso = req.getParameter("id_loaihoso"); String thang = req.getParameter("thang"); String nam = req.getParameter("nam"); CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); Set<Long> setIDNhomThuTuc = new HashSet<Long>(0); List<ReportTongHopChiTiet> listTongHopChiTiet = ReportTongHopChiTietLocalServiceUtil .getListReportTongHopChiTietGroupByIDThuTuc(id_donvi, id_loaihoso, thang, nam); if (id_donvi != null && !id_donvi.equals("0")) { coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(id_donvi)); } for (int i = 0; i < listTongHopChiTiet.size(); i++) { setIDNhomThuTuc.add(listTongHopChiTiet.get(i).getNHOMTHUTUCHANHCHINHID()); } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Bao_cao_tong_hop_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 //header bao cao 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 coquan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.thutuc"); 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, 5000, 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++; //end header bao cao //noi dung bao cao rowNum++; int stt = 0; 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; for (Long s : setIDNhomThuTuc) { stt++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 14)); HSSFRow rowNhomTTHC = (HSSFRow) sheet.createRow(rowNum); HSSFCell cellNhomTTHC = rowNhomTTHC.createCell(0); cellNhomTTHC.setCellValue( stt + ". " + NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinh(s).getTen()); cellNhomTTHC.setCellStyle(styleCellNhomTTHC); for (int i = 1; i < 15; i++) { rowNhomTTHC.createCell(i).setCellStyle(styleCellCenter); } rowNum++; for (int a = 0; a < listTongHopChiTiet.size(); a++) { // khai bao dong va cac cell if (listTongHopChiTiet.get(a).getNHOMTHUTUCHANHCHINHID() == s) { tongNhan += listTongHopChiTiet.get(a).getTONGNHAN(); tongTiepNhan += listTongHopChiTiet.get(a).getTIEPNHAN(); tongTonDau += listTongHopChiTiet.get(a).getTONDAU(); tongDaGiaiQuyet += listTongHopChiTiet.get(a).getTONGDAGIAIQUYET(); tongSom += listTongHopChiTiet.get(a).getSOM(); tongDung += listTongHopChiTiet.get(a).getDUNG(); tongTre += listTongHopChiTiet.get(a).getTRE(); tongDangGiaiQuyet += listTongHopChiTiet.get(a).getTONGDANGGIAIQUYET(); tongChuaDenHan += listTongHopChiTiet.get(a).getCHUADENHAN(); tongDaQuaHan += listTongHopChiTiet.get(a).getQUAHAN(); tongRutHoSo += listTongHopChiTiet.get(a).getRUTHOSO(); tongKhongHopLe += listTongHopChiTiet.get(a).getKHONGHOPLE(); tongTonCuoi += listTongHopChiTiet.get(a).getTONCUOI(); 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(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(ThuTucHanhChinhLocalServiceUtil .getThuTucHanhChinh(listTongHopChiTiet.get(a).getTHUTUCHANHCHINHID()).getTen()); //cell1.setCellValue(""); cell2.setCellValue(listTongHopChiTiet.get(a).getTONGNHAN()); cell3.setCellValue(listTongHopChiTiet.get(a).getTIEPNHAN()); cell4.setCellValue(listTongHopChiTiet.get(a).getTONDAU()); cell5.setCellValue(listTongHopChiTiet.get(a).getTONGDAGIAIQUYET()); cell6.setCellValue(listTongHopChiTiet.get(a).getSOM()); cell7.setCellValue(listTongHopChiTiet.get(a).getDUNG()); cell8.setCellValue(listTongHopChiTiet.get(a).getTRE()); cell9.setCellValue(listTongHopChiTiet.get(a).getTONGDANGGIAIQUYET()); cell10.setCellValue(listTongHopChiTiet.get(a).getCHUADENHAN()); cell11.setCellValue(listTongHopChiTiet.get(a).getQUAHAN()); cell12.setCellValue(listTongHopChiTiet.get(a).getRUTHOSO()); cell13.setCellValue(listTongHopChiTiet.get(a).getKHONGHOPLE()); cell14.setCellValue(listTongHopChiTiet.get(a).getTONCUOI()); rowNum++; } } } //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) { e.printStackTrace(); } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function checkHosoByNHOMTTHCID * Version: 1.0//from w ww.j av a 2 s . com * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelTongHopNam(ResourceRequest req, ResourceResponse res) { try { String id_donvi = req.getParameter("id_donvi"); String id_linhvuc = req.getParameter("id_linhvuc"); String nam = req.getParameter("nam"); CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); Set<Long> setIDNhomThuTuc = new HashSet<Long>(0); List<ReportTongHop> listTongHopChiTiet = ActionUtil.getListReportTongHop(id_donvi, id_linhvuc, nam); if (id_donvi != null && !id_donvi.equals("0")) { coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(id_donvi)); } for (int i = 0; i < listTongHopChiTiet.size(); i++) { setIDNhomThuTuc.add(listTongHopChiTiet.get(i).getNHOMTHUTUCHANHCHINHID()); } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Bao_cao_tong_hop_theo_nam"); // 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 //header bao cao 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 coquan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.thang"); 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, 5000, 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++; //end header bao cao //noi dung bao cao rowNum++; int stt = 0; 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; for (Long s : setIDNhomThuTuc) { stt++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 14)); HSSFRow rowNhomTTHC = (HSSFRow) sheet.createRow(rowNum); HSSFCell cellNhomTTHC = rowNhomTTHC.createCell(0); cellNhomTTHC.setCellValue( stt + ". " + NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinh(s).getTen()); cellNhomTTHC.setCellStyle(styleCellNhomTTHC); for (int i = 1; i < 15; i++) { rowNhomTTHC.createCell(i).setCellStyle(styleCellCenter); } rowNum++; for (int a = 0; a < listTongHopChiTiet.size(); a++) { // khai bao dong va cac cell if (listTongHopChiTiet.get(a).getNHOMTHUTUCHANHCHINHID() == s) { tongNhan += listTongHopChiTiet.get(a).getTONGNHAN(); tongTiepNhan += listTongHopChiTiet.get(a).getTIEPNHAN(); tongTonDau += listTongHopChiTiet.get(a).getTONDAU(); tongDaGiaiQuyet += listTongHopChiTiet.get(a).getTONGDAGIAIQUYET(); tongSom += listTongHopChiTiet.get(a).getSOM(); tongDung += listTongHopChiTiet.get(a).getDUNG(); tongTre += listTongHopChiTiet.get(a).getTRE(); tongDangGiaiQuyet += listTongHopChiTiet.get(a).getTONGDANGGIAIQUYET(); tongChuaDenHan += listTongHopChiTiet.get(a).getCHUADENHAN(); tongDaQuaHan += listTongHopChiTiet.get(a).getQUAHAN(); tongRutHoSo += listTongHopChiTiet.get(a).getRUTHOSO(); tongKhongHopLe += listTongHopChiTiet.get(a).getKHONGHOPLE(); tongTonCuoi += listTongHopChiTiet.get(a).getTONCUOI(); 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(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("Thng " + listTongHopChiTiet.get(a).getTHANG()); //cell1.setCellValue(""); cell2.setCellValue(listTongHopChiTiet.get(a).getTONGNHAN()); cell3.setCellValue(listTongHopChiTiet.get(a).getTIEPNHAN()); cell4.setCellValue(listTongHopChiTiet.get(a).getTONDAU()); cell5.setCellValue(listTongHopChiTiet.get(a).getTONGDAGIAIQUYET()); cell6.setCellValue(listTongHopChiTiet.get(a).getSOM()); cell7.setCellValue(listTongHopChiTiet.get(a).getDUNG()); cell8.setCellValue(listTongHopChiTiet.get(a).getTRE()); cell9.setCellValue(listTongHopChiTiet.get(a).getTONGDANGGIAIQUYET()); cell10.setCellValue(listTongHopChiTiet.get(a).getCHUADENHAN()); cell11.setCellValue(listTongHopChiTiet.get(a).getQUAHAN()); cell12.setCellValue(listTongHopChiTiet.get(a).getRUTHOSO()); cell13.setCellValue(listTongHopChiTiet.get(a).getKHONGHOPLE()); cell14.setCellValue(listTongHopChiTiet.get(a).getTONCUOI()); rowNum++; } } } //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) { e.printStackTrace(); } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function checkHosoByNHOMTTHCID * Version: 1.0//from w w w . j av a 2 s . co m * * 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/* w w w .j a v a 2 s .c om*/ * * 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 ww . j a v a 2 s. c o 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//from w w w . jav a 2 s .c o m * 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) { } }