List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:com.claim.controller.VajiraRFController.java
public ProgrameStatus hcRfDetail(OppReport report) { ProgrameStatus programeStatus = new ProgrameStatus(); int col_last = 20; int row_start = 7; // index row int row_formula_start = row_start + 1; List<ObjRptVajiraRfDetail> listData = new ArrayList<ObjRptVajiraRfDetail>(); String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo(); String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + ""; try {/*from w ww.jav a 2 s .c om*/ connection = new DBManage().open(); VajiraREDao vajiraREDao = new VajiraREDao(); vajiraREDao.setConnection(connection); //readTemplate file = new FileInputStream( new File("." + File.separator + "xls" + File.separator + "HC_RF_DETAIL.xls")); EXCELL_HEADER1 = HEADER_DETAIL.replace("{YEAR}", new DateUtil().getBudgeMonthYear_543(stmp, ConstantVariable.BUDGET_MONTH)); //EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report); EXCELL_HEADER2 = report.getTitle2(); EXCELL_HOSPITAL = "?: " + StringOpUtil.removeNull(report.getServiceName()) + " (" + StringOpUtil.removeNull(report.getServiceCode()) + ")"; // style Excell this.setFontFamily("TH SarabunPSK"); //this.setColorCell(HSSFColor.LIGHT_GREEN.index); this.loadStyle(new HSSFWorkbook(file)); // Start sheet 1 HSSFSheet sheet = workbookBase.getSheetAt(0); sheet.setColumnWidth((col_last + 1), WIDTH_TXID); // row 0 HEADER0 row = sheet.createRow(0); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER1); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); // row 1 HEADER2 row = sheet.createRow(1); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER2); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_last)); cell.setCellStyle(csHead); // row 2 HOSPITAL row = sheet.createRow(2); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HOSPITAL); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, col_last)); cell.setCellStyle(csHead); int curRow = row_start; int autoNumber = 1; listData = vajiraREDao.getListVajiraDetail(report); for (int i = 0; i < listData.size(); i++) { ObjRptVajiraRfDetail objData = listData.get(i); row = sheet.createRow(curRow); row.setHeight((short) 400); cell = row.createCell(0); cell.setCellValue(autoNumber); cell.setCellStyle(csNum3); cell = row.createCell(1); cell.setCellValue(objData.getPid()); cell.setCellStyle(csStringLeft); cell = row.createCell(2); cell.setCellValue(objData.getHn()); cell.setCellStyle(csNum4); cell = row.createCell(3); cell.setCellValue(objData.getPname()); cell.setCellStyle(csStringLeft); cell = row.createCell(4); cell.setCellValue(objData.getHmain() + " : " + objData.getHmain_name()); cell.setCellStyle(csStringLeft); cell = row.createCell(5); cell.setCellValue(objData.getHmainip() + " : " + objData.getHmainip_name()); cell.setCellStyle(csStringLeft); cell = row.createCell(6); cell.setCellValue(objData.getDateopd_th()); cell.setCellStyle(csStringLeft); cell = row.createCell(7); cell.setCellValue(objData.getPdxcode()); cell.setCellStyle(csStringCenter); cell = row.createCell(8); cell.setCellValue(objData.getChrg_hc()); cell.setCellStyle(csDouble2R); cell = row.createCell(9); cell.setCellValue(objData.getChrg_202()); cell.setCellStyle(csDouble2R); cell = row.createCell(10); cell.setCellValue(objData.getChrg_stditem()); cell.setCellStyle(csDouble2R); cell = row.createCell(11); cell.setCellValue(objData.getChrg_other()); cell.setCellStyle(csDouble2R); cell = row.createCell(12); cell.setCellValue(objData.getChrg_total()); cell.setCellStyle(csDouble2R); cell = row.createCell(13); cell.setCellValue(objData.getPaid_model()); cell.setCellStyle(csDouble2R); cell = row.createCell(14); cell.setCellValue(objData.getPaid_202()); cell.setCellStyle(csDouble2R); cell = row.createCell(15); cell.setCellValue(objData.getPaid_stditem()); cell.setCellStyle(csDouble2R); cell = row.createCell(16); cell.setCellValue(objData.getPaid_other()); cell.setCellStyle(csDouble2R); cell = row.createCell(17); cell.setCellValue(objData.getTotalreimburse()); cell.setCellStyle(csDouble2R); cell = row.createCell(18); cell.setCellValue(objData.getInvoice_no()); cell.setCellStyle(csString2); cell = row.createCell(19); cell.setCellValue(objData.getTxid()); cell.setCellStyle(csStringtxid); curRow++; autoNumber++; } //mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1); // row = sheet.createRow(curRow); row.setHeight((short) 450); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 7)); cell.setCellStyle(csNum4B); row.createCell(1).setCellStyle(csNum4B); row.createCell(2).setCellStyle(csNum4B); row.createCell(3).setCellStyle(csNum4B); row.createCell(4).setCellStyle(csNum4B); row.createCell(5).setCellStyle(csNum4B); row.createCell(6).setCellStyle(csNum4B); row.createCell(7).setCellStyle(csNum4B); cell = row.createCell(8); cell.setCellFormula(builderFormulaSumRound(8, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(9); cell.setCellFormula(builderFormulaSumRound(9, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(10); cell.setCellFormula(builderFormulaSumRound(10, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(11); cell.setCellFormula(builderFormulaSumRound(11, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(12); cell.setCellFormula(builderFormulaSumRound(12, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(13); cell.setCellFormula(builderFormulaSumRound(13, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(14); cell.setCellFormula(builderFormulaSumRound(14, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(15); cell.setCellFormula(builderFormulaSumRound(15, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(16); cell.setCellFormula(builderFormulaSumRound(16, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(17); cell.setCellFormula(builderFormulaSumRound(17, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); /* ############ ? ############### */ int rowDateTimeCurrent = curRow + 1; // BathText row = sheet.createRow((rowDateTimeCurrent)); cell = row.createCell(0); cell.setCellValue("? _" + new DateUtil().getDateTimeCurrent()); sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, col_last)); cell.setCellStyle(csStringB); /* ############ ? ############### */ workbookBase.setSheetName(0, report.getServiceCode());// + " " + report.getServiceName()); new FileUtil().mkdirMutiDirectory(pathDirectory); out = new FileOutputStream(pathDirectory + "" + File.separator + FILE_NAME_DETAIL + StringOpUtil.removeNull(report.getServiceCode()) + "_" + report.getStmp() + ".xls"); workbookBase.write(out); out.close(); file.close(); Console.LOG("? " + StringOpUtil.removeNull(report.getServiceName()) + " : " + report.getYearMonth() + "-" + report.getNo() + " ?", 1); programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS); programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE); programeStatus.setProcessStatus(true); } catch (Exception e) { e.printStackTrace(); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } return programeStatus; }
From source file:com.claim.controller.VajiraRFController.java
public ProgrameStatus hcRFSummary(OppReport report) { ProgrameStatus programeStatus = new ProgrameStatus(); int col_last = 17; int row_start = 8; // index row int row_formula_start = row_start + 1; List<ObjRptVajiraRfSummary> listData = new ArrayList<ObjRptVajiraRfSummary>(); String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo(); String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + ""; try {/*w ww.j av a 2 s.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.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 {//from w w w . j a va2 s . c o m connection = new DBManage().open(); VajiraREDao vajiraREDao = new VajiraREDao(); vajiraREDao.setConnection(connection); //readTemplate file = new FileInputStream( new File("." + File.separator + "xls" + File.separator + "HC_RF_SUM_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.cms.utils.ExcelReader.java
public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { Set<CellRangeAddress> mergedRegions = new TreeSet<CellRangeAddress>(); destRow.setHeight(srcRow.getHeight()); for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { HSSFCell oldCell = srcRow.getCell(j); HSSFCell newCell = destRow.getCell(j); if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); }/*ww w . j a va2 s . c o m*/ copyCell(oldCell, newCell, styleMap); CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex()); if (mergedRegion != null) { CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastRow(), mergedRegion.getLastColumn()); if (isNewMergedRegion(newMergedRegion, mergedRegions)) { mergedRegions.add(newMergedRegion); destSheet.addMergedRegion(newMergedRegion); } } } } }
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 }// www . j av a2s.c om 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.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copySheetStyle(HSSFWorkbook destwb, HSSFSheet dest, HSSFWorkbook srcwb, HSSFSheet src) { if (src == null || dest == null) return;//from www .j a v a 2s.c o m dest.setAlternativeExpression(src.getAlternateExpression()); dest.setAlternativeFormula(src.getAlternateFormula()); dest.setAutobreaks(src.getAutobreaks()); dest.setDialog(src.getDialog()); if (src.getColumnBreaks() != null) { for (int col : src.getColumnBreaks()) { dest.setColumnBreak(col); } } dest.setDefaultColumnWidth(src.getDefaultColumnWidth()); dest.setDefaultRowHeight(src.getDefaultRowHeight()); dest.setDefaultRowHeightInPoints(src.getDefaultRowHeightInPoints()); dest.setDisplayGuts(src.getDisplayGuts()); dest.setFitToPage(src.getFitToPage()); dest.setHorizontallyCenter(src.getHorizontallyCenter()); dest.setDisplayFormulas(src.isDisplayFormulas()); dest.setDisplayGridlines(src.isDisplayGridlines()); dest.setDisplayRowColHeadings(src.isDisplayRowColHeadings()); dest.setGridsPrinted(src.isGridsPrinted()); dest.setPrintGridlines(src.isPrintGridlines()); for (int i = 0; i < src.getNumMergedRegions(); i++) { CellRangeAddress r = src.getMergedRegion(i); dest.addMergedRegion(r); } if (src.getRowBreaks() != null) { for (int row : src.getRowBreaks()) { dest.setRowBreak(row); } } dest.setRowSumsBelow(src.getRowSumsBelow()); dest.setRowSumsRight(src.getRowSumsRight()); int maxcol = 0; for (int i = 0; i <= src.getLastRowNum(); i++) { HSSFRow row = src.getRow(i); if (row != null) { if (maxcol < row.getLastCellNum()) maxcol = row.getLastCellNum(); } } for (int col = 0; col <= maxcol; col++) { if (src.getColumnWidth(col) != src.getDefaultColumnWidth()) dest.setColumnWidth(col, src.getColumnWidth(col)); dest.setColumnHidden(col, src.isColumnHidden(col)); } }
From source file:com.eryansky.core.excelTools.JsGridReportBase.java
License:Apache License
/** * /*from www . ja va 2s .c o m*/ * @param * @return void */ private void stopGrouping(HSSFSheet sheet, HashMap<Integer, String> word, HashMap<Integer, Integer> counter, int i, int size, int rownum, HSSFCellStyle style) { String w = word.get(i); if (w != null) { int len = counter.get(i); CellRangeAddress address = new CellRangeAddress(rownum - len, rownum - 1, i, i); sheet.addMergedRegion(address); fillMergedRegion(sheet, address, style); word.remove(i); counter.remove(i); } if (i + 1 < size) { stopGrouping(sheet, word, counter, i + 1, size, rownum, style); } }
From source file:com.eryansky.core.excelTools.JsGridReportBase.java
License:Apache License
/** * /*from w w w.jav a 2 s. c o m*/ * @param * @return void */ private void generateColumn(HSSFSheet sheet, TableColumn tc, int maxlevel, int rownum, int colnum, HSSFCellStyle headerstyle) { HSSFRow row = sheet.getRow(rownum); if (row == null) row = sheet.createRow(rownum); HSSFCell cell = row.createCell(colnum); cell.setCellValue(tc.getDisplay()); if (headerstyle != null) cell.setCellStyle(headerstyle); if (tc.isComplex()) { CellRangeAddress address = new CellRangeAddress(rownum, rownum, colnum, colnum + tc.getLength() - 1); sheet.addMergedRegion(address); fillMergedRegion(sheet, address, headerstyle); int cn = colnum; for (int i = 0; i < tc.getChildren().size(); i++) { if (i != 0) { cn = cn + tc.getChildren().get(i - 1).getLength(); } generateColumn(sheet, tc.getChildren().get(i), maxlevel, rownum + 1, cn, headerstyle); } } else { CellRangeAddress address = new CellRangeAddress(rownum, rownum + maxlevel - tc.level, colnum, colnum); sheet.addMergedRegion(address); fillMergedRegion(sheet, address, headerstyle); } sheet.autoSizeColumn(colnum, true); }
From source file:com.eryansky.core.excelTools.JsGridReportBase.java
License:Apache License
/** * /*from w w w .j a v a2 s .c o m*/ * @param wb Excel * @param title Sheet?? * @param styles ? * @param creator * @param tableData ? * @throws Exception */ public HSSFWorkbook writeSheet(HSSFWorkbook wb, String title, HashMap<String, HSSFCellStyle> styles, String creator, TableData tableData) throws Exception { SimpleDateFormat formater = new SimpleDateFormat("yyyyMMdd HHmm"); String create_time = formater.format(new Date()); HSSFSheet sheet = wb.createSheet(title);// Excel sheet.setDisplayGridlines(false);// ? HSSFRow row = sheet.createRow(0);// HSSFCell cell = row.createCell(0);// int rownum = 0; cell.setCellValue(new HSSFRichTextString(title)); HSSFCellStyle style = styles.get("TITLE"); if (style != null) cell.setCellStyle(style); TableHeaderMetaData headerMetaData = tableData.getTableHeader();// HTML sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headerMetaData.getColumnCount() - 1)); row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString(":")); style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString(creator)); style = styles.get("SUB_TITLE2"); if (style != null) cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(new HSSFRichTextString(":")); style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(3); style = styles.get("SUB_TITLE2"); cell.setCellValue(new HSSFRichTextString(create_time)); if (style != null) cell.setCellStyle(style); rownum = 3;// rownum = 1? HSSFCellStyle headerstyle = styles.get("TABLE_HEADER"); System.out.println(JsonMapper.getInstance().toJson(headerMetaData)); int colnum = 0; for (int i = 0; i < headerMetaData.getOriginColumns().size(); i++) { TableColumn tc = headerMetaData.getOriginColumns().get(i); if (i != 0) { colnum += headerMetaData.getOriginColumns().get(i - 1).getLength(); } generateColumn(sheet, tc, headerMetaData.maxlevel, rownum, colnum, headerstyle); } rownum += headerMetaData.maxlevel; List<TableDataRow> dataRows = tableData.getRows(); HashMap<Integer, Integer> counter = new HashMap<Integer, Integer>(); HashMap<Integer, String> word = new HashMap<Integer, String>(); int index = 0; for (TableDataRow dataRow : dataRows) { row = sheet.createRow(rownum); List<TableDataCell> dataCells = dataRow.getCells(); int size = headerMetaData.getColumns().size(); index = -1; for (int i = 0; i < size; i++) { TableColumn tc = headerMetaData.getColumns().get(i); if (!tc.isVisible()) continue; index++; String value = dataCells.get(i).getValue(); if (tc.isGrouped()) { String w = word.get(index); if (w == null) { word.put(index, value); counter.put(index, 1); createCell(row, tc, dataCells, i, index, styles); } else { if (w.equals(value)) { counter.put(index, counter.get(index) + 1); } else { stopGrouping(sheet, word, counter, index, size, rownum, styles.get("STRING")); word.put(index, value); counter.put(index, 1); createCell(row, tc, dataCells, i, index, styles); } } } else { createCell(row, tc, dataCells, i, index, styles); } } rownum++; } stopGrouping(sheet, word, counter, 0, index, rownum, styles.get("STRING")); // ??? for (int c = 0; c < headerMetaData.getColumns().size(); c++) { sheet.autoSizeColumn((short) c, true); } sheet.setGridsPrinted(true); return wb; }
From source file:com.esd.cs.common.PoiCreateExcel.java
License:Open Source License
/** * /*from www . j a va 2 s. c o m*/ * * @param FilePath * @param companyList * @return */ public static boolean createRepeaExcel(String FilePath, List<ReportViewModel> companyList, ReportModel model) { // Excel Workbook,excel HSSFWorkbook wb = new HSSFWorkbook(); // Excelsheet,exceltab HSSFSheet sheet = wb.createSheet("sheet1"); // excel? sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 3500); // Excel? HSSFRow headRow0 = sheet.createRow(0); HSSFCell headCell = headRow0.createCell(0); // ?? sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 13));// ??? headCell = headRow0.createCell(0); // headCell.setCellValue(model.getTitle()); // ? HSSFCellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(HSSFColor.GREEN.index); style.setAlignment(CellStyle.ALIGN_CENTER);// style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// // HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // style.setFont(font); headCell.setCellStyle(style); // ? ?? HSSFRow RowTow = sheet.createRow(1); HSSFCell CellTow = headRow0.createCell(1); // ?? sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));// ??? CellTow = RowTow.createCell(0); // CellTow.setCellValue(model.getCreateCompany()); // ? // ?? sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 13));// ??? CellTow = RowTow.createCell(6); HSSFCellStyle style1 = wb.createCellStyle(); style1.setFillBackgroundColor(HSSFColor.GREEN.index); style1.setAlignment(CellStyle.ALIGN_RIGHT);// ? CellTow.setCellStyle(style1); // CellTow.setCellValue(model.getCreateData()); // ? HSSFRow headRow = sheet.createRow(2); HSSFCell headell = headRow.createCell(2); // ??? headell = headRow.createCell(0); headell.setCellValue(model.getType()); headell = headRow.createCell(1); headell.setCellValue("??"); headell = headRow.createCell(2); headell.setCellValue("??"); sheet.setColumnWidth(2, 3000); // headell = headRow.createCell(3); headell.setCellValue("???"); sheet.setColumnWidth(3, 3000); // headell = headRow.createCell(4); headell.setCellValue("???"); sheet.setColumnWidth(4, 4000); // headell = headRow.createCell(5); headell.setCellValue("???"); sheet.setColumnWidth(5, 4000); // headell = headRow.createCell(6); headell.setCellValue("???"); sheet.setColumnWidth(6, 4500); // headell = headRow.createCell(7); headell.setCellValue(""); sheet.setColumnWidth(8, 4000); headell = headRow.createCell(8); headell.setCellValue(""); sheet.setColumnWidth(8, 4000); headell = headRow.createCell(9); headell.setCellValue(""); sheet.setColumnWidth(9, 4000); headell = headRow.createCell(10); headell.setCellValue("?"); sheet.setColumnWidth(10, 4000); headell = headRow.createCell(11); headell.setCellValue("???"); headell = headRow.createCell(12); headell.setCellValue("?"); headell = headRow.createCell(13); headell.setCellValue("?"); for (int i = 0; i < companyList.size(); i++) { ReportViewModel company = companyList.get(i); // Excel? HSSFRow row = sheet.createRow(i + 3); HSSFCell cell = row.createCell(i + 3); // ??? // ???? cell = row.createCell(0); cell.setCellValue(company.getReportName()); // ?? cell = row.createCell(1); cell.setCellValue(company.getUnitNum()); // ?? cell = row.createCell(2); cell.setCellValue(company.getEmpTotal()); // ??? cell = row.createCell(3); cell.setCellValue(company.getUnAudit()); // ?, ??? cell = row.createCell(4); cell.setCellValue(company.getUnReAudit()); // ?, ?? cell = row.createCell(5); cell.setCellValue(company.getAuditOk()); // ?, ?? cell = row.createCell(6); cell.setCellValue(company.getUnauditOk()); // cell = row.createCell(7); cell.setCellValue(company.getShouldTotal().toString()); // ? cell = row.createCell(8); cell.setCellValue(company.getAlreadyTotal().toString()); // cell = row.createCell(9); cell.setCellValue(company.getLessTotal().toString()); // ? cell = row.createCell(10); cell.setCellValue(company.getAmountPayable().toString()); // ??? cell = row.createCell(11); cell.setCellValue(company.getReductionAmount().toString()); // ? cell = row.createCell(12); cell.setCellValue(company.getActualAmount().toString()); // ? cell = row.createCell(13); cell.setCellValue(company.getAlreadyAmount().toString()); } // ? HSSFRow row = sheet.createRow(companyList.size() + 3); HSSFCell cell = row.createCell(companyList.size() + 3); // ??? // ???? sheet.addMergedRegion(new CellRangeAddress(companyList.size() + 3, companyList.size() + 3, 0, 13));// ??? cell = row.createCell(0); // ? HSSFCellStyle styleFoot = wb.createCellStyle(); styleFoot.setAlignment(CellStyle.ALIGN_RIGHT);// ? cell.setCellStyle(styleFoot); // cell.setCellValue(model.getCreatePeople()); try { FileOutputStream os = new FileOutputStream(FilePath); wb.write(os); os.flush(); os.close(); companyList.clear(); companyList = null; os = null; wb = null; System.gc(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return true; }