diff options
Diffstat (limited to 'ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportHandler.java')
| -rw-r--r-- | ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportHandler.java | 2924 |
1 files changed, 2222 insertions, 702 deletions
diff --git a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportHandler.java b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportHandler.java index b8f3284c..e89bac58 100644 --- a/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportHandler.java +++ b/ecomp-sdk/epsdk-analytics/src/main/java/org/onap/portalsdk/analytics/model/ReportHandler.java @@ -114,6 +114,7 @@ import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import org.apache.commons.io.FilenameUtils; +import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDateUtil; @@ -126,6 +127,7 @@ import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.BorderStyle; +import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.FillPatternType; @@ -135,6 +137,10 @@ import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; +import org.apache.poi.xssf.streaming.SXSSFCell; +import org.apache.poi.xssf.streaming.SXSSFRow; +import org.apache.poi.xssf.streaming.SXSSFSheet; +import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFDataFormat; @@ -578,7 +584,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext();) { XSSFRow row = sheet.createRow(rowNum); cellNum = -1; - chr = rd.reportColumnHeaderRows.getNext(); if (nvl(sql_whole).length() <= 0 || (!rr.getReportType().equals(AppConstants.RT_LINEAR))) { @@ -603,11 +608,8 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { cellWidth.add(cellNum, new Integer(title.length())); row.getCell((short) cellNum).setCellStyle(styleDataHeader); } - - } // for - } - + } firstPass = false; @@ -769,7 +771,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { firstPass = true; XSSFRow row = null; XSSFCell cell = null; - SimpleDateFormat MMDDYYYYFormat = new SimpleDateFormat("MM/dd/yyyy"); SimpleDateFormat YYYYMMDDFormat = new SimpleDateFormat("yyyy/MM/dd"); SimpleDateFormat MONYYYYFormat = new SimpleDateFormat("MMM yyyy"); @@ -821,16 +822,20 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { j = 0; firstPass = false; + DataValue dv = new DataValue(); for (dr.resetNext(); dr.hasNext(); j++) { styleCell = null; - DataValue dv = dr.getNext(); + dv = dr.getNext(); HtmlFormatter htmlFormat = dv.getCellFormatter(); if ((dr.isRowFormat() && !dv.isCellFormat()) && styles != null) styleCell = (XSSFCellStyle) styles.get(nvl(dr.getFormatId(), DEFAULT)); if (htmlFormat != null && dv.getFormatId() != null && styles != null) styleCell = (XSSFCellStyle) styles.get(nvl(dv.getFormatId(), DEFAULT)); String value = nvl((String) colHash.get(dv.getColId().toUpperCase())); - + if(value.contains("linkTo")) { + String reverseLinkToValue = Utils.removeLinkToForDownload(value); + value = StringUtils.substringBefore(reverseLinkToValue, ","); + } boolean bold = false; if (dv.isVisible()) { @@ -1222,7 +1227,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { if ((tempInt.indexOf(",")) != -1) { tempInt = tempInt.replaceAll(",", ""); } - Long tempIntDollar = 0L; try { @@ -1325,7 +1329,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { int cw = 0; for (int i = 0; i < cellWidth.size(); i++) { cw = ((Integer) cellWidth.get(i)).intValue() + 12; - sheet.setColumnWidth((short) (i), (short) ((cw * 8) / ((double) 1 / 20))); } @@ -1344,10 +1347,11 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { drTotal.resetNext(); drTotal.getNext(); + DataValue dv = new DataValue(); for (; drTotal.hasNext();) { cellNum += 1; cell = row.createCell((short) cellNum); - DataValue dv = drTotal.getNext(); + dv = drTotal.getNext(); String value = dv.getDisplayValue(); cell.setCellValue(value); boolean bold = false; @@ -1379,13 +1383,12 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { if (!(ex.getCause() instanceof java.net.SocketException)) throw new RaptorException(ex); } - - } else { if (rr.getReportType().equals(AppConstants.RT_LINEAR)) { int rowCount = 0; + DataRow dr = new DataRow(); for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext();) { - DataRow dr = rd.reportDataRows.getNext(); + dr = rd.reportDataRows.getNext(); rowCount++; row = sheet.createRow(rowNum); @@ -1414,13 +1417,12 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { cellWidth.set(cellNum, new Integer(rh.getRowTitle().length())); } else cellWidth.add(cellNum, new Integer(rh.getRowTitle().length())); - - } // for + } firstPass = false; int j = 0; - + DataValue dv = new DataValue(); for (dr.resetNext(); dr.hasNext(); j++) { - DataValue dv = dr.getNext(); + dv = dr.getNext(); styleCell = null; boolean bold = false; String value = nvl(dv.getDisplayValue()); @@ -1435,7 +1437,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { cellNum += 1; cell = row.createCell((short) cellNum); String dataType = (String) (dataTypeMap.get(dv.getColId())); - if (dataType != null && dataType.equals("NUMBER")) { int zInt = 0; if (value.equals("null")) { @@ -1539,7 +1540,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } } - } } } @@ -1743,13 +1743,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { if (date != null) { cell.setCellValue(HSSFDateUtil.getExcelDate(date)); - try { - String str = cell.getStringCellValue(); - } catch (IllegalStateException ex) { - logger.error(EELFLoggerDelegate.errorLogger, "IllegalStateException occured", - ex); cell.setCellValue(value); - } } else { cell.setCellValue(value); } @@ -1866,7 +1860,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { cell.setCellValue(tempStr); } } - } else { if (styleCell != null) { styleCell.setWrapText(true); @@ -1898,7 +1891,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { (value.length() <= Globals.getMaxCellWidthInExcel()) ? new Integer(value.length()) : new Integer(Globals.getMaxCellWidthInExcel())); - if (dv.isBold()) { if ((dv.getDisplayTotal() != null && dv.getDisplayTotal().equals("SUM(")) || (dv.getColName() != null && dv.getColName().indexOf("999") != -1)) { @@ -1920,9 +1912,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { && styles != null) { } } - } // for - - + } rowNum += 1; int cw = 0; for (int i = 0; i < cellWidth.size(); i++) { @@ -1931,8 +1921,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } - } // for - + } // To Display Total Values for Linear report if (rd.reportDataTotalRow != null) { row = sheet.createRow(rowNum); @@ -1949,10 +1938,11 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { drTotal.resetNext(); drTotal.getNext(); + DataValue dv = new DataValue(); for (; drTotal.hasNext();) { cellNum += 1; cell = row.createCell((short) cellNum); - DataValue dv = drTotal.getNext(); + dv = drTotal.getNext(); String value = dv.getDisplayValue(); cell.setCellValue(value); boolean bold = false; @@ -1972,15 +1962,12 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } } } - - - } else if (rr.getReportType().equals(AppConstants.RT_CROSSTAB)) { // Linear int rowCount = 0; List l = rd.getReportDataList(); boolean first = true; for (int dataRow = 0; dataRow < l.size(); dataRow++) { - + DataValue dv = new DataValue(); DataRow dr = (DataRow) l.get(dataRow); row = sheet.createRow(rowNum); @@ -1991,7 +1978,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { if (first) { if (rowNames != null) { for (int i = 0; i < rowNames.size(); i++) { - DataValue dv = rowNames.get(i); + dv = rowNames.get(i); cellNum += 1; row.createCell((short) cellNum).setCellValue(strip.stripHtml(dv.getDisplayValue())); row.getCell((short) cellNum).setCellStyle(styleDefault); @@ -1999,8 +1986,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } } first = false; - - DataValue dv = dr.getNext(); + dv = dr.getNext(); if (dv.isVisible()) { String value = dv.getDisplayValue(); if (value.indexOf("|#") != -1) @@ -2040,15 +2026,11 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { for (int i = 0; i < cellWidth.size(); i++) { cw = ((Integer) cellWidth.get(i)).intValue() + 12; sheet.setColumnWidth((short) (i), (short) ((cw * 8) / ((double) 1 / 20))); - } - - } // for - } } - + } String footer = (String) session.getAttribute("FOOTER_" + index); if (nvl(footer).length() > 0) { footer = Utils.replaceInString(footer, "<BR/>", " "); @@ -2094,7 +2076,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { HSSFRow row1 = null; row = sheet.createRow(rowNum); - // Header Style XSSFCellStyle styleHeader = wb.createCellStyle(); styleHeader.setAlignment(HorizontalAlignment.CENTER); XSSFFont font = wb.createFont(); @@ -2163,9 +2144,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { public String saveAsExcelFile(HttpServletRequest request, ReportData rd, ArrayList reportParamNameValues, String reportTitle, String reportDescr) { return saveAsExcelFile(request, rd, reportParamNameValues, reportTitle, reportDescr, 2); // 2 denotes - // ReportRuntime object - // should be taken from - // session. } public String saveAsExcelFile(HttpServletRequest request, ReportData rd, @@ -2191,9 +2169,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { + xlsFName)); try (FileOutputStream xlsOut = new FileOutputStream(FilenameUtils.normalize(AppUtils.getTempFolderPath() + xlsFName))) { - int col = 0; - if (!rd.reportRowHeaderCols.hasNext()) col = rd.getTotalColumnCount(); else @@ -2228,278 +2204,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { + e.getMessage()); return null; } - } // saveAsExcelFile - - public void createExcelFileContent(final Writer out, ReportData rd, final ReportRuntime rr, - final HttpServletRequest request, final HttpServletResponse response, final String user_id, final int type) - throws IOException, RaptorException { - // Adding utility for downloading Dashboard reports. - - Map styles = new HashMap(); - final HttpSession session = request.getSession(); - - XSSFWorkbook wb = null; - - int returnValue = 0; - final boolean isDashboard = (session.getAttribute(AppConstants.SI_DASHBOARD_REP_ID) != null) && - (((String) session.getAttribute(AppConstants.SI_DASHBOARD_REP_ID)).equals(rr.getReportID())); - - if (isDashboard) { - final String formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date()); - final String xlsFName = "./Dashboard.xlsx"; - try { - FileInputStream xlsIn = null; - - final Map reportRuntimeMap = (TreeMap) request.getSession() - .getAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME_MAP); - final Map reportDataMap = (TreeMap) request.getSession() - .getAttribute(AppConstants.SI_DASHBOARD_REPORTDATA_MAP); - if (reportRuntimeMap != null) { - final Set setReportRuntime = reportRuntimeMap.entrySet(); - final Set setReportDataMap = reportDataMap.entrySet(); - final Iterator iter2 = setReportDataMap.iterator(); - int count = 0; - - for (Iterator iter = setReportRuntime.iterator(); iter.hasNext();) { - count++; - try { - xlsIn = - new FileInputStream(xlsFName); - } catch (final FileNotFoundException e) { - logger.error(EELFLoggerDelegate.errorLogger, "File not found in the specified path.", e); - } - if (xlsIn != null) { - wb = new XSSFWorkbook(xlsIn); - } else { - wb = new XSSFWorkbook(); - } - - final Map.Entry entryData = (Entry) iter2.next(); - final Map.Entry entry = (Entry) iter.next(); - final ReportRuntime rrDashRep = (ReportRuntime) entry.getValue(); - final ReportData rdDashRep = (ReportData) entryData.getValue(); - int col = 0; - final String reportTitle = (nvl(rrDashRep.getReportTitle()).length() > 0 ? rrDashRep - .getReportTitle() - : rrDashRep.getReportName()); - final String reportDescr = rrDashRep.getReportDescr(); - if (rdDashRep != null) { - if (!rdDashRep.reportRowHeaderCols.hasNext()) { - col = rdDashRep.getTotalColumnCount(); - } else { - col = rdDashRep.getTotalColumnCount(); - } - }else { - continue; - } - if (col == 0) { - col = 10; - } - int rowNum = 0; - final String formattedReportName = - new HtmlStripper().stripSpecialCharacters(rrDashRep.getReportName()); - - XSSFSheet sheet = null; - try { - sheet = wb.createSheet(formattedReportName); - sheet.getPrintSetup().setLandscape(true); - styles = loadStyles(rrDashRep, wb); - } catch (final IllegalArgumentException ex) { - logger.warn("IllegalArgumentException occured", ex); - try (final FileOutputStream xlsOut = new FileOutputStream(xlsFName)) { - wb.write(xlsOut); - xlsOut.flush(); - } catch (final IOException e) { - logger.warn("Failed to open FileOutputStream", e); - } - continue; - } - - if (Globals.getPrintTitleInDownload() && reportTitle != null) { - paintExcelHeader(wb, rowNum, col, reportTitle, reportDescr, sheet); - rowNum = sheet.getLastRowNum(); - } else { - rowNum = 0; - } - // getting ReportRuntime object from session - if (Globals.getPrintParamsInDownload() - && rrDashRep.getParamNameValuePairsforPDFExcel(request, 1) != null) { - if (count > 1 && Globals.showParamsInAllDashboardReports()) { - paintExcelParams(wb, rowNum, col, - rrDashRep.getParamNameValuePairsforPDFExcel(request, 1), - rrDashRep.getFormFieldComments(request), sheet, reportTitle, reportDescr); - } else if (count == 1) { - paintExcelParams(wb, rowNum, col, - rrDashRep.getParamNameValuePairsforPDFExcel(request, 1), - rrDashRep.getFormFieldComments(request), sheet, reportTitle, reportDescr); - } - } // if - rowNum = sheet.getLastRowNum(); - final String sql_whole = rrDashRep.getWholeSQL(); - returnValue = paintExcelData(wb, rowNum, rdDashRep, styles, rrDashRep, sheet, sql_whole, - request); - if (returnValue == 0) { - if (Globals.getPrintFooterInDownload()) { - rowNum = sheet.getLastRowNum(); - rowNum += 2; - paintExcelFooter(wb, rowNum, col, sheet); - } - try (final FileOutputStream xlsOut = new FileOutputStream(xlsFName)) { - wb.write(xlsOut); - // TODO Remove comment - xlsOut.flush(); - } catch (final IOException e) { - logger.warn("Failed to open FileOutputStream", e); - } - wb = null; - } - } - - response.reset(); - response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); - response.setHeader("Content-disposition", - "attachment;filename=" + "dashboard" + formattedDate + user_id + ".xlsx"); - - xlsIn = new FileInputStream(xlsFName); - - int readBytes = 0; - final byte[] bOut = new byte[4096]; - try (final BufferedInputStream buf = new BufferedInputStream(xlsIn); - final ServletOutputStream sos = response.getOutputStream();) { - // read from the file; write to the ServletOutputStream - while ((readBytes = buf.read(bOut, 0, 4096)) > 0) { - buf.available(); - sos.write(bOut, 0, readBytes); - } - } catch (final IOException e) { - logger.warn("Failed to open BufferedInputStream", e); - } - } - } catch (final IOException ex) { - logger.warn("Failed to open Stream", ex); - throw ex; - } - - final File f = new File(xlsFName); - if (f.exists()) { - f.delete(); - } - - } else { - wb = new XSSFWorkbook (); - setSheetName(Globals.getSheetName()); - if (rr != null) { - styles = loadStyles(rr, wb); - } - - final String reportTitle = (nvl(rr.getReportTitle()).length() > 0 ? rr.getReportTitle() - : rr.getReportName()); - final String reportDescr = rr.getReportDescr(); - - final int col = getColumnCountForDownloadFile(rr, rd); - int rowNum = 0; - final XSSFSheet sheet = wb.createSheet(getSheetName()); - sheet.getPrintSetup().setLandscape(true); - - if (Globals.getPrintTitleInDownload() && reportTitle != null) { - paintExcelHeader(wb, rowNum, col, reportTitle, reportDescr, sheet); - rowNum = sheet.getLastRowNum(); - } else { - rowNum = 0; - } - if (Globals.getPrintParamsInDownload() && rr.getParamNameValuePairsforPDFExcel(request, 1) != null) { - List paramsList = rr.getParamNameValuePairsforPDFExcel(request, 1); - if (paramsList.size() <= 0) { - paramsList = (ArrayList) request.getSession().getAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO); - } - - paintExcelParams(wb, rowNum, col, paramsList, rr.getFormFieldComments(request), sheet, reportTitle, - reportDescr); - } // if - - rowNum = sheet.getLastRowNum(); - if(rowNum<0) - rowNum = 0; - - final String formattedReportName = new HtmlStripper().stripSpecialCharacters(rr.getReportName()); - final String formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date()); - response.reset(); - response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); - response.setHeader("Content-disposition", - "attachment;filename=" + formattedReportName + formattedDate + user_id + ".xlsx"); - if (type == 3 && rr.getSemaphoreList() == null && !(rr.getReportType() - .equals(AppConstants.RT_CROSSTAB))) { // type = 3 is whole - String sql_whole = ""; - sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE); - - if (sql_whole == null) { - if (!rr.getReportType().equals(AppConstants.RT_HIVE)) { - sql_whole = rr.getWholeSQL(); - } else { - sql_whole = rr.getReportSQL(); - } - } - if(rr.isSinglePageDownload()) - sql_whole = rr.getCachedSQL(); - returnValue = paintExcelData(wb, rowNum, rd, styles, rr, sheet, sql_whole, request); - rr.setSinglePageDownload(false); - } else if (type == 2) { - returnValue = paintExcelData(wb, rowNum, rd, styles, rr, sheet, "", request); - } else { - int downloadLimit = - (rr.getMaxRowsInExcelDownload() > 0) ? rr.getMaxRowsInExcelDownload() - : Globals.getDownloadLimit(); - String action = request.getParameter(AppConstants.RI_ACTION); - if (!(rr.getReportType().equals(AppConstants.RT_CROSSTAB)) && !action.endsWith("session")) { - rd = rr.loadReportData(-1, AppUtils.getUserID(request), downloadLimit, request, - false /* download */); - } - if (rr.getSemaphoreList() != null) { - if (rr.getReportType().equals(AppConstants.RT_CROSSTAB)) { - returnValue = paintExcelData(wb, rowNum, rd, styles, rr, sheet, "", request); - } else { - rd = rr.loadReportData(-1, AppUtils.getUserID(request), downloadLimit, request, true); - returnValue = paintExcelData(wb, rowNum, rd, styles, rr, sheet, "", request); - } - } else { - String sql_whole = ""; - sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE); - - if (sql_whole == null) { - if (!rr.getReportType().equals(AppConstants.RT_HIVE)) { - sql_whole = rr.getWholeSQL(); - } else { - sql_whole = rr.getReportSQL(); - } - } - - returnValue = paintExcelData(wb, rowNum, rd, styles, rr, sheet, sql_whole, request); - } - } - if (returnValue == 0) { - if (Globals.getPrintFooterInDownload()) { - rowNum = sheet.getLastRowNum(); - rowNum += 2; - paintExcelFooter(wb, rowNum, col, sheet); - } - // Alternatively: - wb.setPrintArea( - 0, // sheet index - 0, // start column - col, // end column - 0, // start row - rowNum // end row - ); - // TODO Remove comment - try (final ServletOutputStream sos = response.getOutputStream();) { - wb.write(sos); - sos.flush(); - } catch (final IOException e) { - logger.warn("Failed to get OutputStream", e); - } - wb = null; - } - } } public void createFlatFileContent(Writer out, ReportData rd, ReportRuntime rr, @@ -2511,8 +2215,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { String query = rr.getWholeSQL(); String dbInfo = rr.getDbInfo(); DataSet ds = ConnectionUtils.getDataSet(query, dbInfo); - - // Writing Column names to the file List l = rr.getAllColumns(); StringBuffer allColumnsBuffer = new StringBuffer(); DataColumnType dct = null; @@ -2529,8 +2231,9 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { boolean firstPass = true; + DataRow dr = new DataRow(); for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext();) { - DataRow dr = rd.reportDataRows.getNext(); + dr = rd.reportDataRows.getNext(); for (rd.reportRowHeaderCols.resetNext(1); rd.reportRowHeaderCols.hasNext();) { RowHeaderCol rhc = rd.reportRowHeaderCols.getNext(); if (firstPass) @@ -2540,27 +2243,24 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { sos.print(rh.getRowTitle()); if (rhc.hasNext()) sos.print("|"); - } // for + } firstPass = false; - + DataValue dv = new DataValue(); for (dr.resetNext(); dr.hasNext();) { - DataValue dv = dr.getNext(); + dv = dr.getNext(); sos.print(dv.getDisplayValue()); if (dr.hasNext()) sos.print("|"); - } // for - + } sos.println(); - } // for + } sos.close(); - } // createFlatFileContent + } public void createExcel2007FileContent(Writer out, ReportData rd, ReportRuntime rr, HttpServletRequest request, HttpServletResponse response, String user_id, int type) throws Exception { - - // to check performance int mb = 1024 * 1024; Runtime runtime = Runtime.getRuntime(); @@ -2573,16 +2273,11 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { logger.debug(EELFLoggerDelegate.debugLogger, ("Total Memory:" + runtime.totalMemory() / mb)); logger.debug(EELFLoggerDelegate.debugLogger, ("Max Memory:" + runtime.maxMemory() / mb)); logger.debug(EELFLoggerDelegate.debugLogger, ("##### END #####")); - - // Adding utility for downloading Dashboard reports. - Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>(); HttpSession session = request.getSession(); XSSFWorkbook wb = null; String formattedReportName = new HtmlStripper().stripSpecialCharacters(rr.getReportName()); String formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date()); - // Sheet name to be filled is taken from property. How would this be called if it is Dashboard? - // commented out since application will create and leave it blank. boolean isDashboard = false; if ((session.getAttribute(AppConstants.SI_DASHBOARD_REP_ID) != null) && (((String) session.getAttribute(AppConstants.SI_DASHBOARD_REP_ID)).equals(rr.getReportID()))) { @@ -2616,7 +2311,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { int rowNum = 0; XSSFSheet sheet = null; - // save the template String filename = ""; String extension = ""; @@ -2628,10 +2322,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { if (reportRuntimeMap != null) { FileInputStream readTemplate = null; - // Load customized styles int count = 0; - - // If template supplied by Application String templateFilename = rr.getTemplateFile(); extension = templateFilename.substring(templateFilename.lastIndexOf(".") + 1); filename = formattedReportName + formattedDate + user_id; @@ -2651,7 +2342,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { + rr.getTemplateFile()); wb = new XSSFWorkbook(readTemplate); } else { - // copy the os file to new file and open new file in below line wb = new XSSFWorkbook(); } } else { @@ -2711,8 +2401,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { reportSheetName = reportSheetName.substring(0, 28); sheet = wb.getSheet(count + "-" + reportSheetName); sheetRef = sheet.getPackagePart().getPartName().getName(); - - // Step 2. Generate XML file. File tmp = File.createTempFile("sheet", ".xml"); try (FileOutputStream fileOutTemp = new FileOutputStream(tmp); Writer fw = new OutputStreamWriter(fileOutTemp, XML_ENCODING)) { @@ -2729,9 +2417,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { fw.flush(); fileOutTemp.flush(); } - - // Step 3. Substitute the template entry with the generated data - try (FileOutputStream outF = new FileOutputStream( AppUtils.getTempFolderPath() + filename + "." + nvls(extension, "xlsx"))) { templateFile = @@ -2750,13 +2435,10 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } } } else { - // If template supplied by Application if (nvl(rr.getTemplateFile()).length() > 0) { String templateFilename = rr.getTemplateFile(); extension = templateFilename.substring(templateFilename.lastIndexOf(".") + 1); filename = formattedReportName + formattedDate + user_id; - // filename = templateFilename.substring(0, - // templateFilename.lastIndexOf("."))+"_"+formattedDate+user_id; } else { filename = formattedReportName + formattedDate + user_id; } @@ -2765,10 +2447,8 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { try (FileOutputStream os = new FileOutputStream( AppUtils.getTempFolderPath() + "template" + formattedDate + user_id + ".xlsx")) { wb = new XSSFWorkbook(); - // Load customized styles if (rr != null) styles = loadXSSFStyles(rr, wb, styles); - // create data sheet if (isDashboard) { } else { @@ -2778,13 +2458,10 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { if (nvl(reportSheetName).length() > 28) reportSheetName = reportSheetName.substring(0, 28); sheet = wb.createSheet(reportSheetName); - - // customized mode if (!Globals.printExcelInLandscapeMode()) sheet.getPrintSetup().setLandscape(false); else sheet.getPrintSetup().setLandscape(true); - // get data sheet name sheetRef = sheet.getPackagePart().getPartName().getName(); wb.write(os); os.flush(); @@ -2809,8 +2486,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { os.flush(); } } - - // Step 2. Generate XML file. File tmp = File.createTempFile("sheet", ".xml"); try (FileOutputStream fileOutTemp = new FileOutputStream(tmp); Writer fw = new OutputStreamWriter(fileOutTemp, XML_ENCODING)) { @@ -2841,8 +2516,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { fw.flush(); fileOutTemp.flush(); } - // Step 3. Substitute the template entry with the generated data - try (FileOutputStream outF = new FileOutputStream(AppUtils.getTempFolderPath() + filename + "." + nvls(extension, "xlsx"))) { @@ -2857,8 +2530,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } } - // get servlet output stream - response.reset(); try (BufferedInputStream buf = new BufferedInputStream( new FileInputStream(AppUtils.getTempFolderPath() + filename + "." + nvls(extension, "xlsx"))); @@ -2873,8 +2544,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { "attachment;filename=" + filename + "." + nvls(extension, "xlsx")); int readBytes = 0; - - // read from the file; write to the ServletOutputStream while ((readBytes = buf.read()) != -1) sos.write(readBytes); @@ -3043,10 +2712,10 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { csvOut.print(","); } } - } // for + } csvOut.println(); - } // for + } int rowCount = 0; while (rs.next()) { rowCount++; @@ -3082,7 +2751,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { if (dv.isVisible()) { csvOut.print("\"" + strip.stripCSVHtml(dv.getDisplayValue()) + "\","); } - } // for + } csvOut.println(); } @@ -3140,11 +2809,9 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { csvOut.print(","); } } - } // for - + } csvOut.println(); - } // for - + } firstPass = true; int rowCount = 0; for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext();) { @@ -3163,31 +2830,32 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { final RowHeader rh = rhc.getNext(); csvOut.print("\"" + strip.stripCSVHtml(rh.getRowTitle()) + "\","); - } // for + } firstPass = false; - + DataValue dv = new DataValue(); for (dr.resetNext(); dr.hasNext();) { - DataValue dv = dr.getNext(); + dv = dr.getNext(); if (dv.isVisible()) { csvOut.print("\"" + strip.stripCSVHtml(dv.getDisplayValue()) + "\","); } - } // for + } csvOut.println(); - } // for + } if (rd.reportDataTotalRow != null) { + DataRow dr = new DataRow(); + DataValue dv = new DataValue(); for (rd.reportDataTotalRow.resetNext(); rd.reportDataTotalRow.hasNext();) { - DataRow dr = rd.reportDataTotalRow.getNext(); + dr = rd.reportDataTotalRow.getNext(); csvOut.print("\"" + "Total" + "\","); firstPass = false; for (dr.resetNext(); dr.hasNext();) { - DataValue dv = dr.getNext(); + dv = dr.getNext(); if (dv.isVisible()) { csvOut.print("\"" + strip.stripCSVHtml(dv.getDisplayValue()) + "\","); } - } // for - + } csvOut.println(); } } @@ -3223,9 +2891,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { final ZipEntry entry = new ZipEntry(csvFName); zos.putNextEntry(entry); - - // read data to the end of the source file and write it to the zip - // output stream. while ((size = fis.read(buffer, 0, buffer.length)) > 0) { zos.write(buffer, 0, size); } @@ -3254,7 +2919,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { final BufferedInputStream buf = new BufferedInputStream(fileIn);) { final byte[] bOut = new byte[4096]; - // read from the file; write to the ServletOutputStream int readBytes = 0; while ((readBytes = buf.read(bOut, 0, 4096)) > 0) { buf.available(); @@ -3278,7 +2942,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { logger.debug(EELFLoggerDelegate.debugLogger, ("Total Memory:" + runtime.totalMemory() / mb)); logger.debug(EELFLoggerDelegate.debugLogger, ("Max Memory:" + runtime.maxMemory() / mb)); - } // createCSVFileContent + } public String saveXMLFile(HttpServletRequest request, String reportName, String reportXML) { try { @@ -3297,12 +2961,12 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { "Exception saving XML source to file system: " + e.getMessage()); return null; } - } // saveXMLFile + } public ReportRuntime loadReportRuntime(HttpServletRequest request, String reportID) throws RaptorException { return loadReportRuntime(request, reportID, true); - } // loadReportRuntime + } public ReportRuntime loadReportRuntime(HttpServletRequest request, String reportID, boolean prepareForExecution) throws RaptorException { @@ -3320,13 +2984,13 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { AppConstants.SI_REPORT_RUNTIME); boolean inSchedule = AppUtils.getRequestFlag(request, AppConstants.SCHEDULE_ACTION); if (rr != null) { - if (requestFlag == 7) { // DASH + if (requestFlag == 7) { String reportXML = ReportLoader.loadCustomReportXML(reportID); logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] Report [" + reportID + "]: XML loaded")); rr = ReportRuntime.unmarshal(reportXML, reportID, request); rr.setParamValues(request, false, refresh); - rr.setDisplayFlags(true, true); // show content even at the first time + rr.setDisplayFlags(true, true); return rr; } else { logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] Load Report Runtime " @@ -3342,23 +3006,13 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { rr.resetVisualSettings(); rr.setDisplayFlags(nvl(request.getParameter(AppConstants.RI_SOURCE_PAGE)) .length() == 0, rDisplayContent || rr.isDisplayOptionHideForm()); - } // if + } return rr; - } // if + } } } - /* - * Cannot convert the definition => XML file not saved for preview also, commented code not - * maintained up to date ReportDefinition rdef = (ReportDefinition) - * request.getSession().getAttribute(AppConstants.SI_REPORT_DEFINITION); if(rdef!=null) - * if(reportID.equals(rdef.getReportID())) { // The report definition is in the session => create - * report runtime from it rr = new ReportRuntime(rdef, request); if(prepareForExecution) { - * request.getSession().setAttribute(AppConstants.SI_REPORT_RUNTIME, rr); - * rr.setDisplayFlags(request.getParameter(AppConstants.RI_SOURCE_PAGE)==null); } // if return rr; } - * // if - */ String reportXML = ReportLoader.loadCustomReportXML(reportID); logger.debug(EELFLoggerDelegate.debugLogger, @@ -3381,7 +3035,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { else rr.checkUserReadAccess(request); } - // TODO ON Demand if (rDisplayContent) { rr.setParamValues(request, false, true); request.getSession().setAttribute(AppConstants.SI_REPORT_RUNTIME, rr); @@ -3389,7 +3042,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { if (inSchedule) { rr.setParamValues(request, false, false); } - if (requestFlag == 7) { // DASH + if (requestFlag == 7) { rr.setDisplayFlags(true, true); } else { rr.setDisplayFlags(request.getParameter(AppConstants.RI_SOURCE_PAGE) == null, @@ -3405,10 +3058,10 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } - } // if + } return rr; - } // loadReportRuntime + } private boolean isReportAddedAsDashboard(HttpServletRequest request, String dashboardId, String reportId) throws RaptorException { @@ -3431,7 +3084,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { ReportDefinition rdef = ReportDefinition.unmarshal(reportXML, reportID, request); rdef.generateWizardSequence(request); return rdef; - } // createReportDefinition + } public ReportDefinition loadReportDefinition(HttpServletRequest request, String reportID) throws RaptorException { @@ -3458,7 +3111,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { request.getSession().setAttribute(AppConstants.SI_REPORT_DEFINITION, rdef); return rdef; - } // if + } if (isReportIDBlank) rdef = ReportDefinition.createBlank(request); @@ -3467,11 +3120,11 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] Report [" + reportID + "]: XML loaded")); rdef = createReportDefinition(request, reportID, reportXML); - } // else + } request.getSession().setAttribute(AppConstants.SI_REPORT_DEFINITION, rdef); return rdef; - } // loadReportDefinition + } public void setSheetName(String sheet_name) { sheetName = sheet_name; @@ -3670,8 +3323,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { XSSFCellStyle styleDefaultCell = null; styleDefaultCell = (XSSFCellStyle) styles.get(DEFAULT); - - // to check performance int mb = 1024 * 1024; Runtime runtime = Runtime.getRuntime(); @@ -3736,7 +3387,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { title = Utils.replaceInString(title, "_nl_", " \n"); sw.createCell(cellNum, Utils.excelEncode(title), styles.get("header").getIndex()); - } // for + } } @@ -3757,12 +3408,10 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { if (colSpan > 0) cellNum += colSpan; } - } // for + } rowNum += 1; - } // for - + } sw.endRow(); - // All the possible combinations of date format CreationHelper createHelper = wb.getCreationHelper(); HashMap<String, Short> dateFormatMap = new HashMap<>(); @@ -3855,7 +3504,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { rowCount++; if (rowCount % 10000 == 0) { - // to check performance logger.debug(EELFLoggerDelegate.debugLogger, ("Performance check for " + rowCount + " starting**************")); logger.debug(EELFLoggerDelegate.debugLogger, ("##### Heap utilization statistics [MB] #####")); @@ -3867,7 +3515,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { logger.debug(EELFLoggerDelegate.debugLogger, ("Max Memory:" + runtime.maxMemory() / mb)); System.out.println(rowCount + "TH ROW****##### END #####"); - // } sw.insertRow(rowNum); cellNum = -1; @@ -3882,9 +3529,10 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { styleRowCell = (XSSFCellStyle) styles.get(nvl(/* dr.getFormatId(), */"", DEFAULT)); j = 0; firstPass = false; + DataValue dv = new DataValue(); for (dr.resetNext(); dr.hasNext(); j++) { styleCell = null; - DataValue dv = dr.getNext(); + dv = dr.getNext(); HtmlFormatter htmlFormat = dv.getCellFormatter(); if (htmlFormat != null && dv.getFormatId() != null && styles != null) @@ -4412,8 +4060,9 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { if (drTotal != null) { drTotal.resetNext(); drTotal.getNext(); + DataValue dv = new DataValue(); for (; drTotal.hasNext();) { - DataValue dv = drTotal.getNext(); + dv = drTotal.getNext(); if (dv.isVisible()) { cellNum += 1; styleCell = null; @@ -4427,24 +4076,6 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { sw.endRow(); } - /* - * // To Display Total Values for Linear report if(rd.reportDataTotalRow!=null) { row = - * sheet.createRow(rowNum); cellNum = -1; rd.reportTotalRowHeaderCols.resetNext(); //for - * (rd.reportTotalRowHeaderCols.resetNext(); rd.reportTotalRowHeaderCols.hasNext();) { cellNum += 1; - * RowHeaderCol rhc = rd.reportTotalRowHeaderCols.getNext(); RowHeader rh = rhc.getRowHeader(0); - * row.createCell((short) cellNum).setCellValue(strip.stripHtml(rh.getRowTitle())); - * row.getCell((short) cellNum).setCellStyle(styleDefaultTotal); //} - * - * DataRow drTotal = rd.reportDataTotalRow.getNext(); //cellNum = -1; for (drTotal.resetNext(); - * drTotal.hasNext();j++) { cellNum += 1; cell = row.createCell((short) cellNum); DataValue dv = - * drTotal.getNext(); String value = dv.getDisplayValue(); cell.setCellValue(value); boolean bold = - * false; if (dv.isBold()) { if((dv.getDisplayTotal()!=null && dv.getDisplayTotal().equals("SUM(")) - * || (dv.getColName()!=null && dv.getColName().indexOf("999")!=-1)){ if (value!=null && - * (value.trim().startsWith("$")) || (value.trim().startsWith("-$") )) { - * cell.setCellStyle(styleCurrencyTotal); } else { cell.setCellStyle(styleTotal); } } else { - * cell.setCellStyle(styleDefaultTotal); } bold = true; } } } - */ - } catch (SQLException ex) { throw new RaptorException(ex); } catch (ReportSQLException ex) { @@ -4507,9 +4138,9 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } firstPass = false; int j = 0; - + DataValue dv = new DataValue(); for (dr.resetNext(); dr.hasNext(); j++) { - DataValue dv = dr.getNext(); + dv = dr.getNext(); styleCell = null; boolean bold = false; String value = nvl(dv.getDisplayValue()); @@ -4955,31 +4586,17 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { sw.createCell(cellNum, strip.stripHtml(Utils.excelEncode(value)), styles.get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex()); } - - // if (!(value.equals(""))){ - // int temp = Integer.parseInt(value.trim()); - // cell.setCellValue(temp); - // }else{ - // cell.setCellValue(strip.stripHtml(value)); - // } - // HSSFCellStyle styleFormat = null; - // HSSFCellStyle numberStyle = null; - // HSSFFont formatFont = null; - // short fgcolor = 0; - // short fillpattern = 0; - // System.out.println("1IF "+ (dv.isBold()) + " "+ value + " " + dv.getDisplayTotal() + " " + - // dv.getColName() ); if (dv.isBold()) { if ((dv.getDisplayTotal() != null && dv.getDisplayTotal().equals("SUM(")) || (dv.getColName() != null && dv.getColName().indexOf("999") != -1)) { if (value != null && (value.trim().startsWith("$")) || (value.trim().startsWith("-$"))) { - // cell.setCellStyle(styleCurrencyTotal); + } else { - // cell.setCellStyle(styleTotal); + } } else { - // cell.setCellStyle(styleDefaultTotal); + } bold = true; } @@ -4989,26 +4606,15 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } if (htmlFormat != null && dv.getFormatId() != null && bold == false && styles != null) { - // cell.setCellStyle((HSSFCellStyle) styles.get(nvl(/*dv.getFormatId()*/"",DEFAULT))); - } // else if (bold == false) - // cell.setCellStyle(styleDefault); - } // if (dv.isVisible) - } // for + } + + } + } + - /* - * for (int tmp=0; tmp<dataTypeMap.size(); tmp++){ String dataTypeStr = - * (String)(dataTypeMap.get(tmp)); if(dataTypeStr.equals("NUMBER")){ cell.setCellStyle(styleNumber); - * }else if (dataTypeStr.equals("VARCHAR2")){ cell.setCellStyle(styleDefault); - * - * }else if (dataTypeStr.equals("DATE")){ cell.setCellStyle(styleDate); }else{ - * - * } - * - * } - */ rowNum += 1; sw.endRow(); - } // for + } if (rr.getReportType().equals(AppConstants.RT_LINEAR) && rd.reportTotalRowHeaderCols != null) { @@ -5031,10 +4637,11 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { if (drTotal != null) { drTotal.resetNext(); drTotal.getNext(); + DataValue dv = new DataValue(); for (; drTotal.hasNext();) { cellNum += 1; styleCell = null; - DataValue dv = drTotal.getNext(); + dv = drTotal.getNext(); String value = dv.getDisplayValue(); sw.createCell(cellNum, value, styles.get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex()); @@ -5078,215 +4685,10 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } } - // end data from rd - } - - } - - private void paintXSSFExcelParams(HSSFWorkbook wb, int rowNum, int col, ArrayList paramsList, - String customizedParamInfo, XSSFSheet sheet, String reportTitle, String reportDescr) throws IOException { - int cellNum = 0; - XSSFRow row = null; - short s1 = 0, s2 = (short) 1; - HtmlStripper strip = new HtmlStripper(); - // Name Style - HSSFCellStyle styleName = wb.createCellStyle(); - styleName.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex()); - styleName.setAlignment(HorizontalAlignment.CENTER); - styleName.setBorderBottom(BorderStyle.THIN); - styleName.setBorderTop(BorderStyle.THIN); - styleName.setBorderRight(BorderStyle.THIN); - styleName.setBorderLeft(BorderStyle.THIN); - styleName.setDataFormat((short) 0); - HSSFFont font = wb.createFont(); - font.setFontHeight((short) (FONT_SIZE / 0.05)); - font.setFontName(FONT_TAHOMA); - font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); - font.setBold(font.getBold()); - styleName.setFont(font); - // Data Style - - // Create some fonts. - HSSFFont fontDefault = wb.createFont(); - - // Initialize the styles & fonts. - // The default will be plain . - fontDefault.setColor((short) HSSFFont.COLOR_NORMAL); - fontDefault.setFontHeight((short) (FONT_SIZE / 0.05)); - fontDefault.setFontName(FONT_TAHOMA); - fontDefault.setItalic(true); - // Style default will be normal with no background - HSSFCellStyle styleValue = wb.createCellStyle(); - HSSFCellStyle styleCurrencyDecimalNumberTotal = wb.createCellStyle(); - styleValue.setDataFormat((short) 0); - styleValue.setAlignment(HorizontalAlignment.CENTER); - styleValue.setBorderBottom(BorderStyle.THIN); - styleValue.setBorderTop(BorderStyle.THIN); - styleValue.setBorderLeft(BorderStyle.THIN); - styleValue.setBorderRight(BorderStyle.THIN); - - styleValue.setFillPattern(fillPattern.NO_FILL); - styleValue.setFont(fontDefault); - XSSFCell cell = null; - HSSFCellStyle styleDescription = wb.createCellStyle(); - styleDescription.setAlignment(HorizontalAlignment.CENTER); - - HSSFFont fontDescr = wb.createFont(); - fontDescr.setFontHeight((short) (FONT_HEADER_DESCR_SIZE / 0.05)); - fontDescr.setFontName(FONT_TAHOMA); - fontDescr.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); - fontDescr.setBold(true); - styleDescription.setFont(font); - XSSFCell cellDescr = null; - int paramSeq = 0; - Header header = sheet.getHeader(); - StringBuffer strBuf = new StringBuffer(); - if (!Globals.customizeFormFieldInfo() || customizedParamInfo.length() <= 0) { - for (Iterator iter = paramsList.iterator(); iter.hasNext();) { - IdNameValue value = (IdNameValue) iter.next(); - if (nvl(value.getId()).trim().length() > 0 && (!nvl(value.getId()).trim().equals("BLANK"))) { - paramSeq += 1; - if (paramSeq <= 1) { - row = sheet.createRow(++rowNum); - cell = row.createCell((short) 0); - sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, s1, s2)); - cellDescr = row.createCell((short) 0); - cellDescr.setCellValue(RUNTIME_PARAMETERS); - cellDescr.setCellStyle(styleDescription); - - strBuf.append(reportTitle + "\n"); - } - row = sheet.createRow(++rowNum); - cellNum = 0; - cell = row.createCell((short) cellNum); - cell.setCellValue(value.getId()); - cell.setCellStyle(styleName); - cellNum += 1; - cell = row.createCell((short) cellNum); - cell.setCellValue(value.getName().replaceAll("~", ",")); - cell.setCellStyle(styleValue); - - } - } // for - } else { - strBuf.append(reportTitle + "\n"); - Document document = new Document(); - document.open(); - HTMLWorker worker = new HTMLWorker(document); - StyleSheet style = new StyleSheet(); - style.loadTagStyle("body", "leading", "16,0"); - ArrayList p = HTMLWorker.parseToList(new StringReader(customizedParamInfo), style); - String name = ""; - String token = ""; - String value = ""; - String s = ""; - PdfPTable pdfTable = null; - for (int k = 0; k < p.size(); ++k) { - if (p.get(k) instanceof Paragraph) - s = ((Paragraph) p.get(k)).toString(); - else { - pdfTable = ((PdfPTable) p.get(k)); - } - // todo: Logic for parsing pdfTable should be added after upgrading to iText 5.0.0 - s = s.replaceAll(",", "|"); - s = s.replaceAll("~", ","); - if (s.indexOf(":") != -1) { - row = sheet.createRow(++rowNum); - cell = row.createCell((short) 0); - sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, s1, s2)); - cellDescr = row.createCell((short) 0); - cellDescr.setCellValue(RUNTIME_PARAMETERS); - cellDescr.setCellStyle(styleDescription); - - StringTokenizer st = new StringTokenizer(s.trim(), "|"); - while (st.hasMoreTokens()) { - token = st.nextToken(); - token = token.trim(); - if (!(token.trim().equals("|") || token.trim().equals("]]") || token.trim().equals("]") - || token.trim().equals("["))) { - if (token.endsWith(":")) { - name = token; - name = name.substring(0, name.length() - 1); - if (name.startsWith("[")) - name = name.substring(1); - value = st.nextToken(); - if (nvl(value).endsWith("]")) - value = nvl(value).substring(0, nvl(value).length() - 1); - } - if (name != null && name.trim().length() > 0) { - row = sheet.createRow((short) ++rowNum); - cellNum = 0; - cell = row.createCell((short) cellNum); - cell.setCellValue(name.trim()); - cell.setCellStyle(styleName); - cellNum += 1; - cell = row.createCell((short) cellNum); - cell.setCellValue(value.trim()); - cell.setCellStyle(styleValue); - } - /* - * if(token.endsWith(":") && (value!=null && value.trim().length()<=0) && (name!=null && - * name.trim().length()>0 && name.endsWith(":"))) { name = name.substring(0, - * name.indexOf(":")+1); //value = token.substring(token.indexOf(":")+1); row = - * sheet.createRow((short) ++rowNum); cellNum = 0; cell = row.createCell((short) cellNum); - * cell.setCellValue(name.trim()); cell.setCellStyle(styleName); cellNum += 1; cell = - * row.createCell((short) cellNum); cell.setCellValue(value.trim()); - * cell.setCellStyle(styleValue); - * - * //strBuf.append(name.trim()+": "+ value.trim()+"\n"); value = ""; name = ""; } - */ } - int cw = 0; - cw = name.trim().length() + 12; - if (sheet.getColumnWidth((short) 0) < (short) name.trim().length()) - sheet.setColumnWidth((short) 0, (short) name.trim().length()); - if (sheet.getColumnWidth((short) 1) < (short) value.trim().length()) - sheet.setColumnWidth((short) 1, (short) value.trim().length()); - name = ""; - value = ""; - - } - - try { - SimpleDateFormat oracleDateFormat = new SimpleDateFormat("MM/dd/yyyy kk:mm:ss"); - Date sysdate = oracleDateFormat.parse(ReportLoader.getSystemDateTime()); - SimpleDateFormat dtimestamp = new SimpleDateFormat(Globals.getScheduleDatePattern()); - - row = sheet.createRow((short) ++rowNum); - cellNum = 0; - cell = row.createCell((short) cellNum); - cell.setCellValue("Report Date/Time"); - cell.setCellStyle(styleName); - cellNum += 1; - cell = row.createCell((short) cellNum); - - cell.setCellValue(dtimestamp.format(sysdate) + " " + Globals.getTimeZone()); - cell.setCellStyle(styleValue); - - } catch (Exception ex) { - logger.error(EELFLoggerDelegate.errorLogger, "Excetion occured", ex); - } - } - } - - /* - * Iterator iter1 = paramsList.iterator(); s1 = 0; s2 = (short)10; if(iter1.hasNext()) { row = - * sheet.createRow((short) ++rowNum); cellNum = 0; cell = row.createCell((short) cellNum); - * sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, s1, s2)); - * cell.setCellValue(strip.stripHtml(customizedParamInfo)); } - */ - } // if - Iterator iterCheck = paramsList.iterator(); - if (iterCheck.hasNext()) { - rowNum += 2; - row = sheet.createRow(rowNum); - } - header.setCenter(HSSFHeader.font(FONT_TAHOMA, "") + HSSFHeader.fontSize((short) FONT_HEADER_TITLE_SIZE) - + strBuf.toString()); } - // Trying different --> public void createHTMLFileContent(Writer out, ReportData rd, ReportRuntime rr, String sql_whole, HttpServletRequest request, HttpServletResponse response) throws RaptorException, IOException { @@ -5320,7 +4722,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { csvOut.println("</TR>"); } - } // for + } csvOut.println("<TR><TD COLSPAN=\"2\"> </TD></TR>"); csvOut.println("<TR><TD COLSPAN=\"2\"> </TD></TR>"); System.out.println("HTML-Excel: Header Rendering complete " + new java.util.Date()); @@ -5360,18 +4762,14 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { csvOut.print("<TD bgColor=\"8F9381\">" + ch.getColumnTitle() + "</TD>"); } - } // for + } } csvOut.println("</TR>"); - } // for + } while (rs.next()) { csvOut.println("<TR>"); - /* - * if(runtime.freeMemory()/mb <= ((runtime.maxMemory()/mb)*Globals.getMemoryThreshold()/100) ) { - * csvOut.print(Globals.getUserDefinedMessageForMemoryLimitReached() + " " + rowCount - * +"records out of " + rr.getReportDataSize() + " were downloaded to CSV."); break; } - */ rowCount++; + rowCount++; colHash = new HashMap(); for (int i = 1; i <= numberOfColumns; i++) { colHash.put(rsmd.getColumnName(i), rs.getString(i)); @@ -5415,6 +4813,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { logger.error(EELFLoggerDelegate.errorLogger, "SQLException occured", ex); } } + // csvOut.flush(); } else { boolean firstPass = true; int numberOfColumns = 0; @@ -5429,7 +4828,8 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { numberOfColumns++; csvOut.print("<TD bgColor=\"8F9381\">" + rhc.getColumnTitle() + "</TD>"); } - } // for + + } for (chr.resetNext(); chr.hasNext();) { ColumnHeader ch = chr.getNext(); @@ -5438,14 +4838,15 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { numberOfColumns++; csvOut.print("<TD bgColor=\"8F9381\">" + ch.getColumnTitle() + "</TD>"); } - } // for + } firstPass = false; csvOut.println("</TR>"); - } // for + } firstPass = true; + DataRow dr = new DataRow(); for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext();) { - DataRow dr = rd.reportDataRows.getNext(); + dr = rd.reportDataRows.getNext(); csvOut.println("<TR>"); for (rd.reportRowHeaderCols.resetNext(1); rd.reportRowHeaderCols.hasNext();) { RowHeaderCol rhc = rd.reportRowHeaderCols.getNext(); @@ -5454,19 +4855,19 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { RowHeader rh = rhc.getNext(); csvOut.print("<TD bgColor=\"8F9381\">" + strip.stripCSVHtml(rh.getRowTitle()) + "</TD>"); - } // for + } firstPass = false; - + DataValue dv = new DataValue(); for (dr.resetNext(); dr.hasNext();) { - DataValue dv = dr.getNext(); + dv = dr.getNext(); if (dv.isVisible()) csvOut.print( "<TD bgColor=\"8F9381\">" + strip.stripCSVHtml(dv.getDisplayValue()) + "</TD>"); - } // for + } csvOut.println("</TR>"); - } // for + } } else { csvOut.println("<TR><TD COLSPAN=\"" + numberOfColumns + "\">No Data Found</TD></TR>"); } @@ -5481,7 +4882,7 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { System.out.println("Total Memory:" + runtime.totalMemory() / mb); System.out.println("Max Memory:" + runtime.maxMemory() / mb); - } // createCSVFileContent + } /** * Checking if every row and cell in merging region exists, and create those which are not @@ -5507,4 +4908,2123 @@ public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject { } } } -} // ReportHandler + + //XLS RELATED METHODS + public void createExcelXlsContent(final Writer out, ReportData rd, final ReportRuntime rr, + final HttpServletRequest request, final HttpServletResponse response, final String user_id, final int type) + throws IOException, RaptorException { + Map styles = new HashMap(); + final HttpSession session = request.getSession(); + HSSFWorkbook wb = null; + SXSSFWorkbook wb1 = null; + int returnValue = 0; + final boolean isDashboard = (session.getAttribute(AppConstants.SI_DASHBOARD_REP_ID) != null) && + (((String) session.getAttribute(AppConstants.SI_DASHBOARD_REP_ID)).equals(rr.getReportID())); + if (isDashboard) { + final String formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date()); + final String xlsFName = "./Dashboard.xls"; + try { + FileInputStream xlsIn = null; + final Map reportRuntimeMap = (TreeMap) request.getSession() + .getAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME_MAP); + final Map reportDataMap = (TreeMap) request.getSession() + .getAttribute(AppConstants.SI_DASHBOARD_REPORTDATA_MAP); + if (reportRuntimeMap != null) { + final Set setReportRuntime = reportRuntimeMap.entrySet(); + final Set setReportDataMap = reportDataMap.entrySet(); + final Iterator iter2 = setReportDataMap.iterator(); + int count = 0; + for (Iterator iter = setReportRuntime.iterator(); iter.hasNext();) { + count++; + try { + xlsIn = + new FileInputStream(xlsFName); + } catch (final FileNotFoundException e) { + logger.error(EELFLoggerDelegate.errorLogger, "File not found in the specified path.", e); + } + if (xlsIn != null) { + wb = new HSSFWorkbook(xlsIn); + } else { + wb = new HSSFWorkbook(); + } + final Map.Entry entryData = (Entry) iter2.next(); + final Map.Entry entry = (Entry) iter.next(); + final ReportRuntime rrDashRep = (ReportRuntime) entry.getValue(); + final ReportData rdDashRep = (ReportData) entryData.getValue(); + int col = 0; + final String reportTitle = (nvl(rrDashRep.getReportTitle()).length() > 0 ? rrDashRep + .getReportTitle() + : rrDashRep.getReportName()); + final String reportDescr = rrDashRep.getReportDescr(); + if (rdDashRep != null) { + if (!rdDashRep.reportRowHeaderCols.hasNext()) { + col = rdDashRep.getTotalColumnCount(); + } else { + col = rdDashRep.getTotalColumnCount(); + } + }else { + continue; + } + if (col == 0) { + col = 10; + } + int rowNum = 0; + final String formattedReportName = + new HtmlStripper().stripSpecialCharacters(rrDashRep.getReportName()); + + HSSFSheet sheet = null; + try { + sheet = wb.createSheet(formattedReportName); + sheet.getPrintSetup().setLandscape(true); + styles = loadStylesXls(rrDashRep, wb); + } catch (final IllegalArgumentException ex) { + logger.warn("IllegalArgumentException occured", ex); + try (final FileOutputStream xlsOut = new FileOutputStream(xlsFName)) { + wb.write(xlsOut); + xlsOut.flush(); + } catch (final IOException e) { + logger.warn("Failed to open FileOutputStream", e); + } + continue; + } + if (Globals.getPrintTitleInDownload() && reportTitle != null) { + paintExcelHeaderXls(wb, rowNum, col, reportTitle, reportDescr, sheet); + rowNum = sheet.getLastRowNum(); + } else { + rowNum = 0; + } + // getting ReportRuntime object from session + if (Globals.getPrintParamsInDownload() + && rrDashRep.getParamNameValuePairsforPDFExcel(request, 1) != null) { + if (count > 1 && Globals.showParamsInAllDashboardReports()) { + paintExcelParamsXls(wb, rowNum, col, + rrDashRep.getParamNameValuePairsforPDFExcel(request, 1), + rrDashRep.getFormFieldComments(request), sheet, reportTitle, reportDescr); + } else if (count == 1) { + paintExcelParamsXls(wb, rowNum, col, + rrDashRep.getParamNameValuePairsforPDFExcel(request, 1), + rrDashRep.getFormFieldComments(request), sheet, reportTitle, reportDescr); + } + } + rowNum = sheet.getLastRowNum(); + final String sql_whole = rrDashRep.getWholeSQL(); + if(rrDashRep.getCachedSQL().length() > 0) { + returnValue = paintExcelDataXls(wb, rowNum, rdDashRep, styles, rrDashRep, sheet, "", + request); + }else { + returnValue = paintExcelDataXls(wb, rowNum, rdDashRep, styles, rrDashRep, sheet, sql_whole, + request); + } + if (returnValue == 0) { + if (Globals.getPrintFooterInDownload()) { + rowNum = sheet.getLastRowNum(); + rowNum += 2; + paintExcelFooterXls(wb, rowNum, col, sheet); + } + try (final FileOutputStream xlsOut = new FileOutputStream(xlsFName)) { + wb.write(xlsOut); + xlsOut.flush(); + } catch (final IOException e) { + logger.warn("Failed to open FileOutputStream", e); + } + wb = null; + } + } + + response.reset(); + response.setContentType("application/vnd.ms-excel"); + response.setHeader("Content-disposition", + "attachment;filename=" + "dashboard" + formattedDate + user_id + ".xls"); + xlsIn = new FileInputStream(xlsFName); + int readBytes = 0; + final byte[] bOut = new byte[4096]; + try (final BufferedInputStream buf = new BufferedInputStream(xlsIn); + final ServletOutputStream sos = response.getOutputStream();) { + while ((readBytes = buf.read(bOut, 0, 4096)) > 0) { + buf.available(); + sos.write(bOut, 0, readBytes); + } + } catch (final IOException e) { + logger.warn("Failed to open BufferedInputStream", e); + } + } + } catch (final IOException ex) { + logger.warn("Failed to open Stream", ex); + throw ex; + } + + final File f = new File(xlsFName); + if (f.exists()) { + f.delete(); + } + } else { + wb = new HSSFWorkbook(); + setSheetName(Globals.getSheetName()); + if (rr != null) { + styles = loadStylesXls(rr, wb); + } + final String reportTitle = (nvl(rr.getReportTitle()).length() > 0 ? rr.getReportTitle() + : rr.getReportName()); + final String reportDescr = rr.getReportDescr(); + final int col = getColumnCountForDownloadFile(rr, rd); + int rowNum = 0; + final HSSFSheet sheet = wb.createSheet(getSheetName()); + sheet.getPrintSetup().setLandscape(true); + if (Globals.getPrintTitleInDownload() && reportTitle != null) { + paintExcelHeaderXls(wb, rowNum, col, reportTitle, reportDescr, sheet); + rowNum = sheet.getLastRowNum(); + } else { + rowNum = 0; + } + if (Globals.getPrintParamsInDownload() && rr.getParamNameValuePairsforPDFExcel(request, 1) != null) { + List paramsList = rr.getParamNameValuePairsforPDFExcel(request, 1); + if (paramsList.size() <= 0) { + paramsList = (ArrayList) request.getSession().getAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO); + } + paintExcelParamsXls(wb, rowNum, col, paramsList, rr.getFormFieldComments(request), sheet, reportTitle, + reportDescr); + } + rowNum = sheet.getLastRowNum(); + final String formattedReportName = new HtmlStripper().stripSpecialCharacters(rr.getReportName()); + final String formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date()); + response.reset(); + response.setContentType("application/vnd.ms-excel"); + response.setHeader("Content-disposition", + "attachment;filename=" + formattedReportName + formattedDate + user_id + ".xls"); + if (type == 3 && rr.getSemaphoreList() == null && !(rr.getReportType() + .equals(AppConstants.RT_CROSSTAB))) { // type = 3 is whole + String sql_whole = ""; + sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE); + + if (sql_whole == null) { + if (!rr.getReportType().equals(AppConstants.RT_HIVE)) { + sql_whole = rr.getWholeSQL(); + } else { + sql_whole = rr.getReportSQL(); + } + } + if(rr.isSinglePageDownload()) + sql_whole = rr.getCachedSQL(); + returnValue = paintExcelDataXls(wb, rowNum, rd, styles, rr, sheet, sql_whole, request); + rr.setSinglePageDownload(false); + } else if (type == 2) { + returnValue = paintExcelDataXls(wb, rowNum, rd, styles, rr, sheet, "", request); + } else { + int downloadLimit = + (rr.getMaxRowsInExcelDownload() > 0 && (rr.getMaxRowsInExcelDownload() < Globals.getDownloadLimit())) ? rr.getMaxRowsInExcelDownload() + : Globals.getDownloadLimit(); + String action = request.getParameter(AppConstants.RI_ACTION); + if (!(rr.getReportType().equals(AppConstants.RT_CROSSTAB)) && !action.endsWith("session")) { + rd = rr.loadReportData(-1, AppUtils.getUserID(request), downloadLimit, request, + false /* download */); + } + if (rr.getSemaphoreList() != null) { + if (rr.getReportType().equals(AppConstants.RT_CROSSTAB)) { + returnValue = paintExcelDataXls(wb, rowNum, rd, styles, rr, sheet, "", request); + } else { + rd = rr.loadReportData(-1, AppUtils.getUserID(request), downloadLimit, request, true); + returnValue = paintExcelDataXls(wb, rowNum, rd, styles, rr, sheet, "", request); + } + } else { + String sql_whole = ""; + sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE); + + if (sql_whole == null) { + if (!rr.getReportType().equals(AppConstants.RT_HIVE)) { + sql_whole = rr.getWholeSQL(); + } else { + sql_whole = rr.getReportSQL(); + } + } + returnValue = paintExcelDataXls(wb, rowNum, rd, styles, rr, sheet, sql_whole, request); + } + } + if (returnValue == 0) { + if (Globals.getPrintFooterInDownload()) { + rowNum = sheet.getLastRowNum(); + rowNum += 2; + paintExcelFooterXls(wb, rowNum, col, sheet); + } + wb.setPrintArea( + 0, // sheet index + 0, // start column + col, // end column + 0, // start row + rowNum // end row + ); + try (final ServletOutputStream sos = response.getOutputStream();) { + wb.write(sos); + sos.flush(); + } catch (final IOException e) { + logger.warn("Failed to get OutputStream", e); + }finally { + wb.close(); + wb = null; + } + } + } + } + + private HashMap loadStylesXls(ReportRuntime rr, HSSFWorkbook wb) { + HSSFCellStyle styleDefault = wb.createCellStyle(); + HSSFFont fontDefault = wb.createFont(); + fontDefault.setColor((short) HSSFFont.COLOR_NORMAL); + fontDefault.setFontHeight((short) (FONT_SIZE / 0.05)); + fontDefault.setFontName(FONT_TAHOMA); + + styleDefault.setAlignment(HorizontalAlignment.CENTER); + styleDefault.setBorderBottom(BorderStyle.THIN); + styleDefault.setBorderTop(BorderStyle.THIN); + styleDefault.setBorderLeft(BorderStyle.THIN); + styleDefault.setBorderRight(BorderStyle.THIN); + styleDefault.setFillPattern(fillPattern.NO_FILL); + styleDefault.setFont(fontDefault); + + HSSFCellStyle styleRed = wb.createCellStyle(); + styleRed.cloneStyleFrom(styleDefault); + styleRed.setFillForegroundColor((short) HSSFColor.HSSFColorPredefined.RED.getIndex()); + styleRed.setFillPattern(fillPattern.SOLID_FOREGROUND); + HSSFFont fontRed = wb.createFont(); + fontRed.setColor((short) HSSFColor.HSSFColorPredefined.WHITE.getIndex()); + fontRed.setFontHeight((short) (FONT_SIZE / 0.05)); + fontRed.setFontName(FONT_TAHOMA); + styleRed.setFont(fontRed); + + HSSFCellStyle styleYellow = wb.createCellStyle(); + styleYellow.cloneStyleFrom(styleDefault); + styleYellow.setFillForegroundColor((short) HSSFColor.HSSFColorPredefined.YELLOW.getIndex()); + styleYellow.setFillPattern(fillPattern.SOLID_FOREGROUND); + HSSFFont fontYellow = wb.createFont(); + fontYellow.setColor((short) HSSFColor.HSSFColorPredefined.BLACK.getIndex()); + fontYellow.setFontHeight((short) (FONT_SIZE / 0.05)); + fontYellow.setFontName(FONT_TAHOMA); + styleYellow.setFont(fontYellow); + + HSSFCellStyle styleGreen = wb.createCellStyle(); + styleGreen.cloneStyleFrom(styleDefault); + styleGreen.setFillForegroundColor((short) HSSFColor.HSSFColorPredefined.GREEN.getIndex()); + styleGreen.setFillPattern(fillPattern.SOLID_FOREGROUND); + HSSFFont fontGreen = wb.createFont(); + fontGreen.setColor((short) HSSFColor.HSSFColorPredefined.WHITE.getIndex()); + fontGreen.setFontHeight((short) (FONT_SIZE / 0.05)); + fontGreen.setFontName(FONT_TAHOMA); + styleGreen.setFont(fontGreen); + + ArrayList semColumnList = new ArrayList(); + List dsList = rr.getDataSourceList().getDataSource(); + for (Iterator iter = dsList.iterator(); iter.hasNext();) { + DataSourceType element = (DataSourceType) iter.next(); + List dcList = element.getDataColumnList().getDataColumn(); + for (Iterator iterator = dcList.iterator(); iterator.hasNext();) { + DataColumnType element1 = (DataColumnType) iterator.next(); + semColumnList.add(element1.getSemaphoreId()); + + } + } + SemaphoreList semList = rr.getSemaphoreList(); + HashMap hashMapStyles = new HashMap(); + HashMap hashMapFonts = new HashMap(); + hashMapFonts.put(DEFAULT, fontDefault); + hashMapFonts.put(RED, fontRed); + hashMapFonts.put(YELLOW, fontYellow); + hashMapFonts.put(GREEN, fontGreen); + hashMapStyles.put(DEFAULT, styleDefault); + hashMapStyles.put(RED, styleRed); + hashMapStyles.put(YELLOW, styleYellow); + hashMapStyles.put(GREEN, styleGreen); + HSSFCellStyle cellStyle = null; + if (semList == null || semList.getSemaphore() == null) { + return hashMapStyles; + } else { + for (Iterator iter = semList.getSemaphore().iterator(); iter.hasNext();) { + SemaphoreType sem = (SemaphoreType) iter.next(); + if (!semColumnList.contains(sem.getSemaphoreId())) + continue; + FormatList fList = sem.getFormatList(); + List formatList = fList.getFormat(); + for (Iterator fIter = formatList.iterator(); fIter.hasNext();) { + FormatType fmt = (FormatType) fIter.next(); + if (fmt != null) { + cellStyle = wb.createCellStyle(); + HSSFFont cellFont = wb.createFont(); + if (nvl(fmt.getBgColor()).length() > 0) { + cellStyle.setFillForegroundColor(ExcelColorDef.getExcelColor(fmt + .getBgColor())); + cellStyle.setFillPattern(fillPattern.SOLID_FOREGROUND); + } + if (nvl(fmt.getFontColor()).length() > 0) { + cellFont.setColor(ExcelColorDef.getExcelColor(fmt.getFontColor())); + } else + cellFont.setColor((short) HSSFFont.COLOR_NORMAL); + if (fmt.isBold()) + cellFont.setBold(true); + if (fmt.isItalic()) + cellFont.setItalic(true); + if (fmt.isUnderline()) + cellFont.setUnderline(HSSFFont.U_SINGLE); + if (nvl(fmt.getFontFace()).length() > 0) + cellFont.setFontName(fmt.getFontFace()); + else + cellFont.setFontName(FONT_TAHOMA); + + if (nvl(fmt.getFontSize()).length() > 0) { + try { + // cellFont.setFontHeight((short) (Integer.parseInt(fmt.getFontSize()) / 0.05)); + cellFont.setFontHeight((short) (FONT_SIZE / 0.05)); + } catch (NumberFormatException e) { + cellFont.setFontHeight((short) (FONT_SIZE / 0.05));// 10 + } + } else + cellFont.setFontHeight((short) (FONT_SIZE / 0.05)); + cellStyle.setFont(cellFont); + cellStyle.setAlignment(HorizontalAlignment.CENTER); + cellStyle.setBorderBottom(BorderStyle.THIN); + cellStyle.setBorderTop(BorderStyle.THIN); + cellStyle.setBorderLeft(BorderStyle.THIN); + cellStyle.setBorderRight(BorderStyle.THIN); + hashMapStyles.put(fmt.getFormatId(), cellStyle); + } else { + hashMapStyles.put(DEFAULT, styleDefault); + } + } + } + } + return hashMapStyles; + } + + private void paintExcelHeaderXls(HSSFWorkbook wb, int rowNum, int col, String reportTitle, + String reportDescr, HSSFSheet sheet) { + short shrt_one = 0, shrt_two = (short) (col - 1); + rowNum += 1; + if(shrt_two != 0) + sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, shrt_one, shrt_two)); + HSSFRow row = null; + HSSFRow row1 = null; + row = sheet.createRow(rowNum); + HSSFCellStyle styleHeader = wb.createCellStyle(); + styleHeader.setAlignment(HorizontalAlignment.CENTER); + HSSFFont font = wb.createFont(); + font.setFontHeight((short) (FONT_HEADER_TITLE_SIZE / 0.05)); // 14 + font.setFontName(FONT_TAHOMA); + font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); + styleHeader.setFont(font); + HSSFCell cell = row.createCell((short) 0); + cell.setCellValue(reportTitle); + cell.setCellStyle(styleHeader); + Header header = sheet.getHeader(); + header.setCenter(HSSFHeader.font(FONT_TAHOMA, "") + HSSFHeader.fontSize((short) 9) + " " + reportTitle); + if (Globals.getShowDescrAtRuntime() && nvl(reportDescr).length() > 0) { + rowNum += 1; + if(shrt_two != 0) + sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, shrt_one, shrt_two)); + HSSFCellStyle styleDescription = wb.createCellStyle(); + styleDescription.setAlignment(HorizontalAlignment.CENTER); + HSSFFont fontDescr = wb.createFont(); + fontDescr.setFontHeight((short) FONT_HEADER_DESCR_SIZE); + fontDescr.setFontName(FONT_TAHOMA); + fontDescr.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); + styleDescription.setFont(fontDescr); + HSSFCell cellDescr = row.createCell((short) 0); + cellDescr.setCellValue(reportDescr); + cellDescr.setCellStyle(styleHeader); + } + if (Globals.disclaimerPositionedTopInCSVExcel()) { + rowNum += 1; + row = sheet.createRow(rowNum); + if(shrt_two != 0) + sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, shrt_one, shrt_two)); + HSSFCellStyle styleDescription = wb.createCellStyle(); + styleDescription.setAlignment(HorizontalAlignment.CENTER); + HSSFFont fontDescr = wb.createFont(); + fontDescr.setFontHeight((short) (FONT_SIZE / 0.05)); // 14 + fontDescr.setFontName(FONT_TAHOMA); + fontDescr.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); + fontDescr.setBold(true); + styleDescription.setFont(fontDescr); + HSSFCell cellDescr = row.createCell((short) 0); + String disclaimer = Globals.getFooterFirstLine() + " " + Globals.getFooterSecondLine(); + cellDescr.setCellValue(disclaimer); + cellDescr.setCellStyle(styleDescription); + } + rowNum += 1; + row = sheet.createRow(rowNum); + } + + private int paintExcelDataXls(final HSSFWorkbook wb, int rowNum, final ReportData rd, final Map styles, + final ReportRuntime rr, final HSSFSheet sheet, final String sql_whole, + final HttpServletRequest request) throws RaptorException { + int mb = 1024 * 1024; + Runtime runtime = Runtime.getRuntime(); + int returnValue = 0; + HSSFCellStyle styleDefault = wb.createCellStyle(); + HSSFCellStyle styleNumber = wb.createCellStyle(); + HSSFCellStyle styleDecimalNumber = wb.createCellStyle(); + HSSFCellStyle styleCurrencyNumber = wb.createCellStyle(); + HSSFCellStyle styleCurrencyDecimalNumber = wb.createCellStyle(); + HSSFCellStyle styleDate = wb.createCellStyle(); + HtmlStripper strip = new HtmlStripper(); + HSSFCellStyle styleDataHeader = wb.createCellStyle(); + styleDataHeader.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex()); + styleDataHeader.setFillPattern(fillPattern.SOLID_FOREGROUND); + styleDataHeader.setAlignment(HorizontalAlignment.CENTER); + styleDataHeader.setBorderBottom(BorderStyle.THIN); + styleDataHeader.setBorderTop(BorderStyle.THIN); + styleDataHeader.setBorderRight(BorderStyle.THIN); + styleDataHeader.setBorderLeft(BorderStyle.THIN); + HSSFFont font = wb.createFont(); + font.setFontHeight((short) (FONT_SIZE / 0.05)); + font.setFontName(FONT_TAHOMA); + font.setBold(true); + font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); + styleDataHeader.setFont(font); + boolean firstPass = true; + ArrayList cellWidth = new ArrayList(); + java.util.HashMap dataTypeMap = new java.util.HashMap(); + int cellNum = 0; + rowNum += 0; + ColumnHeaderRow chr = null; + String title = ""; + int columnRows = rr.getVisibleColumnCount() - 1; + HttpSession session = request.getSession(); + String drilldown_index = (String) session.getAttribute("drilldown_index"); + int index = 0; + try { + index = Integer.parseInt(drilldown_index); + } catch (NumberFormatException ex) { + index = 0; + } + String header = (String) session.getAttribute("TITLE_" + index); + String subtitle = (String) session.getAttribute("SUBTITLE_" + index); + if (nvl(header).length() > 0) { + header = Utils.replaceInString(header, "<BR/>", " "); + header = Utils.replaceInString(header, "<br/>", " "); + header = Utils.replaceInString(header, "<br>", " "); + header = strip.stripHtml(nvl(header).trim()); + subtitle = Utils.replaceInString(subtitle, "<BR/>", " "); + subtitle = Utils.replaceInString(subtitle, "<br/>", " "); + subtitle = Utils.replaceInString(subtitle, "<br>", " "); + subtitle = strip.stripHtml(nvl(subtitle).trim()); + HSSFRow row = sheet.createRow(rowNum); + cellNum = 0; + row.createCell((short) cellNum).setCellValue(header); + sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, (short) cellNum, (short) (columnRows))); + rowNum += 1; + row = sheet.createRow(rowNum); + cellNum = 0; + row.createCell((short) cellNum).setCellValue(subtitle); + sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, (short) cellNum, (short) (columnRows))); + rowNum += 1; + } + + for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext();) { + HSSFRow row = sheet.createRow(rowNum); + cellNum = -1; + chr = rd.reportColumnHeaderRows.getNext(); + if (nvl(sql_whole).length() <= 0 || (!rr.getReportType().equals(AppConstants.RT_LINEAR))) { + if (rr.getReportType().equals(AppConstants.RT_CROSSTAB)) + rd.reportRowHeaderCols.resetNext(0); + else + rd.reportRowHeaderCols.resetNext(1); + for (; rd.reportRowHeaderCols.hasNext();) { + cellNum += 1; + RowHeaderCol rhc = rd.reportRowHeaderCols.getNext(); + if (firstPass) { + title = rhc.getColumnTitle(); + title = Utils.replaceInString(title, "_nl_", " \n"); + row.createCell((short) cellNum).setCellValue(title); + if (cellWidth.size() > 0 && cellWidth.size() > cellNum) { + if (((Integer) cellWidth.get(cellNum)).intValue() < rhc + .getColumnTitle().length()) + cellWidth.set(cellNum, new Integer(title.length())); + } else + cellWidth.add(cellNum, new Integer(title.length())); + row.getCell((short) cellNum).setCellStyle(styleDataHeader); + } + } + } + firstPass = false; + for (chr.resetNext(); chr.hasNext();) { + ColumnHeader ch = chr.getNext(); + if (ch.isVisible()) { + cellNum += 1; + int colSpan = ch.getColSpan() - 1; + title = ch.getColumnTitle(); + title = Utils.replaceInString(title, "_nl_", " \n"); + row.createCell((short) cellNum).setCellValue(title); + if (colSpan > 0) { + for (int k = 1; k <= colSpan; k++) { + row.createCell((short) cellNum + k); + } + sheet.addMergedRegion( + new CellRangeAddress(rowNum, rowNum, (short) cellNum, (short) (cellNum + colSpan))); + } + row.getCell((short) (cellNum)).setCellStyle(styleDataHeader); + for (int k = 1; k <= colSpan; k++) { + row.getCell((short) (cellNum + k)).setCellStyle(styleDataHeader); + } + if (colSpan > 0) + cellNum += colSpan; + } + } + rowNum += 1; + } + HSSFCellStyle styleCell = null; + HSSFCellStyle styleTotal = wb.createCellStyle(); + HSSFCellStyle styleCurrencyTotal = wb.createCellStyle(); + HSSFCellStyle styleDefaultTotal = wb.createCellStyle(); + HSSFCellStyle styleCurrencyDecimalNumberTotal = wb.createCellStyle(); + HSSFCellStyle styleDecimalNumberTotal = wb.createCellStyle(); + HSSFCellStyle styleCurrencyNumberTotal = wb.createCellStyle(); + HSSFFont fontDefault = wb.createFont(); + HSSFFont fontBold = wb.createFont(); + fontDefault.setColor((short) HSSFFont.COLOR_NORMAL); + fontDefault.setFontHeight((short) (FONT_SIZE / 0.05)); + fontDefault.setFontName(FONT_TAHOMA); + fontBold.setColor((short) HSSFFont.COLOR_NORMAL); + fontBold.setFontHeight((short) (FONT_SIZE / 0.05)); + fontBold.setFontName(FONT_TAHOMA); + fontBold.setBold(true); + styleDefault.setAlignment(HorizontalAlignment.CENTER); + styleDefault.setBorderBottom(BorderStyle.THIN); + styleDefault.setBorderTop(BorderStyle.THIN); + styleDefault.setBorderLeft(BorderStyle.THIN); + styleDefault.setBorderRight(BorderStyle.THIN); + styleDefault.setFillPattern(fillPattern.NO_FILL); + styleDefault.setFont(fontDefault); + styleDefault.setWrapText(true); + styleNumber.setAlignment(HorizontalAlignment.CENTER); + styleNumber.setBorderBottom(BorderStyle.THIN); + styleNumber.setBorderTop(BorderStyle.THIN); + styleNumber.setBorderLeft(BorderStyle.THIN); + styleNumber.setBorderRight(BorderStyle.THIN); + styleNumber.setFillPattern(fillPattern.NO_FILL); + styleNumber.setFont(fontDefault); + try { + styleNumber.setDataFormat((short) 0x26);// HSSFDataFormat.getBuiltinFormat("(#,##0_);[Red](#,##0)")); + } catch (Exception e) { + logger.error(EELFLoggerDelegate.errorLogger, "Excetion in setDataFormat", e); + } + styleDecimalNumber.setAlignment(HorizontalAlignment.CENTER); + styleDecimalNumber.setBorderBottom(BorderStyle.THIN); + styleDecimalNumber.setBorderTop(BorderStyle.THIN); + styleDecimalNumber.setBorderLeft(BorderStyle.THIN); + styleDecimalNumber.setBorderRight(BorderStyle.THIN); + styleDecimalNumber.setFillPattern(fillPattern.NO_FILL); + styleDecimalNumber.setFont(fontDefault); + styleDecimalNumber.setDataFormat((short) 0x27);// HSSFDataFormat.getBuiltinFormat("(#,##0.00_);[Red](#,##0.00)")); + styleDecimalNumberTotal.setAlignment(HorizontalAlignment.CENTER); + styleDecimalNumberTotal.setBorderBottom(BorderStyle.THIN); + styleDecimalNumberTotal.setBorderTop(BorderStyle.THIN); + styleDecimalNumberTotal.setBorderLeft(BorderStyle.THIN); + styleDecimalNumberTotal.setBorderRight(BorderStyle.THIN); + styleDecimalNumberTotal.setFillPattern(fillPattern.NO_FILL); + styleDecimalNumberTotal.setFont(fontBold); + styleDecimalNumberTotal.setDataFormat((short) 0x27);// HSSFDataFormat.getBuiltinFormat("(#,##0.00_);[Red](#,##0.00)")); + styleCurrencyDecimalNumber.setAlignment(HorizontalAlignment.CENTER); + styleCurrencyDecimalNumber.setBorderBottom(BorderStyle.THIN); + styleCurrencyDecimalNumber.setBorderTop(BorderStyle.THIN); + styleCurrencyDecimalNumber.setBorderLeft(BorderStyle.THIN); + styleCurrencyDecimalNumber.setBorderRight(BorderStyle.THIN); + styleCurrencyDecimalNumber.setFillPattern(fillPattern.NO_FILL); + styleCurrencyDecimalNumber.setFont(fontDefault); + styleCurrencyDecimalNumber.setDataFormat((short) 8);// HSSFDataFormat.getBuiltinFormat("($#,##0.00_);[Red]($#,##0.00)")); + styleCurrencyDecimalNumberTotal.setAlignment(HorizontalAlignment.CENTER); + styleCurrencyDecimalNumberTotal.setBorderBottom(BorderStyle.THIN); + styleCurrencyDecimalNumberTotal.setBorderTop(BorderStyle.THIN); + styleCurrencyDecimalNumberTotal.setBorderLeft(BorderStyle.THIN); + styleCurrencyDecimalNumberTotal.setBorderRight(BorderStyle.THIN); + styleCurrencyDecimalNumberTotal.setFillPattern(fillPattern.NO_FILL); + styleCurrencyDecimalNumberTotal.setFont(fontBold); + styleCurrencyDecimalNumberTotal.setDataFormat((short) 8);// HSSFDataFormat.getBuiltinFormat("($#,##0.00_);[Red]($#,##0.00)")); + styleCurrencyNumber.setAlignment(HorizontalAlignment.CENTER); + styleCurrencyNumber.setBorderBottom(BorderStyle.THIN); + styleCurrencyNumber.setBorderTop(BorderStyle.THIN); + styleCurrencyNumber.setBorderLeft(BorderStyle.THIN); + styleCurrencyNumber.setBorderRight(BorderStyle.THIN); + styleCurrencyNumber.setFillPattern(fillPattern.NO_FILL); + styleCurrencyNumber.setFont(fontDefault); + styleCurrencyNumber.setDataFormat((short) 6);// HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); + styleCurrencyNumberTotal.setAlignment(HorizontalAlignment.CENTER); + styleCurrencyNumberTotal.setBorderBottom(BorderStyle.THIN); + styleCurrencyNumberTotal.setBorderTop(BorderStyle.THIN); + styleCurrencyNumberTotal.setBorderLeft(BorderStyle.THIN); + styleCurrencyNumberTotal.setBorderRight(BorderStyle.THIN); + styleCurrencyNumberTotal.setFillPattern(fillPattern.NO_FILL); + styleCurrencyNumberTotal.setFont(fontBold); + styleCurrencyNumberTotal.setDataFormat((short) 6);// HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); + styleDate.setAlignment(HorizontalAlignment.CENTER); + styleDate.setBorderBottom(BorderStyle.THIN); + styleDate.setBorderTop(BorderStyle.THIN); + styleDate.setBorderLeft(BorderStyle.THIN); + styleDate.setBorderRight(BorderStyle.THIN); + styleDate.setFillPattern(fillPattern.NO_FILL); + styleDate.setFont(fontDefault); + styleDate.setDataFormat((short) 0xe);// HSSFDataFormat.getBuiltinFormat("m/d/yy")); + styleTotal.setAlignment(HorizontalAlignment.CENTER); + styleTotal.setBorderBottom(BorderStyle.THIN); + styleTotal.setBorderTop(BorderStyle.THIN); + styleTotal.setBorderLeft(BorderStyle.THIN); + styleTotal.setBorderRight(BorderStyle.THIN); + styleTotal.setFillPattern(fillPattern.NO_FILL); + styleTotal.setDataFormat((short) 0x28);// HSSFDataFormat.getBuiltinFormat("(#,##0.00_);[Red](#,##0.00)")); + styleTotal.setFont(fontBold); + styleCurrencyTotal.setAlignment(HorizontalAlignment.CENTER); + styleCurrencyTotal.setBorderBottom(BorderStyle.THIN); + styleCurrencyTotal.setBorderTop(BorderStyle.THIN); + styleCurrencyTotal.setBorderLeft(BorderStyle.THIN); + styleCurrencyTotal.setBorderRight(BorderStyle.THIN); + styleCurrencyTotal.setFillPattern(fillPattern.NO_FILL); + styleCurrencyTotal.setDataFormat((short) 8);// HSSFDataFormat.getBuiltinFormat("($#,##0.00_);[Red]($#,##0.00)")); + styleCurrencyTotal.setFont(fontBold); + styleDefaultTotal.setAlignment(HorizontalAlignment.CENTER); + styleDefaultTotal.setBorderBottom(BorderStyle.THIN); + styleDefaultTotal.setBorderTop(BorderStyle.THIN); + styleDefaultTotal.setBorderLeft(BorderStyle.THIN); + styleDefaultTotal.setBorderRight(BorderStyle.THIN); + styleDefaultTotal.setFillPattern(fillPattern.NO_FILL); + styleDefaultTotal.setDataFormat((short) 0x28); + styleDefaultTotal.setFont(fontBold); + firstPass = true; + HSSFRow row = null; + HSSFCell cell = null; + SimpleDateFormat MMDDYYYYFormat = new SimpleDateFormat("MM/dd/yyyy"); + SimpleDateFormat YYYYMMDDFormat = new SimpleDateFormat("yyyy/MM/dd"); + SimpleDateFormat MONYYYYFormat = new SimpleDateFormat("MMM yyyy"); + SimpleDateFormat MMYYYYFormat = new SimpleDateFormat("MM/yyyy"); + SimpleDateFormat MMMMMDDYYYYFormat = new SimpleDateFormat("MMMMM dd, yyyy"); + SimpleDateFormat YYYYMMDDDASHFormat = new SimpleDateFormat("yyyy-MM-dd"); + SimpleDateFormat timestampFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); + SimpleDateFormat DDMONYYYYFormat = new SimpleDateFormat("dd-MMM-yyyy"); + SimpleDateFormat MONTHYYYYFormat = new SimpleDateFormat("MMMMM, yyyy"); + SimpleDateFormat MMDDYYYYHHMMSSFormat = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss"); + SimpleDateFormat MMDDYYYYHHMMFormat = new SimpleDateFormat("MM/dd/yyyy HH:mm"); + SimpleDateFormat YYYYMMDDHHMMSSFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); + SimpleDateFormat YYYYMMDDHHMMFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm"); + SimpleDateFormat DDMONYYYYHHMMSSFormat = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss"); + SimpleDateFormat DDMONYYYYHHMMFormat = new SimpleDateFormat("dd-MMM-yyyy HH:mm"); + SimpleDateFormat DDMONYYHHMMFormat = new SimpleDateFormat("dd-MMM-yy HH:mm"); + SimpleDateFormat MMDDYYFormat = new SimpleDateFormat("MM/dd/yy"); + SimpleDateFormat MMDDYYHHMMFormat = new SimpleDateFormat("MM/dd/yy HH:mm"); + SimpleDateFormat MMDDYYHHMMSSFormat = new SimpleDateFormat("MM/dd/yy HH:mm:ss"); + SimpleDateFormat MMDDYYYYHHMMZFormat = new SimpleDateFormat("MM/dd/yyyy HH:mm z"); + SimpleDateFormat MMMMMDDYYYYHHMMSS = new SimpleDateFormat("MMMMM-dd-yyyy HH:mm:ss"); + ResultSetMetaData rsmd = null; + CreationHelper createHelper = wb.getCreationHelper(); + + if (nvl(sql_whole).length() > 0 && rr.getReportType().equals(AppConstants.RT_LINEAR)) { + try (Connection conn = ConnectionUtils.getConnection(rr.getDbInfo()); + Statement st = conn.createStatement(); + ResultSet rs = st.executeQuery(sql_whole)) { + System.out.println("************* Map Whole SQL *************"); + System.out.println(sql_whole); + System.out.println("*****************************************"); + rsmd = rs.getMetaData(); + int numberOfColumns = rsmd.getColumnCount(); + HashMap colHash = new HashMap(); + DataRow dr = null; + int j = 0; + int rowCount = 0; + StringBuffer value = new StringBuffer(""); + DataValue dv = new DataValue(); + while (rs.next()) { + rowCount++; + row = sheet.createRow(rowNum); + cellNum = -1; + colHash = new HashMap(); + for (int i = 1; i <= numberOfColumns; i++) { + colHash.put(rsmd.getColumnLabel(i).toUpperCase(), strip.stripHtml(rs.getString(i))); + } + rd.reportDataRows.resetNext(); + dr = rd.reportDataRows.getNext(); + j = 0; + firstPass = false; + for (dr.resetNext(); dr.hasNext(); j++) { + value.delete(0, value.length()); + styleCell = null; + dv = dr.getNext(); + HtmlFormatter htmlFormat = dv.getCellFormatter(); + if ((dr.isRowFormat() && !dv.isCellFormat()) && styles != null) + styleCell = (HSSFCellStyle) styles.get(nvl(dr.getFormatId(), DEFAULT)); + if (htmlFormat != null && dv.getFormatId() != null && styles != null) + styleCell = (HSSFCellStyle) styles.get(nvl(dv.getFormatId(), DEFAULT)); + if(Utils.isRemoveLinkToEligible(nvl((String) colHash.get(dv.getColId().toUpperCase())))) { + value.append(StringUtils.substringBefore(Utils.removeLinkToForDownload(nvl((String) colHash.get(dv.getColId().toUpperCase()))), ",")); + } else { + value.append(nvl((String) colHash.get(dv.getColId().toUpperCase()))); + } + boolean bold = false; + if (dv.isVisible()) { + cellNum += 1; + cell = row.createCell((short) cellNum); + String dataType = (String) (dataTypeMap.get(dv.getColId())); + if (dataType != null && dataType.equals("NUMBER")) { + int zInt = 0; + if (value.toString().equals("null")) { + cell.setCellValue(zInt); + } else { + if ((value.indexOf(".")) != -1) { + if ((value.toString().trim().startsWith("$")) || (value.toString().trim().startsWith("-$"))) { + + String tempDollar = dv.getDisplayValue().trim(); + tempDollar = tempDollar.replaceAll(" ", "").substring(0); + tempDollar = tempDollar.replaceAll("\\$", "").substring(0); + if ((tempDollar.indexOf(",")) != -1) { + tempDollar = tempDollar.replaceAll(",", ""); + } + double tempDoubleDollar = 0.0; + try { + tempDoubleDollar = Double.parseDouble(tempDollar); + if (styleCell != null) { + styleCell.setDataFormat((short) 8);// HSSFDataFormat.getBuiltinFormat("($#,##0.00_);[Red]($#,##0.00)")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleCurrencyDecimalNumber); + cell.setCellValue(tempDoubleDollar); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(tempDollar); + } + } else { + double tempDouble = 0.0; + try { + tempDouble = Double.parseDouble(value.toString()); + if (styleCell != null) { + styleCell.setDataFormat((short) 0x28);// HSSFDataFormat.getBuiltinFormat("(#,##0.00_);[Red](#,##0.00)")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDecimalNumber); + cell.setCellValue(tempDouble); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(value.toString()); + } + } + } else { + if (!(value.equals(""))) { + if ((value.toString().trim().startsWith("$")) || (value.toString().trim().startsWith("-$"))) { + String tempInt = value.toString().trim(); + tempInt = tempInt.replaceAll(" ", "").substring(0); + tempInt = tempInt.replaceAll("\\$", "").substring(0); + if ((tempInt.indexOf(",")) != -1) { + tempInt = tempInt.replaceAll(",", ""); + } + Long tempIntDollar = 0L; + try { + tempIntDollar = Long.parseLong(tempInt); + if (styleCell != null) { + styleCell.setDataFormat((short) 6);// HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleCurrencyNumber); + cell.setCellValue(tempIntDollar); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(tempInt); + } + } else { + String tempStr = value.toString().trim(); + if ((tempStr.indexOf(",")) != -1) { + tempStr = tempStr.replaceAll(",", ""); + } + Long temp = 0L; + + try { + temp = Long.parseLong(tempStr); + if (styleCell != null) { + styleCell.setDataFormat((short) 0x26);// HSSFDataFormat.getBuiltinFormat("(#,##0_);[Red](#,##0)")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleNumber); + cell.setCellValue(temp); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(tempStr); + } + } + } + } + } + } else if ((dataType != null && dataType.equals("DATE")) + || (dv.getDisplayName() != null + && dv.getDisplayName().toLowerCase().endsWith("date")) + || + (dv.getColId() != null && dv.getColId().toLowerCase().endsWith("date")) || + (dv.getColName() != null && dv.getColName().toLowerCase().endsWith("date"))) { + if (styleCell != null) { + styleCell.setDataFormat((short) 0xe);// HSSFDataFormat.getBuiltinFormat("m/d/yy")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDate); + Date date = null; + int flag = 0; + date = MMDDYYHHMMSSFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss")); + flag = 1; + } + if (date == null) + date = MMDDYYHHMMFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy h:mm")); + flag = 1; + } + if (date == null) + date = MMDDYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy")); + flag = 1; + } + if (date == null) + date = MMDDYYYYHHMMSSFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yyyy h:mm:ss")); + flag = 1; + } + if (date == null) + date = MMDDYYYYHHMMFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yyyy h:mm")); + flag = 1; + } + if (date == null) + date = MMDDYYYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yyyy")); + flag = 1; + } + if (date == null) + date = YYYYMMDDFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("yyyy/m/d")); + flag = 1; + } + if (date == null) + date = timestampFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("yyyy-m-d h:mm:ss")); // yyyy-MM-dd + // HH:mm:ss + flag = 1; + } + if (date == null) + date = MONYYYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("mmm yyyy")); + flag = 1; + } + if (date == null) + date = MMYYYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/yyyy")); + flag = 1; + } + if (date == null) + date = MMMMMDDYYYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("mmm/d/yyyy")); + flag = 1; + } + if (date == null) + date = MONTHYYYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("mmm/yyyy")); + flag = 1; + } + if (date == null) + date = YYYYMMDDHHMMSSFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("yyyy/m/d h:mm:ss")); + flag = 1; + } + if (date == null) + date = YYYYMMDDDASHFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("yyyy-m-d")); + flag = 1; + } + if (date == null) + date = YYYYMMDDHHMMFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("yyyy/m/d h:mm")); + flag = 1; + } + if (date == null) + date = DDMONYYYYHHMMSSFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("d-mmm-yyyy h:mm:ss")); + flag = 1; + } + if (date == null) + date = DDMONYYYYHHMMFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("d-mmm-yyyy h:mm")); + flag = 1; + } + if (date == null) + date = DDMONYYHHMMFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("d-mmm-yy h:mm")); + flag = 1; + } + if (date == null) + date = DDMONYYYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("d-mmm-yyyy")); + flag = 1; + } + if (date == null) + date = MMDDYYHHMMSSFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss")); + flag = 1; + } + if (date == null) + date = MMDDYYHHMMFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy h:mm")); + flag = 1; + } + if (date == null) + date = MMDDYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy")); + flag = 1; + } + if (date == null) + date = MMDDYYHHMMFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy h:mm")); + flag = 1; + } + if (date == null) + date = MMDDYYHHMMSSFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss")); + flag = 1; + } + if (date == null) + date = MMDDYYYYHHMMZFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yyyy h:mm")); + flag = 1; + } + if (date == null) + date = MMMMMDDYYYYHHMMSS.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yyyy h:mm")); + flag = 1; + } + + if (date != null) { + cell.setCellValue(HSSFDateUtil.getExcelDate(date)); + try { + String str = cell.getStringCellValue(); + } catch (IllegalStateException ex) { + logger.error(EELFLoggerDelegate.errorLogger, "IllegalStateException occured", + ex); + cell.setCellValue(value.toString()); + } + } else { + cell.setCellValue(value.toString()); + } + + } else if ((dv.getDisplayTotal() != null && dv.getDisplayTotal().equals("SUM(")) + || (dv.getColName() != null && dv.getColName().indexOf("999") != -1)) { + cell = row.createCell((short) cellNum); + int zInt = 0; + if (value.equals("null")) { + cell.setCellValue(zInt); + } else { + + if ((value.indexOf(".")) != -1) { + if ((value.toString().trim().startsWith("$")) || (value.toString().trim().startsWith("-$"))) { + + String tempDollar = value.toString().trim(); + tempDollar = tempDollar.replaceAll(" ", "").substring(0); + tempDollar = tempDollar.replaceAll("\\$", "").substring(0); + if ((tempDollar.indexOf(",")) != -1) { + tempDollar = tempDollar.replaceAll(",", ""); + } + double tempDoubleDollar = 0.0; + try { + tempDoubleDollar = Double.parseDouble(tempDollar); + if (styleCell != null) { + styleCell.setDataFormat((short) 8);// HSSFDataFormat.getBuiltinFormat("($#,##0.00_);[Red]($#,##0.00)")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleCurrencyDecimalNumber); + cell.setCellValue(tempDoubleDollar); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(tempDollar); + } + } else { + String tempDoubleStr = value.toString().trim(); + tempDoubleStr = tempDoubleStr.replaceAll(" ", "").substring(0); + if ((tempDoubleStr.indexOf(",")) != -1) { + tempDoubleStr = tempDoubleStr.replaceAll(",", ""); + } + double tempDouble = 0.0; + try { + tempDouble = Double.parseDouble(tempDoubleStr); + if (styleCell != null) { + styleCell.setDataFormat((short) 0x28);// HSSFDataFormat.getBuiltinFormat("(#,##0.00_);[Red](#,##0.00)")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDecimalNumber); + cell.setCellValue(tempDouble); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(tempDoubleStr); + } + } + } else { + if (!(value.equals(""))) { + if ((value.toString().trim().startsWith("$")) || (value.toString().trim().startsWith("-$"))) { + String tempInt = value.toString().trim(); + tempInt = tempInt.replaceAll(" ", "").substring(0); + tempInt = tempInt.replaceAll("\\$", "").substring(0); + if ((tempInt.indexOf(",")) != -1) { + tempInt = tempInt.replaceAll(",", ""); + } + Long tempIntDollar = 0L; + try { + tempIntDollar = Long.parseLong(tempInt); + if (styleCell != null) { + styleCell.setDataFormat((short) 6);// HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleCurrencyNumber); + cell.setCellValue(tempIntDollar); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(tempInt); + } + } else { + String tempStr = value.toString().trim(); + if ((tempStr.indexOf(",")) != -1) { + tempStr = tempStr.replaceAll(",", ""); + } + Long temp = 0L; + try { + temp = Long.parseLong(tempStr); + if (styleCell != null) { + styleCell.setDataFormat((short) 0x26);// HSSFDataFormat.getBuiltinFormat("(#,##0_);[Red](#,##0)")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleNumber); + cell.setCellValue(temp); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(tempStr); + } + } + } else { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + } + } + } + } else { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(strip.stripHtml(value.toString())); + } + if (cellWidth.size() > cellNum) { + if (((Integer) cellWidth.get(cellNum)).intValue() < dv + .getDisplayValue().length()) + cellWidth.set((cellNum), + (value.length() <= Globals.getMaxCellWidthInExcel()) + ? new Integer(value.length()) + : new Integer(Globals.getMaxCellWidthInExcel())); + } else + cellWidth.add((cellNum), + (value.length() <= Globals.getMaxCellWidthInExcel()) + ? new Integer(value.length()) + : new Integer(Globals.getMaxCellWidthInExcel())); + if (dv.isBold()) { + if ((dv.getDisplayTotal() != null && dv.getDisplayTotal().equals("SUM(")) + || (dv.getColName() != null && dv.getColName().indexOf("999") != -1)) { + if (value != null && (value.toString().trim().startsWith("$")) + || (value.toString().trim().startsWith("-$"))) { + cell.setCellStyle(styleCurrencyTotal); + } else { + cell.setCellStyle(styleTotal); + } + } else { + cell.setCellStyle(styleDefaultTotal); + } + bold = true; + } + if ((dr.isRowFormat() && !dv.isCellFormat()) && styles != null) { + continue; + } + if (htmlFormat != null && dv.getFormatId() != null && bold == false + && styles != null) { + } + } + } + rowNum += 1; + } + int cw = 0; + for (int i = 0; i < cellWidth.size(); i++) { + cw = ((Integer) cellWidth.get(i)).intValue() + 12; + sheet.setColumnWidth((short) (i), (short) ((cw * 8) / ((double) 1 / 20))); + } + if (rd.reportDataTotalRow != null) { + row = sheet.createRow(rowNum); + cellNum = -1; + rd.reportTotalRowHeaderCols.resetNext(); + cellNum += 1; + RowHeaderCol rhc = rd.reportTotalRowHeaderCols.getNext(); + RowHeader rh = rhc.getRowHeader(0); + row.createCell((short) cellNum).setCellValue(strip.stripHtml(rh.getRowTitle())); + row.getCell((short) cellNum).setCellStyle(styleDefaultTotal); + rd.reportDataTotalRow.resetNext(); + DataRow drTotal = rd.reportDataTotalRow.getNext(); + drTotal.resetNext(); + drTotal.getNext(); + for (; drTotal.hasNext();) { + cellNum += 1; + cell = row.createCell((short) cellNum); + dv = drTotal.getNext(); + String value1 = dv.getDisplayValue(); + cell.setCellValue(value1); + boolean bold = false; + if (dv.isBold()) { + if ((dv.getDisplayTotal() != null && dv.getDisplayTotal().equals("SUM(")) + || (dv.getColName() != null && dv.getColName().indexOf("999") != -1)) { + if (value1 != null && (value1.trim().startsWith("$")) + || (value.toString().trim().startsWith("-$"))) { + cell.setCellStyle(styleCurrencyTotal); + } else { + cell.setCellStyle(styleTotal); + } + } else { + cell.setCellStyle(styleDefaultTotal); + } + bold = true; + } + } + } + } catch (SQLException ex) { + logger.error(EELFLoggerDelegate.errorLogger, "SQLException occured ", ex); + throw new RaptorException(ex); + } catch (ReportSQLException ex) { + logger.error(EELFLoggerDelegate.errorLogger, "ReportSQLException occured ", ex); + throw new RaptorException(ex); + } catch (Exception ex) { + logger.error(EELFLoggerDelegate.errorLogger, "Exception occured ", ex); + if (!(ex.getCause() instanceof java.net.SocketException)) + throw new RaptorException(ex); + } + } else { + if (rr.getReportType().equals(AppConstants.RT_LINEAR)) { + StringBuffer value = new StringBuffer(); + int rowCount = 0; + DataRow dr = new DataRow(); + for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext();) { + dr = rd.reportDataRows.getNext(); + rowCount++; + row = sheet.createRow(rowNum); + cellNum = -1; + if (rr.getReportType().equals(AppConstants.RT_LINEAR) && rd.reportTotalRowHeaderCols != null) { + rd.reportRowHeaderCols.resetNext(0); + if (rd.reportTotalRowHeaderCols != null) { + } + } else { + rd.reportRowHeaderCols.resetNext(0); + } + for (; rd.reportRowHeaderCols.hasNext();) { + cellNum += 1; + RowHeaderCol rhc = rd.reportRowHeaderCols.getNext(); + if (firstPass) + rhc.resetNext(); + RowHeader rh = rhc.getNext(); + row.createCell((short) cellNum).setCellValue(strip.stripHtml(rh.getRowTitle())); + row.getCell((short) cellNum).setCellStyle(styleDefault); + if (cellWidth.size() > 0) { + if (((Integer) cellWidth.get(cellNum)).intValue() < rh.getRowTitle() + .length()) + cellWidth.set(cellNum, new Integer(rh.getRowTitle().length())); + } else + cellWidth.add(cellNum, new Integer(rh.getRowTitle().length())); + } + firstPass = false; + int j = 0; + DataValue dv = new DataValue(); + for (dr.resetNext(); dr.hasNext(); j++) { + value.delete(0, value.length()); + dv = dr.getNext(); + styleCell = null; + boolean bold = false; + if(Utils.isRemoveLinkToEligible(dv.getDisplayValue())) { + value.append(StringUtils.substringBefore(Utils.removeLinkToForDownload(nvl(dv.getDisplayValue())), ",")); + } else { + value.append(dv.getDisplayValue()); + } + HtmlFormatter htmlFormat = dv.getCellFormatter(); + if ((dr.isRowFormat() && !dv.isCellFormat()) && styles != null) + styleCell = (HSSFCellStyle) styles.get(nvl(dr.getFormatId(), DEFAULT)); + if (htmlFormat != null && dv.getFormatId() != null && styles != null) + styleCell = (HSSFCellStyle) styles.get(nvl(dv.getFormatId(), DEFAULT)); + if (dv.isVisible()) { + cellNum += 1; + cell = row.createCell((short) cellNum); + String dataType = (String) (dataTypeMap.get(dv.getColId())); + if (dataType != null && dataType.equals("NUMBER")) { + int zInt = 0; + if (value.equals("null")) { + cell.setCellValue(zInt); + } else { + if ((value.indexOf(".")) != -1) { + if ((value.toString().trim().startsWith("$")) || (value.toString().trim().startsWith("-$"))) { + String tempDollar = value.toString().trim(); + tempDollar = tempDollar.replaceAll(" ", "").substring(0); + tempDollar = tempDollar.replaceAll("\\$", "").substring(0); + if ((tempDollar.indexOf(",")) != -1) { + tempDollar = tempDollar.replaceAll(",", ""); + } + double tempDoubleDollar = 0.0; + try { + tempDoubleDollar = Double.parseDouble(tempDollar); + if (styleCell != null) { + styleCell.setDataFormat((short) 8);// HSSFDataFormat.getBuiltinFormat("($#,##0.00_);[Red]($#,##0.00)")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleCurrencyDecimalNumber); + cell.setCellValue(tempDoubleDollar); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(tempDollar); + } + } else { + double tempDouble = 0.0; + try { + tempDouble = Double.parseDouble(value.toString()); + if (styleCell != null) { + styleCell.setDataFormat((short) 0x28);// HSSFDataFormat.getBuiltinFormat("(#,##0.00_);[Red](#,##0.00)")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDecimalNumber); + cell.setCellValue(tempDouble); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(value.toString()); + } + } + } else { + if (!(value.equals(""))) { + if ((value.toString().trim().startsWith("$")) || (value.toString().trim().startsWith("-$"))) { + String tempInt = value.toString().trim(); + tempInt = tempInt.replaceAll(" ", "").substring(0); + tempInt = tempInt.replaceAll("\\$", "").substring(0); + if ((tempInt.indexOf(",")) != -1) { + tempInt = tempInt.replaceAll(",", ""); + } + Long tempIntDollar = 0L; + try { + tempIntDollar = Long.parseLong(tempInt); + if (styleCell != null) { + styleCell.setDataFormat((short) 6);// HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleCurrencyNumber); + cell.setCellValue(tempIntDollar); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(tempInt); + } + } else { + String tempStr = value.toString().trim(); + if ((tempStr.indexOf(",")) != -1) { + tempStr = tempStr.replaceAll(",", ""); + } + Long temp = 0L; + try { + temp = Long.parseLong(tempStr); + if (styleCell != null) { + styleCell.setDataFormat((short) 0x26);// HSSFDataFormat.getBuiltinFormat("(#,##0_);[Red](#,##0)")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleNumber); + cell.setCellValue(temp); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(tempStr); + } + } + } + } + } + } else if ((dataType != null && dataType.equals("DATE")) + || (dv.getDisplayName() != null + && dv.getDisplayName().toLowerCase().endsWith("date")) + || + (dv.getColId() != null && dv.getColId().toLowerCase().endsWith("date")) || + (dv.getColName() != null && dv.getColName().toLowerCase().endsWith("date"))) { + + if (styleCell != null) { + styleCell.setDataFormat((short) 0xe); // HSSFDataFormat.getBuiltinFormat("m/d/yy")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDate); + Date date = null; + int flag = 0; + date = MMDDYYHHMMSSFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss")); + flag = 1; + } + if (date == null) + date = MMDDYYHHMMFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy h:mm")); + flag = 1; + } + if (date == null) + date = MMDDYYYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yyyy")); + flag = 1; + } + if (date == null) + date = MMDDYYYYHHMMSSFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yyyy h:mm:ss")); + flag = 1; + } + if (date == null) + date = MMDDYYYYHHMMFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yyyy h:mm")); + flag = 1; + } + if (date == null) + date = MMDDYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy")); + flag = 1; + } + if (date == null) + date = YYYYMMDDFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("yyyy/m/d")); + flag = 1; + } + if (date == null) + date = timestampFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("yyyy-m-d h:mm:ss")); // yyyy-MM-dd + // HH:mm:ss + flag = 1; + } + if (date == null) + date = MONYYYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("mmm yyyy")); + flag = 1; + } + if (date == null) + date = MMYYYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/yyyy")); + flag = 1; + } + if (date == null) + date = MMMMMDDYYYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("mmm/d/yyyy")); + flag = 1; + } + if (date == null) + date = MONTHYYYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("mmm/yyyy")); + flag = 1; + } + if (date == null) + date = YYYYMMDDHHMMSSFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("yyyy/m/d h:mm:ss")); + flag = 1; + } + if (date == null) + date = YYYYMMDDDASHFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("yyyy-m-d")); + flag = 1; + } + if (date == null) + date = YYYYMMDDHHMMFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("yyyy/m/d h:mm")); + flag = 1; + } + if (date == null) + date = DDMONYYYYHHMMSSFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("d-mmm-yyyy h:mm:ss")); + flag = 1; + } + if (date == null) + date = DDMONYYYYHHMMFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("d-mmm-yyyy h:mm")); + flag = 1; + } + if (date == null) + date = DDMONYYHHMMFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("d-mmm-yy h:mm")); + flag = 1; + } + if (date == null) + date = DDMONYYYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("d-mmm-yyyy")); + flag = 1; + } + if (date == null) + date = MMDDYYHHMMSSFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss")); + flag = 1; + } + if (date == null) + date = MMDDYYHHMMFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy h:mm")); + flag = 1; + } + if (date == null) + date = MMDDYYFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy")); + flag = 1; + } + if (date == null) + date = MMDDYYHHMMFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy h:mm")); + flag = 1; + } + if (date == null) + date = MMDDYYHHMMSSFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss")); + flag = 1; + } + if (date == null) + date = MMDDYYYYHHMMZFormat.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yyyy h:mm")); + flag = 1; + } + if (date == null) + date = MMMMMDDYYYYHHMMSS.parse(value.toString(), new ParsePosition(0)); + if (date != null && flag == 0) { + cell.getCellStyle().setDataFormat( + createHelper.createDataFormat().getFormat("m/d/yyyy h:mm")); + flag = 1; + } + if (date != null) { + cell.setCellValue(HSSFDateUtil.getExcelDate(date)); + cell.setCellValue(value.toString()); + } else { + cell.setCellValue(value.toString()); + } + } else if ((dv.getDisplayTotal() != null && dv.getDisplayTotal().equals("SUM(")) + || (dv.getColName() != null && dv.getColName().indexOf("999") != -1)) { + cell = row.createCell((short) cellNum); + int zInt = 0; + if (value.equals("null")) { + cell.setCellValue(zInt); + } else { + + if ((value.indexOf(".")) != -1) { + if ((value.toString().startsWith("$")) || (value.toString().trim().startsWith("-$"))) { + String tempDollar = value.toString().trim(); + tempDollar = tempDollar.replaceAll(" ", "").substring(0); + tempDollar = tempDollar.replaceAll("\\$", "").substring(0); + if ((tempDollar.indexOf(",")) != -1) { + tempDollar = tempDollar.replaceAll(",", ""); + } + double tempDoubleDollar = 0.0; + try { + tempDoubleDollar = Double.parseDouble(tempDollar); + if (styleCell != null) { + styleCell.setDataFormat((short) 8);// HSSFDataFormat.getBuiltinFormat("($#,##0.00_);[Red]($#,##0.00)")); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleCurrencyDecimalNumber); + cell.setCellValue(tempDoubleDollar); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(tempDollar); + } + } else { + String tempDoubleStr = value.toString().trim(); + tempDoubleStr = tempDoubleStr.replaceAll(" ", "").substring(0); + if ((tempDoubleStr.indexOf(",")) != -1) { + tempDoubleStr = tempDoubleStr.replaceAll(",", ""); + } + double tempDouble = 0.0; + try { + tempDouble = Double.parseDouble(tempDoubleStr); + if (styleCell != null) { + styleCell.setDataFormat((short) 0x28); // for decimal + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDecimalNumber); + cell.setCellValue(tempDouble); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(tempDoubleStr); + } + } + } else { + if (!(value.equals(""))) { + if ((value.toString().trim().startsWith("$")) || (value.toString().trim().startsWith("-$"))) { + String tempInt = value.toString().trim(); + tempInt = tempInt.replaceAll(" ", "").substring(0); + tempInt = tempInt.replaceAll("\\$", "").substring(0); + if ((tempInt.indexOf(",")) != -1) { + tempInt = tempInt.replaceAll(",", ""); + } + Long tempIntDollar = 0L; + try { + tempIntDollar = Long.parseLong(tempInt); + if (styleCell != null) { + styleCell.setDataFormat((short) 6); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleCurrencyNumber); + cell.setCellValue(tempIntDollar); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(tempInt); + } + } else { + // styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); + String tempStr = value.toString().trim(); + if ((tempStr.indexOf(",")) != -1) { + tempStr = tempStr.replaceAll(",", ""); + } + Long temp = 0L; + + try { + temp = Long.parseLong(tempStr); + if (styleCell != null) { + styleCell.setDataFormat((short) 0x26); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleNumber); + cell.setCellValue(temp); + } catch (NumberFormatException ne) { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(tempStr); + } + } + } else { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + } + } + } + } else { + if (styleCell != null) { + styleCell.setWrapText(true); + cell.setCellStyle(styleCell); + } else + cell.setCellStyle(styleDefault); + cell.setCellValue(strip.stripHtml(value.toString())); + } + if (cellWidth.size() > cellNum) { + if (((Integer) cellWidth.get(cellNum)).intValue() < dv + .getDisplayValue().length()) + cellWidth.set((cellNum), + (value.length() <= Globals.getMaxCellWidthInExcel()) + ? new Integer(value.length()) + : new Integer(Globals.getMaxCellWidthInExcel())); + } else + cellWidth.add((cellNum), + (value.length() <= Globals.getMaxCellWidthInExcel()) + ? new Integer(value.length()) + : new Integer(Globals.getMaxCellWidthInExcel())); + if (dv.isBold()) { + if ((dv.getDisplayTotal() != null && dv.getDisplayTotal().equals("SUM(")) + || (dv.getColName() != null && dv.getColName().indexOf("999") != -1)) { + if (value != null && (value.toString().trim().startsWith("$")) + || (value.toString().trim().startsWith("-$"))) { + cell.setCellStyle(styleCurrencyTotal); + } else { + cell.setCellStyle(styleTotal); + } + } else { + cell.setCellStyle(styleDefaultTotal); + } + bold = true; + } + if ((dr.isRowFormat() && !dv.isCellFormat()) && styles != null) { + continue; + } + if (htmlFormat != null && dv.getFormatId() != null && bold == false + && styles != null) { + } + } + } + rowNum += 1; + int cw = 0; + for (int i = 0; i < cellWidth.size(); i++) { + cw = ((Integer) cellWidth.get(i)).intValue() + 12; + sheet.setColumnWidth((short) (i), (short) ((cw * 8) / ((double) 1 / 20))); + + } + + } + // To Display Total Values for Linear report + if (rd.reportDataTotalRow != null) { + row = sheet.createRow(rowNum); + cellNum = -1; + rd.reportTotalRowHeaderCols.resetNext(); + cellNum += 1; + RowHeaderCol rhc = rd.reportTotalRowHeaderCols.getNext(); + RowHeader rh = rhc.getRowHeader(0); + row.createCell((short) cellNum).setCellValue(strip.stripHtml(rh.getRowTitle())); + row.getCell((short) cellNum).setCellStyle(styleDefaultTotal); + rd.reportDataTotalRow.resetNext(); + DataRow drTotal = rd.reportDataTotalRow.getNext(); + drTotal.resetNext(); + drTotal.getNext(); + DataValue dv = new DataValue(); + for (; drTotal.hasNext();) { + cellNum += 1; + cell = row.createCell((short) cellNum); + dv = drTotal.getNext(); + String value1 = dv.getDisplayValue(); + cell.setCellValue(value1); + boolean bold = false; + if (dv.isBold()) { + if ((dv.getDisplayTotal() != null && dv.getDisplayTotal().equals("SUM(")) + || (dv.getColName() != null && dv.getColName().indexOf("999") != -1)) { + if (value1 != null && (value1.trim().startsWith("$")) + || (value1.trim().startsWith("-$"))) { + cell.setCellStyle(styleCurrencyTotal); + } else { + cell.setCellStyle(styleTotal); + } + } else { + cell.setCellStyle(styleDefaultTotal); + } + bold = true; + } + } + } + } else if (rr.getReportType().equals(AppConstants.RT_CROSSTAB)) { // Linear + int rowCount = 0; + List l = rd.getReportDataList(); + boolean first = true; + for (int dataRow = 0; dataRow < l.size(); dataRow++) { + DataRow dr = (DataRow) l.get(dataRow); + row = sheet.createRow(rowNum); + cellNum = -1; + first = true; + Vector<DataValue> rowNames = dr.getRowValues(); + for (dr.resetNext(); dr.hasNext(); rowCount++) { + if (first) { + if (rowNames != null) { + for (int i = 0; i < rowNames.size(); i++) { + DataValue dv = rowNames.get(i); + cellNum += 1; + row.createCell((short) cellNum).setCellValue(strip.stripHtml(dv.getDisplayValue())); + row.getCell((short) cellNum).setCellStyle(styleDefault); + } + } + } + first = false; + DataValue dv = dr.getNext(); + if (dv.isVisible()) { + String value = dv.getDisplayValue(); + if (value.indexOf("|#") != -1) + value = value.substring(0, value.indexOf("|")); + + if (dr.isRowFormat() || nvl(dv.getFormatId()).length() > 0) { + cellNum += 1; + row.createCell((short) cellNum).setCellValue(strip.stripHtml(dv.getDisplayValue())); + if (nvl(dv.getFormatId()).length() > 0) + row.getCell((short) cellNum) + .setCellStyle((HSSFCellStyle) styles.get(nvl(dv.getFormatId(), DEFAULT))); + else + row.setRowStyle((HSSFCellStyle) styles.get(nvl(dr.getFormatId(), DEFAULT))); + } else { + cellNum += 1; + row.createCell((short) cellNum).setCellValue(strip.stripHtml(value)); + row.getCell((short) cellNum).setCellStyle(styleDefault); + } // end + value = dv.getDisplayValue(); + if (value.indexOf("|#") != -1) { + String color = value.substring(value.indexOf("|") + 1); + if (color.equals("#FF0000")) + row.getCell((short) cellNum).setCellStyle((HSSFCellStyle) styles.get(RED)); + else if (color.equals("#008000")) + row.getCell((short) cellNum).setCellStyle((HSSFCellStyle) styles.get(GREEN)); + else if (color.equals("#FFFF00")) + row.getCell((short) cellNum).setCellStyle((HSSFCellStyle) styles.get(YELLOW)); + else { + row.getCell((short) cellNum).setCellStyle((HSSFCellStyle) styles.get(DEFAULT)); + } + } + } + } + rowNum += 1; + int cw = 0; + for (int i = 0; i < cellWidth.size(); i++) { + cw = ((Integer) cellWidth.get(i)).intValue() + 12; + sheet.setColumnWidth((short) (i), (short) ((cw * 8) / ((double) 1 / 20))); + } + } + } + } + String footer = (String) session.getAttribute("FOOTER_" + index); + if (nvl(footer).length() > 0) { + footer = Utils.replaceInString(footer, "<BR/>", " "); + footer = Utils.replaceInString(footer, "<br/>", " "); + footer = Utils.replaceInString(footer, "<br>", " "); + footer = strip.stripHtml(nvl(footer).trim()); + row = sheet.createRow(rowNum); + cellNum = 0; + row.createCell((short) cellNum).setCellValue(footer); + sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, (short) cellNum, (short) (columnRows))); + rowNum += 1; + } + if (Globals.getShowDisclaimer() && !Globals.disclaimerPositionedTopInCSVExcel()) { + + rowNum += 1; + row = sheet.createRow(rowNum); + cellNum = 0; + String disclaimer = Globals.getFooterFirstLine() + " " + Globals.getFooterSecondLine(); + row.createCell((short) cellNum).setCellValue(disclaimer); + sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, (short) cellNum, (short) (columnRows))); + rowNum += 1; + } + logger.debug(EELFLoggerDelegate.debugLogger, ("##### Heap utilization statistics [MB] #####")); + logger.debug(EELFLoggerDelegate.debugLogger, ("Used Memory:" + + (runtime.maxMemory() - runtime.freeMemory()) / mb)); + logger.debug(EELFLoggerDelegate.debugLogger, ("Free Memory:" + + runtime.freeMemory() / mb)); + logger.debug(EELFLoggerDelegate.debugLogger, ("Total Memory:" + runtime.totalMemory() / mb)); + logger.debug(EELFLoggerDelegate.debugLogger, ("Max Memory:" + runtime.maxMemory() / mb)); + return returnValue; + } + + private void paintExcelParamsXls(final HSSFWorkbook wb, int rowNum, final int col, final List paramsList, + final String customizedParamInfo, final HSSFSheet sheet, final String reportTitle, final String reportDescr) + throws IOException { + int cellNum = 0; + HSSFRow row = null; + short s1 = 0; + short s2 = (short) 1; + HSSFCellStyle styleName = wb.createCellStyle(); + styleName.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex()); + styleName.setAlignment(HorizontalAlignment.CENTER); + styleName.setBorderBottom(BorderStyle.THIN); + styleName.setBorderTop(BorderStyle.THIN); + styleName.setBorderRight(BorderStyle.THIN); + styleName.setBorderLeft(BorderStyle.THIN); + styleName.setDataFormat((short) 0); + HSSFFont font = wb.createFont(); + font.setFontHeight((short) (FONT_SIZE / 0.05)); + font.setFontName(FONT_TAHOMA); + font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); + font.setBold(true); + styleName.setFont(font); + HSSFFont fontDefault = wb.createFont(); + fontDefault.setColor((short) HSSFFont.COLOR_NORMAL); + fontDefault.setFontHeight((short) (FONT_SIZE / 0.05)); + fontDefault.setFontName(FONT_TAHOMA); + fontDefault.setItalic(true); + HSSFCellStyle styleValue = wb.createCellStyle(); + styleValue.setDataFormat((short) 0); + styleValue.setAlignment(HorizontalAlignment.CENTER); + styleValue.setBorderBottom(BorderStyle.THIN); + styleValue.setBorderTop(BorderStyle.THIN); + styleValue.setBorderLeft(BorderStyle.THIN); + styleValue.setBorderRight(BorderStyle.THIN); + // styleValue.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex()); + styleValue.setFillPattern(fillPattern.NO_FILL); + styleValue.setFont(fontDefault); + HSSFCell cell = null; + HSSFCellStyle styleDescription = wb.createCellStyle(); + styleDescription.setAlignment(HorizontalAlignment.CENTER); + + HSSFFont fontDescr = wb.createFont(); + fontDescr.setFontHeight((short) (FONT_SIZE / 0.05)); // 14 + fontDescr.setFontName(FONT_TAHOMA); + fontDescr.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); + fontDescr.setBold(true); + styleDescription.setFont(font); + HSSFCell cellDescr = null; + int paramSeq = 0; + Header header = sheet.getHeader(); + StringBuilder strBuf = new StringBuilder(); + if (!Globals.customizeFormFieldInfo() || customizedParamInfo.length() <= 0) { + for (Iterator iter = paramsList.iterator(); iter.hasNext();) { + IdNameValue value = (IdNameValue) iter.next(); + if (nvl(value.getId()).trim().length() > 0 && (!nvl(value.getId()).trim().equals("BLANK"))) { + paramSeq += 1; + if (paramSeq <= 1) { + row = sheet.createRow(++rowNum); + cell = row.createCell((short) 0); + sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, s1, s2)); + cellDescr = row.createCell((short) 0); + cellDescr.setCellValue(RUNTIME_PARAMETERS); + cellDescr.setCellStyle(styleDescription); + strBuf.append(reportTitle + "\n"); + } + row = sheet.createRow(++rowNum); + cellNum = 0; + cell = row.createCell((short) cellNum); + cell.setCellValue(value.getId()); + cell.setCellStyle(styleName); + cellNum += 1; + cell = row.createCell((short) cellNum); + cell.setCellValue(value.getName().replaceAll("~", ",")); + cell.setCellStyle(styleValue); + } + } // for + } else { + strBuf.append(reportTitle + "\n"); + Document document = new Document(); + document.open(); + HTMLWorker worker = new HTMLWorker(document); + StyleSheet style = new StyleSheet(); + style.loadTagStyle("body", "leading", "16,0"); + ArrayList p = HTMLWorker.parseToList(new StringReader(customizedParamInfo), style); + String name = ""; + String token = ""; + String value = ""; + String s = ""; + PdfPTable pdfTable = null; + for (int k = 0; k < p.size(); ++k) { + if (p.get(k) instanceof Paragraph) + s = ((Paragraph) p.get(k)).toString(); + else { + pdfTable = ((PdfPTable) p.get(k)); + } + + s = s.replaceAll(",", "|"); + s = s.replaceAll("~", ","); + if (s.indexOf(":") != -1) { + row = sheet.createRow(++rowNum); + cell = row.createCell((short) 0); + sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, s1, s2)); + cellDescr = row.createCell((short) 0); + cellDescr.setCellValue(RUNTIME_PARAMETERS); + cellDescr.setCellStyle(styleDescription); + StringTokenizer st = new StringTokenizer(s.trim(), "|"); + while (st.hasMoreTokens()) { + token = st.nextToken(); + token = token.trim(); + if (!(token.trim().equals("|") || token.trim().equals("]]") || token.trim().equals("]") + || token.trim().equals("["))) { + if (token.endsWith(":")) { + name = token; + name = name.substring(0, name.length() - 1); + if (name.startsWith("[")) + name = name.substring(1); + value = st.nextToken(); + if (nvl(value).endsWith("]")) + value = nvl(value).substring(0, nvl(value).length() - 1); + } + if (name != null && name.trim().length() > 0) { + row = sheet.createRow((short) ++rowNum); + cellNum = 0; + cell = row.createCell((short) cellNum); + cell.setCellValue(name.trim()); + cell.setCellStyle(styleName); + cellNum += 1; + cell = row.createCell((short) cellNum); + cell.setCellValue(value.trim()); + cell.setCellStyle(styleValue); + } + if (name != null && (sheet.getColumnWidth((short) 0) < (short) name.trim().length())) { + sheet.setColumnWidth((short) 0, (short) name.trim().length()); + } + if (sheet.getColumnWidth((short) 1) < (short) value.trim().length()) { + sheet.setColumnWidth((short) 1, (short) value.trim().length()); + } + name = ""; + value = ""; + } + try { + SimpleDateFormat oracleDateFormat = new SimpleDateFormat("MM/dd/yyyy kk:mm:ss"); + Date sysdate = oracleDateFormat.parse(ReportLoader.getSystemDateTime()); + SimpleDateFormat dtimestamp = new SimpleDateFormat(Globals.getScheduleDatePattern()); + row = sheet.createRow((short) ++rowNum); + cellNum = 0; + cell = row.createCell((short) cellNum); + cell.setCellValue("Report Date/Time"); + cell.setCellStyle(styleName); + cellNum += 1; + cell = row.createCell((short) cellNum); + cell.setCellValue(dtimestamp.format(sysdate) + " " + Globals.getTimeZone()); + cell.setCellStyle(styleValue); + } catch (Exception ex) { + logger.error(EELFLoggerDelegate.errorLogger, "Excetion in paintExcelParams", ex); + } + } + } + + } // if + Iterator iterCheck = paramsList.iterator(); + if (iterCheck.hasNext()) { + rowNum += 2; + row = sheet.createRow(rowNum); + } + header.setCenter(HSSFHeader.font(FONT_TAHOMA, "") + HSSFHeader.fontSize((short) 9) + " " + strBuf.toString()); + Footer footer = sheet.getFooter(); + footer.setLeft(HSSFFooter.font(FONT_TAHOMA, "") + HSSFFooter.fontSize((short) 9) + "Page " + HSSFFooter.page() + + " of " + HSSFFooter.numPages()); + footer.setCenter( + HSSFFooter.font(FONT_TAHOMA, "") + HSSFFooter.fontSize((short) 9) + Globals.getFooterFirstLine() + + "\n" + Globals.getFooterSecondLine()); + } + } + + private void paintExcelFooterXls(HSSFWorkbook wb, int rowNum, int col, HSSFSheet sheet) { + logger.debug(EELFLoggerDelegate.debugLogger, ("excel footer")); + Footer footer = sheet.getFooter(); + footer.setLeft(HSSFFooter.font(FONT_TAHOMA, "") + HSSFFooter.fontSize((short) FONT_FOOTER_SIZE) + "Page " + + HSSFFooter.page() + + " of " + HSSFFooter.numPages()); + footer.setCenter(HSSFFooter.font(FONT_TAHOMA, "") + HSSFFooter.fontSize((short) FONT_FOOTER_SIZE) + + Globals.getFooterFirstLine() + "\n" + Globals.getFooterSecondLine()); + + logger.debug(EELFLoggerDelegate.debugLogger, ("Done")); + } + +}
\ No newline at end of file |