Example usage for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. addMergedRegion

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion.

Prototype

@Override
public int addMergedRegion(CellRangeAddress region)

Source Link

Document

Adds a merged region of cells on a sheet.

Usage

From source file:com.accounting.reportMBean.ReportCommonBean.java

public void postProcessXLSGeneral(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    //        headerFont.setFontHeightInPoints((short) 20);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    //         Font headerFont1 = wb.createFont();
    //        headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    //        headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    //        headerCellStyle1.setFont(headerFont);
    //        headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    HSSFSheet sheet = wb.getSheetAt(0);
    sheet.shiftRows(0, sheet.getLastRowNum(), 6);
    sheet.autoSizeColumn(0);/* www  .  ja va  2s.  c  o  m*/
    sheet.autoSizeColumn(1);
    sheet.autoSizeColumn(2);
    sheet.autoSizeColumn(3);
    sheet.autoSizeColumn(4);
    sheet.autoSizeColumn(5);
    sheet.autoSizeColumn(6);

    HSSFRow firstRow = sheet.createRow(0);
    firstRow.createCell(0).setCellValue(HelperUtil.getLoggedInOffice().getName());
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(1);
    secondRow.createCell(0).setCellValue(HelperUtil.getLoggedInOffice().getAddress());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(2);
    thirdRow.createCell(0).setCellValue(HelperUtil.getPageName().getPageName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow forthRow = sheet.createRow(4);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    forthRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    forthRow.getCell(0).setCellStyle(headerCellStyle2);

    HSSFRow fifthRow = sheet.createRow(3);
    fifthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fifthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 12));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 12));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 12));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 12));
    sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 12));
    sheet.addMergedRegion(new CellRangeAddress(5, 5, 0, 12));
}

From source file:com.accounting.reportMBean.ReportCommonBean.java

public void simplePostProcessXLSSalesSummary(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate7");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    HSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(0).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);/*  w w  w. j a v  a  2  s . c  om*/
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("Sales Report Summary Wise");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    HSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
}

From source file:com.accounting.reportMBean.ReportCommonBean.java

public void simplePostProcessXLSForPurchase(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    HSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(0).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);/*from  ww  w. j  a  va  2 s  .  com*/
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue(HelperUtil.getPageName().getPageName());
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    HSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
}

From source file:com.accounting.reportMBean.ReportCommonBean.java

public void simplePostProcessXLSForPurchaseforSearch(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate7");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    HSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(0).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);/*from  w  w w  .  j  a  va2s  . c  o  m*/
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue(HelperUtil.getPageName().getPageName());
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    HSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
}

From source file:com.accounting.reportMBean.ReportCommonBean.java

public void simplePostProcessXLS(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    HSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(0).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);/*from w  w  w . ja  va2s  .  co  m*/
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue(pageName);
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    HSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
    HSSFRow lastRow;
    for (Row row : sheet) {
        if (row.getRowNum() > 4) {
            for (int i = 3; i < 8; i++) {

                String cost = row.getCell(i).getStringCellValue();
                if (cost != null && !cost.isEmpty()) {
                    row.getCell(i).setCellType(HSSFCell.CELL_TYPE_BLANK);
                    row.getCell(i).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    row.getCell(i).setCellValue(Double.parseDouble(cost.replace(",", "")));
                }
            }
        }
    }
}

From source file:com.accounting.reportMBean.StockLedgerMBeans.java

public void postProcessXLS(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateReading = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);
    HSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(0).getLastCellNum();

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);/*  w ww . j  av  a  2 s  .co m*/
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(0);
    firstRow.createCell(0).setCellValue(reportHeader);
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(1);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String generatedDate = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");

    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + generatedDate + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    HSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("From: " + startDateString + " To: " + endDateReading);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);
    System.out.println("The cell count " + cellCount);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cellCount));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, cellCount));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, cellCount));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, cellCount));
    for (Row row : sheet) {
        if (row.getRowNum() > 4) {
            for (int i = 3; i < count; i++) {
                String cost = row.getCell(i).getStringCellValue();
                if (cost != null && !cost.isEmpty()) {
                    row.getCell(i).setCellType(HSSFCell.CELL_TYPE_BLANK);
                    row.getCell(i).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    row.getCell(i).setCellValue(Double.parseDouble(cost.replace(",", "")));
                }
            }
        }
    }
}

