List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setColumnWidth
@Override public void setColumnWidth(int columnIndex, int width)
The maximum column width for an individual cell is 255 characters.
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function excelLephichitiet// www . ja v a2 s . c o m * Version: 1.0 * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelLephichitiet(ResourceRequest req, ResourceResponse res) { try { String coQuanQuanLyId = req.getParameter("coQuanQuanLyId"); CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); if (coQuanQuanLyId != null && !coQuanQuanLyId.equals("0")) { coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId)); } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Tinh_hinh_thu_le_phi_chi_tiet"); // define a cell style HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 5000); HSSFFont ngaythangFont = (HSSFFont) workbook.createFont(); ngaythangFont.setItalic(true); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 5000); titleFont.setFontHeightInPoints((short) 15); HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle(); styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellHeader.setFont(tableHeaderFont); styleCellHeader.setWrapText(true); HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle(); styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNgaythang.setFont(ngaythangFont); styleCellNgaythang.setWrapText(true); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); styleCellRight.setFont(tableHeaderFont); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle(); styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNhomTTHC.setFont(tableHeaderFont); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // end style String tungay = ParamUtil.getString(req, "tungay"); String denngay = ParamUtil.getString(req, "denngay"); int rowNum = 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowUBND = sheet.createRow(rowNum); rowUBND.createCell(0).setCellValue(""); rowUBND.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 9)); rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn")); rowUBND.getCell(2).setCellStyle(styleCellHeader); rowNum++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowTenDonVi = sheet.createRow(rowNum); rowTenDonVi.createCell(0).setCellValue(coQuanQuanLy.getTen()); rowTenDonVi.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 9)); rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp")); rowTenDonVi.getCell(2).setCellStyle(styleCellHeader); rowNum++; List<NhomThuTucHanhChinh> listnhomtt = NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinhs(0, NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinhsCount()); List<Lephi_chitiet> listlephichitiet = Lephi_chitietLocalServiceUtil .lephichitiet(ParamUtil.getString(req, "id_loaihoso"), tungay, denngay); String loaihoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.loaihoso"); String nguoinop = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.nguoinop"); String chuhoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.chuhoso"); String diachi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.diachi"); String sobohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.sobohoso"); String lephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.lephi"); String phihoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.phihoso"); String tonglephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.tonglephi"); String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.ghichu"); String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephichitiet.tieude"); String[] header = { "STT", loaihoso, nguoinop, chuhoso, diachi, sobohoso, lephi, phihoso, tonglephi, ghichu }; rowNum++; int[] width = { 1500, 15000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000 }; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 9)); HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styleTitle); titleCell.setCellValue(tieude); rowNum += 2; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 9)); HSSFRow ngaythangRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell ngaythangCell = ngaythangRow.createCell(0); ngaythangCell.setCellStyle(styleCellNgaythang); ngaythangCell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tungay") + " " + tungay + " " + ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.denngay") + " " + denngay); rowNum = rowNum + 3; HSSFRow headerRow = (HSSFRow) sheet.createRow(rowNum); int colNum = 0; for (int i = 0; i < header.length; i++) { HSSFCell cell = headerRow.createCell(colNum); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); sheet.setColumnWidth(i, width[i]); colNum++; } rowNum++; long tong = 0; int stt = 0; for (int j = 0; j < listnhomtt.size(); j++) { if (ActionUtil.checkHosoByNHOMTTHCID(listnhomtt.get(j).getId(), listlephichitiet)) { sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 9)); HSSFRow rowNhomTTHC = (HSSFRow) sheet.createRow(rowNum); HSSFCell cellNhomTTHC = rowNhomTTHC.createCell(0); cellNhomTTHC.setCellValue(listnhomtt.get(j).getTen()); cellNhomTTHC.setCellStyle(styleCellNhomTTHC); rowNhomTTHC.createCell(9).setCellStyle(styleCellNhomTTHC); rowNum++; for (int a = 0; a < listlephichitiet.size(); a++) { // khai bao dong va cac cell if (listnhomtt.get(j).getId() == listlephichitiet.get(a).getNHOMTHUTUCHANHCHINHID()) { stt++; HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); // set style cho cac cell cell.setCellStyle(styleCellCenter); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); // set gia tri cho cac cell cell.setCellValue(stt); cell1.setCellValue(listlephichitiet.get(a).getTENHOSO()); cell2.setCellValue(listlephichitiet.get(a).getNGUOINOP()); cell3.setCellValue(listlephichitiet.get(a).getCHUHOSO()); cell4.setCellValue(listlephichitiet.get(a).getDIACHI()); cell5.setCellValue(listlephichitiet.get(a).getSOBOHOSO()); cell6.setCellValue(listlephichitiet.get(a).getLEPHI()); cell7.setCellValue(listlephichitiet.get(a).getPHIHOSO()); cell8.setCellValue(listlephichitiet.get(a).getTONGLEPHI()); tong = tong + Long.valueOf(listlephichitiet.get(a).getTONGLEPHI()); rowNum++; } } } } sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 7)); HSSFRow rowTong = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = rowTong.createCell(0); HSSFCell cell1 = rowTong.createCell(1); HSSFCell cell2 = rowTong.createCell(2); HSSFCell cell3 = rowTong.createCell(3); HSSFCell cell4 = rowTong.createCell(4); HSSFCell cell5 = rowTong.createCell(5); HSSFCell cell6 = rowTong.createCell(6); HSSFCell cell7 = rowTong.createCell(7); HSSFCell cell8 = rowTong.createCell(8); HSSFCell cell9 = rowTong.createCell(9); // set style cho cac cell cell.setCellStyle(styleCellRight); cell1.setCellStyle(styleCellCenter); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); // set value for cells of rowtong cell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tongcong")); cell8.setCellValue(tong); res.setContentType("application/vnd.ms-excel"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaotinhhinhlephi.xls"); OutputStream out = res.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function excelLephilinhvuc// w w w. ja va 2s. com * Version: 1.0 * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelLephilinhvuc(ResourceRequest req, ResourceResponse res) { try { String coQuanQuanLyId = req.getParameter("coQuanQuanLyId"); CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); if (coQuanQuanLyId != null && !coQuanQuanLyId.equals("0")) { coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId)); } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Tinh_hinh_thu_le_phi_theo_linh_vuc"); // define a cell style HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 5000); HSSFFont ngaythangFont = (HSSFFont) workbook.createFont(); ngaythangFont.setItalic(true); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 5000); titleFont.setFontHeightInPoints((short) 15); HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle(); styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellHeader.setFont(tableHeaderFont); styleCellHeader.setWrapText(true); HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle(); styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNgaythang.setFont(ngaythangFont); styleCellNgaythang.setWrapText(true); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); styleCellRight.setFont(tableHeaderFont); HSSFCellStyle styleCellRightNobold = (HSSFCellStyle) workbook.createCellStyle(); styleCellRightNobold.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRightNobold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRightNobold.setWrapText(true); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle(); styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNhomTTHC.setFont(tableHeaderFont); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // end style String tungay = ParamUtil.getString(req, "tungay"); String denngay = ParamUtil.getString(req, "denngay"); int rowNum = 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowUBND = sheet.createRow(rowNum); rowUBND.createCell(0).setCellValue(""); rowUBND.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 6)); rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn")); rowUBND.getCell(2).setCellStyle(styleCellHeader); rowNum++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowTenDonVi = sheet.createRow(rowNum); rowTenDonVi.createCell(0).setCellValue(coQuanQuanLy.getTen()); rowTenDonVi.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 6)); rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp")); rowTenDonVi.getCell(2).setCellStyle(styleCellHeader); rowNum++; List<Lephi_linhvuc> listlephilinhvuc = Lephi_linhvucLocalServiceUtil .lephilinhvuc(ParamUtil.getString(req, "id_linhvuc"), tungay, denngay); String linhvuc = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.linhvuc"); String tongsohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tongsohoso"); String lephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.lephi"); String phihoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.phihoso"); String tonglephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tonglephi"); String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.ghichu"); String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tieude"); String[] header = { "STT", linhvuc, tongsohoso, lephi, phihoso, tonglephi, ghichu }; rowNum++; int[] width = { 1500, 15000, 5000, 5000, 5000, 5000, 5000, 5000, 5000 }; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 6)); HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styleTitle); titleCell.setCellValue(tieude); rowNum += 2; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 6)); HSSFRow ngaythangRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell ngaythangCell = ngaythangRow.createCell(0); ngaythangCell.setCellStyle(styleCellNgaythang); ngaythangCell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tungay") + " " + tungay + " " + ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.denngay") + " " + denngay); rowNum = rowNum + 3; HSSFRow headerRow = (HSSFRow) sheet.createRow(rowNum); int colNum = 0; for (int i = 0; i < header.length; i++) { HSSFCell cell = headerRow.createCell(colNum); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); sheet.setColumnWidth(i, width[i]); colNum++; } rowNum++; long tong = 0; int stt = 0; for (int a = 0; a < listlephilinhvuc.size(); a++) { // khai bao dong va cac cell stt++; HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); // set style cho cac cell cell.setCellStyle(styleCellCenter); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellRightNobold); cell4.setCellStyle(styleCellRightNobold); cell5.setCellStyle(styleCellRightNobold); cell6.setCellStyle(styleCellRightNobold); // set gia tri cho cac cell cell.setCellValue(stt); cell1.setCellValue(NhomThuTucHanhChinhLocalServiceUtil .getNhomThuTucHanhChinh(listlephilinhvuc.get(a).getNHOMTHUTUCHANHCHINHID()).getTen()); cell2.setCellValue(listlephilinhvuc.get(a).getTONGHOSO()); cell3.setCellValue(listlephilinhvuc.get(a).getLEPHIHOSO()); cell4.setCellValue(listlephilinhvuc.get(a).getPHIHOSO()); cell5.setCellValue(listlephilinhvuc.get(a).getTONGLEPHI()); tong = tong + Long.valueOf(listlephilinhvuc.get(a).getTONGLEPHI()); rowNum++; } sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 4)); HSSFRow rowTong = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = rowTong.createCell(0); HSSFCell cell1 = rowTong.createCell(1); HSSFCell cell2 = rowTong.createCell(2); HSSFCell cell3 = rowTong.createCell(3); HSSFCell cell4 = rowTong.createCell(4); HSSFCell cell5 = rowTong.createCell(5); HSSFCell cell6 = rowTong.createCell(6); // set style cho cac cell cell.setCellStyle(styleCellRight); cell1.setCellStyle(styleCellCenter); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellRightNobold); cell6.setCellStyle(styleCellCenter); // set value for cells of rowtong cell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tongcong")); cell5.setCellValue(tong); res.setContentType("application/vnd.ms-excel"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaotinhhinhlephi.xls"); OutputStream out = res.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function excelLephitonghop//ww w . ja va2s . c o m * Version: 1.0 * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelLephitonghop(ResourceRequest req, ResourceResponse res) { try { String coQuanQuanLyId = req.getParameter("coQuanQuanLyId"); CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); if (coQuanQuanLyId != null && !coQuanQuanLyId.equals("0")) { coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId)); } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Tinh_hinh_thu_le_phi_tong_hop"); // define a cell style HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 5000); HSSFFont ngaythangFont = (HSSFFont) workbook.createFont(); ngaythangFont.setItalic(true); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 5000); titleFont.setFontHeightInPoints((short) 15); HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle(); styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellHeader.setFont(tableHeaderFont); styleCellHeader.setWrapText(true); HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle(); styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNgaythang.setFont(ngaythangFont); styleCellNgaythang.setWrapText(true); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); styleCellRight.setFont(tableHeaderFont); HSSFCellStyle styleCellRightNobold = (HSSFCellStyle) workbook.createCellStyle(); styleCellRightNobold.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRightNobold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRightNobold.setWrapText(true); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle(); styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNhomTTHC.setFont(tableHeaderFont); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // end style String tungay = ParamUtil.getString(req, "tungay"); String denngay = ParamUtil.getString(req, "denngay"); int rowNum = 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowUBND = sheet.createRow(rowNum); rowUBND.createCell(0).setCellValue(""); rowUBND.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 6)); rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn")); rowUBND.getCell(2).setCellStyle(styleCellHeader); rowNum++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowTenDonVi = sheet.createRow(rowNum); rowTenDonVi.createCell(0).setCellValue(coQuanQuanLy.getTen()); rowTenDonVi.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 6)); rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp")); rowTenDonVi.getCell(2).setCellStyle(styleCellHeader); rowNum++; List<NhomThuTucHanhChinh> listnhomtt = NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinhs(0, NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinhsCount()); List<Lephi_tonghop> listlephitonghop = Lephi_tonghopLocalServiceUtil .lephitonghop(ParamUtil.getString(req, "id_loaihoso"), tungay, denngay); String linhvuc = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.linhvuc"); String tongsohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tongsohoso"); String lephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.lephi"); String phihoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.phihoso"); String tonglephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.tonglephi"); String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephilinhvuc.ghichu"); String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephitonghop.tieude"); String[] header = { "STT", linhvuc, tongsohoso, lephi, phihoso, tonglephi, ghichu }; rowNum++; int[] width = { 1500, 15000, 5000, 5000, 5000, 5000, 5000, 5000, 5000 }; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 6)); HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styleTitle); titleCell.setCellValue(tieude); rowNum += 2; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 6)); HSSFRow ngaythangRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell ngaythangCell = ngaythangRow.createCell(0); ngaythangCell.setCellStyle(styleCellNgaythang); ngaythangCell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tungay") + " " + tungay + " " + ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.denngay") + " " + denngay); rowNum = rowNum + 3; HSSFRow headerRow = (HSSFRow) sheet.createRow(rowNum); int colNum = 0; for (int i = 0; i < header.length; i++) { HSSFCell cell = headerRow.createCell(colNum); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); sheet.setColumnWidth(i, width[i]); colNum++; } rowNum++; long tong = 0; int stt = 0; for (int j = 0; j < listnhomtt.size(); j++) { if (ActionUtil.checkLePhiTongHopByNHOMTTHCID(listnhomtt.get(j).getId(), listlephitonghop)) { sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 6)); HSSFRow rowNhomTTHC = (HSSFRow) sheet.createRow(rowNum); HSSFCell cellNhomTTHC = rowNhomTTHC.createCell(0); cellNhomTTHC.setCellValue(listnhomtt.get(j).getTen()); cellNhomTTHC.setCellStyle(styleCellNhomTTHC); rowNhomTTHC.createCell(6).setCellStyle(styleCellNhomTTHC); rowNum++; for (int a = 0; a < listlephitonghop.size(); a++) { // khai bao dong va cac cell if (listnhomtt.get(j).getId() == listlephitonghop.get(a).getNHOMTHUTUCHANHCHINHID()) { stt++; HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); // set style cho cac cell cell.setCellStyle(styleCellCenter); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellRightNobold); cell4.setCellStyle(styleCellRightNobold); cell5.setCellStyle(styleCellRightNobold); cell6.setCellStyle(styleCellRightNobold); // set gia tri cho cac cell cell.setCellValue(stt); cell1.setCellValue( ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaolephitonghop.thang") + " " + listlephitonghop.get(a).getTHANGNHAN()); cell2.setCellValue(listlephitonghop.get(a).getTONGHOSO()); cell3.setCellValue(listlephitonghop.get(a).getLEPHI()); cell4.setCellValue(listlephitonghop.get(a).getPHIHOSO()); cell5.setCellValue(listlephitonghop.get(a).getTONGLEPHI()); tong = tong + Long.valueOf(listlephitonghop.get(a).getTONGLEPHI()); rowNum++; } } } } sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 4)); HSSFRow rowTong = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = rowTong.createCell(0); HSSFCell cell1 = rowTong.createCell(1); HSSFCell cell2 = rowTong.createCell(2); HSSFCell cell3 = rowTong.createCell(3); HSSFCell cell4 = rowTong.createCell(4); HSSFCell cell5 = rowTong.createCell(5); HSSFCell cell6 = rowTong.createCell(6); // set style cho cac cell cell.setCellStyle(styleCellRight); cell1.setCellStyle(styleCellCenter); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellRightNobold); cell6.setCellStyle(styleCellCenter); // set value for cells of rowtong cell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tongcong")); cell5.setCellValue(tong); res.setContentType("application/vnd.ms-excel"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaotinhhinhlephi.xls"); OutputStream out = res.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function excel BaoCaochungthuc * Version: 1.0// ww w. j a v a2s. c o m * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelBaocaochungthuc(ResourceRequest req, ResourceResponse res) { try { DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); String coQuanQuanLyId = req.getParameter("coQuanQuanLyId"); CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); if (coQuanQuanLyId != null && !coQuanQuanLyId.equals("0")) { coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId)); } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Baocaohosochungthuc"); // define a cell style HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 5000); HSSFFont ngaythangFont = (HSSFFont) workbook.createFont(); ngaythangFont.setItalic(true); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 5000); titleFont.setFontHeightInPoints((short) 15); HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle(); styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellHeader.setFont(tableHeaderFont); styleCellHeader.setWrapText(true); HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle(); styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNgaythang.setFont(ngaythangFont); styleCellNgaythang.setWrapText(true); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); styleCellRight.setFont(tableHeaderFont); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle(); styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNhomTTHC.setFont(tableHeaderFont); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // end style String tungay = ParamUtil.getString(req, "tungay"); String denngay = ParamUtil.getString(req, "denngay"); int rowNum = 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowUBND = sheet.createRow(rowNum); rowUBND.createCell(0).setCellValue(""); rowUBND.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 11)); rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn")); rowUBND.getCell(2).setCellStyle(styleCellHeader); rowNum++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowTenDonVi = sheet.createRow(rowNum); rowTenDonVi.createCell(0).setCellValue(coQuanQuanLy.getTen()); rowTenDonVi.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 11)); rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp")); rowTenDonVi.getCell(2).setCellStyle(styleCellHeader); rowNum++; String str_loaihoso = ParamUtil.getString(req, "id_loaihoso"); List<HoSoChungThuc> listhosochungthuc = ActionUtil.listhsct(str_loaihoso, tungay, denngay); String sochungthuc = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.sochungthuc"); String nguoinop = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.nguoinop"); String diachi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.diachi"); String noidungcongviec = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.noidungcongviec"); String loaigiayto = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.loaigiayto"); String sobo = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.sobo"); String sotrang = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.sotrang"); String ngaynhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.ngaynhan"); String ngaytraketqua = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.ngaytraketqua"); String lephi = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.lephi"); String nguoithuchien = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.nguoithuchien"); String nguoiky = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.nguoiky"); String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaochungthuc.tieude"); String[] header = { "STT", sochungthuc, nguoinop, diachi, noidungcongviec, loaigiayto, sobo, sotrang, ngaynhan, ngaytraketqua, lephi, nguoithuchien, nguoiky }; rowNum++; int[] width = { 1500, 2000, 5000, 5000, 5000, 5000, 2000, 2000, 3000, 3000, 2000, 4000, 4000 }; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 12)); HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styleTitle); titleCell.setCellValue(tieude); rowNum += 2; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 12)); HSSFRow ngaythangRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell ngaythangCell = ngaythangRow.createCell(0); ngaythangCell.setCellStyle(styleCellNgaythang); ngaythangCell.setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.tungay") + " " + tungay + " " + ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.denngay") + " " + denngay); rowNum = rowNum + 3; HSSFRow headerRow = (HSSFRow) sheet.createRow(rowNum); int colNum = 0; for (int i = 0; i < header.length; i++) { HSSFCell cell = headerRow.createCell(colNum); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); sheet.setColumnWidth(i, width[i]); colNum++; } rowNum++; long tong = 0; int stt = 0; for (int a = 0; a < listhosochungthuc.size(); a++) { // khai bao dong va cac cell stt++; HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); // set style cho cac cell cell.setCellStyle(styleCellCenter); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); // set gia tri cho cac cell String diachithuongtru = listhosochungthuc.get(a).getDiaChiThuongTruNguoiNop() != null ? listhosochungthuc.get(a).getDiaChiThuongTruNguoiNop() : ""; String dienthoaididong = listhosochungthuc.get(a).getSoDienThoaiDiDongNguoiNop() != null ? "T:" + listhosochungthuc.get(a).getSoDienThoaiDiDongNguoiNop() : ""; cell.setCellValue(stt); cell1.setCellValue(listhosochungthuc.get(a).getSoChungThuc() != null ? listhosochungthuc.get(a).getSoChungThuc() : ""); cell2.setCellValue(listhosochungthuc.get(a).getHoTenNguoiNopHoSo() != null ? listhosochungthuc.get(a).getHoTenNguoiNopHoSo() : ""); cell3.setCellValue(diachithuongtru + "\n" + dienthoaididong); cell4.setCellValue(listhosochungthuc.get(a).getTenChungThuc()); cell5.setCellValue( listhosochungthuc.get(a).getThuTucHanhChinhId() != null ? ThuTucHanhChinhLocalServiceUtil .getThuTucHanhChinh(listhosochungthuc.get(a).getThuTucHanhChinhId()).getTen() : ""); cell6.setCellValue(listhosochungthuc.get(a).getSoBoHoSo()); cell7.setCellValue(listhosochungthuc.get(a).getSoTo()); cell8.setCellValue(listhosochungthuc.get(a).getNgayNopHoSo() != null ? df.format(listhosochungthuc.get(a).getNgayNopHoSo()) : ""); cell9.setCellValue(listhosochungthuc.get(a).getNgayTraKetQua() != null ? df.format(listhosochungthuc.get(a).getNgayTraKetQua()) : ""); cell10.setCellValue(listhosochungthuc.get(a).getLePhi()); cell11.setCellValue( listhosochungthuc.get(a).getCanBoTiepNhanId() != null ? CongChucLocalServiceUtil .fetchCongChuc(listhosochungthuc.get(a).getCanBoTiepNhanId()).getHoVaTen() : ""); cell12.setCellValue( listhosochungthuc.get(a).getTenCanBoKy() != null ? listhosochungthuc.get(a).getTenCanBoKy() : ""); rowNum++; } res.setContentType("application/vnd.ms-excel"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaohosochungthuc.xls"); OutputStream out = res.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function excel report detail//from w ww.j a v a2 s . co 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 finishSheet(HSSFSheet sheet, HSSFWorkbook wb, String qcName, String qcType, String sheetName) { int i, columnIndex; ArrayList<DictionaryDO> tempQcColumns; DictionaryDO dict;//ww w . j a va 2s .c om HashSet<Integer> emptyColumns; Name rangeName; Row row; String rangeFormula; if (qcColumns != null && !qcColumns.isEmpty()) row = sheet.getRow(32); else row = sheet.getRow(3); emptyColumns = new HashSet<Integer>(); for (i = 0; i < row.getLastCellNum(); i++) { if (i >= maxChars.size() || maxChars.get(i) == 0) emptyColumns.add(i); } setHeaderCells(sheet, qcName, qcType, sheetName); if (qcColumns != null && !qcColumns.isEmpty()) { tempQcColumns = new ArrayList<DictionaryDO>(); tempQcColumns.addAll(qcColumns); for (i = tempQcColumns.size() - 1; i > -1; i--) { if (emptyColumns.contains(i + 5)) { tempQcColumns.remove(i); removeColumn(sheet, i + 5); maxChars.remove(i + 5); } } rangeName = getName(wb, sheet, "RowNumber"); if (rangeName == null) { rangeName = wb.createName(); rangeName.setSheetIndex(wb.getSheetIndex(sheet)); rangeName.setNameName("RowNumber"); } rangeFormula = sheet.getSheetName() + "!$" + CellReference.convertNumToColString(0) + "$33:" + "$" + CellReference.convertNumToColString(0) + "$" + (sheet.getLastRowNum() + 1); rangeName.setRefersToFormula(rangeFormula); /* * Create named ranges for the graph to be able to locate the appropriate * data */ columnIndex = 5; for (i = 0; i < tempQcColumns.size(); i++) { dict = tempQcColumns.get(i); if (!DataBaseUtil.isEmpty(dict.getCode())) { rangeName = getName(wb, sheet, dict.getCode()); if (rangeName == null) { rangeName = wb.createName(); rangeName.setSheetIndex(wb.getSheetIndex(sheet)); rangeName.setNameName(dict.getCode()); } rangeFormula = rangeName.getRefersToFormula(); if (rangeFormula != null && rangeFormula.length() > 0 && !"$A$2".equals(rangeFormula.substring(rangeFormula.indexOf("!") + 1))) rangeFormula += ","; else rangeFormula = ""; rangeFormula += sheet.getSheetName() + "!$" + CellReference.convertNumToColString(columnIndex) + "$33:" + "$" + CellReference.convertNumToColString(columnIndex) + "$" + (sheet.getLastRowNum() + 1); rangeName.setRefersToFormula(rangeFormula); } columnIndex++; } /* * make each column wide enough to show the longest string in it; the * width for each column is set as the maximum number of characters in * that column multiplied by 256; this is because the default width of * one character is 1/256 units in Excel */ for (i = 5; i < maxChars.size(); i++) sheet.setColumnWidth(i, maxChars.get(i) * 256); } else if (worksheetHeaders != null && worksheetHeaders.size() > 0) { /* * make each column wide enough to show the longest string in it; the * width for each column is set as the maximum number of characters in * that column multiplied by 256; this is because the default width of * one character is 1/256 units in Excel */ for (i = 0; i < maxChars.size(); i++) sheet.setColumnWidth(i, maxChars.get(i) * 256); } wb.setSheetName(wb.getSheetIndex(sheet), sheetName); sheet.setForceFormulaRecalculation(true); maxChars.clear(); }
From source file:org.openmrs.module.kenyaemr.export.ExportLayouter.java
License:Open Source License
/** * Builds the report layout./*from w w w.ja v a 2 s .c om*/ * * This doesn't have any data yet. This is template. */ public static void buildReport(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // Set column widths worksheet.setColumnWidth(0, 5000); worksheet.setColumnWidth(1, 6000); worksheet.setColumnWidth(2, 5000); worksheet.setColumnWidth(3, 3000); worksheet.setColumnWidth(4, 3000); worksheet.setColumnWidth(5, 5000); worksheet.setColumnWidth(6, 5000); worksheet.setColumnWidth(7, 5000); worksheet.setColumnWidth(8, 5000); worksheet.setColumnWidth(9, 5000); // Build the title and date headers buildTitle(worksheet, startRowIndex, startColIndex); // Build the column headers buildHeaders(worksheet, startRowIndex, startColIndex); }
From source file:org.openswing.swing.export.java.ExportToExcel.java
License:Open Source License
/** * Append current row to result StringBuffer. * @return current row to append/*from w ww .j ava 2 s . co m*/ */ private int appendRow(HSSFWorkbook wb, HSSFSheet s, Object vo, ExportOptions exportOptions, GridExportOptions opt, Hashtable gettersMethods, HSSFCellStyle csText, HSSFCellStyle csBool, HSSFCellStyle csDecNum, HSSFCellStyle csIntNum, HSSFCellStyle csDate, HSSFCellStyle csTime, HSSFCellStyle csDateTime, int rownum, int tableType) throws Throwable { int type; HSSFRow r = null; HSSFCell c = null; r = s.createRow(rownum); String aName = null; Method getter = null; Class clazz = null; Object obj = null; for (short i = 0; i < opt.getExportColumns().size(); i++) { c = r.createCell(i); clazz = vo.getClass(); obj = vo; aName = opt.getExportAttrColumns().get(i).toString(); // value = ((Method)gettersMethods.get(aName)).invoke(vo,new Object[0]); // check if the specified attribute is a composed attribute and there exist inner v.o. to instantiate... while (aName.indexOf(".") != -1) { try { getter = clazz.getMethod( "get" + aName.substring(0, 1).toUpperCase() + aName.substring(1, aName.indexOf(".")), new Class[0]); } catch (NoSuchMethodException ex2) { getter = clazz.getMethod( "is" + aName.substring(0, 1).toUpperCase() + aName.substring(1, aName.indexOf(".")), new Class[0]); } aName = aName.substring(aName.indexOf(".") + 1); clazz = getter.getReturnType(); obj = getter.invoke(obj, new Object[0]); if (obj == null) break; } try { getter = clazz.getMethod("get" + aName.substring(0, 1).toUpperCase() + aName.substring(1), new Class[0]); } catch (NoSuchMethodException ex2) { getter = clazz.getMethod("is" + aName.substring(0, 1).toUpperCase() + aName.substring(1), new Class[0]); } if (obj != null) obj = getter.invoke(obj, new Object[0]); if (obj != null) { if (obj instanceof String) { try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(obj.toString()); c.setCellStyle(csText); } else if (obj instanceof BigDecimal || obj instanceof Double || obj instanceof Float || obj.getClass() == Double.TYPE || obj.getClass() == Float.TYPE) { c.setCellValue(Double.parseDouble(obj.toString())); c.setCellStyle(csDecNum); } else if (obj instanceof Integer || obj instanceof Short || obj instanceof Long || obj.getClass() == Integer.TYPE || obj.getClass() == Short.TYPE || obj.getClass() == Long.TYPE) { c.setCellValue(Double.parseDouble(obj.toString())); c.setCellStyle(csIntNum); } else if (obj instanceof Boolean) { c.setCellValue(((Boolean) obj).booleanValue()); c.setCellStyle(csBool); } else if (obj.getClass().equals(boolean.class)) { c.setCellValue(((Boolean) obj).booleanValue()); c.setCellStyle(csBool); } else if (obj instanceof Date || obj instanceof java.util.Date || obj instanceof java.sql.Timestamp) { c.setCellValue((java.util.Date) obj); type = ((Integer) opt.getColumnsType().get(opt.getExportAttrColumns().get(i))).intValue(); if (type == opt.TYPE_DATE) c.setCellStyle(csDate); else if (type == opt.TYPE_DATE_TIME) c.setCellStyle(csDateTime); else if (type == opt.TYPE_TIME) { c.setCellStyle(csTime); Calendar cal = Calendar.getInstance(); cal.setTime((java.util.Date) obj); if (cal.get(Calendar.YEAR) < 1900) cal.set(Calendar.YEAR, 2000); c.setCellValue(cal.getTime()); } } } else { c.setCellValue(""); c.setCellStyle(csText); } // make this column a bit wider s.setColumnWidth(i, (short) (256 / 8 * ((Integer) opt.getColumnsWidth().get(opt.getExportAttrColumns().get(i))).shortValue())); } rownum++; if (opt.getCallbacks() != null) { if (tableType == 0) rownum = processComponent(wb, s, exportOptions, opt.getCallbacks().getComponentPerRowInHeader((ValueObject) vo, rownum), rownum); else if (tableType == 1) rownum = processComponent(wb, s, exportOptions, opt.getCallbacks().getComponentPerRow((ValueObject) vo, rownum), rownum); else if (tableType == 2) rownum = processComponent(wb, s, exportOptions, opt.getCallbacks().getComponentPerRowInFooter((ValueObject) vo, rownum), rownum); } return rownum; }
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)); }//w w w. 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/* w w w . j a v a 2s .c o m*/ * @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); } } } } }