Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook

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

Introduction

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

Prototype

public HSSFWorkbook(InputStream s) throws IOException

Source Link

Document

Companion to HSSFWorkbook(POIFSFileSystem), this constructs the POI filesystem around your InputStream , including all nodes.

Usage

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

public ProgrameStatus tmdActDetail(OppReport report) {
    int[] indexsCol = new int[] { 0, 1, 2, 3, 4, 5 };
    int count_limit = 0;
    boolean is_beginrow = false;
    int col_last = 11;
    int row_start = 4; // index row
    int row_formula_start = row_start + 1;
    ProgrameStatus programeStatus = new ProgrameStatus();
    List<ObjRptTmdActDetail> listData = new ArrayList<ObjRptTmdActDetail>();

    String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo();
    String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + "";
    try {/*  w w w.  ja v  a  2s. c om*/
        connection = new DBManage().open();
        ThaiMedicineDao tmdDao = new ThaiMedicineDao();
        tmdDao.setConnection(connection);

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

        EXCELL_HEADER1 = report.getTitle1().replace("{No.}", subNoStrStmp(stmp));
        report.setTmdTableName(TABLE_RPT_ACT);
        //EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report);
        EXCELL_HEADER2 = getTitleDateOpd(report);
        EXCELL_HOSPITAL = "?: "
                + StringOpUtil.removeNull(report.getServiceName()) + " ("
                + StringOpUtil.removeNull(report.getServiceCode()) + ")";

        // style Excell
        HSSFWorkbook wbTmd = new HSSFWorkbook(file);
        this.setFontFamily("Arial");
        this.setColorCell(HSSFColor.LIGHT_GREEN.index);
        this.setFontSize(7);
        this.setFontHeaderSize(8);
        this.loadStyle(wbTmd);

        // 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 2 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;
        double sumTotalPay = 0.00;
        int key_rank = 0;
        int autoNumber = 1;
        listData = tmdDao.getListTmdAct(report);
        for (int j = 0; j < listData.size(); j++) {
            ObjRptTmdActDetail data = listData.get(j);

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

            cell = row.createCell(0);
            cell.setCellValue(autoNumber);
            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(csStringCenter);

            cell = row.createCell(4);
            cell.setCellValue(data.getHmain() + ": " + data.getHmainname());
            cell.setCellStyle(csStringLeft);

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

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

            cell = row.createCell(7);
            cell.setCellValue(data.getItem_code() + ": " + data.getItem_desc());
            cell.setCellStyle(csStringLeft);

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

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

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

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

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

            //                System.out.println("key_rank ::==" + key_rank);
            //                System.out.println("data.getRank_hcode ::==" + data.getRank_hcode());
            /*
             Merge
             */
            /*if (key_rank != data.getRank_hcode() && i > 1) {
             count_limit = mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1);
             col1++;
             } else {
             count_limit++;
             }
             key_rank = data.getRank_hcode();
             */
            sumTotalPay += data.getTotalpay();

            curRow++;
            autoNumber++;
        }

        //mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1);
        // 
        row = sheet.createRow(curRow);
        row.setHeight((short) 450);
        cell = row.createCell(0);
        cell.setCellValue("");
        sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 7));
        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);
        row.createCell(7).setCellStyle(csDouble2B);

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

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

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

        /*
         ############ ? ###############
         */
        int rowDateTimeCurrent = curRow + 1;
        // BathText
        row = sheet.createRow((rowDateTimeCurrent));
        cell = row.createCell(0);
        cell.setCellValue("? _"
                + new DateUtil().getDateTimeCurrent());
        sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, 10));
        cell.setCellStyle(csStringB);

        /*
         ############ ? ###############
         */

        /*
         ############ BathText ###############
         */
        /* int rowBathText = curRow + 1;
         // BathText
         row = sheet.createRow((rowBathText));
         cell = row.createCell(0);
         cell.setCellValue(new UtilDao().getThaiBath(Double.parseDouble(new NumberUtil().numberDigiit(sumTotalPay, 2))));
         sheet.addMergedRegion(new CellRangeAddress(rowBathText, rowBathText, 0, 10));
         cell.setCellStyle(csNum4B_R);
         */
        /*
         ############ BathText ###############
         */
        workbookBase.setSheetName(0, report.getServiceCode());// + "  " + report.getServiceName());

        /// int sheetIndex, int startColumn, int endColumn, int startRow, int endRow)                    
        /*sheet.setAutobreaks(false);
        sheet.setColumnHidden(col_last + 1, true);
        sheet.setRowBreak((curRow + 1));
        sheet.setColumnBreak(col_last);
        //wb.setPrintArea(0, "A1:K" + (curRow + 1));
                
        // file out                           
        ExtendedFormatRecord e = new ExtendedFormatRecord();
        e.setShrinkToFit(true);*/

        new FileUtil().mkdirMutiDirectory(pathDirectory);

        out = new FileOutputStream(pathDirectory + "" + File.separator + "thaimedicine_act"
                + StringOpUtil.removeNull(report.getServiceCode()) + "_" + report.getStmp() + ".xls");
        workbookBase.write(out);

        out.close();
        file.close();
        System.out.println("report.getServiceName() ::==" + report.getServiceName());
        Console.LOG("? " + StringOpUtil.removeNull(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;
}

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

public ProgrameStatus tmdMomDetail(OppReport report) {
    int[] indexsCol = new int[] { 0, 1, 2, 3, 4, 5 };
    int count_limit = 0;
    int col_last = 11;
    int row_start = 4; // index row
    int row_formula_start = row_start + 1;
    ProgrameStatus programeStatus = new ProgrameStatus();
    List<ObjRptTmdMomDetail> listData = new ArrayList<ObjRptTmdMomDetail>();

    String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo();
    String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + "";
    try {/*www. jav  a 2  s .co m*/
        connection = new DBManage().open();

        ThaiMedicineDao tmdDao = new ThaiMedicineDao();
        tmdDao.setConnection(connection);

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

        EXCELL_HEADER1 = report.getTitle1().replace("{No.}", subNoStrStmp(stmp));
        report.setTmdTableName(TABLE_RPT_MOM);
        EXCELL_HEADER2 = getTitleDateOpd(report);
        EXCELL_HOSPITAL = "?: "
                + StringOpUtil.removeNull(report.getServiceName()) + " ("
                + StringOpUtil.removeNull(report.getServiceCode()) + ")";

        // style Excell
        HSSFWorkbook wbTmd = new HSSFWorkbook(file);
        this.setFontFamily("Arial");
        this.setFontSize(7);
        this.setColorCell(HSSFColor.LIGHT_GREEN.index);
        this.setFontHeaderSize(8);
        this.loadStyle(wbTmd);

        // 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 2 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;
        double sumTotalPay = 0.00;
        listData = tmdDao.getListTmdMom(report);
        int autoNumber = 1;
        for (int j = 0; j < listData.size(); j++) {
            ObjRptTmdMomDetail data = listData.get(j);

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

            /*                
             Merge   sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 7));              
             */
            //                System.out.println("count_limit ::==" + count_limit);
            //                System.out.println("curRow :;==" + curRow);
            //                System.out.println("j ::==" + (j + row_start));
            cell = row.createCell(0);
            cell.setCellValue(autoNumber);
            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(csStringCenter);

            cell = row.createCell(4);
            cell.setCellValue(data.getHmain() + ": " + data.getHmainname());
            cell.setCellStyle(csStringLeft);

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

            cell = row.createCell(6);
            cell.setCellValue(data.getItem_code() + ": " + data.getItem_desc());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(7);
            cell.setCellValue(data.getCase_place());
            cell.setCellStyle(csStringLeft);

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

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

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

            cell = row.createCell(11);
            cell.setCellValue(data.getInvoice_no());
            cell.setCellStyle(csStringCenter);

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

            /*
             Merge
             */
            /*if (count_limit == SERVICE_LIMIT) {
             System.out.println("write ::==" + col1);
             count_limit = mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1);
             col1++;
                    
             } else {
             System.out.println("count_limit ++");
             count_limit++;
             }
             */
            sumTotalPay += data.getTotalpay();
            curRow++;
            autoNumber++;
        }

        //            if (SERVICE_LIMIT == count_limit) {
        //                mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1);
        //            }
        // 
        row = sheet.createRow(curRow);
        row.setHeight((short) 450);
        cell = row.createCell(0);
        cell.setCellValue("");
        sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 7));
        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);
        row.createCell(7).setCellStyle(csDouble2B);

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

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

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

        /*
         ############ ? ###############
         */
        int rowDateTimeCurrent = curRow + 1;
        // BathText
        row = sheet.createRow((rowDateTimeCurrent));
        cell = row.createCell(0);
        cell.setCellValue("? _"
                + new DateUtil().getDateTimeCurrent());
        sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, 10));
        cell.setCellStyle(csStringB);
        /*
         ############ ? ###############
         */

        /*
         ############ BathText ###############
         */
        /*int rowBathText = curRow + 1;
         // BathText
         row = sheet.createRow((rowBathText));
         cell = row.createCell(0);
         cell.setCellValue(new UtilDao().getThaiBath(Double.parseDouble(new NumberUtil().numberDigiit(sumTotalPay, 2))));
         sheet.addMergedRegion(new CellRangeAddress(rowBathText, rowBathText, 0, 10));
         cell.setCellStyle(csNum4B_R);
         */
        /*
         ############ BathText ###############
         */
        workbookBase.setSheetName(0, report.getServiceCode());// + "  " + report.getServiceName());

        /// int sheetIndex, int startColumn, int endColumn, int startRow, int endRow)                    
        /*sheet.setAutobreaks(false);
        sheet.setColumnHidden(col_last + 1, true);
        sheet.setColumnBreak(col_last);
                
        //wb.setPrintArea(0, "$A$1:$K$" + (curRow + 1));
        sheet.setColumnBreak(col_last);*/

        // file out 
        new FileUtil().mkdirMutiDirectory(pathDirectory);

        out = new FileOutputStream(pathDirectory + "" + File.separator + "thaimedicine_mom"
                + 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;
}

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

