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

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

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

Introduction

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

Prototype

@Override
public void setColumnWidth(int columnIndex, int width)

Source Link

Document

Set the width (in units of 1/256th of a character width)

The maximum column width for an individual cell is 255 characters.

Usage

From source file:com.brick.customer.util.CustomerInfoExcel.java

@SuppressWarnings("unchecked")
public HSSFWorkbook createReport(Map<String, Object> params, Context context) throws Exception {

    ExcelFileWriter efw = new ExcelFileWriter();
    HSSFSheet sheet = efw.createSheet(context.contextMap.get("sheetName") == null ? "summary"
            : (String) context.contextMap.get("sheetName"));
    List<HashMap<String, Object>> list = (List<HashMap<String, Object>>) params.get("cusInfo");

    sheet.setColumnWidth(0, 5000);
    sheet.setColumnWidth(1, 3000);//  ww  w .j a v  a  2 s .  c  om
    sheet.setColumnWidth(2, 5300);
    sheet.setColumnWidth(3, 3600);
    sheet.setColumnWidth(4, 4600);
    sheet.setColumnWidth(5, 10000);
    sheet.setColumnWidth(6, 6000);
    sheet.setColumnWidth(7, 4000);
    sheet.setColumnWidth(8, 3500);
    sheet.setColumnWidth(9, 3500);
    sheet.setColumnWidth(10, 3500);
    sheet.setColumnWidth(11, 4200);
    sheet.setColumnWidth(12, 4200);
    sheet.setColumnWidth(13, 4200);
    sheet.setColumnWidth(14, 4800);
    sheet.setColumnWidth(15, 5000);
    sheet.setColumnWidth(16, 5000);
    sheet.setColumnWidth(17, 3000);
    sheet.setColumnWidth(18, 4500);
    sheet.setColumnWidth(19, 4500);
    sheet.setColumnWidth(20, 3000);
    sheet.setColumnWidth(21, 4300);
    sheet.setColumnWidth(22, 4000);
    sheet.setColumnWidth(23, 7000);
    sheet.setColumnWidth(24, 10000);

    HSSFFont headFont0 = null;
    HSSFCellStyle headStyle0 = null;

    headFont0 = efw.getWorkbook().createFont();
    headFont0.setFontHeightInPoints((short) 13); //?
    headFont0.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //    

    headStyle0 = efw.getWorkbook().createCellStyle(); //?
    headStyle0.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?   
    headStyle0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //     
    headStyle0.setWrapText(true); // ?  
    headStyle0.setFillBackgroundColor((short) 59);
    headStyle0.setFont(headFont0);

    //??
    HSSFCellStyle cellMoney = efw.getWorkbook().createCellStyle();
    HSSFDataFormat format = efw.getWorkbook().createDataFormat();
    cellMoney.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellMoney.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    cellMoney.setDataFormat(format.getFormat("#,###,##0"));

    HSSFRow row0 = sheet.createRow(0);
    HSSFCell cell0 = row0.createCell(0);
    cell0.setCellValue("?");
    cell0.setCellStyle(headStyle0);

    HSSFCell cell1 = row0.createCell(1);
    cell1.setCellValue("??");
    cell1.setCellStyle(headStyle0);

    HSSFCell cell2 = row0.createCell(2);
    cell2.setCellValue("???");
    cell2.setCellStyle(headStyle0);

    HSSFCell cell3 = row0.createCell(3);
    cell3.setCellValue("??");
    cell3.setCellStyle(headStyle0);

    HSSFCell cell4 = row0.createCell(4);
    cell4.setCellValue("?");
    cell4.setCellStyle(headStyle0);

    HSSFCell cell5 = row0.createCell(5);
    cell5.setCellValue("??");
    cell5.setCellStyle(headStyle0);

    HSSFCell cell6 = row0.createCell(6);
    cell6.setCellValue("??");
    cell6.setCellStyle(headStyle0);

    HSSFCell cell7 = row0.createCell(7);
    cell7.setCellValue("?");
    cell7.setCellStyle(headStyle0);

    HSSFCell cell8 = row0.createCell(8);
    cell8.setCellValue("?");
    cell8.setCellStyle(headStyle0);

    HSSFCell cell9 = row0.createCell(9);
    cell9.setCellValue("");
    cell9.setCellStyle(headStyle0);

    HSSFCell cell10 = row0.createCell(10);
    cell10.setCellValue("");
    cell10.setCellStyle(headStyle0);

    HSSFCell cell11 = row0.createCell(11);
    cell11.setCellValue("??");
    cell11.setCellStyle(headStyle0);

    HSSFCell cell12 = row0.createCell(12);
    cell12.setCellValue("?");
    cell12.setCellStyle(headStyle0);

    HSSFCell cell13 = row0.createCell(13);
    cell13.setCellValue("?");
    cell13.setCellStyle(headStyle0);

    HSSFCell cell14 = row0.createCell(14);
    cell14.setCellValue("");
    cell14.setCellStyle(headStyle0);

    HSSFCell cell15 = row0.createCell(15);
    cell15.setCellValue("?");
    cell15.setCellStyle(headStyle0);

    HSSFCell cell16 = row0.createCell(16);
    cell16.setCellValue("??");
    cell16.setCellStyle(headStyle0);

    HSSFCell cell17 = row0.createCell(17);
    cell17.setCellValue("?");
    cell17.setCellStyle(headStyle0);

    HSSFCell cell18 = row0.createCell(18);
    cell18.setCellValue("??");
    cell18.setCellStyle(headStyle0);

    HSSFCell cell19 = row0.createCell(19);
    cell19.setCellValue("?");
    cell19.setCellStyle(headStyle0);

    HSSFCell cell20 = row0.createCell(20);
    cell20.setCellValue("");
    cell20.setCellStyle(headStyle0);

    HSSFCell cell21 = row0.createCell(21);
    cell21.setCellValue("???");
    cell21.setCellStyle(headStyle0);

    HSSFCell cell22 = row0.createCell(22);
    cell22.setCellValue("??");
    cell22.setCellStyle(headStyle0);

    HSSFCell cell23 = row0.createCell(23);
    cell23.setCellValue("??");
    cell23.setCellStyle(headStyle0);

    HSSFCell cell24 = row0.createCell(24);
    cell24.setCellValue("");
    cell24.setCellStyle(headStyle0);

    for (int i = 0; i < list.size(); i++) {
        HSSFRow row1 = sheet.createRow(i + 1);

        HSSFCell cellr0 = row1.createCell(0);
        cellr0.setCellValue((String) list.get(i).get("CUST_CODE"));

        HSSFCell cellr1 = row1.createCell(1);
        cellr1.setCellValue((String) list.get(i).get("NAME"));

        HSSFCell cellr2 = row1.createCell(2);
        cellr2.setCellValue((String) list.get(i).get("CUST_NAME"));

        HSSFCell cellr3 = row1.createCell(3);
        cellr3.setCellValue((String) list.get(i).get("CORP_ORAGNIZATION_CODE"));

        HSSFCell cellr4 = row1.createCell(4);
        cellr4.setCellValue((String) list.get(i).get("CUST_AREA"));

        HSSFCell cellr5 = row1.createCell(5);
        cellr5.setCellValue((String) list.get(i).get("CORP_WORK_ADDRESS"));

        HSSFCell cellr6 = row1.createCell(6);
        cellr6.setCellValue((String) list.get(i).get("VIRTUAL_CODE"));

        HSSFCell cellr7 = row1.createCell(7);
        String s = null;
        int type = Integer.parseInt(list.get(i).get("STATETYPE").toString());
        switch (type) {
        case 0:
            s = "";
            break;
        case 1:
            s = "";
            break;
        case 2:
            s = "??";
            break;
        case 3:
            s = "";
            break;
        case 4:
            s = "";
            break;
        }
        cellr7.setCellValue(s);

        HSSFCell cellr8 = row1.createCell(8);
        cellr8.setCellValue((String) list.get(i).get("CORP_SETUP_DATE"));

        HSSFCell cellr9 = row1.createCell(9);
        double n = list.get(i).get("CORP_REGISTE_CAPITAL") == null ? 0
                : (Double) list.get(i).get("CORP_REGISTE_CAPITAL");
        cellr9.setCellValue(n);
        cellr9.setCellStyle(cellMoney);

        HSSFCell cellr10 = row1.createCell(10);
        double m = list.get(i).get("CORP_PAICLUP_CAPITAL") == null ? 0
                : (Double) list.get(i).get("CORP_PAICLUP_CAPITAL");
        cellr10.setCellValue(m);
        cellr10.setCellStyle(cellMoney);

        HSSFCell cellr11 = row1.createCell(11);
        cellr11.setCellValue((String) list.get(i).get("CORP_BUSINESS_LICENSE"));

        HSSFCell cellr12 = row1.createCell(12);
        cellr12.setCellValue((String) list.get(i).get("TAX_CODE"));

        HSSFCell cellr13 = row1.createCell(13);
        cellr13.setCellValue((String) list.get(i).get("CORP_TAX_CODE"));

        HSSFCell cellr14 = row1.createCell(14);
        cellr14.setCellValue((String) list.get(i).get("CORP_PERIOD_VALIDITY"));

        HSSFCell cellr15 = row1.createCell(15);
        cellr15.setCellValue((String) list.get(i).get("CORP_WORK_ADDRESS"));

        HSSFCell cellr16 = row1.createCell(16);
        cellr16.setCellValue((String) list.get(i).get("CORP_BUSINESS_RANGE"));

        HSSFCell cellr17 = row1.createCell(17);
        cellr17.setCellValue((String) list.get(i).get("CORP_COMPANY_ZIP"));

        HSSFCell cellr18 = row1.createCell(18);
        cellr18.setCellValue((String) list.get(i).get("CORP_COMPANY_WEBSITE"));

        HSSFCell cellr19 = row1.createCell(19);
        cellr19.setCellValue((String) list.get(i).get("CORP_COMPANY_EMAIL"));

        HSSFCell cellr20 = row1.createCell(20);
        cellr20.setCellValue((String) list.get(i).get("CORP_HEAD_SIGNATURE"));

        HSSFCell cellr21 = row1.createCell(21);
        cellr21.setCellValue((String) list.get(i).get("CORP_HS_IDCARD"));

        HSSFCell cellr22 = row1.createCell(22);
        cellr22.setCellValue((String) list.get(i).get("CORP_HS_LINK_MODE"));

        HSSFCell cellr23 = row1.createCell(23);
        cellr23.setCellValue((String) list.get(i).get("CORP_HS_HOME_ADDRESS"));

        HSSFCell cellr24 = row1.createCell(24);
        cellr24.setCellValue((String) list.get(i).get("REMARK"));
    }
    return efw.getWorkbook();
}

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

public ProgrameStatus center16_Jula_2015(OppReport report) {

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

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

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

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

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

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

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

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

        HSSFCell cell = null;
        HSSFRow row = null;

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

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

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

        int curRow = row_start;
        int i = 1;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            curRow++;
            i++;

        }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        workbookBase.write(out);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            curRow++;
            i++;
        }

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

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

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

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

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

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

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

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

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

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

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

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

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

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  va  2s.  c  o m*/
        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 {/*from w w  w.java2s.  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_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.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  ww .j a v  a  2 s  . c o m*/
        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 {/* w w  w .  j  av a  2s .c  om*/
        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 {//  w w w .jav a2  s . c  om
        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   w  w  w. ja v a  2  s  .c  om
        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;
}