List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function excel BaoCaochungthuc * Version: 1.0/*from w w w . ja v a 2 s . com*/ * * 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/* w ww . j a v a 2s . 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.openelis.bean.QcChartReport1Bean.java
License:Open Source License
private void setHeaderCells(HSSFSheet sheet, String qcName, String qcType, String analyteName) { int i, startRow; Cell cell;/*ww w . j a va2s. com*/ Row row; if (qcColumns != null && qcColumns.size() > 0) startRow = 29; else startRow = 0; row = sheet.createRow(startRow); cell = row.createCell(0); cell.setCellStyle(headerStyle); setCellValue(cell, "QC Name"); setMaxChars(cell, maxChars); cell = row.createCell(1); cell.setCellStyle(baseStyle); setCellValue(cell, qcName); setMaxChars(cell, maxChars); cell = row.createCell(2); cell.setCellStyle(baseStyle); sheet.addMergedRegion(new CellRangeAddress(startRow, startRow, 1, 2)); row = sheet.createRow(startRow + 1); cell = row.createCell(0); cell.setCellStyle(headerStyle); setCellValue(cell, "QC Type: Analyte"); setMaxChars(cell, maxChars); cell = row.createCell(1); cell.setCellStyle(baseStyle); setCellValue(cell, qcType + ": " + analyteName); setMaxChars(cell, maxChars); cell = row.createCell(2); cell.setCellStyle(baseStyle); sheet.addMergedRegion(new CellRangeAddress(startRow + 1, startRow + 1, 1, 2)); row = sheet.createRow(startRow + 2); cell = row.createCell(0); cell.setCellStyle(headerStyle); setCellValue(cell, "Row Number"); setMaxChars(cell, maxChars); cell = row.createCell(1); cell.setCellStyle(headerStyle); setCellValue(cell, "Accession # / Worksheet #"); setMaxChars(cell, maxChars); cell = row.createCell(2); cell.setCellStyle(headerStyle); setCellValue(cell, "Lot #"); setMaxChars(cell, maxChars); cell = row.createCell(3); cell.setCellStyle(headerStyle); setCellValue(cell, "Created Date"); setMaxChars(cell, maxChars); cell = row.createCell(4); cell.setCellStyle(headerStyle); setCellValue(cell, "QC Link"); setMaxChars(cell, maxChars); if (qcColumns != null && !qcColumns.isEmpty()) { for (i = 0; i < qcColumns.size(); i++) { cell = row.createCell(i + 5); cell.setCellStyle(headerStyle); setCellValue(cell, qcColumns.get(i).getEntry()); setMaxChars(cell, maxChars); } } else if (worksheetHeaders != null && !worksheetHeaders.isEmpty()) { for (i = 0; i < worksheetHeaders.size(); i++) { cell = row.createCell(i + 5); cell.setCellStyle(headerStyle); setCellValue(cell, worksheetHeaders.get(i)); setMaxChars(cell, maxChars); } } }
From source file:org.openmrs.module.kenyaemr.export.ExportLayouter.java
License:Open Source License
/** * Builds the report title and the date header * /* w w w .j a va2s .c o m*/ * @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()); }
From source file:org.orbeon.oxf.util.XLSUtils.java
License:Open Source License
public static void copySheet(HSSFWorkbook workbook, HSSFSheet destination, HSSFSheet source) { // Copy column width short maxCellNum = getMaxCellNum(source); for (short i = 0; i <= maxCellNum; i++) { destination.setColumnWidth(i, source.getColumnWidth(i)); }/*from www.j a va 2 s .c o m*/ // Copy merged cells for (int i = 0; i < source.getNumMergedRegions(); i++) { Region region = source.getMergedRegionAt(i); destination.addMergedRegion(region); } // Copy rows for (int i = 0; i <= source.getLastRowNum(); i++) { HSSFRow sourceRow = source.getRow(i); HSSFRow destinationRow = destination.createRow(i); copyRow(workbook, destinationRow, sourceRow); } }
From source file:org.sevenorcas.style.app.mod.ss.SpreadSheetServiceImp.java
/** * Export sheet to workbook/* ww w. j av a 2 s .com*/ * @param spreadSheet * @param workbook * @throws Exception */ private void exportSpreadSheet(List<SpreadSheet> sheets, HSSFWorkbook wb) throws Exception { for (SpreadSheet ss : sheets) { HSSFSheet sheet = wb.createSheet(ss.getSheetname()); /*-*************************************************************** * Default column widths ****************************************************************/ for (int column = 0; column <= ss.getLastColumn(); column++) { if (ss.getColumnWidth(column) != -1) { sheet.setColumnWidth(column, ss.getColumnWidth(column)); } } /*-*************************************************************** * Output row data ****************************************************************/ for (int row = 0; row <= ss.getLastRow(); row++) { for (int column = 0; column <= ss.getLastColumn(); column++) { HSSFRow sheetRow = sheet.getRow(row); if (sheetRow == null) { sheetRow = sheet.createRow(row); } HSSFCell cell = sheetRow.createCell(column); SpreadsheetCell cellX = ss.getCell(column, row); if (cellX != null) { if (cellX.getCellRangeAddress() != null) { sheet.addMergedRegion(cellX.getCellRangeAddress()); } //Ex HSSFCellStyle style = cellX.getCellStyle(wb); cell.setCellStyle(style); boolean set = ss.getColumnWidth(column) == -1; if (set && cellX.isHeader() && cellX.getWidth() != null) { sheet.setColumnWidth(column, cellX.getWidth()); } cellX.setCellValue(cell, wb); } else { HSSFCellStyle style = ss.getCellStyleDefault(wb, row, column); cell.setCellStyle(style); } } } } }
From source file:org.sigmah.server.endpoint.export.Export.java
License:Open Source License
private void createHeaders(ActivityDTO activity, HSSFSheet sheet) { /// The HEADER rows Row headerRow1 = sheet.createRow(0); Row headerRow2 = sheet.createRow(1); headerRow2.setHeightInPoints(75);/*from w ww . j a v a 2 s .c om*/ int column = 0; createHeaderCell(headerRow2, column++, "Date1", CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow2, column++, "Date2", CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow2, column, "Partner"); sheet.setColumnWidth(column, 16 * 256); column++; createHeaderCell(headerRow2, column, activity.getLocationType().getName()); sheet.setColumnWidth(column, 20 * 256); column++; createHeaderCell(headerRow2, column++, "Axe"); indicators = new ArrayList<Integer>(activity.getIndicators().size()); if (activity.getReportingFrequency() == ActivityDTO.REPORT_ONCE) { for (IndicatorGroup group : activity.groupIndicators()) { if (group.getName() != null) { // create a merged cell on the top row spanning all members of the group createHeaderCell(headerRow1, column, group.getName()); sheet.addMergedRegion( new CellRangeAddress(0, 0, column, column + group.getIndicators().size() - 1)); } for (IndicatorDTO indicator : group.getIndicators()) { indicators.add(indicator.getId()); createHeaderCell(headerRow2, column, indicator.getName(), indicatorHeaderStyle); sheet.setColumnWidth(column, 16 * 256); column++; } } } attributes = new ArrayList<Integer>(); int firstAttributeColumn = column; for (AttributeGroupDTO group : activity.getAttributeGroups()) { if (group.getAttributes().size() != 0) { createHeaderCell(headerRow1, column, group.getName(), CellStyle.ALIGN_CENTER); sheet.addMergedRegion( new CellRangeAddress(0, 0, column, column + group.getAttributes().size() - 1)); for (AttributeDTO attrib : group.getAttributes()) { attributes.add(attrib.getId()); createHeaderCell(headerRow2, column, attrib.getName(), attribHeaderStyle); sheet.setColumnWidth(column, 5 * 256); column++; } } } // sheet.getSheetConditionalFormatting().addConditionalFormatting( // new CellRangeAddress[] { new CellRangeAddress(2, 65535, firstAttributeColumn, column-1) }, // new HSSFConditionalFormattingRule[] { attribTrueRule, attribFalseRule }); levels = new ArrayList<Integer>(); for (AdminLevelDTO level : activity.getAdminLevels()) { createHeaderCell(headerRow2, column++, "Code " + level.getName()); createHeaderCell(headerRow2, column++, level.getName()); levels.add(level.getId()); } createHeaderCell(headerRow2, column, "Longitude", CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow2, column + 1, "Latitude", CellStyle.ALIGN_RIGHT); sheet.setColumnWidth(column, 12 * 256); sheet.setColumnWidth(column + 1, 12 * 256); }
From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.ExcelUtils.java
License:Open Source License
public void putMainTitle(final HSSFSheet sheet, int rowIndex, String text, int maxCols) { // title/*from ww w .j a va2s . com*/ row = sheet.createRow(rowIndex); row.setHeightInPoints(ExportConstants.HEADER_ROW_HEIGHT); cell = row.createCell(1); cell.setCellValue(text); cell.setCellStyle(getTopicStyle(wb)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, maxCols)); }
From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.ExcelUtils.java
License:Open Source License
public void putInfoRow(final HSSFSheet sheet, int rowIndex, String key, String value, int maxCols) { int cellIndex = 0; row = sheet.createRow(rowIndex);/*from www . jav a 2 s . c om*/ row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT); cell = row.createCell(++cellIndex); cell.setCellValue(key); cell.setCellStyle(getInfoStyle(wb, true)); cell = row.createCell(++cellIndex); cell.setCellValue(value); cell.setCellStyle(getInfoStyle(wb, false)); sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, maxCols)); }
From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.template.BaseSynthesisExcelTemplate.java
License:Open Source License
private int putLayout(final HSSFSheet sheet, final Layout layout, int rowIndex) throws Throwable { int typeStartRow = rowIndex; boolean firstGroup = true; // layout groups for each phase for (final LayoutGroup layoutGroup : layout.getGroups()) { // layout group cell if (!firstGroup) { row = sheet.createRow(++rowIndex); }//from w w w . j av a 2 s . c o m firstGroup = false; row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT); CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 2, data.getNumbOfCols()); sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb)); cell = sheet.getRow(rowIndex).createCell(2); cell.setCellStyle(utils.getGroupStyle(wb)); cell.setCellValue(layoutGroup.getTitle()); // elements for each layout group for (final LayoutConstraint constraint : layoutGroup.getConstraints()) { final FlexibleElement element = constraint.getElement(); // skip if element is not exportable if (!element.isExportable()) continue; final String elementName = "element." + element.getClass().getSimpleName(); Integer id = (clazz.equals(Project.class)) ? data.getProject().getId() : data.getOrgUnit().getId(); final GetValue command = new GetValue(id, element.getId(), elementName, null); final ValueResult valueResult = (ValueResult) data.getHandler().execute(command, null); // prepare value and label ValueLabel pair = null; boolean isMessage = false; /* DEF FLEXIBLE */ if (elementName.equals("element.DefaultFlexibleElement") || elementName.equals("element.BudgetElement")) { pair = data.getDataProvider().getDefElementPair(valueResult, element, data.getProject() != null ? data.getProject() : data.getOrgUnit(), clazz, data.getEntityManager(), data.getLocale(), data.getTranslator()); } /* CHECKBOX */else if (elementName.equals("element.CheckboxElement")) { pair = data.getDataProvider().getCheckboxElementPair(valueResult, element, data.getLocale(), data.getTranslator()); } /* TEXT AREA */else if (elementName.equals("element.TextAreaElement")) { pair = data.getDataProvider().getTextAreaElementPair(valueResult, element); } /* TRIPLET */else if (elementName.equals("element.TripletsListElement")) { pair = data.getDataProvider().getTripletPair(element, valueResult); } /* CHOICE */else if (elementName.equals("element.QuestionElement")) { pair = data.getDataProvider().getChoicePair(element, valueResult); } /* MESSAGE */else if (elementName.equals("element.MessageElement")) { pair = new ValueLabel(data.getLocalizedVersion("flexibleElementMessage"), GlobalExportDataProvider.clearHtmlFormatting(element.getLabel())); isMessage = true; } if (pair != null) putElement(sheet, ++rowIndex, pair, isMessage); } // elements } region = new CellRangeAddress(typeStartRow, rowIndex, 1, 1); sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb)); return rowIndex; }