From source file:com.ah.ui.actions.admin.LicenseMgrAction.java

License:Open Source License

private void exportEntitleKeyInfo() {
    try {//from  ww  w.j  a  v  a 2 s  .  c  o m
        // create a new file
        FileOutputStream out = new FileOutputStream(ORDERKEYINFO_EXPORT_FILE_PATH);
        // create a new workbook
        HSSFWorkbook wb = new HSSFWorkbook();
        // create a new sheet
        HSSFSheet s = wb.createSheet("Sheet1");
        // declare a row object reference
        HSSFRow r = null;
        // declare a cell object reference
        HSSFCell c = null;
        // row index
        int rowNum = 0;
        // cell count
        int cellcount = 0;
        if (NmsUtil.isHMForOEM()) {
            cellcount = 7;
        } else if (getIsInHomeDomain()) {
            cellcount = 8;
        } else if (NmsUtil.isHostedHMApplication()) {
            cellcount = 9;
        }
        if (cellcount == 0) {
            return;
        } else {
            for (int i = 0; i < cellcount; i++) {
                s.setColumnWidth(i, getColumnWidthByIndex(i) * 256);
            }
        }
        // create cell style
        HSSFCellStyle cs = wb.createCellStyle();
        // create font object
        HSSFFont f = wb.createFont();

        //set font to 12 point type
        f.setFontHeightInPoints((short) 12);
        f.setFontName("Calibri");

        //set cell stlye
        cs.setFont(f);
        cs.setAlignment(CellStyle.ALIGN_CENTER);
        cs.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs1 = wb.createCellStyle();
        // create font object
        HSSFFont f1 = wb.createFont();

        //set font to 12 point type
        f1.setFontHeightInPoints((short) 12);
        f1.setFontName("Calibri");
        f1.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //set cell stlye
        cs1.setFont(f1);
        cs1.setAlignment(CellStyle.ALIGN_RIGHT);
        cs1.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs2 = wb.createCellStyle();
        cs2.setFont(f1);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        cs2.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs3 = wb.createCellStyle();
        cs3.setFont(f);
        cs3.setAlignment(CellStyle.ALIGN_RIGHT);
        cs3.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs4 = wb.createCellStyle();
        cs4.setFont(f1);
        cs4.setAlignment(CellStyle.ALIGN_LEFT);
        cs4.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs5 = wb.createCellStyle();
        // create font object
        HSSFFont f2 = wb.createFont();

        //set font to 12 point type
        f2.setFontHeightInPoints((short) 12);
        f2.setFontName("Calibri");
        f2.setBoldweight(Font.BOLDWEIGHT_BOLD);
        f2.setColor(Font.COLOR_RED);
        cs5.setFont(f2);
        cs5.setAlignment(CellStyle.ALIGN_LEFT);
        cs5.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs6 = wb.createCellStyle();
        cs6.setFont(f);
        cs6.setAlignment(CellStyle.ALIGN_LEFT);
        cs6.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create a row
        // row 1
        s.addMergedRegion(new CellRangeAddress(0, 0, 0, cellcount - 1));
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.title"));
        c.setCellStyle(cs);

        // row 2
        String sysInfo = "";
        if (getIsInHomeDomain()) {
            sysInfo = "System ID: " + getSystemId();
            // HiveManager Online user
        } else if (NmsUtil.isHostedHMApplication()) {
            sysInfo = "VHM ID: " + getDomain().getVhmID();
        }
        s.addMergedRegion(new CellRangeAddress(1, 1, 0, cellcount - 1));
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(sysInfo);
        c.setCellStyle(cs);

        // row 3
        s.addMergedRegion(new CellRangeAddress(2, 2, 0, cellcount - 1));
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        List<?> userInfo = QueryUtil.executeQuery(
                "SELECT email, company FROM " + UserRegInfoForLs.class.getSimpleName(), null,
                new FilterParams("owner.domainName", getDomain().getDomainName()));
        if (!userInfo.isEmpty()) {
            Object[] userInfos = (Object[]) userInfo.get(0);
            c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.email.company",
                    new String[] { (String) userInfos[0], (String) userInfos[1] }));
        }
        c.setCellStyle(cs);

        // row 4
        r = s.createRow(rowNum++);

        // row 5
        r = s.createRow(rowNum++);

        // row 6 cell 1
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.device.licensed"));
        c.setCellStyle(cs1);

        // row 6 cell 2
        c = r.createCell(1);
        c.setCellValue(licenseInfo.getHiveAps());
        c.setCellStyle(cs2);

        // device management info
        Map<String, Integer> apcount = HiveAPInfoFromeDatabase
                .getManagedDeviceTypeAndNumber(getDomain().getDomainName());
        int vpnCount = 0;
        int totalCount = 0;
        if (null != apcount) {
            Object[] typeNames = apcount.keySet().toArray();
            Arrays.sort(typeNames);
            for (Object typeName : typeNames) {

                // VPN Gateway VA does not belong to device
                if (MgrUtil.getEnumString("enum.hiveAp.model.10").equals(typeName)) {
                    vpnCount = apcount.get(typeName);
                } else {
                    r = s.createRow(rowNum++);
                    c = r.createCell(0);
                    c.setCellValue((String) typeName);
                    c.setCellStyle(cs3);

                    c = r.createCell(1);
                    c.setCellValue(apcount.get(typeName));
                    c.setCellStyle(cs);
                    totalCount += apcount.get(typeName);
                }
            }

        }

        // managed device total number cell1
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.device.total"));
        c.setCellStyle(cs1);

        // managed device total number cell2
        c = r.createCell(1);
        c.setCellValue(totalCount);
        c.setCellStyle(cs2);

        // blank row
        r = s.createRow(rowNum++);

        // licensed VPN Gateway VA cell1
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.cvg.licensed"));
        c.setCellStyle(cs1);

        // licensed VPN Gateway VA cell2
        c = r.createCell(1);
        c.setCellValue(licenseInfo.getCvgNumber());
        c.setCellStyle(cs2);

        // managed VPN Gateway VA cell1
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.cvg.total"));
        c.setCellStyle(cs1);

        // managed VPN Gateway VA cell 2
        c = r.createCell(1);
        c.setCellValue(vpnCount);
        c.setCellStyle(cs2);

        // entitlement key information
        preparePage();
        if (null != page && !page.isEmpty()) {
            r = s.createRow(rowNum++);
            List<OrderHistoryInfo> normalKey = new ArrayList<>();
            List<OrderHistoryInfo> invalidKey = new ArrayList<>();
            List<OrderHistoryInfo> expiredKey = new ArrayList<>();

            for (Object obj : page) {
                OrderHistoryInfo orderInfo = (OrderHistoryInfo) obj;
                if (orderInfo.getStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_NORMAL
                        && orderInfo.getCvgStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_NORMAL) {
                    normalKey.add(orderInfo);
                } else if (orderInfo.getStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_DISABLE
                        || orderInfo.getCvgStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_DISABLE) {
                    invalidKey.add(orderInfo);
                } else {
                    expiredKey.add(orderInfo);
                }
            }

            if (!normalKey.isEmpty()) {

                // normal entitle key title
                r = s.createRow(rowNum++);
                setEntitlementKeyCellValue(cellcount, cs4, null, r, null, MgrUtil.getUserMessage("order.key"));

                // normal entitle key info
                for (OrderHistoryInfo keyInfo : normalKey) {
                    r = s.createRow(rowNum++);
                    setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null);
                }
            }

            if (!invalidKey.isEmpty()) {

                if (!normalKey.isEmpty()) {
                    r = s.createRow(rowNum++);
                    r = s.createRow(rowNum++);
                }

                // invalid entitle key title
                r = s.createRow(rowNum++);
                setEntitlementKeyCellValue(cellcount, cs5, null, r, null,
                        MgrUtil.getUserMessage("admin.license.orderkey.export.invalidkey.title"));

                // invalid entitle key info
                for (OrderHistoryInfo keyInfo : invalidKey) {
                    r = s.createRow(rowNum++);
                    setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null);
                }
            }

            if (!expiredKey.isEmpty()) {

                if (!normalKey.isEmpty() || !invalidKey.isEmpty()) {
                    r = s.createRow(rowNum++);
                    r = s.createRow(rowNum++);
                }

                // expired entitle key title
                r = s.createRow(rowNum++);
                setEntitlementKeyCellValue(cellcount, cs5, null, r, null,
                        MgrUtil.getUserMessage("admin.license.orderkey.export.expiredkey.title"));

                // expired entitle key info
                for (OrderHistoryInfo keyInfo : expiredKey) {
                    r = s.createRow(rowNum++);
                    setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null);
                }
            }
        }

        // write the workbook to the output stream
        // close our file (don't blow out our file handles)
        wb.write(out);
        out.close();
        generateAuditLog(HmAuditLog.STATUS_SUCCESS,
                MgrUtil.getUserMessage("hm.audit.log.export.entitlement.key"));
    } catch (Exception ex) {
        generateAuditLog(HmAuditLog.STATUS_FAILURE,
                MgrUtil.getUserMessage("hm.audit.log.export.entitlement.key"));
        log.error("exportEntitleKeyInfo()", ex.getMessage());
    }
}

