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.gbif.portal.web.view.ExcelView.java
License:Open Source License
/** * @see org.springframework.web.servlet.view.document.AbstractExcelView#buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook, javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse) *///from w w w.ja v a2 s . c om @SuppressWarnings("unchecked") protected void buildExcelDocument(Map model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) { Locale locale = RequestContextUtils.getLocale(request); //create results sheet String sheetTitle = messageSource.getMessage(resultsSheetTitleI18nKey, null, locale); HSSFSheet resultsSheet = workbook.createSheet(sheetTitle); resultsSheet.setDefaultColumnWidth((short) (defaultColumnWidth)); //create a titles style HSSFCellStyle titlesStyle = workbook.createCellStyle(); titlesStyle.setFillPattern((short) HSSFCellStyle.SOLID_FOREGROUND); titlesStyle.setFillBackgroundColor(HSSFColor.DARK_GREEN.index); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.WHITE.index); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); titlesStyle.setFont(font); //create a hyperlink style HSSFCellStyle hyperlinkStyle = workbook.createCellStyle(); HSSFFont hyperLinkFont = workbook.createFont(); hyperLinkFont.setColor(HSSFColor.BLUE.index); hyperlinkStyle.setFont(hyperLinkFont); //reused cell reference HSSFCell cell = null; //write results sheet List<Field> fields = (List<Field>) request.getAttribute("requestedFields"); List results = (List) request.getAttribute("results"); int currentRow = 0; //column headings for (int i = 0; i < fields.size(); i++) { cell = getCell(resultsSheet, currentRow, i); cell.setCellStyle(titlesStyle); String title = messageSource.getMessage(fields.get(i).getFieldI18nNameKey(), null, locale); setText(cell, title); short titleLength = (short) (title.length() * charWidth); short columnWidth = resultsSheet.getColumnWidth((short) i); //update column width for long columns if (columnWidth < titleLength) { resultsSheet.setColumnWidth((short) i, (short) (titleLength)); } } currentRow++; //results for (int i = 0; i < results.size(); i++) { Object result = results.get(i); for (int j = 0; j < fields.size(); j++) { Field field = fields.get(j); cell = getCell(resultsSheet, currentRow, j); try { short textWidth = defaultColumnWidth; String propertyValue = field.getRenderValue(request, messageSource, locale, result); if (propertyValue != null) setText(cell, propertyValue); if (field instanceof UrlField) { if (propertyValue != null && propertyValue.length() < urlMaxLength) { String linkFormula = "HYPERLINK(\"" + propertyValue + "\")"; cell.setCellFormula(linkFormula); cell.setCellStyle(hyperlinkStyle); } } if (propertyValue != null) { int textWidthInt = propertyValue.length() * charWidth; if (textWidthInt > 32768) { textWidth = Short.MAX_VALUE; } else { textWidth = (short) textWidthInt; } } //update column width for long columns short columnWidth = resultsSheet.getColumnWidth((short) j); if (textWidth > columnWidth) { resultsSheet.setColumnWidth((short) j, (short) textWidth); } } catch (Exception e) { logger.warn(e.getMessage(), e); } } currentRow++; } //set up details sheet HSSFSheet detailsSheet = workbook .createSheet(messageSource.getMessage(detailsSheetTitleI18nKey, null, locale)); detailsSheet.setColumnWidth((short) 0, (short) 6000); detailsSheet.setColumnWidth((short) 1, (short) Short.MAX_VALUE); ; List<FilterDTO> filters = (List) request.getAttribute("filters"); CriteriaDTO criteria = (CriteriaDTO) request.getAttribute("criteria"); String query = FilterUtils.getQueryDescription(filters, criteria, messageSource, locale); cell = getCell(detailsSheet, 0, 0); cell.setCellStyle(titlesStyle); setText(cell, messageSource.getMessage("occurrence.search.description", null, locale)); cell = getCell(detailsSheet, 0, 1); setText(cell, query); //add url for search cell = getCell(detailsSheet, 1, 0); cell.setCellStyle(titlesStyle); setText(cell, messageSource.getMessage("occurrence.search.url", null, locale)); cell = getCell(detailsSheet, 1, 1); cell.setCellStyle(hyperlinkStyle); String url = "http://" + request.getHeader("host") + request.getContextPath() + "/occurrences/search.htm?" + CriteriaUtil.getUrl(criteria); setText(cell, url); //there is a formula limit in Excel of 255 characters if (url != null && url.length() < urlMaxLength) { String link = "HYPERLINK(\"" + url + "\")"; cell.setCellFormula(link); } //add url for download page cell = getCell(detailsSheet, 2, 0); cell.setCellStyle(titlesStyle); setText(cell, messageSource.getMessage("occurrence.search.download.url", null, locale)); cell = getCell(detailsSheet, 2, 1); cell.setCellStyle(hyperlinkStyle); String downloadurl = "http://" + request.getHeader("host") + request.getContextPath() + "/occurrences/download.htm?" + CriteriaUtil.getUrl(criteria); setText(cell, downloadurl); if (downloadurl != null && downloadurl.length() < urlMaxLength) { String link = "HYPERLINK(\"" + downloadurl + "\")"; cell.setCellFormula(link); } //add date for this download cell = getCell(detailsSheet, 3, 0); cell.setCellStyle(titlesStyle); setText(cell, messageSource.getMessage("occurrence.search.download.date", null, locale)); cell = getCell(detailsSheet, 3, 1); SimpleDateFormat sdf = new SimpleDateFormat("dd MMM yyyy"); setText(cell, sdf.format(new Date(System.currentTimeMillis()))); }
From source file:org.goobi.production.flow.helper.SearchResultHelper.java
License:Open Source License
@SuppressWarnings("deprecation") public HSSFWorkbook getResult(List<SearchColumn> columnList, String filter, String order, boolean showClosedProcesses, boolean showArchivedProjects) { List<SearchColumn> sortedList = new ArrayList<>(columnList.size()); for (SearchColumn sc : columnList) { if (!sc.getTableName().startsWith("metadata")) { sortedList.add(sc);// w w w. j ava 2 s . c o m } } for (SearchColumn sc : columnList) { if (sc.getTableName().startsWith("metadata")) { sortedList.add(sc); } } columnList = sortedList; @SuppressWarnings("rawtypes") List list = search(columnList, filter, order, showClosedProcesses, showArchivedProjects); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Search results"); // create title row int titleColumnNumber = 0; HSSFRow title = sheet.createRow(0); int columnNumber = 0; for (SearchColumn sc : columnList) { HSSFCell titleCell = title.createCell(titleColumnNumber++); titleCell.setCellValue(Helper.getTranslation(sc.getValue())); HSSFCellStyle cellStyle = wb.createCellStyle(); HSSFFont cellFont = wb.createFont(); cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(cellFont); titleCell.setCellStyle(cellStyle); } int rowNumber = 1; for (Object obj : list) { Object[] objArr = (Object[]) obj; HSSFRow row = sheet.createRow(rowNumber++); columnNumber = 0; for (Object entry : objArr) { HSSFCell cell = row.createCell(columnNumber++); if (entry != null) { cell.setCellValue(((String) entry).replace("\"", "")); } else { cell.setCellValue(""); } } } sheet.createFreezePane(0, 1); for (int i = 0; i < columnList.size(); i++) { sheet.autoSizeColumn(i); if (sheet.getColumnWidth(i) > 15000) { sheet.setColumnWidth(i, 15000); } } return wb; }
From source file:org.jfree.workbook.io.XLWriter.java
License:Open Source License
/** * Creates a new sheet in the HSSFWorkbook, based on the supplied worksheet. * /*from w w w . j av a 2 s. c o m*/ * @param hssfWorkbook the workbook. * @param worksheet the worksheet. */ private void createHSSFWorksheet(HSSFWorkbook hssfWorkbook, Worksheet worksheet) { HSSFSheet hssfSheet = hssfWorkbook.createSheet(worksheet.getName()); Iterator iterator = worksheet.getCells().getRowsIterator(); while (iterator.hasNext()) { Row row = (Row) iterator.next(); createHSSFRow(hssfWorkbook, hssfSheet, worksheet, row); } ColumnAttributesManager attributes = worksheet.getColumnAttributesManager(); // default column width (pts as double) : convert it to chars as short... short w = (short) (attributes.getDefaultColumnWidth() / 4.0); hssfSheet.setDefaultColumnWidth(w); RowAttributesManager rowAttrs = worksheet.getRowAttributesManager(); // default row height ... hssfSheet.setDefaultRowHeightInPoints((float) rowAttrs.getDefaultRowHeight()); // now define the individual column widths... iterator = attributes.getAttributesIterator(); while (iterator.hasNext()) { ColumnAttributes ca = (ColumnAttributes) iterator.next(); for (int c = ca.getStartColumn(); c <= ca.getEndColumn(); c++) { hssfSheet.setColumnWidth((short) c, (short) (ca.getWidth() * 64)); } } }
From source file:org.jxstar.report.studio.ExportStatBO.java
/** * //w ww . j a v a 2s. c o m * @param fileTitle -- * @param titles -- * @param sheet -- * @return */ private HSSFSheet createTitleArea(String fileTitle, String[] titles, HSSFSheet sheet) { HSSFCell sfCell = null; int rsCnt = titles.length + 1; HSSFWorkbook wb = sheet.getWorkbook(); //? HSSFCellStyle titleStyle = expXls.createTitleStyle(wb); //? HSSFCellStyle headerStyle = expXls.createHeadStyle(wb); //1 HSSFRow hfRow = sheet.createRow(0); hfRow.setHeightInPoints(25); for (int i = 0, n = rsCnt; i < n; i++) { sfCell = hfRow.createCell(i); sheet.setColumnWidth(i, 4000);//? } sfCell = hfRow.getCell(0); sheet.setColumnWidth(0, 448); // //?? CellRangeAddress range = new CellRangeAddress(0, 0, 1, rsCnt - 1); sheet.addMergedRegion(range); //?? sfCell = hfRow.getCell(1); sfCell.setCellType(HSSFCell.CELL_TYPE_STRING); sfCell.setCellValue(fileTitle); sfCell.setCellStyle(titleStyle); // hfRow = sheet.createRow(1); for (int i = 0, n = rsCnt; i < n; i++) { sfCell = hfRow.createCell(i); if (i == 0) continue; String colname = titles[i - 1]; sfCell.setCellType(HSSFCell.CELL_TYPE_STRING); sfCell.setCellValue(colname); sfCell.setCellStyle(headerStyle); } return sheet; }
From source file:org.jxstar.report.studio.ExportXlsBO.java
/** * // w w w.j a v a 2 s . c om * @param title -- * @param lsCol -- * @param sheet -- * @return */ private HSSFSheet createTitleArea(String title, List<Map<String, String>> lsCol, HSSFSheet sheet) { HSSFCell sfCell = null; int rsCnt = lsCol.size() + 1; // HSSFRow hfRow = sheet.createRow(0); hfRow.setHeightInPoints(10); for (int i = 0, n = rsCnt; i < n; i++) { sfCell = hfRow.createCell(i); sheet.setColumnWidth(i, 4000); } sfCell = hfRow.getCell(0); sheet.setColumnWidth(0, 448); // //? hfRow = sheet.createRow(1); hfRow.setHeightInPoints(25); for (int i = 0, n = rsCnt; i < n; i++) { sfCell = hfRow.createCell(i); } //???6? int posi = rsCnt / 2; int fromCell = (((posi - 2) < 0) ? 0 : (posi - 2)); int toCell = (((rsCnt - posi) < 0) ? 0 : (rsCnt - posi + 2)); CellRangeAddress range = new CellRangeAddress(1, 1, fromCell, toCell); sheet.addMergedRegion(range); HSSFWorkbook wb = sheet.getWorkbook(); //?? sfCell = hfRow.getCell(fromCell); sfCell.setCellType(HSSFCell.CELL_TYPE_STRING); sfCell.setCellValue(title); sfCell.setCellStyle(createTitleStyle(wb)); // hfRow = sheet.createRow(2); for (int i = 0, n = rsCnt; i < n; i++) { sfCell = hfRow.createCell(i); if (i != 0) { String colname = lsCol.get(i - 1).get("col_name"); sfCell.setCellType(HSSFCell.CELL_TYPE_STRING); sfCell.setCellValue(colname); sfCell.setCellStyle(createHeadStyle(wb)); } } return sheet; }
From source file:org.kurator.validation.actors.io.AnalysisSpreadsheetBuilder.java
License:Open Source License
private void initFirstSheet(long count) { StringBuffer stringBuffer = new StringBuffer(); try {/*w w w .j av a2s.c o m*/ BufferedReader bufferedReader = new BufferedReader( new InputStreamReader(this.getClass().getResourceAsStream("/analysis.txt"))); String line = null; while ((line = bufferedReader.readLine()) != null) { stringBuffer.append(line).append("\n"); } stringBuffer.append("\nTotal record count: " + count + " occurrence records."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } // TODO: Add list of sources to first page HSSFSheet sheet = wb.createSheet("Description"); sheet.setColumnWidth(0, 18000); HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); CellStyle style = wb.createCellStyle(); style.setWrapText(true); cell.setCellStyle(style); cell.setCellValue(stringBuffer.toString()); }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function excelTongHopChiTiet/* w w w . j a v a2 s.co m*/ * Version: 1.0 * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelTongHopChiTiet(ResourceRequest req, ResourceResponse res) { try { String id_donvi = req.getParameter("id_donvi"); String id_loaihoso = req.getParameter("id_loaihoso"); String thang = req.getParameter("thang"); String nam = req.getParameter("nam"); CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); Set<Long> setIDNhomThuTuc = new HashSet<Long>(0); List<ReportTongHopChiTiet> listTongHopChiTiet = ReportTongHopChiTietLocalServiceUtil .getListReportTongHopChiTietGroupByIDThuTuc(id_donvi, id_loaihoso, thang, nam); if (id_donvi != null && !id_donvi.equals("0")) { coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(id_donvi)); } for (int i = 0; i < listTongHopChiTiet.size(); i++) { setIDNhomThuTuc.add(listTongHopChiTiet.get(i).getNHOMTHUTUCHANHCHINHID()); } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Bao_cao_tong_hop_chi_tiet"); // define a cell style HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 5000); HSSFFont ngaythangFont = (HSSFFont) workbook.createFont(); ngaythangFont.setItalic(true); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 5000); titleFont.setFontHeightInPoints((short) 15); HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle(); styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellHeader.setFont(tableHeaderFont); styleCellHeader.setWrapText(true); HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle(); styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNgaythang.setFont(ngaythangFont); styleCellNgaythang.setWrapText(true); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); styleCellRight.setFont(tableHeaderFont); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle(); styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNhomTTHC.setFont(tableHeaderFont); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // end style //header bao cao int rowNum = 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowUBND = sheet.createRow(rowNum); rowUBND.createCell(0).setCellValue(""); rowUBND.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 14)); rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn")); rowUBND.getCell(2).setCellStyle(styleCellHeader); rowNum++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowTenDonVi = sheet.createRow(rowNum); rowTenDonVi.createCell(0).setCellValue(coQuanQuanLy.getTen()); rowTenDonVi.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 14)); rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp")); rowTenDonVi.getCell(2).setCellStyle(styleCellHeader); rowNum++; String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaotonghop.tieude"); String coquan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.thutuc"); String tong = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tong"); String tiepnhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tiepnhan"); String kytruocchuyenqua = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.kytruocchuyenqua"); String hosodagiaiquyet = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosodagiaiquyet"); String hosodanggiaiquyet = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosodanggiaiquyet"); String cdruthoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.cdruthoso"); String hosotralai = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosotralai"); String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.ghichu"); String sohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.sohoso"); String som = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.som"); String dung = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.dung"); String tre = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tre"); String chuadenhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.chuadenhan"); String daquahan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.daquahan"); String[] header = { "STT", coquan, tong, tiepnhan, kytruocchuyenqua, sohoso, som, dung, tre, sohoso, chuadenhan, daquahan, cdruthoso, hosotralai, ghichu }; rowNum++; int[] width = { 1500, 10000, 2000, 2000, 5000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 3000, 3000, 3000 }; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 14)); HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styleTitle); titleCell.setCellValue(tieude); rowNum += 2; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 6)); rowNum = rowNum + 2; HSSFRow headerRow1 = (HSSFRow) sheet.createRow(rowNum); HSSFRow headerRow2 = (HSSFRow) sheet.createRow(rowNum + 1); sheet.addMergedRegion(new Region(rowNum, (short) 5, rowNum, (short) 8)); sheet.addMergedRegion(new Region(rowNum, (short) 9, rowNum, (short) 11)); for (int i = 0; i < header.length; i++) { if (i < 5 || i > 11) { sheet.addMergedRegion(new Region(rowNum, (short) i, rowNum + 1, (short) i)); } HSSFCell cell = headerRow1.createCell(i); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); HSSFCell cell2 = headerRow2.createCell(i); cell2.setCellStyle(styleHeader); cell2.setCellValue(header[i]); if (i == 5) { cell.setCellValue(hosodagiaiquyet); } if (i == 9) { cell.setCellValue(hosodanggiaiquyet); } sheet.setColumnWidth(i, width[i]); } rowNum++; //end header bao cao //noi dung bao cao rowNum++; int stt = 0; int tongNhan = 0; int tongTiepNhan = 0; int tongTonDau = 0; int tongDaGiaiQuyet = 0; int tongSom = 0; int tongDung = 0; int tongTre = 0; int tongDangGiaiQuyet = 0; int tongChuaDenHan = 0; int tongDaQuaHan = 0; int tongRutHoSo = 0; int tongKhongHopLe = 0; int tongTonCuoi = 0; for (Long s : setIDNhomThuTuc) { stt++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 14)); HSSFRow rowNhomTTHC = (HSSFRow) sheet.createRow(rowNum); HSSFCell cellNhomTTHC = rowNhomTTHC.createCell(0); cellNhomTTHC.setCellValue( stt + ". " + NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinh(s).getTen()); cellNhomTTHC.setCellStyle(styleCellNhomTTHC); for (int i = 1; i < 15; i++) { rowNhomTTHC.createCell(i).setCellStyle(styleCellCenter); } rowNum++; for (int a = 0; a < listTongHopChiTiet.size(); a++) { // khai bao dong va cac cell if (listTongHopChiTiet.get(a).getNHOMTHUTUCHANHCHINHID() == s) { tongNhan += listTongHopChiTiet.get(a).getTONGNHAN(); tongTiepNhan += listTongHopChiTiet.get(a).getTIEPNHAN(); tongTonDau += listTongHopChiTiet.get(a).getTONDAU(); tongDaGiaiQuyet += listTongHopChiTiet.get(a).getTONGDAGIAIQUYET(); tongSom += listTongHopChiTiet.get(a).getSOM(); tongDung += listTongHopChiTiet.get(a).getDUNG(); tongTre += listTongHopChiTiet.get(a).getTRE(); tongDangGiaiQuyet += listTongHopChiTiet.get(a).getTONGDANGGIAIQUYET(); tongChuaDenHan += listTongHopChiTiet.get(a).getCHUADENHAN(); tongDaQuaHan += listTongHopChiTiet.get(a).getQUAHAN(); tongRutHoSo += listTongHopChiTiet.get(a).getRUTHOSO(); tongKhongHopLe += listTongHopChiTiet.get(a).getKHONGHOPLE(); tongTonCuoi += listTongHopChiTiet.get(a).getTONCUOI(); sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); HSSFCell cell13 = row.createCell(13); HSSFCell cell14 = row.createCell(14); // set style cho cac cell cell.setCellStyle(styleCellLeft); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); cell13.setCellStyle(styleCellCenter); cell14.setCellStyle(styleCellCenter); // set gia tri cho cac cell cell.setCellValue(ThuTucHanhChinhLocalServiceUtil .getThuTucHanhChinh(listTongHopChiTiet.get(a).getTHUTUCHANHCHINHID()).getTen()); //cell1.setCellValue(""); cell2.setCellValue(listTongHopChiTiet.get(a).getTONGNHAN()); cell3.setCellValue(listTongHopChiTiet.get(a).getTIEPNHAN()); cell4.setCellValue(listTongHopChiTiet.get(a).getTONDAU()); cell5.setCellValue(listTongHopChiTiet.get(a).getTONGDAGIAIQUYET()); cell6.setCellValue(listTongHopChiTiet.get(a).getSOM()); cell7.setCellValue(listTongHopChiTiet.get(a).getDUNG()); cell8.setCellValue(listTongHopChiTiet.get(a).getTRE()); cell9.setCellValue(listTongHopChiTiet.get(a).getTONGDANGGIAIQUYET()); cell10.setCellValue(listTongHopChiTiet.get(a).getCHUADENHAN()); cell11.setCellValue(listTongHopChiTiet.get(a).getQUAHAN()); cell12.setCellValue(listTongHopChiTiet.get(a).getRUTHOSO()); cell13.setCellValue(listTongHopChiTiet.get(a).getKHONGHOPLE()); cell14.setCellValue(listTongHopChiTiet.get(a).getTONCUOI()); rowNum++; } } } //row tong sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); HSSFCell cell13 = row.createCell(13); HSSFCell cell14 = row.createCell(14); // set style cho cac cell cell.setCellStyle(styleHeader); cell1.setCellStyle(styleHeader); cell2.setCellStyle(styleHeader); cell3.setCellStyle(styleHeader); cell4.setCellStyle(styleHeader); cell5.setCellStyle(styleHeader); cell6.setCellStyle(styleHeader); cell7.setCellStyle(styleHeader); cell8.setCellStyle(styleHeader); cell9.setCellStyle(styleHeader); cell10.setCellStyle(styleHeader); cell11.setCellStyle(styleHeader); cell12.setCellStyle(styleHeader); cell13.setCellStyle(styleHeader); cell14.setCellStyle(styleHeader); // set gia tri cho cac cell cell.setCellValue("Tng"); //cell1.setCellValue(""); cell2.setCellValue(tongNhan); cell3.setCellValue(tongTiepNhan); cell4.setCellValue(tongTonDau); cell5.setCellValue(tongDaGiaiQuyet); cell6.setCellValue(tongSom); cell7.setCellValue(tongDung); cell8.setCellValue(tongTre); cell9.setCellValue(tongDangGiaiQuyet); cell10.setCellValue(tongChuaDenHan); cell11.setCellValue(tongDaQuaHan); cell12.setCellValue(tongRutHoSo); cell13.setCellValue(tongKhongHopLe); cell14.setCellValue(tongTonCuoi); //ket thuc row tong //ket thuc noi dung bao cao res.setContentType("application/vnd.ms-excel"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaotonghop.xls"); OutputStream out = res.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function checkHosoByNHOMTTHCID * Version: 1.0/*www.j a v a2 s . c o m*/ * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelTongHopNam(ResourceRequest req, ResourceResponse res) { try { String id_donvi = req.getParameter("id_donvi"); String id_linhvuc = req.getParameter("id_linhvuc"); String nam = req.getParameter("nam"); CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); Set<Long> setIDNhomThuTuc = new HashSet<Long>(0); List<ReportTongHop> listTongHopChiTiet = ActionUtil.getListReportTongHop(id_donvi, id_linhvuc, nam); if (id_donvi != null && !id_donvi.equals("0")) { coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(id_donvi)); } for (int i = 0; i < listTongHopChiTiet.size(); i++) { setIDNhomThuTuc.add(listTongHopChiTiet.get(i).getNHOMTHUTUCHANHCHINHID()); } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Bao_cao_tong_hop_theo_nam"); // define a cell style HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 5000); HSSFFont ngaythangFont = (HSSFFont) workbook.createFont(); ngaythangFont.setItalic(true); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 5000); titleFont.setFontHeightInPoints((short) 15); HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle(); styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellHeader.setFont(tableHeaderFont); styleCellHeader.setWrapText(true); HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle(); styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNgaythang.setFont(ngaythangFont); styleCellNgaythang.setWrapText(true); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); styleCellRight.setFont(tableHeaderFont); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle(); styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNhomTTHC.setFont(tableHeaderFont); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // end style //header bao cao int rowNum = 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowUBND = sheet.createRow(rowNum); rowUBND.createCell(0).setCellValue(""); rowUBND.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 14)); rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn")); rowUBND.getCell(2).setCellStyle(styleCellHeader); rowNum++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowTenDonVi = sheet.createRow(rowNum); rowTenDonVi.createCell(0).setCellValue(coQuanQuanLy.getTen()); rowTenDonVi.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 14)); rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp")); rowTenDonVi.getCell(2).setCellStyle(styleCellHeader); rowNum++; String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaotonghop.tieude"); String coquan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.thang"); String tong = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tong"); String tiepnhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tiepnhan"); String kytruocchuyenqua = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.kytruocchuyenqua"); String hosodagiaiquyet = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosodagiaiquyet"); String hosodanggiaiquyet = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosodanggiaiquyet"); String cdruthoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.cdruthoso"); String hosotralai = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosotralai"); String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.ghichu"); String sohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.sohoso"); String som = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.som"); String dung = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.dung"); String tre = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tre"); String chuadenhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.chuadenhan"); String daquahan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.daquahan"); String[] header = { "STT", coquan, tong, tiepnhan, kytruocchuyenqua, sohoso, som, dung, tre, sohoso, chuadenhan, daquahan, cdruthoso, hosotralai, ghichu }; rowNum++; int[] width = { 1500, 10000, 2000, 2000, 5000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 3000, 3000, 3000 }; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 14)); HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styleTitle); titleCell.setCellValue(tieude); rowNum += 2; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 6)); rowNum = rowNum + 2; HSSFRow headerRow1 = (HSSFRow) sheet.createRow(rowNum); HSSFRow headerRow2 = (HSSFRow) sheet.createRow(rowNum + 1); sheet.addMergedRegion(new Region(rowNum, (short) 5, rowNum, (short) 8)); sheet.addMergedRegion(new Region(rowNum, (short) 9, rowNum, (short) 11)); for (int i = 0; i < header.length; i++) { if (i < 5 || i > 11) { sheet.addMergedRegion(new Region(rowNum, (short) i, rowNum + 1, (short) i)); } HSSFCell cell = headerRow1.createCell(i); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); HSSFCell cell2 = headerRow2.createCell(i); cell2.setCellStyle(styleHeader); cell2.setCellValue(header[i]); if (i == 5) { cell.setCellValue(hosodagiaiquyet); } if (i == 9) { cell.setCellValue(hosodanggiaiquyet); } sheet.setColumnWidth(i, width[i]); } rowNum++; //end header bao cao //noi dung bao cao rowNum++; int stt = 0; int tongNhan = 0; int tongTiepNhan = 0; int tongTonDau = 0; int tongDaGiaiQuyet = 0; int tongSom = 0; int tongDung = 0; int tongTre = 0; int tongDangGiaiQuyet = 0; int tongChuaDenHan = 0; int tongDaQuaHan = 0; int tongRutHoSo = 0; int tongKhongHopLe = 0; int tongTonCuoi = 0; for (Long s : setIDNhomThuTuc) { stt++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 14)); HSSFRow rowNhomTTHC = (HSSFRow) sheet.createRow(rowNum); HSSFCell cellNhomTTHC = rowNhomTTHC.createCell(0); cellNhomTTHC.setCellValue( stt + ". " + NhomThuTucHanhChinhLocalServiceUtil.getNhomThuTucHanhChinh(s).getTen()); cellNhomTTHC.setCellStyle(styleCellNhomTTHC); for (int i = 1; i < 15; i++) { rowNhomTTHC.createCell(i).setCellStyle(styleCellCenter); } rowNum++; for (int a = 0; a < listTongHopChiTiet.size(); a++) { // khai bao dong va cac cell if (listTongHopChiTiet.get(a).getNHOMTHUTUCHANHCHINHID() == s) { tongNhan += listTongHopChiTiet.get(a).getTONGNHAN(); tongTiepNhan += listTongHopChiTiet.get(a).getTIEPNHAN(); tongTonDau += listTongHopChiTiet.get(a).getTONDAU(); tongDaGiaiQuyet += listTongHopChiTiet.get(a).getTONGDAGIAIQUYET(); tongSom += listTongHopChiTiet.get(a).getSOM(); tongDung += listTongHopChiTiet.get(a).getDUNG(); tongTre += listTongHopChiTiet.get(a).getTRE(); tongDangGiaiQuyet += listTongHopChiTiet.get(a).getTONGDANGGIAIQUYET(); tongChuaDenHan += listTongHopChiTiet.get(a).getCHUADENHAN(); tongDaQuaHan += listTongHopChiTiet.get(a).getQUAHAN(); tongRutHoSo += listTongHopChiTiet.get(a).getRUTHOSO(); tongKhongHopLe += listTongHopChiTiet.get(a).getKHONGHOPLE(); tongTonCuoi += listTongHopChiTiet.get(a).getTONCUOI(); sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); HSSFCell cell13 = row.createCell(13); HSSFCell cell14 = row.createCell(14); // set style cho cac cell cell.setCellStyle(styleCellLeft); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); cell13.setCellStyle(styleCellCenter); cell14.setCellStyle(styleCellCenter); // set gia tri cho cac cell cell.setCellValue("Thng " + listTongHopChiTiet.get(a).getTHANG()); //cell1.setCellValue(""); cell2.setCellValue(listTongHopChiTiet.get(a).getTONGNHAN()); cell3.setCellValue(listTongHopChiTiet.get(a).getTIEPNHAN()); cell4.setCellValue(listTongHopChiTiet.get(a).getTONDAU()); cell5.setCellValue(listTongHopChiTiet.get(a).getTONGDAGIAIQUYET()); cell6.setCellValue(listTongHopChiTiet.get(a).getSOM()); cell7.setCellValue(listTongHopChiTiet.get(a).getDUNG()); cell8.setCellValue(listTongHopChiTiet.get(a).getTRE()); cell9.setCellValue(listTongHopChiTiet.get(a).getTONGDANGGIAIQUYET()); cell10.setCellValue(listTongHopChiTiet.get(a).getCHUADENHAN()); cell11.setCellValue(listTongHopChiTiet.get(a).getQUAHAN()); cell12.setCellValue(listTongHopChiTiet.get(a).getRUTHOSO()); cell13.setCellValue(listTongHopChiTiet.get(a).getKHONGHOPLE()); cell14.setCellValue(listTongHopChiTiet.get(a).getTONCUOI()); rowNum++; } } } //row tong sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); HSSFCell cell13 = row.createCell(13); HSSFCell cell14 = row.createCell(14); // set style cho cac cell cell.setCellStyle(styleHeader); cell1.setCellStyle(styleHeader); cell2.setCellStyle(styleHeader); cell3.setCellStyle(styleHeader); cell4.setCellStyle(styleHeader); cell5.setCellStyle(styleHeader); cell6.setCellStyle(styleHeader); cell7.setCellStyle(styleHeader); cell8.setCellStyle(styleHeader); cell9.setCellStyle(styleHeader); cell10.setCellStyle(styleHeader); cell11.setCellStyle(styleHeader); cell12.setCellStyle(styleHeader); cell13.setCellStyle(styleHeader); cell14.setCellStyle(styleHeader); // set gia tri cho cac cell cell.setCellValue("Tng"); //cell1.setCellValue(""); cell2.setCellValue(tongNhan); cell3.setCellValue(tongTiepNhan); cell4.setCellValue(tongTonDau); cell5.setCellValue(tongDaGiaiQuyet); cell6.setCellValue(tongSom); cell7.setCellValue(tongDung); cell8.setCellValue(tongTre); cell9.setCellValue(tongDangGiaiQuyet); cell10.setCellValue(tongChuaDenHan); cell11.setCellValue(tongDaQuaHan); cell12.setCellValue(tongRutHoSo); cell13.setCellValue(tongKhongHopLe); cell14.setCellValue(tongTonCuoi); //ket thuc row tong //ket thuc noi dung bao cao res.setContentType("application/vnd.ms-excel"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=Baocaotonghop.xls"); OutputStream out = res.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function checkHosoByNHOMTTHCID * Version: 1.0//ww w . j a v a2 s . c o m * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelTonghopTongquat(ResourceRequest req, ResourceResponse res) { try { String thang = req.getParameter("thang"); String nam = req.getParameter("nam"); String id_donvi = req.getParameter("id_donvi"); String id_linhvuc = req.getParameter("id_linhvuc"); String coQuanQuanLyId = req.getParameter("id_coquanquanly"); CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); if (coQuanQuanLyId != null && !coQuanQuanLyId.equals("0")) { coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId)); } List<ReportTongHop> listtonghop = ReportTongHopLocalServiceUtil .getListReportTongHopGroupByIDNhomThuTuc(id_donvi, id_linhvuc, thang, nam); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Thongke"); // define a cell style HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 5000); HSSFFont ngaythangFont = (HSSFFont) workbook.createFont(); ngaythangFont.setItalic(true); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 5000); titleFont.setFontHeightInPoints((short) 15); HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle(); styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellHeader.setFont(tableHeaderFont); styleCellHeader.setWrapText(true); HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle(); styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNgaythang.setFont(ngaythangFont); styleCellNgaythang.setWrapText(true); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); styleCellRight.setFont(tableHeaderFont); HSSFCellStyle styleCellRightNobold = (HSSFCellStyle) workbook.createCellStyle(); styleCellRightNobold.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRightNobold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRightNobold.setWrapText(true); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle(); styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNhomTTHC.setFont(tableHeaderFont); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // end style int rowNum = 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowUBND = sheet.createRow(rowNum); rowUBND.createCell(0).setCellValue(""); rowUBND.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 14)); rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn")); rowUBND.getCell(2).setCellStyle(styleCellHeader); rowNum++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowTenDonVi = sheet.createRow(rowNum); rowTenDonVi.createCell(0).setCellValue(coQuanQuanLy.getTen()); rowTenDonVi.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 14)); rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp")); rowTenDonVi.getCell(2).setCellStyle(styleCellHeader); rowNum++; String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaotonghop.tieude"); String nhomtthc = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaotonghop.nhomtthc"); String tong = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tong"); String tiepnhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tiepnhan"); String kytruocchuyenqua = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.kytruocchuyenqua"); String hosodagiaiquyet = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosodagiaiquyet"); String hosodanggiaiquyet = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosodanggiaiquyet"); String cdruthoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.cdruthoso"); String hosotralai = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosotralai"); String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.ghichu"); String sohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.sohoso"); String som = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.som"); String dung = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.dung"); String tre = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tre"); String chuadenhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.chuadenhan"); String daquahan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.daquahan"); String[] header = { "STT", nhomtthc, tong, tiepnhan, kytruocchuyenqua, sohoso, som, dung, tre, sohoso, chuadenhan, daquahan, cdruthoso, hosotralai, ghichu }; rowNum++; int[] width = { 1500, 10000, 2000, 2000, 3000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 3000, 3000, 3000 }; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 14)); HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styleTitle); titleCell.setCellValue(tieude); rowNum += 2; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 6)); rowNum = rowNum + 2; HSSFRow headerRow1 = (HSSFRow) sheet.createRow(rowNum); HSSFRow headerRow2 = (HSSFRow) sheet.createRow(rowNum + 1); sheet.addMergedRegion(new Region(rowNum, (short) 5, rowNum, (short) 8)); sheet.addMergedRegion(new Region(rowNum, (short) 9, rowNum, (short) 11)); for (int i = 0; i < header.length; i++) { if (i < 5 || i > 11) { sheet.addMergedRegion(new Region(rowNum, (short) i, rowNum + 1, (short) i)); } HSSFCell cell = headerRow1.createCell(i); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); HSSFCell cell2 = headerRow2.createCell(i); cell2.setCellStyle(styleHeader); cell2.setCellValue(header[i]); if (i == 5) { cell.setCellValue(hosodagiaiquyet); } if (i == 9) { cell.setCellValue(hosodanggiaiquyet); } sheet.setColumnWidth(i, width[i]); } rowNum += 2; int tongNhan = 0; int tongTiepNhan = 0; int tongTonDau = 0; int tongDaGiaiQuyet = 0; int tongSom = 0; int tongDung = 0; int tongTre = 0; int tongDangGiaiQuyet = 0; int tongChuaDenHan = 0; int tongDaQuaHan = 0; int tongRutHoSo = 0; int tongKhongHopLe = 0; int tongTonCuoi = 0; int stt = 1; for (int a = 0; a < listtonghop.size(); a++) { tongNhan += listtonghop.get(a).getTONGNHAN(); tongTiepNhan += listtonghop.get(a).getTIEPNHAN(); tongTonDau += listtonghop.get(a).getTONDAU(); tongDaGiaiQuyet += listtonghop.get(a).getTONGDAGIAIQUYET(); tongSom += listtonghop.get(a).getSOM(); tongDung += listtonghop.get(a).getDUNG(); tongTre += listtonghop.get(a).getTRE(); tongDangGiaiQuyet += listtonghop.get(a).getTONGDANGGIAIQUYET(); tongChuaDenHan += listtonghop.get(a).getCHUADENHAN(); tongDaQuaHan += listtonghop.get(a).getQUAHAN(); tongRutHoSo += listtonghop.get(a).getRUTHOSO(); tongKhongHopLe += listtonghop.get(a).getKHONGHOPLE(); tongTonCuoi += listtonghop.get(a).getTONCUOI(); // khai bao dong va cac cell HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); HSSFCell cell13 = row.createCell(13); HSSFCell cell14 = row.createCell(14); // set style cho cac cell cell.setCellStyle(styleCellLeft); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); cell13.setCellStyle(styleCellCenter); cell14.setCellStyle(styleCellCenter); // set gia tri cho cac cell cell.setCellValue(stt); //cell1.setCellValue(""); cell1.setCellValue(NhomThuTucHanhChinhLocalServiceUtil .getNhomThuTucHanhChinh(listtonghop.get(a).getNHOMTHUTUCHANHCHINHID()).getTen()); cell2.setCellValue(listtonghop.get(a).getTONGNHAN()); cell3.setCellValue(listtonghop.get(a).getTIEPNHAN()); cell4.setCellValue(listtonghop.get(a).getTONDAU()); cell5.setCellValue(listtonghop.get(a).getTONGDAGIAIQUYET()); cell6.setCellValue(listtonghop.get(a).getSOM()); cell7.setCellValue(listtonghop.get(a).getDUNG()); cell8.setCellValue(listtonghop.get(a).getTRE()); cell9.setCellValue(listtonghop.get(a).getTONGDANGGIAIQUYET()); cell10.setCellValue(listtonghop.get(a).getCHUADENHAN()); cell11.setCellValue(listtonghop.get(a).getQUAHAN()); cell12.setCellValue(listtonghop.get(a).getRUTHOSO()); cell13.setCellValue(listtonghop.get(a).getKHONGHOPLE()); cell14.setCellValue(listtonghop.get(a).getTONCUOI()); rowNum++; stt++; } //row tong sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); HSSFCell cell13 = row.createCell(13); HSSFCell cell14 = row.createCell(14); // set style cho cac cell cell.setCellStyle(styleHeader); cell1.setCellStyle(styleHeader); cell2.setCellStyle(styleHeader); cell3.setCellStyle(styleHeader); cell4.setCellStyle(styleHeader); cell5.setCellStyle(styleHeader); cell6.setCellStyle(styleHeader); cell7.setCellStyle(styleHeader); cell8.setCellStyle(styleHeader); cell9.setCellStyle(styleHeader); cell10.setCellStyle(styleHeader); cell11.setCellStyle(styleHeader); cell12.setCellStyle(styleHeader); cell13.setCellStyle(styleHeader); cell14.setCellStyle(styleHeader); // set gia tri cho cac cell cell.setCellValue("Tng"); //cell1.setCellValue(""); cell2.setCellValue(tongNhan); cell3.setCellValue(tongTiepNhan); cell4.setCellValue(tongTonDau); cell5.setCellValue(tongDaGiaiQuyet); cell6.setCellValue(tongSom); cell7.setCellValue(tongDung); cell8.setCellValue(tongTre); cell9.setCellValue(tongDangGiaiQuyet); cell10.setCellValue(tongChuaDenHan); cell11.setCellValue(tongDaQuaHan); cell12.setCellValue(tongRutHoSo); cell13.setCellValue(tongKhongHopLe); cell14.setCellValue(tongTonCuoi); //ket thuc row tong //ket thuc noi dung bao cao res.setContentType("application/vnd.ms-excel"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename='Baocaotonghop.xls'"); OutputStream out = res.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { } }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function checkHosoByNHOMTTHCID * Version: 1.0//from w w w. ja v a 2 s. c o m * * History: * DATE AUTHOR DESCRIPTION * ------------------------------------------------- * 3-March-2013 Nam Dinh Create new * @param req * @param res */ public static void excelThongke(ResourceRequest req, ResourceResponse res) { try { String thang = req.getParameter("thang"); String nam = req.getParameter("nam"); String coQuanQuanLyId = req.getParameter("id_coquanquanly"); // CoQuanQuanLy coQuanQuanLy = new CoQuanQuanLyImpl(); // if(coQuanQuanLyId!=null && !coQuanQuanLyId.equals("0")){ // coQuanQuanLy = CoQuanQuanLyLocalServiceUtil.getCoQuanQuanLy(Long.valueOf(coQuanQuanLyId)); // } List<ReportThongKe> listthongke = ReportThongKeLocalServiceUtil .getListReportThongKeGroupByIDCoQuan(coQuanQuanLyId, thang, nam); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Thongke"); // define a cell style HSSFFont tableHeaderFont = (HSSFFont) workbook.createFont(); tableHeaderFont.setBoldweight((short) 5000); HSSFFont ngaythangFont = (HSSFFont) workbook.createFont(); ngaythangFont.setItalic(true); HSSFFont titleFont = (HSSFFont) workbook.createFont(); titleFont.setBoldweight((short) 5000); titleFont.setFontHeightInPoints((short) 15); HSSFCellStyle styleCellHeader = (HSSFCellStyle) workbook.createCellStyle(); styleCellHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellHeader.setFont(tableHeaderFont); styleCellHeader.setWrapText(true); HSSFCellStyle styleCellNgaythang = (HSSFCellStyle) workbook.createCellStyle(); styleCellNgaythang.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellNgaythang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNgaythang.setFont(ngaythangFont); styleCellNgaythang.setWrapText(true); HSSFCellStyle styleCellCenter = (HSSFCellStyle) workbook.createCellStyle(); styleCellCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCellCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellCenter.setWrapText(true); HSSFCellStyle styleCellLeft = (HSSFCellStyle) workbook.createCellStyle(); styleCellLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellLeft.setWrapText(true); HSSFCellStyle styleCellRight = (HSSFCellStyle) workbook.createCellStyle(); styleCellRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRight.setWrapText(true); styleCellRight.setFont(tableHeaderFont); HSSFCellStyle styleCellRightNobold = (HSSFCellStyle) workbook.createCellStyle(); styleCellRightNobold.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellRightNobold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCellRightNobold.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellRightNobold.setWrapText(true); HSSFCellStyle styleHeader = (HSSFCellStyle) workbook.createCellStyle(); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleHeader.setFont(tableHeaderFont); styleHeader.setWrapText(true); HSSFCellStyle styleCellNhomTTHC = (HSSFCellStyle) workbook.createCellStyle(); styleCellNhomTTHC.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCellNhomTTHC.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleCellNhomTTHC.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCellNhomTTHC.setFont(tableHeaderFont); HSSFCellStyle styleTitle = (HSSFCellStyle) workbook.createCellStyle(); styleTitle.setFont(titleFont); styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // end style int rowNum = 1; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowUBND = sheet.createRow(rowNum); rowUBND.createCell(0).setCellValue(""); rowUBND.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 14)); rowUBND.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.chxhcnvn")); rowUBND.getCell(2).setCellStyle(styleCellHeader); rowNum++; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow rowTenDonVi = sheet.createRow(rowNum); rowTenDonVi.createCell(0).setCellValue(""); rowTenDonVi.getCell(0).setCellStyle(styleCellHeader); sheet.addMergedRegion(new Region(rowNum, (short) 2, rowNum, (short) 14)); rowTenDonVi.createCell(2).setCellValue(ActionUtil.getLanguage(req, "vn.dtt.cmon.report.excel.dltdhp")); rowTenDonVi.getCell(2).setCellStyle(styleCellHeader); rowNum++; String tieude = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tieude"); String coquan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.coquan"); String tong = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tong"); String tiepnhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tiepnhan"); String kytruocchuyenqua = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.kytruocchuyenqua"); String hosodagiaiquyet = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosodagiaiquyet"); String hosodanggiaiquyet = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosodanggiaiquyet"); String cdruthoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.cdruthoso"); String hosotralai = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.hosotralai"); String ghichu = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.ghichu"); String sohoso = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.sohoso"); String som = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.som"); String dung = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.dung"); String tre = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.tre"); String chuadenhan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.chuadenhan"); String daquahan = ActionUtil.getLanguage(req, "vn.dtt.cmon.report.baocaothongke.daquahan"); String[] header = { "STT", coquan, tong, tiepnhan, kytruocchuyenqua, sohoso, som, dung, tre, sohoso, chuadenhan, daquahan, cdruthoso, hosotralai, ghichu }; rowNum++; int[] width = { 1500, 10000, 2000, 2000, 3000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 3000, 3000, 3000 }; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum + 1, (short) 14)); HSSFRow titleRow = (HSSFRow) sheet.createRow(rowNum); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styleTitle); titleCell.setCellValue(tieude); rowNum += 2; sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 6)); rowNum = rowNum + 2; HSSFRow headerRow1 = (HSSFRow) sheet.createRow(rowNum); HSSFRow headerRow2 = (HSSFRow) sheet.createRow(rowNum + 1); sheet.addMergedRegion(new Region(rowNum, (short) 5, rowNum, (short) 8)); sheet.addMergedRegion(new Region(rowNum, (short) 9, rowNum, (short) 11)); for (int i = 0; i < header.length; i++) { if (i < 5 || i > 11) { sheet.addMergedRegion(new Region(rowNum, (short) i, rowNum + 1, (short) i)); } HSSFCell cell = headerRow1.createCell(i); cell.setCellStyle(styleHeader); cell.setCellValue(header[i]); HSSFCell cell2 = headerRow2.createCell(i); cell2.setCellStyle(styleHeader); cell2.setCellValue(header[i]); if (i == 5) { cell.setCellValue(hosodagiaiquyet); } if (i == 9) { cell.setCellValue(hosodanggiaiquyet); } sheet.setColumnWidth(i, width[i]); } rowNum += 2; long tongnhan = 0; long tongtiepnhan = 0; long tondau = 0; long dagiaiquyet = 0; long tongsom = 0; long tongdung = 0; long tongtre = 0; long danggiaiquyet = 0; long tongchuadenhan = 0; long quahan = 0; long ruthoso = 0; long khonghople = 0; long toncuoi = 0; int stt = 1; for (int a = 0; a < listthongke.size(); a++) { ReportThongKe thongke = listthongke.get(a); tongnhan += thongke.getTONGNHAN(); tongtiepnhan += thongke.getTIEPNHAN(); tondau += thongke.getTONDAU(); dagiaiquyet += thongke.getTONGDAGIAIQUYET(); tongsom += thongke.getSOM(); tongdung += thongke.getDUNG(); tongtre += thongke.getTRE(); danggiaiquyet += thongke.getTONGDANGGIAIQUYET(); tongchuadenhan += thongke.getCHUADENHAN(); quahan += thongke.getQUAHAN(); ruthoso += thongke.getRUTHOSO(); khonghople += thongke.getKHONGHOPLE(); toncuoi += thongke.getTONCUOI(); // khai bao dong va cac cell HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); HSSFCell cell13 = row.createCell(13); HSSFCell cell14 = row.createCell(14); // set style cho cac cell cell.setCellStyle(styleCellLeft); cell1.setCellStyle(styleCellLeft); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); cell13.setCellStyle(styleCellCenter); cell14.setCellStyle(styleCellCenter); // set gia tri cho cac cell cell.setCellValue(stt); //cell1.setCellValue(""); cell1.setCellValue(CoQuanQuanLyLocalServiceUtil .getCoQuanQuanLy(listthongke.get(a).getCOQUANQUANLYID()).getTen()); cell2.setCellValue(listthongke.get(a).getTONGNHAN()); cell3.setCellValue(listthongke.get(a).getTIEPNHAN()); cell4.setCellValue(listthongke.get(a).getTONDAU()); cell5.setCellValue(listthongke.get(a).getTONGDAGIAIQUYET()); cell6.setCellValue(listthongke.get(a).getSOM()); cell7.setCellValue(listthongke.get(a).getDUNG()); cell8.setCellValue(listthongke.get(a).getTRE()); cell9.setCellValue(listthongke.get(a).getTONGDANGGIAIQUYET()); cell10.setCellValue(listthongke.get(a).getCHUADENHAN()); cell11.setCellValue(listthongke.get(a).getQUAHAN()); cell12.setCellValue(listthongke.get(a).getRUTHOSO()); cell13.setCellValue(listthongke.get(a).getKHONGHOPLE()); cell14.setCellValue(listthongke.get(a).getTONCUOI()); rowNum++; stt++; } sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 1)); HSSFRow row = (HSSFRow) sheet.createRow(rowNum); HSSFCell cell = row.createCell(0); HSSFCell cell1 = row.createCell(1); HSSFCell cell2 = row.createCell(2); HSSFCell cell3 = row.createCell(3); HSSFCell cell4 = row.createCell(4); HSSFCell cell5 = row.createCell(5); HSSFCell cell6 = row.createCell(6); HSSFCell cell7 = row.createCell(7); HSSFCell cell8 = row.createCell(8); HSSFCell cell9 = row.createCell(9); HSSFCell cell10 = row.createCell(10); HSSFCell cell11 = row.createCell(11); HSSFCell cell12 = row.createCell(12); HSSFCell cell13 = row.createCell(13); HSSFCell cell14 = row.createCell(14); // set style cho cac cell //cell.setCellStyle(styleCellCenter); cell.setCellStyle(styleCellCenter); cell1.setCellStyle(styleCellCenter); cell2.setCellStyle(styleCellCenter); cell3.setCellStyle(styleCellCenter); cell4.setCellStyle(styleCellCenter); cell5.setCellStyle(styleCellCenter); cell6.setCellStyle(styleCellCenter); cell7.setCellStyle(styleCellCenter); cell8.setCellStyle(styleCellCenter); cell9.setCellStyle(styleCellCenter); cell10.setCellStyle(styleCellCenter); cell11.setCellStyle(styleCellCenter); cell12.setCellStyle(styleCellCenter); cell13.setCellStyle(styleCellCenter); cell14.setCellStyle(styleCellCenter); // set gia tri cho cac cell //cell1.setCellValue(""); cell.setCellValue("Tng cng"); cell2.setCellValue(tongnhan); cell3.setCellValue(tongtiepnhan); cell4.setCellValue(tondau); cell5.setCellValue(dagiaiquyet); cell6.setCellValue(tongsom); cell7.setCellValue(tongdung); cell8.setCellValue(tongtre); cell9.setCellValue(danggiaiquyet); cell10.setCellValue(tongchuadenhan); cell11.setCellValue(quahan); cell12.setCellValue(ruthoso); cell13.setCellValue(khonghople); cell14.setCellValue(toncuoi); res.setContentType("application/vnd.ms-excel"); res.addProperty(HttpHeaders.CACHE_CONTROL, "max-age=3600, must-revalidate"); res.addProperty(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename='Baocaothongke.xls'"); OutputStream out = res.getPortletOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { } }