List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createRow
@Override public HSSFRow createRow(int rownum)
From source file:com.cimmyt.reports.impl.ServiceReportLaboratoryImpl.java
License:Apache License
private HSSFWorkbook getBookResultData(ResultDataExportDataBean resultDataExport, PropertyHelper pro, SortedMap<Integer, ResultsPreferencesDetail> sortedMap) { HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow filaDatGral;/* ww w . jav a 2 s. c om*/ HSSFCell cellDatGral; HSSFRichTextString textDatGral; filaDatGral = sheet.createRow(0); cellDatGral = filaDatGral.createCell(0); textDatGral = new HSSFRichTextString(pro.getKey(LBL_GENERIC_PLATE)); cellDatGral.setCellValue(textDatGral); cellDatGral = filaDatGral.createCell(1); textDatGral = new HSSFRichTextString(resultDataExport.getListPlate()); cellDatGral.setCellValue(textDatGral); filaDatGral = sheet.createRow(1); cellDatGral = filaDatGral.createCell(0); textDatGral = new HSSFRichTextString(pro.getKey(LBL_GENERIC_EXPORT)); cellDatGral.setCellValue(textDatGral); cellDatGral = filaDatGral.createCell(1); textDatGral = new HSSFRichTextString(resultDataExport.getNameExport()); cellDatGral.setCellValue(textDatGral); filaDatGral = sheet.createRow(2); cellDatGral = filaDatGral.createCell(0); textDatGral = new HSSFRichTextString("Date"); cellDatGral.setCellValue(textDatGral); cellDatGral = filaDatGral.createCell(1); textDatGral = new HSSFRichTextString(resultDataExport.getDateExport()); cellDatGral.setCellValue(textDatGral); HSSFRow filaEncabezos = sheet.createRow(6); int colCounter = 0; for (ResultsPreferencesDetail resultsPreferencesDetail : sortedMap.values()) { HSSFCell headerCell = filaEncabezos.createCell(colCounter); HSSFRichTextString headerText = new HSSFRichTextString(resultsPreferencesDetail.getHeader()); headerCell.setCellValue(headerText); colCounter++; } int rowCounter = 7; colCounter = 0; for (RowResultDataBean bean : resultDataExport.getListResults()) { HSSFRow rowData = sheet.createRow(rowCounter); colCounter = 0; for (String str : bean.getListCell()) { if (str != null) { HSSFCell dataCell = rowData.createCell(colCounter); HSSFRichTextString cellValue = new HSSFRichTextString(str); dataCell.setCellValue(cellValue); } colCounter++; } rowCounter++; } return book; }
From source file:com.claim.controller.Center16AndJula2015Controller.java
public ProgrameStatus center16_Jula_2015(OppReport report) { ProgrameStatus programeStatus = new ProgrameStatus(); List<ObjRptChula> listData = new ArrayList<ObjRptChula>(); int col_last = 29; int row_start = 8; // index row int row_formula_start = row_start + 1; int col_txtid_width = col_last + 1; try {//from w w w.j av a2 s.c o m connection = new DBManage().open(); Center16AndChula2015DAO chula2015DAO = new Center16AndChula2015DAO(); chula2015DAO.setConnection(connection); if (report.getServiceCode().equals(HCODE_CENTER16)) { // center 16 listData = chula2015DAO.getListChulaDetail(report.getStmp(), HCODE_CENTER16); EXCELL_HEADER1 = "???? OP : " + chula2015DAO.getMonthPayment(report.getStmp()); EXCELL_HEADER2 = "? 16 13661 Model 2 "; EXCELL_HOSPITAL = "?? ? 16 13661 "; //out out = new FileOutputStream(report.getPathFile() + "" + File.separator + "HC16_13661_" + report.getYearMonth() + "-" + report.getNo() + ".xls"); report.setServiceName( " ? 16 "); } else if (report.getServiceCode().equals(HCODE_CHULA)) { // Jula listData = chula2015DAO.getListChulaDetail(report.getStmp(), HCODE_CHULA); EXCELL_HEADER1 = "???? OP : " + chula2015DAO.getMonthPayment(report.getStmp()); EXCELL_HEADER2 = "? 16 13661 Model 2"; EXCELL_HOSPITAL = "?? ? ? 13756"; //out out = new FileOutputStream(report.getPathFile() + "" + File.separator + "Chula_13756_" + report.getYearMonth() + "-" + report.getNo() + ".xls"); report.setServiceName("?"); } //readTemplate file = new FileInputStream( new File("." + File.separator + "xls" + File.separator + "CH16_CHula_detail_2015.xls")); // style Excell HSSFWorkbook wbCenter16Jula = new HSSFWorkbook(file); this.loadStyle(wbCenter16Jula); // Start sheet 1 ******************************************************************************* HSSFSheet sheet = workbookBase.getSheetAt(0); sheet.createFreezePane(4, 8); // col,row sheet.setColumnWidth(col_txtid_width, WIDTH_TXID); HSSFCell cell = null; HSSFRow row = null; // row 0 HEADER0 row = sheet.createRow(0); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER1); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); // row 1 HEADER2 row = sheet.createRow(1); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER2); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_last)); cell.setCellStyle(csHead); // row 1 HOSPITAL row = sheet.createRow(2); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HOSPITAL); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, col_last)); cell.setCellStyle(csHead); int curRow = row_start; int i = 1; for (int j = 0; j < listData.size(); j++) { ObjRptChula data = listData.get(j); int col1 = i; row = sheet.createRow(curRow); row.setHeight((short) 340); /*PoiHssfUtil stylePoi = new PoiHssfUtil(wb, row, cell); stylePoi.setStyleText(0, String.valueOf(col1), PoiHssfUtil.CENTER);*/ cell = row.createCell(0); cell.setCellValue(col1); cell.setCellStyle(csNum4); cell = row.createCell(1); cell.setCellValue(data.getPid()); cell.setCellStyle(csStringPid); cell = row.createCell(2); cell.setCellValue(data.getHn()); cell.setCellStyle(csStringLeft); cell = row.createCell(3); cell.setCellValue(data.getPname()); cell.setCellStyle(csStringLeft); cell = row.createCell(4); cell.setCellValue(data.getHmain()); cell.setCellStyle(csStringLeft); cell = row.createCell(5); cell.setCellValue(data.getDateopd_th()); cell.setCellStyle(csStringLeft); cell = row.createCell(6); cell.setCellValue(data.getPdxcode()); cell.setCellStyle(csNum4); cell = row.createCell(7); cell.setCellValue(data.getChrg_car()); cell.setCellStyle(csDouble2); cell = row.createCell(8); cell.setCellValue(data.getChrg_rehab_inst()); cell.setCellStyle(csDouble2); cell = row.createCell(9); cell.setCellValue(data.getChrg_ophc()); cell.setCellStyle(csDouble2); cell = row.createCell(10); cell.setCellValue(data.getChrg_car_rehabinst_ophc_total()); cell.setCellStyle(csDouble2); cell = row.createCell(11); cell.setCellValue(data.getChrg_202()); cell.setCellStyle(csDouble2); cell = row.createCell(12); cell.setCellValue(data.getChrg_stditem()); cell.setCellStyle(csDouble2); cell = row.createCell(13); cell.setCellValue(data.getChrg_other()); cell.setCellStyle(csDouble2); cell = row.createCell(14); cell.setCellValue(data.getChrg_total()); cell.setCellStyle(csDouble2); cell = row.createCell(15); cell.setCellValue(data.getSum_chrg()); cell.setCellStyle(csDouble2); cell = row.createCell(16); cell.setCellValue(data.getPaid_car()); cell.setCellStyle(csDouble2); cell = row.createCell(17); cell.setCellValue(data.getPaid_rehab_inst()); cell.setCellStyle(csDouble2); cell = row.createCell(18); cell.setCellValue(data.getPaid_ophc()); cell.setCellStyle(csDouble2); cell = row.createCell(19); cell.setCellValue(data.getPaid_car_rehabinst_ophc_total()); cell.setCellStyle(csDouble2); cell = row.createCell(20); cell.setCellValue(data.getPaid_202()); cell.setCellStyle(csDouble2); cell = row.createCell(21); cell.setCellValue(data.getPaid_stditem()); cell.setCellStyle(csDouble2); cell = row.createCell(22); cell.setCellValue(data.getPaid_other()); cell.setCellStyle(csDouble2); cell = row.createCell(23); cell.setCellValue(data.getPaid_202_stditem_other_total()); cell.setCellStyle(csDouble2); cell = row.createCell(24); cell.setCellValue(data.getPaid_cal_point()); cell.setCellStyle(csDouble2); cell = row.createCell(25); cell.setCellValue(data.getPaid_cal_point_total()); cell.setCellStyle(csDouble2); cell = row.createCell(26); cell.setCellValue(data.getPaid_total()); cell.setCellStyle(csDouble2); cell = row.createCell(27); cell.setCellValue(data.getCompensation_fee_total()); cell.setCellStyle(csDouble2); cell = row.createCell(28); cell.setCellValue(data.getRemark()); cell.setCellStyle(csStringLeft); cell = row.createCell(29); cell.setCellValue(data.getInvoice_no()); cell.setCellStyle(csStringLeft); cell = row.createCell(30); cell.setCellValue(data.getTxid()); cell.setCellStyle(csStringtxid); curRow++; i++; } // row = sheet.createRow(curRow); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 6)); cell.setCellStyle(csNum4B); row.createCell(1).setCellStyle(csNum4B); row.createCell(2).setCellStyle(csNum4B); row.createCell(3).setCellStyle(csNum4B); row.createCell(4).setCellStyle(csNum4B); row.createCell(5).setCellStyle(csNum4B); row.createCell(6).setCellStyle(csNum4B); cell = row.createCell(7); cell.setCellFormula(builderFormulaSum(7, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(8); cell.setCellFormula(builderFormulaSum(8, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(9); cell.setCellFormula(builderFormulaSum(9, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(10); cell.setCellFormula(builderFormulaSum(10, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(11); cell.setCellFormula(builderFormulaSum(11, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(12); cell.setCellFormula(builderFormulaSum(12, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(13); cell.setCellFormula(builderFormulaSum(13, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(14); cell.setCellFormula(builderFormulaSum(14, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(15); cell.setCellFormula(builderFormulaSum(15, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(16); cell.setCellFormula(builderFormulaSum(16, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(17); cell.setCellFormula(builderFormulaSum(17, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(18); cell.setCellFormula(builderFormulaSum(18, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(19); cell.setCellFormula(builderFormulaSum(19, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(20); cell.setCellFormula(builderFormulaSum(20, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(21); cell.setCellFormula(builderFormulaSum(21, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(22); cell.setCellFormula(builderFormulaSum(22, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(23); cell.setCellFormula(builderFormulaSum(23, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(24); cell.setCellFormula(builderFormulaSum(24, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(25); cell.setCellFormula(builderFormulaSum(25, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(26); cell.setCellFormula(builderFormulaSum(26, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(27); cell.setCellFormula(builderFormulaSum(27, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(28); cell.setCellStyle(csNum4B); workbookBase.setSheetName(0, report.getServiceCode() + " " + report.getServiceName()); workbookBase.write(out); out.close(); file.close(); Console.LOG(Message.exportSuccess(report.getServiceName()), 1); programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS); programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE); programeStatus.setProcessStatus(true); } catch (Exception e) { e.printStackTrace(); Console.LOG(e.getMessage(), 0); programeStatus.setMessage(ConstantMessage.MSG_PROCESS_FAILS + e.toString()); programeStatus.setTitle(ConstantMessage.MSG_CONTACT_ADMIN); programeStatus.setProcessStatus(false); } finally { if (connection != null) { try { connection.close(); } catch (SQLException ex) { Logger.getLogger(Center16AndJula2015Controller.class.getName()).log(Level.SEVERE, null, ex); } } } return programeStatus; }
From source file:com.claim.controller.Noni2015Controller.java
public ProgrameStatus noniDetail(OppReport report) { int col_last = 13; int row_start = 6; // index row int row_formula_start = 7; ProgrameStatus programeStatus = new ProgrameStatus(); List<ObjRptNoniDetail> listData = new ArrayList<ObjRptNoniDetail>(); String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo(); String pathDirectory = report.getPathFile() + "" + File.separator + "noni" + File.separator + "" + stmp + "" + File.separator + ""; try {/*from w w w . j ava 2 s .co 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.Noni2015Controller.java
public ProgrameStatus noniSum(OppReport report) { int col_last = 9; int row_start = 5; // index_row int row_formula_start = 6; ProgrameStatus programeStatus = new ProgrameStatus(); List<ObjRptNoniSum> listData = new ArrayList<>(); String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo(); String pathDirectory = report.getPathFile() + "" + File.separator + "noni" + File.separator + "" + stmp + "" + File.separator + ""; try {/*from ww w . j av a 2 s . c o m*/ connection = new DBManage().open(); Noni2015DAO noni2015DAO = new Noni2015DAO(); noni2015DAO.setConnection(connection); file = new FileInputStream( new File("." + File.separator + "xls" + File.separator + "NONI_summary.xls")); // Top Excell Sheet1 EXCELL_HEADER1 = report.getTitle1(); // String EXCELL_SERVICE1 = " Clearing house? " + dateReport; /*if (report.getBudget_year().equals("2014")) { EXCELL_HEADER2 = " 01 ()"; } else { EXCELL_HEADER2 = " " + new DateUtil().convertStmpToString(report.getStmp()); }*/ EXCELL_HEADER2 = new DateUtil().convertStmpToNoniString(report.getStmp()); HSSFWorkbook wbNoni = new HSSFWorkbook(file); this.loadStyle(wbNoni); HSSFSheet sheet = workbookBase.getSheetAt(0); sheet.createFreezePane(3, 5); // row 0 Header row = sheet.createRow(0); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER1); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); // row 2 Header row = sheet.createRow(1); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER2); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); int curRow = row_start; int i = 1; listData = noni2015DAO.getListNoniSum(report); System.out.println("listData.size() :" + listData.size()); for (int j = 0; j < listData.size(); j++) { ObjRptNoniSum objData = listData.get(j); row = sheet.createRow(curRow); row.setHeight((short) 360); cell = row.createCell(0); cell.setCellValue(i); cell.setCellStyle(csNum4); cell = row.createCell(1); cell.setCellValue(objData.getHcode()); cell.setCellStyle(csStringCenter); cell = row.createCell(2); cell.setCellValue(objData.getHcodename()); cell.setCellStyle(csStringLeft); cell = row.createCell(3); cell.setCellValue(objData.getCount_visit()); cell.setCellStyle(csNum4); cell = row.createCell(4); cell.setCellValue(objData.getSum_chrg_middle_priced_items()); cell.setCellStyle(csDouble2); cell = row.createCell(5); cell.setCellValue(objData.getSum_chrg_other()); cell.setCellStyle(csDouble2); cell = row.createCell(6); cell.setCellValue(objData.getSum_chrg_total()); cell.setCellStyle(csDouble2); cell = row.createCell(7); cell.setCellValue(objData.getSum_paid_middle_priced_items()); cell.setCellStyle(csDouble2); cell = row.createCell(8); cell.setCellValue(objData.getSum_paid_other()); cell.setCellStyle(csDouble2); cell = row.createCell(9); cell.setCellValue(objData.getSum_paid_total()); cell.setCellStyle(csDouble2); curRow++; i++; } /** * footer summary total */ row = sheet.createRow(curRow); row.setHeight((short) 450); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 1)); cell.setCellStyle(csNum4B); cell = row.createCell(1); cell.setCellStyle(csHeadTab); cell = row.createCell(2); cell.setCellStyle(csHeadTab); cell = row.createCell(3); cell.setCellFormula(builderFormulaSumRound(3, row_formula_start, curRow, 0)); cell.setCellStyle(csNum4B); cell = row.createCell(4); cell.setCellFormula(builderFormulaSumRound(4, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); 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); // ############# mkdir ############ pathDirectory = new FileUtil().mkdirDir(pathDirectory, report.getBudget_year(), "noni"); // ############# mkdir ############ //write file Excell out = new FileOutputStream(pathDirectory + "" + File.separator + "noni_summary_" + report.getYearMonth() + "-" + report.getNo() + ".xls"); workbookBase.write(out); out.close(); file.close(); Console.LOG("noni_summary ???", 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 {// ww w . j a va 2 s .co m connection = new DBManage().open(); ThaiMedicineDao tmdDao = new ThaiMedicineDao(); tmdDao.setConnection(connection); //readTemplate file = new FileInputStream( new File("." + File.separator + "xls" + File.separator + "TMD_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 {/* w w w . ja v a2 s . 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_MOM_DETAIL.xls")); EXCELL_HEADER1 = report.getTitle1().replace("{No.}", subNoStrStmp(stmp)); report.setTmdTableName(TABLE_RPT_MOM); EXCELL_HEADER2 = getTitleDateOpd(report); EXCELL_HOSPITAL = "?: " + StringOpUtil.removeNull(report.getServiceName()) + " (" + StringOpUtil.removeNull(report.getServiceCode()) + ")"; // style Excell HSSFWorkbook wbTmd = new HSSFWorkbook(file); this.setFontFamily("Arial"); this.setFontSize(7); this.setColorCell(HSSFColor.LIGHT_GREEN.index); this.setFontHeaderSize(8); this.loadStyle(wbTmd); // Start sheet 1 HSSFSheet sheet = workbookBase.getSheetAt(0); //sheet.createFreezePane(5, row_start); // col[F],row[index 6 = 7] sheet.setColumnWidth((col_last + 1), WIDTH_TXID); // row 0 HEADER0 row = sheet.createRow(0); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER1); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); // row 1 HEADER2 row = sheet.createRow(1); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER2); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_last)); cell.setCellStyle(csHead); // row 2 HOSPITAL row = sheet.createRow(2); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HOSPITAL); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, col_last)); cell.setCellStyle(csHead); int curRow = row_start; double sumTotalPay = 0.00; listData = tmdDao.getListTmdMom(report); int autoNumber = 1; for (int j = 0; j < listData.size(); j++) { ObjRptTmdMomDetail data = listData.get(j); row = sheet.createRow(curRow); row.setHeight((short) 400); /* Merge sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 7)); */ // System.out.println("count_limit ::==" + count_limit); // System.out.println("curRow :;==" + curRow); // System.out.println("j ::==" + (j + row_start)); cell = row.createCell(0); cell.setCellValue(autoNumber); cell.setCellStyle(csNum4); cell = row.createCell(1); cell.setCellValue(data.getPid()); cell.setCellStyle(csStringPid); cell = row.createCell(2); cell.setCellValue(data.getPname()); cell.setCellStyle(csStringLeft); cell = row.createCell(3); cell.setCellValue(data.getHn()); cell.setCellStyle(csStringCenter); cell = row.createCell(4); cell.setCellValue(data.getHmain() + ": " + data.getHmainname()); cell.setCellStyle(csStringLeft); cell = row.createCell(5); cell.setCellValue(data.getDateopd_th()); cell.setCellStyle(csStringCenter); cell = row.createCell(6); cell.setCellValue(data.getItem_code() + ": " + data.getItem_desc()); cell.setCellStyle(csStringLeft); cell = row.createCell(7); cell.setCellValue(data.getCase_place()); cell.setCellStyle(csStringLeft); cell = row.createCell(8); cell.setCellValue(data.getPoint()); cell.setCellStyle(csDouble2); cell = row.createCell(9); cell.setCellValue(data.getRatepay()); cell.setCellStyle(csDouble2); cell = row.createCell(10); cell.setCellValue(data.getTotalpay()); cell.setCellStyle(csDouble2); cell = row.createCell(11); cell.setCellValue(data.getInvoice_no()); cell.setCellStyle(csStringCenter); cell = row.createCell(12); cell.setCellValue(data.getTxid()); cell.setCellStyle(csStringtxid); /* Merge */ /*if (count_limit == SERVICE_LIMIT) { System.out.println("write ::==" + col1); count_limit = mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1); col1++; } else { System.out.println("count_limit ++"); count_limit++; } */ sumTotalPay += data.getTotalpay(); curRow++; autoNumber++; } // if (SERVICE_LIMIT == count_limit) { // mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1); // } // row = sheet.createRow(curRow); row.setHeight((short) 450); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 7)); cell.setCellStyle(csNum4B); row.createCell(1).setCellStyle(csNum4B); row.createCell(2).setCellStyle(csNum4B); row.createCell(3).setCellStyle(csNum4B); row.createCell(4).setCellStyle(csNum4B); row.createCell(5).setCellStyle(csNum4B); row.createCell(6).setCellStyle(csNum4B); row.createCell(7).setCellStyle(csDouble2B); cell = row.createCell(8); cell.setCellFormula(builderFormulaSumRound(8, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(9); //cell.setCellFormula(builderFormula(9, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(10); cell.setCellFormula(builderFormulaSumRound(10, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); /* ############ ? ############### */ int rowDateTimeCurrent = curRow + 1; // BathText row = sheet.createRow((rowDateTimeCurrent)); cell = row.createCell(0); cell.setCellValue("? _" + new DateUtil().getDateTimeCurrent()); sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, 10)); cell.setCellStyle(csStringB); /* ############ ? ############### */ /* ############ BathText ############### */ /*int rowBathText = curRow + 1; // BathText row = sheet.createRow((rowBathText)); cell = row.createCell(0); cell.setCellValue(new UtilDao().getThaiBath(Double.parseDouble(new NumberUtil().numberDigiit(sumTotalPay, 2)))); sheet.addMergedRegion(new CellRangeAddress(rowBathText, rowBathText, 0, 10)); cell.setCellStyle(csNum4B_R); */ /* ############ BathText ############### */ workbookBase.setSheetName(0, report.getServiceCode());// + " " + report.getServiceName()); /// int sheetIndex, int startColumn, int endColumn, int startRow, int endRow) /*sheet.setAutobreaks(false); sheet.setColumnHidden(col_last + 1, true); sheet.setColumnBreak(col_last); //wb.setPrintArea(0, "$A$1:$K$" + (curRow + 1)); sheet.setColumnBreak(col_last);*/ // file out new FileUtil().mkdirMutiDirectory(pathDirectory); out = new FileOutputStream(pathDirectory + "" + File.separator + "thaimedicine_mom" + StringOpUtil.removeNull(report.getServiceCode()) + "_" + report.getStmp() + ".xls"); workbookBase.write(out); out.close(); file.close(); Console.LOG("? " + report.getServiceName() + " : " + report.getYearMonth() + "-" + report.getNo() + " ?", 1); programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS); programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE); programeStatus.setProcessStatus(true); } catch (Exception e) { e.printStackTrace(); Console.LOG(e.getMessage(), 0); programeStatus.setMessage(ConstantMessage.MSG_PROCESS_FAILS + e.toString()); programeStatus.setTitle(ConstantMessage.MSG_CONTACT_ADMIN); programeStatus.setProcessStatus(false); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } return programeStatus; }
From source file:com.claim.controller.ThaiMedicineController.java
public ProgrameStatus tmdActSummary(OppReport report) { int col_last = 18; int row_start = 6; // index row int col_freeze = 3; int row_freeze = 6; int row_formula_start = row_start + 1; ProgrameStatus programeStatus = new ProgrameStatus(); List<ObjRptTmdActSummary> listData = new ArrayList<ObjRptTmdActSummary>(); String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo(); String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + ""; try {/*from w w w . ja v a2 s . c o m*/ connection = new DBManage().open(); ThaiMedicineDao tmdDao = new ThaiMedicineDao(); tmdDao.setConnection(connection); file = new FileInputStream(new File("." + File.separator + "xls" + File.separator + "TMD_ACT_SUM.xls")); // Top Excell Sheet1 EXCELL_HEADER1 = report.getTitle1().replace("{No.}", subNoStrStmp(stmp)); report.setTmdTableName(TABLE_RPT_ACT); EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report); HSSFWorkbook wbTmd = new HSSFWorkbook(file); this.setFontFamily("Arial"); this.setFontSize(7); this.setColorCell(HSSFColor.LIGHT_GREEN.index); this.setFontHeaderSize(8); this.loadStyle(wbTmd); HSSFSheet sheet = workbookBase.getSheetAt(0); sheet.setDefaultRowHeightInPoints(100); //sheet.createFreezePane(col_freeze, row_freeze); // row 0 Header row = sheet.createRow(0); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER1); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); // // row 2 Header // row = sheet.createRow(1); // row.setHeight((short) 390); // cell = row.createCell(0); // cell.setCellValue(EXCELL_HEADER2); // sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); // cell.setCellStyle(csHead); int curRow = row_start; double sumTotalSumPay = 0.00; int i = 1; listData = tmdDao.getListSummaryTmdAct(report); System.out.println("listData.size() :" + listData.size()); for (int j = 0; j < listData.size(); j++) { ObjRptTmdActSummary objData = listData.get(j); row = sheet.createRow(curRow); row.setHeight((short) 360); cell = row.createCell(0); cell.setCellValue(i); cell.setCellStyle(csNum4); cell = row.createCell(1); cell.setCellValue(objData.getHcode()); cell.setCellStyle(csString2Center); cell = row.createCell(2); cell.setCellValue(objData.getHcodename()); cell.setCellStyle(csStringLeft); /* 1 */ cell = row.createCell(3); cell.setCellValue(objData.getCount_dis_txid1()); cell.setCellStyle(csNum4R); cell = row.createCell(4); cell.setCellValue(objData.getSum_point1()); cell.setCellStyle(csDouble2); cell = row.createCell(5); cell.setCellValue(objData.getSum_totalpay1()); cell.setCellStyle(csDouble2); /* 2 */ cell = row.createCell(6); cell.setCellValue(objData.getCount_dis_txid2()); cell.setCellStyle(csNum4R); cell = row.createCell(7); cell.setCellValue(objData.getSum_point2()); cell.setCellStyle(csDouble2); cell = row.createCell(8); cell.setCellValue(objData.getSum_totalpay2()); cell.setCellStyle(csDouble2); /* 3 */ cell = row.createCell(9); cell.setCellValue(objData.getCount_dis_txid3()); cell.setCellStyle(csNum4R); cell = row.createCell(10); cell.setCellValue(objData.getSum_point3()); cell.setCellStyle(csDouble2); cell = row.createCell(11); cell.setCellValue(objData.getSum_totalpay3()); cell.setCellStyle(csDouble2); /* 4 */ cell = row.createCell(12); cell.setCellValue(objData.getCount_dis_txid4()); cell.setCellStyle(csNum4R); cell = row.createCell(13); cell.setCellValue(objData.getSum_point4()); cell.setCellStyle(csDouble2); cell = row.createCell(14); cell.setCellValue(objData.getSum_totalpay4()); cell.setCellStyle(csDouble2); /* 5 */ cell = row.createCell(15); cell.setCellValue(objData.getCount_dis_txid5()); cell.setCellStyle(csNum4R); cell = row.createCell(16); cell.setCellValue(objData.getSum_point5()); cell.setCellStyle(csDouble2); cell = row.createCell(17); cell.setCellValue(objData.getSum_totalpay5()); cell.setCellStyle(csDouble2); cell = row.createCell(18); cell.setCellValue(objData.getSum_totalpay_all()); cell.setCellStyle(csDouble2); sumTotalSumPay += objData.getSum_totalpay_all(); curRow++; i++; } /** * footer summary total */ row = sheet.createRow(curRow); row.setHeight((short) 450); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 2)); cell.setCellStyle(csNum4B); row.createCell(1).setCellStyle(csHeadTab); row.createCell(2).setCellStyle(csHeadTab); cell = row.createCell(3); cell.setCellFormula(builderFormulaSum(3, row_formula_start, curRow)); cell.setCellStyle(csNum3B); cell = row.createCell(4); cell.setCellFormula(builderFormulaSum(4, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(5); cell.setCellFormula(builderFormulaSum(5, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(6); cell.setCellFormula(builderFormulaSum(6, row_formula_start, curRow)); cell.setCellStyle(csNum3B); cell = row.createCell(7); cell.setCellFormula(builderFormulaSum(7, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(8); cell.setCellFormula(builderFormulaSum(8, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(9); cell.setCellFormula(builderFormulaSum(9, row_formula_start, curRow)); cell.setCellStyle(csNum3B); cell = row.createCell(10); cell.setCellFormula(builderFormulaSum(10, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(11); cell.setCellFormula(builderFormulaSum(11, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(12); cell.setCellFormula(builderFormulaSum(12, row_formula_start, curRow)); cell.setCellStyle(csNum3B); cell = row.createCell(13); cell.setCellFormula(builderFormulaSum(13, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(14); cell.setCellFormula(builderFormulaSum(14, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(15); cell.setCellFormula(builderFormulaSum(15, row_formula_start, curRow)); cell.setCellStyle(csNum3B); cell = row.createCell(16); cell.setCellFormula(builderFormulaSum(16, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(17); cell.setCellFormula(builderFormulaSum(17, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(18); cell.setCellFormula(builderFormulaSum(18, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); /* ############ ? ############### */ int rowDateTimeCurrent = curRow + 1; // BathText row = sheet.createRow((rowDateTimeCurrent)); cell = row.createCell(0); cell.setCellValue("? _" + new DateUtil().getDateTimeCurrent()); sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, 10)); cell.setCellStyle(csStringB); /* ############ ? ############### */ /* ################# bathTaxt ##################### */ /*System.out.println("(curRow+1) ::=="+(curRow+1)); CellReference cellReference = new CellReference("S"+(curRow+1)); Row row = sheet.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); System.out.println("cell.getCellFormula() ::=="+cell.getCellFormula()); double totalPay = cell.getNumericCellValue(); int rowBathText = curRow + 1; // BathText row = sheet.createRow((rowBathText)); cell = row.createCell(0); cell.setCellValue(new UtilDao().getThaiBath(Double.parseDouble(new NumberUtil().numberDigiit(sumTotalSumPay, 2)))); sheet.addMergedRegion(new CellRangeAddress(rowBathText, rowBathText, 0, 18)); cell.setCellStyle(csNum4B_R); */ /* ################# bathTaxt ##################### */ new FileUtil().mkdirMutiDirectory(pathDirectory); //write file Excell out = new FileOutputStream(pathDirectory + "" + File.separator + "tmdact_summary_" + report.getYearMonth() + "-" + report.getNo() + ".xls"); workbookBase.write(out); out.close(); file.close(); Console.LOG( " ???? ???", 1); programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS); programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE); programeStatus.setProcessStatus(true); } catch (Exception e) { e.printStackTrace(); Console.LOG(e.getMessage(), 0); programeStatus.setMessage(ConstantMessage.MSG_PROCESS_FAILS + e.toString()); programeStatus.setTitle(ConstantMessage.MSG_CONTACT_ADMIN); programeStatus.setProcessStatus(false); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } return programeStatus; }
From source file:com.claim.controller.ThaiMedicineController.java
public ProgrameStatus tmdMomSummary(OppReport report) { int col_last = 7; int row_start = 4; // index row int row_freeze = 4; int col_freeze = 3; int row_formula_start = row_start + 1; ProgrameStatus programeStatus = new ProgrameStatus(); List<ObjRptTmdMomSummary> listData = new ArrayList<ObjRptTmdMomSummary>(); String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo(); String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + ""; try {//from w w w. j a v a 2 s . com connection = new DBManage().open(); ThaiMedicineDao tmdDao = new ThaiMedicineDao(); tmdDao.setConnection(connection); file = new FileInputStream(new File("." + File.separator + "xls" + File.separator + "TMD_MOM_SUM.xls")); // Top Excell Sheet1 EXCELL_HEADER1 = report.getTitle1().replace("{No.}", subNoStrStmp(stmp)); report.setTmdTableName(TABLE_RPT_MOM); EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report); HSSFWorkbook wbTmd = new HSSFWorkbook(file); this.setFontFamily("Arial"); this.setFontSize(7); this.setColorCell(HSSFColor.LIGHT_GREEN.index); this.setFontHeaderSize(8); this.loadStyle(wbTmd); HSSFSheet sheet = workbookBase.getSheetAt(0); //sheet.createFreezePane(col_freeze, row_freeze); // row 0 Header row = sheet.createRow(0); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER1); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); // // row 2 Header // row = sheet.createRow(1); // row.setHeight((short) 390); // cell = row.createCell(0); // cell.setCellValue(EXCELL_HEADER2); // sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); // cell.setCellStyle(csHead); int curRow = row_start; int i = 1; double sumTotalSumPay = 0.00; listData = tmdDao.getListSummaryTmdMom(report); System.out.println("listData.size() :" + listData.size()); for (int j = 0; j < listData.size(); j++) { ObjRptTmdMomSummary objData = listData.get(j); row = sheet.createRow(curRow); row.setHeight((short) 360); cell = row.createCell(0); cell.setCellValue(i); cell.setCellStyle(csNum4); cell = row.createCell(1); cell.setCellValue(objData.getHcode()); cell.setCellStyle(csString2Center); cell = row.createCell(2); cell.setCellValue(objData.getHcodename()); cell.setCellStyle(csStringLeft); cell = row.createCell(3); cell.setCellValue(objData.getCount_in_hosp()); cell.setCellStyle(csNum4R); cell = row.createCell(4); cell.setCellValue(objData.getSum_in_hosp()); cell.setCellStyle(csDouble2); cell = row.createCell(5); cell.setCellValue(objData.getCount_out_hosp()); cell.setCellStyle(csNum4R); cell = row.createCell(6); cell.setCellValue(objData.getSum_out_hosp()); cell.setCellStyle(csDouble2); cell = row.createCell(7); cell.setCellValue(objData.getSum_totalpay()); cell.setCellStyle(csDouble2); sumTotalSumPay += objData.getSum_totalpay(); curRow++; i++; } /** * footer summary total */ row = sheet.createRow(curRow); row.setHeight((short) 450); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 2)); cell.setCellStyle(csNum4B); row.createCell(1).setCellStyle(csHeadTab); row.createCell(2).setCellStyle(csHeadTab); cell = row.createCell(3); cell.setCellFormula(builderFormulaSum(3, row_formula_start, curRow)); cell.setCellStyle(csNum4BR); cell = row.createCell(4); cell.setCellFormula(builderFormulaSum(4, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(5); cell.setCellFormula(builderFormulaSum(5, row_formula_start, curRow)); cell.setCellStyle(csNum4BR); cell = row.createCell(6); cell.setCellFormula(builderFormulaSum(6, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(7); cell.setCellFormula(builderFormulaSum(7, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); /* ############ ? ############### */ int rowDateTimeCurrent = curRow + 1; // BathText row = sheet.createRow((rowDateTimeCurrent)); cell = row.createCell(0); cell.setCellValue("? _" + new DateUtil().getDateTimeCurrent()); sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, 10)); cell.setCellStyle(csStringB); /* ############ ? ############### */ /* ############ BathText ############### */ /* int rowBathText = curRow + 1; // BathText row = sheet.createRow((rowBathText)); cell = row.createCell(0); cell.setCellValue(new UtilDao().getThaiBath(Double.parseDouble(new NumberUtil().numberDigiit(sumTotalSumPay, 2)))); sheet.addMergedRegion(new CellRangeAddress(rowBathText, rowBathText, 0, 7)); cell.setCellStyle(csNum4B_R); */ /* ################# bathTaxt ##################### */ new FileUtil().mkdirMutiDirectory(pathDirectory); //write file Excell out = new FileOutputStream(pathDirectory + "" + File.separator + "tmdmom_summary_" + report.getYearMonth() + "-" + report.getNo() + ".xls"); workbookBase.write(out); out.close(); file.close(); Console.LOG( "??????? ???", 1); programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS); programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE); programeStatus.setProcessStatus(true); } catch (Exception e) { e.printStackTrace(); Console.LOG(e.getMessage(), 0); programeStatus.setMessage(ConstantMessage.MSG_PROCESS_FAILS + e.toString()); programeStatus.setTitle(ConstantMessage.MSG_CONTACT_ADMIN); programeStatus.setProcessStatus(false); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } return programeStatus; }
From source file:com.claim.controller.Vajira11535Controller.java
public ProgrameStatus hc11535Detail(OppReport report) { ProgrameStatus programeStatus = new ProgrameStatus(); int col_last = 20; int row_start = 8; // index row int row_formula_start = row_start + 1; List<ObjRptVajiraHc11535Detail> listData = new ArrayList<ObjRptVajiraHc11535Detail>(); String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo(); String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + ""; try {// w w w . ja va2s. c om 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 {/* w ww .j av 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; }