From source file:com.aimluck.eip.modules.screens.TimecardXlsExportScreen.java

License:Open Source License

private void setupTimecardSheet(RunData rundata, Context context, HSSFWorkbook wb) throws Exception {

    TimecardSelectData listData = new TimecardSelectData();
    listData.initField();/*w  w w.  j  av  a 2 s .  com*/
    listData.setRowsNum(1000);
    listData.doViewList(this, rundata, context);

    String sheet_name = "";
    // ?
    String[] headers = { "", "?", "", "", "?" };
    // 01
    short[] cell_enc_types = { HSSFCell.ENCODING_UTF_16, HSSFCell.CELL_TYPE_NUMERIC, HSSFCell.ENCODING_UTF_16,
            HSSFCell.ENCODING_UTF_16, HSSFCell.ENCODING_UTF_16 };
    HSSFSheet sheet = createHSSFSheet(wb, sheet_name, headers, cell_enc_types);

    int rowcount = 0;

    // ?
    HSSFCellStyle style_col = wb.createCellStyle();
    style_col.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    style_col.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);

    TimecardListResultData tclistrd = null;
    List<String> daykeys = listData.getDateListKeys();
    int daykeysize = daykeys.size();
    for (int i = 0; i < daykeysize; i++) {
        tclistrd = listData.getDateListValue(daykeys.get(i));
        List<TimecardResultData> viewlist = tclistrd.getViewList();
        int viewlistsize = viewlist.size();

        for (int j = 0; j < viewlistsize; j++) {
            TimecardResultData rd = viewlist.get(j);
            String workStr = null;
            if ("0".equals(rd.getWorkFlag().toString())) {
                workStr = "";
            } else {
                workStr = "";
            }

            String[] rows = { tclistrd.getDateStr(), tclistrd.getSummayTimes(), workStr, rd.getWorkDateStr(),
                    rd.getReason().toString() };

            rowcount = rowcount + 1;
            addRow(sheet.createRow(rowcount), cell_enc_types, rows);
        }
        sheet.addMergedRegion(new Region(rowcount - viewlistsize + 1, (short) 0, rowcount, (short) 0));
        HSSFRow row = sheet.getRow(rowcount - viewlistsize + 1);
        HSSFCell cell1 = row.getCell((short) 0);
        cell1.setCellStyle(style_col);

        sheet.addMergedRegion(new Region(rowcount - viewlistsize + 1, (short) 1, rowcount, (short) 1));
        HSSFCell cell2 = row.getCell((short) 1);
        cell2.setCellStyle(style_col);
    }

    int uid = ALEipUtils.getUserId(rundata);
    ALEventlogFactoryService.getInstance().getEventlogHandler().logXlsScreen(uid, "",
            ALEventlogConstants.PORTLET_TYPE_TIMECARD_XLS_SCREEN);
}

