List of usage examples for org.apache.poi.ss.usermodel Row setHeightInPoints
void setHeightInPoints(float height);
From source file:apm.common.utils.excel.ExportExcel.java
License:Open Source License
/** * ?/*from w w w .j ava2 s . co m*/ * @param title ? * @param headerList */ private void initialize(String title, List<String> headerList) { this.wb = new SXSSFWorkbook(500); this.sheet = wb.createSheet("Export"); this.styles = createStyles(wb); // Create title if (StringUtils.isNotBlank(title)) { Row titleRow = sheet.createRow(rownum++); titleRow.setHeightInPoints(30); Cell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styles.get("title")); titleCell.setCellValue(title); sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), headerList.size() - 1)); } // Create header if (headerList == null) { throw new RuntimeException("headerList not null!"); } Row headerRow = sheet.createRow(rownum++); headerRow.setHeightInPoints(16); for (int i = 0; i < headerList.size(); i++) { Cell cell = headerRow.createCell(i); cell.setCellStyle(styles.get("header")); String[] ss = StringUtils.split(headerList.get(i), "**", 2); if (ss.length == 2) { cell.setCellValue(ss[0]); Comment comment = this.sheet.createDrawingPatriarch() .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6)); comment.setString(new XSSFRichTextString(ss[1])); cell.setCellComment(comment); } else { cell.setCellValue(headerList.get(i)); } sheet.autoSizeColumn(i); } for (int i = 0; i < headerList.size(); i++) { int colWidth = sheet.getColumnWidth(i) * 2; sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth); } log.debug("Initialize success."); }
From source file:br.com.algoritmo.compilacao.CompilaXlsx.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;// w w w .j a v a 2 s . co m Map<Integer, Object[]> data = new TreeMap<Integer, Object[]>(); data.put(0, new Object[] { 0, "Luiz Carlos Miyadaira Ribeiro Junior", "Base", "0468265522433921", "SOFTWARE", null, null, null }); data.put(1, new Object[] { 1, "Sergio Antnio Andrade de Freitas", "Destino 1", "0395549254894676", "SOFTWARE", null, null, null }); data.put(2, new Object[] { 2, "Andre Luiz Aquere de Cerqueira e Souza", "Destino 2", "8424412648258970", "CIVIL", null, null, null }); data.put(3, new Object[] { 3, "Edson Mintsu Hung Destino", "Destino 3", "6753551743147880", "ELETRNICA", null, null, null }); data.put(4, new Object[] { 4, "Edgard Costa Oliveira", "Destino 4", "1196380808351110", "SOFTWARE", null, null, null }); data.put(5, new Object[] { 5, "Edson Alves da Costa Jnior", "Destino 5", "2105379147123450", "SOFTWARE", null, null, null }); data.put(6, new Object[] { 6, "Andr Barros de Sales", "Destino 6", "7610669796869660", "SOFTWARE", null, null, null }); data.put(7, new Object[] { 7, "Giovanni Almeida dos Santos", "Destino 7", "0580891429319047", "SOFTWARE", null, null, null }); data.put(8, new Object[] { 8, "Cristiane Soares Ramos", "Destino 8", "9950213660160160", "SOFTWARE", null, null, null }); data.put(9, new Object[] { 9, "Fabricio Ataides Braz", "Destino 9", "1700216932505000", "SOFTWARE", null, null, null }); data.put(10, new Object[] { 10, "Alexandre Srgio de Arajo Bezerra", "Destino 10", "0255998976169051", "MEDICINA", null, null, null }); data.put(11, new Object[] { 11, "Eduardo Stockler Tognetti", "Destino 11", "2443108673822680", "ELTRICA", null, null, null }); data.put(12, new Object[] { 12, "Jan Mendona Correa", "Destino 12", "7844006017790570", "CINCIA DA COMPUTAO", null, null, null }); data.put(13, new Object[] { 13, "Rejane Maria da Costa Figueiredo", "Destino 13", "2187680174312042", "SOFTWARE", null, null, null }); data.put(14, new Object[] { 14, "Augusto Csar de Mendona Brasil", "Destino 14", "0571960641751286", "ENERGIA", null, null, null }); data.put(15, new Object[] { 15, "Fbio Macdo Mendes", "Destino 15", "8075435338067780", "F?SICA", null, null, null }); if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet aba1 = wb.createSheet("Percentual de similaridade 1"); PrintSetup printSetup = aba1.getPrintSetup(); printSetup.setLandscape(true); aba1.setFitToPage(true); aba1.setHorizontallyCenter(true); Sheet aba2 = wb.createSheet("Percentual de similaridade 2"); PrintSetup printSetup2 = aba2.getPrintSetup(); printSetup2.setLandscape(true); aba1.setFitToPage(true); aba1.setHorizontallyCenter(true); //title row Row titleRow = aba1.createRow(0); titleRow.setHeightInPoints(15); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue( "Resultado da aplicao do algoritmo de clculo do percentual de similaridade entre os indivduos"); titleCell.setCellStyle(styles.get("title")); aba1.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1")); //header row Row headerRow = aba1.createRow(1); headerRow.setHeightInPoints(15); Cell headerCell; for (int i = 1; i <= titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i - 1]); headerCell.setCellStyle(styles.get("header")); } Row headerBase = aba1.createRow(2); headerBase.setHeightInPoints(15); Cell headerCellBase; for (int i = 1; i <= base.length; i++) { headerCellBase = headerBase.createCell(i); headerCellBase.setCellValue(base[i - 1]); headerCellBase.setCellStyle(styles.get("header1")); } Row headerDestino = aba1.createRow(4); headerDestino.setHeightInPoints(15); Cell headerCellDestino; for (int i = 1; i <= destino.length; i++) { headerCellDestino = headerDestino.createCell(i); headerCellDestino.setCellValue(destino[i - 1]); headerCellDestino.setCellStyle(styles.get("header1")); } /*int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if(j == 9){ //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" +rownum+ ":I" + rownum; cell.setCellFormula("SUM("+ref+")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11){ cell.setCellFormula("J" +rownum+ "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } rownum = 3; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles1.length; j++) { Cell cell = row.createCell(j); if(j == 9){ //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" +rownum+ ":I" + rownum; cell.setCellFormula("SUM("+ref+")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11){ cell.setCellFormula("J" +rownum+ "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } */ //set sample data //Iterate over data and write to sheet Set<Integer> keyset = data.keySet(); int rownum = 0; for (Integer key : keyset) { Row row = aba1.createRow(3 + rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); } if (row.getRowNum() == 3) { rownum++; } } //finally set column widths, the width is measured in units of 1/256th of a character width aba1.setColumnWidth(0, 2 * 256); //2 characters wide aba1.setColumnWidth(1, 26 * 256); //26 characters wide aba1.setColumnWidth(2, 20 * 256); //20 characters wide aba1.setColumnWidth(3, 18 * 256); //18 characters wide aba1.setColumnWidth(4, 20 * 256); //20 characters wide for (int i = 5; i < 9; i++) { aba1.setColumnWidth(i, 15 * 256); //6 characters wide } // Write the output to a file String file = "Sada/Percentual de similaridade.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:br.com.techne.gluonsoft.eowexport.builder.ExcelBuilder.java
License:Apache License
/** * mtodo cria bytes de documento Excel/*from w ww .ja v a 2 s . c o m*/ * @param titles * @param columnIndex * @param dataRows * @param locale * @return * @throws Exception */ public static byte[] createExcelBytes(String[] titles, String[] columnIndex, List<HashMap<String, Object>> dataRows, Locale locale) throws Exception { //Workbook wb = new HSSFWorkbook(); XSSFWorkbook wb = new XSSFWorkbook(); byte[] outBytes; try { HashMap<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Tab 1"); //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(12.75f); for (int indexColumn = 0; indexColumn < titles.length; indexColumn++) { Cell cell = headerRow.createCell(indexColumn); cell.setCellValue(titles[indexColumn]); if ((titles.length - 1) < indexColumn) { cell.setCellValue(""); } else cell.setCellValue(titles[indexColumn]); cell.setCellStyle(styles.get("header")); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1;//devido constar titulo, comea do indice 1 ValueCellUtil vcutil = new ValueCellUtil(locale); for (int indexRow = 0; indexRow < dataRows.size(); indexRow++, rownum++) { row = sheet.createRow(rownum); HashMap<String, Object> dataRow = dataRows.get(indexRow); if (dataRow == null) continue; List<String> keysAttribs = null; if (columnIndex.length == 0) { keysAttribs = Arrays.asList(dataRow.keySet().toArray(new String[0])); Collections.reverse(keysAttribs); } else { keysAttribs = Arrays.asList(columnIndex); } int colCt = 0; for (String keyAttrib : keysAttribs) { cell = row.createCell(colCt); String styleName; cell.setCellValue(vcutil.parseValue(dataRow.get(keyAttrib)).toString()); //zebrando tabela if (indexRow % 2 == 0) { // even row styleName = "cell_normal_even"; } else { // odd row styleName = "cell_normal_odd"; } if (indexRow == 0) { //setando auto ajuste sheet.autoSizeColumn(colCt); } cell.setCellStyle(styles.get(styleName)); colCt++; } } sheet.setZoom(75); //75% scale // Write the output to a file // write for return byte[] ByteArrayOutputStream out = new ByteArrayOutputStream(); try { wb.write(out); outBytes = out.toByteArray(); } finally { out.close(); } } finally { wb.close(); } return outBytes; }
From source file:code.excelreport.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); try {//from w ww .ja v a 2s . c om /* TODO output your page here. You may use following sample code. */ String allpath = getServletContext().getRealPath("/MOIS.xlsm"); XSSFWorkbook wb1; String pathtodelete = null; Date da = new Date(); String dat2 = da.toString().replace(" ", "_"); dat2 = dat2.toString().replace(":", "_"); String mydrive = allpath.substring(0, 1); String np = mydrive + ":\\APHIAPLUS\\MOIS\\MACROS\\MOIS_REPORT" + dat2 + ".xlsm"; //check if file exists String sourcepath = getServletContext().getRealPath("/MOIS.xlsm"); File f = new File(np); if (!f.exists() && !f.isFile()) { /* do something */ copytemplates ct = new copytemplates(); System.out.println("Copying macros.."); ct.transfermacros(sourcepath, np); } else //copy the file alone { copytemplates ct = new copytemplates(); //copy the agebased file only ct.copymacros(sourcepath, np); } File allpathfile = new File(np); OPCPackage pkg = OPCPackage.open(allpathfile); pathtodelete = np; //wb = new XSSFWorkbook( OPCPackage.open(allpath) ); wb1 = new XSSFWorkbook(pkg); SXSSFWorkbook wb = new SXSSFWorkbook(wb1, 100); Sheet rawdata = wb.getSheet("Facility Report"); String startdate = "2016-10-01"; String enddate = "2016-10-30"; String county = ""; if (request.getParameter("startdate") != null) { startdate = request.getParameter("startdate"); } if (request.getParameter("county") != null) { if (!request.getParameter("county").equals("")) { county = request.getParameter("county"); } } if (request.getParameter("enddate") != null) { enddate = request.getParameter("enddate"); } String countywhere = " and 1=1"; if (!county.equals("")) { countywhere = " and ( county like '" + county + "' ) "; } DateTime dateTime1 = new DateTime(startdate); DateTime dateTime2 = new DateTime(enddate); int weeks = Weeks.weeksBetween(dateTime1, dateTime2).getWeeks(); System.out.println("___Weeks____" + weeks); if (weeks == 0) { weeks = 1; } //String header[]="county,subcounty,facilityname,startdate,enddate,hiv_pos_target_child,hiv_pos_target_adult,hiv_pos_target_total,hiv_pos_child,hiv_pos_adult,hiv_pos_total,new_care_child,new_care_adult,new_care_total,new_art_target_child,new_art_target_adult,new_art_target_total,started_art_child,started_art_adult,started_art_total,viral_load_target_child,viral_load_target_adult,viral_load_target_total,viral_load_done_child,viral_load_done_adult,viral_load_done_total,ipt_target_child,ipt_target_adult,ipt_target_total,ipt_child,ipt_adult,ipt_total,testing_target_child,testing_target_adult,testing_target_total,test_child,test_adult,test_total,pmtct_hiv_pos_target,pmtct_hiv_pos,eid_target,eid_done,viral_load_mothers_target,viral_load_mothers_done,hiv_pos_yield_perc_child,hiv_pos_yield_perc_adult,hiv_pos_yield_perc_all,hiv_pos_care_perc_child,hiv_pos_care_perc_adult,hiv_pos_care_perc_all,started_art_perc_child,started_art_perc_adult,started_art_perc_all,viral_test_perc_child,viral_test_perc_adult,viral_test_perc_all,ipt_done_perc_child,ipt_done_perc_adult,ipt_done_perc_all,tested_perc_child,tested_perc_adult,tested_perc_all,viral_load_mothers_perc,eid_done_perc,pmtct_hiv_pos_perc,hiv_pos_yield_cmts,hiv_pos_care_cmts,started_art_cmts,viral_test_cmts,ipt_done_cmts,tested_cmts,viral_load_mothers_cmts,eid_done_cmts,pmtct_hiv_pos_cmts".split(","); //String headername[]="COUNTY,SUB-COUNTY,FACILITY,START DATE,END DATE,HIV POSITIVE TARGET CHILDREN,HIV POSITIVE TARGET ADULT,HIV POSITIVE TARGET TOTAL,HIV POSITIVE CHILDREN,HIV POSITIVE ADULT,HIV POSITIVE TOTAL, NEW CARE CHILDREN,NEW CARE ADULT,NEW CARE TOTAL,NEW ART TARGET CHILDREN,NEW ART TARGET ADULT,NEW ART TARGET TOTAL,STARTED ART CHILDREN,STARTED ART ADULT,STARTED ART TOTAL,VIRAL LOAD TARGET CHILDREN,VIRAL LOAD TARGET ADULT,VIRAL LOAD TARGET TOTAL,VIRAL LOAD DONE CHILDREN,VIRAL LOAD DONE ADULT,VIRAL LOAD DONE TOTAL,IPT TARGET CHILDREN,IPT TARGET ADULT,IPT TARGET TOTAL,IPT CHILDREN,IPT ADULT,IPT TOTAL,TESTING TARGET CHILDREN,TESTING TARGET ADULT,TESTING TARGET TOTAL,TESTING CHILDREN,TESTING ADULT,TESTING TOTAL,PMTCT HIV POSITIVE TARGET,PMTCT HIV POSITIVE ,EID TARGET,EID DONE,VIRAL LOAD MOTHERS TARGET,VIRAL LOAD MOTHERS DONE,HIV POSITIVE YIELD CHILDREN ,HIV POSITIVE YIELD ADULT,HIV POSITIVE YIELD ALL,HIV POSITIVE CARE CHILDREN,HIV POSITIVE CARE ADULT,HIV POSITIVE CARE ALL,STARTED ART CHILDREN,STARTED ART ADULT,STARTED ART ,VIRAL TEST CHILDREN,VIRAL TEST ADULT,VIRAL TEST ALL,IPT DONE CHILD,IPT DONE ADULT,IPT DONE ALL,TESTED CHILD,TESTED ADULT,TESTED ALL,VIRAL LOAD MOTHERS ,EID DONE,PMTCT HIV POSITIVE,HIV POSITIVE YIELD COMMENTS,HIV POSITIVE CARE COMMENTS,STARTED ART COMMENTS,VIRAL TEST COMMENTS,IPT DONE COMMENTS ,TESTED COMMENTS,VIRAL LOAD MOTHERS COMMENTS,EID DONE COMMENTS,PMTCT HIV POSITIVE COMMENTS,REPORTING RATE".split(","); String header[] = "county,subcounty,facilityname,startdate,enddate,testing_target_child,testing_target_adult,testing_target_total,test_child,test_adult,test_total,tested_perc_child,tested_perc_adult,tested_perc_all,tested_cmts,hiv_pos_target_child,hiv_pos_target_adult,hiv_pos_target_total,hiv_pos_child,hiv_pos_adult,hiv_pos_total,hiv_pos_yield_perc_child,hiv_pos_yield_perc_adult,hiv_pos_yield_perc_all,hiv_pos_yield_cmts,new_care_child,new_care_adult,new_care_total,hiv_pos_care_perc_child,hiv_pos_care_perc_adult,hiv_pos_care_perc_all,hiv_pos_care_cmts,new_art_target_child,new_art_target_adult,new_art_target_total,started_art_child,started_art_adult,started_art_total,started_art_perc_child,started_art_perc_adult,started_art_perc_all,started_art_cmts,viral_load_target_child,viral_load_target_adult,viral_load_target_total,viral_load_done_child,viral_load_done_adult,viral_load_done_total,viral_test_perc_child,viral_test_perc_adult,viral_test_perc_all,viral_test_cmts,ipt_target_child,ipt_target_adult,ipt_target_total,ipt_child,ipt_adult,ipt_total,ipt_done_perc_child,ipt_done_perc_adult,ipt_done_perc_all,ipt_done_cmts,pmtct_hiv_pos_target,pmtct_hiv_pos,pmtct_hiv_pos_perc,pmtct_hiv_pos_cmts,eid_target,eid_done,eid_done_perc,eid_done_cmts,viral_load_mothers_target,viral_load_mothers_done,viral_load_mothers_perc,viral_load_mothers_cmts" .split(","); String headername[] = "COUNTY,SUB-COUNTY,FACILITY,START DATE,END DATE,TESTING TARGET CHILDREN,TESTING TARGET ADULT,TESTING TARGET TOTAL,TESTING CHILDREN,TESTING ADULT,TESTING TOTAL,PERCENTAGE TESTED AGAINST TARGET CHILDREN,PERCENTAGE TESTED AGAINST TARGET ADULT,PERCENTAGE TESTED AGAINST TARGET ALL,PERCENTAGE TESTED AGAINST TARGET COMMENTS,HIV POSITIVE TARGET CHILDREN,HIV POSITIVE TARGET ADULT,HIV POSITIVE TARGET TOTAL,HIV POSITIVE CHILDREN,HIV POSITIVE ADULT,HIV POSITIVE TOTAL,PERCENTAGE HIV POSITIVE TARGET YIELD ACHIEVED CHILDREN,PERCENTAGE HIV POSITIVE TARGET YIELD ACHIEVED ADULT,PERCENTAGE HIV POSITIVE TARGET YIELD ACHIEVED ALL,PERCENTAGE HIV POSITIVE TARGET YIELD ACHIEVED COMMENTS, NEW CARE CHILDREN,NEW CARE ADULT,NEW CARE TOTAL,PERCENTAGE HIV POSITIVE ENROLLED ON CARE CHILDREN,PERCENTAGE HIV POSITIVE ENROLLED ON CARE ADULT,PERCENTAGE HIV POSITIVE ENROLLED ON CARE ALL,PERCENTAGE HIV POSITIVE ENROLLED ON CARE COMMENTS,NEW ART TARGET CHILDREN,NEW ART TARGET ADULT,NEW ART TARGET TOTAL,STARTED ART CHILDREN,STARTED ART ADULT,STARTED ART TOTAL,PERCENTAGE OF TARGET STARTED ON ART CHILDREN,PERCENTAGE OF TARGET STARTED ON ART ADULT,PERCENTAGE OF TARGET STARTED ON ART ALL,PERCENTAGE OF TARGET STARTED ON ART COMMENTS,VIRAL LOAD TARGET CHILDREN,VIRAL LOAD TARGET ADULT,VIRAL LOAD TARGET TOTAL,VIRAL LOAD DONE CHILDREN,VIRAL LOAD DONE ADULT,VIRAL LOAD DONE TOTAL,PERCENTAGE OF VIRAL LOAD TESTS DONE AGAINST TARGET CHILDREN,PERCENTAGE OF VIRAL LOAD TESTS DONE AGAINST TARGET ADULT,PERCENTAGE OF VIRAL LOAD TESTS DONE AGAINST TARGET ALL,PERCENTAGE OF VIRAL LOAD TESTS DONE AGAINST TARGET COMMENTS,IPT TARGET CHILDREN,IPT TARGET ADULT,IPT TARGET TOTAL,IPT CHILDREN,IPT ADULT,IPT TOTAL,PERCENTAGE OF IPT DONE AGAINST TARGET CHILDREN,PERCENTAGE OF IPT DONE AGAINST TARGET ADULT,PERCENTAGE OF IPT DONE AGAINST TARGET ALL,PERCENTAGE OF IPT DONE AGAINST TARGET COMMENTS,PMTCT HIV POSITIVE TARGET,PMTCT HIV POSITIVE ,PERCENTAGE PMTCT HIV POSITIVE YIELD ACHIEVED AGAINST TARGET ALL,PERCENTAGE PMTCT HIV POSITIVE YIELD ACHIEVED AGAINST TARGET COMMENTS,EID TARGET,EID DONE,PERCENTAGE EID DONE AGAINST TARGET ALL,PERCENTAGE EID DONE AGAINST TARGET COMMENTS,VIRAL LOAD MOTHERS TARGET,VIRAL LOAD MOTHERS DONE,PERCENTAGE VIRAL LOAD TESTS DONE FOR MOTHERS AGAINST TARGET ALL,PERCENTAGE VIRAL LOAD TESTS DONE FOR MOTHERS AGAINST TARGET COMMENTS,REPORTING RATE" .split(","); // // Row rw0 = rawdata.createRow(0); rw0.setHeightInPoints(25); for (int d = 0; d < headername.length; d++) { Cell ce = rw0.createCell(d); ce.setCellValue(headername[d]); } dbConnweb conn = new dbConnweb(); conn.st.executeUpdate("SET GLOBAL max_allowed_packet = 209715200"); conn.rs = conn.st.executeQuery("SHOW VARIABLES LIKE 'max_allowed_packet' "); if (conn.rs.next()) { System.out.println("Generating report | Max_allowed_connection_" + conn.rs.getString(2)); } String where = " (enddate between '" + startdate + "' and '" + enddate + "') " + countywhere + " and (id not like '%_weekly%' ) "; String where1 = " (enddate between '" + startdate + "' and '" + enddate + "') and (id not like '%_weekly%' ) "; //get data in report form and add into the various json macros. //String query="select facilityname,startdate,enddate, hiv_pos_target_child,hiv_pos_target_adult,hiv_pos_target_total ,hiv_pos_child as hiv_pos_child , hiv_pos_adult as hiv_pos_adult ,hiv_pos_total as hiv_pos_total ,new_care_child as new_care_child ,new_care_adult as new_care_adult ,new_care_total as new_care_total , new_art_target_child ,new_art_target_adult ,new_art_target_total ,started_art_child as started_art_child, started_art_adult as started_art_adult ,started_art_total as started_art_total ,viral_load_target_child ,viral_load_target_adult ,viral_load_target_total ,viral_load_done_child as viral_load_done_child ,viral_load_done_adult as viral_load_done_adult ,viral_load_done_total as viral_load_done_total ,ipt_target_child ,ipt_target_adult ,ipt_target_total ,ipt_child as ipt_child ,ipt_adult as ipt_adult ,ipt_total as ipt_total ,testing_target_child ,testing_target_adult ,testing_target_total ,test_child as test_child ,test_adult as test_adult ,test_total as test_total , pmtct_hiv_pos_target,pmtct_hiv_pos as pmtct_hiv_pos, eid_target , eid_done as eid_done, viral_load_mothers_target, viral_load_mothers_done as viral_load_mothers_done from weekly_data_new join facility on weekly_data_new.facilityname=facility.facility_name "; String query = " select facility.county,facility.subcounty,cur.facilityname,cur.startdate,cur.enddate,cur.testing_target_child,cur.testing_target_adult,cur.testing_target_total,cur.test_child,cur.test_adult,cur.test_total,cur.tested_perc_child,cur.tested_perc_adult,cur.tested_perc_all,cur.tested_cmts,cur.hiv_pos_target_child,cur.hiv_pos_target_adult,cur.hiv_pos_target_total,cur.hiv_pos_child,cur.hiv_pos_adult,cur.hiv_pos_total,cur.hiv_pos_yield_perc_child,cur.hiv_pos_yield_perc_adult,cur.hiv_pos_yield_perc_all,cur.hiv_pos_yield_cmts,cur.new_care_child,cur.new_care_adult,cur.new_care_total,cur.hiv_pos_care_perc_child,cur.hiv_pos_care_perc_adult,cur.hiv_pos_care_perc_all,cur.hiv_pos_care_cmts,cur.new_art_target_child,cur.new_art_target_adult,cur.new_art_target_total,cur.started_art_child,cur.started_art_adult,cur.started_art_total,cur.started_art_perc_child,cur.started_art_perc_adult,cur.started_art_perc_all,cur.started_art_cmts,cur.viral_load_target_child,cur.viral_load_target_adult,cur.viral_load_target_total,cur.viral_load_done_child,cur.viral_load_done_adult,cur.viral_load_done_total,cur.viral_test_perc_child,cur.viral_test_perc_adult,cur.viral_test_perc_all,cur.viral_test_cmts,cur.ipt_target_child,cur.ipt_target_adult,cur.ipt_target_total,cur.ipt_child,cur.ipt_adult,cur.ipt_total,cur.ipt_done_perc_child,cur.ipt_done_perc_adult,cur.ipt_done_perc_all,cur.ipt_done_cmts,cur.pmtct_hiv_pos_target,cur.pmtct_hiv_pos,cur.pmtct_hiv_pos_perc,cur.pmtct_hiv_pos_cmts,cur.eid_target,cur.eid_done,cur.eid_done_perc,cur.eid_done_cmts,cur.viral_load_mothers_target,cur.viral_load_mothers_done,cur.viral_load_mothers_perc,cur.viral_load_mothers_cmts from weekly_data_new cur join facility on cur.facilityname=facility.facility_name where " + where + " " + " and not exists ( select * from weekly_data_new high join facility on high.facilityname=facility.facility_name where high.facilityname = cur.facilityname and high.enddate > cur.enddate and " + where + ")"; System.out.println("" + query); try { conn.rs = conn.st.executeQuery(query); String facilname = ""; String facilnamecopy = ""; String id = ""; //percentages String viral_load_mothers_perc = null; String eid_done_perc = null; String pmtct_hiv_pos_perc = null; //new percenatages String hiv_pos_yield_perc_child = null; String hiv_pos_yield_perc_adult = null; String hiv_pos_yield_perc_all = null; String hiv_pos_care_perc_child = null; String hiv_pos_care_perc_adult = null; String hiv_pos_care_perc_all = null; String started_art_perc_child = null; String started_art_perc_adult = null; String started_art_perc_all = null; String viral_test_perc_child = null; String viral_test_perc_adult = null; String viral_test_perc_all = null; String ipt_done_perc_child = null; String ipt_done_perc_adult = null; String ipt_done_perc_all = null; String tested_perc_child = null; String tested_perc_adult = null; String tested_perc_all = null; //comments String viral_load_mothers_cmts = null; String eid_done_cmts = null; String pmtct_hiv_pos_cmts = null; String hiv_pos_yield_cmts = null; String hiv_pos_care_cmts = null; String started_art_cmts = null; String viral_test_cmts = null; String ipt_done_cmts = null; String tested_cmts = null; Row rwx = null; int rowno = 0; while (conn.rs.next()) { rowno++; rwx = rawdata.createRow(rowno); rwx.setHeightInPoints(23); //do a per facility //System.out.println("___FACILITY : "+conn.rs.getString("facilityname")+" => hiv_pos_total "+conn.rs.getString("hiv_pos_total")); for (int a = 1; a <= header.length; a++) { System.out.print(header[a - 1] + " _ " + conn.rs.getString(a)); Cell ce = rwx.createCell(a - 1); if (a > 5 && a <= 73) { if (header[a - 1].contains("cmts")) { ce.setCellValue(conn.rs.getString(a)); } else if (header[a - 1].contains("_perc")) { ce.setCellValue(conn.rs.getInt(a)); System.out.println("__Percent"); } else { ce.setCellValue(conn.rs.getInt(a)); } } else { ce.setCellValue(conn.rs.getString(a)); } } //reporting rate int reportingrate = 0; String getrates = "select ROUND(((count(facilityname))/(" + weeks + ")*100)) as count from weekly_data_new where facilityname like '" + conn.rs.getString("facilityname") + "' and " + where1 + " "; conn.rs1 = conn.st1.executeQuery(getrates); if (conn.rs1.next()) { reportingrate = conn.rs1.getInt(1); } Cell ce = rwx.createCell(header.length); ce.setCellValue(reportingrate); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.conne != null) { conn.conne.close(); } Date dat = new Date(); String dat1 = dat.toString().replace(" ", "_"); // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=MOIS_Cum_Report_From" + startdate.replace(" ", "-") + "_To_" + enddate.replace(" ", "_") + ".xlsm"); response.setHeader("Set-Cookie", "fileDownload=true; path=/"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); wb.dispose(); // response.sendRedirect("index.jsp"); File file = new File(pathtodelete); if (file.delete()) { System.out.println(file.getName() + " is deleted!"); } else { System.out.println("Delete operation is failed."); } } catch (SQLException ex) { Logger.getLogger(excelreport.class.getName()).log(Level.SEVERE, null, ex); } } catch (InvalidFormatException ex) { Logger.getLogger(excelreport.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(excelreport.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:code.excelreport_cumulative.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); try {//from w w w .jav a 2 s. c o m /* TODO output your page here. You may use following sample code. */ String allpath = getServletContext().getRealPath("/MOIS.xlsm"); XSSFWorkbook wb1; String pathtodelete = null; Date da = new Date(); String dat2 = da.toString().replace(" ", "_"); dat2 = dat2.toString().replace(":", "_"); String mydrive = allpath.substring(0, 1); String np = mydrive + ":\\APHIAPLUS\\MOIS\\MACROS\\MOIS_REPORT" + dat2 + ".xlsm"; //check if file exists String sourcepath = getServletContext().getRealPath("/MOIS.xlsm"); File f = new File(np); if (!f.exists() && !f.isFile()) { /* do something */ copytemplates ct = new copytemplates(); System.out.println("Copying macros.."); ct.transfermacros(sourcepath, np); } else //copy the file alone { copytemplates ct = new copytemplates(); //copy the agebased file only ct.copymacros(sourcepath, np); } File allpathfile = new File(np); OPCPackage pkg = OPCPackage.open(allpathfile); pathtodelete = np; //wb = new XSSFWorkbook( OPCPackage.open(allpath) ); wb1 = new XSSFWorkbook(pkg); SXSSFWorkbook wb = new SXSSFWorkbook(wb1, 100); Sheet rawdata = wb.getSheet("Facility Report"); String startdate = "2016-10-01"; String enddate = "2016-10-30"; String county = ""; if (request.getParameter("startdate") != null) { startdate = request.getParameter("startdate"); } if (request.getParameter("county") != null) { if (!request.getParameter("county").equals("")) { county = request.getParameter("county"); } } if (request.getParameter("enddate") != null) { enddate = request.getParameter("enddate"); } String countywhere = " and 1=1"; if (!county.equals("")) { countywhere = " and ( county like '" + county + "' )"; } DateTime dateTime1 = new DateTime(startdate); DateTime dateTime2 = new DateTime(enddate); int weeks = Weeks.weeksBetween(dateTime1, dateTime2).getWeeks(); System.out.println("___Weeks____" + weeks); if (weeks == 0) { weeks = 1; } //String header[]="county,subcounty,facilityname,startdate,enddate,hiv_pos_target_child,hiv_pos_target_adult,hiv_pos_target_total,hiv_pos_child,hiv_pos_adult,hiv_pos_total,new_care_child,new_care_adult,new_care_total,new_art_target_child,new_art_target_adult,new_art_target_total,started_art_child,started_art_adult,started_art_total,viral_load_target_child,viral_load_target_adult,viral_load_target_total,viral_load_done_child,viral_load_done_adult,viral_load_done_total,ipt_target_child,ipt_target_adult,ipt_target_total,ipt_child,ipt_adult,ipt_total,testing_target_child,testing_target_adult,testing_target_total,test_child,test_adult,test_total,pmtct_hiv_pos_target,pmtct_hiv_pos,eid_target,eid_done,viral_load_mothers_target,viral_load_mothers_done,hiv_pos_yield_perc_child,hiv_pos_yield_perc_adult,hiv_pos_yield_perc_all,hiv_pos_care_perc_child,hiv_pos_care_perc_adult,hiv_pos_care_perc_all,started_art_perc_child,started_art_perc_adult,started_art_perc_all,viral_test_perc_child,viral_test_perc_adult,viral_test_perc_all,ipt_done_perc_child,ipt_done_perc_adult,ipt_done_perc_all,tested_perc_child,tested_perc_adult,tested_perc_all,viral_load_mothers_perc,eid_done_perc,pmtct_hiv_pos_perc,hiv_pos_yield_cmts,hiv_pos_care_cmts,started_art_cmts,viral_test_cmts,ipt_done_cmts,tested_cmts,viral_load_mothers_cmts,eid_done_cmts,pmtct_hiv_pos_cmts".split(","); //String headername[]="COUNTY,SUB-COUNTY,FACILITY,START DATE,END DATE,HIV POSITIVE TARGET CHILDREN,HIV POSITIVE TARGET ADULT,HIV POSITIVE TARGET TOTAL,HIV POSITIVE CHILDREN,HIV POSITIVE ADULT,HIV POSITIVE TOTAL, NEW CARE CHILDREN,NEW CARE ADULT,NEW CARE TOTAL,NEW ART TARGET CHILDREN,NEW ART TARGET ADULT,NEW ART TARGET TOTAL,STARTED ART CHILDREN,STARTED ART ADULT,STARTED ART TOTAL,VIRAL LOAD TARGET CHILDREN,VIRAL LOAD TARGET ADULT,VIRAL LOAD TARGET TOTAL,VIRAL LOAD DONE CHILDREN,VIRAL LOAD DONE ADULT,VIRAL LOAD DONE TOTAL,IPT TARGET CHILDREN,IPT TARGET ADULT,IPT TARGET TOTAL,IPT CHILDREN,IPT ADULT,IPT TOTAL,TESTING TARGET CHILDREN,TESTING TARGET ADULT,TESTING TARGET TOTAL,TESTING CHILDREN,TESTING ADULT,TESTING TOTAL,PMTCT HIV POSITIVE TARGET,PMTCT HIV POSITIVE ,EID TARGET,EID DONE,VIRAL LOAD MOTHERS TARGET,VIRAL LOAD MOTHERS DONE,HIV POSITIVE YIELD CHILDREN ,HIV POSITIVE YIELD ADULT,HIV POSITIVE YIELD ALL,HIV POSITIVE CARE CHILDREN,HIV POSITIVE CARE ADULT,HIV POSITIVE CARE ALL,STARTED ART CHILDREN,STARTED ART ADULT,STARTED ART ,VIRAL TEST CHILDREN,VIRAL TEST ADULT,VIRAL TEST ALL,IPT DONE CHILD,IPT DONE ADULT,IPT DONE ALL,TESTED CHILD,TESTED ADULT,TESTED ALL,VIRAL LOAD MOTHERS ,EID DONE,PMTCT HIV POSITIVE,HIV POSITIVE YIELD COMMENTS,HIV POSITIVE CARE COMMENTS,STARTED ART COMMENTS,VIRAL TEST COMMENTS,IPT DONE COMMENTS ,TESTED COMMENTS,VIRAL LOAD MOTHERS COMMENTS,EID DONE COMMENTS,PMTCT HIV POSITIVE COMMENTS,REPORTING RATE".split(","); String header[] = "county,subcounty,facilityname,startdate,enddate,testing_target_child,testing_target_adult,testing_target_total,test_child,test_adult,test_total,tested_perc_child,tested_perc_adult,tested_perc_all,tested_cmts,hiv_pos_target_child,hiv_pos_target_adult,hiv_pos_target_total,hiv_pos_child,hiv_pos_adult,hiv_pos_total,hiv_pos_yield_perc_child,hiv_pos_yield_perc_adult,hiv_pos_yield_perc_all,hiv_pos_yield_cmts,new_care_child,new_care_adult,new_care_total,hiv_pos_care_perc_child,hiv_pos_care_perc_adult,hiv_pos_care_perc_all,hiv_pos_care_cmts,new_art_target_child,new_art_target_adult,new_art_target_total,started_art_child,started_art_adult,started_art_total,started_art_perc_child,started_art_perc_adult,started_art_perc_all,started_art_cmts,viral_load_target_child,viral_load_target_adult,viral_load_target_total,viral_load_done_child,viral_load_done_adult,viral_load_done_total,viral_test_perc_child,viral_test_perc_adult,viral_test_perc_all,viral_test_cmts,ipt_target_child,ipt_target_adult,ipt_target_total,ipt_child,ipt_adult,ipt_total,ipt_done_perc_child,ipt_done_perc_adult,ipt_done_perc_all,ipt_done_cmts,pmtct_hiv_pos_target,pmtct_hiv_pos,pmtct_hiv_pos_perc,pmtct_hiv_pos_cmts,eid_target,eid_done,eid_done_perc,eid_done_cmts,viral_load_mothers_target,viral_load_mothers_done,viral_load_mothers_perc,viral_load_mothers_cmts" .split(","); String headername[] = "COUNTY,SUB-COUNTY,FACILITY,START DATE,END DATE,TESTING TARGET CHILDREN,TESTING TARGET ADULT,TESTING TARGET TOTAL,TESTING CHILDREN,TESTING ADULT,TESTING TOTAL,PERCENTAGE TESTED AGAINST TARGET CHILDREN,PERCENTAGE TESTED AGAINST TARGET ADULT,PERCENTAGE TESTED AGAINST TARGET ALL,PERCENTAGE TESTED AGAINST TARGET COMMENTS,HIV POSITIVE TARGET CHILDREN,HIV POSITIVE TARGET ADULT,HIV POSITIVE TARGET TOTAL,HIV POSITIVE CHILDREN,HIV POSITIVE ADULT,HIV POSITIVE TOTAL,PERCENTAGE HIV POSITIVE TARGET YIELD ACHIEVED CHILDREN,PERCENTAGE HIV POSITIVE TARGET YIELD ACHIEVED ADULT,PERCENTAGE HIV POSITIVE TARGET YIELD ACHIEVED ALL,PERCENTAGE HIV POSITIVE TARGET YIELD ACHIEVED COMMENTS, NEW CARE CHILDREN,NEW CARE ADULT,NEW CARE TOTAL,PERCENTAGE HIV POSITIVE ENROLLED ON CARE CHILDREN,PERCENTAGE HIV POSITIVE ENROLLED ON CARE ADULT,PERCENTAGE HIV POSITIVE ENROLLED ON CARE ALL,PERCENTAGE HIV POSITIVE ENROLLED ON CARE COMMENTS,NEW ART TARGET CHILDREN,NEW ART TARGET ADULT,NEW ART TARGET TOTAL,STARTED ART CHILDREN,STARTED ART ADULT,STARTED ART TOTAL,PERCENTAGE OF TARGET STARTED ON ART CHILDREN,PERCENTAGE OF TARGET STARTED ON ART ADULT,PERCENTAGE OF TARGET STARTED ON ART ALL,PERCENTAGE OF TARGET STARTED ON ART COMMENTS,VIRAL LOAD TARGET CHILDREN,VIRAL LOAD TARGET ADULT,VIRAL LOAD TARGET TOTAL,VIRAL LOAD DONE CHILDREN,VIRAL LOAD DONE ADULT,VIRAL LOAD DONE TOTAL,PERCENTAGE OF VIRAL LOAD TESTS DONE AGAINST TARGET CHILDREN,PERCENTAGE OF VIRAL LOAD TESTS DONE AGAINST TARGET ADULT,PERCENTAGE OF VIRAL LOAD TESTS DONE AGAINST TARGET ALL,PERCENTAGE OF VIRAL LOAD TESTS DONE AGAINST TARGET COMMENTS,IPT TARGET CHILDREN,IPT TARGET ADULT,IPT TARGET TOTAL,IPT CHILDREN,IPT ADULT,IPT TOTAL,PERCENTAGE OF IPT DONE AGAINST TARGET CHILDREN,PERCENTAGE OF IPT DONE AGAINST TARGET ADULT,PERCENTAGE OF IPT DONE AGAINST TARGET ALL,PERCENTAGE OF IPT DONE AGAINST TARGET COMMENTS,PMTCT HIV POSITIVE TARGET,PMTCT HIV POSITIVE ,PERCENTAGE PMTCT HIV POSITIVE YIELD ACHIEVED AGAINST TARGET ALL,PERCENTAGE PMTCT HIV POSITIVE YIELD ACHIEVED AGAINST TARGET COMMENTS,EID TARGET,EID DONE,PERCENTAGE EID DONE AGAINST TARGET ALL,PERCENTAGE EID DONE AGAINST TARGET COMMENTS,VIRAL LOAD MOTHERS TARGET,VIRAL LOAD MOTHERS DONE,PERCENTAGE VIRAL LOAD TESTS DONE FOR MOTHERS AGAINST TARGET ALL,PERCENTAGE VIRAL LOAD TESTS DONE FOR MOTHERS AGAINST TARGET COMMENTS,REPORTING RATE" .split(","); // // Row rw0 = rawdata.createRow(0); rw0.setHeightInPoints(25); for (int d = 0; d < headername.length; d++) { Cell ce = rw0.createCell(d); ce.setCellValue(headername[d]); } dbConnweb conn = new dbConnweb(); conn.st.executeUpdate("SET GLOBAL max_allowed_packet = 209715200"); conn.rs = conn.st.executeQuery("SHOW VARIABLES LIKE 'max_allowed_packet' "); if (conn.rs.next()) { System.out.println("Generating report | Max_allowed_connection_" + conn.rs.getString(2)); } String where = " (enddate between '" + startdate + "' and '" + enddate + "') " + countywhere + " and cur.id like '%_weekly%' "; String where1 = " (enddate between '" + startdate + "' and '" + enddate + "') and weekly_data_new.id like '%_weekly%' "; String period = "1"; String yr = enddate.substring(0, 4); String tar[] = enddate.split("-"); int mwaka = new Integer(yr); if (new Integer(tar[1]) >= 10 && new Integer(tar[1]) <= 12) { mwaka = mwaka + 1; } //get data in report form and add into the various json macros. //String query="select facilityname,startdate,enddate, hiv_pos_target_child,hiv_pos_target_adult,hiv_pos_target_total ,hiv_pos_child as hiv_pos_child , hiv_pos_adult as hiv_pos_adult ,hiv_pos_total as hiv_pos_total ,new_care_child as new_care_child ,new_care_adult as new_care_adult ,new_care_total as new_care_total , new_art_target_child ,new_art_target_adult ,new_art_target_total ,started_art_child as started_art_child, started_art_adult as started_art_adult ,started_art_total as started_art_total ,viral_load_target_child ,viral_load_target_adult ,viral_load_target_total ,viral_load_done_child as viral_load_done_child ,viral_load_done_adult as viral_load_done_adult ,viral_load_done_total as viral_load_done_total ,ipt_target_child ,ipt_target_adult ,ipt_target_total ,ipt_child as ipt_child ,ipt_adult as ipt_adult ,ipt_total as ipt_total ,testing_target_child ,testing_target_adult ,testing_target_total ,test_child as test_child ,test_adult as test_adult ,test_total as test_total , pmtct_hiv_pos_target,pmtct_hiv_pos as pmtct_hiv_pos, eid_target , eid_done as eid_done, viral_load_mothers_target, viral_load_mothers_done as viral_load_mothers_done from weekly_data_new join facility on weekly_data_new.facilityname=facility.facility_name "; String query = " select facility.county,facility.subcounty,cur.facilityname,cur.startdate,cur.enddate,case when ROUND(targets.testing_target_child/" + period + ") >0 then ROUND(targets.testing_target_child/" + period + ") else 1 end as testing_target_child,case when ROUND(targets.testing_target_adult/" + period + ") >0 then ROUND(targets.testing_target_adult/" + period + ") else 1 end as testing_target_adult,case when ROUND(targets.testing_target_total/" + period + ") >0 then ROUND(targets.testing_target_total/" + period + ") else 1 end as testing_target_total,SUM(cur.test_child) as test_child,SUM(cur.test_adult) as test_adult,SUM(cur.test_total) as test_total,AVG(cur.tested_perc_child) as tested_perc_child,AVG(cur.tested_perc_adult) as tested_perc_adult,AVG(cur.tested_perc_all) as tested_perc_all,cur.tested_cmts,case when ROUND(targets.hiv_pos_target_child/" + period + ") >0 then ROUND(targets.hiv_pos_target_child/" + period + ") else 1 end as hiv_pos_target_child,case when ROUND(targets.hiv_pos_target_adult/" + period + ") >0 then ROUND(targets.hiv_pos_target_adult/" + period + ") else 1 end as hiv_pos_target_adult,case when ROUND(targets.hiv_pos_target_total/" + period + ") >0 then ROUND(targets.hiv_pos_target_total/" + period + ") else 1 end as hiv_pos_target_total,SUM(cur.hiv_pos_child) as hiv_pos_child,SUM(cur.hiv_pos_adult) as hiv_pos_adult,SUM(cur.hiv_pos_total) as hiv_pos_total,AVG(cur.hiv_pos_yield_perc_child) as hiv_pos_yield_perc_child,AVG(cur.hiv_pos_yield_perc_adult) as hiv_pos_yield_perc_adult,AVG(cur.hiv_pos_yield_perc_all) as hiv_pos_yield_perc_all,cur.hiv_pos_yield_cmts,SUM(cur.new_care_child) as new_care_child,SUM(cur.new_care_adult) as new_care_adult,SUM(cur.new_care_total) as new_care_total,AVG(cur.hiv_pos_care_perc_child) as hiv_pos_care_perc_child,AVG(cur.hiv_pos_care_perc_adult) as hiv_pos_care_perc_adult,AVG(cur.hiv_pos_care_perc_all) as hiv_pos_care_perc_all,cur.hiv_pos_care_cmts,case when ROUND(targets.new_art_target_child/" + period + ") >0 then ROUND(targets.new_art_target_child/" + period + ") else 1 end as new_art_target_child,case when ROUND(targets.new_art_target_adult/" + period + ") >0 then ROUND(targets.new_art_target_adult/" + period + ") else 1 end as new_art_target_adult,case when ROUND(targets.new_art_target_total/" + period + ") >0 then ROUND(targets.new_art_target_total/" + period + ") else 1 end as new_art_target_total,SUM(cur.started_art_child) as started_art_child,SUM(cur.started_art_adult) as started_art_adult,SUM(cur.started_art_total) as started_art_total,AVG(cur.started_art_perc_child) as started_art_perc_child,AVG(cur.started_art_perc_adult) as started_art_perc_adult, AVG(cur.started_art_perc_all) as started_art_perc_all,cur.started_art_cmts,case when ROUND(targets.viral_load_target_child/" + period + ") >0 then ROUND(targets.viral_load_target_child/" + period + ") else 1 end as viral_load_target_child,case when ROUND(targets.viral_load_target_adult/" + period + ") >0 then ROUND(targets.viral_load_target_adult/" + period + ") else 1 end as viral_load_target_adult,case when ROUND(targets.viral_load_target_total/" + period + ") >0 then ROUND(targets.viral_load_target_total/" + period + ") else 1 end as viral_load_target_total,SUM(cur.viral_load_done_child) as viral_load_done_child,SUM(cur.viral_load_done_adult) as viral_load_done_adult, SUM(cur.viral_load_done_total) as viral_load_done_total,AVG(cur.viral_test_perc_child) as viral_test_perc_child,AVG(cur.viral_test_perc_adult) as viral_test_perc_adult, AVG(cur.viral_test_perc_all) as viral_test_perc_all,cur.viral_test_cmts,case when ROUND(targets.ipt_target_child/" + period + ") >0 then ROUND(targets.ipt_target_child/" + period + ") else 1 end as ipt_target_child,case when ROUND(targets.ipt_target_adult/" + period + ") >0 then ROUND(targets.ipt_target_adult/" + period + ") else 1 end as ipt_target_adult,case when ROUND(targets.ipt_target_total/" + period + ") >0 then ROUND(targets.ipt_target_total/" + period + ") else 1 end as ipt_target_total,SUM(cur.ipt_child) as ipt_child,SUM(cur.ipt_adult) as ipt_adult ,SUM(cur.ipt_total) as ipt_total,AVG(cur.ipt_done_perc_child) as ipt_done_perc_child,AVG(cur.ipt_done_perc_adult) as ipt_done_perc_adult, AVG(cur.ipt_done_perc_all) as ipt_done_perc_all,cur.ipt_done_cmts,case when ROUND(targets.pmtct_hiv_pos_target/" + period + ") >0 then ROUND(targets.pmtct_hiv_pos_target/" + period + ") else 1 end as pmtct_hiv_pos_target,SUM(cur.pmtct_hiv_pos) as pmtct_hiv_pos,AVG(cur.pmtct_hiv_pos_perc) as pmtct_hiv_pos_perc,cur.pmtct_hiv_pos_cmts,case when ROUND(targets.eid_target/" + period + ") >0 then ROUND(targets.eid_target/" + period + ") else 1 end as eid_target,SUM(cur.eid_done) as eid_done,AVG(cur.eid_done_perc) as eid_done_perc,cur.eid_done_cmts,case when ROUND(targets.viral_load_mothers_target/" + period + ") >0 then ROUND(targets.viral_load_mothers_target/" + period + ") else 1 end as viral_load_mothers_target,SUM(cur.viral_load_mothers_done) as viral_load_mothers_done,AVG(cur.viral_load_mothers_perc) as viral_load_mothers_perc,cur.viral_load_mothers_cmts from weekly_data_new cur join (facility join targets on (facility.mflcode=targets.facility and targets.year='" + mwaka + "' ) ) on cur.facilityname=facility.facility_name where " + where + " " + " group by facilityname "; System.out.println("" + query); try { conn.rs = conn.st.executeQuery(query); String facilname = ""; String facilnamecopy = ""; String id = ""; //percentages String viral_load_mothers_perc = null; String eid_done_perc = null; String pmtct_hiv_pos_perc = null; //new percenatages String hiv_pos_yield_perc_child = null; String hiv_pos_yield_perc_adult = null; String hiv_pos_yield_perc_all = null; String hiv_pos_care_perc_child = null; String hiv_pos_care_perc_adult = null; String hiv_pos_care_perc_all = null; String started_art_perc_child = null; String started_art_perc_adult = null; String started_art_perc_all = null; String viral_test_perc_child = null; String viral_test_perc_adult = null; String viral_test_perc_all = null; String ipt_done_perc_child = null; String ipt_done_perc_adult = null; String ipt_done_perc_all = null; String tested_perc_child = null; String tested_perc_adult = null; String tested_perc_all = null; //comments String viral_load_mothers_cmts = null; String eid_done_cmts = null; String pmtct_hiv_pos_cmts = null; String hiv_pos_yield_cmts = null; String hiv_pos_care_cmts = null; String started_art_cmts = null; String viral_test_cmts = null; String ipt_done_cmts = null; String tested_cmts = null; Row rwx = null; int rowno = 0; while (conn.rs.next()) { rowno++; rwx = rawdata.createRow(rowno); rwx.setHeightInPoints(23); //do a per facility //System.out.println("___FACILITY : "+conn.rs.getString("facilityname")+" => hiv_pos_total "+conn.rs.getString("hiv_pos_total")); for (int a = 1; a <= header.length; a++) { System.out.print(header[a - 1] + " _ " + conn.rs.getString(a)); Cell ce = rwx.createCell(a - 1); if (a > 5 && a <= 73) { if (header[a - 1].contains("cmts")) { ce.setCellValue(conn.rs.getString(a)); } else if (header[a - 1].contains("_perc")) { ce.setCellValue(conn.rs.getInt(a)); System.out.println("__Percent"); } else { ce.setCellValue(conn.rs.getInt(a)); } } else { ce.setCellValue(conn.rs.getString(a)); } } //reporting rate int reportingrate = 0; String getrates = "select ROUND(((count(facilityname))/(" + weeks + ")*100)) as count from weekly_data_new where facilityname like '" + conn.rs.getString("facilityname") + "' and " + where1 + " "; conn.rs1 = conn.st1.executeQuery(getrates); if (conn.rs1.next()) { reportingrate = conn.rs1.getInt(1); } Cell ce = rwx.createCell(header.length); ce.setCellValue(reportingrate); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.conne != null) { conn.conne.close(); } Date dat = new Date(); String dat1 = dat.toString().replace(" ", "_"); // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=MOIS_Cum_Rpt_From" + startdate.replace(" ", "-") + "_To_" + enddate.replace(" ", "_") + ".xlsm"); response.setHeader("Set-Cookie", "fileDownload=true; path=/"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); wb.dispose(); // response.sendRedirect("index.jsp"); File file = new File(pathtodelete); if (file.delete()) { System.out.println(file.getName() + " is deleted!"); } else { System.out.println("Delete operation is failed."); } } catch (SQLException ex) { Logger.getLogger(excelreport.class.getName()).log(Level.SEVERE, null, ex); } } catch (InvalidFormatException ex) { Logger.getLogger(excelreport.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(excelreport.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:code.excelreport_weekly.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); try {/*from w w w .j ava2 s . c o m*/ /* TODO output your page here. You may use following sample code. */ String allpath = getServletContext().getRealPath("/MOIS_WEEKLY.xlsm"); XSSFWorkbook wb1; String pathtodelete = null; Date da = new Date(); String dat2 = da.toString().replace(" ", "_"); dat2 = dat2.toString().replace(":", "_"); String mydrive = allpath.substring(0, 1); String np = mydrive + ":\\APHIAPLUS\\MOIS\\MACROS\\MOIS_REPORT" + dat2 + ".xlsm"; //check if file exists String sourcepath = getServletContext().getRealPath("/MOIS_WEEKLY.xlsm"); File f = new File(np); if (!f.exists() && !f.isFile()) { /* do something */ copytemplates ct = new copytemplates(); System.out.println("Copying macros.."); ct.transfermacros(sourcepath, np); } else //copy the file alone { copytemplates ct = new copytemplates(); //copy the agebased file only ct.copymacros(sourcepath, np); } File allpathfile = new File(np); OPCPackage pkg = OPCPackage.open(allpathfile); pathtodelete = np; //wb = new XSSFWorkbook( OPCPackage.open(allpath) ); wb1 = new XSSFWorkbook(pkg); SXSSFWorkbook wb = new SXSSFWorkbook(wb1, 100); Sheet rawdata = wb.getSheet("Facility Report"); String startdate = "2016-10-01"; String enddate = "2016-10-30"; String county = ""; if (request.getParameter("startdate") != null) { startdate = request.getParameter("startdate"); } if (request.getParameter("county") != null) { if (!request.getParameter("county").equals("")) { county = request.getParameter("county"); } } if (request.getParameter("enddate") != null) { enddate = request.getParameter("enddate"); } String countywhere = " and 1=1"; if (!county.equals("")) { countywhere = " and ( county like '" + county + "' )"; } DateTime dateTime1 = new DateTime(startdate); DateTime dateTime2 = new DateTime(enddate); int weeks = Weeks.weeksBetween(dateTime1, dateTime2).getWeeks(); System.out.println("___Weeks____" + weeks); if (weeks == 0) { weeks = 1; } //String header[]="county,subcounty,facilityname,startdate,enddate,hiv_pos_target_child,hiv_pos_target_adult,hiv_pos_target_total,hiv_pos_child,hiv_pos_adult,hiv_pos_total,new_care_child,new_care_adult,new_care_total,new_art_target_child,new_art_target_adult,new_art_target_total,started_art_child,started_art_adult,started_art_total,viral_load_target_child,viral_load_target_adult,viral_load_target_total,viral_load_done_child,viral_load_done_adult,viral_load_done_total,ipt_target_child,ipt_target_adult,ipt_target_total,ipt_child,ipt_adult,ipt_total,testing_target_child,testing_target_adult,testing_target_total,test_child,test_adult,test_total,pmtct_hiv_pos_target,pmtct_hiv_pos,eid_target,eid_done,viral_load_mothers_target,viral_load_mothers_done,hiv_pos_yield_perc_child,hiv_pos_yield_perc_adult,hiv_pos_yield_perc_all,hiv_pos_care_perc_child,hiv_pos_care_perc_adult,hiv_pos_care_perc_all,started_art_perc_child,started_art_perc_adult,started_art_perc_all,viral_test_perc_child,viral_test_perc_adult,viral_test_perc_all,ipt_done_perc_child,ipt_done_perc_adult,ipt_done_perc_all,tested_perc_child,tested_perc_adult,tested_perc_all,viral_load_mothers_perc,eid_done_perc,pmtct_hiv_pos_perc,hiv_pos_yield_cmts,hiv_pos_care_cmts,started_art_cmts,viral_test_cmts,ipt_done_cmts,tested_cmts,viral_load_mothers_cmts,eid_done_cmts,pmtct_hiv_pos_cmts".split(","); //String headername[]="COUNTY,SUB-COUNTY,FACILITY,START DATE,END DATE,HIV POSITIVE TARGET CHILDREN,HIV POSITIVE TARGET ADULT,HIV POSITIVE TARGET TOTAL,HIV POSITIVE CHILDREN,HIV POSITIVE ADULT,HIV POSITIVE TOTAL, NEW CARE CHILDREN,NEW CARE ADULT,NEW CARE TOTAL,NEW ART TARGET CHILDREN,NEW ART TARGET ADULT,NEW ART TARGET TOTAL,STARTED ART CHILDREN,STARTED ART ADULT,STARTED ART TOTAL,VIRAL LOAD TARGET CHILDREN,VIRAL LOAD TARGET ADULT,VIRAL LOAD TARGET TOTAL,VIRAL LOAD DONE CHILDREN,VIRAL LOAD DONE ADULT,VIRAL LOAD DONE TOTAL,IPT TARGET CHILDREN,IPT TARGET ADULT,IPT TARGET TOTAL,IPT CHILDREN,IPT ADULT,IPT TOTAL,TESTING TARGET CHILDREN,TESTING TARGET ADULT,TESTING TARGET TOTAL,TESTING CHILDREN,TESTING ADULT,TESTING TOTAL,PMTCT HIV POSITIVE TARGET,PMTCT HIV POSITIVE ,EID TARGET,EID DONE,VIRAL LOAD MOTHERS TARGET,VIRAL LOAD MOTHERS DONE,HIV POSITIVE YIELD CHILDREN ,HIV POSITIVE YIELD ADULT,HIV POSITIVE YIELD ALL,HIV POSITIVE CARE CHILDREN,HIV POSITIVE CARE ADULT,HIV POSITIVE CARE ALL,STARTED ART CHILDREN,STARTED ART ADULT,STARTED ART ,VIRAL TEST CHILDREN,VIRAL TEST ADULT,VIRAL TEST ALL,IPT DONE CHILD,IPT DONE ADULT,IPT DONE ALL,TESTED CHILD,TESTED ADULT,TESTED ALL,VIRAL LOAD MOTHERS ,EID DONE,PMTCT HIV POSITIVE,HIV POSITIVE YIELD COMMENTS,HIV POSITIVE CARE COMMENTS,STARTED ART COMMENTS,VIRAL TEST COMMENTS,IPT DONE COMMENTS ,TESTED COMMENTS,VIRAL LOAD MOTHERS COMMENTS,EID DONE COMMENTS,PMTCT HIV POSITIVE COMMENTS,REPORTING RATE".split(","); String header[] = "county,subcounty,facilityname,startdate,enddate,testing_target_child,testing_target_adult,testing_target_total,test_child,test_adult,test_total,tested_perc_child,tested_perc_adult,tested_perc_all,tested_cmts,hiv_pos_target_child,hiv_pos_target_adult,hiv_pos_target_total,hiv_pos_child,hiv_pos_adult,hiv_pos_total,hiv_pos_yield_perc_child,hiv_pos_yield_perc_adult,hiv_pos_yield_perc_all,hiv_pos_yield_cmts,new_care_child,new_care_adult,new_care_total,hiv_pos_care_perc_child,hiv_pos_care_perc_adult,hiv_pos_care_perc_all,hiv_pos_care_cmts,new_art_target_child,new_art_target_adult,new_art_target_total,started_art_child,started_art_adult,started_art_total,started_art_perc_child,started_art_perc_adult,started_art_perc_all,started_art_cmts,viral_load_target_child,viral_load_target_adult,viral_load_target_total,viral_load_done_child,viral_load_done_adult,viral_load_done_total,viral_test_perc_child,viral_test_perc_adult,viral_test_perc_all,viral_test_cmts,ipt_target_child,ipt_target_adult,ipt_target_total,ipt_child,ipt_adult,ipt_total,ipt_done_perc_child,ipt_done_perc_adult,ipt_done_perc_all,ipt_done_cmts,pmtct_hiv_pos_target,pmtct_hiv_pos,pmtct_hiv_pos_perc,pmtct_hiv_pos_cmts,eid_target,eid_done,eid_done_perc,eid_done_cmts,viral_load_mothers_target,viral_load_mothers_done,viral_load_mothers_perc,viral_load_mothers_cmts" .split(","); String headername[] = "COUNTY,SUB-COUNTY,FACILITY,START DATE,END DATE,TESTING TARGET CHILDREN,TESTING TARGET ADULT,TESTING TARGET TOTAL,TESTING CHILDREN,TESTING ADULT,TESTING TOTAL,PERCENTAGE TESTED AGAINST TARGET CHILDREN,PERCENTAGE TESTED AGAINST TARGET ADULT,PERCENTAGE TESTED AGAINST TARGET ALL,PERCENTAGE TESTED AGAINST TARGET COMMENTS,HIV POSITIVE TARGET CHILDREN,HIV POSITIVE TARGET ADULT,HIV POSITIVE TARGET TOTAL,HIV POSITIVE CHILDREN,HIV POSITIVE ADULT,HIV POSITIVE TOTAL,PERCENTAGE HIV POSITIVE TARGET YIELD ACHIEVED CHILDREN,PERCENTAGE HIV POSITIVE TARGET YIELD ACHIEVED ADULT,PERCENTAGE HIV POSITIVE TARGET YIELD ACHIEVED ALL,PERCENTAGE HIV POSITIVE TARGET YIELD ACHIEVED COMMENTS, NEW CARE CHILDREN,NEW CARE ADULT,NEW CARE TOTAL,PERCENTAGE HIV POSITIVE ENROLLED ON CARE CHILDREN,PERCENTAGE HIV POSITIVE ENROLLED ON CARE ADULT,PERCENTAGE HIV POSITIVE ENROLLED ON CARE ALL,PERCENTAGE HIV POSITIVE ENROLLED ON CARE COMMENTS,NEW ART TARGET CHILDREN,NEW ART TARGET ADULT,NEW ART TARGET TOTAL,STARTED ART CHILDREN,STARTED ART ADULT,STARTED ART TOTAL,PERCENTAGE OF TARGET STARTED ON ART CHILDREN,PERCENTAGE OF TARGET STARTED ON ART ADULT,PERCENTAGE OF TARGET STARTED ON ART ALL,PERCENTAGE OF TARGET STARTED ON ART COMMENTS,VIRAL LOAD TARGET CHILDREN,VIRAL LOAD TARGET ADULT,VIRAL LOAD TARGET TOTAL,VIRAL LOAD DONE CHILDREN,VIRAL LOAD DONE ADULT,VIRAL LOAD DONE TOTAL,PERCENTAGE OF VIRAL LOAD TESTS DONE AGAINST TARGET CHILDREN,PERCENTAGE OF VIRAL LOAD TESTS DONE AGAINST TARGET ADULT,PERCENTAGE OF VIRAL LOAD TESTS DONE AGAINST TARGET ALL,PERCENTAGE OF VIRAL LOAD TESTS DONE AGAINST TARGET COMMENTS,IPT TARGET CHILDREN,IPT TARGET ADULT,IPT TARGET TOTAL,IPT CHILDREN,IPT ADULT,IPT TOTAL,PERCENTAGE OF IPT DONE AGAINST TARGET CHILDREN,PERCENTAGE OF IPT DONE AGAINST TARGET ADULT,PERCENTAGE OF IPT DONE AGAINST TARGET ALL,PERCENTAGE OF IPT DONE AGAINST TARGET COMMENTS,PMTCT HIV POSITIVE TARGET,PMTCT HIV POSITIVE ,PERCENTAGE PMTCT HIV POSITIVE YIELD ACHIEVED AGAINST TARGET ALL,PERCENTAGE PMTCT HIV POSITIVE YIELD ACHIEVED AGAINST TARGET COMMENTS,EID TARGET,EID DONE,PERCENTAGE EID DONE AGAINST TARGET ALL,PERCENTAGE EID DONE AGAINST TARGET COMMENTS,VIRAL LOAD MOTHERS TARGET,VIRAL LOAD MOTHERS DONE,PERCENTAGE VIRAL LOAD TESTS DONE FOR MOTHERS AGAINST TARGET ALL,PERCENTAGE VIRAL LOAD TESTS DONE FOR MOTHERS AGAINST TARGET COMMENTS,REPORTING RATE" .split(","); // // Row rw0 = rawdata.createRow(0); rw0.setHeightInPoints(25); for (int d = 0; d < headername.length; d++) { Cell ce = rw0.createCell(d); ce.setCellValue(headername[d]); } dbConnweb conn = new dbConnweb(); conn.st.executeUpdate("SET GLOBAL max_allowed_packet = 209715200"); conn.rs = conn.st.executeQuery("SHOW VARIABLES LIKE 'max_allowed_packet' "); if (conn.rs.next()) { System.out.println("Generating report | Max_allowed_connection_" + conn.rs.getString(2)); } String where = " (enddate between '" + startdate + "' and '" + enddate + "') " + countywhere + " and cur.id like '%_weekly%' "; String where1 = " (enddate between '" + startdate + "' and '" + enddate + "') and weekly_data_new.id like '%_weekly%' "; String period = "52"; String yr = enddate.substring(0, 4); String tar[] = enddate.split("-"); int mwaka = new Integer(yr); if (new Integer(tar[1]) >= 10 && new Integer(tar[1]) <= 12) { mwaka = mwaka + 1; } //get data in report form and add into the various json macros. //String query="select facilityname,startdate,enddate, hiv_pos_target_child,hiv_pos_target_adult,hiv_pos_target_total ,hiv_pos_child as hiv_pos_child , hiv_pos_adult as hiv_pos_adult ,hiv_pos_total as hiv_pos_total ,new_care_child as new_care_child ,new_care_adult as new_care_adult ,new_care_total as new_care_total , new_art_target_child ,new_art_target_adult ,new_art_target_total ,started_art_child as started_art_child, started_art_adult as started_art_adult ,started_art_total as started_art_total ,viral_load_target_child ,viral_load_target_adult ,viral_load_target_total ,viral_load_done_child as viral_load_done_child ,viral_load_done_adult as viral_load_done_adult ,viral_load_done_total as viral_load_done_total ,ipt_target_child ,ipt_target_adult ,ipt_target_total ,ipt_child as ipt_child ,ipt_adult as ipt_adult ,ipt_total as ipt_total ,testing_target_child ,testing_target_adult ,testing_target_total ,test_child as test_child ,test_adult as test_adult ,test_total as test_total , pmtct_hiv_pos_target,pmtct_hiv_pos as pmtct_hiv_pos, eid_target , eid_done as eid_done, viral_load_mothers_target, viral_load_mothers_done as viral_load_mothers_done from weekly_data_new join facility on weekly_data_new.facilityname=facility.facility_name "; String query = " select facility.county,facility.subcounty,cur.facilityname,cur.startdate,cur.enddate,case when ROUND(targets.testing_target_child/" + period + ") >0 then ROUND(targets.testing_target_child/" + period + ") else 1 end as testing_target_child,case when ROUND(targets.testing_target_adult/" + period + ") >0 then ROUND(targets.testing_target_adult/" + period + ") else 1 end as testing_target_adult,case when ROUND(targets.testing_target_total/" + period + ") >0 then ROUND(targets.testing_target_total/" + period + ") else 1 end as testing_target_total,cur.test_child,cur.test_adult,cur.test_total,cur.tested_perc_child,cur.tested_perc_adult,cur.tested_perc_all,cur.tested_cmts,case when ROUND(targets.hiv_pos_target_child/" + period + ") >0 then ROUND(targets.hiv_pos_target_child/" + period + ") else 1 end as hiv_pos_target_child,case when ROUND(targets.hiv_pos_target_adult/" + period + ") >0 then ROUND(targets.hiv_pos_target_adult/" + period + ") else 1 end as hiv_pos_target_adult,case when ROUND(targets.hiv_pos_target_total/" + period + ") >0 then ROUND(targets.hiv_pos_target_total/" + period + ") else 1 end as hiv_pos_target_total,cur.hiv_pos_child,cur.hiv_pos_adult,cur.hiv_pos_total,cur.hiv_pos_yield_perc_child,cur.hiv_pos_yield_perc_adult,cur.hiv_pos_yield_perc_all,cur.hiv_pos_yield_cmts,cur.new_care_child,cur.new_care_adult,cur.new_care_total,cur.hiv_pos_care_perc_child,cur.hiv_pos_care_perc_adult,cur.hiv_pos_care_perc_all,cur.hiv_pos_care_cmts,case when ROUND(targets.new_art_target_child/" + period + ") >0 then ROUND(targets.new_art_target_child/" + period + ") else 1 end as new_art_target_child,case when ROUND(targets.new_art_target_adult/" + period + ") >0 then ROUND(targets.new_art_target_adult/" + period + ") else 1 end as new_art_target_adult,case when ROUND(targets.new_art_target_total/" + period + ") >0 then ROUND(targets.new_art_target_total/" + period + ") else 1 end as new_art_target_total,cur.started_art_child,cur.started_art_adult,cur.started_art_total,cur.started_art_perc_child,cur.started_art_perc_adult,cur.started_art_perc_all,cur.started_art_cmts,case when ROUND(targets.viral_load_target_child/" + period + ") >0 then ROUND(targets.viral_load_target_child/" + period + ") else 1 end as viral_load_target_child,case when ROUND(targets.viral_load_target_adult/" + period + ") >0 then ROUND(targets.viral_load_target_adult/" + period + ") else 1 end as viral_load_target_adult,case when ROUND(targets.viral_load_target_total/" + period + ") >0 then ROUND(targets.viral_load_target_total/" + period + ") else 1 end as viral_load_target_total,cur.viral_load_done_child,cur.viral_load_done_adult,cur.viral_load_done_total,cur.viral_test_perc_child,cur.viral_test_perc_adult,cur.viral_test_perc_all,cur.viral_test_cmts,case when ROUND(targets.ipt_target_child/" + period + ") >0 then ROUND(targets.ipt_target_child/" + period + ") else 1 end as ipt_target_child,case when ROUND(targets.ipt_target_adult/" + period + ") >0 then ROUND(targets.ipt_target_adult/" + period + ") else 1 end as ipt_target_adult,case when ROUND(targets.ipt_target_total/" + period + ") >0 then ROUND(targets.ipt_target_total/" + period + ") else 1 end as ipt_target_total,cur.ipt_child,cur.ipt_adult,cur.ipt_total,cur.ipt_done_perc_child,cur.ipt_done_perc_adult,cur.ipt_done_perc_all,cur.ipt_done_cmts,case when ROUND(targets.pmtct_hiv_pos_target/" + period + ") >0 then ROUND(targets.pmtct_hiv_pos_target/" + period + ") else 1 end as pmtct_hiv_pos_target,cur.pmtct_hiv_pos,cur.pmtct_hiv_pos_perc,cur.pmtct_hiv_pos_cmts,case when ROUND(targets.eid_target/" + period + ") >0 then ROUND(targets.eid_target/" + period + ") else 1 end as eid_target,cur.eid_done,cur.eid_done_perc,cur.eid_done_cmts,case when ROUND(targets.viral_load_mothers_target/" + period + ") >0 then ROUND(targets.viral_load_mothers_target/" + period + ") else 1 end as viral_load_mothers_target,cur.viral_load_mothers_done,cur.viral_load_mothers_perc,cur.viral_load_mothers_cmts from weekly_data_new cur join (facility join targets on (facility.mflcode=targets.facility and targets.year='" + mwaka + "' ) ) on cur.facilityname=facility.facility_name where " + where + " " + " group by cur.id "; System.out.println("" + query); try { conn.rs = conn.st.executeQuery(query); String facilname = ""; String facilnamecopy = ""; String id = ""; //percentages String viral_load_mothers_perc = null; String eid_done_perc = null; String pmtct_hiv_pos_perc = null; //new percenatages String hiv_pos_yield_perc_child = null; String hiv_pos_yield_perc_adult = null; String hiv_pos_yield_perc_all = null; String hiv_pos_care_perc_child = null; String hiv_pos_care_perc_adult = null; String hiv_pos_care_perc_all = null; String started_art_perc_child = null; String started_art_perc_adult = null; String started_art_perc_all = null; String viral_test_perc_child = null; String viral_test_perc_adult = null; String viral_test_perc_all = null; String ipt_done_perc_child = null; String ipt_done_perc_adult = null; String ipt_done_perc_all = null; String tested_perc_child = null; String tested_perc_adult = null; String tested_perc_all = null; //comments String viral_load_mothers_cmts = null; String eid_done_cmts = null; String pmtct_hiv_pos_cmts = null; String hiv_pos_yield_cmts = null; String hiv_pos_care_cmts = null; String started_art_cmts = null; String viral_test_cmts = null; String ipt_done_cmts = null; String tested_cmts = null; Row rwx = null; int rowno = 0; while (conn.rs.next()) { rowno++; rwx = rawdata.createRow(rowno); rwx.setHeightInPoints(23); //do a per facility //System.out.println("___FACILITY : "+conn.rs.getString("facilityname")+" => hiv_pos_total "+conn.rs.getString("hiv_pos_total")); for (int a = 1; a <= header.length; a++) { System.out.print(header[a - 1] + " _ " + conn.rs.getString(a)); Cell ce = rwx.createCell(a - 1); if (a > 5 && a <= 73) { if (header[a - 1].contains("cmts")) { ce.setCellValue(conn.rs.getString(a)); } else if (header[a - 1].contains("_perc")) { ce.setCellValue(conn.rs.getInt(a)); System.out.println("__Percent"); } else { ce.setCellValue(conn.rs.getInt(a)); } } else { ce.setCellValue(conn.rs.getString(a)); } } //reporting rate int reportingrate = 0; String getrates = "select ROUND(((count(facilityname))/(" + weeks + ")*100)) as count from weekly_data_new where facilityname like '" + conn.rs.getString("facilityname") + "' and " + where1 + " "; conn.rs1 = conn.st1.executeQuery(getrates); if (conn.rs1.next()) { reportingrate = conn.rs1.getInt(1); } Cell ce = rwx.createCell(header.length); ce.setCellValue(reportingrate); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.conne != null) { conn.conne.close(); } Date dat = new Date(); String dat1 = dat.toString().replace(" ", "_"); // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=MOIS_Weekly_Report_From" + startdate.replace(" ", "-") + "_To_" + enddate.replace(" ", "_") + ".xlsm"); response.setHeader("Set-Cookie", "fileDownload=true; path=/"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); wb.dispose(); // response.sendRedirect("index.jsp"); File file = new File(pathtodelete); if (file.delete()) { System.out.println(file.getName() + " is deleted!"); } else { System.out.println("Delete operation is failed."); } } catch (SQLException ex) { Logger.getLogger(excelreport.class.getName()).log(Level.SEVERE, null, ex); } } catch (InvalidFormatException ex) { Logger.getLogger(excelreport.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(excelreport.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.actelion.research.spiritapp.report.FoodWaterReport.java
License:Open Source License
protected void createWorkBookNew(Workbook wb) throws Exception { if (study.getPhases().size() == 0) throw new Exception("You cannot generate a FoodWater report if you don't have phases in your study"); Row row; DAOResult.attachOrCreateStudyResultsToTops(study, study.getParticipantsSorted(), null, null); List<FoodWater> fws = DAOFoodWater.getFoodWater(study, null); List<Phase> phases = FoodWater.getPhases(fws); Map<Biosample, List<Biosample>> mapHistory = DAORevision.getHistories(study.getParticipantsSorted()); boolean hasCageChanges = false; for (Biosample top : study.getParticipantsSorted()) { Set<String> containerIds = new HashSet<>(); for (Phase phase : phases) { String containerId = getContainerAt(top, phase, fws, mapHistory.get(top)); if (containerId != null && containerId.length() > 0) containerIds.add(containerId); }// www. ja va2 s . c om if (containerIds.size() > 1) { hasCageChanges = true; break; } } boolean displayCageEachPhase = study.getFirstDate() != null && study.getPhaseFormat() == PhaseFormat.DAY_MINUTES && hasCageChanges; //Loop through Food (i=0) and water (i==1) reports for (int i = 0; i < 2; i++) { //Check if we have some data boolean hasData = false; for (FoodWater fw : fws) { if (i == 0 && (fw.getFoodTare() != null || fw.getFoodWeight() != null)) hasData = true; else if (i == 1 && (fw.getWaterTare() != null || fw.getWaterWeight() != null)) hasData = true; } if (!hasData) continue; Sheet sheet = createSheet(wb, i == 0 ? "Food" : "Water"); createHeadersWithTitle(sheet, study, i == 0 ? "Food Consumption [g/animal/day]" : "Water Consumption [ml/animal/day]"); //Create Table Header row = sheet.createRow(5); row.setHeightInPoints(21f); row = sheet.createRow(6); row.setHeightInPoints(21f); int x = 0; set(sheet, 6, x++, "Group", Style.S_TH_CENTER); set(sheet, 6, x++, "Id", Style.S_TH_CENTER); set(sheet, 6, x++, "No", Style.S_TH_CENTER); if (!displayCageEachPhase) { set(sheet, 6, x++, "Container", Style.S_TH_CENTER); } Phase previousPhase = null; for (Phase phase : phases) { if (previousPhase == null) { set(sheet, 5, x, phase.getShortName() + (phase.getAbsoluteDate() != null ? "[" + FormatterUtils.formatDate(phase.getAbsoluteDate()) + "]" : ""), Style.S_TH_CENTER, 1, 1); if (displayCageEachPhase) set(sheet, 6, x++, "Container", Style.S_TH_CENTER); if (!displayCageEachPhase) set(sheet, 6, x++, "newTare", Style.S_TH_CENTER); } else { set(sheet, 5, x, " -> " + phase.getShortName() + (phase.getAbsoluteDate() != null ? " [" + FormatterUtils.formatDate(phase.getAbsoluteDate()) + "]" : ""), Style.S_TH_CENTER, 1, 2 + (displayCageEachPhase ? 4 : 2)); if (displayCageEachPhase) set(sheet, 6, x++, "ContainerId", Style.S_TH_CENTER); if (displayCageEachPhase) set(sheet, 6, x++, "Tare", Style.S_TH_CENTER); if (displayCageEachPhase) set(sheet, 6, x++, "Weight", Style.S_TH_CENTER); if (displayCageEachPhase) set(sheet, 6, x++, "days", Style.S_TH_CENTER); if (!displayCageEachPhase) set(sheet, 6, x++, "oldTare", Style.S_TH_CENTER); if (!displayCageEachPhase) set(sheet, 6, x++, "newTare", Style.S_TH_CENTER); set(sheet, 6, x++, "n", Style.S_TH_CENTER); set(sheet, 6, x++, "Cons.", Style.S_TH_CENTER); } previousPhase = phase; } int maxX = x - 1; // //Create table data int y = 7; Group previousGroup = null; ListHashMap<Group, Integer> group2Lines = new ListHashMap<>(); for (Biosample b : study.getParticipantsSorted()) { Group gr = b.getInheritedGroup(); group2Lines.add(gr, y); x = 0; set(sheet, y, x++, b.getInheritedGroupString(SpiritFrame.getUsername()), Style.S_TD_BOLD_LEFT); set(sheet, y, x++, b.getSampleId(), Style.S_TD_CENTER); set(sheet, y, x++, b.getSampleName(), Style.S_TD_CENTER); String containerId = null; if (!displayCageEachPhase) { //Display cage at time of death containerId = b.getContainerId(); if (containerId == null || containerId.length() == 0) { List<Biosample> history = mapHistory.get(b); for (Biosample h : history) { if (h.getContainerId() != null && h.getContainerId().length() > 0) { containerId = h.getContainerId(); break; } } } set(sheet, y, x++, containerId, Style.S_TD_BOLD_CENTER); } //data: Loop through phases previousPhase = null; for (Phase phase : phases) { if (displayCageEachPhase) { //Find the containerId of this sample at this date: containerId = getContainerAt(b, phase, fws, mapHistory.get(b)); } if (containerId == null || containerId.length() == 0) containerId = "??"; FoodWater fw = FoodWater.extract(fws, containerId, phase); FoodWater previousFW = fw == null ? null : fw.getPreviousFromList(fws, i == 1); Result r = b.getAuxResult(DAOTest.getTest(DAOTest.FOODWATER_TESTNAME), phase); ResultValue val = r == null || r.getOutputResultValues().size() < 2 ? null : i == 0 ? r.getOutputResultValues().get(0) : r.getOutputResultValues().get(1); String value = val == null ? null : val.getValue(); if (previousPhase == null) { if (displayCageEachPhase) set(sheet, y, x++, containerId, Style.S_TD_BOLD_CENTER); if (!displayCageEachPhase) set(sheet, y, x++, fw == null ? null : i == 0 ? fw.getFoodTare() : fw.getWaterTare(), Style.S_TD_DOUBLE1); } else { if (displayCageEachPhase) set(sheet, y, x++, containerId, Style.S_TD_BOLD_CENTER); if (displayCageEachPhase) set(sheet, y, x++, previousFW == null ? null : i == 0 ? previousFW.getFoodTare() : previousFW.getWaterTare(), Style.S_TD_DOUBLE1); set(sheet, y, x++, fw == null ? null : i == 0 ? fw.getFoodWeight() : fw.getWaterWeight(), Style.S_TD_DOUBLE1); if (displayCageEachPhase) set(sheet, y, x++, previousFW == null ? null : fw.getPhase().getDays() - previousFW.getPhase().getDays(), Style.S_TD_DOUBLE0); if (!displayCageEachPhase) set(sheet, y, x++, fw == null ? null : i == 0 ? fw.getFoodTare() : fw.getWaterTare(), Style.S_TD_DOUBLE1); set(sheet, y, x++, fw == null ? null : fw.getNAnimals(), Style.S_TD_DOUBLE0); set(sheet, y, x++, value, Style.S_TD_DOUBLE1_BLUE); } previousPhase = phase; } if (previousGroup != null && !previousGroup.equals(gr)) { drawLineAbove(sheet, y, 0, maxX, (short) 1); } previousGroup = gr; y++; } // y++; ////////////////////// AVERAGES x = 0; set(sheet, y + 1, x++, "Averages", Style.S_TH_CENTER); set(sheet, y + 1, x++, "", Style.S_TH_CENTER); set(sheet, y + 1, x++, "", Style.S_TH_CENTER); if (!displayCageEachPhase) set(sheet, y + 1, x++, "", Style.S_TH_CENTER); previousPhase = null; for (Phase phase : phases) { if (previousPhase == null) { set(sheet, y, x, phase.getShortName() + (phase.getAbsoluteDate() != null ? " [" + FormatterUtils.formatDate(phase.getAbsoluteDate()) + "]" : ""), Style.S_TH_CENTER); set(sheet, y + 1, x++, "", Style.S_TH_CENTER); } else { set(sheet, y, x, " -> " + phase.getShortName() + (phase.getAbsoluteDate() != null ? " [" + FormatterUtils.formatDate(phase.getAbsoluteDate()) + "]" : ""), Style.S_TH_CENTER, 1, 2 + (displayCageEachPhase ? 4 : 2)); for (int k = 0; k < (displayCageEachPhase ? 4 : 2); k++) { set(sheet, y + 1, x++, "", Style.S_TH_CENTER); } set(sheet, y + 1, x++, "", Style.S_TH_CENTER); set(sheet, y + 1, x++, "Cons.", Style.S_TH_CENTER); } previousPhase = phase; } y += 2; for (Group gr : study.getGroups()) { if (study.getParticipants(gr).size() == 0) continue; x = 0; set(sheet, y, x++, gr == null ? "N/A" : gr.getBlindedName(SpiritFrame.getUsername()), Style.S_TD_BOLD_LEFT); set(sheet, y, x++, "", Style.S_TD_LEFT); set(sheet, y, x++, "", Style.S_TD_LEFT); if (!displayCageEachPhase) set(sheet, y, x++, "", Style.S_TD_BOLD_LEFT); //data: Loop through phases previousPhase = null; for (Phase phase : phases) { List<Integer> lines = group2Lines.get(gr); if (previousPhase == null) { set(sheet, y, x, "", Style.S_TD_DOUBLE1); x += 1; } else { for (int k = 0; k < (displayCageEachPhase ? 4 : 2); k++) { set(sheet, y, x++, "", Style.S_TD_CENTER); } if (lines != null && lines.size() > 0) { int valcol = x + 1; set(sheet, y, x++, "", Style.S_TD_CENTER); setFormula(sheet, y, x++, "IF(COUNT(" + convertLinesToCells(lines, valcol) + ")>0, AVERAGE(" + convertLinesToCells(lines, valcol) + "), \"\")", Style.S_TD_DOUBLE1_BLUE); } else { set(sheet, y, x++, "", Style.S_TD_CENTER); set(sheet, y, x++, "", Style.S_TD_CENTER); } } previousPhase = phase; } y++; } POIUtils.autoSizeColumns(sheet); } }
From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java
License:Open Source License
@SuppressWarnings("rawtypes") public static void exportToExcel(String[][] table, ExportMode exportMode) throws IOException { Class[] types = getTypes(table); Workbook wb = new XSSFWorkbook(); Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style;//from w w w. j ava 2s .co m DataFormat df = wb.createDataFormat(); Font font = wb.createFont(); font.setFontName("Serif"); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 15); style = wb.createCellStyle(); style.setFont(font); styles.put("title", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 10); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("th", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-border", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-double", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-right", style); font = wb.createFont(); font.setFontName("Serif"); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-bold", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setDataFormat(df.getFormat("d.mm.yyyy h:MM")); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-date", style); Sheet sheet = wb.createSheet(); sheet.setFitToPage(true); Cell cell; int maxRows = 0; for (int r = 0; r < table.length; r++) { Row row = sheet.createRow(r); if (r == 0) { row.setRowStyle(styles.get("th")); } int rows = 1; for (int c = 0; c < table[r].length; c++) { cell = row.createCell(c); String s = table[r][c]; if (s == null) continue; rows = Math.max(rows, s.split("\n").length); try { if (exportMode == ExportMode.HEADERS_TOP && r == 0) { cell.setCellStyle(styles.get("th")); cell.setCellValue(s); } else if (exportMode == ExportMode.HEADERS_TOPLEFT && (r == 0 || c == 0)) { if (r == 0 && c == 0) { cell.setCellStyle(styles.get("td")); } else { cell.setCellStyle(styles.get("th")); } cell.setCellValue(s); } else if (types[c] == Double.class) { cell.setCellStyle(styles.get("td-double")); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(s)); } else if (types[c] == String.class) { cell.setCellStyle( styles.get(exportMode == ExportMode.HEADERS_TOPLEFT ? "td-border" : "td")); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(s); } else { cell.setCellStyle(styles.get("td-right")); cell.setCellValue(s); } } catch (Exception e) { cell.setCellStyle(styles.get("td")); cell.setCellValue(s); } } maxRows = Math.max(maxRows, rows); row.setHeightInPoints(rows * 16f); } // Add footer notes if (footerData.size() > 0) { Row row = sheet.createRow(table.length); row.setHeightInPoints((footerData.size() * sheet.getDefaultRowHeightInPoints())); cell = row.createCell(0); sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), //first row (0-based) row.getRowNum(), //last row (0-based) 0, //first column (0-based) table[0].length - 1 //last column (0-based) )); //for ( String data : footerData ) { style = wb.createCellStyle(); style.setWrapText(true); cell.setCellStyle(style); cell.setCellValue(MiscUtils.flatten(footerData, "\n")); //} } footerData.clear(); autoSizeColumns(sheet); if (table.length > 0) { for (int c = 0; c < table[0].length; c++) { if (sheet.getColumnWidth(c) > 10000) sheet.setColumnWidth(c, 3000); } } if (exportMode == ExportMode.HEADERS_TOPLEFT) { for (int r = 1; r < table.length; r++) { sheet.getRow(r).setHeightInPoints(maxRows * 16f); } } File reportFile = IOUtils.createTempFile("export_", ".xlsx"); FileOutputStream out = new FileOutputStream(reportFile); wb.write(out); wb.close(); out.close(); Desktop.getDesktop().open(reportFile); }
From source file:com.b510.excel.client.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from w w w .ja v a 2 s . com if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //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(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); calendar.setTime(fmt.parse("9-Jul")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
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 {//w w w. ja v a 2s . co 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(); }