public ProgrameStatus tmdActSummary(OppReport report) {
    int col_last = 18;
    int row_start = 6; // index row
    int col_freeze = 3;
    int row_freeze = 6;
    int row_formula_start = row_start + 1;
    ProgrameStatus programeStatus = new ProgrameStatus();
    List<ObjRptTmdActSummary> listData = new ArrayList<ObjRptTmdActSummary>();
    String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo();
    String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + "";
    try {//  ww  w. j ava  2 s.  c o m
        connection = new DBManage().open();
        ThaiMedicineDao tmdDao = new ThaiMedicineDao();
        tmdDao.setConnection(connection);

        file = new FileInputStream(new File("." + File.separator + "xls" + File.separator + "TMD_ACT_SUM.xls"));

        // Top Excell Sheet1
        EXCELL_HEADER1 = report.getTitle1().replace("{No.}", subNoStrStmp(stmp));
        report.setTmdTableName(TABLE_RPT_ACT);
        EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report);

        HSSFWorkbook wbTmd = new HSSFWorkbook(file);
        this.setFontFamily("Arial");
        this.setFontSize(7);
        this.setColorCell(HSSFColor.LIGHT_GREEN.index);
        this.setFontHeaderSize(8);
        this.loadStyle(wbTmd);

        HSSFSheet sheet = workbookBase.getSheetAt(0);
        sheet.setDefaultRowHeightInPoints(100);
        //sheet.createFreezePane(col_freeze, row_freeze);

        // row 0 Header
        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 2 Header
        //            row = sheet.createRow(1);
        //            row.setHeight((short) 390);
        //            cell = row.createCell(0);
        //            cell.setCellValue(EXCELL_HEADER2);
        //            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last));
        //            cell.setCellStyle(csHead);
        int curRow = row_start;
        double sumTotalSumPay = 0.00;
        int i = 1;
        listData = tmdDao.getListSummaryTmdAct(report);
        System.out.println("listData.size() :" + listData.size());
        for (int j = 0; j < listData.size(); j++) {
            ObjRptTmdActSummary objData = listData.get(j);

            row = sheet.createRow(curRow);
            row.setHeight((short) 360);
            cell = row.createCell(0);
            cell.setCellValue(i);
            cell.setCellStyle(csNum4);

            cell = row.createCell(1);
            cell.setCellValue(objData.getHcode());
            cell.setCellStyle(csString2Center);

            cell = row.createCell(2);
            cell.setCellValue(objData.getHcodename());
            cell.setCellStyle(csStringLeft);

            /*
             1
             */
            cell = row.createCell(3);
            cell.setCellValue(objData.getCount_dis_txid1());
            cell.setCellStyle(csNum4R);

            cell = row.createCell(4);
            cell.setCellValue(objData.getSum_point1());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(5);
            cell.setCellValue(objData.getSum_totalpay1());
            cell.setCellStyle(csDouble2);

            /*
             2
             */
            cell = row.createCell(6);
            cell.setCellValue(objData.getCount_dis_txid2());
            cell.setCellStyle(csNum4R);

            cell = row.createCell(7);
            cell.setCellValue(objData.getSum_point2());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(8);
            cell.setCellValue(objData.getSum_totalpay2());
            cell.setCellStyle(csDouble2);

            /*
             3
             */
            cell = row.createCell(9);
            cell.setCellValue(objData.getCount_dis_txid3());
            cell.setCellStyle(csNum4R);

            cell = row.createCell(10);
            cell.setCellValue(objData.getSum_point3());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(11);
            cell.setCellValue(objData.getSum_totalpay3());
            cell.setCellStyle(csDouble2);

            /*
             4
             */
            cell = row.createCell(12);
            cell.setCellValue(objData.getCount_dis_txid4());
            cell.setCellStyle(csNum4R);

            cell = row.createCell(13);
            cell.setCellValue(objData.getSum_point4());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(14);
            cell.setCellValue(objData.getSum_totalpay4());
            cell.setCellStyle(csDouble2);
            /*
             5
             */
            cell = row.createCell(15);
            cell.setCellValue(objData.getCount_dis_txid5());
            cell.setCellStyle(csNum4R);

            cell = row.createCell(16);
            cell.setCellValue(objData.getSum_point5());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(17);
            cell.setCellValue(objData.getSum_totalpay5());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(18);
            cell.setCellValue(objData.getSum_totalpay_all());
            cell.setCellStyle(csDouble2);
            sumTotalSumPay += objData.getSum_totalpay_all();
            curRow++;
            i++;
        }

        /**
         * footer summary total
         */
        row = sheet.createRow(curRow);
        row.setHeight((short) 450);
        cell = row.createCell(0);
        cell.setCellValue("");
        sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 2));
        cell.setCellStyle(csNum4B);

        row.createCell(1).setCellStyle(csHeadTab);
        row.createCell(2).setCellStyle(csHeadTab);

        cell = row.createCell(3);
        cell.setCellFormula(builderFormulaSum(3, row_formula_start, curRow));
        cell.setCellStyle(csNum3B);
        cell = row.createCell(4);
        cell.setCellFormula(builderFormulaSum(4, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);
        cell = row.createCell(5);
        cell.setCellFormula(builderFormulaSum(5, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);
        cell = row.createCell(6);
        cell.setCellFormula(builderFormulaSum(6, row_formula_start, curRow));
        cell.setCellStyle(csNum3B);
        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(csNum3B);
        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(csNum3B);
        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(csNum3B);
        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);

        /*
         ############ ? ###############
         */
        int rowDateTimeCurrent = curRow + 1;
        // BathText
        row = sheet.createRow((rowDateTimeCurrent));
        cell = row.createCell(0);
        cell.setCellValue("? _"
                + new DateUtil().getDateTimeCurrent());
        sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, 10));
        cell.setCellStyle(csStringB);

        /*
         ############ ? ###############
         */

        /*
         ################# bathTaxt #####################
         */
        /*System.out.println("(curRow+1) ::=="+(curRow+1));
         CellReference cellReference = new CellReference("S"+(curRow+1)); 
         Row row = sheet.getRow(cellReference.getRow());
         Cell cell = row.getCell(cellReference.getCol());             
         System.out.println("cell.getCellFormula() ::=="+cell.getCellFormula());
         double totalPay = cell.getNumericCellValue();
         int rowBathText = curRow + 1;
         // BathText
         row = sheet.createRow((rowBathText));
         cell = row.createCell(0);
         cell.setCellValue(new UtilDao().getThaiBath(Double.parseDouble(new NumberUtil().numberDigiit(sumTotalSumPay, 2))));
         sheet.addMergedRegion(new CellRangeAddress(rowBathText, rowBathText, 0, 18));
         cell.setCellStyle(csNum4B_R);
         */
        /*
         ################# bathTaxt #####################
         */
        new FileUtil().mkdirMutiDirectory(pathDirectory);

        //write file Excell
        out = new FileOutputStream(pathDirectory + "" + File.separator + "tmdact_summary_"
                + report.getYearMonth() + "-" + report.getNo() + ".xls");
        workbookBase.write(out);
        out.close();
        file.close();
        Console.LOG(
                " ???? ???",
                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;
}

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