From source file:com.claim.controller.Center16AndJula2015Controller.java

public ProgrameStatus center16_Jula_2015(OppReport report) {

    ProgrameStatus programeStatus = new ProgrameStatus();
    List<ObjRptChula> listData = new ArrayList<ObjRptChula>();
    int col_last = 29;
    int row_start = 8; // index row
    int row_formula_start = row_start + 1;
    int col_txtid_width = col_last + 1;
    try {//from   w ww . j  a  v  a  2 s .com
        connection = new DBManage().open();
        Center16AndChula2015DAO chula2015DAO = new Center16AndChula2015DAO();
        chula2015DAO.setConnection(connection);

        if (report.getServiceCode().equals(HCODE_CENTER16)) { // center 16
            listData = chula2015DAO.getListChulaDetail(report.getStmp(), HCODE_CENTER16);
            EXCELL_HEADER1 = "???? OP : "
                    + chula2015DAO.getMonthPayment(report.getStmp());
            EXCELL_HEADER2 = "? 16    13661  Model 2 ";
            EXCELL_HOSPITAL = "??  ? 16   13661 ";

            //out 
            out = new FileOutputStream(report.getPathFile() + "" + File.separator + "HC16_13661_"
                    + report.getYearMonth() + "-" + report.getNo() + ".xls");
            report.setServiceName(
                    " ? 16 ");
        } else if (report.getServiceCode().equals(HCODE_CHULA)) { //  Jula
            listData = chula2015DAO.getListChulaDetail(report.getStmp(), HCODE_CHULA);
            EXCELL_HEADER1 = "???? OP : "
                    + chula2015DAO.getMonthPayment(report.getStmp());
            EXCELL_HEADER2 = "? 16    13661  Model 2";
            EXCELL_HOSPITAL = "??  ?  ?     13756";

            //out 
            out = new FileOutputStream(report.getPathFile() + "" + File.separator + "Chula_13756_"
                    + report.getYearMonth() + "-" + report.getNo() + ".xls");
            report.setServiceName("?");
        }

        //readTemplate 
        file = new FileInputStream(
                new File("." + File.separator + "xls" + File.separator + "CH16_CHula_detail_2015.xls"));

        // style Excell
        HSSFWorkbook wbCenter16Jula = new HSSFWorkbook(file);
        this.loadStyle(wbCenter16Jula);

        // Start sheet 1 *******************************************************************************
        HSSFSheet sheet = workbookBase.getSheetAt(0);

        sheet.createFreezePane(4, 8); // col,row
        sheet.setColumnWidth(col_txtid_width, WIDTH_TXID);

        HSSFCell cell = null;
        HSSFRow row = null;

        // row 0 HEADER0
        row = sheet.createRow(0);
        row.setHeight((short) 390);
        cell = row.createCell(0);
        cell.setCellValue(EXCELL_HEADER1);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last));
        cell.setCellStyle(csHead);

        // row 1 HEADER2
        row = sheet.createRow(1);
        row.setHeight((short) 390);
        cell = row.createCell(0);
        cell.setCellValue(EXCELL_HEADER2);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_last));
        cell.setCellStyle(csHead);

        // row 1 HOSPITAL
        row = sheet.createRow(2);
        row.setHeight((short) 390);
        cell = row.createCell(0);
        cell.setCellValue(EXCELL_HOSPITAL);
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, col_last));
        cell.setCellStyle(csHead);

        int curRow = row_start;
        int i = 1;

        for (int j = 0; j < listData.size(); j++) {
            ObjRptChula data = listData.get(j);
            int col1 = i;

            row = sheet.createRow(curRow);
            row.setHeight((short) 340);

            /*PoiHssfUtil stylePoi = new PoiHssfUtil(wb, row, cell);
             stylePoi.setStyleText(0, String.valueOf(col1), PoiHssfUtil.CENTER);*/
            cell = row.createCell(0);
            cell.setCellValue(col1);
            cell.setCellStyle(csNum4);

            cell = row.createCell(1);
            cell.setCellValue(data.getPid());
            cell.setCellStyle(csStringPid);

            cell = row.createCell(2);
            cell.setCellValue(data.getHn());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(3);
            cell.setCellValue(data.getPname());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(4);
            cell.setCellValue(data.getHmain());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(5);
            cell.setCellValue(data.getDateopd_th());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(6);
            cell.setCellValue(data.getPdxcode());
            cell.setCellStyle(csNum4);

            cell = row.createCell(7);
            cell.setCellValue(data.getChrg_car());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(8);
            cell.setCellValue(data.getChrg_rehab_inst());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(9);
            cell.setCellValue(data.getChrg_ophc());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(10);
            cell.setCellValue(data.getChrg_car_rehabinst_ophc_total());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(11);
            cell.setCellValue(data.getChrg_202());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(12);
            cell.setCellValue(data.getChrg_stditem());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(13);
            cell.setCellValue(data.getChrg_other());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(14);
            cell.setCellValue(data.getChrg_total());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(15);
            cell.setCellValue(data.getSum_chrg());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(16);
            cell.setCellValue(data.getPaid_car());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(17);
            cell.setCellValue(data.getPaid_rehab_inst());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(18);
            cell.setCellValue(data.getPaid_ophc());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(19);
            cell.setCellValue(data.getPaid_car_rehabinst_ophc_total());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(20);
            cell.setCellValue(data.getPaid_202());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(21);
            cell.setCellValue(data.getPaid_stditem());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(22);
            cell.setCellValue(data.getPaid_other());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(23);
            cell.setCellValue(data.getPaid_202_stditem_other_total());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(24);
            cell.setCellValue(data.getPaid_cal_point());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(25);
            cell.setCellValue(data.getPaid_cal_point_total());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(26);
            cell.setCellValue(data.getPaid_total());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(27);
            cell.setCellValue(data.getCompensation_fee_total());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(28);
            cell.setCellValue(data.getRemark());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(29);
            cell.setCellValue(data.getInvoice_no());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(30);
            cell.setCellValue(data.getTxid());
            cell.setCellStyle(csStringtxid);

            curRow++;
            i++;

        }

        // 
        row = sheet.createRow(curRow);
        cell = row.createCell(0);
        cell.setCellValue("");
        sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 6));
        cell.setCellStyle(csNum4B);

        row.createCell(1).setCellStyle(csNum4B);
        row.createCell(2).setCellStyle(csNum4B);
        row.createCell(3).setCellStyle(csNum4B);
        row.createCell(4).setCellStyle(csNum4B);
        row.createCell(5).setCellStyle(csNum4B);
        row.createCell(6).setCellStyle(csNum4B);

        cell = row.createCell(7);
        cell.setCellFormula(builderFormulaSum(7, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(8);
        cell.setCellFormula(builderFormulaSum(8, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(9);
        cell.setCellFormula(builderFormulaSum(9, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(10);
        cell.setCellFormula(builderFormulaSum(10, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(11);
        cell.setCellFormula(builderFormulaSum(11, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(12);
        cell.setCellFormula(builderFormulaSum(12, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(13);
        cell.setCellFormula(builderFormulaSum(13, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(14);
        cell.setCellFormula(builderFormulaSum(14, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(15);
        cell.setCellFormula(builderFormulaSum(15, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(16);
        cell.setCellFormula(builderFormulaSum(16, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(17);
        cell.setCellFormula(builderFormulaSum(17, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(18);
        cell.setCellFormula(builderFormulaSum(18, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(19);
        cell.setCellFormula(builderFormulaSum(19, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(20);
        cell.setCellFormula(builderFormulaSum(20, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(21);
        cell.setCellFormula(builderFormulaSum(21, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(22);
        cell.setCellFormula(builderFormulaSum(22, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(23);
        cell.setCellFormula(builderFormulaSum(23, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(24);
        cell.setCellFormula(builderFormulaSum(24, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(25);
        cell.setCellFormula(builderFormulaSum(25, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(26);
        cell.setCellFormula(builderFormulaSum(26, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(27);
        cell.setCellFormula(builderFormulaSum(27, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(28);
        cell.setCellStyle(csNum4B);

        workbookBase.setSheetName(0, report.getServiceCode() + "  " + report.getServiceName());

        workbookBase.write(out);

        out.close();
        file.close();

        Console.LOG(Message.exportSuccess(report.getServiceName()), 1);
        programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS);
        programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE);
        programeStatus.setProcessStatus(true);
    } catch (Exception e) {
        e.printStackTrace();
        Console.LOG(e.getMessage(), 0);
        programeStatus.setMessage(ConstantMessage.MSG_PROCESS_FAILS + e.toString());
        programeStatus.setTitle(ConstantMessage.MSG_CONTACT_ADMIN);
        programeStatus.setProcessStatus(false);
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(Center16AndJula2015Controller.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    return programeStatus;
}

From source file:com.claim.controller.Noni2015Controller.java

public ProgrameStatus noniDetail(OppReport report) {
    int col_last = 13;
    int row_start = 6; // index row
    int row_formula_start = 7;
    ProgrameStatus programeStatus = new ProgrameStatus();
    List<ObjRptNoniDetail> listData = new ArrayList<ObjRptNoniDetail>();
    String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo();
    String pathDirectory = report.getPathFile() + "" + File.separator + "noni" + File.separator + "" + stmp + ""
            + File.separator + "";
    try {//from   w w  w .  j a  v a 2  s  .c  om
        connection = new DBManage().open();
        Noni2015DAO noni2015DAO = new Noni2015DAO();
        noni2015DAO.setConnection(connection);

        //readTemplate 
        file = new FileInputStream(new File("." + File.separator + "xls" + File.separator + "NONI_detail.xls"));

        //EXCELL_HEADER1 = "??  ???? (NONI)   2557";
        EXCELL_HEADER1 = report.getTitle1();
        /*if (report.getBudget_year().equals("2014")) {
         EXCELL_HEADER2 = " 01 ()";
         } else {
         EXCELL_HEADER2 = " " + new DateUtil().convertStmpToString(report.getStmp());
         }*/
        EXCELL_HEADER2 = new DateUtil().convertStmpToNoniString(report.getStmp());
        EXCELL_HOSPITAL = "?: "
                + StringOpUtil.removeNull(report.getServiceName()) + " ("
                + StringOpUtil.removeNull(report.getServiceCode()) + ")";

        // style Excell
        HSSFWorkbook wbNoni = new HSSFWorkbook(file);
        this.loadStyle(wbNoni);

        // Start sheet 1 
        HSSFSheet sheet = workbookBase.getSheetAt(0);
        sheet.createFreezePane(5, row_start); // col[F],row[index 6 = 7]
        sheet.setColumnWidth((col_last + 1), WIDTH_TXID);

        // row 0 HEADER0
        row = sheet.createRow(0);
        row.setHeight((short) 390);
        cell = row.createCell(0);
        cell.setCellValue(EXCELL_HEADER1);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last));
        cell.setCellStyle(csHead);

        // row 1 HEADER2
        row = sheet.createRow(1);
        row.setHeight((short) 390);
        cell = row.createCell(0);
        cell.setCellValue(EXCELL_HEADER2);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_last));
        cell.setCellStyle(csHead);

        // row 1 HOSPITAL
        row = sheet.createRow(2);
        row.setHeight((short) 390);
        cell = row.createCell(0);
        cell.setCellValue(EXCELL_HOSPITAL);
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, col_last));
        cell.setCellStyle(csHead);

        int curRow = row_start;
        int i = 1;
        listData = noni2015DAO.getListNoniDetail(report);
        for (int j = 0; j < listData.size(); j++) {
            ObjRptNoniDetail data = listData.get(j);
            int col1 = i;

            row = sheet.createRow(curRow);
            row.setHeight((short) 340);

            /*PoiHssfUtil stylePoi = new PoiHssfUtil(wb, row, cell);
             stylePoi.setStyleText(0, String.valueOf(col1), PoiHssfUtil.CENTER);*/
            cell = row.createCell(0);
            cell.setCellValue(col1);
            cell.setCellStyle(csNum4);

            cell = row.createCell(1);
            cell.setCellValue(data.getPid());
            cell.setCellStyle(csStringPid);

            cell = row.createCell(2);
            cell.setCellValue(data.getPname());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(3);
            cell.setCellValue(data.getHn());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(4);
            cell.setCellValue(data.getHmainname());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(5);
            cell.setCellValue(data.getDateopd_thai_buddha());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(6);
            cell.setCellValue(data.getNoniclass());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(7);
            cell.setCellValue(data.getChrg_middle_priced_items());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(8);
            cell.setCellValue(data.getChrg_other());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(9);
            cell.setCellValue(data.getChrg_total());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(10);
            cell.setCellValue(data.getPaid_middle_priced_items());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(11);
            cell.setCellValue(data.getPaid_other());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(12);
            cell.setCellValue(data.getPaid_total());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(13);
            cell.setCellValue(data.getInvoice_no());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(14);
            cell.setCellValue(data.getTxid());
            cell.setCellStyle(csStringtxid);

            curRow++;
            i++;
        }

        // 
        row = sheet.createRow(curRow);
        cell = row.createCell(0);
        cell.setCellValue("");
        sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 6));
        cell.setCellStyle(csNum4B);

        cell = row.createCell(1);
        cell.setCellStyle(csNum4B);
        cell = row.createCell(2);
        cell.setCellStyle(csNum4B);
        cell = row.createCell(3);
        cell.setCellStyle(csNum4B);
        cell = row.createCell(4);
        cell.setCellStyle(csNum4B);
        cell = row.createCell(5);
        cell.setCellStyle(csNum4B);
        cell = row.createCell(6);
        cell.setCellStyle(csNum4B);

        cell = row.createCell(7);
        cell.setCellFormula(builderFormulaSum(7, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(8);
        cell.setCellFormula(builderFormulaSum(8, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(9);
        cell.setCellFormula(builderFormulaSum(9, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(10);
        cell.setCellFormula(builderFormulaSum(10, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(11);
        cell.setCellFormula(builderFormulaSum(11, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(12);
        cell.setCellFormula(builderFormulaSum(12, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(13);
        cell.setCellStyle(csDouble2B);

        workbookBase.setSheetName(0, report.getServiceCode());// + "  " + report.getServiceName());
        /// int sheetIndex, int startColumn, int endColumn, int startRow, int endRow)                    
        sheet.setColumnHidden(13, true);
        workbookBase.setPrintArea(0, "$A$1:$M$" + (curRow + 1));
        // file out 
        // ############# mkdir ############          
        pathDirectory = new FileUtil().mkdirDir(pathDirectory, report.getBudget_year(), "noni");
        // ############# mkdir ############            
        out = new FileOutputStream(pathDirectory + "" + File.separator + "noni_"
                + StringOpUtil.removeNull(report.getServiceCode()) + "_" + report.getStmp() + ".xls");
        workbookBase.write(out);

        out.close();
        file.close();

        Console.LOG("? " + report.getServiceName() + " : "
                + report.getYearMonth() + "-" + report.getNo()
                + " ?", 1);
        programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS);
        programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE);
        programeStatus.setProcessStatus(true);

    } catch (Exception e) {
        e.printStackTrace();
        Console.LOG(e.getMessage(), 0);
        programeStatus.setMessage(ConstantMessage.MSG_PROCESS_FAILS + e.toString());
        programeStatus.setTitle(ConstantMessage.MSG_CONTACT_ADMIN);
        programeStatus.setProcessStatus(false);
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return programeStatus;
}