List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setWrapText
@Override public void setWrapText(boolean wrapped)
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 . j a va 2 s . c om*/ * * 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/* www . ja v a2 s . c o m*/ * Version: 1.0 * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param resourceRequest * @param resourceResponse */ public static void excelbaocaochitiet(ResourceRequest resourceRequest, ResourceResponse resourceResponse) { try { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("new sheet"); HSSFFont Row2_font = workbook.createFont(); Row2_font.setFontName(HSSFFont.FONT_ARIAL); Row2_font.setFontHeightInPoints((short) 13); Row2_font.setUnderline(HSSFFont.U_SINGLE); HSSFFont ngaythang_font = workbook.createFont(); ngaythang_font.setFontName(HSSFFont.FONT_ARIAL); ngaythang_font.setFontHeightInPoints((short) 13); ngaythang_font.setItalic(true); HSSFFont style_font = workbook.createFont(); style_font.setFontHeightInPoints((short) 13); HSSFFont trangthai_font = workbook.createFont(); trangthai_font.setFontName(HSSFFont.FONT_ARIAL); trangthai_font.setFontHeightInPoints((short) 11); trangthai_font.setItalic(true); /*define a cell style*/ HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 3500); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 3500); titleFont.setFontHeightInPoints((short) 13); HSSFFont titleTongStyle_font = (HSSFFont) workbook.createFont(); titleTongStyle_font.setFontHeightInPoints((short) 13); titleTongStyle_font.setBoldweight((short) 3500); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); HSSFCellStyle titleTongStyle = (HSSFCellStyle) workbook.createCellStyle(); titleTongStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleTongStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleTongStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleTongStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleTongStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); titleTongStyle.setFont(titleTongStyle_font); HSSFCellStyle titleLinhvucStyle = (HSSFCellStyle) workbook.createCellStyle(); titleLinhvucStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleLinhvucStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleLinhvucStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleLinhvucStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleLinhvucStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); titleLinhvucStyle.setFont(titleTongStyle_font); HSSFCellStyle styleQuochieu = (HSSFCellStyle) workbook.createCellStyle(); styleQuochieu.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleQuochieu.setFont(style_font); HSSFCellStyle styleRow2 = (HSSFCellStyle) workbook.createCellStyle(); styleRow2.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleRow2.setFont(Row2_font); HSSFCellStyle ngaythang_style = (HSSFCellStyle) workbook.createCellStyle(); ngaythang_style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); ngaythang_style.setFont(ngaythang_font); HSSFCellStyle style_trangthai = (HSSFCellStyle) workbook.createCellStyle(); style_trangthai.setAlignment(HSSFCellStyle.ALIGN_CENTER); style_trangthai.setFont(trangthai_font); HSSFCellStyle style_tungay_denngay = (HSSFCellStyle) workbook.createCellStyle(); style_tungay_denngay.setAlignment(HSSFCellStyle.ALIGN_CENTER); style_tungay_denngay.setFont(trangthai_font); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); /*end style*/ int rowNum = 0; String[] header = { "STT", "Lnh vc", LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.tenhoso"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.sohoso"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.trangthai"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.diachi"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.dienthoai"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.nguoinophoso"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.chuhoso"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.ngaynhan"), LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.ngaytra"), "Tnh trng\n h s", LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.ghichu") }; int[] width = { 1200, 3750, 3750, 4200, 3650, 3650, 3650, 3650, 3750, 3750, 3750, 3750, 3750 }; String[] listloaihoso = null; if (Validator.isNotNull(ParamUtil.getParameterValues(resourceRequest, "chon_hoso"))) { listloaihoso = ParamUtil.getParameterValues(resourceRequest, "chon_hoso"); } String ngaynhan_tungay = ""; if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "ngaynhan_tungay"))) { ngaynhan_tungay = ParamUtil.getString(resourceRequest, "ngaynhan_tungay"); } String ngaynhan_denngay = ""; if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "ngaynhan_denngay"))) { ngaynhan_denngay = ParamUtil.getString(resourceRequest, "ngaynhan_denngay"); } String trangthai_hoso = ""; if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "trangthai_hoso"))) { trangthai_hoso = ParamUtil.getString(resourceRequest, "trangthai_hoso"); } String tinhtrang = ""; if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "tinhtrang_hoso"))) { tinhtrang = ParamUtil.getString(resourceRequest, "tinhtrang_hoso"); } String loaidangky = ""; if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "loaidangky_hoso"))) { loaidangky = ParamUtil.getString(resourceRequest, "loaidangky_hoso"); } String ten_donvi = ""; if (Validator.isNotNull(ParamUtil.getString(resourceRequest, "chon_donvi"))) { ten_donvi = ParamUtil.getString(resourceRequest, "chon_donvi"); } List<Baocaochitiet> list = BaocaochitietLocalServiceUtil.hienthiDanhsachBaocao(listloaihoso, ngaynhan_tungay, ngaynhan_denngay, trangthai_hoso, tinhtrang, loaidangky, QueryUtil.ALL_POS, QueryUtil.ALL_POS); sheet.addMergedRegion(new Region(rowNum, (short) 0, (short) 0, (short) 2)); HSSFRow Quochieu = sheet.createRow(rowNum); HSSFCell Quochieucell_1 = Quochieu.createCell(0); HSSFCell Quochieucell_2 = Quochieu.createCell(3); Quochieucell_1.setCellStyle(styleQuochieu); Quochieucell_2.setCellStyle(styleQuochieu); Quochieucell_1.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.qlc")); sheet.addMergedRegion(new Region(rowNum, (short) 3, (short) 0, (short) 12)); Quochieucell_2.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.chxhcnvn")); rowNum = rowNum + 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, (short) 1, (short) 2)); HSSFRow Row_2 = sheet.createRow(rowNum); //HSSFCell Quan = Row_2.createCell(0); //Quan.setCellValue(LanguageUtil.get((PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG) ,resourceRequest.getLocale(),"vn.dtt.cmon.report.excel.qlc")); //Quan.setCellStyle(styleRow2); sheet.addMergedRegion(new Region(rowNum, (short) 3, (short) 1, (short) 12)); HSSFCell Tieungu = Row_2.createCell(3); Tieungu.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.dltdhp")); Tieungu.setCellStyle(styleRow2); rowNum = rowNum + 1; Calendar dateTime = Calendar.getInstance(); SimpleDateFormat ngay_format = new SimpleDateFormat("dd"); SimpleDateFormat thang_format = new SimpleDateFormat("MM"); SimpleDateFormat nam_format = new SimpleDateFormat("yyyy"); Date time = dateTime.getTime(); String day = ngay_format.format(time); String month = thang_format.format(time); String year = nam_format.format(time); sheet.addMergedRegion(new Region(rowNum, (short) 0, (short) 2, (short) 10)); HSSFRow Row_3 = sheet.createRow(rowNum); HSSFCell ngaythang = Row_3.createCell(0); ngaythang.setCellValue(ten_donvi + ", " + LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.ngay") + " " + day + " " + LanguageUtil .get((PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.thang") + " " + month + " " + LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.nam") + " " + year); ngaythang.setCellStyle(ngaythang_style); rowNum = rowNum + 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10)); HSSFRow titleRow = sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.tieude")); titleCell.setCellStyle(styleTitle); if (ngaynhan_tungay != "" && ngaynhan_denngay != "") { rowNum = rowNum + 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10)); HSSFRow TimeReportRow = sheet.createRow(rowNum); HSSFCell TimeReportCell = TimeReportRow.createCell(0); TimeReportCell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.tungay") + ": " + ngaynhan_tungay + " " + LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.denngay") + ": " + ngaynhan_denngay); TimeReportCell.setCellStyle(style_tungay_denngay); } else if (ngaynhan_tungay != "" && ngaynhan_denngay == "") { rowNum = rowNum + 1; ngaynhan_denngay = "01/12/" + ngaynhan_tungay.substring(6, 10); sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10)); HSSFRow TimeReportRow = sheet.createRow(rowNum); HSSFCell TimeReportCell = TimeReportRow.createCell(0); TimeReportCell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.tungay") + ": " + ngaynhan_tungay + " " + LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.denngay") + ": " + ngaynhan_denngay); TimeReportCell.setCellStyle(style_tungay_denngay); } else if (ngaynhan_tungay == "" && ngaynhan_denngay != "") { rowNum = rowNum + 1; ngaynhan_tungay = "01/01/" + ngaynhan_denngay.substring(6, 10); sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10)); HSSFRow TimeReportRow = sheet.createRow(rowNum); HSSFCell TimeReportCell = TimeReportRow.createCell(0); TimeReportCell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.tungay") + ": " + ngaynhan_tungay + " " + LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.denngay") + ": " + ngaynhan_denngay); TimeReportCell.setCellStyle(style_tungay_denngay); } else { rowNum = rowNum + 1; ngaynhan_tungay = "01/01/" + year; ngaynhan_denngay = "01/12/" + year; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10)); HSSFRow TimeReportRow = sheet.createRow(rowNum); HSSFCell TimeReportCell = TimeReportRow.createCell(0); TimeReportCell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.tungay") + ": " + ngaynhan_tungay + " " + LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.denngay") + ": " + ngaynhan_denngay); TimeReportCell.setCellStyle(style_tungay_denngay); } if (!trangthai_hoso.equals("")) { rowNum = rowNum + 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 10)); HSSFRow TimeReportRow = sheet.createRow(rowNum); HSSFCell TimeReportCell = TimeReportRow.createCell(0); TimeReportCell .setCellValue("(" + LanguageUtil.get( (PortletConfig) resourceRequest .getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.trangthai") + ": " + TrangThaiHoSoLocalServiceUtil .getTrangThaiHoSo(Long.parseLong(trangthai_hoso)).getTrangThai() + ")"); TimeReportCell.setCellStyle(style_trangthai); } rowNum = rowNum + 3; HSSFRow headerRow = (HSSFRow) sheet.createRow(rowNum); int colNum = 0; for (int i = 0; i < header.length; i++) { HSSFCell cell = headerRow.createCell(colNum); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); sheet.setColumnWidth(i, width[i]); colNum++; } rowNum++; long count = 0; if (list != null && list.size() > 0) { long linhvucId = 0L; for (int a = 0; a < list.size(); a++) { String tinh_trang = ""; if (Validator.isNotNull(list.get(a).getNGAYTRAKETQUA()) && Validator.isNotNull(list.get(a).getNGAYHENTRAKETQUA())) { DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date ngaytra = df.parse(list.get(a).getNGAYTRAKETQUA()); Date ngayhentra = df.parse(list.get(a).getNGAYHENTRAKETQUA()); if (ngaytra.before(ngayhentra)) { tinh_trang = "Sm hn"; } else if (ngaytra.equals(ngayhentra)) { tinh_trang = "ng hn"; } else if (ngaytra.after(ngayhentra)) { tinh_trang = "Tr hn"; } } //khai bao dong va cac cell HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); //set style cho cac cell cell.setCellStyle(styleCellCenter); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); //set gia tri cho cac cell cell.setCellValue((a + 1)); cell1.setCellValue(NhomThuTucHanhChinhLocalServiceUtil .getNhomThuTucHanhChinh(list.get(a).getLINHVUCID()).getTen()); cell2.setCellValue(list.get(a).getTEN()); cell3.setCellValue(list.get(a).getMASOHOSO()); cell4.setCellValue(list.get(a).getTRANGTHAI()); cell5.setCellValue(list.get(a).getDIACHIHIENNAY()); cell6.setCellValue(list.get(a).getDIENTHOAIDIDONG()); cell7.setCellValue(list.get(a).getHOTENNGUOINOPHOSO()); cell8.setCellValue(list.get(a).getNGUOIDAIDIENPHAPLUAT()); cell9.setCellValue(list.get(a).getNGAYNHANHOSO()); cell10.setCellValue(list.get(a).getNGAYTRAKETQUA()); cell11.setCellValue(tinh_trang); cell12.setCellValue(list.get(a).getGHICHU()); count = a; rowNum++; } count = count + 1; } else { //khai bao dong va cac cell HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); //set style cho cac cell cell.setCellStyle(styleCellCenter); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); //set gia tri cho cac cell cell.setCellValue(""); cell1.setCellValue(""); cell2.setCellValue(""); cell3.setCellValue(""); cell4.setCellValue(""); cell5.setCellValue(""); cell6.setCellValue(""); cell7.setCellValue(""); cell8.setCellValue(""); cell9.setCellValue(""); cell10.setCellValue(""); cell11.setCellValue(""); cell12.setCellValue(""); count = 0; rowNum++; } sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 11)); HSSFRow rowTong = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = rowTong.createCell(0); HSSFCell cell1 = rowTong.createCell(1); HSSFCell cell2 = rowTong.createCell(2); HSSFCell cell3 = rowTong.createCell(3); HSSFCell cell4 = rowTong.createCell(4); HSSFCell cell5 = rowTong.createCell(5); HSSFCell cell6 = rowTong.createCell(6); HSSFCell cell7 = rowTong.createCell(7); HSSFCell cell8 = rowTong.createCell(8); HSSFCell cell9 = rowTong.createCell(9); HSSFCell cell10 = rowTong.createCell(10); HSSFCell cell11 = rowTong.createCell(11); HSSFCell cell12 = rowTong.createCell(12); //set style cho cac cell cell.setCellStyle(titleTongStyle); cell1.setCellStyle(styleCellCenter); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); //set value for cells of rowtong cell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.baocaochitiet.tonghoso")); cell12.setCellValue(count); rowNum = rowNum + 2; sheet.addMergedRegion(new Region(rowNum, (short) 1, rowNum, (short) 2)); HSSFRow nguoilap_baocaoRow = sheet.createRow(rowNum); HSSFCell nguoilap_Cell = nguoilap_baocaoRow.createCell(1); nguoilap_Cell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.nguoilap")); nguoilap_Cell.setCellStyle(styleTitle); sheet.addMergedRegion(new Region(rowNum, (short) 7, rowNum, (short) 10)); HSSFCell title_nguoibaocao_Cell = nguoilap_baocaoRow.createCell(7); title_nguoibaocao_Cell.setCellValue(LanguageUtil.get( (PortletConfig) resourceRequest.getAttribute(JavaConstants.JAVAX_PORTLET_CONFIG), resourceRequest.getLocale(), "vn.dtt.cmon.report.excel.nguoibaocao")); title_nguoibaocao_Cell.setCellStyle(styleTitle); rowNum = rowNum + 4; if (Validator.isNotNull(resourceRequest.getAttribute("THEME_DISPLAY"))) { CongChuc congchuc = new CongChucImpl(); ThemeDisplay themdisplay = (ThemeDisplay) resourceRequest.getAttribute("THEME_DISPLAY"); User user = themdisplay.getUser(); TaiKhoanNguoiDung taikhoan = new TaiKhoanNguoiDungImpl(); taikhoan = TaiKhoanNguoiDungLocalServiceUtil.findByTaiKhoanNguoiDungId(user.getUserId()); congchuc = ActionUtil.getCongchucByTaiKhoanNguoiDungId(taikhoan.getId()); sheet.addMergedRegion(new Region(rowNum, (short) 1, rowNum, (short) 2)); HSSFRow nguoi_baocaoRow = sheet.createRow(rowNum); HSSFCell nguoibaocao_Cell = nguoi_baocaoRow.createCell(1); nguoibaocao_Cell.setCellValue(congchuc.getHoVaTen()); nguoibaocao_Cell.setCellStyle(styleTitle); } resourceResponse.setContentType("application/vnd.ms-excel;charset=utf-8"); resourceResponse.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); resourceResponse.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaochitiet.xls"); OutputStream out = resourceResponse.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { } }
From source file:org.openmrs.module.kenyaemr.export.ExportFillManager.java
License:Open Source License
/** * Fills the report with content//from w ww. jav a2 s . com * * @param worksheet * @param startRowIndex * starting row offset * @param startColIndex * starting column offset * @param datasource * the data source */ public static void fillReport(HSSFSheet worksheet, int startRowIndex, int startColIndex, List<ReportModel> datasource) { // Row offset startRowIndex += 2; // Create cell style for the body HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle(); bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER); bodyCellStyle.setWrapText(true); // Create body for (int i = startRowIndex; i + startRowIndex - 2 < datasource.size() + 2; i++) { // Create a new row HSSFRow row = worksheet.createRow((short) i + 1); // Retrieve the Accepted Date HSSFCell cell1 = row.createCell(startColIndex + 0); cell1.setCellValue(""); cell1.setCellStyle(bodyCellStyle); // Retrieve the Patient Identifier HSSFCell cell2 = row.createCell(startColIndex + 1); cell2.setCellValue(""); cell2.setCellStyle(bodyCellStyle); // Retrieve the Name of Patient HSSFCell cell3 = row.createCell(startColIndex + 2); cell3.setCellValue(""); cell3.setCellStyle(bodyCellStyle); // Retrieve the Age of Patient HSSFCell cell4 = row.createCell(startColIndex + 3); cell4.setCellValue(""); cell4.setCellStyle(bodyCellStyle); // Retrieve the Gender of Patient HSSFCell cell5 = row.createCell(startColIndex + 4); cell5.setCellValue(""); cell5.setCellStyle(bodyCellStyle); // Retrieve the Sample Id HSSFCell cell6 = row.createCell(startColIndex + 5); cell6.setCellValue(""); cell6.setCellStyle(bodyCellStyle); // Retrieve the Name of Investigation HSSFCell cell7 = row.createCell(startColIndex + 6); cell7.setCellValue(""); cell7.setCellStyle(bodyCellStyle); // Retrieve the Name of Test HSSFCell cell8 = row.createCell(startColIndex + 7); cell8.setCellValue(""); cell8.setCellStyle(bodyCellStyle); // Retrieve the Name of Test name HSSFCell cell9 = row.createCell(startColIndex + 8); cell9.setCellValue(""); cell9.setCellStyle(bodyCellStyle); // Retrieve the Test Result HSSFCell cell10 = row.createCell(startColIndex + 9); cell10.setCellValue(""); cell10.setCellStyle(bodyCellStyle); } }
From source file:org.openmrs.module.kenyaemr.export.ExportLayouter.java
License:Open Source License
/** * Builds the report title and the date header * // ww w.j ava 2s. c om * @param worksheet * @param startRowIndex * starting row offset * @param startColIndex * starting column offset */ public static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // Create font style for the report title Font fontTitle = worksheet.getWorkbook().createFont(); fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD); fontTitle.setFontHeight((short) 280); // Create cell style for the report title HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle(); cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER); cellStyleTitle.setWrapText(true); cellStyleTitle.setFont(fontTitle); // Create report title HSSFRow rowTitle = worksheet.createRow((short) startRowIndex); rowTitle.setHeight((short) 500); HSSFCell cellTitle = rowTitle.createCell(startColIndex); cellTitle.setCellValue("Patient Lab Result Report"); cellTitle.setCellStyle(cellStyleTitle); // Create merged region for the report title worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); // Create date header HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1); HSSFCell cellDate = dateTitle.createCell(startColIndex); //ghanshyam 27-sept-2012 Support #393 [Laboratory]Export to Excel option in print worklist (note: changed day,date and time format inside excel report sheet) String months[] = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" }; String days[] = { "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat" }; GregorianCalendar gcalendar = new GregorianCalendar(); String dayName = days[gcalendar.get(Calendar.DAY_OF_WEEK) - 1]; String date = (gcalendar.get(Calendar.DATE) + "-" + months[gcalendar.get(Calendar.MONTH)] + "-" + gcalendar.get(Calendar.YEAR)).toString(); Date d = new Date(); cellDate.setCellValue("This report was generated on " + dayName + " " + date + " " + "at" + " " + d.getHours() + ":" + d.getMinutes() + ":" + d.getSeconds()); }
From source file:org.openmrs.module.kenyaemr.export.ExportLayouter.java
License:Open Source License
/** * Builds the column headers/*from w w w .j av a 2 s. c o m*/ * * @param worksheet * @param startRowIndex * starting row offset * @param startColIndex * starting column offset */ public static void buildHeaders(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // Create font style for the headers Font font = worksheet.getWorkbook().createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create cell style for the headers HSSFCellStyle headerCellStyle = worksheet.getWorkbook().createCellStyle(); headerCellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); headerCellStyle.setFillPattern(CellStyle.FINE_DOTS); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); headerCellStyle.setWrapText(true); headerCellStyle.setFont(font); headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN); // Create the column headers HSSFRow rowHeader = worksheet.createRow((short) startRowIndex + 2); rowHeader.setHeight((short) 500); HSSFCell cell1 = rowHeader.createCell(startColIndex + 0); cell1.setCellValue("Order Date"); cell1.setCellStyle(headerCellStyle); HSSFCell cell2 = rowHeader.createCell(startColIndex + 1); cell2.setCellValue("Patient Identifier"); cell2.setCellStyle(headerCellStyle); HSSFCell cell3 = rowHeader.createCell(startColIndex + 2); cell3.setCellValue("Name"); cell3.setCellStyle(headerCellStyle); HSSFCell cell4 = rowHeader.createCell(startColIndex + 3); cell4.setCellValue("Age"); cell4.setCellStyle(headerCellStyle); HSSFCell cell5 = rowHeader.createCell(startColIndex + 4); cell5.setCellValue("Gender"); cell5.setCellStyle(headerCellStyle); HSSFCell cell6 = rowHeader.createCell(startColIndex + 5); cell6.setCellValue("Sample No."); cell6.setCellStyle(headerCellStyle); HSSFCell cell7 = rowHeader.createCell(startColIndex + 6); cell7.setCellValue("Lab"); cell7.setCellStyle(headerCellStyle); HSSFCell cell8 = rowHeader.createCell(startColIndex + 7); cell8.setCellValue("Test"); cell8.setCellStyle(headerCellStyle); HSSFCell cell9 = rowHeader.createCell(startColIndex + 8); cell9.setCellValue("Test name"); cell9.setCellStyle(headerCellStyle); HSSFCell cell10 = rowHeader.createCell(startColIndex + 9); cell10.setCellValue("Result"); cell10.setCellStyle(headerCellStyle); }
From source file:org.openmrs.module.laboratory.web.export.ExportFillManager.java
License:Open Source License
/** * Fills the report with content/*from www. ja va2s .com*/ * * @param worksheet * @param startRowIndex * starting row offset * @param startColIndex * starting column offset * @param datasource * the data source */ public static void fillReport(HSSFSheet worksheet, int startRowIndex, int startColIndex, List<TestModel> datasource) { // Row offset startRowIndex += 2; // Create cell style for the body HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle(); bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER); bodyCellStyle.setWrapText(true); // Create body for (int i = startRowIndex; i + startRowIndex - 2 < datasource.size() + 2; i++) { // Create a new row HSSFRow row = worksheet.createRow((short) i + 1); // Retrieve the Accepted Date HSSFCell cell1 = row.createCell(startColIndex + 0); cell1.setCellValue(datasource.get(i - 2).getAcceptedDate()); cell1.setCellStyle(bodyCellStyle); // Retrieve the Patient Identifier HSSFCell cell2 = row.createCell(startColIndex + 1); cell2.setCellValue(datasource.get(i - 2).getPatientIdentifier()); cell2.setCellStyle(bodyCellStyle); // Retrieve the Name of Patient HSSFCell cell3 = row.createCell(startColIndex + 2); cell3.setCellValue(datasource.get(i - 2).getPatientName()); cell3.setCellStyle(bodyCellStyle); // Retrieve the Age of Patient HSSFCell cell4 = row.createCell(startColIndex + 3); cell4.setCellValue(datasource.get(i - 2).getAge()); cell4.setCellStyle(bodyCellStyle); // Retrieve the Gender of Patient HSSFCell cell5 = row.createCell(startColIndex + 4); cell5.setCellValue(datasource.get(i - 2).getGender()); cell5.setCellStyle(bodyCellStyle); // Retrieve the Sample Id HSSFCell cell6 = row.createCell(startColIndex + 5); cell6.setCellValue(datasource.get(i - 2).getSampleId()); cell6.setCellStyle(bodyCellStyle); // Retrieve the Name of Investigation HSSFCell cell7 = row.createCell(startColIndex + 6); cell7.setCellValue(datasource.get(i - 2).getInvestigation()); cell7.setCellStyle(bodyCellStyle); // Retrieve the Name of Test HSSFCell cell8 = row.createCell(startColIndex + 7); cell8.setCellValue(datasource.get(i - 2).getTest().getName().getName()); cell8.setCellStyle(bodyCellStyle); // Retrieve the Name of Test name HSSFCell cell9 = row.createCell(startColIndex + 8); cell9.setCellValue(datasource.get(i - 2).getTestName().getName().getName()); cell9.setCellStyle(bodyCellStyle); // Retrieve the Test Result HSSFCell cell10 = row.createCell(startColIndex + 9); cell10.setCellValue(datasource.get(i - 2).getValue()); cell10.setCellStyle(bodyCellStyle); } }
From source file:org.orbeon.oxf.util.XLSUtils.java
License:Open Source License
public static void copyCell(HSSFWorkbook workbook, HSSFCell destination, HSSFCell source) { // Copy cell content destination.setCellType(source.getCellType()); switch (source.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: destination.setCellValue(source.getBooleanCellValue()); break;//from w w w .ja va2 s . c o m case HSSFCell.CELL_TYPE_FORMULA: case HSSFCell.CELL_TYPE_STRING: destination.setCellValue(source.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: destination.setCellValue(source.getNumericCellValue()); break; } // Copy cell style HSSFCellStyle sourceCellStyle = source.getCellStyle(); HSSFCellStyle destinationCellStyle = workbook.createCellStyle(); destinationCellStyle.setAlignment(sourceCellStyle.getAlignment()); destinationCellStyle.setBorderBottom(sourceCellStyle.getBorderBottom()); destinationCellStyle.setBorderLeft(sourceCellStyle.getBorderLeft()); destinationCellStyle.setBorderRight(sourceCellStyle.getBorderRight()); destinationCellStyle.setBorderTop(sourceCellStyle.getBorderTop()); destinationCellStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor()); destinationCellStyle.setDataFormat(sourceCellStyle.getDataFormat()); destinationCellStyle.setFillBackgroundColor(sourceCellStyle.getFillForegroundColor()); destinationCellStyle.setFillForegroundColor(sourceCellStyle.getFillForegroundColor()); destinationCellStyle.setFillPattern(sourceCellStyle.getFillPattern()); destinationCellStyle.setFont(workbook.getFontAt(sourceCellStyle.getFontIndex())); destinationCellStyle.setHidden(sourceCellStyle.getHidden()); destinationCellStyle.setIndention(sourceCellStyle.getIndention()); destinationCellStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor()); destinationCellStyle.setLocked(sourceCellStyle.getLocked()); destinationCellStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor()); destinationCellStyle.setRotation(sourceCellStyle.getRotation()); destinationCellStyle.setTopBorderColor(sourceCellStyle.getTopBorderColor()); destinationCellStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment()); destinationCellStyle.setWrapText(sourceCellStyle.getWrapText()); destination.setCellStyle(destinationCellStyle); }
From source file:org.rti.zcore.dar.report.StockUsageReport.java
License:Apache License
@Override public void getPatientRegister(Date beginDate, Date endDate, int siteId) { Connection conn = null;/*from w w w. ja va 2 s.co m*/ try { conn = DatabaseUtils.getZEPRSConnection(org.rti.zcore.Constants.DATABASE_ADMIN_USERNAME); HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle boldStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); boldStyle.setFont(font); HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setWrapText(true); headerStyle.setFont(font); //log.debug("Before getPatientStockMap:" + DateUtils.getTime()); HashMap<Long, List<StockControl>> stockMap = InventoryDAO.getPatientStockMap(conn, siteId, beginDate, endDate); //log.debug("Before EncountersDAO.getAll:" + DateUtils.getTime()); // loop through all of the items //List<Item> items = EncountersDAO.getAll(conn, Long.valueOf(131), "SQL_RETRIEVE_ALL_ADMIN131", Item.class); List<Item> items = null; Map queries = QueryLoader.instance().load("/" + Constants.SQL_GENERATED_PROPERTIES); String sql = (String) queries.get("SQL_RETRIEVE_ALL_ADMIN131") + " ORDER BY item.name"; ArrayList values = new ArrayList(); items = DatabaseUtils.getList(conn, Item.class, sql, values); //log.debug("Before looping through items:" + DateUtils.getTime()); //int j = 0; boolean generateReport = false; for (Item item : items) { Boolean useInReport = item.getUse_in_report(); Long itemId = item.getId(); useInReport = Boolean.TRUE; if ((useInReport != null) && (useInReport.booleanValue() == Boolean.TRUE)) { List<StockControl> patientStockChanges = stockMap.get(itemId); if (patientStockChanges == null) { patientStockChanges = new ArrayList<StockControl>(); } List<StockControl> stockChanges = InventoryDAO.getStockEncounterChanges(conn, itemId, siteId, beginDate, endDate, null, patientStockChanges); int stockChangesSize = stockChanges.size(); //if ((stockChangesSize > 0) || ((stockChangesSize == 0 && (item.getUse_in_report() != null) && (!item.getUse_in_report().equals(Boolean.FALSE))))) { if ((stockChangesSize > 0)) { generateReport = true; StockReport stockReport = InventoryDAO.generateStockSummary(conn, itemId, beginDate, stockChanges, false); // populate the common fields //HSSFSheet sheet = wb.getSheetAt(j); String fixedName = StringManipulation.escapeString(item.getName()).replace("/", " ") .replace("\\", " ").replace(",", "_").replace("[", "").replace("(", "-") .replace(")", "-"); int lenName = fixedName.length(); String itemIdString = String.valueOf(itemId); int lenItemId = itemIdString.length(); String uniqueName = null; if ((lenName + lenItemId) < 31) { uniqueName = fixedName + "_" + itemId; //log.debug(itemId + " size: " + uniqueName.length()); } else { int offset = (30 - lenItemId) - 1; if (lenName > offset) { uniqueName = fixedName.substring(0, offset) + "_" + itemIdString; //log.debug(itemId + " size: " + uniqueName.length()); } else { uniqueName = fixedName + "_" + itemId; } } HSSFSheet sheet = null; try { sheet = wb.createSheet(uniqueName); } catch (IllegalArgumentException e) { log.debug("Problem with name : " + uniqueName + " Error: " + e.getMessage()); //this.setType("error"); // e.printStackTrace(); } String code = ""; if (item.getCode() != null) { code = " (" + item.getCode() + ")"; } if (sheet != null) { //sheet.createRow(0).createCell(0).setCellValue(new HSSFRichTextString(item.getName() + code)); HSSFHeader header = sheet.getHeader(); header.setCenter(item.getName() + code); /*HSSFCell titleCell = sheet.createRow(0).createCell(0); titleCell.setCellStyle(boldStyle); titleCell.setCellValue(new HSSFRichTextString(item.getName() + code));*/ //sheet.createRow(2).createCell(0).setCellValue(new HSSFRichTextString("Beginning Balance")); HSSFRow row = sheet.createRow(0); row.setHeightInPoints((3 * sheet.getDefaultRowHeightInPoints())); HSSFCell cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Beginning \nBalance")); //sheet.getRow(0).createCell(1).setCellValue(new HSSFRichTextString("Quantity Received this period")); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Quantity \nReceived \nthis period")); //sheet.getRow(0).createCell(2).setCellValue(new HSSFRichTextString("Quantity dispensed this period")); cell = row.createCell(2); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Quantity \ndispensed \nthis period")); //sheet.getRow(0).createCell(3).setCellValue(new HSSFRichTextString("Total Issued to Patients")); cell = row.createCell(3); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Total Issued \nto Patients")); //sheet.getRow(0).createCell(4).setCellValue(new HSSFRichTextString("Positive Adjustments")); cell = row.createCell(4); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Positive \nAdjustments")); //sheet.getRow(0).createCell(5).setCellValue(new HSSFRichTextString("Negative Adjustments")); cell = row.createCell(5); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Negative \nAdjustments")); //sheet.getRow(0).createCell(6).setCellValue(new HSSFRichTextString("Ending Balance / Physical Count")); cell = row.createCell(6); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Ending Balance \nPhysical Count")); sheet.autoSizeColumn((short) 0); sheet.autoSizeColumn((short) 1); sheet.autoSizeColumn((short) 2); sheet.autoSizeColumn((short) 3); sheet.autoSizeColumn((short) 4); sheet.autoSizeColumn((short) 5); sheet.autoSizeColumn((short) 6); sheet.createRow(1).createCell(0).setCellValue(stockReport.getBalanceBF()); sheet.getRow(1).createCell(1).setCellValue(stockReport.getAdditionsTotal()); sheet.getRow(1).createCell(2).setCellValue(stockReport.getDeletionsTotal()); sheet.getRow(1).createCell(3).setCellValue(stockReport.getTotalDispensed()); sheet.getRow(1).createCell(4).setCellValue(stockReport.getPosAdjustments()); sheet.getRow(1).createCell(5).setCellValue(stockReport.getNegAdjustments()); sheet.getRow(1).createCell(6).setCellValue(stockReport.getOnHand()); row = sheet.createRow(4); row.setHeightInPoints((3 * sheet.getDefaultRowHeightInPoints())); //sheet.createRow(4).createCell(0).setCellValue(new HSSFRichTextString("Date")); cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Date")); //sheet.getRow(4).createCell(1).setCellValue(new HSSFRichTextString("Type of Stock Change")); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Type of \nStock \nChange")); //sheet.getRow(4).createCell(2).setCellValue(new HSSFRichTextString("Expiry Date")); cell = row.createCell(2); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Expiry \nDate")); //sheet.getRow(4).createCell(3).setCellValue(new HSSFRichTextString("Reference / Notes")); cell = row.createCell(3); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Reference/ \n Notes")); //sheet.getRow(4).createCell(4).setCellValue(new HSSFRichTextString("Additions")); cell = row.createCell(4); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Additions")); //sheet.getRow(4).createCell(5).setCellValue(new HSSFRichTextString("Subtractions")); cell = row.createCell(5); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Subtractions")); //sheet.getRow(4).createCell(6).setCellValue(new HSSFRichTextString("Recorded Balance")); cell = row.createCell(6); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Recorded \nBalance")); //sheet.getRow(4).createCell(7).setCellValue(new HSSFRichTextString("Calculated Balance")); cell = row.createCell(7); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Calculated \nBalance")); sheet.autoSizeColumn((short) 7); int k = 4; for (StockControl stockControl : stockChanges) { if (stockControl.getDate_of_record() != null) { k++; String stockChangeTypeString = null; Integer posAdjust = null; Integer negAdjust = null; if (stockControl.getType_of_change() != null) { int stockChangeType = stockControl.getType_of_change(); switch (stockChangeType) { case 3263: stockChangeTypeString = "Received"; posAdjust = stockControl.getChange_value(); break; case 3264: stockChangeTypeString = "Issued"; negAdjust = stockControl.getChange_value(); break; case 3265: stockChangeTypeString = "Losses"; negAdjust = stockControl.getChange_value(); break; case 3266: stockChangeTypeString = "Pos. Adjust."; posAdjust = stockControl.getChange_value(); break; case 3267: stockChangeTypeString = "Neg. Adjust."; negAdjust = stockControl.getChange_value(); break; case 3279: stockChangeTypeString = "Out-of-stock"; break; default: break; } } row = sheet.createRow(k); if (stockControl.getDate_of_record() != null) { Date dateRecord = stockControl.getDate_of_record(); row.createCell(0) .setCellValue(new HSSFRichTextString(dateRecord.toString())); } else { row.createCell(0).setCellValue(new HSSFRichTextString("")); } row.createCell(1).setCellValue(new HSSFRichTextString(stockChangeTypeString)); if (stockControl.getExpiry_date() != null) { Date expiryDate = stockControl.getExpiry_date(); row.createCell(2) .setCellValue(new HSSFRichTextString(expiryDate.toString())); } else { row.createCell(2).setCellValue(new HSSFRichTextString("")); } row.createCell(3).setCellValue(new HSSFRichTextString(stockControl.getNotes())); if (posAdjust != null) { row.createCell(4).setCellValue(posAdjust); } if (negAdjust != null) { row.createCell(5).setCellValue(negAdjust); } if (stockControl.getBalance() != null) { row.createCell(6).setCellValue(stockControl.getBalance()); } if (stockControl.getComputedBalance() != null) { row.createCell(7).setCellValue(stockControl.getComputedBalance()); } } } } } } //j++; } if (generateReport) { FileOutputStream stream = new FileOutputStream(this.getReportPath()); wb.write(stream); stream.close(); } else { this.setType("empty"); } } catch (Exception e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { log.error(e); } } }
From source file:org.sharegov.cirm.utils.ExcelExportUtil.java
License:Apache License
public void exportData(OutputStream out, Json allData) throws IOException { //Set the filename Date dt = new Date(); SimpleDateFormat fmt = new SimpleDateFormat("MM-dd-yyyy"); String filename = fmt.format(dt); // Create Excel Workbook and Sheet HSSFWorkbook wb = new HSSFWorkbook(); sheet = wb.createSheet(filename);/*from w w w.ja v a 2 s. com*/ HSSFHeader header = sheet.getHeader(); header.setCenter(filename); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle boldStyle = wb.createCellStyle(); boldStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); boldStyle.setFont(boldFont); boldStyle.setWrapText(true); //Start : populate the spreadsheet int rowCounter = 0; rowCounter = searchCriteriaRows(allData, boldStyle); rowCounter = headerRow(allData, boldStyle, rowCounter); int headingsRowSplitter = rowCounter; rowCounter = dataRows(allData, rowCounter); //end : populate the spreadsheet // Freeze Panes on Header Row sheet.createFreezePane(0, headingsRowSplitter); // Row 1 Repeats on each page wb.setRepeatingRowsAndColumns(0, 0, 0, 0, headingsRowSplitter); // Set Print Area, Footer int colCount = allData.at("metaData").at("columns").asInteger(); wb.setPrintArea(0, 0, colCount, 0, rowCounter); HSSFFooter footer = sheet.getFooter(); footer.setCenter("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages()); // Fit Sheet to 1 page wide but very long sheet.setAutobreaks(true); HSSFPrintSetup ps = sheet.getPrintSetup(); ps.setFitWidth((short) 1); ps.setFitHeight((short) 9999); sheet.setGridsPrinted(true); sheet.setHorizontallyCenter(true); ps.setPaperSize(HSSFPrintSetup.LETTER_PAPERSIZE); if (colCount > 5) { ps.setLandscape(true); } if (colCount > 10) { ps.setPaperSize(HSSFPrintSetup.LEGAL_PAPERSIZE); } if (colCount > 14) { ps.setPaperSize(HSSFPrintSetup.EXECUTIVE_PAPERSIZE); } // Set Margins ps.setHeaderMargin((double) .35); ps.setFooterMargin((double) .35); sheet.setMargin(HSSFSheet.TopMargin, (double) .50); sheet.setMargin(HSSFSheet.BottomMargin, (double) .50); sheet.setMargin(HSSFSheet.LeftMargin, (double) .50); sheet.setMargin(HSSFSheet.RightMargin, (double) .50); // Write out the spreadsheet wb.write(out); out.close(); }
From source file:org.tentackle.ui.FormTableUtilityPopup.java
License:Open Source License
/** * Converts the table to an excel spreadsheet. * @param file the output file//w w w. j av a 2s . c o m * @param onlySelected true if export only selected rows * @throws IOException if export failed */ public void excel(File file, boolean onlySelected) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); TableModel model = table.getModel(); TableColumnModel columnModel = table.getColumnModel(); int[] selectedRows = onlySelected ? table.getSelectedRows() : new int[] {}; int rows = onlySelected ? selectedRows.length : model.getRowCount(); // number of data rows int cols = columnModel.getColumnCount(); // number of data columns short srow = 0; // current spreadsheet row // local copies cause might be changed String xTitle = this.title; String xIntro = this.intro; if (xTitle == null) { // get default from window title Window parent = FormHelper.getParentWindow(table); try { // paint page-title xTitle = ((FormWindow) parent).getTitle(); } catch (Exception e) { xTitle = null; } } if (xTitle != null) { HSSFRow row = sheet.createRow(srow); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle cs = wb.createCellStyle(); cs.setAlignment(HSSFCellStyle.ALIGN_CENTER); cs.setFont(font); HSSFCell cell = row.createCell(0); cell.setCellStyle(cs); cell.setCellValue(new HSSFRichTextString(xTitle)); // region rowFrom, colFrom, rowTo, colTo sheet.addMergedRegion(new CellRangeAddress(0, srow, 0, cols - 1)); srow++; } if (xIntro != null || onlySelected) { HSSFRow row = sheet.createRow(srow); HSSFCell cell = row.createCell(0); HSSFCellStyle cs = wb.createCellStyle(); cs.setAlignment(HSSFCellStyle.ALIGN_LEFT); cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cs.setWrapText(true); cell.setCellStyle(cs); if (onlySelected) { if (xIntro == null) { xIntro = ""; } else { xIntro += ", "; } xIntro += Locales.bundle.getString("<nur_selektierte_Zeilen>"); } cell.setCellValue(new HSSFRichTextString(xIntro)); sheet.addMergedRegion(new CellRangeAddress(srow, srow + 2, 0, cols - 1)); srow += 3; } // column headers boolean isAbstractFormTableModel = model instanceof AbstractFormTableModel; srow++; // always skip one line HSSFRow row = sheet.createRow(srow); HSSFFont font = wb.createFont(); font.setItalic(true); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle cs = wb.createCellStyle(); cs.setAlignment(HSSFCellStyle.ALIGN_CENTER); cs.setFont(font); for (int c = 0; c < cols; c++) { HSSFCell cell = row.createCell(c); cell.setCellValue(new HSSFRichTextString(isAbstractFormTableModel ? ((AbstractFormTableModel) model) .getDisplayedColumnName(columnModel.getColumn(c).getModelIndex()) : model.getColumnName(columnModel.getColumn(c).getModelIndex()))); cell.setCellStyle(cs); } srow++; // default cell-style for date HSSFCellStyle dateStyle = wb.createCellStyle(); dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); // cellstyles for numbers List<HSSFCellStyle> numberStyles = new ArrayList<HSSFCellStyle>(); HSSFDataFormat format = wb.createDataFormat(); for (int r = 0; r < rows; r++) { int modelRow = onlySelected ? selectedRows[r] : r; row = sheet.createRow(srow + (short) r); for (int i = 0; i < cols; i++) { int c = columnModel.getColumn(i).getModelIndex(); Object value = model.getValueAt(modelRow, c); HSSFCell cell = row.createCell(i); if (value instanceof Boolean) { cell.setCellValue(((Boolean) value).booleanValue()); } else if (value instanceof BMoney) { BMoney money = (BMoney) value; cell.setCellValue(money.doubleValue()); String fmt = "#,##0"; if (money.scale() > 0) { fmt += "."; for (int j = 0; j < money.scale(); j++) { fmt += "0"; } } // create format short fmtIndex = format.getFormat(fmt); // check if there is already a cellstyle with this scale Iterator<HSSFCellStyle> iter = numberStyles.iterator(); boolean found = false; while (iter.hasNext()) { cs = iter.next(); if (cs.getDataFormat() == fmtIndex) { // reuse that found = true; break; } } if (!found) { // create a new style cs = wb.createCellStyle(); cs.setDataFormat(fmtIndex); numberStyles.add(cs); } cell.setCellStyle(cs); } else if (value instanceof Number) { cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellValue((Date) value); cell.setCellStyle(dateStyle); } else if (value instanceof GregorianCalendar) { cell.setCellValue((GregorianCalendar) value); cell.setCellStyle(dateStyle); } else if (value != null) { cell.setCellValue(new HSSFRichTextString(value.toString())); } } } // set the width for each column for (int c = 0; c < cols; c++) { short width = (short) (columnModel.getColumn(c).getWidth() * 45); // is a reasonable value sheet.setColumnWidth(c, width); } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); // open Excel URLHelper.openURL(file.getPath()); }