public ProgrameStatus tmdMomSummary(OppReport report) {
    int col_last = 7;
    int row_start = 4; // index row
    int row_freeze = 4;
    int col_freeze = 3;
    int row_formula_start = row_start + 1;
    ProgrameStatus programeStatus = new ProgrameStatus();
    List<ObjRptTmdMomSummary> listData = new ArrayList<ObjRptTmdMomSummary>();
    String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo();
    String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + "";
    try {//from www.  jav  a2  s .  co  m
        connection = new DBManage().open();
        ThaiMedicineDao tmdDao = new ThaiMedicineDao();
        tmdDao.setConnection(connection);

        file = new FileInputStream(new File("." + File.separator + "xls" + File.separator + "TMD_MOM_SUM.xls"));

        // Top Excell Sheet1
        EXCELL_HEADER1 = report.getTitle1().replace("{No.}", subNoStrStmp(stmp));
        report.setTmdTableName(TABLE_RPT_MOM);
        EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report);

        HSSFWorkbook wbTmd = new HSSFWorkbook(file);
        this.setFontFamily("Arial");
        this.setFontSize(7);
        this.setColorCell(HSSFColor.LIGHT_GREEN.index);
        this.setFontHeaderSize(8);
        this.loadStyle(wbTmd);

        HSSFSheet sheet = workbookBase.getSheetAt(0);
        //sheet.createFreezePane(col_freeze, row_freeze);

        // row 0 Header
        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 2 Header
        //            row = sheet.createRow(1);
        //            row.setHeight((short) 390);
        //            cell = row.createCell(0);
        //            cell.setCellValue(EXCELL_HEADER2);
        //            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last));
        //            cell.setCellStyle(csHead);
        int curRow = row_start;
        int i = 1;
        double sumTotalSumPay = 0.00;
        listData = tmdDao.getListSummaryTmdMom(report);
        System.out.println("listData.size() :" + listData.size());
        for (int j = 0; j < listData.size(); j++) {
            ObjRptTmdMomSummary objData = listData.get(j);

            row = sheet.createRow(curRow);
            row.setHeight((short) 360);
            cell = row.createCell(0);
            cell.setCellValue(i);
            cell.setCellStyle(csNum4);

            cell = row.createCell(1);
            cell.setCellValue(objData.getHcode());
            cell.setCellStyle(csString2Center);

            cell = row.createCell(2);
            cell.setCellValue(objData.getHcodename());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(3);
            cell.setCellValue(objData.getCount_in_hosp());
            cell.setCellStyle(csNum4R);

            cell = row.createCell(4);
            cell.setCellValue(objData.getSum_in_hosp());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(5);
            cell.setCellValue(objData.getCount_out_hosp());
            cell.setCellStyle(csNum4R);

            cell = row.createCell(6);
            cell.setCellValue(objData.getSum_out_hosp());
            cell.setCellStyle(csDouble2);

            cell = row.createCell(7);
            cell.setCellValue(objData.getSum_totalpay());
            cell.setCellStyle(csDouble2);

            sumTotalSumPay += objData.getSum_totalpay();
            curRow++;
            i++;
        }

        /**
         * footer summary total
         */
        row = sheet.createRow(curRow);
        row.setHeight((short) 450);
        cell = row.createCell(0);
        cell.setCellValue("");
        sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 2));
        cell.setCellStyle(csNum4B);

        row.createCell(1).setCellStyle(csHeadTab);
        row.createCell(2).setCellStyle(csHeadTab);

        cell = row.createCell(3);
        cell.setCellFormula(builderFormulaSum(3, row_formula_start, curRow));
        cell.setCellStyle(csNum4BR);
        cell = row.createCell(4);
        cell.setCellFormula(builderFormulaSum(4, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);
        cell = row.createCell(5);
        cell.setCellFormula(builderFormulaSum(5, row_formula_start, curRow));
        cell.setCellStyle(csNum4BR);
        cell = row.createCell(6);
        cell.setCellFormula(builderFormulaSum(6, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);
        cell = row.createCell(7);
        cell.setCellFormula(builderFormulaSum(7, row_formula_start, curRow));
        cell.setCellStyle(csDouble2B);

        /*
         ############ ? ###############
         */
        int rowDateTimeCurrent = curRow + 1;
        // BathText
        row = sheet.createRow((rowDateTimeCurrent));
        cell = row.createCell(0);
        cell.setCellValue("? _"
                + new DateUtil().getDateTimeCurrent());
        sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, 10));
        cell.setCellStyle(csStringB);

        /*
         ############ ? ###############
         */

        /*
         ############ BathText ###############
         */
        /* int rowBathText = curRow + 1;
         // BathText
         row = sheet.createRow((rowBathText));
         cell = row.createCell(0);
         cell.setCellValue(new UtilDao().getThaiBath(Double.parseDouble(new NumberUtil().numberDigiit(sumTotalSumPay, 2))));
         sheet.addMergedRegion(new CellRangeAddress(rowBathText, rowBathText, 0, 7));
         cell.setCellStyle(csNum4B_R);
         */
        /*
         ################# bathTaxt #####################
         */
        new FileUtil().mkdirMutiDirectory(pathDirectory);

        //write file Excell
        out = new FileOutputStream(pathDirectory + "" + File.separator + "tmdmom_summary_"
                + report.getYearMonth() + "-" + report.getNo() + ".xls");
        workbookBase.write(out);
        out.close();
        file.close();
        Console.LOG(
                "??????? ???",
                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;
}

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

