/*
 * ============LICENSE_START==========================================
 * ONAP Portal SDK
 * ===================================================================
 * Copyright © 2017 AT&T Intellectual Property. All rights reserved.
 * ===================================================================
 *
 * Unless otherwise specified, all software contained herein is licensed
 * under the Apache License, Version 2.0 (the "License");
 * you may not use this software except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *             http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * Unless otherwise specified, all documentation contained herein is licensed
 * under the Creative Commons License, Attribution 4.0 Intl. (the "License");
 * you may not use this documentation except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *             https://creativecommons.org/licenses/by/4.0/
 *
 * Unless required by applicable law or agreed to in writing, documentation
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * ============LICENSE_END============================================
 *
 *
 */
/* ===========================================================================================
 * This class is part of <I>RAPTOR (Rapid Application Programming Tool for OLAP Reporting)</I>
 * Raptor : This tool is used to generate different kinds of reports with lot of utilities
 * ===========================================================================================
 *
 * -------------------------------------------------------------------------------------------
 * ReportHandler.java - This class is used to generate reports in Excel using POI and also to
 * create ReportRuntime and ReportDefinition object using report id.
 * -------------------------------------------------------------------------------------------
 *
 *
 * Changes
 * -------
 * 18-Aug-2009 : Version 8.5.1 (Sundar);<UL><LI> request Object is passed to prevent caching user/roles - Datamining/Hosting. </LI></UL>
 * 14-Jul-2009 : Version 8.4 (Sundar); <UL><LI> Signature for generating excel method has been changed to add the report name as sheet name. </LI>
 *                                     <LI> Dashboard reports can be downloaded with each report as a separate sheet. </LI>
 *                                     </UL>
 * 08-Jun-2009 : Version 8.3 (Sundar); <UL><LI> Short datatype is replaced with default integer datatype to create
 *               row as short is not expoting more than 32768 rows. </LI></UL>
 *
 */
package org.onap.portalsdk.analytics.model;

import com.lowagie.text.Document;
import com.lowagie.text.Paragraph;
import com.lowagie.text.html.simpleparser.HTMLWorker;
import com.lowagie.text.html.simpleparser.StyleSheet;
import com.lowagie.text.pdf.PdfPTable;

import java.awt.Font;
import java.io.BufferedInputStream;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.io.StringReader;
import java.io.UnsupportedEncodingException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.StringTokenizer;
import java.util.TreeMap;
import java.util.Vector;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Future;
import java.util.concurrent.ScheduledThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
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.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.ss.usermodel.Header;
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.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbookType;
import org.onap.portalsdk.analytics.controller.ErrorHandler;
import org.onap.portalsdk.analytics.error.RaptorException;
import org.onap.portalsdk.analytics.error.ReportSQLException;
import org.onap.portalsdk.analytics.model.base.IdNameValue;
import org.onap.portalsdk.analytics.model.definition.ReportDefinition;
import org.onap.portalsdk.analytics.model.runtime.ReportRuntime;
import org.onap.portalsdk.analytics.system.AppUtils;
import org.onap.portalsdk.analytics.system.ConnectionUtils;
import org.onap.portalsdk.analytics.system.ExecuteQuery;
import org.onap.portalsdk.analytics.system.Globals;
import org.onap.portalsdk.analytics.util.AppConstants;
import org.onap.portalsdk.analytics.util.DataSet;
import org.onap.portalsdk.analytics.util.ExcelColorDef;
import org.onap.portalsdk.analytics.util.HtmlStripper;
import org.onap.portalsdk.analytics.util.Log;
import org.onap.portalsdk.analytics.util.Utils;
import org.onap.portalsdk.analytics.view.ColumnHeader;
import org.onap.portalsdk.analytics.view.ColumnHeaderRow;
import org.onap.portalsdk.analytics.view.DataRow;
import org.onap.portalsdk.analytics.view.DataValue;
import org.onap.portalsdk.analytics.view.HtmlFormatter;
import org.onap.portalsdk.analytics.view.ReportData;
import org.onap.portalsdk.analytics.view.RowHeader;
import org.onap.portalsdk.analytics.view.RowHeaderCol;
import org.onap.portalsdk.analytics.xmlobj.DataColumnType;
import org.onap.portalsdk.analytics.xmlobj.DataSourceType;
import org.onap.portalsdk.analytics.xmlobj.FormatList;
import org.onap.portalsdk.analytics.xmlobj.FormatType;
import org.onap.portalsdk.analytics.xmlobj.Reports;
import org.onap.portalsdk.analytics.xmlobj.SemaphoreList;
import org.onap.portalsdk.analytics.xmlobj.SemaphoreType;
import org.onap.portalsdk.core.logging.logic.EELFLoggerDelegate;
import org.owasp.esapi.ESAPI;

public class ReportHandler extends org.onap.portalsdk.analytics.RaptorObject {

    private static final EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(ReportHandler.class);

    public ReportHandler() {}

    private String sheetName = "";
    private static final String XML_ENCODING = "UTF-8";
    private static final int FONT_SIZE = 10;
    private static final int FONT_HEADER_TITLE_SIZE = 12;
    private static final int FONT_HEADER_DESCR_SIZE = 9;
    private static final int FONT_FOOTER_SIZE = 9;
    private static final String DEFAULT = "default";
    private static final String YELLOW = "yellow";
    private static final String GREEN = "green";
    private static final String RED = "red";
    private static final String RUNTIME_PARAMETERS = "Run-time Parameters";
    private static final String FONT_TAHOMA = "Tahoma";
    FillPatternType fillPattern = null;

    private HashMap loadStyles(ReportRuntime rr, XSSFWorkbook wb) {
    	XSSFCellStyle styleDefault = wb.createCellStyle();
    	XSSFFont 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);

        XSSFCellStyle styleRed = wb.createCellStyle();
        styleRed.cloneStyleFrom(styleDefault);
        styleRed.setFillForegroundColor((short) HSSFColor.RED.index);
        styleRed.setFillPattern(fillPattern.SOLID_FOREGROUND);
        XSSFFont fontRed = wb.createFont();
        fontRed.setColor((short) HSSFColor.WHITE.index);
        fontRed.setFontHeight((short) (FONT_SIZE / 0.05));
        fontRed.setFontName(FONT_TAHOMA);
        styleRed.setFont(fontRed);

        XSSFCellStyle styleYellow = wb.createCellStyle();
        styleYellow.cloneStyleFrom(styleDefault);
        styleYellow.setFillForegroundColor((short) HSSFColor.YELLOW.index);
        styleYellow.setFillPattern(fillPattern.SOLID_FOREGROUND);
        XSSFFont fontYellow = wb.createFont();
        fontYellow.setColor((short) HSSFColor.BLACK.index);
        fontYellow.setFontHeight((short) (FONT_SIZE / 0.05));
        fontYellow.setFontName(FONT_TAHOMA);
        styleYellow.setFont(fontYellow);

