List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setAlignment
@Override public void setAlignment(HorizontalAlignment align)
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
public String printListVaccinationReport(String type, String timeFrom, String timeTo, Commune commune, District district, List<RegionVaccinationReportData> statistics) { String path = ""; String prefixFileName = ""; if (commune != null) prefixFileName = commune.getDistrict().getProvince().getProvinceId() + commune.getDistrict().getDistrictId() + commune.getCommuneId(); else/*from ww w . j a v a 2s . c o m*/ prefixFileName = district.getProvince().getProvinceId() + district.getDistrictId(); GraniteContext gc = GraniteContext.getCurrentInstance(); ServletContext sc = ((HttpGraniteContext) gc).getServletContext(); String reportDir = sc.getRealPath(config.getBaseReportDir()); long currentTime = System.currentTimeMillis(); String filePath = reportDir + "/" + prefixFileName + "_Report_" + currentTime; if (type.equalsIgnoreCase("pdf")) { JasperPrint reportPrint = createListVaccinationReportPrint(timeFrom, timeTo, commune, district, statistics); try { filePath += ".pdf"; JasperExportManager.exportReportToPdfFile(reportPrint, filePath); path = "/reports/" + prefixFileName + "_Report_" + currentTime + ".pdf"; log.debug("path to pdf report:" + path); } catch (Exception ex) { String connectMsg = "Could not create the report " + ex.getMessage() + " " + ex.getLocalizedMessage(); log.debug(connectMsg); } } else { filePath += ".xls"; POIFSFileSystem fs; String regionName = ""; String provinceName = ""; String districtName = ""; String communeName = ""; String timeData = ""; Short rId = 0; if (commune != null) { communeName = commune.getCommuneName(); provinceName = commune.getDistrict().getProvince().getProvinceName(); districtName = commune.getDistrict().getDistrictName(); rId = commune.getDistrict().getProvince().getRegionId(); } else if (district != null) { provinceName = district.getProvince().getProvinceName(); districtName = district.getDistrictName(); rId = district.getProvince().getRegionId(); } if (rId == 1) regionName = "Mi?n Bc"; else if (rId == 2) regionName = "Mi?n Trung"; else regionName = "Mi?n Nam"; if (timeFrom.equalsIgnoreCase(timeTo)) { timeData = timeTo; } else { timeData = timeFrom + " - " + timeTo; } try { fs = new POIFSFileSystem(new FileInputStream(reportDir + "/excel/TCMR_Report_Template.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs, true); HSSFSheet s = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; r = s.getRow(4); c = r.getCell(1); c.setCellValue(c.getStringCellValue() + " " + regionName.toUpperCase()); r = s.getRow(5); c = r.getCell(1); c.setCellValue(c.getStringCellValue() + " " + provinceName.toUpperCase()); r = s.getRow(6); c = r.getCell(1); c.setCellValue(c.getStringCellValue() + " " + districtName.toUpperCase()); r = s.getRow(7); c = r.getCell(1); c.setCellValue(c.getStringCellValue() + " " + communeName.toUpperCase()); r = s.getRow(4); c = r.getCell(15); c.setCellValue(timeData); HSSFCellStyle cs = wb.createCellStyle(); cs.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs.setBorderTop(HSSFCellStyle.BORDER_THIN); cs.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle cs1 = wb.createCellStyle(); cs1.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs1.setBorderTop(HSSFCellStyle.BORDER_THIN); cs1.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs1.setBorderRight(HSSFCellStyle.BORDER_THIN); cs1.setAlignment(HSSFCellStyle.ALIGN_CENTER); int rownum = 13; for (rownum = 13; rownum < statistics.size() + 11; rownum++) { if (rownum < statistics.size() + 10) copyRow(wb, s, rownum, rownum + 1); r = s.getRow(rownum); r.setHeight((short) 270); c = r.getCell(1); c.setCellValue(rownum - 12); c = r.getCell(3); c.setCellValue(statistics.get(rownum - 13).getRegionName()); c = r.getCell(6); c.setCellValue(statistics.get(rownum - 13).getChildrenUnder1() == null ? 0 : statistics.get(rownum - 13).getChildrenUnder1()); c = r.getCell(8); c.setCellValue(statistics.get(rownum - 13).getBCG() + " (" + statistics.get(rownum - 13).geteBCG() + ")"); c = r.getCell(10); c.setCellValue(statistics.get(rownum - 13).getVGBL24() + " (" + statistics.get(rownum - 13).geteVGBL24() + ")"); c = r.getCell(11); c.setCellValue(statistics.get(rownum - 13).getVGBG24() + " (" + statistics.get(rownum - 13).geteVGBG24() + ")"); c = r.getCell(14); c.setCellValue(statistics.get(rownum - 13).getDPT_VGB_Hib1() + " (" + statistics.get(rownum - 13).geteDPT_VGB_Hib1() + ")"); c = r.getCell(16); c.setCellValue(statistics.get(rownum - 13).getDPT_VGB_Hib2() + " (" + statistics.get(rownum - 13).geteDPT_VGB_Hib2() + ")"); c = r.getCell(18); c.setCellValue(statistics.get(rownum - 13).getDPT_VGB_Hib3() + " (" + statistics.get(rownum - 13).geteDPT_VGB_Hib3() + ")"); c = r.getCell(19); c.setCellValue(statistics.get(rownum - 13).getOPV1() + " (" + statistics.get(rownum - 13).geteOPV1() + ")"); c = r.getCell(20); c.setCellValue(statistics.get(rownum - 13).getOPV2() + " (" + statistics.get(rownum - 13).geteOPV2() + ")"); c = r.getCell(21); c.setCellValue(statistics.get(rownum - 13).getOPV3() + " (" + statistics.get(rownum - 13).geteOPV3() + ")"); c = r.getCell(23); c.setCellValue(statistics.get(rownum - 13).getMeasles1() + " (" + statistics.get(rownum - 13).geteMeasles1() + ")"); c = r.getCell(25); c.setCellValue(statistics.get(rownum - 13).getAmountOfFinish()); c = r.getCell(27); c.setCellValue(statistics.get(rownum - 13).getProtectedTetanusCases() == null ? 0 : statistics.get(rownum - 13).getProtectedTetanusCases()); c = r.getCell(28); c.setCellValue(statistics.get(rownum - 13).getReactionNormalCases() == null ? 0 : statistics.get(rownum - 13).getReactionNormalCases()); c = r.getCell(30); c.setCellValue(statistics.get(rownum - 13).getReactionSeriousCases() == null ? 0 : statistics.get(rownum - 13).getReactionSeriousCases()); } if (statistics != null && statistics.size() > 0) { for (; rownum < statistics.size() + 13; rownum++) { r = s.getRow(rownum); c = r.getCell(6); c.setCellValue(statistics.get(rownum - 13).getChildrenUnder1() == null ? 0 : statistics.get(rownum - 13).getChildrenUnder1()); c = r.getCell(8); c.setCellValue(statistics.get(rownum - 13).getBCG() + "\n(" + statistics.get(rownum - 13).geteBCG() + ")"); c = r.getCell(10); c.setCellValue(statistics.get(rownum - 13).getVGBL24() + "\n(" + statistics.get(rownum - 13).geteVGBL24() + ")"); c = r.getCell(11); c.setCellValue(statistics.get(rownum - 13).getVGBG24() + "\n(" + statistics.get(rownum - 13).geteVGBG24() + ")"); c = r.getCell(14); c.setCellValue(statistics.get(rownum - 13).getDPT_VGB_Hib1() + "\n(" + statistics.get(rownum - 13).geteDPT_VGB_Hib1() + ")"); c = r.getCell(16); c.setCellValue(statistics.get(rownum - 13).getDPT_VGB_Hib2() + "\n(" + statistics.get(rownum - 13).geteDPT_VGB_Hib2() + ")"); c = r.getCell(18); c.setCellValue(statistics.get(rownum - 13).getDPT_VGB_Hib3() + "\n(" + statistics.get(rownum - 13).geteDPT_VGB_Hib3() + ")"); c = r.getCell(19); c.setCellValue(statistics.get(rownum - 13).getOPV1() + "\n(" + statistics.get(rownum - 13).geteOPV1() + ")"); c = r.getCell(20); c.setCellValue(statistics.get(rownum - 13).getOPV2() + "\n(" + statistics.get(rownum - 13).geteOPV2() + ")"); c = r.getCell(21); c.setCellValue(statistics.get(rownum - 13).getOPV3() + "\n(" + statistics.get(rownum - 13).geteOPV3() + ")"); c = r.getCell(23); c.setCellValue(statistics.get(rownum - 13).getMeasles1() + "\n(" + statistics.get(rownum - 13).geteMeasles1() + ")"); c = r.getCell(25); c.setCellValue(statistics.get(rownum - 13).getAmountOfFinish()); c = r.getCell(27); c.setCellValue(statistics.get(rownum - 13).getProtectedTetanusCases() == null ? 0 : statistics.get(rownum - 13).getProtectedTetanusCases()); c = r.getCell(28); c.setCellValue(statistics.get(rownum - 13).getReactionNormalCases() == null ? 0 : statistics.get(rownum - 13).getReactionNormalCases()); c = r.getCell(30); c.setCellValue(statistics.get(rownum - 13).getReactionSeriousCases() == null ? 0 : statistics.get(rownum - 13).getReactionSeriousCases()); r.setHeight((short) 500); } } else { for (; rownum < 16; rownum++) { r = s.getRow(rownum); c = r.getCell(6); c.setCellValue(""); c = r.getCell(8); c.setCellValue(""); c = r.getCell(10); c.setCellValue(""); c = r.getCell(11); c.setCellValue(""); c = r.getCell(14); c.setCellValue(""); c = r.getCell(16); c.setCellValue(""); c = r.getCell(18); c.setCellValue(""); c = r.getCell(19); c.setCellValue(""); c = r.getCell(20); c.setCellValue(""); c = r.getCell(21); c.setCellValue(""); c = r.getCell(23); c.setCellValue(""); c = r.getCell(25); c.setCellValue(""); c = r.getCell(27); c.setCellValue(""); c = r.getCell(28); c.setCellValue(""); c = r.getCell(30); c.setCellValue(""); } } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); fileOut.close(); path = "/reports/" + prefixFileName + "_Report_" + currentTime + ".xls"; log.debug("Excel: " + path); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } return path; }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
public String printListChildren(ChildrenSearchVO params) { List<ChildrenPrintVO> children = childrenDaoExt.searchChildrenForPrint(params); SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy"); String strDOBFrom = format.format(params.getDateOfBirthFrom()); String strDOBTo = format.format(params.getDateOfBirthTo()); String path = ""; String prefixFileName = ""; Commune commune = communeDao.get(params.getCommuneId()); prefixFileName = commune.getDistrict().getProvince().getProvinceId() + commune.getDistrict().getDistrictId() + commune.getCommuneId();//ww w .j a v a 2s . c om GraniteContext gc = GraniteContext.getCurrentInstance(); ServletContext sc = ((HttpGraniteContext) gc).getServletContext(); String reportDir = sc.getRealPath(config.getBaseReportDir()); long currentTime = System.currentTimeMillis(); String filePath = reportDir + "/" + prefixFileName + "_List_Children_Excel_" + currentTime; POIFSFileSystem fs; try { filePath += ".xls"; fs = new POIFSFileSystem(new FileInputStream(reportDir + "/excel/ListOfChildrenInCommune.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs, true); HSSFSheet s = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; r = s.getRow(0); c = r.getCell(1); c.setCellValue(commune.getCommuneName()); c = r.getCell(2); c.setCellValue("(" + strDOBFrom + " - " + strDOBTo + ")"); HSSFCellStyle cs = wb.createCellStyle(); cs.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs.setBorderTop(HSSFCellStyle.BORDER_THIN); cs.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle cs1 = wb.createCellStyle(); cs1.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs1.setBorderTop(HSSFCellStyle.BORDER_THIN); cs1.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs1.setBorderRight(HSSFCellStyle.BORDER_THIN); cs1.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setBorderTop(HSSFCellStyle.BORDER_THIN); cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs2.setBorderRight(HSSFCellStyle.BORDER_THIN); CreationHelper createHelper = wb.getCreationHelper(); cs2.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy")); int rownum = 3; for (rownum = 3; rownum < children.size() + 3; rownum++) { r = s.createRow(rownum); c = r.createCell(0); c.setCellStyle(cs1); c.setCellValue(rownum - 2); c = r.createCell(1); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFullName()); c = r.createCell(2); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDateOfBirth()); c = r.createCell(3); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).isGender() == true ? "N" : "Nam"); c = r.createCell(4); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getVillageName()); c = r.createCell(5); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getMotherName()); c = r.createCell(6); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getMotherBirthYear() != null ? children.get(rownum - 3).getMotherBirthYear() : 0); c = r.createCell(7); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getMotherMobile()); c = r.createCell(8); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFatherName()); c = r.createCell(9); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFatherBirthYear() != null ? children.get(rownum - 3).getFatherBirthYear() : 0); c = r.createCell(10); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFatherMobile()); c = r.createCell(11); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getCaretakerName()); c = r.createCell(12); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getCaretakerMobile()); c = r.createCell(13); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getVGB()); c = r.createCell(14); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getBCG()); c = r.createCell(15); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib1()); c = r.createCell(16); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib2()); c = r.createCell(17); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib3()); c = r.createCell(18); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getOPV1()); c = r.createCell(19); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getOPV2()); c = r.createCell(20); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getOPV3()); c = r.createCell(21); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getMeasles1()); } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); fileOut.close(); path = "/reports/" + prefixFileName + "_List_Children_Excel_" + currentTime + ".xls"; } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return path; }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
public String printListVaccinatedInLocationReport(String type, String timeFrom, String timeTo, Commune commune, District district, Vaccination vaccine, List<ChildrenVaccinatedInLocationVO> statistics) { SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy"); String path = ""; String prefixFileName = ""; if (commune != null) prefixFileName = district.getDistrictId() + "_" + commune.getCommuneId(); else if (district != null) prefixFileName = district.getDistrictId(); GraniteContext gc = GraniteContext.getCurrentInstance(); ServletContext sc = ((HttpGraniteContext) gc).getServletContext(); String reportDir = sc.getRealPath(config.getBaseReportDir()); long currentTime = System.currentTimeMillis(); String filePath = reportDir + "/" + prefixFileName + "_DanhSachTreDenTiem_" + vaccine.getName() + "_" + currentTime;//from w ww.j a va2 s. com POIFSFileSystem fs; try { filePath += ".xls"; fs = new POIFSFileSystem( new FileInputStream(reportDir + "/excel/ListOfChildrenVaccinatedInLocation.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs, true); HSSFSheet s = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; r = s.getRow(0); c = r.getCell(0); c.setCellValue("Danh sch tr n tim chng " + vaccine.getName() + " (bao gm c tr tim bnh vin/phng khm)"); r = s.getRow(1); c = r.getCell(1); c.setCellValue(district.getDistrictName()); if (commune != null) { c = r.getCell(2); c.setCellValue("X"); c = r.getCell(3); c.setCellValue(commune.getCommuneName()); c = r.getCell(4); c.setCellValue("(" + timeFrom + " - " + timeTo + ")"); } else { c = r.getCell(2); c.setCellValue("(" + timeFrom + " - " + timeTo + ")"); } HSSFCellStyle cs = wb.createCellStyle(); cs.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs.setBorderTop(HSSFCellStyle.BORDER_THIN); cs.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle cs1 = wb.createCellStyle(); cs1.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs1.setBorderTop(HSSFCellStyle.BORDER_THIN); cs1.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs1.setBorderRight(HSSFCellStyle.BORDER_THIN); cs1.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setBorderTop(HSSFCellStyle.BORDER_THIN); cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs2.setBorderRight(HSSFCellStyle.BORDER_THIN); CreationHelper createHelper = wb.getCreationHelper(); cs2.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy")); int rownum = 3; for (rownum = 3; rownum < statistics.size() + 3; rownum++) { r = s.createRow(rownum); c = r.createCell(0); c.setCellStyle(cs1); c.setCellValue(rownum - 2); c = r.createCell(1); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getCommuneName()); c = r.createCell(2); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getVillageName()); c = r.createCell(3); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getChildCode()); c = r.createCell(4); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getFullName()); c = r.createCell(5); c.setCellStyle(cs1); c.setCellValue(statistics.get(rownum - 3).getGender() == true ? "N" : "Nam"); c = r.createCell(6); c.setCellStyle(cs2); c.setCellValue(statistics.get(rownum - 3).getDateOfBirth()); c = r.createCell(7); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getMotherName()); c = r.createCell(8); c.setCellStyle(cs2); c.setCellValue(statistics.get(rownum - 3).getDateOfImmunization()); c = r.createCell(9); c.setCellStyle(cs); String vaccinatedLocation = ""; if (statistics.get(rownum - 3).getOtherLocation() != null && statistics.get(rownum - 3).getOtherLocation() >= 1 && statistics.get(rownum - 3).getOtherLocation() <= 4) { if (statistics.get(rownum - 3).getOtherLocation() == 1) vaccinatedLocation = "Bnh vin TW"; else if (statistics.get(rownum - 3).getOtherLocation() == 2) vaccinatedLocation = "Bnh vin tnh"; else if (statistics.get(rownum - 3).getOtherLocation() == 3) vaccinatedLocation = "Bnh vin huyn"; else if (statistics.get(rownum - 3).getOtherLocation() == 4) vaccinatedLocation = "Phng khm/Bnh vin t nhn"; } else vaccinatedLocation = statistics.get(rownum - 3).getVaccinatedCommune(); c.setCellValue(vaccinatedLocation); } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); fileOut.close(); path = "/reports/" + prefixFileName + "_DanhSachTreDenTiem_" + vaccine.getName() + "_" + currentTime + ".xls"; } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return path; }
From source file:org.jfree.workbook.io.XLWriter.java
License:Open Source License
/** * Creates a new cell in the HSSFRow, based on the contents of the supplied cell. * //from w w w. j a v a2 s . c o m * @param hssfWorkbook the workbook. * @param hssfRow the row. * @param worksheet the worksheet. * @param cell the cell. */ private void createHSSFCell(HSSFWorkbook hssfWorkbook, HSSFRow hssfRow, Worksheet worksheet, Cell cell) { HSSFCell hssfCell = hssfRow.createCell((short) cell.getColumn()); if (cell.getType() == Cell.LABEL_TYPE) { hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING); hssfCell.setCellValue(cell.getContent()); } else if (cell.getType() == Cell.VALUE_TYPE) { hssfCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); hssfCell.setCellValue(Double.valueOf(cell.getContent()).doubleValue()); } else if (cell.getType() == Cell.DATE_TYPE) { hssfCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); hssfCell.setCellValue(Double.valueOf(cell.getContent()).doubleValue()); } HSSFCellStyle hssfStyle = hssfWorkbook.createCellStyle(); Style style = worksheet.getStyles().getStyle(cell.getRow(), cell.getColumn()); hssfStyle.setAlignment(getXLHorizontalAlignment(style)); hssfStyle.setVerticalAlignment(getXLVerticalAlignment(style)); hssfStyle.setWrapText(style.isWrapText()); //hssfStyle.setFillBackgroundColor(style.getBackgroundColor()); hssfStyle.setBorderTop(getXLBorder(style.getBorder().getTop())); hssfStyle.setBorderBottom(getXLBorder(style.getBorder().getBottom())); hssfStyle.setBorderLeft(getXLBorder(style.getBorder().getLeft())); hssfStyle.setBorderRight(getXLBorder(style.getBorder().getRight())); hssfCell.setCellStyle(hssfStyle); }
From source file:org.jxstar.report.studio.ExportXlsBO.java
/** * ?//from w w w .ja va2 s.c o m * @param wb -- ? * @return */ public HSSFCellStyle createTitleStyle(HSSFWorkbook wb) { // HSSFFont cellFont = wb.createFont(); cellFont.setFontName(""); cellFont.setFontHeightInPoints((short) 16); cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //? HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle.setFont(cellFont); return cellStyle; }
From source file:org.jxstar.report.studio.ExportXlsBO.java
/** * ?//w w w.ja va2s . c om * @param wb -- ? * @return */ public HSSFCellStyle createHeadStyle(HSSFWorkbook wb) { // HSSFFont cellFont = wb.createFont(); cellFont.setFontName(""); cellFont.setFontHeightInPoints((short) 9); cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //? HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle.setFont(cellFont); // cellStyle.setBorderBottom((short) 1); cellStyle.setBorderLeft((short) 1); cellStyle.setBorderRight((short) 1); cellStyle.setBorderTop((short) 1); return cellStyle; }
From source file:org.jxstar.report.studio.ExportXlsBO.java
/** * ?//from w ww.j ava2s. c om * @param wb -- ? * @return */ public HSSFCellStyle createCellStyle(HSSFWorkbook wb) { // HSSFFont cellFont = wb.createFont(); cellFont.setFontName(""); cellFont.setFontHeightInPoints((short) 9); //? HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle.setFont(cellFont); // cellStyle.setBorderBottom((short) 1); cellStyle.setBorderLeft((short) 1); cellStyle.setBorderRight((short) 1); cellStyle.setBorderTop((short) 1); return cellStyle; }
From source file:org.meveocrm.admin.action.reporting.MeasurementBean.java
License:Open Source License
public HSSFCellStyle getCellStyle(HSSFWorkbook workbook) { HSSFCellStyle style = workbook.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); return style; }
From source file:org.oep.cmon.report.portlet.util.ActionUtil.java
License:Apache License
/** * This is function excelTongHopChiTiet//from w w w. j a v a2s . 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/*from www. j av 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(); } }