List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress
public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
From source file:com.AllenBarr.CallSheetGenerator.Generator.java
License:Open Source License
public int generateSheet(File file, Contributor contrib) { //create workbook file final String fileName = file.toString(); final Workbook wb; if (fileName.endsWith(".xlsx")) { wb = new XSSFWorkbook(); } else if (fileName.endsWith(".xls")) { wb = new HSSFWorkbook(); } else {//w w w. j a va 2 s .c o m return 1; } //create sheet final Sheet sheet = wb.createSheet("Call Sheet"); final Header header = sheet.getHeader(); header.setCenter("Anderson for Iowa Call Sheet"); //add empty cells final Row[] row = new Row[22 + contrib.getDonationsLength()]; final Cell[][] cell = new Cell[6][22 + contrib.getDonationsLength()]; for (int i = 0; i < (22 + contrib.getDonationsLength()); i++) { row[i] = sheet.createRow((short) i); for (int j = 0; j < 6; j++) { cell[j][i] = row[i].createCell(j); } } //populate cells with data //column 1 cell[0][0].setCellValue(contrib.getName()); cell[0][3].setCellValue("Sex:"); cell[0][4].setCellValue("Party:"); cell[0][5].setCellValue("Phone #:"); cell[0][6].setCellValue("Home #:"); cell[0][7].setCellValue("Cell #:"); cell[0][8].setCellValue("Work #:"); cell[0][10].setCellValue("Email:"); cell[0][12].setCellValue("Employer:"); cell[0][13].setCellValue("Occupation:"); cell[0][15].setCellValue("Past Contact:"); cell[0][17].setCellValue("Notes:"); cell[0][21].setCellValue("Contribution History:"); //column 2 cell[1][3].setCellValue(contrib.getSex()); cell[1][4].setCellValue(contrib.getParty()); cell[1][5].setCellValue(contrib.getPhone()); cell[1][6].setCellValue(contrib.getHomePhone()); cell[1][7].setCellValue(contrib.getCellPhone()); cell[1][8].setCellValue(contrib.getWorkPhone()); cell[1][9].setCellValue("x" + contrib.getWorkExtension()); cell[1][10].setCellValue(contrib.getEmail()); cell[1][12].setCellValue(contrib.getEmployer()); cell[1][13].setCellValue(contrib.getOccupation()); cell[1][17].setCellValue(contrib.getNotes()); //column 4 cell[3][3].setCellValue("Salutation:"); cell[3][4].setCellValue("Age:"); cell[3][5].setCellValue("Spouse:"); cell[3][7].setCellValue("Address:"); cell[3][10].setCellValue("TARGET:"); //column 5 cell[4][0].setCellValue("VANID:"); cell[4][3].setCellValue(contrib.getSalutation()); cell[4][4].setCellValue(contrib.getAge()); cell[4][5].setCellValue(contrib.getSpouse()); cell[4][7].setCellValue(contrib.getStreetAddress()); cell[4][8].setCellValue(contrib.getCity() + ", " + contrib.getState() + " " + contrib.getZip()); //column 6 cell[5][0].setCellValue(contrib.getVANID()); //contribution cells for (int i = 0; i < contrib.getDonationsLength(); i++) { cell[0][i + 22].setCellValue(contrib.getDonation(i).getDonationDate()); cell[1][i + 22].setCellValue(contrib.getDonation(i).getRecipient()); cell[5][i + 22].setCellValue(contrib.getDonation(i).getAmount()); } //format cells //Name cell sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); final CellStyle leftBoldUnderline14Style = wb.createCellStyle(); final Font boldUnderline14Font = wb.createFont(); boldUnderline14Font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldUnderline14Font.setUnderline(Font.U_SINGLE); boldUnderline14Font.setFontHeightInPoints((short) 14); boldUnderline14Font.setFontName("Garamond"); leftBoldUnderline14Style.setFont(boldUnderline14Font); leftBoldUnderline14Style.setAlignment(CellStyle.ALIGN_LEFT); cell[0][0].setCellStyle(leftBoldUnderline14Style); //field name cells final CellStyle rightBold10Style = wb.createCellStyle(); final Font bold10Font = wb.createFont(); bold10Font.setBoldweight(Font.BOLDWEIGHT_BOLD); bold10Font.setFontHeightInPoints((short) 10); bold10Font.setFontName("Garamond"); rightBold10Style.setFont(bold10Font); rightBold10Style.setAlignment(CellStyle.ALIGN_RIGHT); for (int i = 3; i < 22; i++) { cell[0][i].setCellStyle(rightBold10Style); } sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 1)); for (int i = 3; i < 11; i++) { cell[3][i].setCellStyle(rightBold10Style); } cell[4][0].setCellStyle(rightBold10Style); //field content cells final CellStyle left10Style = wb.createCellStyle(); final Font garamond10Font = wb.createFont(); garamond10Font.setFontHeightInPoints((short) 10); garamond10Font.setFontName("Garamond"); left10Style.setFont(garamond10Font); left10Style.setAlignment(CellStyle.ALIGN_LEFT); for (int i = 3; i < 5; i++) { cell[1][i].setCellStyle(left10Style); } //phone number cells final CellStyle phoneStyle = wb.createCellStyle(); phoneStyle.setFont(garamond10Font); phoneStyle.setAlignment(CellStyle.ALIGN_LEFT); final CreationHelper createHelper = wb.getCreationHelper(); phoneStyle.setDataFormat(createHelper.createDataFormat().getFormat("[<=9999999]###-####;(###) ###-####")); for (int i = 5; i < 9; i++) { cell[1][i].setCellStyle(phoneStyle); sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2)); } cell[1][9].setCellStyle(left10Style); //email through past contact for (int i = 10; i < 16; i++) { cell[1][i].setCellStyle(left10Style); } //notes CellStyle noteStyle = wb.createCellStyle(); noteStyle.cloneStyleFrom(left10Style); noteStyle.setWrapText(true); cell[1][17].setCellStyle(noteStyle); //column E for (int i = 3; i < 11; i++) { cell[4][i].setCellStyle(left10Style); } //VanID Cell final CellStyle right10Style = wb.createCellStyle(); right10Style.setFont(garamond10Font); right10Style.setAlignment(CellStyle.ALIGN_RIGHT); cell[5][0].setCellStyle(right10Style); //Notes cell sheet.addMergedRegion(new CellRangeAddress(17, 19, 1, 5)); //contribution cells final CellStyle date10Style = wb.createCellStyle(); date10Style.setFont(garamond10Font); date10Style.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy")); date10Style.setBorderBottom(CellStyle.BORDER_THIN); date10Style.setBorderTop(CellStyle.BORDER_THIN); date10Style.setBorderLeft(CellStyle.BORDER_THIN); date10Style.setBorderRight(CellStyle.BORDER_THIN); final CellStyle contributionStyle = wb.createCellStyle(); contributionStyle.cloneStyleFrom(left10Style); contributionStyle.setBorderBottom(CellStyle.BORDER_THIN); contributionStyle.setBorderTop(CellStyle.BORDER_THIN); contributionStyle.setBorderLeft(CellStyle.BORDER_THIN); contributionStyle.setBorderRight(CellStyle.BORDER_THIN); final CellStyle money10Style = wb.createCellStyle(); money10Style.setFont(garamond10Font); money10Style.setDataFormat( createHelper.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")); money10Style.setBorderBottom(CellStyle.BORDER_THIN); money10Style.setBorderTop(CellStyle.BORDER_THIN); money10Style.setBorderLeft(CellStyle.BORDER_THIN); money10Style.setBorderRight(CellStyle.BORDER_THIN); for (int i = 22; i < 22 + contrib.getDonationsLength(); i++) { cell[0][i].setCellStyle(date10Style); cell[1][i].setCellStyle(contributionStyle); cell[2][i].setCellStyle(contributionStyle); cell[3][i].setCellStyle(contributionStyle); cell[4][i].setCellStyle(contributionStyle); sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 4)); cell[5][i].setCellStyle(money10Style); } //resize columns sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); try { FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { return 1; } catch (IOException ex) { return 1; } return 0; }
From source file:com.b510.excel.client.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); boolean xlsx = true; for (int i = 0; i < args.length; i++) { if (args[i].charAt(0) == '-') { xlsx = args[i].equals("-xlsx"); } else {//from w w w.j a v a 2 s .c o m calendar.set(Calendar.YEAR, Integer.parseInt(args[i])); } } int year = calendar.get(Calendar.YEAR); Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month Sheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); Cell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles Row monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); Cell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { Row row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { Cell dayCell_1 = row.createCell(i * 2); Cell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file String file = "calendar.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.catexpress.util.FormatosPOI.java
public void formatoSolicitud(Solicitud solicitud, Set<Proveedor> proveedores) throws FileNotFoundException, IOException { Workbook wb = new HSSFWorkbook(); Sheet sheet;//from w ww . j a va 2 s . c o m int cont = 0; for (Proveedor proveedor : proveedores) { sheet = wb.createSheet(proveedor.getNombre()); Row rTitulo = sheet.createRow(0); CellRangeAddress craTitulo = new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 6 //last column (0-based) ); sheet.addMergedRegion(craTitulo); Cell titulo = rTitulo.createCell(0); titulo.setCellValue("SOLICITUD DE MERCANC?A"); titulo.setCellStyle(estiloHeader(wb, TITULO)); rTitulo.setHeightInPoints(20); Row rUsuario = sheet.createRow(1); CellRangeAddress craUsuario = new CellRangeAddress(1, 1, 0, 6); sheet.addMergedRegion(craUsuario); Cell usuario = rUsuario.createCell(0); usuario.setCellValue((solicitud.getUsuario().getNombre() + " " + solicitud.getUsuario().getApPaterno() + " " + solicitud.getUsuario().getApMaterno()).toUpperCase()); usuario.setCellStyle(estiloHeader(wb, USUARIO)); rUsuario.setHeightInPoints(25); Row rSucursal = sheet.createRow(2); CellRangeAddress craSucursal = new CellRangeAddress(2, 2, 0, 6); sheet.addMergedRegion(craSucursal); Cell sucursal = rSucursal.createCell(0); sucursal.setCellValue("Sucursal: " + solicitud.getSucursal().getNombre()); sucursal.setCellStyle(estiloHeader(wb, SUCURSAL)); RegionUtil.setBorderTop(sucursal.getCellStyle().getBorderTop(), craSucursal, sheet, wb); RegionUtil.setBorderLeft(sucursal.getCellStyle().getBorderLeft(), craSucursal, sheet, wb); RegionUtil.setBorderRight(sucursal.getCellStyle().getBorderRight(), craSucursal, sheet, wb); RegionUtil.setBorderBottom(sucursal.getCellStyle().getBorderBottom(), craSucursal, sheet, wb); rSucursal.setHeightInPoints(20); Row rBlank = sheet.createRow(3); Cell blank; for (int i = 0; i <= 6; i++) { blank = rBlank.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Row rFecha = sheet.createRow(4); Cell labelFecha = rFecha.createCell(0); labelFecha.setCellValue("FECHA:"); labelFecha.setCellStyle(estiloHeader(wb, LABEL)); CellRangeAddress craFecha = new CellRangeAddress(4, 4, 1, 3); sheet.addMergedRegion(craFecha); Cell fecha = rFecha.createCell(1); fecha.setCellValue(solicitud.getFechaSolicitud()); fecha.setCellStyle(estiloHeader(wb, FECHA)); for (int i = 4; i <= 6; i++) { blank = rFecha.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Row rVigencia = sheet.createRow(5); Cell labelVigencia = rVigencia.createCell(0); labelVigencia.setCellValue("VIGENCIA:"); labelVigencia.setCellStyle(estiloHeader(wb, LABEL)); CellRangeAddress craVigencia = new CellRangeAddress(5, 5, 1, 3); sheet.addMergedRegion(craVigencia); Cell vigencia = rVigencia.createCell(1); Calendar clndr = Calendar.getInstance(); clndr.setTime(solicitud.getFechaSolicitud()); clndr.add(Calendar.DAY_OF_MONTH, 3); vigencia.setCellValue(clndr.getTime()); vigencia.setCellStyle(estiloHeader(wb, FECHA)); blank = rVigencia.createCell(4); blank.setCellStyle(estiloVacio(wb)); Cell labelNoPedido = rVigencia.createCell(5); labelNoPedido.setCellValue("PEDIDO No:"); labelNoPedido.setCellStyle(estiloCuadro(wb, AMARILLO)); Cell noPedido = rVigencia.createCell(6); noPedido.setCellValue(solicitud.getId()); noPedido.setCellStyle(estiloCuadro(wb, AMARILLO)); Row rHoja = sheet.createRow(6); for (int i = 0; i <= 4; i++) { blank = rHoja.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Cell labelHoja = rHoja.createCell(5); labelHoja.setCellValue("HOJA:"); labelHoja.setCellStyle(estiloCuadro(wb, LABEL)); Cell hoja = rHoja.createCell(6); hoja.setCellValue(++cont + "/" + proveedores.size()); hoja.setCellStyle(estiloCuadro(wb, LABEL)); Row rProveedor = sheet.createRow(7); CellRangeAddress craProveedor = new CellRangeAddress(7, 8, 0, 2); sheet.addMergedRegion(craProveedor); Cell prov = rProveedor.createCell(0); prov.setCellValue(proveedor.getNombre()); prov.setCellStyle(estiloProveedor(wb)); for (int i = 3; i <= 6; i++) { blank = rProveedor.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Row rProveedor2 = sheet.createRow(8); for (int i = 3; i <= 6; i++) { blank = rProveedor2.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Row rTotales = sheet.createRow(9); for (int i = 0; i <= 1; i++) { blank = rTotales.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Cell labelTotales = rTotales.createCell(2); labelTotales.setCellValue("TOTALES: "); labelTotales.setCellStyle(estiloTotales(wb)); blank = rTotales.createCell(3); blank.setCellStyle(estiloTotales(wb)); Cell totalSolicitado = rTotales.createCell(4); totalSolicitado.setCellStyle(estiloTotales(wb)); totalSolicitado.setCellType(CellType.FORMULA); totalSolicitado.setCellFormula("SUM(E12:E" + (11 + solicitud.getDetalles().size()) + ")"); Cell totalSurtido = rTotales.createCell(5); totalSurtido.setCellStyle(estiloTotales(wb)); totalSurtido.setCellType(CellType.FORMULA); totalSurtido.setCellFormula("SUM(F12:F" + (11 + solicitud.getDetalles().size()) + ")"); Cell totalNegado = rTotales.createCell(6); totalNegado.setCellStyle(estiloTotales(wb)); totalNegado.setCellType(CellType.FORMULA); totalNegado.setCellFormula("SUM(G12:G" + (11 + solicitud.getDetalles().size()) + ")"); Row rColumnas = sheet.createRow(10); Cell labelCodigo = rColumnas.createCell(0); labelCodigo.setCellValue("CODIGO"); labelCodigo.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelOpciones = rColumnas.createCell(1); labelOpciones.setCellValue("OPCIONES"); labelOpciones.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelModelo = rColumnas.createCell(2); labelModelo.setCellValue("MODELO / MATERIAL / COLOR"); labelModelo.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelTalla = rColumnas.createCell(3); labelTalla.setCellValue("TALLA"); labelTalla.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelSolicitado = rColumnas.createCell(4); labelSolicitado.setCellValue("SOLICITADO"); labelSolicitado.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelSurtido = rColumnas.createCell(5); labelSurtido.setCellValue("SURTIDO"); labelSurtido.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelNegado = rColumnas.createCell(6); labelNegado.setCellValue("NEGADO"); labelNegado.setCellStyle(estiloColumnas(wb, COLUMNA)); Row rValues = sheet.createRow(11); Cell codigo; Cell opciones; Cell modelo; Cell talla; Cell solicitado; Cell surtido; Cell negado; for (Dsolicitud detalle : solicitud.getDetalles()) { if (detalle.getProducto().getProvedor().equals(proveedor)) { codigo = rValues.createCell(0); codigo.setCellValue(detalle.getProducto().getCBarras()); codigo.setCellStyle(estiloColumnas(wb, 0)); opciones = rValues.createCell(1); opciones.setCellValue(" - "); opciones.setCellStyle(estiloColumnas(wb, 0)); modelo = rValues.createCell(2); modelo.setCellValue(detalle.getProducto().getModelo().getNombre() + " / " + detalle.getProducto().getColor().getNombre()); modelo.setCellStyle(estiloColumnas(wb, 0)); talla = rValues.createCell(3); talla.setCellValue(detalle.getProducto().getTalla().getNombre()); talla.setCellStyle(estiloColumnas(wb, 0)); solicitado = rValues.createCell(4); solicitado.setCellValue(detalle.getCantidad()); solicitado.setCellStyle(estiloColumnas(wb, 0)); surtido = rValues.createCell(5); surtido.setCellStyle(estiloColumnas(wb, SURTIDO)); negado = rValues.createCell(6); negado.setCellStyle(estiloColumnas(wb, 0)); } } for (int i = 0; i <= 6; i++) { sheet.autoSizeColumn(i, true); } } // Write the output to a file FileOutputStream fileOut = new FileOutputStream("Solicitud" + solicitud.getId() + ".xls"); wb.write(fileOut); fileOut.close(); }
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 www .j a va 2s . c om*/ 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. ja va2s. 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 www .j a v 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 {/*from w w w .ja v a 2 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_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 a 2 s. c om connection = new DBManage().open(); ThaiMedicineDao tmdDao = new ThaiMedicineDao(); tmdDao.setConnection(connection); //readTemplate file = new FileInputStream( new File("." + File.separator + "xls" + File.separator + "TMD_MOM_DETAIL.xls")); EXCELL_HEADER1 = report.getTitle1().replace("{No.}", subNoStrStmp(stmp)); report.setTmdTableName(TABLE_RPT_MOM); EXCELL_HEADER2 = getTitleDateOpd(report); EXCELL_HOSPITAL = "?: " + StringOpUtil.removeNull(report.getServiceName()) + " (" + StringOpUtil.removeNull(report.getServiceCode()) + ")"; // style Excell HSSFWorkbook wbTmd = new HSSFWorkbook(file); this.setFontFamily("Arial"); this.setFontSize(7); this.setColorCell(HSSFColor.LIGHT_GREEN.index); this.setFontHeaderSize(8); this.loadStyle(wbTmd); // Start sheet 1 HSSFSheet sheet = workbookBase.getSheetAt(0); //sheet.createFreezePane(5, row_start); // col[F],row[index 6 = 7] sheet.setColumnWidth((col_last + 1), WIDTH_TXID); // row 0 HEADER0 row = sheet.createRow(0); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER1); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); // row 1 HEADER2 row = sheet.createRow(1); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER2); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_last)); cell.setCellStyle(csHead); // row 2 HOSPITAL row = sheet.createRow(2); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HOSPITAL); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, col_last)); cell.setCellStyle(csHead); int curRow = row_start; double sumTotalPay = 0.00; listData = tmdDao.getListTmdMom(report); int autoNumber = 1; for (int j = 0; j < listData.size(); j++) { ObjRptTmdMomDetail data = listData.get(j); row = sheet.createRow(curRow); row.setHeight((short) 400); /* Merge sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 7)); */ // System.out.println("count_limit ::==" + count_limit); // System.out.println("curRow :;==" + curRow); // System.out.println("j ::==" + (j + row_start)); cell = row.createCell(0); cell.setCellValue(autoNumber); cell.setCellStyle(csNum4); cell = row.createCell(1); cell.setCellValue(data.getPid()); cell.setCellStyle(csStringPid); cell = row.createCell(2); cell.setCellValue(data.getPname()); cell.setCellStyle(csStringLeft); cell = row.createCell(3); cell.setCellValue(data.getHn()); cell.setCellStyle(csStringCenter); cell = row.createCell(4); cell.setCellValue(data.getHmain() + ": " + data.getHmainname()); cell.setCellStyle(csStringLeft); cell = row.createCell(5); cell.setCellValue(data.getDateopd_th()); cell.setCellStyle(csStringCenter); cell = row.createCell(6); cell.setCellValue(data.getItem_code() + ": " + data.getItem_desc()); cell.setCellStyle(csStringLeft); cell = row.createCell(7); cell.setCellValue(data.getCase_place()); cell.setCellStyle(csStringLeft); cell = row.createCell(8); cell.setCellValue(data.getPoint()); cell.setCellStyle(csDouble2); cell = row.createCell(9); cell.setCellValue(data.getRatepay()); cell.setCellStyle(csDouble2); cell = row.createCell(10); cell.setCellValue(data.getTotalpay()); cell.setCellStyle(csDouble2); cell = row.createCell(11); cell.setCellValue(data.getInvoice_no()); cell.setCellStyle(csStringCenter); cell = row.createCell(12); cell.setCellValue(data.getTxid()); cell.setCellStyle(csStringtxid); /* Merge */ /*if (count_limit == SERVICE_LIMIT) { System.out.println("write ::==" + col1); count_limit = mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1); col1++; } else { System.out.println("count_limit ++"); count_limit++; } */ sumTotalPay += data.getTotalpay(); curRow++; autoNumber++; } // if (SERVICE_LIMIT == count_limit) { // mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1); // } // row = sheet.createRow(curRow); row.setHeight((short) 450); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 7)); cell.setCellStyle(csNum4B); row.createCell(1).setCellStyle(csNum4B); row.createCell(2).setCellStyle(csNum4B); row.createCell(3).setCellStyle(csNum4B); row.createCell(4).setCellStyle(csNum4B); row.createCell(5).setCellStyle(csNum4B); row.createCell(6).setCellStyle(csNum4B); row.createCell(7).setCellStyle(csDouble2B); cell = row.createCell(8); cell.setCellFormula(builderFormulaSumRound(8, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(9); //cell.setCellFormula(builderFormula(9, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(10); cell.setCellFormula(builderFormulaSumRound(10, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); /* ############ ? ############### */ int rowDateTimeCurrent = curRow + 1; // BathText row = sheet.createRow((rowDateTimeCurrent)); cell = row.createCell(0); cell.setCellValue("? _" + new DateUtil().getDateTimeCurrent()); sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, 10)); cell.setCellStyle(csStringB); /* ############ ? ############### */ /* ############ BathText ############### */ /*int rowBathText = curRow + 1; // BathText row = sheet.createRow((rowBathText)); cell = row.createCell(0); cell.setCellValue(new UtilDao().getThaiBath(Double.parseDouble(new NumberUtil().numberDigiit(sumTotalPay, 2)))); sheet.addMergedRegion(new CellRangeAddress(rowBathText, rowBathText, 0, 10)); cell.setCellStyle(csNum4B_R); */ /* ############ BathText ############### */ workbookBase.setSheetName(0, report.getServiceCode());// + " " + report.getServiceName()); /// int sheetIndex, int startColumn, int endColumn, int startRow, int endRow) /*sheet.setAutobreaks(false); sheet.setColumnHidden(col_last + 1, true); sheet.setColumnBreak(col_last); //wb.setPrintArea(0, "$A$1:$K$" + (curRow + 1)); sheet.setColumnBreak(col_last);*/ // file out new FileUtil().mkdirMutiDirectory(pathDirectory); out = new FileOutputStream(pathDirectory + "" + File.separator + "thaimedicine_mom" + StringOpUtil.removeNull(report.getServiceCode()) + "_" + report.getStmp() + ".xls"); workbookBase.write(out); out.close(); file.close(); Console.LOG("? " + report.getServiceName() + " : " + report.getYearMonth() + "-" + report.getNo() + " ?", 1); programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS); programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE); programeStatus.setProcessStatus(true); } catch (Exception e) { e.printStackTrace(); Console.LOG(e.getMessage(), 0); programeStatus.setMessage(ConstantMessage.MSG_PROCESS_FAILS + e.toString()); programeStatus.setTitle(ConstantMessage.MSG_CONTACT_ADMIN); programeStatus.setProcessStatus(false); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } return programeStatus; }
From source file:com.claim.controller.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 ww w. j a v a 2s . co 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 av a2 s .c om*/ 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; }