public ProgrameStatus hc11535Detail(OppReport report) {
    ProgrameStatus programeStatus = new ProgrameStatus();
    int col_last = 20;
    int row_start = 8; // index row
    int row_formula_start = row_start + 1;
    List<ObjRptVajiraHc11535Detail> listData = new ArrayList<ObjRptVajiraHc11535Detail>();

    String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo();
    String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + "";
    try {/* www.j a v a 2  s  .  c  o  m*/
        connection = new DBManage().open();

        Vajira11535Dao vajiraHCDao = new Vajira11535Dao();
        vajiraHCDao.setConnection(connection);

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

        EXCELL_HEADER1 = HEADER_DETAIL.replace("{YEAR}",
                new DateUtil().getBudgeMonthYear_543(stmp, ConstantVariable.BUDGET_MONTH));
        //EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report);
        EXCELL_HEADER2 = report.getTitle2();
        EXCELL_HOSPITAL = "?: "
                + StringOpUtil.removeNull(report.getServiceName()) + " ("
                + StringOpUtil.removeNull(report.getServiceCode()) + ")";

        // style Excell
        this.setFontFamily("TH SarabunPSK");
        //this.setColorCell(HSSFColor.LIGHT_GREEN.index);
        this.loadStyle(new HSSFWorkbook(file));

        // Start sheet 1 
        HSSFSheet sheet = workbookBase.getSheetAt(0);
        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 2 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 autoNumber = 1;
        listData = vajiraHCDao.getListVajiraDetail(report);

        for (int i = 0; i < listData.size(); i++) {
            ObjRptVajiraHc11535Detail objData = listData.get(i);

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

            cell = row.createCell(0);
            cell.setCellValue(autoNumber);
            cell.setCellStyle(csNum3);

            cell = row.createCell(1);
            cell.setCellValue(objData.getPid());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(2);
            cell.setCellValue(objData.getHn());
            cell.setCellStyle(csStringCenter);

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

            cell = row.createCell(4);
            cell.setCellValue(objData.getHmain() + " : " + objData.getHmain_name());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(5);
            cell.setCellValue(objData.getHmainip() + " : " + objData.getHmainip_name());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(6);
            cell.setCellValue(objData.getDateopd());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(7);
            cell.setCellValue(objData.getPdxcode());
            cell.setCellStyle(csStringCenter);

            cell = row.createCell(8);
            cell.setCellValue(objData.getChrg_hc());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(9);
            cell.setCellValue(objData.getChrg_202());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(10);
            cell.setCellValue(objData.getChrg_stditem());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(11);
            cell.setCellValue(objData.getChrg_other());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(12);
            cell.setCellValue(objData.getChrg_total());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(13);
            cell.setCellValue(objData.getPaid_model());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(14);
            cell.setCellValue(objData.getPaid_202());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(15);
            cell.setCellValue(objData.getPaid_stditem());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(16);
            cell.setCellValue(objData.getPaid_other());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(17);
            cell.setCellValue(objData.getPaid_total());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(18);
            cell.setCellValue(objData.getReimburse());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(19);
            cell.setCellValue(objData.getPoint());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(20);
            cell.setCellValue(objData.getTotalreimburse());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(21);
            cell.setCellValue(objData.getInvoice_no());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(22);
            cell.setCellValue(objData.getTxid());
            cell.setCellStyle(csStringtxid);

            curRow++;
            autoNumber++;
        }

        System.out.println("row.getPhysicalNumberOfCells(); ::==" + row.getPhysicalNumberOfCells());

        //mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1);
        // 
        row = sheet.createRow(curRow);
        row.setHeight((short) 450);
        cell = row.createCell(0);
        cell.setCellValue("");
        sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 7));
        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);
        row.createCell(7).setCellStyle(csNum4B);

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

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

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

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

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

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

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

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

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

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

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

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

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

        /*
         ############ ? ###############
         */
        int rowDateTimeCurrent = curRow + 1;
        // BathText
        row = sheet.createRow((rowDateTimeCurrent));
        cell = row.createCell(0);
        cell.setCellValue("? _"
                + new DateUtil().getDateTimeCurrent());
        sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, col_last));
        cell.setCellStyle(csStringB);

        System.out.println("sheet.getLastRowNum(); ::==" + sheet.getLastRowNum());

        /*
         ############ ? ###############
         */
        workbookBase.setSheetName(0, report.getServiceCode());// + "  " + report.getServiceName());

        new FileUtil().mkdirMutiDirectory(pathDirectory);

        out = new FileOutputStream(pathDirectory + "" + File.separator + FILE_NAME_DETAIL
                + StringOpUtil.removeNull(report.getServiceCode()) + "_" + report.getStmp() + ".xls");
        workbookBase.write(out);

        out.close();
        file.close();
        Console.LOG("? " + StringOpUtil.removeNull(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();
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return programeStatus;
}

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

public ProgrameStatus hc11535Summary(OppReport report) {
    ProgrameStatus programeStatus = new ProgrameStatus();
    int col_last = 17;
    int row_start = 8; // index row
    int row_formula_start = row_start + 1;
    List<ObjRptVajiraHc11535Summary> listData = new ArrayList<ObjRptVajiraHc11535Summary>();

    String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo();
    String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + "";
    try {//from   w  w  w .  j a va  2s .c om
        connection = new DBManage().open();

        Vajira11535Dao vajiraREDao = new Vajira11535Dao();
        vajiraREDao.setConnection(connection);

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

        EXCELL_HEADER1 = HEADER_DETAIL.replace("{YEAR}",
                new DateUtil().getBudgeMonthYear_543(stmp, ConstantVariable.BUDGET_MONTH));
        //EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report);
        EXCELL_HEADER2 = report.getTitle2();
        EXCELL_HOSPITAL = "?: ? (11535)";

        // style Excell
        this.setFontFamily("TH SarabunPSK");
        //this.setColorCell(HSSFColor.LIGHT_GREEN.index);
        this.loadStyle(new HSSFWorkbook(file));

        // Start sheet 1 
        HSSFSheet sheet = workbookBase.getSheetAt(0);
        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 2 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 autoNumber = 1;
        listData = vajiraREDao.getListVajiraSumGroupHmain(report);

        for (int i = 0; i < listData.size(); i++) {
            ObjRptVajiraHc11535Summary objData = listData.get(i);

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

            cell = row.createCell(0);
            cell.setCellValue(autoNumber);
            cell.setCellStyle(csNum3);

            cell = row.createCell(1);
            cell.setCellValue(objData.getHmain());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(2);
            cell.setCellValue(objData.getHmain_name());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(3);
            cell.setCellValue(objData.getCount_pid());
            cell.setCellStyle(csNum3);

            cell = row.createCell(4);
            cell.setCellValue(objData.getCount_txid());
            cell.setCellStyle(csNum3);

            cell = row.createCell(5);
            cell.setCellValue(objData.getSum_chrg_hc());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(6);
            cell.setCellValue(objData.getSum_chrg_202());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(7);
            cell.setCellValue(objData.getSum_chrg_stditem());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(8);
            cell.setCellValue(objData.getSum_chrg_other());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(9);
            cell.setCellValue(objData.getSum_chrg_total());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(10);
            cell.setCellValue(objData.getSum_paid_hc());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(11);
            cell.setCellValue(objData.getSum_paid_202());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(12);
            cell.setCellValue(objData.getSum_paid_stditem());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(13);
            cell.setCellValue(objData.getSum_paid_other());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(14);
            cell.setCellValue(objData.getSum_paid_total());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(15);
            cell.setCellValue(objData.getSum_point());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(16);
            cell.setCellValue(objData.getSum_reimburse());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(17);
            cell.setCellValue(objData.getSum_totalreimburse());
            cell.setCellStyle(csDouble2R);

            curRow++;
            autoNumber++;
        }

        //mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1);
        // 
        row = sheet.createRow(curRow);
        row.setHeight((short) 450);
        cell = row.createCell(0);
        cell.setCellValue("");
        sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 2));
        cell.setCellStyle(csNum4B);

        row.createCell(1).setCellStyle(csString2Center);
        row.createCell(2).setCellStyle(csString2Center);

        cell = row.createCell(3);
        cell.setCellFormula(builderFormulaSumRound(3, row_formula_start, curRow, 0));
        cell.setCellStyle(csNum4BCenter);

        cell = row.createCell(4);
        cell.setCellFormula(builderFormulaSumRound(4, row_formula_start, curRow, 0));
        cell.setCellStyle(csNum4BCenter);

        cell = row.createCell(5);
        cell.setCellFormula(builderFormulaSumRound(5, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(6);
        cell.setCellFormula(builderFormulaSumRound(6, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

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

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

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

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

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

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

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

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

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

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

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

        /*
         ############ ? ###############
         */
        int rowDateTimeCurrent = curRow + 1;
        // BathText
        row = sheet.createRow((rowDateTimeCurrent));
        cell = row.createCell(0);
        cell.setCellValue("? _"
                + new DateUtil().getDateTimeCurrent());
        sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, col_last));
        cell.setCellStyle(csStringB);

        /*
         ############ ? ###############
         */
        workbookBase.setSheetName(0, report.getServiceCode());// + "  " + report.getServiceName());

        new FileUtil().mkdirMutiDirectory(pathDirectory);

        out = new FileOutputStream(pathDirectory + "" + File.separator + "vajira_summary_11535_"
                + report.getYearMonth() + "-" + report.getNo() + ".xls");

        workbookBase.write(out);
        out.close();
        file.close();
        Console.LOG(
                "??? Op? ? ???",
                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;
}

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

public ProgrameStatus hcHcDetail(OppReport report) {
    ProgrameStatus programeStatus = new ProgrameStatus();
    int col_last = 20;
    int row_start = 8; // index row
    int row_formula_start = row_start + 1;
    List<ObjRptVajiraHcDetail> listData = new ArrayList<ObjRptVajiraHcDetail>();

    String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo();
    String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + "";
    String yearTitle = new DateUtil().getBudgeMonthYear_543(stmp, ConstantVariable.BUDGET_MONTH);
    try {/*from w  w  w .  j  a v  a 2s .  com*/
        connection = new DBManage().open();

        VajiraHCDao vajiraHCDao = new VajiraHCDao();
        vajiraHCDao.setConnection(connection);

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

        EXCELL_HEADER1 = HEADER_DETAIL.replace("{YEAR}",
                new DateUtil().getBudgeMonthYear_543(stmp, ConstantVariable.BUDGET_MONTH));
        //EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report);
        EXCELL_HEADER2 = report.getTitle2();
        EXCELL_HOSPITAL = "?: "
                + StringOpUtil.removeNull(report.getServiceName()) + " ("
                + StringOpUtil.removeNull(report.getServiceCode()) + ")";

        // style Excell
        this.setFontFamily("TH SarabunPSK");
        //this.setColorCell(HSSFColor.LIGHT_GREEN.index);
        this.loadStyle(new HSSFWorkbook(file));

        // Start sheet 1 
        HSSFSheet sheet = workbookBase.getSheetAt(0);
        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 2 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 autoNumber = 1;
        listData = vajiraHCDao.getListVajiraDetail(report);

        for (int i = 0; i < listData.size(); i++) {
            ObjRptVajiraHcDetail objData = listData.get(i);

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

            cell = row.createCell(0);
            cell.setCellValue(autoNumber);
            cell.setCellStyle(csNum3);

            cell = row.createCell(1);
            cell.setCellValue(objData.getPid());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(2);
            cell.setCellValue(objData.getHn());
            cell.setCellStyle(csStringCenter);

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

            cell = row.createCell(4);
            cell.setCellValue(objData.getHmain() + " : " + objData.getHmain_name());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(5);
            cell.setCellValue(objData.getHmainip() + " : " + objData.getHmainip_name());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(6);
            cell.setCellValue(objData.getDateopd_th());
            cell.setCellStyle(csStringCenter);

            cell = row.createCell(7);
            cell.setCellValue(objData.getPdxcode());
            cell.setCellStyle(csStringCenter);

            cell = row.createCell(8);
            cell.setCellValue(objData.getChrg_hc());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(9);
            cell.setCellValue(objData.getChrg_202());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(10);
            cell.setCellValue(objData.getChrg_stditem());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(11);
            cell.setCellValue(objData.getChrg_other());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(12);
            cell.setCellValue(objData.getChrg_total());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(13);
            cell.setCellValue(objData.getPaid_model());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(14);
            cell.setCellValue(objData.getPaid_202());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(15);
            cell.setCellValue(objData.getPaid_stditem());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(16);
            cell.setCellValue(objData.getPaid_other());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(17);
            cell.setCellValue(objData.getPaid_total());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(18);
            cell.setCellValue(objData.getReimburse());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(19);
            cell.setCellValue(objData.getPoint());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(20);
            cell.setCellValue(objData.getTotalreimburse());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(21);
            cell.setCellValue(objData.getInvoice_no());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(22);
            cell.setCellValue(objData.getTxid());
            cell.setCellStyle(csStringtxid);

            curRow++;
            autoNumber++;
        }

        //mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1);
        // 
        row = sheet.createRow(curRow);
        row.setHeight((short) 450);
        cell = row.createCell(0);
        cell.setCellValue("");
        sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 7));
        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);
        row.createCell(7).setCellStyle(csNum4B);

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

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

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

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

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

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

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

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

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

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

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

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

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

        /*
         ############ ? ###############
         */
        int rowDateTimeCurrent = curRow + 1;
        // BathText
        row = sheet.createRow((rowDateTimeCurrent));
        cell = row.createCell(0);
        cell.setCellValue("? _"
                + new DateUtil().getDateTimeCurrent());
        sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, col_last));
        cell.setCellStyle(csStringB);

        System.out.println("sheet.getLastRowNum(); ::==" + sheet.getLastRowNum());

        /*
         ############ ? ###############
         */
        workbookBase.setSheetName(0, report.getServiceCode());// + "  " + report.getServiceName());

        new FileUtil().mkdirMutiDirectory(pathDirectory);

        out = new FileOutputStream(pathDirectory + "" + File.separator + "vajira_HC_"
                + StringOpUtil.removeNull(report.getServiceCode()) + "_" + report.getStmp() + ".xls");
        workbookBase.write(out);

        out.close();
        file.close();
        Console.LOG("? " + StringOpUtil.removeNull(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();
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return programeStatus;
}

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

public ProgrameStatus hcHcSummary(OppReport report) {
    ProgrameStatus programeStatus = new ProgrameStatus();
    int col_last = 17;
    int row_start = 8; // index row
    int row_formula_start = row_start + 1;
    List<ObjRptVajiraHcSummary> listData = new ArrayList<ObjRptVajiraHcSummary>();

    String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo();
    String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + "";
    try {//from  www.j  a  v a  2 s .  c  o m
        connection = new DBManage().open();

        VajiraHCDao vajiraHCDao = new VajiraHCDao();
        vajiraHCDao.setConnection(connection);

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

        EXCELL_HEADER1 = HEADER_DETAIL.replace("{YEAR}",
                new DateUtil().getBudgeMonthYear_543(stmp, ConstantVariable.BUDGET_MONTH));
        //EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report);
        EXCELL_HEADER2 = report.getTitle2();
        EXCELL_HOSPITAL = "?: "
                + StringOpUtil.removeNull(report.getServiceName()) + " ("
                + StringOpUtil.removeNull(report.getServiceCode()) + ")";

        // style Excell
        this.setFontFamily("TH SarabunPSK");
        //this.setColorCell(HSSFColor.LIGHT_GREEN.index);
        this.loadStyle(new HSSFWorkbook(file));

        // Start sheet 1 
        HSSFSheet sheet = workbookBase.getSheetAt(0);
        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 2 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 autoNumber = 1;
        listData = vajiraHCDao.getListVajiraSumGroupHmain(report);

        for (int i = 0; i < listData.size(); i++) {
            ObjRptVajiraHcSummary objData = listData.get(i);

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

            cell = row.createCell(0);
            cell.setCellValue(autoNumber);
            cell.setCellStyle(csNum3);

            cell = row.createCell(1);
            cell.setCellValue(objData.getHmain());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(2);
            cell.setCellValue(objData.getHmain_name());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(3);
            cell.setCellValue(objData.getCount_pid());
            cell.setCellStyle(csNum3);

            cell = row.createCell(4);
            cell.setCellValue(objData.getCount_txid());
            cell.setCellStyle(csNum3);

            cell = row.createCell(5);
            cell.setCellValue(objData.getSum_chrg_hc());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(6);
            cell.setCellValue(objData.getSum_chrg_202());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(7);
            cell.setCellValue(objData.getSum_chrg_stditem());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(8);
            cell.setCellValue(objData.getSum_chrg_other());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(9);
            cell.setCellValue(objData.getSum_chrg_total());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(10);
            cell.setCellValue(objData.getSum_paid_hc());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(11);
            cell.setCellValue(objData.getSum_paid_202());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(12);
            cell.setCellValue(objData.getSum_paid_stditem());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(13);
            cell.setCellValue(objData.getSum_paid_other());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(14);
            cell.setCellValue(objData.getSum_paid_total());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(15);
            cell.setCellValue(objData.getSum_point());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(16);
            cell.setCellValue(objData.getSum_reimburse());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(17);
            cell.setCellValue(objData.getSum_totalreimburse());
            cell.setCellStyle(csDouble2R);

            curRow++;
            autoNumber++;
        }

        //mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1);
        // 
        row = sheet.createRow(curRow);
        row.setHeight((short) 450);
        cell = row.createCell(0);
        cell.setCellValue("");
        sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 2));
        cell.setCellStyle(csNum4B);

        row.createCell(1).setCellStyle(csString2Center);
        row.createCell(2).setCellStyle(csString2Center);

        cell = row.createCell(3);
        cell.setCellFormula(builderFormulaSumRound(3, row_formula_start, curRow, 0));
        cell.setCellStyle(csNum4BCenter);

        cell = row.createCell(4);
        cell.setCellFormula(builderFormulaSumRound(4, row_formula_start, curRow, 0));
        cell.setCellStyle(csNum4BCenter);

        cell = row.createCell(5);
        cell.setCellFormula(builderFormulaSumRound(5, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(6);
        cell.setCellFormula(builderFormulaSumRound(6, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

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

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

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

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

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

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

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

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

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

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

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

        /*
         ############ ? ###############
         */
        int rowDateTimeCurrent = curRow + 1;
        // BathText
        row = sheet.createRow((rowDateTimeCurrent));
        cell = row.createCell(0);
        cell.setCellValue("? _"
                + new DateUtil().getDateTimeCurrent());
        sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, col_last));
        cell.setCellStyle(csStringB);

        /*
         ############ ? ###############
         */
        workbookBase.setSheetName(0, report.getServiceCode());// + "  " + report.getServiceName());

        new FileUtil().mkdirMutiDirectory(pathDirectory);

        out = new FileOutputStream(pathDirectory + "" + File.separator + "vajira_summary_HC_"
                + report.getYearMonth() + "-" + report.getNo() + ".xls");

        workbookBase.write(out);
        out.close();
        file.close();
        Console.LOG(
                "??? Op? ? ???",
                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;
}

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

public ProgrameStatus hcRfDetail(OppReport report) {
    ProgrameStatus programeStatus = new ProgrameStatus();
    int col_last = 20;
    int row_start = 7; // index row
    int row_formula_start = row_start + 1;
    List<ObjRptVajiraRfDetail> listData = new ArrayList<ObjRptVajiraRfDetail>();

    String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo();
    String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + "";
    try {//from ww  w  .j a  v a 2  s. c  o  m
        connection = new DBManage().open();

        VajiraREDao vajiraREDao = new VajiraREDao();
        vajiraREDao.setConnection(connection);

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

        EXCELL_HEADER1 = HEADER_DETAIL.replace("{YEAR}",
                new DateUtil().getBudgeMonthYear_543(stmp, ConstantVariable.BUDGET_MONTH));
        //EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report);
        EXCELL_HEADER2 = report.getTitle2();
        EXCELL_HOSPITAL = "?: "
                + StringOpUtil.removeNull(report.getServiceName()) + " ("
                + StringOpUtil.removeNull(report.getServiceCode()) + ")";

        // style Excell
        this.setFontFamily("TH SarabunPSK");
        //this.setColorCell(HSSFColor.LIGHT_GREEN.index);
        this.loadStyle(new HSSFWorkbook(file));

        // Start sheet 1 
        HSSFSheet sheet = workbookBase.getSheetAt(0);
        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 2 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 autoNumber = 1;
        listData = vajiraREDao.getListVajiraDetail(report);

        for (int i = 0; i < listData.size(); i++) {
            ObjRptVajiraRfDetail objData = listData.get(i);

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

            cell = row.createCell(0);
            cell.setCellValue(autoNumber);
            cell.setCellStyle(csNum3);

            cell = row.createCell(1);
            cell.setCellValue(objData.getPid());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(2);
            cell.setCellValue(objData.getHn());
            cell.setCellStyle(csNum4);

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

            cell = row.createCell(4);
            cell.setCellValue(objData.getHmain() + " : " + objData.getHmain_name());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(5);
            cell.setCellValue(objData.getHmainip() + " : " + objData.getHmainip_name());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(6);
            cell.setCellValue(objData.getDateopd_th());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(7);
            cell.setCellValue(objData.getPdxcode());
            cell.setCellStyle(csStringCenter);

            cell = row.createCell(8);
            cell.setCellValue(objData.getChrg_hc());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(9);
            cell.setCellValue(objData.getChrg_202());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(10);
            cell.setCellValue(objData.getChrg_stditem());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(11);
            cell.setCellValue(objData.getChrg_other());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(12);
            cell.setCellValue(objData.getChrg_total());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(13);
            cell.setCellValue(objData.getPaid_model());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(14);
            cell.setCellValue(objData.getPaid_202());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(15);
            cell.setCellValue(objData.getPaid_stditem());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(16);
            cell.setCellValue(objData.getPaid_other());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(17);
            cell.setCellValue(objData.getTotalreimburse());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(18);
            cell.setCellValue(objData.getInvoice_no());
            cell.setCellStyle(csString2);

            cell = row.createCell(19);
            cell.setCellValue(objData.getTxid());
            cell.setCellStyle(csStringtxid);

            curRow++;
            autoNumber++;
        }

        //mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1);
        // 
        row = sheet.createRow(curRow);
        row.setHeight((short) 450);
        cell = row.createCell(0);
        cell.setCellValue("");
        sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 7));
        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);
        row.createCell(7).setCellStyle(csNum4B);

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

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

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

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

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

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

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

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

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

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

        /*
         ############ ? ###############
         */
        int rowDateTimeCurrent = curRow + 1;
        // BathText
        row = sheet.createRow((rowDateTimeCurrent));
        cell = row.createCell(0);
        cell.setCellValue("? _"
                + new DateUtil().getDateTimeCurrent());
        sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, col_last));
        cell.setCellStyle(csStringB);

        /*
         ############ ? ###############
         */
        workbookBase.setSheetName(0, report.getServiceCode());// + "  " + report.getServiceName());

        new FileUtil().mkdirMutiDirectory(pathDirectory);

        out = new FileOutputStream(pathDirectory + "" + File.separator + FILE_NAME_DETAIL
                + StringOpUtil.removeNull(report.getServiceCode()) + "_" + report.getStmp() + ".xls");
        workbookBase.write(out);

        out.close();
        file.close();
        Console.LOG("? " + StringOpUtil.removeNull(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();
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return programeStatus;
}

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

public ProgrameStatus hcRFSummary(OppReport report) {
    ProgrameStatus programeStatus = new ProgrameStatus();
    int col_last = 17;
    int row_start = 8; // index row
    int row_formula_start = row_start + 1;
    List<ObjRptVajiraRfSummary> listData = new ArrayList<ObjRptVajiraRfSummary>();

    String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo();
    String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + "";
    try {/*w w  w  .  j  a v  a  2 s .c  o  m*/
        connection = new DBManage().open();

        VajiraREDao vajiraREDao = new VajiraREDao();
        vajiraREDao.setConnection(connection);

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

        EXCELL_HEADER1 = HEADER_DETAIL.replace("{YEAR}",
                new DateUtil().getBudgeMonthYear_543(stmp, ConstantVariable.BUDGET_MONTH));
        //EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report);
        EXCELL_HEADER2 = report.getTitle2();
        EXCELL_HOSPITAL = "?: "
                + StringOpUtil.removeNull(report.getServiceName()) + " ("
                + StringOpUtil.removeNull(report.getServiceCode()) + ")";

        // style Excell
        this.setFontFamily("TH SarabunPSK");
        //this.setColorCell(HSSFColor.LIGHT_GREEN.index);
        this.loadStyle(new HSSFWorkbook(file));

        // Start sheet 1 
        HSSFSheet sheet = workbookBase.getSheetAt(0);
        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 2 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 autoNumber = 1;
        listData = vajiraREDao.getListVajiraSumGroupHcode(report);

        for (int i = 0; i < listData.size(); i++) {
            ObjRptVajiraRfSummary objData = listData.get(i);

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

            cell = row.createCell(0);
            cell.setCellValue(autoNumber);
            cell.setCellStyle(csNum3);

            cell = row.createCell(1);
            cell.setCellValue(objData.getHcode());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(2);
            cell.setCellValue(objData.getHcode_name());
            cell.setCellStyle(csStringLeft);

            cell = row.createCell(3);
            cell.setCellValue(objData.getCount_pid());
            cell.setCellStyle(csNum3);

            cell = row.createCell(4);
            cell.setCellValue(objData.getCount_txid());
            cell.setCellStyle(csNum3);

            cell = row.createCell(5);
            cell.setCellValue(objData.getSum_chrg_hc());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(6);
            cell.setCellValue(objData.getSum_chrg_202());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(7);
            cell.setCellValue(objData.getSum_chrg_stditem());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(8);
            cell.setCellValue(objData.getSum_chrg_other());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(9);
            cell.setCellValue(objData.getSum_chrg_total());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(10);
            cell.setCellValue(objData.getSum_paid_hc());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(11);
            cell.setCellValue(objData.getSum_paid_202());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(12);
            cell.setCellValue(objData.getSum_paid_stditem());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(13);
            cell.setCellValue(objData.getSum_paid_other());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(14);
            cell.setCellValue(objData.getSum_paid_total());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(15);
            cell.setCellValue(objData.getSum_point());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(16);
            cell.setCellValue(objData.getSum_reimburse());
            cell.setCellStyle(csDouble2R);

            cell = row.createCell(17);
            cell.setCellValue(objData.getSum_totalreimburse());
            cell.setCellStyle(csDouble2R);

            curRow++;
            autoNumber++;
        }

        //mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1);
        // 
        row = sheet.createRow(curRow);
        row.setHeight((short) 450);
        cell = row.createCell(0);
        cell.setCellValue("");
        sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 2));
        cell.setCellStyle(csNum4B);

        row.createCell(1).setCellStyle(csString2Center);
        row.createCell(2).setCellStyle(csString2Center);

        cell = row.createCell(3);
        cell.setCellFormula(builderFormulaSumRound(3, row_formula_start, curRow, 0));
        cell.setCellStyle(csNum4BCenter);

        cell = row.createCell(4);
        cell.setCellFormula(builderFormulaSumRound(4, row_formula_start, curRow, 0));
        cell.setCellStyle(csNum4BCenter);

        cell = row.createCell(5);
        cell.setCellFormula(builderFormulaSumRound(5, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

        cell = row.createCell(6);
        cell.setCellFormula(builderFormulaSumRound(6, row_formula_start, curRow, 0));
        cell.setCellStyle(csDouble2B);

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

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

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

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

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

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

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

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

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

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

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

        /*
         ############ ? ###############
         */
        int rowDateTimeCurrent = curRow + 1;
        // BathText
        row = sheet.createRow((rowDateTimeCurrent));
        cell = row.createCell(0);
        cell.setCellValue("? _"
                + new DateUtil().getDateTimeCurrent());
        sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, col_last));
        cell.setCellStyle(csStringB);

        /*
         ############ ? ###############
         */
        workbookBase.setSheetName(0, report.getServiceCode());// + "  " + report.getServiceName());

        new FileUtil().mkdirMutiDirectory(pathDirectory);

        out = new FileOutputStream(pathDirectory + "" + File.separator + FILE_NAME_SUM + report.getYearMonth()
                + "-" + report.getNo() + ".xls");

        workbookBase.write(out);
        out.close();
        file.close();
        Console.LOG(
                "??? Op? ? ???",
                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;
}