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

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

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

Introduction

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

Prototype

@Override
public HSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

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  a2 s.co  m
        connection = new DBManage().open();

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

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

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

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

        // Start sheet 1 
        HSSFSheet sheet = workbookBase.getSheetAt(0);
        sheet.setColumnWidth((col_last + 1), WIDTH_TXID);

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

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

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

        int curRow = row_start;
        int autoNumber = 1;
        listData = vajiraHCDao.getListVajiraDetail(report);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            curRow++;
            autoNumber++;
        }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        new FileUtil().mkdirMutiDirectory(pathDirectory);

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

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

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return programeStatus;
}

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

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

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

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

        //readTemplate 
        file = new FileInputStream(new File("." + File.separator + "xls" + File.separator + "HC_HC_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 {/* w ww . jav  a2 s.co m*/
        connection = new DBManage().open();

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

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

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

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

        // Start sheet 1 
        HSSFSheet sheet = workbookBase.getSheetAt(0);
        sheet.setColumnWidth((col_last + 1), WIDTH_TXID);

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

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

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

        int curRow = row_start;
        int autoNumber = 1;
        listData = vajiraREDao.getListVajiraDetail(report);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            curRow++;
            autoNumber++;
        }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        new FileUtil().mkdirMutiDirectory(pathDirectory);

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

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

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return programeStatus;
}

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

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

    String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo();
    String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + "";
    try {//from   w w  w .j a v a2  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_SUM.xls"));

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

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

        // Start sheet 1 
        HSSFSheet sheet = workbookBase.getSheetAt(0);
        sheet.setColumnWidth((col_last + 1), WIDTH_TXID);

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

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

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

        int curRow = row_start;
        int autoNumber = 1;
        listData = vajiraREDao.getListVajiraSumGroupHcode(report);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            curRow++;
            autoNumber++;
        }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        new FileUtil().mkdirMutiDirectory(pathDirectory);

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

        workbookBase.write(out);
        out.close();
        file.close();
        Console.LOG(
                "??? Op? ? ???",
                1);

        programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS);
        programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE);
        programeStatus.setProcessStatus(true);

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

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

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

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

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

        //readTemplate 
        file = new FileInputStream(
                new File("." + File.separator + "xls" + File.separator + "HC_RF_SUM_SPLITE.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.getListVajiraSumGroupHmain(report);

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

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

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

            cell = row.createCell(1);
            cell.setCellValue(objData.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 + FILE_NAME_SUM_REPORT
                + 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.claudesoft.service.CarTbDetailed.java

@Override
void makeTableHead(HSSFSheet sheet) {
    HSSFRow row = null;/*from w  w  w.  j a v  a2s  .  co  m*/
    HSSFCell cell = null;
    row = sheet.createRow(2);
    for (int j = 0; j < this.columnCount; j++) {

        //
        if (j == 0) {
            sheet.setColumnWidth(j, 10 * 256);
        } else {
            sheet.setColumnWidth(j, 15 * 256);
        }
        cell = row.createCell(j);
        cell.setCellStyle(makeTableHeadStyle());
    }

    cell = sheet.getRow(2).getCell(0);
    cell.setCellValue("");
    cell = sheet.getRow(2).getCell(1);
    cell.setCellValue("");
    cell = sheet.getRow(2).getCell(2);
    cell.setCellValue("");
    cell = sheet.getRow(2).getCell(3);
    cell.setCellValue("");
    cell = sheet.getRow(2).getCell(4);
    cell.setCellValue("");
    cell = sheet.getRow(2).getCell(5);
    cell.setCellValue("");
    cell = sheet.getRow(2).getCell(6);
    cell.setCellValue("");
    cell = sheet.getRow(2).getCell(7);
    cell.setCellValue("");
    cell = sheet.getRow(2).getCell(8);
    cell.setCellValue("");
    cell = sheet.getRow(2).getCell(9);
    cell.setCellValue("");
    cell = sheet.getRow(2).getCell(10);
    cell.setCellValue("");
    cell = sheet.getRow(2).getCell(11);
    cell.setCellValue("");

}

From source file:com.clonescriptscrapper.excelfile.GenerateCsvFile.java

public static void excel() throws FileNotFoundException, IOException {

    HSSFWorkbook hwb = new HSSFWorkbook();
    HSSFSheet sheet = hwb.createSheet("Monster Details");
    HSSFRow rowhead = sheet.createRow((int) 0);
    rowhead.createCell((int) 0).setCellValue("S.No.");
    rowhead.createCell((int) 1).setCellValue("CATEGORY_DATA_ID");
    rowhead.createCell((int) 2).setCellValue("CATEGORY_ID");
    rowhead.createCell((int) 3).setCellValue("TITLE");
    rowhead.createCell((int) 4).setCellValue("NAME");
    rowhead.createCell((int) 5).setCellValue("CLICKS");
    rowhead.createCell((int) 6).setCellValue("ADDED_ON");
    rowhead.createCell((int) 7).setCellValue("PAGE_RANK");
    rowhead.createCell((int) 8).setCellValue("DESCRIPTION");
    rowhead.createCell((int) 9).setCellValue("DEMO_URL");
    rowhead.createCell((int) 10).setCellValue("CATEGORY_ID");
    rowhead.createCell((int) 11).setCellValue("CATEGORY_NAME");
    rowhead.createCell((int) 12).setCellValue("CATEGORY_URL");
    rowhead.createCell((int) 13).setCellValue("ISCRAWLED");

    try {//from   w w  w  . ja  va  2  s .  c om
        Class.forName("com.mysql.jdbc.Driver");
        java.sql.Connection con = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/clonescriptdirectorydb", "root", "");
        String sql = "SELECT * FROM `categories_data`,categories where categories_data.CATEGORY_ID= categories .CATEGORY_ID;";
        java.sql.PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        int k = 0;
        while (rs.next()) {

            HSSFRow row = sheet.createRow((int) k + 2);
            try {
                row.createCell((int) 0).setCellValue(k + 1);
            } catch (Exception sd) {
            }
            try {
                row.createCell((int) 1).setCellValue(rs.getString("CATEGORY_DATA_ID") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 2).setCellValue(rs.getString("CATEGORY_ID") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 3).setCellValue(rs.getString("TITLE") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 4).setCellValue(rs.getString("NAME") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 5).setCellValue(rs.getString("CLICKS") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 6).setCellValue(rs.getString("ADDED_ON") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 7).setCellValue(rs.getString("PAGE_RANK") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 8).setCellValue(rs.getString("DESCRIPTION") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 9).setCellValue(rs.getString("DEMO_URL") + "");
            } catch (Exception sd) {
            }
            try {
                row.createCell((int) 10).setCellValue(rs.getString("CATEGORY_ID") + "");
            } catch (Exception sd) {
            }
            try {
                row.createCell((int) 11).setCellValue(rs.getString("CATEGORY_NAME") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 12).setCellValue(rs.getString("CATEGORY_URL") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 13).setCellValue(rs.getString("ISCRAWLED") + "");
            } catch (Exception sd) {
            }

            k++;
        }

        try {

            String filename = "data.csv";
            System.out.println("Directory is created!");
            FileOutputStream fileOut = new FileOutputStream(filename);
            hwb.write(fileOut);
            fileOut.close();
            System.out.println("Your excel file has been generated!");
        } catch (IOException iOException) {
        }

    } catch (Exception aaa) {
    }

}

From source file:com.cms.utils.ExcelReader.java

public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle) {
    int maxColumnNum = 0;
    Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;
    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
        HSSFRow srcRow = sheet.getRow(i);
        HSSFRow destRow = newSheet.createRow(i);
        if (srcRow != null) {
            copyRow(sheet, newSheet, srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }//from   ww  w  . j  a v  a2s  . c  o m
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
    }
}

From source file:com.comcast.cats.config.ui.monitoring.reboot.UpTimeAndRebootStatusBean.java

License:Open Source License

public void postProcessXls(Object document) {
    logger.trace("postProcessXls start document " + document);
    if (document != null) {
        HSSFWorkbook workBook = (HSSFWorkbook) document;
        HSSFSheet sheet = workBook.getSheetAt(0);

        HSSFRow headerRow = sheet.getRow(0);

        for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
            sheet.setColumnWidth(i, 30 * 265); // width for 40 characters
        }/*from   w  w  w.  java  2 s .  c  o m*/

        sheet.shiftRows(0, sheet.getLastRowNum(), 5); // shift rows 0 to n
                                                      // by 1 to get space
                                                      // for header
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F3"));

        HSSFFont headerFont = workBook.createFont();
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        HSSFCellStyle headerCellStyle = workBook.createCellStyle();
        headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
        headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerCellStyle.setFont(headerFont);
        headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFCell headerCell = headerRow.createCell(0);
        headerCell.setCellStyle(headerCellStyle);
        headerCell.setCellValue("CATS Uptime and Reboot Status : " + (new Date()));

        HSSFCellStyle metaDataCellStyle = workBook.createCellStyle();
        metaDataCellStyle.setFont(headerFont);

        HSSFRow metaDataRow = sheet.getRow(3);
        if (metaDataRow == null) {
            metaDataRow = sheet.createRow(3);
        }
        HSSFCell metaDataKey = metaDataRow.createCell(0);
        metaDataKey.setCellStyle(metaDataCellStyle);
        metaDataKey.setCellValue("CATS Instance");

        HSSFCell metaDataValue = metaDataRow.createCell(1);
        metaDataValue.setCellStyle(metaDataCellStyle);
        metaDataValue.setCellValue(AuthController.getHostAddress());

        HSSFCellStyle datatTableHeaderCellStyle = workBook.createCellStyle();
        datatTableHeaderCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        datatTableHeaderCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        datatTableHeaderCellStyle.setFont(headerFont);

        HSSFRow actualDataTableHeaderRow = sheet.getRow(5);
        for (int i = 0; i < actualDataTableHeaderRow.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = actualDataTableHeaderRow.getCell(i);
            if (cell != null) {
                String cellValue = cell.getStringCellValue();
                cellValue = cellValue.replace("<br/> ", ""); // replace
                                                             // any line
                                                             // breaks
                cell.setCellValue(cellValue);
                cell.setCellStyle(datatTableHeaderCellStyle);
            }
        }

    }
    logger.trace("postProcessXls end");
}

From source file:com.commander4j.util.JExcel.java

License:Open Source License

public void exportToExcel(String filename, ResultSet rs) {
    try {//from w  w  w  .  j  a  v  a  2s .  c o m

        ResultSetMetaData rsmd = rs.getMetaData();
        int numberOfColumns = rsmd.getColumnCount();
        int columnType = 0;
        String columnTypeName = "";
        int recordNumber = 0;
        int passwordCol = -1;

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        HSSFCellStyle cellStyle_varchar = workbook.createCellStyle();
        cellStyle_varchar.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_nvarchar = workbook.createCellStyle();
        cellStyle_nvarchar.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_varchar2 = workbook.createCellStyle();
        cellStyle_varchar2.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_title = workbook.createCellStyle();
        cellStyle_title.setAlignment(HorizontalAlignment.CENTER);

        HSSFCellStyle cellStyle_char = workbook.createCellStyle();
        cellStyle_char.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_date = workbook.createCellStyle();
        cellStyle_date.setAlignment(HorizontalAlignment.CENTER);
        cellStyle_date.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        HSSFCellStyle cellStyle_timestamp = workbook.createCellStyle();
        cellStyle_timestamp.setAlignment(HorizontalAlignment.CENTER);
        cellStyle_timestamp.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        HSSFCellStyle cellStyle_decimal = workbook.createCellStyle();
        cellStyle_decimal.setAlignment(HorizontalAlignment.RIGHT);

        HSSFFont font_title = workbook.createFont();
        font_title.setColor((short) 0xc);
        font_title.setBold(true);
        ;
        font_title.setItalic(true);
        font_title.setUnderline(HSSFFont.U_DOUBLE);
        cellStyle_title.setFont(font_title);

        HSSFCell cell;
        HSSFRow row;

        // rs.beforeFirst();

        while (rs.next()) {
            recordNumber++;

            if (recordNumber == 1) {
                row = sheet.createRow((int) 0);
                for (int column = 1; column <= numberOfColumns; column++) {
                    cell = row.createCell((int) (column - 1));
                    String columnName = rsmd.getColumnLabel(column);
                    columnName = columnName.replace("_", " ");
                    columnName = JUtility.capitalize(columnName);
                    cell.setCellStyle(cellStyle_title);
                    cell.setCellValue(columnName);
                    if (columnName.equals("Password")) {
                        passwordCol = column;
                    }
                }
            }

            row = sheet.createRow((int) recordNumber);

            for (int column = 1; column <= numberOfColumns; column++) {

                columnType = rsmd.getColumnType(column);
                columnTypeName = rsmd.getColumnTypeName(column);

                cell = row.createCell((int) (column - 1));

                try {
                    switch (columnType) {
                    case java.sql.Types.NVARCHAR:
                        HSSFRichTextString rtf_nvarchar;
                        if (column == passwordCol) {
                            rtf_nvarchar = new HSSFRichTextString("*****");
                        } else {
                            rtf_nvarchar = new HSSFRichTextString(rs.getString(column));
                        }

                        cell.setCellStyle(cellStyle_nvarchar);
                        cell.setCellValue(rtf_nvarchar);
                        break;
                    case java.sql.Types.VARCHAR:
                        HSSFRichTextString rtf_varchar;
                        if (column == passwordCol) {
                            rtf_varchar = new HSSFRichTextString("*****");
                        } else {
                            rtf_varchar = new HSSFRichTextString(rs.getString(column));
                        }

                        cell.setCellStyle(cellStyle_varchar);
                        cell.setCellValue(rtf_varchar);
                        break;
                    case java.sql.Types.CHAR:
                        HSSFRichTextString rtf_char = new HSSFRichTextString(rs.getString(column));
                        cell.setCellStyle(cellStyle_char);
                        cell.setCellValue(rtf_char);
                        break;
                    case java.sql.Types.DATE:
                        try {
                            cell.setCellValue(rs.getTimestamp(column));
                            cell.setCellStyle(cellStyle_date);
                        } catch (Exception ex) {

                        }
                        break;
                    case java.sql.Types.TIMESTAMP:
                        try {
                            cell.setCellValue(rs.getTimestamp(column));
                            cell.setCellStyle(cellStyle_timestamp);
                        } catch (Exception ex) {

                        }
                        break;
                    case java.sql.Types.DECIMAL:
                        HSSFRichTextString rtf_decimal = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_decimal);
                        break;
                    case java.sql.Types.NUMERIC:
                        HSSFRichTextString rtf_decimaln = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_decimaln);
                        break;
                    case java.sql.Types.BIGINT:
                        HSSFRichTextString rtf_bigint = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_bigint);
                        break;
                    case java.sql.Types.INTEGER:
                        HSSFRichTextString rtf_int = new HSSFRichTextString(String.valueOf(rs.getInt(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_int);
                        break;
                    case java.sql.Types.FLOAT:
                        HSSFRichTextString rtf_float = new HSSFRichTextString(
                                String.valueOf(rs.getFloat(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_float);
                        break;
                    case java.sql.Types.DOUBLE:
                        HSSFRichTextString rtf_double = new HSSFRichTextString(
                                String.valueOf(rs.getDouble(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_double);
                        break;
                    default:
                        cell.setCellValue(new HSSFRichTextString(columnTypeName));
                        break;
                    }
                } catch (Exception ex) {
                    String errormessage = ex.getLocalizedMessage();
                    HSSFRichTextString rtf_exception = new HSSFRichTextString(errormessage);
                    cell.setCellStyle(cellStyle_varchar);
                    cell.setCellValue(rtf_exception);
                    break;
                }
            }

            if (recordNumber == 65535) {
                break;
            }
        }

        for (int column = 1; column <= numberOfColumns; column++) {
            sheet.autoSizeColumn((int) (column - 1));
        }

        if (recordNumber > 0) {
            try {
                FileOutputStream fileOut = new FileOutputStream(filename.toLowerCase());
                workbook.write(fileOut);
                fileOut.close();
            } catch (Exception ex) {
                setErrorMessage(ex.getMessage());
            }
        }

        try {
            workbook.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    } catch (SQLException e) {
        setErrorMessage(e.getMessage());
    }
}