        XSSFCellStyle styleGreen = wb.createCellStyle();
        styleGreen.cloneStyleFrom(styleDefault);
        styleGreen.setFillForegroundColor((short) HSSFColor.GREEN.index);
        styleGreen.setFillPattern(fillPattern.SOLID_FOREGROUND);
        XSSFFont fontGreen = wb.createFont();
        fontGreen.setColor((short) HSSFColor.WHITE.index);
        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);
        XSSFCellStyle 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();
                        XSSFFont 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) (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 paintExcelParams(final XSSFWorkbook  wb, int rowNum, final int col, final List paramsList,
            final String customizedParamInfo, final XSSFSheet  sheet, final String reportTitle, final String reportDescr)
                    throws IOException {
        int cellNum = 0;
        XSSFRow row = null;
        short s1 = 0;
        short s2 = (short) 1;
        XSSFCellStyle styleName = wb.createCellStyle();
        styleName.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        styleName.setAlignment(HorizontalAlignment.CENTER);
        styleName.setBorderBottom(BorderStyle.THIN);
        styleName.setBorderTop(BorderStyle.THIN);
        styleName.setBorderRight(BorderStyle.THIN);
        styleName.setBorderLeft(BorderStyle.THIN);
        styleName.setDataFormat((short) 0);
        XSSFFont font = wb.createFont();
        font.setFontHeight((short) (FONT_SIZE / 0.05));
        font.setFontName(FONT_TAHOMA);
        font.setColor(HSSFColor.BLACK.index);
        font.setBold(true);
        styleName.setFont(font);

        XSSFFont fontDefault = wb.createFont();

        fontDefault.setColor((short) HSSFFont.COLOR_NORMAL);
        fontDefault.setFontHeight((short) (FONT_SIZE / 0.05));
        fontDefault.setFontName(FONT_TAHOMA);
        fontDefault.setItalic(true);
        XSSFCellStyle 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.setFillPattern(fillPattern.NO_FILL);
        styleValue.setFont(fontDefault);
        XSSFCell cell = null;
        XSSFCellStyle styleDescription = wb.createCellStyle();
        styleDescription.setAlignment(HorizontalAlignment.CENTER);

        XSSFFont fontDescr = wb.createFont();
        fontDescr.setFontHeight((short) (FONT_SIZE / 0.05)); // 14
        fontDescr.setFontName(FONT_TAHOMA);
        fontDescr.setColor(HSSFColor.BLACK.index);
        fontDescr.setBold(true);
        styleDescription.setFont(font);
        XSSFCell 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 int paintExcelData(final XSSFWorkbook  wb, int rowNum, final ReportData rd, final Map styles,
            final ReportRuntime rr, final XSSFSheet  sheet, final String sql_whole,
            final HttpServletRequest request) throws RaptorException {
        int mb = 1024 * 1024;
        Runtime runtime = Runtime.getRuntime();
        int returnValue = 0;
        XSSFCellStyle styleDefault = wb.createCellStyle();
        XSSFCellStyle styleNumber = wb.createCellStyle();
        XSSFCellStyle styleDecimalNumber = wb.createCellStyle();
        XSSFCellStyle styleCurrencyNumber = wb.createCellStyle();
        XSSFCellStyle styleCurrencyDecimalNumber = wb.createCellStyle();
        XSSFCellStyle styleDate = wb.createCellStyle();
        HtmlStripper strip = new HtmlStripper();
        XSSFCellStyle styleDataHeader = wb.createCellStyle();
        styleDataHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        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);
        XSSFFont font = wb.createFont();
        font.setFontHeight((short) (FONT_SIZE / 0.05));
        font.setFontName(FONT_TAHOMA);
        font.setBold(true);
        font.setColor(HSSFColor.BLACK.index);
        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());
            XSSFRow 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();) {
            XSSFRow 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);
                    }

                } // for

            }

            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;
        }
        XSSFCellStyle styleCell = null;

        XSSFCellStyle styleTotal = wb.createCellStyle();
        XSSFCellStyle styleCurrencyTotal = wb.createCellStyle();
        XSSFCellStyle styleDefaultTotal = wb.createCellStyle();
        XSSFCellStyle styleCurrencyDecimalNumberTotal = wb.createCellStyle();
        XSSFCellStyle styleDecimalNumberTotal = wb.createCellStyle();
        XSSFCellStyle styleCurrencyNumberTotal = wb.createCellStyle();
        XSSFFont fontDefault = wb.createFont();
        XSSFFont 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;
        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");
        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;
                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++) {
                        styleCell = null;
                        DataValue 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()));

                        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.equals("null")) {
                                    cell.setCellValue(zInt);
                                } else {

                                    if ((value.indexOf(".")) != -1) {
                                        if ((value.trim().startsWith("$")) || (value.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);
                                                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);
                                            }

                                        }
                                    } else {
                                        if (!(value.equals(""))) {
                                            if ((value.trim().startsWith("$")) || (value.trim().startsWith("-$"))) {
                                                String tempInt = value.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.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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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);
                                    }
                                } else {
                                    cell.setCellValue(value);
                                }
                           
                            } 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.trim().startsWith("$")) || (value.trim().startsWith("-$"))) {

                                            String tempDollar = value.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.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.trim().startsWith("$")) || (value.trim().startsWith("-$"))) {
                                                String tempInt = value.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.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));
                            }

                          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.trim().startsWith("$"))
                                            || (value.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);
                        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) {
                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)) {
                int rowCount = 0;
                for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext();) {
                    DataRow 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()));

                    } // for
                    firstPass = false;
                    int j = 0;

                    for (dr.resetNext(); dr.hasNext(); j++) {
                        DataValue dv = dr.getNext();
                        styleCell = null;
                        boolean bold = false;
                        String value = nvl(dv.getDisplayValue());
                        value = strip.stripHtml(value);
                        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));

                        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.trim().startsWith("$")) || (value.trim().startsWith("-$"))) {

                                            String tempDollar = value.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);
                                                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);
                                            }

                                        }
                                    } else {
                                        if (!(value.equals(""))) {
                                            if ((value.trim().startsWith("$")) || (value.trim().startsWith("-$"))) {
                                                String tempInt = value.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.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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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);
                                    }
                                } else {
                                    cell.setCellValue(value);
                                }
                            } 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.trim().startsWith("$")) || (value.trim().startsWith("-$"))) {

                                            String tempDollar = value.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.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.trim().startsWith("$")) || (value.trim().startsWith("-$"))) {
                                                String tempInt = value.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.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));
                            }

                            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.trim().startsWith("$"))
                                            || (value.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) {
                                } 
                            } 
                    } // for

                
                    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)));
               
                    }

                } // for

                // 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();
                    for (; drTotal.hasNext();) {
                        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;
                        }
                    }
                }

                

            } 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)));
          
                    }

                } // for

            }

        }

        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 paintExcelHeader(XSSFWorkbook wb, int rowNum, int col, String reportTitle,
            String reportDescr, XSSFSheet 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));
        XSSFRow row = null;
		HSSFRow row1 = null;

        row = sheet.createRow(rowNum);
        // Header Style
        XSSFCellStyle styleHeader = wb.createCellStyle();
        styleHeader.setAlignment(HorizontalAlignment.CENTER);
        XSSFFont font = wb.createFont();
        font.setFontHeight((short) (FONT_HEADER_TITLE_SIZE / 0.05)); // 14
        font.setFontName(FONT_TAHOMA);
        font.setColor(HSSFColor.BLACK.index);
        styleHeader.setFont(font);

        XSSFCell 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));
            XSSFCellStyle styleDescription = wb.createCellStyle();
            styleDescription.setAlignment(HorizontalAlignment.CENTER);
            XSSFFont fontDescr = wb.createFont();
            fontDescr.setFontHeight((short) FONT_HEADER_DESCR_SIZE);
            fontDescr.setFontName(FONT_TAHOMA);
            fontDescr.setColor(HSSFColor.BLACK.index);
            styleDescription.setFont(fontDescr);
            XSSFCell 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));
            XSSFCellStyle styleDescription = wb.createCellStyle();
            styleDescription.setAlignment(HorizontalAlignment.CENTER);
            XSSFFont fontDescr = wb.createFont();
            fontDescr.setFontHeight((short) (FONT_SIZE / 0.05)); // 14
            fontDescr.setFontName(FONT_TAHOMA);
            fontDescr.setColor(HSSFColor.BLACK.index);
            fontDescr.setBold(true);
            styleDescription.setFont(fontDescr);
            XSSFCell cellDescr = row.createCell((short) 0);
            String disclaimer = Globals.getFooterFirstLine() + " " + Globals.getFooterSecondLine();
            cellDescr.setCellValue(disclaimer);
            cellDescr.setCellStyle(styleDescription);
        }

        rowNum += 1;
        row = sheet.createRow(rowNum);
    }

    private void paintExcelFooter(XSSFWorkbook  wb, int rowNum, int col, XSSFSheet  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"));
    }

    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,
            ArrayList reportParamNameValues, String reportTitle, String reportDescr, int requestFlag) {
        setSheetName(Globals.getSheetName());
        try (XSSFWorkbook  wb = new XSSFWorkbook()) {
            ReportRuntime rr;
            if (requestFlag == 2)
                rr = (ReportRuntime) request.getSession().getAttribute(
                        AppConstants.SI_REPORT_RUNTIME);
            else
                rr = (ReportRuntime) request.getAttribute(
                        AppConstants.SI_REPORT_RUNTIME);

            HashMap styles = new HashMap();
            String xlsFName = "";
            if (rr != null) {
                styles = loadStyles(rr, wb);
                xlsFName = AppUtils.generateUniqueFileName(request, rr.getReportName(), AppConstants.FT_XLS);
            }
            logger.debug(EELFLoggerDelegate.debugLogger, ("Xls File name " +
                    AppUtils.getTempFolderPath()
                    + xlsFName));
            try (FileOutputStream xlsOut = new FileOutputStream(FilenameUtils.normalize(AppUtils.getTempFolderPath()
                    + xlsFName))) {
            
                int col = 0;
              
                if (!rd.reportRowHeaderCols.hasNext())
                    col = rd.getTotalColumnCount();
                else
                    col = rd.getTotalColumnCount();
                int rowNum = 0;
                XSSFSheet sheet = wb.createSheet(getSheetName());

                if (Globals.getPrintTitleInDownload() && reportTitle != null) {
                    paintExcelHeader(wb, rowNum, col, reportTitle, reportDescr, sheet);
                    rowNum = sheet.getLastRowNum();
                } else
                    rowNum = 0;
                if (Globals.getPrintParamsInDownload() && rr != null
                        && rr.getParamNameValuePairsforPDFExcel(request, 1) != null) {
                    paintExcelParams(wb, rowNum, col, rr.getParamNameValuePairsforPDFExcel(request, 1),
                            rr.getFormFieldComments(request), sheet, reportTitle, reportDescr);
                } // if
                rowNum = sheet.getLastRowNum();
                paintExcelData(wb, rowNum, rd, styles, rr, sheet, "", request);
                if (Globals.getPrintFooterInDownload()) {
                    rowNum = sheet.getLastRowNum();
                    rowNum += 2;
                    paintExcelFooter(wb, rowNum, col, sheet);
                }
                wb.write(xlsOut);
                xlsOut.flush();
            }
            return xlsFName;
        } catch (Exception e) {
            logger.error(EELFLoggerDelegate.errorLogger, "Excetion in saveAsExcelFile", e);
            (new ErrorHandler()).processError(request, "Exception saving data to EXCEL file: "
                    + 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();

            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,
            HttpServletRequest request, HttpServletResponse response, String user_id)
                    throws IOException, Exception {
        ReportHandler rephandler = new ReportHandler();
        String reportID = rr.getReportID();
        rr = rephandler.loadReportRuntime(request, reportID);
        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;

        for (Iterator iter = l.iterator(); iter.hasNext();) {
            dct = (DataColumnType) iter.next();
            allColumnsBuffer.append(dct.getDisplayName());
            if (iter.hasNext())
                allColumnsBuffer.append("|");
        }
        rd = rr.loadReportData(-1, user_id, -1, request, true);

        ServletOutputStream sos = response.getOutputStream();


        boolean firstPass = true;
        for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext();) {
            DataRow dr = rd.reportDataRows.getNext();
            for (rd.reportRowHeaderCols.resetNext(1); rd.reportRowHeaderCols.hasNext();) {
                RowHeaderCol rhc = rd.reportRowHeaderCols.getNext();
                if (firstPass)
                    rhc.resetNext();
                RowHeader rh = rhc.getNext();

                sos.print(rh.getRowTitle());
                if (rhc.hasNext())
                    sos.print("|");
            } // for
            firstPass = false;

            for (dr.resetNext(); dr.hasNext();) {
                DataValue 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();

        logger.debug(EELFLoggerDelegate.debugLogger, ("STARTING.EXCELX DOWNLOAD...."));
        logger.debug(EELFLoggerDelegate.debugLogger, ("##### Heap utilization statistics [MB] #####"));
        logger.debug(EELFLoggerDelegate.debugLogger, ("Used Memory:"
                + (runtime.totalMemory() - 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));
        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()))) {
            isDashboard = true;
        }
        ArrayList sheetArrayList = new ArrayList();

        Map reportRuntimeMap = null;
        Map reportDataMap = null;

        ArrayList reportIDList = new ArrayList();

        if (isDashboard) {
            reportRuntimeMap = (TreeMap) request.getSession().getAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME_MAP);
            reportDataMap = (TreeMap) request.getSession().getAttribute(AppConstants.SI_DASHBOARD_REPORTDATA_MAP);

            if (reportRuntimeMap != null) {
                Set setReportRuntime = reportRuntimeMap.entrySet();
                for (Iterator iter = setReportRuntime.iterator(); iter.hasNext();) {
                    Map.Entry entry = (Entry) iter.next();
                    ReportRuntime rrDashRep = (ReportRuntime) entry.getValue();
                    reportIDList.add(rrDashRep.getReportID());
                }
            }
        }

        int col = 0;
        String reportTitle = (nvl(rr.getReportTitle()).length() > 0 ? rr.getReportTitle() : rr.getReportName());
        String reportDescr = rr.getReportDescr();

        int rowNum = 0;

        XSSFSheet sheet = null;
        // save the template
        String filename = "";
        String extension = "";

        String sheetRef = null;

        File templateFile = null;

        if (isDashboard) {
            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;

                Set setReportRuntimeWB = reportRuntimeMap.entrySet();
                for (Iterator iter = setReportRuntimeWB.iterator(); iter.hasNext();) {
                    count++;
                    Map.Entry entry = (Entry) iter.next();
                    ReportRuntime rrDashRep = (ReportRuntime) entry.getValue();
                    try (FileOutputStream os = new FileOutputStream(
                            AppUtils.getTempFolderPath() + filename + "T." + nvls(extension, "xlsx"))) {

                        if (count == 1) {
                            if (nvl(rr.getTemplateFile()).length() > 0) {
                                readTemplate = new FileInputStream(
                                        org.onap.portalsdk.analytics.system.AppUtils.getExcelTemplatePath()
                                        + 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 {
                            readTemplate = new FileInputStream(
                                    AppUtils.getTempFolderPath() + filename + "." + nvls(extension, "xlsx"));
                            wb = new XSSFWorkbook(readTemplate);
                        }
                        String reportSheetName = "";
                        if (rrDashRep != null) {
                            styles = loadXSSFStyles(rrDashRep, wb, styles);
                            reportSheetName = new HtmlStripper().stripSpecialCharacters(rrDashRep.getReportName());
                        }
                        if (nvl(reportSheetName).length() > 28)
                            reportSheetName = reportSheetName.substring(0, 28);
                        sheet = wb.createSheet(count + "-" + reportSheetName);
                        if (!Globals.printExcelInLandscapeMode())
                            sheet.getPrintSetup().setLandscape(false);
                        else
                            sheet.getPrintSetup().setLandscape(true);
                        wb.write(os);
                        os.flush();
                        if (nvl(rr.getTemplateFile()).length() > 0) {
                            readTemplate.close();
                        }

                        try (FileInputStream inF = new FileInputStream(
                                AppUtils.getTempFolderPath() + filename + "T." + nvls(extension, "xlsx"));
                                FileOutputStream outStream = new FileOutputStream(
                                        AppUtils.getTempFolderPath() + filename + "." + nvls(extension, "xlsx"))) {
                            copyStream(inF, outStream);
                            outStream.flush();
                        }

                    }
                }
                FileInputStream xlsIn = null;
                POIFSFileSystem fileSystem = null;
                FileOutputStream xlsOut = null;
                formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date());
                String xlsFName = "dashboard" + formattedDate + user_id + ".xls";

                Set setReportRuntime = reportRuntimeMap.entrySet();
                Set setReportDataMap = reportDataMap.entrySet();
                Iterator iter2 = setReportDataMap.iterator();

                count = 0;
                for (Iterator iter = setReportRuntime.iterator(); iter.hasNext();) {
                    count++;

                    Map.Entry entry = (Entry) iter.next();
                    Map.Entry entryData = (Entry) iter2.next();
                    ReportRuntime rrDashRep = (ReportRuntime) entry.getValue();
                    ReportData rdDashRep = (ReportData) entryData.getValue();

                    String reportSheetName = new HtmlStripper().stripSpecialCharacters(rrDashRep.getReportName());
                    if (nvl(reportSheetName).length() > 28)
                        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)) {

                        String sql_whole = rrDashRep.getWholeSQL();

                        SpreadsheetWriter sw = new SpreadsheetWriter(fw);
                        sw.beginSheet();

                        generate(wb, sw, styles, rdDashRep, sql_whole, rrDashRep, request, sheet);

                        sw.endSheet();

                        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 =
                                new File(AppUtils.getTempFolderPath() + filename + "T." + nvls(extension, "xlsx"));
                        substitute(templateFile, tmp, sheetRef.substring(1), outF);
                        outF.flush();
                    }

                    try (FileInputStream inF = new FileInputStream(
                            AppUtils.getTempFolderPath() + filename + "." + nvls(extension, "xlsx"));
                            FileOutputStream outStream = new FileOutputStream(
                                    AppUtils.getTempFolderPath() + filename + "T." + nvls(extension, "xlsx"))) {
                        copyStream(inF, outStream);
                        outStream.flush();
                    }
                }
            }
        } 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;
            }

            if (nvl(rr.getTemplateFile()).length() <= 0) {
                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 {

                    }
                    String reportSheetName = new HtmlStripper().stripSpecialCharacters(rr.getReportName());
                    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();
                }

            } else {
                try (FileOutputStream os =
                        new FileOutputStream(AppUtils.getTempFolderPath() + filename + "T." + nvls(extension, "xlsx"));
                        FileInputStream readTemplate =
                                new FileInputStream(org.onap.portalsdk.analytics.system.AppUtils.getExcelTemplatePath()
                                        + rr.getTemplateFile());) {
                    wb = new XSSFWorkbook(readTemplate);
                    if (rr != null)
                        styles = loadXSSFStyles(rr, wb, styles);
                    sheet = wb.getSheetAt(0);
                    if (!Globals.printExcelInLandscapeMode())
                        sheet.getPrintSetup().setLandscape(false);
                    else
                        sheet.getPrintSetup().setLandscape(true);
                    sheetRef = sheet.getPackagePart().getPartName().getName();
                    wb.write(os);
                    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)) {

                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();
                }

                SpreadsheetWriter sw = new SpreadsheetWriter(fw);

                sw.beginSheet();

                if ((rd.getDataRowCount() >= rr.getReportDataSize())
                        && !rr.getReportType().equals(AppConstants.RT_HIVE)) {
                    sql_whole = "";
                }

                generate(wb, sw, styles, rd, sql_whole, rr, request, sheet);

                sw.endSheet();

                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"))) {

                if (nvl(rr.getTemplateFile()).length() > 0) {
                    templateFile = new File(AppUtils.getTempFolderPath() + filename + "T." + nvls(extension, "xlsx"));
                } else {
                    templateFile =
                            new File(AppUtils.getTempFolderPath() + "template" + formattedDate + user_id + ".xlsx");
                }
                substitute(templateFile, tmp, sheetRef.substring(1), outF);
                outF.flush();
            }

        }
        // get servlet output stream

        response.reset();
        try (BufferedInputStream buf = new BufferedInputStream(
                new FileInputStream(AppUtils.getTempFolderPath() + filename + "." + nvls(extension, "xlsx")));
                ServletOutputStream sos = response.getOutputStream()) {

            String mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            if (extension.equals("xlsm"))
                mime_type = "application/vnd.ms-excel.sheet.macroEnabled.12";
            response.setContentType(mime_type);

            response.setHeader("Content-disposition",
                    "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);

            sos.flush();
        }
        logger.debug(EELFLoggerDelegate.debugLogger, ("ENDING..DOWNLOADING XLSX..."));
        logger.debug(EELFLoggerDelegate.debugLogger, ("##### Heap utilization statistics [MB] #####"));
        logger.debug(EELFLoggerDelegate.debugLogger, ("Used Memory:"
                + (runtime.totalMemory() - 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));
        logger.debug(EELFLoggerDelegate.debugLogger, ("##### END #####"));
    }

    /**
     *
     * @param zipfile the template file
     * @param tmpfile the XML file with the sheet data
     * @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
     * @param out the stream to write the result to
     */
    private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException {
        try (ZipFile zip = new ZipFile(zipfile);
                ZipOutputStream zos = new ZipOutputStream(out)) {

            @SuppressWarnings("unchecked")
            Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
            while (en.hasMoreElements()) {
                ZipEntry ze = en.nextElement();
                if (!ze.getName().equals(entry)) {
                    zos.putNextEntry(new ZipEntry(ze.getName()));
                    try (InputStream is = zip.getInputStream(ze)) {
                        copyStream(is, zos);
                    }
                }
            }
            zos.putNextEntry(new ZipEntry(entry));
            try (InputStream is = new FileInputStream(tmpfile)) {
                copyStream(is, zos);
            }
            zos.flush();
        }
    }

    private static void copyStream(InputStream in, OutputStream out) throws IOException {
        byte[] chunk = new byte[1024];
        int count;
        while ((count = in.read(chunk)) >= 0) {
            out.write(chunk, 0, count);
        }
    }

    public void createCSVFileContent(final Writer out, final ReportData rd, final ReportRuntime rr,
            final HttpServletRequest request, final HttpServletResponse response) throws RaptorException {

        final String formattedReportName = new HtmlStripper().stripSpecialCharacters(rr.getReportName());
        final String formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date());
        final String fName = formattedReportName + formattedDate + AppUtils.getUserID(request);
        final boolean raw = AppUtils.getRequestFlag(request, "raw");
        String sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE);

        final String csvFName = fName + ".csv";
        final String zipFName = fName + ".zip";

        final String fileName = AppUtils.getTempFolderPath() + "" + csvFName;
        final Runtime runtime = Runtime.getRuntime();
        final int mb = 1024 * 1024;

        try (final PrintWriter csvOut =
                new PrintWriter(new BufferedWriter(new OutputStreamWriter(new FileOutputStream(fileName), "UTF-8")),
                        false);) {
            final HtmlStripper strip = new HtmlStripper();
            ColumnHeaderRow chr = null;
            if (!raw) {
                final String reportTitle = (nvl(rr.getReportTitle()).length() > 0 ? rr.getReportTitle()
                        : rr.getReportName());
                csvOut.println();
                csvOut.print("\"" + reportTitle + "\",");
                csvOut.println();

                if (Globals.disclaimerPositionedTopInCSVExcel()) {
                    if (Globals.getShowDisclaimer()) {
                        csvOut.println();
                        csvOut.print("\"" + Globals.getFooterFirstLine() + "\",");
                        csvOut.println();
                        csvOut.print("\"" + Globals.getFooterSecondLine() + "\",");
                        csvOut.println();
                        csvOut.println();
                    }
                }
            }
            if (Globals.getPrintParamsInCSVDownload() && !raw) {
                List paramsList = rr.getParamNameValuePairsforPDFExcel(request, 1);
                if (paramsList.size() <= 0) {
                    paramsList = (ArrayList) request.getSession().getAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO);
                }
                int paramSeq = 0;
                for (final Iterator iter = paramsList.iterator(); iter.hasNext();) {
                    final IdNameValue value = (IdNameValue) iter.next();
                    if (nvl(value.getId()).trim().length() > 0 && (!"BLANK".equals(nvl(value.getId()).trim()))) {
                        paramSeq += 1;
                        if (paramSeq <= 1) {
                            csvOut.print("\"" + RUNTIME_PARAMETERS + "\"");
                            csvOut.println();
                        }
                        csvOut.print("\"" + value.getId() + ":" + "\",");
                        final String valueName = ESAPI.encoder().canonicalize(nvl(value.getName()));
                        if (valueName.indexOf("~") != -1 && valueName.startsWith("(")) {
                            csvOut.print("\"'" + valueName.replaceAll("~", ",") + "'\",");
                        } else {
                            if (valueName.startsWith("(") && valueName.endsWith(")")) {
                                csvOut.print(
                                        "\"" + valueName.replaceAll("~", ",").substring(1, valueName.length() - 1)
                                        + "\",");
                            } else {
                                csvOut.print("\"" + valueName.replaceAll("~", ",") + "\",");
                            }
                        }
                        csvOut.println();
                    }
                } // for
                csvOut.println();
                csvOut.println();
            }

            System.out.println("##### Heap utilization statistics [MB] #####");
            System.out.println("Used Memory:" + (runtime.maxMemory() - runtime.freeMemory()) / mb);
            System.out.println("Free Memory:" + runtime.freeMemory() / mb);
            System.out.println("Total Memory:" + runtime.totalMemory() / mb);
            System.out.println("Max Memory:" + runtime.maxMemory() / mb);

            sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE);

            if (sql_whole == null) {
                if (!AppConstants.RT_HIVE.equals(rr.getReportType())) {
                    sql_whole = rr.getWholeSQL();
                } else {
                    sql_whole = rr.getReportSQL();
                }
            }

            if (nvl(sql_whole).length() > 0) {
                try (final Connection conn = ConnectionUtils.getConnection(rr.getDbInfo());
                        final Statement st = conn.createStatement();
                        final ResultSet rs = st.executeQuery(sql_whole)) {

                    System.out.println("************* Map Whole SQL *************");
                    System.out.println(sql_whole);
                    System.out.println("*****************************************");
                    final ResultSetMetaData rsmd = rs.getMetaData();
                    int numberOfColumns = rsmd.getColumnCount();
                    String title = "";

                    if (rd != null) {
                        for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext();) {
                            chr = rd.reportColumnHeaderRows.getNext();
                            for (chr.resetNext(); chr.hasNext();) {
                                ColumnHeader ch = chr.getNext();
                                title = ch.getColumnTitle();
                                title = Utils.replaceInString(title, "_nl_", " \n");
                                if (ch.isVisible() && nvl(title).length() > 0) {
                                    csvOut.print("\"" + title + "\",");
                                    for (int i = 1; i < ch.getColSpan(); i++) {
                                        csvOut.print(",");
                                    }
                                }
                            } // for

                            csvOut.println();
                        } // for
                        int rowCount = 0;
                        while (rs.next()) {
                            rowCount++;
                            final Map colHash = new HashMap();
                            for (int i = 1; i <= numberOfColumns; i++) {
                                colHash.put(rsmd.getColumnLabel(i).toUpperCase(), rs.getString(i));
                            }
                            for (chr.resetNext(); chr.hasNext();) {
                                final ColumnHeader ch = chr.getNext();
                                title = ch.getColumnTitle();
                                title = Utils.replaceInString(title, "_nl_", " \n");

                                if (ch.isVisible() && nvl(title).length() > 0) {
                                    csvOut.print(
                                            "\"" + strip
                                            .stripCSVHtml(
                                                    nvl((String) colHash.get(ch.getColId().toUpperCase())))
                                            + "\",");
                                }

                            }
                            csvOut.println();
                        }

                        if (rd.reportDataTotalRow != null) {
                            for (rd.reportDataTotalRow.resetNext(); rd.reportDataTotalRow.hasNext();) {
                                final DataRow dr = rd.reportDataTotalRow.getNext();
                                csvOut.print("\"" + "Total" + "\",");
                                dr.resetNext();
                                dr.getNext();
                                for (; dr.hasNext();) {
                                    final DataValue dv = dr.getNext();
                                    if (dv.isVisible()) {
                                        csvOut.print("\"" + strip.stripCSVHtml(dv.getDisplayValue()) + "\",");
                                    }
                                } // for

                                csvOut.println();
                            }
                        }

                        if (rowCount == 0) {
                            csvOut.print("\"No Data Found \"");
                        }
                    } else {
                        csvOut.print("\"No Data Found \"");
                    }

                } catch (final SQLException ex) {
                    throw new RaptorException(ex);
                } catch (final ReportSQLException ex) {
                    throw new RaptorException(ex);
                } catch (final Exception ex) {
                    throw new RaptorException(ex);
                }

                if (!raw) {
                    if (!Globals.disclaimerPositionedTopInCSVExcel()) {
                        if (Globals.getShowDisclaimer()) {
                            csvOut.print("\"" + Globals.getFooterFirstLine() + "\",");
                            csvOut.println();
                            csvOut.print("\"" + Globals.getFooterSecondLine() + "\",");
                            csvOut.println();
                        }
                    }
                }
            } else {
                boolean firstPass = true;
                if (rd != null) {
                    if (rd.reportTotalRowHeaderCols != null) {
                        csvOut.print("\"" + "#" + "\",");
                    }

                    for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext();) {
                        chr = rd.reportColumnHeaderRows.getNext();
                        for (rd.reportRowHeaderCols.resetNext(1); rd.reportRowHeaderCols.hasNext();) {
                            RowHeaderCol rhc = rd.reportRowHeaderCols.getNext();

                            if (firstPass) {
                                csvOut.print("\"" + rhc.getColumnTitle() + "\"");
                            }
                            csvOut.print(",");
                        } // for
                        firstPass = false;

                        for (chr.resetNext(); chr.hasNext();) {
                            ColumnHeader ch = chr.getNext();
                            if (ch.isVisible()) {
                                csvOut.print("\"" + ch.getColumnTitle() + "\",");
                                for (int i = 1; i < ch.getColSpan(); i++) {
                                    csvOut.print(",");
                                }
                            }
                        } // for

                        csvOut.println();
                    } // for

                    firstPass = true;
                    int rowCount = 0;
                    for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext();) {
                        if (rd.reportDataTotalRow != null) {
                            rowCount++;
                            csvOut.print("\"" + rowCount + "\",");
                        }

                        final DataRow dr = rd.reportDataRows.getNext();

                        for (rd.reportRowHeaderCols.resetNext(1); rd.reportRowHeaderCols.hasNext();) {
                            final RowHeaderCol rhc = rd.reportRowHeaderCols.getNext();
                            if (firstPass) {
                                rhc.resetNext();
                            }
                            final RowHeader rh = rhc.getNext();

                            csvOut.print("\"" + strip.stripCSVHtml(rh.getRowTitle()) + "\",");
                        } // for
                        firstPass = false;

                        for (dr.resetNext(); dr.hasNext();) {
                            DataValue dv = dr.getNext();
                            if (dv.isVisible()) {
                                csvOut.print("\"" + strip.stripCSVHtml(dv.getDisplayValue()) + "\",");
                            }
                        } // for

                        csvOut.println();
                    } // for
                    if (rd.reportDataTotalRow != null) {
                        for (rd.reportDataTotalRow.resetNext(); rd.reportDataTotalRow.hasNext();) {
                            DataRow dr = rd.reportDataTotalRow.getNext();
                            csvOut.print("\"" + "Total" + "\",");
                            firstPass = false;

                            for (dr.resetNext(); dr.hasNext();) {
                                DataValue dv = dr.getNext();
                                if (dv.isVisible()) {
                                    csvOut.print("\"" + strip.stripCSVHtml(dv.getDisplayValue()) + "\",");
                                }
                            } // for

                            csvOut.println();
                        }
                    }

                    if (!raw) {
                        if (!Globals.disclaimerPositionedTopInCSVExcel()) {
                            if (Globals.getShowDisclaimer()) {
                                csvOut.print("\"" + Globals.getFooterFirstLine() + "\",");
                                csvOut.println();
                                csvOut.print("\"" + Globals.getFooterSecondLine() + "\",");
                                csvOut.println();
                            }
                        }
                    }
                } else {
                    csvOut.print("\"No Data Found \"");
                }
            }
            csvOut.flush();
        } catch (final FileNotFoundException fex) {
            logger.warn("Failed to open/read/write file", fex);
        } catch (final UnsupportedEncodingException fex1) {
            logger.warn("Failed to open/read/write file", fex1);
        }

        if (!raw) {
            try (final ZipOutputStream zos =
                    new ZipOutputStream(new FileOutputStream(AppUtils.getTempFolderPath() + "" + zipFName));
                    final FileInputStream fis = new FileInputStream(fileName)) {

                int size = 0;
                final byte[] buffer = new byte[1024];

                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);
                }
                zos.closeEntry();

            } catch (final Exception e) {
                logger.warn("Failed to open/read/write file", e);
            }
        }

        response.reset();
        final File file;

        if (!raw) {
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename=" + fName + ".zip");
            file = new File(AppUtils.getTempFolderPath() + "" + fName + ".zip");
        } else {
            response.setContentType("application/csv");
            response.setHeader("Content-disposition", "attachment;filename=" + fName + ".csv");
            file = new File(AppUtils.getTempFolderPath() + "" + fName + ".csv");
        }

        try (final ServletOutputStream sos = response.getOutputStream();
                final FileInputStream fileIn = new FileInputStream(file);
                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();
                sos.write(bOut, 0, readBytes);
            }

        } catch (final IOException ex) {
            logger.warn("Failed to open/read/write file", ex);
        } catch (final Exception e) {
            logger.warn("Failed to open/read/write file", e);
        }

        final File f = new File(AppUtils.getTempFolderPath() + fName);
        if (f.exists()) {
            f.delete();
        }

        System.out.println("##### Heap utilization statistics [MB] #####");
        System.out.println("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));

    } // createCSVFileContent

    public String saveXMLFile(HttpServletRequest request, String reportName, String reportXML) {
        try {
            String xmlFName = AppUtils.generateUniqueFileName(request, reportName, AppConstants.FT_XML);

            try (PrintWriter xmlOut = new PrintWriter(new BufferedWriter(new FileWriter(new File(
                    AppUtils.getTempFolderPath() + xmlFName))))) {
                xmlOut.println(reportXML);
            }

            return java.net.URLEncoder.encode(java.net.URLDecoder.decode(xmlFName));

        } catch (Exception e) {
            logger.error(EELFLoggerDelegate.errorLogger, "Exception in saveXMLFile", e);
            (new ErrorHandler()).processError(request,
                    "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 {
        return loadReportRuntime(request, reportID, true, 2); // where 2 is adding to session
    }

    public ReportRuntime loadReportRuntime(HttpServletRequest request, String reportID,
            boolean prepareForExecution, int requestFlag) throws RaptorException {
        boolean refresh = nvl(request.getParameter(AppConstants.RI_REFRESH)).toUpperCase().startsWith("Y");
        boolean rDisplayContent = AppUtils.getRequestFlag(request,
                AppConstants.RI_DISPLAY_CONTENT)
                || AppUtils.getRequestFlag(request, "noFormFields");

        ReportRuntime rr = (ReportRuntime) request.getSession().getAttribute(
                AppConstants.SI_REPORT_RUNTIME);
        boolean inSchedule = AppUtils.getRequestFlag(request, AppConstants.SCHEDULE_ACTION);
        if (rr != null) {
            if (requestFlag == 7) { // DASH
                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
                return rr;
            } else {
                logger.debug(EELFLoggerDelegate.debugLogger, ("[DEBUG MESSAGE FROM RAPTOR] Load Report Runtime "
                        + reportID + " " + rr.getReportID() + " " + request.getParameter("refresh")));
                if (reportID.equals(rr.getReportID())
                        && (request.getParameter("refresh") == null || !request.getParameter("refresh").equals("Y"))) {
                    if (prepareForExecution) {
                        boolean resetParams = AppUtils.getRequestFlag(request,
                                AppConstants.RI_RESET_PARAMS);
                        rr.setParamValues(request, resetParams, refresh);

                        if (resetParams)
                            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,
                ("[DEBUG MESSAGE FROM RAPTOR] Report [" + reportID + "]: XML loaded"));

        rr = ReportRuntime.unmarshal(reportXML, reportID, request);
        if (prepareForExecution) {
            String userID;
            int flag = 0;
            if (request.getAttribute("schedule_email_userId") != null) {
                userID = (String) request.getAttribute("schedule_email_userId");
                flag = 1;
            } else
                userID = AppUtils.getUserID(request);
            String dashboardId = AppUtils.getRequestValue(request, AppConstants.RI_DASHBOARD_ID);
           if (!rr.isDashboardType() && !(isReportAddedAsDashboard(request, dashboardId, rr.getReportID()))) {
                if (AppUtils.getRequestNvlValue(request, "pdfAttachmentKey").length() <= 0)
                    if (flag == 1)
                        rr.checkUserReadAccess(request, userID);
                    else
                        rr.checkUserReadAccess(request);
            }
            // TODO ON Demand
            if (rDisplayContent) {
                rr.setParamValues(request, false, true);
                request.getSession().setAttribute(AppConstants.SI_REPORT_RUNTIME, rr);
            }
            if (inSchedule) {
                rr.setParamValues(request, false, false);
            }
            if (requestFlag == 7) { // DASH
                rr.setDisplayFlags(true, true);
            } else {
                rr.setDisplayFlags(request.getParameter(AppConstants.RI_SOURCE_PAGE) == null,
                        rDisplayContent || rr.isDisplayOptionHideForm());
            }

            if (requestFlag == 2 && !rDisplayContent) {
                request.getSession().setAttribute(AppConstants.SI_REPORT_RUNTIME, rr);
                rr.setParamValues(request, false, false);
            } else if (requestFlag == 1) {
                rr.setParamValues(request, false, refresh);
                request.setAttribute(AppConstants.SI_REPORT_RUNTIME, rr);

            }

        } // if

        return rr;
    } // loadReportRuntime

    private boolean isReportAddedAsDashboard(HttpServletRequest request, String dashboardId, String reportId)
            throws RaptorException {
        if (nvl(dashboardId).length() <= 0)
            return false;
        String reportXML = ReportLoader.loadCustomReportXML(dashboardId);
        ReportDefinition rdef = createReportDefinition(request, dashboardId, reportXML);
        List l = rdef.getDashBoardReports().getReportsList();
        for (Iterator iterator = l.iterator(); iterator.hasNext();) {
            Reports reports = (Reports) iterator.next();
            if (reports.getReportId().equals(reportId))
                return true;

        }
        return false;
    }

    public ReportDefinition createReportDefinition(HttpServletRequest request,
            String reportID, String reportXML) throws RaptorException {
        ReportDefinition rdef = ReportDefinition.unmarshal(reportXML, reportID, request);
        rdef.generateWizardSequence(request);
        return rdef;
    } // createReportDefinition

    public ReportDefinition loadReportDefinition(HttpServletRequest request, String reportID)
            throws RaptorException {
        boolean isReportIDBlank = (reportID.length() == 0 || reportID.equals("-1"));
        String actionKey = nvl(request.getParameter(AppConstants.RI_ACTION), "");
        String wizardActionKey = nvl(request.getParameter(AppConstants.RI_WIZARD_ACTION), "");
        ReportDefinition rdef = (ReportDefinition) request.getSession().getAttribute(
                AppConstants.SI_REPORT_DEFINITION);
        if (nvl(actionKey).equals("report.edit"))
            rdef = null;
        if (rdef != null)
            if (isReportIDBlank || reportID.equals(rdef.getReportID())) {
                return rdef;
            }

        ReportRuntime rr = (ReportRuntime) request.getSession().getAttribute(
                AppConstants.SI_REPORT_RUNTIME);
        if (rr != null)
            if (isReportIDBlank || reportID.equals(rr.getReportID())) {
       
                rdef = new ReportDefinition(rr, request);
                String userID = AppUtils.getUserID(request);
                rdef.generateWizardSequence(request);

                request.getSession().setAttribute(AppConstants.SI_REPORT_DEFINITION, rdef);
                return rdef;
            } // if

        if (isReportIDBlank)
            rdef = ReportDefinition.createBlank(request);
        else {
            String reportXML = ReportLoader.loadCustomReportXML(reportID);
            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;
    }

    public String getSheetName() {
        return sheetName;
    }

    /**
     * Writes spreadsheet data in a Writer. (YK: in future it may evolve in a full-featured API for
     * streaming data in Excel)
     */
    public static class SpreadsheetWriter {
        private final Writer _out;
        private int _rownum;

        public SpreadsheetWriter(Writer out) {
            _out = out;
        }

        public void beginSheet() throws IOException {
            _out.write("<?xml version=\"1.0\" encoding=\"" + XML_ENCODING + "\"?>" +
                    "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">");
            _out.write("<sheetData>\n");
        }

        public void endSheet() throws IOException {
            _out.write("</sheetData>");
            _out.write("</worksheet>");
        }

        /**
         * Insert a new row
         *
         * @param rownum 0-based row number
         */
        public void insertRow(int rownum) throws IOException {
            _out.write("<row r=\"" + (rownum + 1) + "\">\n");
            this._rownum = rownum;
        }

        /**
         * Insert row end marker
         */
        public void endRow() throws IOException {
            _out.write("</row>\n");
        }

        public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
            String ref = new CellReference(_rownum, columnIndex).formatAsString();
            _out.write("<c r=\"" + ref + "\" t=\"inlineStr\"");
            if (styleIndex != -1)
                _out.write(" s=\"" + styleIndex + "\"");
            _out.write(">");
            _out.write("<is><t>" + value + "</t></is>");
            _out.write("</c>");
        }

        public void createCell(int columnIndex, String value) throws IOException {
            createCell(columnIndex, value, -1);
        }

        public void createCell(int columnIndex, double value, int styleIndex) throws IOException {
            String ref = new CellReference(_rownum, columnIndex).formatAsString();
            _out.write("<c r=\"" + ref + "\" t=\"n\"");
            if (styleIndex != -1)
                _out.write(" s=\"" + styleIndex + "\"");
            _out.write(">");
            _out.write("<v>" + value + "</v>");
            _out.write("</c>");
        }

        public void createCell(int columnIndex, double value) throws IOException {
            createCell(columnIndex, value, -1);
        }

        public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
            createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);
        }
    }

    public int getColumnCountForDownloadFile(ReportRuntime rr, ReportData rd) {
        int columnCount = 0;
        for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext();) {
            ColumnHeaderRow chr = rd.reportColumnHeaderRows.getNext();
            for (chr.resetNext(); chr.hasNext();) {
                ColumnHeader ch = chr.getNext();
                if (ch.isVisible()) {
                    columnCount++;
                }
            }
        }
        if (rr.getReportType().equals(AppConstants.RT_CROSSTAB)) {
            for (rd.reportRowHeaderCols.resetNext(); rd.reportRowHeaderCols.hasNext();) {
                RowHeaderCol rhc = rd.reportRowHeaderCols.getNext();
                if (rhc.isVisible()) {
                    columnCount++;
                }
            }
        }
        return columnCount;
    }

    private Map<String, XSSFCellStyle> loadXSSFStyles(ReportRuntime rr, XSSFWorkbook wb,
            Map<String, XSSFCellStyle> loadedStyles) {
        XSSFCellStyle styleDefault = wb.createCellStyle();

        XSSFFont fontDefault = wb.createFont();

        XSSFDataFormat xssffmt = wb.createDataFormat();
        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);
        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();
        Map<String, XSSFCellStyle> hashMapStyles = new HashMap<>();;
        Map<String, XSSFFont> hashMapFonts = new HashMap<>();
        hashMapFonts.put(DEFAULT, fontDefault);
        hashMapStyles.put(DEFAULT, styleDefault);
        XSSFCellStyle styleLeftDefault = wb.createCellStyle();
        styleLeftDefault.setAlignment(HorizontalAlignment.LEFT);
        styleLeftDefault.setBorderBottom(BorderStyle.THIN);
        styleLeftDefault.setBorderTop(BorderStyle.THIN);
        styleLeftDefault.setBorderLeft(BorderStyle.THIN);
        styleLeftDefault.setBorderRight(BorderStyle.THIN);
        styleLeftDefault.setFillPattern(fillPattern.NO_FILL);
        styleLeftDefault.setFont(fontDefault);
        hashMapStyles.put("defaultLeft", styleLeftDefault);

        XSSFCellStyle styleDate = wb.createCellStyle();
        styleDate.setAlignment(HorizontalAlignment.RIGHT);
        styleDate.setDataFormat(xssffmt.getFormat("d-mmm-yy"));
        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);
        hashMapStyles.put("date", styleDate);

        XSSFCellStyle rowHeaderStyle = wb.createCellStyle();
        XSSFFont headerFont = wb.createFont();
        headerFont.setBold(true);
        rowHeaderStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        rowHeaderStyle.setFillPattern(fillPattern.SOLID_FOREGROUND);
        rowHeaderStyle.setFont(headerFont);
        rowHeaderStyle.setBorderTop(BorderStyle.THIN);
        rowHeaderStyle.setBorderLeft(BorderStyle.THIN);
        rowHeaderStyle.setBorderBottom(BorderStyle.THIN);
        rowHeaderStyle.setBorderRight(BorderStyle.THIN);
        hashMapStyles.put("header", rowHeaderStyle);

        XSSFCellStyle boldStyle = wb.createCellStyle();

        boldStyle.setFont(headerFont);
        boldStyle.setBorderTop(BorderStyle.THIN);
        boldStyle.setBorderLeft(BorderStyle.THIN);
        boldStyle.setBorderBottom(BorderStyle.THIN);
        boldStyle.setBorderRight(BorderStyle.THIN);
        boldStyle.setAlignment(HorizontalAlignment.CENTER);
        hashMapStyles.put("title", boldStyle);

        XSSFCellStyle cellStyle = null;
        if (semList == null || semList.getSemaphore() == null) {
            hashMapStyles.put(DEFAULT, styleDefault);
        } 
        loadedStyles.putAll(hashMapStyles);
        return loadedStyles;
    }

    private void generate(XSSFWorkbook wb, SpreadsheetWriter sw, Map<String, XSSFCellStyle> styles, ReportData rd,
            String sql_whole, ReportRuntime rr, HttpServletRequest request, XSSFSheet sheet) throws Exception {
        HtmlStripper strip = new HtmlStripper();
        XSSFCellStyle styleCell = null;
        XSSFCellStyle styleRowCell = null;
        XSSFCellStyle styleDefaultCell = null;

        styleDefaultCell = (XSSFCellStyle) styles.get(DEFAULT);

        // to check performance
        int mb = 1024 * 1024;
        Runtime runtime = Runtime.getRuntime();

        int rowNum = 0;
      int cellNum = 0;

        ColumnHeaderRow chr = null;
        java.util.HashMap dataTypeMap = new java.util.HashMap();
        boolean firstPass = true;
        int columnRows = rr.getVisibleColumnCount();

        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());
            sw.insertRow(rowNum);
            cellNum = 0;
            sw.createCell(cellNum, Utils.excelEncode(header));
            for (int i = 1; i <= columnRows; i++) {
                sw.createCell(cellNum + i, "");
            }
            sheet.addMergedRegion(new CellRangeAddress(rowNum + 1, rowNum + 1, cellNum + 1, columnRows));
            sw.endRow();
           rowNum += 1;
            sw.insertRow(rowNum);
            cellNum = 0;
        
            sheet.addMergedRegion(new CellRangeAddress(rowNum + 1, rowNum + 1, cellNum + 1, columnRows));
            sw.createCell(cellNum, Utils.excelEncode(subtitle));
            sw.endRow();
             rowNum += 1;
        }
        cellNum = 0;
        String title = "";
        for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext();) {
            sw.insertRow(rowNum);
            cellNum = -1;
      
            chr = rd.reportColumnHeaderRows.getNext();

            if (nvl(sql_whole).length() <= 0 || (!rr.getReportType().equals(AppConstants.RT_LINEAR))) {

                for (rd.reportRowHeaderCols.resetNext(1); rd.reportRowHeaderCols.hasNext();) {
                    cellNum += 1;
                    RowHeaderCol rhc = rd.reportRowHeaderCols.getNext();
                    title = rhc.getColumnTitle();
                    title = Utils.replaceInString(title, "_nl_", " \n");

                    sw.createCell(cellNum, Utils.excelEncode(title), styles.get("header").getIndex());
               } // for

            }

            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");
                    sw.createCell(cellNum, Utils.excelEncode(title), styles.get("header").getIndex());
                    if (colSpan > 0) {
                        for (int k = 1; k <= colSpan; k++) {
                            sw.createCell(cellNum + k, "", styles.get("header").getIndex());
                        }
                     }
                    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<>();

        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");

        short dateFormat = createHelper.createDataFormat().getFormat("MM/dd/yyyy");
        dateFormatMap.put("MMDDYYYY", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("yyyy/MM/dd");
        dateFormatMap.put("YYYYMMDD", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("MMM yyyy");
        dateFormatMap.put("MONYYYY", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("MM/yyyy");
        dateFormatMap.put("MMYYYY", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("MMMMM dd, yyyy");
        dateFormatMap.put("MMMMMDDYYYY", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("yyyy-MM-dd");
        dateFormatMap.put("YYYYMMDDDASH", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss");
        dateFormatMap.put("timestamp", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("dd-MMM-yyyy");
        dateFormatMap.put("MONTHYYYY", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("MMMMM, yyyy");
        dateFormatMap.put("MMDDYYYY", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("MM/dd/yyyy HH:mm:ss");
        dateFormatMap.put("MMDDYYYYHHMM", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("MM/dd/yyyy HH:mm");
        dateFormatMap.put("MMDDYYYY", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("yyyy/MM/dd HH:mm:ss");
        dateFormatMap.put("YYYYMMDDHHMMSS", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("yyyy/MM/dd HH:mm");
        dateFormatMap.put("YYYYMMDDHHMM", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("dd-MMM-yyyy HH:mm:ss");
        dateFormatMap.put("DDMONYYYYHHMMSS", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("dd-MMM-yyyy HH:mm");
        dateFormatMap.put("DDMONYYYYHHMM", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("dd-MMM-yy HH:mm");
        dateFormatMap.put("DDMONYYHHMM", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("dd-MMM-yyyy");
        dateFormatMap.put("DDMONYYYY", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("MM/dd/yy");
        dateFormatMap.put("MMDDYY", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("MM/dd/yy HH:mm");
        dateFormatMap.put("MMDDYYHHMM", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("MM/dd/yy HH:mm:ss");
        dateFormatMap.put("MMDDYYHHMMSS", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("MM/dd/yyyy HH:mm z");
        dateFormatMap.put("MMDDYYYYHHMMZ", new Short(dateFormat));
        dateFormat = createHelper.createDataFormat().getFormat("MMMMM-dd-yyyy HH:mm:ss");
        dateFormatMap.put("MMMMMDDYYYYHHMMSS", new Short(dateFormat));

        ResultSetMetaData rsmd = null;

        if (nvl(sql_whole).length() > 0 && (rr.getReportType().equals(AppConstants.RT_LINEAR)
                || rr.getReportType().equals(AppConstants.RT_HIVE))) {
            try (Connection conn = ConnectionUtils.getConnection(rr.getDbInfo());
                    Statement st = conn.createStatement();
                    ResultSet rs = st.executeQuery(sql_whole)) {

                logger.debug(EELFLoggerDelegate.debugLogger, ("************* Map Whole SQL *************"));
                logger.debug(EELFLoggerDelegate.debugLogger, (sql_whole));
                logger.debug(EELFLoggerDelegate.debugLogger, ("*****************************************"));
                rsmd = rs.getMetaData();
                int numberOfColumns = rsmd.getColumnCount();
                HashMap colHash = new HashMap();
                DataRow dr = null;
                int j = 0;
                int rowCount = 0;
                while (rs.next()) {

                    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] #####"));
                        logger.debug(EELFLoggerDelegate.debugLogger, ("Used Memory:"
                                + (runtime.totalMemory() - 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));
                        System.out.println(rowCount + "TH ROW****##### END #####");

                        //
                    }
                    sw.insertRow(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();
                    styleRowCell = null;
                    if (dr.isRowFormat() && styles != null)
                        styleRowCell = (XSSFCellStyle) styles.get(nvl(/* dr.getFormatId(), */"", DEFAULT));
                    j = 0;
                  firstPass = false;
                    for (dr.resetNext(); dr.hasNext(); j++) {
                        styleCell = null;
                        DataValue dv = dr.getNext();
                        HtmlFormatter htmlFormat = dv.getCellFormatter();

                        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()));

                        boolean bold = false;

                        if (dv.isVisible()) {
                            cellNum += 1;
                            String dataType = (String) (dataTypeMap.get(dv.getColId()));
                            if (dataType != null && dataType.equals("NUMBER")) {
                                int zInt = 0;
                                if (value.equals("null")) {
                                    sw.createCell(cellNum, zInt,
                                            styles.get(nvl(/* dv.getFormatId() */"", DEFAULT)).getIndex());
                                } else {

                                    if ((value.indexOf(".")) != -1) {
                                        if ((value.trim().startsWith("$")) || (value.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 (styleRowCell != null)
                                                    sw.createCell(cellNum, tempDoubleDollar, styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, tempDoubleDollar, styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, tempDoubleDollar, styles
                                                            .get(nvl(/* dv.getFormatId() */"", DEFAULT)).getIndex());
                                            } catch (NumberFormatException ne) {
                                                if (styleRowCell != null)
                                                    sw.createCell(cellNum, Utils.excelEncode(tempDollar),
                                                            styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, Utils.excelEncode(tempDollar),
                                                            styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, Utils.excelEncode(tempDollar), styles
                                                            .get(nvl(/* dv.getFormatId() */"", DEFAULT)).getIndex());
                                            }
                                        } else {
                                            double tempDouble = 0.0;
                                            try {
                                                tempDouble = Double.parseDouble(value);
                                                if (styleRowCell != null)
                                                    sw.createCell(cellNum, tempDouble, styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, tempDouble, styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, tempDouble, styles
                                                            .get(nvl(/* dv.getFormatId() */"", DEFAULT)).getIndex());
                                            } catch (NumberFormatException ne) {
                                                if (styleRowCell != null)
                                                    sw.createCell(cellNum, Utils.excelEncode(value),
                                                            styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, Utils.excelEncode(value),
                                                            styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, Utils.excelEncode(value), styles
                                                            .get(nvl(/* dv.getFormatId() */"", DEFAULT)).getIndex());
                                            }

                                        }
                                    } else {
                                        if (!(value.equals(""))) {
                                            if ((value.trim().startsWith("$")) || (value.trim().startsWith("-$"))) {
                                                String tempInt = value.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 (styleRowCell != null)
                                                        sw.createCell(cellNum, tempIntDollar, styleRowCell.getIndex());
                                                    else if (styleCell != null)
                                                        sw.createCell(cellNum, tempIntDollar, styleCell.getIndex());
                                                    else
                                                        sw.createCell(cellNum, tempIntDollar,
                                                                styles.get(nvl(/* dv.getFormatId() */"", DEFAULT))
                                                                .getIndex());
                                                } catch (NumberFormatException ne) {
                                                    if (styleRowCell != null)
                                                        sw.createCell(cellNum, tempInt, styleRowCell.getIndex());
                                                    else if (styleCell != null)
                                                        sw.createCell(cellNum, tempInt, styleCell.getIndex());
                                                    else
                                                        sw.createCell(cellNum, tempInt,
                                                                styles.get(nvl(/* dv.getFormatId() */"", DEFAULT))
                                                                .getIndex());
                                                }
                                            } else {
                                                String tempStr = value.trim();
                                                if ((tempStr.indexOf(",")) != -1) {
                                                    tempStr = tempStr.replaceAll(",", "");
                                                }
                                                Long temp = 0L;

                                                try {
                                                    temp = Long.parseLong(tempStr);
                                                    if (styleRowCell != null)
                                                        sw.createCell(cellNum, temp, styleRowCell.getIndex());
                                                    else if (styleCell != null)
                                                        sw.createCell(cellNum, temp, styleCell.getIndex());
                                                    else
                                                        sw.createCell(cellNum, temp,
                                                                styles.get(nvl(/* dv.getFormatId() */"", DEFAULT))
                                                                .getIndex());
                                                } catch (NumberFormatException ne) {
                                                    if (styleRowCell != null)
                                                        sw.createCell(cellNum, Utils.excelEncode(tempStr),
                                                                styleRowCell.getIndex());
                                                    else if (styleCell != null)
                                                        sw.createCell(cellNum, Utils.excelEncode(tempStr),
                                                                styleCell.getIndex());
                                                    else
                                                        sw.createCell(cellNum, Utils.excelEncode(tempStr),
                                                                styles.get(nvl(/* dv.getFormatId() */"", DEFAULT))
                                                                .getIndex());
                                                }
                                            }
                                        }
                                    }
                                }

                            } 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"))) {
                                XSSFCellStyle cellStyle = null;
                                if (styleRowCell != null) {
                                    cellStyle = styleRowCell;
                                } else if (styleCell != null) {
                                    cellStyle = styleCell;
                                } else {
                                    cellStyle = styles.get(nvl(/* dv.getFormatId() */"", "date"));
                                }

                                Date date = null;
                                int flag = 0;
                                date = MMDDYYHHMMSSFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMDDYYHHMMSS"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MMDDYYHHMMFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMDDYYHHMM"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MMDDYYFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMDDYY"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MMDDYYYYHHMMSSFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMDDYYYYHHMMSS"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MMDDYYYYHHMMFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMDDYYYYHHMM"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MMDDYYYYFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMDDYYYY"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = YYYYMMDDFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("YYYYMMDD"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = timestampFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("timestamp"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MONYYYYFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MONYYYY"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MMYYYYFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMYYYY"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MMMMMDDYYYYFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMMMMDDYYYY"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MONTHYYYYFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MONTHYYYY"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = YYYYMMDDHHMMSSFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("YYYYMMDDHHMMSS"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = YYYYMMDDDASHFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("YYYYMMDDDASH"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = YYYYMMDDHHMMFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("YYYYMMDDHHMM"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = DDMONYYYYHHMMSSFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("DDMONYYYYHHMMSS"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = DDMONYYYYHHMMFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("DDMONYYYYHHMM"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = DDMONYYHHMMFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("DDMONYYHHMM"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = DDMONYYYYFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("DDMONYYYY"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MMDDYYHHMMSSFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMDDYYHHMMSS"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MMDDYYHHMMFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMDDYYHHMM"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MMDDYYFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMDDYY"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MMDDYYHHMMFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMDDYYHHMM"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MMDDYYHHMMSSFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMDDYYHHMMSS"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MMDDYYYYHHMMZFormat.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMDDYYYYHHMMZ"));
                                    flag = 1;
                                }
                                if (date == null)
                                    date = MMMMMDDYYYYHHMMSS.parse(value, new ParsePosition(0));
                                if (date != null && flag == 0) {
                                    cellStyle.setDataFormat(dateFormatMap.get("MMMMMDDYYYYHHMMSS"));
                                    flag = 1;
                                }

                                if (date != null) {
                                    Calendar cal = Calendar.getInstance();
                                    cal.setTime(date);
                                     sw.createCell(cellNum, cal, cellStyle.getIndex());
                                } else {
                                    sw.createCell(cellNum, Utils.excelEncode(value), cellStyle.getIndex());
                               
                                }
                             
                            } else if ((dv.getDisplayTotal() != null && dv.getDisplayTotal().equals("SUM("))
                                    || (dv.getColName() != null && dv.getColName().indexOf("999") != -1)) {
                                int zInt = 0;
                                if (value.equals("null")) {
                                    if (styleRowCell != null)
                                        sw.createCell(cellNum, zInt, styleRowCell.getIndex());
                                    else if (styleCell != null)
                                        sw.createCell(cellNum, zInt, styleCell.getIndex());
                                    else
                                        sw.createCell(cellNum, zInt, styleDefaultCell.getIndex());

                                } else {

                                    if ((value.indexOf(".")) != -1) {
                                        if ((value.trim().startsWith("$")) || (value.trim().startsWith("-$"))) {

                                            String tempDollar = value.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 (styleRowCell != null)
                                                    sw.createCell(cellNum, tempDoubleDollar, styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, tempDoubleDollar, styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, tempDoubleDollar, styles
                                                            .get(nvl(/* dv.getFormatId() */"", DEFAULT)).getIndex());
                                            } catch (NumberFormatException ne) {
                                                if (styleRowCell != null)
                                                    sw.createCell(cellNum, Utils.excelEncode(tempDollar),
                                                            styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, Utils.excelEncode(tempDollar),
                                                            styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, Utils.excelEncode(tempDollar), styles
                                                            .get(nvl(/* dv.getFormatId() */"", DEFAULT)).getIndex());
                                            }

                                        } else {
                                            String tempDoubleStr = value.trim();
                                            tempDoubleStr = tempDoubleStr.replaceAll(" ", "").substring(0);
                                            if ((tempDoubleStr.indexOf(",")) != -1) {
                                                tempDoubleStr = tempDoubleStr.replaceAll(",", "");
                                            }
                                            double tempDouble = 0.0;
                                            try {
                                                tempDouble = Double.parseDouble(tempDoubleStr);
                                                if (styleRowCell != null)
                                                    sw.createCell(cellNum, tempDouble, styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, tempDouble, styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, tempDouble, styles
                                                            .get(nvl(/* dv.getFormatId() */"", DEFAULT)).getIndex());
                                            } catch (NumberFormatException ne) {
                                                if (styleRowCell != null)
                                                    sw.createCell(cellNum, Utils.excelEncode(tempDoubleStr),
                                                            styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, Utils.excelEncode(tempDoubleStr),
                                                            styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, Utils.excelEncode(tempDoubleStr), styles
                                                            .get(nvl(/* dv.getFormatId() */"", DEFAULT)).getIndex());
                                            }
                                        }

                                    } else {
                                        if (!(value.equals(""))) {
                                            if ((value.trim().startsWith("$")) || (value.trim().startsWith("-$"))) {
                                                String tempInt = value.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 (styleRowCell != null)
                                                        sw.createCell(cellNum, tempIntDollar, styleRowCell.getIndex());
                                                    else if (styleCell != null)
                                                        sw.createCell(cellNum, tempIntDollar, styleCell.getIndex());
                                                    else
                                                        sw.createCell(cellNum, tempIntDollar,
                                                                styles.get(nvl(/* dv.getFormatId() */"", DEFAULT))
                                                                .getIndex());

                                                } catch (NumberFormatException ne) {
                                                    if (styleRowCell != null)
                                                        sw.createCell(cellNum, Utils.excelEncode(tempInt),
                                                                styleRowCell.getIndex());
                                                    else if (styleCell != null)
                                                        sw.createCell(cellNum, Utils.excelEncode(tempInt),
                                                                styleCell.getIndex());
                                                    else
                                                        sw.createCell(cellNum, Utils.excelEncode(tempInt),
                                                                styles.get(nvl(/* dv.getFormatId() */"", DEFAULT))
                                                                .getIndex());
                                                }
                                            } else {
                                                String tempStr = value.trim();
                                                if ((tempStr.indexOf(",")) != -1) {
                                                    tempStr = tempStr.replaceAll(",", "");
                                                }
                                                Long temp = 0L;

                                                try {
                                                    temp = Long.parseLong(tempStr);
                                                    if (styleRowCell != null)
                                                        sw.createCell(cellNum, temp, styleRowCell.getIndex());
                                                    else if (styleCell != null)
                                                        sw.createCell(cellNum, temp, styleCell.getIndex());
                                                    else
                                                        sw.createCell(cellNum, temp,
                                                                styles.get(nvl(/* dv.getFormatId() */"", DEFAULT))
                                                                .getIndex());
                                                } catch (NumberFormatException ne) {
                                                    if (styleRowCell != null)
                                                        sw.createCell(cellNum, Utils.excelEncode(tempStr),
                                                                styleRowCell.getIndex());
                                                    else if (styleCell != null)
                                                        sw.createCell(cellNum, Utils.excelEncode(tempStr),
                                                                styleCell.getIndex());
                                                    else
                                                        sw.createCell(cellNum, Utils.excelEncode(tempStr),
                                                                styles.get(nvl(/* dv.getFormatId() */"", DEFAULT))
                                                                .getIndex());

                                                }
                                            }
                                        } else {
                                            sw.createCell(cellNum, "",
                                                    styles.get(nvl(/* dv.getFormatId() */"", DEFAULT)).getIndex());
                                        }
                                    }
                                }

                            } else {
                                if (styleRowCell != null)
                                    sw.createCell(cellNum, strip.stripHtml(Utils.excelEncode(value)),
                                            styleRowCell.getIndex());
                                else if (styleCell != null)
                                    sw.createCell(cellNum, strip.stripHtml(Utils.excelEncode(value)),
                                            styleCell.getIndex());
                                else {
                                    if (nvl(value).startsWith(" "))
                                        sw.createCell(cellNum, strip.stripHtml(Utils.excelEncode(value)),
                                                styles.get(nvl(/* dv.getFormatId(), */"", "defaultLeft")).getIndex());
                                    else
                                        sw.createCell(cellNum, strip.stripHtml(Utils.excelEncode(value)),
                                                styles.get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());

                                }

                            }

                            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("-$"))) {
                                    } else {
                                    }
                                } else {
                                }
                                bold = true;
                            }
                            if ((dr.isRowFormat() && !dv.isCellFormat()) && styles != null) {
                                continue;
                            }
                            if (htmlFormat != null && dv.getFormatId() != null && bold == false
                                    && styles != null) {
                            } 
                        } 

                    }
                    rowNum += 1;
                    sw.endRow();

                }
                if (rd.reportTotalRowHeaderCols != null) {
                    rowCount++;
                    sw.insertRow(rowNum);
                    cellNum = -1;
                    rd.reportTotalRowHeaderCols.resetNext();
                    cellNum += 1;
                    RowHeaderCol rhc = rd.reportTotalRowHeaderCols.getNext();
                    RowHeader rh = rhc.getRowHeader(0);
                    if (dr.isRowFormat() && styles != null)
                        styleRowCell = (XSSFCellStyle) styles.get(nvl(/* dr.getFormatId(), */"", DEFAULT));

                    if (styleRowCell != null)
                        sw.createCell(cellNum, strip.stripHtml(rh.getRowTitle()), styleRowCell.getIndex());
                    else
                        sw.createCell(cellNum, strip.stripHtml(rh.getRowTitle()), styleDefaultCell.getIndex());
                    rd.reportDataTotalRow.resetNext();
                    DataRow drTotal = rd.reportDataTotalRow.getNext();
                    if (drTotal != null) {
                        drTotal.resetNext();
                        drTotal.getNext();
                        for (; drTotal.hasNext();) {
                            DataValue dv = drTotal.getNext();
                            if (dv.isVisible()) {
                                cellNum += 1;
                                styleCell = null;
                                String value = dv.getDisplayValue();
                                sw.createCell(cellNum, value,
                                        styles.get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                            }
                        }
                    }
                    rowNum += 1;
                    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) {
                throw new RaptorException(ex);
            } catch (Exception ex) {
                if (!(ex.getCause() instanceof java.net.SocketException))
                    throw new RaptorException(ex);
            }

            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());
                rowNum += 1;
                sw.insertRow(rowNum);
                cellNum = 0;
                sw.createCell(cellNum, footer.replaceAll("&", "&amp;"), styleDefaultCell.getIndex());
                sw.endRow();
                rowNum += 1;
            }

            if (Globals.getShowDisclaimer()) {
                rowNum += 1;
                sw.insertRow(rowNum);
                cellNum = 0;

                sw.createCell(cellNum,
                        org.onap.portalsdk.analytics.system.Globals.getFooterFirstLine().replaceAll("&", "&amp;"),
                        styleDefaultCell.getIndex());
                sw.endRow();
                rowNum += 1;
                sw.insertRow(rowNum);
                cellNum = 0;
                sw.createCell(cellNum,
                        org.onap.portalsdk.analytics.system.Globals.getFooterSecondLine().replaceAll("&", "&amp;"),
                        styleDefaultCell.getIndex());
                sw.endRow();
            }

        } else {

            int rowCount = 0;
            DataRow dr = null;
            for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext();) {
                rowCount++;

                dr = rd.reportDataRows.getNext();
                sw.insertRow(rowNum);

                cellNum = -1;

                if (rr.getReportType().equals(AppConstants.RT_LINEAR) && rd.reportTotalRowHeaderCols != null) {
                    rd.reportRowHeaderCols.resetNext(0);
                    if (rd.reportTotalRowHeaderCols != null) {
             
                    }

                }
                firstPass = false;
                int j = 0;

                for (dr.resetNext(); dr.hasNext(); j++) {
                    DataValue dv = dr.getNext();
                    styleCell = null;
                    boolean bold = false;
                    String value = nvl(dv.getDisplayValue());
                    value = strip.stripHtml(value);
                    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));

                    if (dv.isVisible()) {
                        cellNum += 1;
                       String dataType = (String) (dataTypeMap.get(dv.getColId()));
                       
                        if (dataType != null && dataType.equals("NUMBER")) {
                           int zInt = 0;
                            if (value.equals("null")) {
                                sw.createCell(cellNum, zInt,
                                        styles.get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                            } else {

                                if ((value.indexOf(".")) != -1) {
                                    if ((value.trim().startsWith("$")) || (value.trim().startsWith("-$"))) {

                                        String tempDollar = value.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 (styleRowCell != null)
                                                sw.createCell(cellNum, tempDoubleDollar, styleRowCell.getIndex());
                                            else if (styleCell != null)
                                                sw.createCell(cellNum, tempDoubleDollar, styleCell.getIndex());
                                            else
                                                sw.createCell(cellNum, tempDoubleDollar, styles
                                                        .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                        } catch (NumberFormatException ne) {
                                            if (styleRowCell != null)
                                                sw.createCell(cellNum, Utils.excelEncode(tempDollar),
                                                        styleRowCell.getIndex());
                                            else if (styleCell != null)
                                                sw.createCell(cellNum, Utils.excelEncode(tempDollar),
                                                        styleCell.getIndex());
                                            else
                                                sw.createCell(cellNum, Utils.excelEncode(tempDollar), styles
                                                        .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                        }
                                    } else {
                                        double tempDouble = 0.0;
                                        try {
                                            tempDouble = Double.parseDouble(value);
                                            if (styleRowCell != null)
                                                sw.createCell(cellNum, tempDouble, styleRowCell.getIndex());
                                            else if (styleCell != null)
                                                sw.createCell(cellNum, tempDouble, styleCell.getIndex());
                                            else
                                                sw.createCell(cellNum, tempDouble, styles
                                                        .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                        } catch (NumberFormatException ne) {
                                            if (styleRowCell != null)
                                                sw.createCell(cellNum, Utils.excelEncode(value),
                                                        styleRowCell.getIndex());
                                            else if (styleCell != null)
                                                sw.createCell(cellNum, Utils.excelEncode(value), styleCell.getIndex());
                                            else
                                                sw.createCell(cellNum, Utils.excelEncode(value), styles
                                                        .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                        }

                                    }
                                } else {
                                    if (!(value.equals(""))) {
                                        if ((value.trim().startsWith("$")) || (value.trim().startsWith("-$"))) {
                                           String tempInt = value.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 (styleRowCell != null)
                                                    sw.createCell(cellNum, tempIntDollar, styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, tempIntDollar, styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, tempIntDollar, styles
                                                            .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                            } catch (NumberFormatException ne) {
                                                if (styleRowCell != null)
                                                    sw.createCell(cellNum, tempInt, styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, tempInt, styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, tempInt, styles
                                                            .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                            }
                                        } else {
                                            String tempStr = value.trim();
                                            if ((tempStr.indexOf(",")) != -1) {
                                                tempStr = tempStr.replaceAll(",", "");
                                            }
                                            Long temp = 0L;

                                            try {
                                                temp = Long.parseLong(tempStr);
                                                if (styleRowCell != null)
                                                    sw.createCell(cellNum, temp, styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, temp, styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, temp, styles
                                                            .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                            } catch (NumberFormatException ne) {
                                                if (styleRowCell != null)
                                                    sw.createCell(cellNum, Utils.excelEncode(tempStr),
                                                            styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, Utils.excelEncode(tempStr),
                                                            styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, Utils.excelEncode(tempStr), styles
                                                            .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                            }

                                        }
        
                                    }
                                }
                            }

                        } 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"))) {
                            Date date = null;
                            int flag = 0;
                            date = MMDDYYHHMMSSFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MMDDYYHHMMFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MMDDYYFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MMDDYYYYHHMMSSFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MMDDYYYYHHMMFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MMDDYYYYFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = YYYYMMDDFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = timestampFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MONYYYYFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MMYYYYFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MMMMMDDYYYYFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MONTHYYYYFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = YYYYMMDDHHMMSSFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = YYYYMMDDDASHFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = YYYYMMDDHHMMFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = DDMONYYYYHHMMSSFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = DDMONYYYYHHMMFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = DDMONYYHHMMFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = DDMONYYYYFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MMDDYYHHMMSSFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MMDDYYHHMMFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MMDDYYFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MMDDYYHHMMFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MMDDYYHHMMSSFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MMDDYYYYHHMMZFormat.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }
                            if (date == null)
                                date = MMMMMDDYYYYHHMMSS.parse(value, new ParsePosition(0));
                            if (date != null && flag == 0) {
                                flag = 1;
                            }

                            if (date != null) {
                                Calendar cal = Calendar.getInstance();
                                cal.setTime(date);
                                if (styleRowCell != null)
                                    sw.createCell(cellNum, cal, styleRowCell.getIndex());
                                else if (styleCell != null)
                                    sw.createCell(cellNum, cal, styleCell.getIndex());
                                else
                                    sw.createCell(cellNum, cal,
                                            styles.get(nvl(/* dv.getFormatId() */"", "date")).getIndex());

                            } else {
                                if (styleRowCell != null)
                                    sw.createCell(cellNum, Utils.excelEncode(value), styleRowCell.getIndex());
                                else if (styleCell != null)
                                    sw.createCell(cellNum, Utils.excelEncode(value), styleCell.getIndex());
                                else
                                    sw.createCell(cellNum, Utils.excelEncode(value),
                                            styles.get(nvl(/* dv.getFormatId(), */"", "date")).getIndex());

                            }
                         
                        } else if ((dv.getDisplayTotal() != null && dv.getDisplayTotal().equals("SUM("))
                                || (dv.getColName() != null && dv.getColName().indexOf("999") != -1)) {
                            int zInt = 0;
                            if (value.equals("null")) {
                                if (styleRowCell != null)
                                    sw.createCell(cellNum, zInt, styleRowCell.getIndex());
                                else if (styleCell != null)
                                    sw.createCell(cellNum, zInt, styleCell.getIndex());
                                else
                                    sw.createCell(cellNum, zInt, styleDefaultCell.getIndex());
                            } else {

                                if ((value.indexOf(".")) != -1) {
                                    if ((value.trim().startsWith("$")) || (value.trim().startsWith("-$"))) {

                                        String tempDollar = value.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 (styleRowCell != null)
                                                sw.createCell(cellNum, tempDoubleDollar, styleRowCell.getIndex());
                                            else if (styleCell != null)
                                                sw.createCell(cellNum, tempDoubleDollar, styleCell.getIndex());
                                            else
                                                sw.createCell(cellNum, tempDoubleDollar, styles
                                                        .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                        } catch (NumberFormatException ne) {
                                            if (styleRowCell != null)
                                                sw.createCell(cellNum, Utils.excelEncode(tempDollar),
                                                        styleRowCell.getIndex());
                                            else if (styleCell != null)
                                                sw.createCell(cellNum, Utils.excelEncode(tempDollar),
                                                        styleCell.getIndex());
                                            else
                                                sw.createCell(cellNum, Utils.excelEncode(tempDollar), styles
                                                        .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                        }

                                    } else {
                                        String tempDoubleStr = value.trim();
                                        tempDoubleStr = tempDoubleStr.replaceAll(" ", "").substring(0);
                                        if ((tempDoubleStr.indexOf(",")) != -1) {
                                            tempDoubleStr = tempDoubleStr.replaceAll(",", "");
                                        }
                                        double tempDouble = 0.0;
                                        try {
                                            tempDouble = Double.parseDouble(tempDoubleStr);
                                            if (styleRowCell != null)
                                                sw.createCell(cellNum, tempDouble, styleRowCell.getIndex());
                                            else if (styleCell != null)
                                                sw.createCell(cellNum, tempDouble, styleCell.getIndex());
                                            else
                                                sw.createCell(cellNum, tempDouble, styles
                                                        .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                        } catch (NumberFormatException ne) {
                                            if (styleRowCell != null)
                                                sw.createCell(cellNum, Utils.excelEncode(tempDoubleStr),
                                                        styleRowCell.getIndex());
                                            else if (styleCell != null)
                                                sw.createCell(cellNum, Utils.excelEncode(tempDoubleStr),
                                                        styleCell.getIndex());
                                            else
                                                sw.createCell(cellNum, Utils.excelEncode(tempDoubleStr), styles
                                                        .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                        }
                                    }

                                } else {
                                    if (!(value.equals(""))) {
                                        if ((value.trim().startsWith("$")) || (value.trim().startsWith("-$"))) {
                                            String tempInt = value.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 (styleRowCell != null)
                                                    sw.createCell(cellNum, tempIntDollar, styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, tempIntDollar, styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, tempIntDollar, styles
                                                            .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                            } catch (NumberFormatException ne) {
                                                if (styleRowCell != null)
                                                    sw.createCell(cellNum, Utils.excelEncode(tempInt),
                                                            styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, Utils.excelEncode(tempInt),
                                                            styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, Utils.excelEncode(tempInt), styles
                                                            .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                            }
                                        } else {
                                            String tempStr = value.trim();
                                            if ((tempStr.indexOf(",")) != -1) {
                                                tempStr = tempStr.replaceAll(",", "");
                                            }
                                            Long temp = 0L;

                                            try {
                                                temp = Long.parseLong(tempStr);
                                                if (styleRowCell != null)
                                                    sw.createCell(cellNum, temp, styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, temp, styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, temp, styles
                                                            .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                            } catch (NumberFormatException ne) {
                                                if (styleRowCell != null)
                                                    sw.createCell(cellNum, Utils.excelEncode(tempStr),
                                                            styleRowCell.getIndex());
                                                else if (styleCell != null)
                                                    sw.createCell(cellNum, Utils.excelEncode(tempStr),
                                                            styleCell.getIndex());
                                                else
                                                    sw.createCell(cellNum, Utils.excelEncode(tempStr), styles
                                                            .get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                            }
                                        }
 
                                    } else {
                                        sw.createCell(cellNum, "",
                                                styles.get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                                    }
                                }
                            }

                        } else {
                            if (styleRowCell != null)
                                sw.createCell(cellNum, strip.stripHtml(Utils.excelEncode(value)),
                                        styleRowCell.getIndex());
                            else if (styleCell != null)
                                sw.createCell(cellNum, strip.stripHtml(Utils.excelEncode(value)), styleCell.getIndex());
                            else
                                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;
                        }

                        if ((dr.isRowFormat() && !dv.isCellFormat()) && styles != null) {
                            continue;
                        }
                        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) {

                for (rd.reportDataTotalRow.resetNext(); rd.reportDataTotalRow.hasNext();) {
                    rowCount++;
                    sw.insertRow(rowNum);
                    cellNum = -1;
                    cellNum += 1;

                    RowHeaderCol rhc = rd.reportTotalRowHeaderCols.getNext();
                    RowHeader rh = rhc.getRowHeader(0);
                    if (dr.isRowFormat() && styles != null)
                        styleRowCell = (XSSFCellStyle) styles.get(nvl(/* dr.getFormatId(), */"", DEFAULT));
                    if (styleRowCell != null)
                        sw.createCell(cellNum, strip.stripHtml(rh.getRowTitle()), styleRowCell.getIndex());
                    else
                        sw.createCell(cellNum, strip.stripHtml(rh.getRowTitle()), styleDefaultCell.getIndex());

                    DataRow drTotal = rd.reportDataTotalRow.getNext();
                    if (drTotal != null) {
                        drTotal.resetNext();
                        drTotal.getNext();
                        for (; drTotal.hasNext();) {
                            cellNum += 1;
                            styleCell = null;
                            DataValue dv = drTotal.getNext();
                            String value = dv.getDisplayValue();
                            sw.createCell(cellNum, value,
                                    styles.get(nvl(/* dv.getFormatId(), */"", DEFAULT)).getIndex());
                        }
                    }

                    rowNum += 1;
                    sw.endRow();
                }

                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());
                    rowNum += 1;
                    sw.insertRow(rowNum);
                    cellNum = 0;
                    sw.createCell(cellNum, footer.replaceAll("&", "&amp;"), styleDefaultCell.getIndex());
                    sw.endRow();
                    rowNum += 1;
                }

                if (Globals.getShowDisclaimer()) {
                    rowNum += 1;
                    sw.insertRow(rowNum);
                    cellNum = 0;

                    sw.createCell(cellNum,
                            org.onap.portalsdk.analytics.system.Globals.getFooterFirstLine().replaceAll("&", "&amp;"),
                            styleDefaultCell.getIndex());
                    sw.endRow();
                    rowNum += 1;
                    sw.insertRow(rowNum);
                    cellNum = 0;
                    sw.createCell(cellNum,
                            org.onap.portalsdk.analytics.system.Globals.getFooterSecondLine().replaceAll("&", "&amp;"),
                            styleDefaultCell.getIndex());
                    sw.endRow();
                }

            }
            // 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.GREY_25_PERCENT.index);
        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.BLACK.index);
        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.BLACK.index);
        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 {
  
        PrintWriter csvOut = response.getWriter();
        HtmlStripper strip = new HtmlStripper();
        ResultSet rs = null;
        Connection conn = null;
        Statement st = null;
        ResultSetMetaData rsmd = null;
        ColumnHeaderRow chr = null;
        int mb = 1024 * 1024;
        Runtime runtime = Runtime.getRuntime();
        csvOut.println("<HTML>\n" +
                "<HEAD><TITLE>" + rr.getReportName() + "</TITLE></HEAD>\n" +
                "<BODY>\n");
        System.out.println("HTML-Excel Generation Triggered: " + new java.util.Date());
        csvOut.print("<TABLE>");
        if (Globals.getPrintParamsInCSVDownload()) {
            ArrayList paramsList = rr.getParamNameValuePairsforPDFExcel(request, 1);
            int paramSeq = 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) {
                        csvOut.println("<TR><TD COLSPAN=\"2\">" + RUNTIME_PARAMETERS + "</TD></TR>");
                    }
                    csvOut.println("<TR><TD>" + value.getId() + "</TD>");
                    csvOut.println("<TD>" + value.getName().replaceAll("~", ",") + "</TD>");
                    csvOut.println("</TR>");

                }
            } // for
            csvOut.println("<TR><TD COLSPAN=\"2\"> &nbsp;</TD></TR>");
            csvOut.println("<TR><TD COLSPAN=\"2\"> &nbsp;</TD></TR>");
            System.out.println("HTML-Excel: Header Rendering complete " + new java.util.Date());
        }
        int rowCount = 0;
        if (nvl(sql_whole).length() > 0) {
            try {
                conn = ConnectionUtils.getConnection(rr.getDbInfo());
                st = conn.createStatement();
                Log.write("[SQL] " + sql_whole, 4);
                int downloadLimit = Globals.getDownloadLimit();
                Callable<ResultSet> callable = new ExecuteQuery(st, sql_whole, downloadLimit);
                ExecutorService executor = new ScheduledThreadPoolExecutor(5);
                System.out.println("Time Started" + new java.util.Date());
                Future<ResultSet> future = executor.submit(callable);
                try {
                    rs = future.get(900, TimeUnit.SECONDS);
                } catch (TimeoutException ex) {
                    logger.error(EELFLoggerDelegate.errorLogger, "TimeoutException occured", ex);
                    logger.debug(EELFLoggerDelegate.debugLogger, "Cancelling Query");
                    st.cancel();
                    logger.debug(EELFLoggerDelegate.debugLogger, "Query Cancelled");
                    throw new Exception("user requested");
                }
                rsmd = rs.getMetaData();
                int numberOfColumns = rsmd.getColumnCount();
                HashMap colHash = new HashMap();

                if (rd != null) {
                    for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext();) {
                        chr = rd.reportColumnHeaderRows.getNext();
                        csvOut.println("<TR>");
                        if (chr != null) {
                            for (chr.resetNext(); chr.hasNext();) {
                                ColumnHeader ch = chr.getNext();
                                if (ch.isVisible()) {
                                    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++;
                        colHash = new HashMap();
                        for (int i = 1; i <= numberOfColumns; i++) {
                            colHash.put(rsmd.getColumnName(i), rs.getString(i));
                        }
                        if (chr != null) {
                            for (chr.resetNext(); chr.hasNext();) {
                                ColumnHeader ch = chr.getNext();
                                if (ch.isVisible()) {
                                    csvOut.println("<TD>"
                                            + strip.stripCSVHtml(
                                                    nvl((String) colHash.get(ch.getLinkColId().toUpperCase())))
                                            + "</TD>");
                                }

                            }
                        }
                        csvOut.println("</TR>");
                    }
                    System.out.println("Downloaded Rows in HTML-Excel " + rowCount + " : " + new java.util.Date());
                    if (rowCount == 0) {
                        csvOut.print("<TR><TD COLSPAN=\"" + numberOfColumns + "\">No Data Found</TD></TR>");
                    } else {
                    }
                } else {
                    csvOut.println("<TR><TD COLSPAN=\"" + numberOfColumns + "\">No Data Found</TD></TR>");
                }
                csvOut.println("</TABLE></BODY>\n</HTML>");

            } catch (Exception ex) {
                logger.error(EELFLoggerDelegate.errorLogger, "Exception occured", ex);
                throw new RaptorException(ex);
            } finally {
                try {
                    if (conn != null)
                        conn.close();
                    if (st != null)
                        st.close();
                    if (rs != null)
                        rs.close();
                } catch (SQLException ex) {
                    logger.error(EELFLoggerDelegate.errorLogger, "SQLException occured", ex);
                }
            }
        } else {
            boolean firstPass = true;
            int numberOfColumns = 0;
            if (rd != null) {
                for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext();) {
                    chr = rd.reportColumnHeaderRows.getNext();
                    csvOut.println("<TR>");
                    for (rd.reportRowHeaderCols.resetNext(1); rd.reportRowHeaderCols.hasNext();) {
                        RowHeaderCol rhc = rd.reportRowHeaderCols.getNext();

                        if (firstPass) {
                            numberOfColumns++;
                            csvOut.print("<TD bgColor=\"8F9381\">" + rhc.getColumnTitle() + "</TD>");
                        }
                    } // for

                    for (chr.resetNext(); chr.hasNext();) {
                        ColumnHeader ch = chr.getNext();
                        if (ch.isVisible()) {
                            if (firstPass)
                                numberOfColumns++;
                            csvOut.print("<TD bgColor=\"8F9381\">" + ch.getColumnTitle() + "</TD>");
                        }
                    } // for
                    firstPass = false;
                    csvOut.println("</TR>");
                } // for

                firstPass = true;
                for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext();) {
                    DataRow dr = rd.reportDataRows.getNext();
                    csvOut.println("<TR>");
                    for (rd.reportRowHeaderCols.resetNext(1); rd.reportRowHeaderCols.hasNext();) {
                        RowHeaderCol rhc = rd.reportRowHeaderCols.getNext();
                        if (firstPass)
                            rhc.resetNext();
                        RowHeader rh = rhc.getNext();

                        csvOut.print("<TD bgColor=\"8F9381\">" + strip.stripCSVHtml(rh.getRowTitle()) + "</TD>");
                    } // for
                    firstPass = false;

                    for (dr.resetNext(); dr.hasNext();) {
                        DataValue 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>");
            }
        }
        csvOut.println("</TABLE></BODY>\n</HTML>");
        System.out.println("HTML-Excel Generation: Data Rendering complete " + new java.util.Date());
        System.out.println("##### Heap utilization statistics [MB] #####");
        System.out.println("Used Memory:"
                + (runtime.maxMemory() - runtime.freeMemory()) / mb);
        System.out.println("Free Memory:"
                + runtime.freeMemory() / mb);
        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
     *
     * @param sheet in which check is performed
     * @param region to check
     * @param cellStyle cell style to apply for whole region
     */
    private void cleanBeforeMergeOnValidCells(XSSFSheet sheet, CellRangeAddress region, XSSFCellStyle cellStyle) {
        for (int rowNum = region.getFirstRow(); rowNum <= region.getLastRow(); rowNum++) {
            XSSFRow row = sheet.getRow(rowNum);
            if (row == null) {
                sheet.createRow(rowNum);
            }
            if (row != null) {
                for (int colNum = region.getFirstColumn(); colNum <= region.getLastColumn(); colNum++) {
                    XSSFCell currentCell = row.getCell(colNum);
                    if (currentCell == null) {
                        currentCell = row.createCell(colNum);
                    }
                    currentCell.setCellStyle(cellStyle);
                }
            }
        }
    }
} // ReportHandler