List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook
public HSSFWorkbook(InputStream s) throws IOException
From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java
License:Apache License
/** * Method that load the sample detail into the HSSFWorkbook *///from w w w.j a v a 2 s . co m private void loadExcelList() { String rutaResumen = property.getKey(REPORT_PATH_KBIO, Bundle.conf); InputStream inputStream = getClass().getResourceAsStream(rutaResumen); try { listBook = new HSSFWorkbook(inputStream); HSSFSheet sheetDetails = listBook.getSheetAt(0); loadDetailSheet(sheetDetails); HSSFSheet sheetList = listBook.getSheetAt(1); HSSFSheet actsheetUsefull = listBook.getSheetAt(2); int inidatsheetList = 2; int inidatsheetUsefull = 1; String wellkbio; String platenameact = ""; Integer platevoy = 0; Integer rowusefull = 0; int mod = setShipmentDetail.iterator().next().getStSampleDetail().getLabstudyid().getPlatesize(); int plates = setShipmentDetail.size() / mod; //Crear hojas usefull que necesitare dependiendo del numero de platos for (int j = 1; j < plates; j++) { listBook.cloneSheet(2); } int i = 0; for (ShipmentDetail shipmentDetail : setShipmentDetail) { SampleDetail sampledet = shipmentDetail.getStSampleDetail(); HSSFRow actrowsheetList; //**************** sheetList ************************** // if (sheetList.getRow(i + inidatsheetList) == null) sheetList.createRow(i + inidatsheetList); actrowsheetList = sheetList.getRow(i + inidatsheetList); if (actrowsheetList.getCell(0) == null) actrowsheetList.createCell(0); if (sampledet.getSamplegid() != null) actrowsheetList.getCell(0).setCellValue( sampledet.getLabstudyid().getPrefix() + (sampledet.getLabstudyid().isUsePadded() ? StrUtils.getPaddingCeros(sampledet.getSamplegid()) : String.valueOf(sampledet.getSamplegid()))); if (actrowsheetList.getCell(1) == null) actrowsheetList.createCell(1); actrowsheetList.getCell(1).setCellValue(sampledet.getPlatename()); if (actrowsheetList.getCell(2) == null) actrowsheetList.createCell(2); wellkbio = sampledet.getPlateloc(); if (wellkbio.length() == 2) wellkbio = wellkbio.substring(0, 1) + "0" + wellkbio.substring(1); actrowsheetList.getCell(2).setCellValue(wellkbio); if (actrowsheetList.getCell(3) == null) actrowsheetList.createCell(3); actrowsheetList.getCell(3).setCellValue(""); //Por especificaciones de KBIo se pone C si es un control y nada si es ADN normal if (sampledet.getControltype() != null) if (sampledet.getControltype().equals("K") || sampledet.getControltype().equals("B")) actrowsheetList.getCell(3).setCellValue("C"); if (sampledet.getSamplegid() == null) actrowsheetList.getCell(3).setCellValue("C"); if (actrowsheetList.getCell(4) == null) actrowsheetList.createCell(4); actrowsheetList.getCell(4).setCellValue(sampledet.getLabstudyid().getPlatesize()); // ******************** sheetUsefull *********************** if (!platenameact.equals(sampledet.getPlatename())) { platenameact = sampledet.getPlatename(); platevoy = platevoy + 1; actsheetUsefull = listBook.getSheetAt(platevoy + 1); rowusefull = inidatsheetUsefull; } HSSFRow actrowsheetUsefull; if (actsheetUsefull.getRow(rowusefull) == null) actsheetUsefull.createRow(rowusefull); actrowsheetUsefull = actsheetUsefull.getRow(rowusefull); if (actrowsheetUsefull.getCell(1) == null) actrowsheetUsefull.createCell(1); actrowsheetUsefull.getCell(1).setCellValue(wellkbio); if (actrowsheetUsefull.getCell(2) == null) actrowsheetUsefull.createCell(2); actrowsheetUsefull.getCell(2).setCellValue(i + 1); rowusefull = rowusefull + 1; i = i + 1; } } catch (IOException e) { logger.error(e.getMessage()); } }
From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java
License:Apache License
private void loadListGrid() { String rutaResumen;// w w w .jav a 2s . c o m int size = setShipmentDetail.iterator().next().getStSampleDetail().getLabstudyid().getPlatesize(); int plates = setShipmentDetail.size() / size; if (size == SIZE_PLATE_96) { //PlateContentList.letters=PlateContentList.letters96; rutaResumen = property.getKey(REPORT_PATH_KBIO_G96, Bundle.conf); rowsplate = 8; colsplate = 12; } else { rowsplate = 16; colsplate = 24; rutaResumen = property.getKey(REPORT_PATH_KBIO_G384, Bundle.conf); //PlateContentList.letters=PlateContentList.letters384; } InputStream inputStream = null; String platenameac = ""; int rowplatename; Integer rowItemNo; Integer rowinidatsamples = 4; Integer actualrow = 0; Integer actualcol = 1; Integer cuanhojasvoy = 1; try { inputStream = getClass().getResourceAsStream(rutaResumen); listBook = new HSSFWorkbook(inputStream); HSSFSheet sheetDetails = listBook.getSheetAt(0); loadDetailSheet(sheetDetails); //Crear las hojas que voy a necesitar dependiendo el numero de platos Integer cuanhojasnecesito = 1; if (size == SIZE_PLATE_96) { cuanhojasnecesito = plates / 45; if (plates % 45 != 0) cuanhojasnecesito = cuanhojasnecesito + 1; } if (size == SIZE_PLATE_384) { cuanhojasnecesito = plates / 26; if (plates % 26 != 0) cuanhojasnecesito = cuanhojasnecesito + 1; } for (int i = 1; i < cuanhojasnecesito; i++) { listBook.cloneSheet(1); } HSSFSheet sheetGridActual; sheetGridActual = listBook.getSheetAt(cuanhojasvoy); int cuanplatosvoy = 0; int i = 0; for (ShipmentDetail shipmentDetail : setShipmentDetail) { SampleDetail sampledetail = shipmentDetail.getStSampleDetail(); HSSFRow actrowsheetGrid; if (sheetGridActual.getRow(i) == null) sheetGridActual.createRow(i); //poner nombre del plato if (!platenameac.equals(sampledetail.getPlatename())) { platenameac = sampledetail.getPlatename(); cuanplatosvoy = cuanplatosvoy + 1; //si hay mas de 45 platos entonces hacer una nueva hoja if (cuanplatosvoy % 46 == 0 && size == SIZE_PLATE_96) { cuanhojasvoy = cuanhojasvoy + 1; actualrow = 0; actualcol = 1; cuanplatosvoy = 1; sheetGridActual = listBook.getSheetAt(cuanhojasvoy); } //si hay mas de 26 platos entonces hacer una nueva hoja if (cuanplatosvoy % 26 == 0 && size == SIZE_PLATE_384) { cuanhojasvoy = cuanhojasvoy + 1; actualrow = 0; actualcol = 1; cuanplatosvoy = 1; sheetGridActual = listBook.getSheetAt(cuanhojasvoy); } rowplatename = (cuanplatosvoy - 1) * (rowsplate + 2) + cuanplatosvoy; if (cuanplatosvoy > 8) { rowplatename = rowplatename + 1; } //Indica que area se va a combinar (roiwini, rowfin, colini, colfin) if (sheetGridActual.getRow(rowplatename) == null) sheetGridActual.createRow(rowplatename); actrowsheetGrid = sheetGridActual.getRow(rowplatename); if (actrowsheetGrid.getCell(1) == null) actrowsheetGrid.createCell(1); actrowsheetGrid.getCell(1).setCellValue(platenameac); if (cuanplatosvoy == 1) rowinidatsamples = 2; else if (cuanplatosvoy == 9 && size == SIZE_PLATE_96) rowinidatsamples = rowinidatsamples + rowsplate + 4; else rowinidatsamples = rowinidatsamples + rowsplate + 3; } //sacar la siguiente posicion actualrow = actualrow + 1; if (actualrow > rowsplate) { actualcol = actualcol + 1; actualrow = 1; } if (actualcol > colsplate) { actualrow = 1; actualcol = 1; } //PONER EL SAMPLEID rowItemNo = actualrow + rowinidatsamples; if (sheetGridActual.getRow(rowItemNo) == null) sheetGridActual.createRow(rowItemNo); actrowsheetGrid = sheetGridActual.getRow(rowItemNo); if (actrowsheetGrid.getCell(actualcol) == null) actrowsheetGrid.createCell(actualcol); //SI NO TIENE SAMPLEID COLOCAR "BLANK" if (sampledetail.getSamplegid() == null || (sampledetail.getControltype() != null && sampledetail.getControltype().equals("B"))) actrowsheetGrid.getCell(actualcol).setCellValue("BLANK"); else actrowsheetGrid.getCell(actualcol).setCellValue( sampledetail.getLabstudyid().getPrefix() + (sampledetail.getLabstudyid().isUsePadded() ? StrUtils.getPaddingCeros(sampledetail.getSamplegid()) : String.valueOf(sampledetail.getSamplegid()))); i = i + 1; } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java
License:Apache License
/** * Method that load the sample detail into the HSSFWorkbook *//* w ww . j ava 2 s .c o m*/ private void loadExcelListGenotypingService() { String rutaResumen = property.getKey(Constants.REPORT_PATH_GENOTYPING_SERVICES_96, Bundle.conf); InputStream inputStream = getClass().getResourceAsStream(rutaResumen); try { listBook = new HSSFWorkbook(inputStream); HSSFSheet sheetDetails = listBook.getSheetAt(1); loadCustomerDetailSheetGenotyping(sheetDetails); HSSFSheet sheetList = listBook.getSheetAt(4); loadListSampleIDGenotypingService(sheetList); loadPlateID(listBook.getSheetAt(3)); loadStyleCells(); loadListGenotypingSewrvices(); } catch (IOException e) { logger.error(e.getMessage()); } }
From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java
License:Apache License
/** * Method that load the sample detail into the HSSFWorkbook *//*from ww w . ja va 2 s .c om*/ private void loadExcelListIntertek() { String rutaResumen = property.getKey(Constants.REPORT_PATH_INTERTEK_G96, Bundle.conf); InputStream inputStream = getClass().getResourceAsStream(rutaResumen); try { listBook = new HSSFWorkbook(inputStream); HSSFSheet sheetDetails = listBook.getSheetAt(0); loadCustomerDetailSheet(sheetDetails); HSSFSheet sheeorderForm = listBook.getSheetAt(1); loadOrderForm(sheeorderForm); HSSFSheet sheetList = listBook.getSheetAt(3); loadListSampleID(sheetList); loadStyleCells(); loadListGridIntertek(); } catch (IOException e) { logger.error(e.getMessage()); } }
From source file:com.citrix.g2w.webdriver.util.ReadExcelReport.java
License:Open Source License
/** * Method to read the file.//w w w .j a va 2 s .c om * * @param filePath * (file to read) * @return testReport */ public Map<Integer, List> readFile(final String filePath) { this.logger.log("Absolute file path:" + filePath); List<List> report = null; try { FileInputStream file = new FileInputStream(new File(filePath)); // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); // Get the worksheet count int workSheetCount = workbook.getNumberOfSheets(); for (int count = 0; count < workSheetCount; count++) { // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(count); Row rowObj = null; List rowData = new ArrayList(); report = new ArrayList<List>(); for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) { rowObj = sheet.getRow(rowNum); rowData = new ArrayList(); if (rowObj != null) { Iterator<Cell> rowCellIterator = rowObj.cellIterator(); while (rowCellIterator.hasNext()) { Cell cellObj = rowCellIterator.next(); rowData.add(this.getValue(cellObj)); } report.add(rowData); } } this.workSheetsContent.put(count, report); } } catch (Exception e) { e.printStackTrace(); String errorMessage = "Error while reading file : " + filePath; this.logger.log(errorMessage); this.logger.log(e.getMessage()); throw new RuntimeException(e.getMessage()); } return this.workSheetsContent; }
From source file:com.cladonia.xngreditor.ImportUtilities.java
License:Open Source License
public static DefaultTableModel splitExcelFile(File toSplit, int acceptFormula, int tableIndex, boolean convertCharsToEntites) throws Exception { //file = toSplit; POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(toSplit)); HSSFWorkbook wb = new HSSFWorkbook(fs); if (tableIndex > -1) { HSSFSheet sheet = wb.getSheetAt(tableIndex - 1); boolean isBlankRow = true; int numCols = 0; int numRows = 0; ///* ww w . j av a2 s .c om*/ //int firstRow = 0; int firstColumn = 0; int iColumn = 0; //firstRow = getFirstRow(sheet,-1); /*if(firstRow==-1) { //MessageHandler.showError("Error, Cannot Read Sheet: "+tableIndex,"Import From Excel Error"); return(null); } else {*/ HSSFRow row = sheet.getRow(sheet.getFirstRowNum()); firstColumn = getFirstColumn(sheet); numCols = getNumberOfCells(sheet); //row = sheet.getRow(firstRow); //numRows = sheet.getLastRowNum() - firstRow; Vector rows = new Vector(); /*System.out.println(firstColumn+":"+numCols); System.out.println(sheet.getFirstRowNum()+" To "+sheet.getLastRowNum()); System.out.println(sheet.getPhysicalNumberOfRows());*/ for (int rCnt = sheet.getFirstRowNum(); rCnt < sheet.getPhysicalNumberOfRows(); ++rCnt) { //reset the blank row boolean isBlankRow = true; row = sheet.getRow(rCnt); //System.out.println(numCols + ":" + firstColumn); String[] separated = new String[numCols - firstColumn]; for (int cCnt = firstColumn; cCnt < numCols; ++cCnt) { try { HSSFCell cell = row.getCell((short) cCnt); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC://System.out.println(rCnt+":"+cCnt+ " is numeric"); double value = row.getCell((short) cCnt).getNumericCellValue(); try { //get the long value which eliminates the decimal point long iValue = (new Double(value)).longValue(); //get the double value from this long value double longValue = (new Long(iValue)).doubleValue(); //subtract the two, if answer is 0 then //value can be converted, //if not then it can't if (value - longValue == 0) { //use long value separated[cCnt - firstColumn] = String.valueOf(iValue); } //end if else { //use double value separated[cCnt - firstColumn] = String.valueOf(value); } //end else } //end try catch (NumberFormatException e) { //use double value separated[cCnt - firstColumn] = String.valueOf(value); } //end catch break; case HSSFCell.CELL_TYPE_STRING://System.out.println(rCnt+":"+cCnt+ " is string"); isBlankRow = false; separated[cCnt - firstColumn] = row.getCell((short) cCnt).getStringCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA://System.out.println(rCnt+":"+cCnt+ " is formula"); isBlankRow = false; if (acceptFormula == 0) { //prompt String[] options = { "Cell Value", "Formula Text" }; Object formulaValue = JOptionPane.showInputDialog(null, "This worksheet contains formulas\n" + "What format would you like to import " + "the formula cells by: ", "Import From Table", JOptionPane.INFORMATION_MESSAGE, null, options, options[0]); if (formulaValue.toString().equalsIgnoreCase(options[0])) { //accept values acceptFormula = 2; } //end if else { acceptFormula = 1; } //end else } //end if else if (acceptFormula == 1) { //accept formula separated[cCnt - firstColumn] = row.getCell((short) cCnt).getCellFormula(); } //end else else if (acceptFormula == 2) { //dont accept formula double doubleValue = row.getCell((short) cCnt).getNumericCellValue(); Double dValue = new Double(doubleValue); if (dValue.isNaN()) { //should have been a string separated[cCnt - firstColumn] = row.getCell((short) cCnt) .getStringCellValue(); } //end if else { try { //get the long value which eliminates the decimal point long iValue = (new Double(doubleValue)).longValue(); //get the double value from this long value double longValue = (new Long(iValue)).doubleValue(); //subtract the two, if answer is 0 then //value can be converted, //if not then it can't if (doubleValue - longValue == 0) { //use long value separated[cCnt - firstColumn] = String.valueOf(iValue); } //end if else { //use double value separated[cCnt - firstColumn] = String.valueOf(doubleValue); } //end else } //end try catch (NumberFormatException e) { //use double value separated[cCnt - firstColumn] = String.valueOf(doubleValue); } //end catch } //end else } //end else break; case HSSFCell.CELL_TYPE_ERROR://System.out.println(rCnt+":"+cCnt+ " is error"); separated[cCnt - firstColumn] = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN://System.out.println(rCnt+":"+cCnt+ " is boolean"); isBlankRow = false; boolean booleanValue = row.getCell((short) cCnt).getBooleanCellValue(); separated[cCnt - firstColumn] = String.valueOf(booleanValue); break; case HSSFCell.CELL_TYPE_BLANK://System.out.println(rCnt+":"+cCnt+ " is blank"); separated[cCnt - firstColumn] = ""; break; } } //end if cell!=null else { } //end else } catch (Exception e) { //just a blank cell separated[cCnt - firstColumn] = ""; } //end try catch } //end for cCnt if (!isBlankRow) { rows.add(separated); } //HSSFCell cell; } //end for rCnt DefaultTableModel tableModel = addRowsToTable(rows, numCols - firstColumn); /*fileName = file.getAbsolutePath(); fileName = fileName.substring(0, fileName.lastIndexOf(".")); fileName += ".xml";*/ return (tableModel); } //end else //} return (null); }
From source file:com.cladonia.xngreditor.ImportUtilities.java
License:Open Source License
public static String[] getWorkSheets(File toSplit) throws FileNotFoundException, IOException, Exception { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(toSplit)); HSSFWorkbook wb = new HSSFWorkbook(fs); String[] workSheets = new String[wb.getNumberOfSheets() + 1]; //this.tablesCombo.addItem("- Please Select -"); workSheets[0] = "- Please Select -"; for (int cnt = 0; cnt < wb.getNumberOfSheets(); ++cnt) { workSheets[cnt + 1] = wb.getSheetName(cnt); }/*from ww w . j a v a2 s . c om*/ return (workSheets); }
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 {//ww w .java 2 s . c o m connection = new DBManage().open(); Center16AndChula2015DAO chula2015DAO = new Center16AndChula2015DAO(); chula2015DAO.setConnection(connection); if (report.getServiceCode().equals(HCODE_CENTER16)) { // center 16 listData = chula2015DAO.getListChulaDetail(report.getStmp(), HCODE_CENTER16); EXCELL_HEADER1 = "???? OP : " + chula2015DAO.getMonthPayment(report.getStmp()); EXCELL_HEADER2 = "? 16 13661 Model 2 "; EXCELL_HOSPITAL = "?? ? 16 13661 "; //out out = new FileOutputStream(report.getPathFile() + "" + File.separator + "HC16_13661_" + report.getYearMonth() + "-" + report.getNo() + ".xls"); report.setServiceName( " ? 16 "); } else if (report.getServiceCode().equals(HCODE_CHULA)) { // Jula listData = chula2015DAO.getListChulaDetail(report.getStmp(), HCODE_CHULA); EXCELL_HEADER1 = "???? OP : " + chula2015DAO.getMonthPayment(report.getStmp()); EXCELL_HEADER2 = "? 16 13661 Model 2"; EXCELL_HOSPITAL = "?? ? ? 13756"; //out out = new FileOutputStream(report.getPathFile() + "" + File.separator + "Chula_13756_" + report.getYearMonth() + "-" + report.getNo() + ".xls"); report.setServiceName("?"); } //readTemplate file = new FileInputStream( new File("." + File.separator + "xls" + File.separator + "CH16_CHula_detail_2015.xls")); // style Excell HSSFWorkbook wbCenter16Jula = new HSSFWorkbook(file); this.loadStyle(wbCenter16Jula); // Start sheet 1 ******************************************************************************* HSSFSheet sheet = workbookBase.getSheetAt(0); sheet.createFreezePane(4, 8); // col,row sheet.setColumnWidth(col_txtid_width, WIDTH_TXID); HSSFCell cell = null; HSSFRow row = null; // row 0 HEADER0 row = sheet.createRow(0); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER1); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); // row 1 HEADER2 row = sheet.createRow(1); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER2); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_last)); cell.setCellStyle(csHead); // row 1 HOSPITAL row = sheet.createRow(2); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HOSPITAL); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, col_last)); cell.setCellStyle(csHead); int curRow = row_start; int i = 1; for (int j = 0; j < listData.size(); j++) { ObjRptChula data = listData.get(j); int col1 = i; row = sheet.createRow(curRow); row.setHeight((short) 340); /*PoiHssfUtil stylePoi = new PoiHssfUtil(wb, row, cell); stylePoi.setStyleText(0, String.valueOf(col1), PoiHssfUtil.CENTER);*/ cell = row.createCell(0); cell.setCellValue(col1); cell.setCellStyle(csNum4); cell = row.createCell(1); cell.setCellValue(data.getPid()); cell.setCellStyle(csStringPid); cell = row.createCell(2); cell.setCellValue(data.getHn()); cell.setCellStyle(csStringLeft); cell = row.createCell(3); cell.setCellValue(data.getPname()); cell.setCellStyle(csStringLeft); cell = row.createCell(4); cell.setCellValue(data.getHmain()); cell.setCellStyle(csStringLeft); cell = row.createCell(5); cell.setCellValue(data.getDateopd_th()); cell.setCellStyle(csStringLeft); cell = row.createCell(6); cell.setCellValue(data.getPdxcode()); cell.setCellStyle(csNum4); cell = row.createCell(7); cell.setCellValue(data.getChrg_car()); cell.setCellStyle(csDouble2); cell = row.createCell(8); cell.setCellValue(data.getChrg_rehab_inst()); cell.setCellStyle(csDouble2); cell = row.createCell(9); cell.setCellValue(data.getChrg_ophc()); cell.setCellStyle(csDouble2); cell = row.createCell(10); cell.setCellValue(data.getChrg_car_rehabinst_ophc_total()); cell.setCellStyle(csDouble2); cell = row.createCell(11); cell.setCellValue(data.getChrg_202()); cell.setCellStyle(csDouble2); cell = row.createCell(12); cell.setCellValue(data.getChrg_stditem()); cell.setCellStyle(csDouble2); cell = row.createCell(13); cell.setCellValue(data.getChrg_other()); cell.setCellStyle(csDouble2); cell = row.createCell(14); cell.setCellValue(data.getChrg_total()); cell.setCellStyle(csDouble2); cell = row.createCell(15); cell.setCellValue(data.getSum_chrg()); cell.setCellStyle(csDouble2); cell = row.createCell(16); cell.setCellValue(data.getPaid_car()); cell.setCellStyle(csDouble2); cell = row.createCell(17); cell.setCellValue(data.getPaid_rehab_inst()); cell.setCellStyle(csDouble2); cell = row.createCell(18); cell.setCellValue(data.getPaid_ophc()); cell.setCellStyle(csDouble2); cell = row.createCell(19); cell.setCellValue(data.getPaid_car_rehabinst_ophc_total()); cell.setCellStyle(csDouble2); cell = row.createCell(20); cell.setCellValue(data.getPaid_202()); cell.setCellStyle(csDouble2); cell = row.createCell(21); cell.setCellValue(data.getPaid_stditem()); cell.setCellStyle(csDouble2); cell = row.createCell(22); cell.setCellValue(data.getPaid_other()); cell.setCellStyle(csDouble2); cell = row.createCell(23); cell.setCellValue(data.getPaid_202_stditem_other_total()); cell.setCellStyle(csDouble2); cell = row.createCell(24); cell.setCellValue(data.getPaid_cal_point()); cell.setCellStyle(csDouble2); cell = row.createCell(25); cell.setCellValue(data.getPaid_cal_point_total()); cell.setCellStyle(csDouble2); cell = row.createCell(26); cell.setCellValue(data.getPaid_total()); cell.setCellStyle(csDouble2); cell = row.createCell(27); cell.setCellValue(data.getCompensation_fee_total()); cell.setCellStyle(csDouble2); cell = row.createCell(28); cell.setCellValue(data.getRemark()); cell.setCellStyle(csStringLeft); cell = row.createCell(29); cell.setCellValue(data.getInvoice_no()); cell.setCellStyle(csStringLeft); cell = row.createCell(30); cell.setCellValue(data.getTxid()); cell.setCellStyle(csStringtxid); curRow++; i++; } // row = sheet.createRow(curRow); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 6)); cell.setCellStyle(csNum4B); row.createCell(1).setCellStyle(csNum4B); row.createCell(2).setCellStyle(csNum4B); row.createCell(3).setCellStyle(csNum4B); row.createCell(4).setCellStyle(csNum4B); row.createCell(5).setCellStyle(csNum4B); row.createCell(6).setCellStyle(csNum4B); cell = row.createCell(7); cell.setCellFormula(builderFormulaSum(7, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(8); cell.setCellFormula(builderFormulaSum(8, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(9); cell.setCellFormula(builderFormulaSum(9, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(10); cell.setCellFormula(builderFormulaSum(10, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(11); cell.setCellFormula(builderFormulaSum(11, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(12); cell.setCellFormula(builderFormulaSum(12, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(13); cell.setCellFormula(builderFormulaSum(13, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(14); cell.setCellFormula(builderFormulaSum(14, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(15); cell.setCellFormula(builderFormulaSum(15, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(16); cell.setCellFormula(builderFormulaSum(16, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(17); cell.setCellFormula(builderFormulaSum(17, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(18); cell.setCellFormula(builderFormulaSum(18, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(19); cell.setCellFormula(builderFormulaSum(19, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(20); cell.setCellFormula(builderFormulaSum(20, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(21); cell.setCellFormula(builderFormulaSum(21, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(22); cell.setCellFormula(builderFormulaSum(22, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(23); cell.setCellFormula(builderFormulaSum(23, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(24); cell.setCellFormula(builderFormulaSum(24, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(25); cell.setCellFormula(builderFormulaSum(25, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(26); cell.setCellFormula(builderFormulaSum(26, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(27); cell.setCellFormula(builderFormulaSum(27, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(28); cell.setCellStyle(csNum4B); workbookBase.setSheetName(0, report.getServiceCode() + " " + report.getServiceName()); workbookBase.write(out); out.close(); file.close(); Console.LOG(Message.exportSuccess(report.getServiceName()), 1); programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS); programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE); programeStatus.setProcessStatus(true); } catch (Exception e) { e.printStackTrace(); Console.LOG(e.getMessage(), 0); programeStatus.setMessage(ConstantMessage.MSG_PROCESS_FAILS + e.toString()); programeStatus.setTitle(ConstantMessage.MSG_CONTACT_ADMIN); programeStatus.setProcessStatus(false); } finally { if (connection != null) { try { connection.close(); } catch (SQLException ex) { Logger.getLogger(Center16AndJula2015Controller.class.getName()).log(Level.SEVERE, null, ex); } } } return programeStatus; }
From source file:com.claim.controller.Noni2015Controller.java
public ProgrameStatus noniDetail(OppReport report) { int col_last = 13; int row_start = 6; // index row int row_formula_start = 7; ProgrameStatus programeStatus = new ProgrameStatus(); List<ObjRptNoniDetail> listData = new ArrayList<ObjRptNoniDetail>(); String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo(); String pathDirectory = report.getPathFile() + "" + File.separator + "noni" + File.separator + "" + stmp + "" + File.separator + ""; try {//from w ww . j a v a2s. c om 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 av a2s . 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; }