/*-
* ================================================================================
* eCOMP Portal SDK
* ================================================================================
* Copyright (C) 2017 AT&T Intellectual Property
* ================================================================================
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file 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.
* ================================================================================
*/
/* ===========================================================================================
* This class is part of RAPTOR (Rapid Application Programming Tool for OLAP Reporting)
* 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);- request Object is passed to prevent caching user/roles - Datamining/Hosting.
* 14-Jul-2009 : Version 8.4 (Sundar); - Signature for generating excel method has been changed to add the report name as sheet name.
* - Dashboard reports can be downloaded with each report as a separate sheet.
*
* 08-Jun-2009 : Version 8.3 (Sundar); - Short datatype is replaced with default integer datatype to create
* row as short is not expoting more than 32768 rows.
*
*/
package org.openecomp.portalsdk.analytics.model;
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.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.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
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.openecomp.portalsdk.analytics.controller.ErrorHandler;
import org.openecomp.portalsdk.analytics.error.RaptorException;
import org.openecomp.portalsdk.analytics.error.ReportSQLException;
import org.openecomp.portalsdk.analytics.model.base.IdNameValue;
import org.openecomp.portalsdk.analytics.model.definition.ReportDefinition;
import org.openecomp.portalsdk.analytics.model.runtime.ReportRuntime;
import org.openecomp.portalsdk.analytics.system.AppUtils;
import org.openecomp.portalsdk.analytics.system.ConnectionUtils;
import org.openecomp.portalsdk.analytics.system.ExecuteQuery;
import org.openecomp.portalsdk.analytics.system.Globals;
import org.openecomp.portalsdk.analytics.util.AppConstants;
import org.openecomp.portalsdk.analytics.util.DataSet;
import org.openecomp.portalsdk.analytics.util.ExcelColorDef;
import org.openecomp.portalsdk.analytics.util.HtmlStripper;
import org.openecomp.portalsdk.analytics.util.Log;
import org.openecomp.portalsdk.analytics.util.Utils;
import org.openecomp.portalsdk.analytics.view.ColumnHeader;
import org.openecomp.portalsdk.analytics.view.ColumnHeaderRow;
import org.openecomp.portalsdk.analytics.view.DataRow;
import org.openecomp.portalsdk.analytics.view.DataValue;
import org.openecomp.portalsdk.analytics.view.HtmlFormatter;
import org.openecomp.portalsdk.analytics.view.ReportData;
import org.openecomp.portalsdk.analytics.view.RowHeader;
import org.openecomp.portalsdk.analytics.view.RowHeaderCol;
import org.openecomp.portalsdk.analytics.xmlobj.DataColumnType;
import org.openecomp.portalsdk.analytics.xmlobj.DataSourceType;
import org.openecomp.portalsdk.analytics.xmlobj.FormatList;
import org.openecomp.portalsdk.analytics.xmlobj.FormatType;
import org.openecomp.portalsdk.analytics.xmlobj.Reports;
import org.openecomp.portalsdk.analytics.xmlobj.SemaphoreList;
import org.openecomp.portalsdk.analytics.xmlobj.SemaphoreType;
import org.openecomp.portalsdk.core.logging.logic.EELFLoggerDelegate;
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 javax.servlet.RequestDispatcher;
public class ReportHandler extends org.openecomp.portalsdk.analytics.RaptorObject {
EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(ReportHandler.class);
public ReportHandler() {
}
private String SHEET_NAME = "";
private static final String XML_ENCODING = "UTF-8";
private static int font_size = 10;
private static int font_header_title_size = 12;
private static int font_header_descr_size = 9;
private static int font_footer_size = 9;
private HashMap loadStyles(ReportRuntime rr, HSSFWorkbook wb) {
HSSFCellStyle styleDefault = wb.createCellStyle();
//System.out.println("Load Styles");
// Style default will be normal with no background
HSSFFont fontDefault = wb.createFont();
// The default will be plain .
fontDefault.setColor((short) HSSFFont.COLOR_NORMAL);
fontDefault.setFontHeight((short) (font_size / 0.05));
fontDefault.setFontName("Tahoma");
styleDefault.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleDefault.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleDefault.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleDefault.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleDefault.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleDefault.setFillPattern(HSSFCellStyle.NO_FILL);
styleDefault.setFont(fontDefault);
HSSFCellStyle styleRed = wb.createCellStyle();
styleRed.cloneStyleFrom(styleDefault);
styleRed.setFillForegroundColor((short)HSSFColor.RED.index);
styleRed.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont fontRed = wb.createFont();
fontRed.setColor((short) HSSFColor.WHITE.index);
fontRed.setFontHeight((short) (font_size / 0.05));
fontRed.setFontName("Tahoma");
styleRed.setFont(fontRed);
HSSFCellStyle styleYellow = wb.createCellStyle();
styleYellow.cloneStyleFrom(styleDefault);
styleYellow.setFillForegroundColor((short)HSSFColor.YELLOW.index);
styleYellow.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont fontYellow = wb.createFont();
fontYellow.setColor((short) HSSFColor.BLACK.index);
fontYellow.setFontHeight((short) (font_size / 0.05));
fontYellow.setFontName("Tahoma");
styleYellow.setFont(fontYellow);
HSSFCellStyle styleGreen = wb.createCellStyle();
styleGreen.cloneStyleFrom(styleDefault);
styleGreen.setFillForegroundColor((short)HSSFColor.GREEN.index);
styleGreen.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont fontGreen = wb.createFont();
fontGreen.setColor((short) HSSFColor.WHITE.index);
fontGreen.setFontHeight((short) (font_size / 0.05));
fontGreen.setFontName("Tahoma");
styleGreen.setFont(fontGreen);
ArrayList semColumnList = new ArrayList();
List dsList = rr.getDataSourceList().getDataSource();
for (Iterator iter = dsList.iterator(); iter.hasNext();) {
DataSourceType element = (DataSourceType) iter.next();
List dcList = element.getDataColumnList().getDataColumn();
for (Iterator iterator = dcList.iterator(); iterator.hasNext();) {
DataColumnType element1 = (DataColumnType) iterator.next();
semColumnList.add(element1.getSemaphoreId());
}
}
SemaphoreList semList = rr.getSemaphoreList();
HashMap hashMapStyles = new HashMap();
HashMap hashMapFonts = new HashMap();
hashMapFonts.put("default", fontDefault);
hashMapFonts.put("red", fontRed);
hashMapFonts.put("yellow", fontYellow);
hashMapFonts.put("green", fontGreen);
hashMapStyles.put("default", styleDefault);
hashMapStyles.put("red", styleRed);
hashMapStyles.put("yellow", styleYellow);
hashMapStyles.put("green", styleGreen);
HSSFCellStyle cellStyle = null;
if (semList == null || semList.getSemaphore() == null) {
return hashMapStyles;
} else {
for (Iterator iter = semList.getSemaphore().iterator(); iter.hasNext();) {
SemaphoreType sem = (SemaphoreType) iter.next();
if(!semColumnList.contains(sem.getSemaphoreId())) continue;
//System.out.println("SemphoreId ----> " + sem.getSemaphoreId());
FormatList fList = sem.getFormatList();
List formatList = fList.getFormat();
for (Iterator fIter = formatList.iterator(); fIter.hasNext();) {
FormatType fmt = (FormatType) fIter.next();
if(fmt!=null){
//if (fmt.getLessThanValue().length() > 0) {
cellStyle = wb.createCellStyle();
HSSFFont cellFont = wb.createFont();
//System.out.println("Format Id " + fmt.getFormatId());
if (nvl(fmt.getBgColor()).length() > 0) {
// System.out.println("Load Styles " +
// fmt.getFormatId()
// + " " +fmt.getBgColor() + " " +
// ExcelColorDef.getExcelColor(fmt.getBgColor()));
cellStyle.setFillForegroundColor(ExcelColorDef.getExcelColor(fmt
.getBgColor()));
cellStyle.setFillPattern(HSSFCellStyle.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.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
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("Tahoma");
//cellFont.setFontHeight((short) (10 / 0.05));
if(nvl(fmt.getFontSize()).length()>0) {
try {
//cellFont.setFontHeight((short) (Integer.parseInt(fmt.getFontSize()) / 0.05));
cellFont.setFontHeight((short) (font_size/0.05));
} catch(NumberFormatException e){
cellFont.setFontHeight((short) (font_size / 0.05));//10
}
}
else
cellFont.setFontHeight((short) (font_size / 0.05));
cellStyle.setFont(cellFont);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
hashMapStyles.put(fmt.getFormatId(), cellStyle);
} else {
hashMapStyles.put(fmt.getFormatId(), styleDefault);
hashMapStyles.put("default", styleDefault);
}
}
}
}
return hashMapStyles;
}
private void paintExcelParams(HSSFWorkbook wb,int rowNum,int col,ArrayList paramsList, String customizedParamInfo, HSSFSheet sheet, String reportTitle, String reportDescr) throws IOException {
//HSSFSheet sheet = wb.getSheet(getSheetName());
int cellNum = 0;
HSSFRow row = null;
short s1 = 0, s2 = (short) 1;
HtmlStripper strip = new HtmlStripper();
// Name Style
HSSFCellStyle styleName = wb.createCellStyle();
//styleName.setFillBackgroundColor(HSSFColor.GREY_80_PERCENT.index);
styleName.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
//styleName.setFillPattern(HSSFCellStyle.SPARSE_DOTS);
styleName.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleName.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleName.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleName.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleName.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleName.setDataFormat((short)0);
HSSFFont font = wb.createFont();
font.setFontHeight((short) (font_size / 0.05));
font.setFontName("Tahoma");
font.setColor(HSSFColor.BLACK.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
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("Tahoma");
fontDefault.setItalic(true);
// Style default will be normal with no background
HSSFCellStyle styleValue = wb.createCellStyle();
styleValue.setDataFormat((short)0);
styleValue.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleValue.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleValue.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleValue.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleValue.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleValue.setFillForegroundColor(HSSFColor.YELLOW.index);
styleValue.setFillPattern(HSSFCellStyle.NO_FILL);
styleValue.setFont(fontDefault);
HSSFCell cell = null;
HSSFCellStyle styleDescription = wb.createCellStyle();
styleDescription.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// styleDescription.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// styleDescription.setBorderTop(HSSFCellStyle.BORDER_THIN);
// styleDescription.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDescription.setBorderLeft(HSSFCellStyle.BORDER_THIN);
HSSFFont fontDescr = wb.createFont();
fontDescr.setFontHeight((short) (font_size / 0.05)); //14
fontDescr.setFontName("Tahoma");
fontDescr.setColor(HSSFColor.BLACK.index);
fontDescr.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
styleDescription.setFont(font);
HSSFCell cellDescr = null;
int paramSeq = 0;
HSSFHeader 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();
//System.out.println("\"" + value.getId() + " = " + value.getName() + "\"");
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 Region(rowNum, s1, rowNum, s2));
cellDescr = row.createCell((short) 0);
cellDescr.setCellValue("Run-time Parameters");
cellDescr.setCellStyle(styleDescription);
strBuf.append(reportTitle+"\n");
//strBuf.append("Run-time Parameters\n");
}
row = sheet.createRow(++rowNum);
cellNum = 0;
//System.out.println("RowNum " + rowNum + " " + value.getId() + " " +value.getName());
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);
//strBuf.append(value.getId()+": "+ value.getName()+"\n");
}
} //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 { /*if ((p.get(k) instanceof PdfPTable))*/
pdfTable = ((PdfPTable)p.get(k));
}
//todo: Logic for parsing pdfTable should be added after upgrading to iText 5.0.0
//s = Utils.replaceInString(s, ",", "|");
s = s.replaceAll(",", "|");
s = s.replaceAll("~", ",");
if(s.indexOf(":")!= -1) {
//System.out.println("|"+s+"|");
row = sheet.createRow(++rowNum);
cell = row.createCell((short) 0);
sheet.addMergedRegion(new Region(rowNum, s1, rowNum, s2));
cellDescr = row.createCell((short) 0);
cellDescr.setCellValue("Run-time Parameters");
cellDescr.setCellStyle(styleDescription);
//strBuf.append("Run-time Parameters\n");
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);
} /*else if(name != null && name.length() > 0) {
value = st.nextToken();
if(value.endsWith("]]"))value = value.substring(0, 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);
//strBuf.append(name.trim()+": "+ value.trim()+"\n");
}
/* 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(i!=cellWidth.size()-1)
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) {
//ex.printStackTrace();
}
}
}
/* 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 Region(rowNum, s1, rowNum, s2));
cell.setCellValue(strip.stripHtml(customizedParamInfo));
}
*/
/* rowNum += 2;
row = sheet.createRow(rowNum);*/
} // if
Iterator iterCheck = paramsList.iterator();
if(iterCheck.hasNext()) {
rowNum += 2;
row = sheet.createRow(rowNum);
}
header.setCenter(HSSFHeader.font("Tahoma", "")+ HSSFHeader.fontSize((short) 9)+" " + strBuf.toString());
HSSFFooter footer = sheet.getFooter();
footer.setLeft(HSSFFooter.font("Tahoma", "")+ HSSFFooter.fontSize((short) 9)+ "Page " + HSSFFooter.page()
+ " of " + HSSFFooter.numPages() );
footer.setCenter(HSSFFooter.font("Tahoma", "")+ HSSFFooter.fontSize((short) 9)+Globals.getFooterFirstLine()+"\n"+Globals.getFooterSecondLine());
}
private int paintExcelData(HSSFWorkbook wb, int rowNum, int col, ReportData rd,
HashMap styles, ReportRuntime rr, HSSFSheet sheet, String sql_whole, OutputStream sos, HttpServletRequest request) throws RaptorException {
int mb = 1024*1024;
Runtime runtime = Runtime.getRuntime();
int returnValue = 0;
// HSSFSheet sheet = wb.getSheetAt(0);
HSSFCellStyle styleDefault = wb.createCellStyle();
HSSFCellStyle styleNumber = wb.createCellStyle();
HSSFCellStyle styleDecimalNumber = wb.createCellStyle();
HSSFCellStyle styleCurrencyNumber = wb.createCellStyle();
HSSFCellStyle styleCurrencyDecimalNumber = wb.createCellStyle();
HSSFCellStyle styleDate = wb.createCellStyle();
HtmlStripper strip = new HtmlStripper();
//HSSFSheet sheet = wb.getSheet(getSheetName());
HSSFCellStyle styleDataHeader = wb.createCellStyle();
// style.setFillBackgroundColor(HSSFColor.AQUA.index);
styleDataHeader.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
styleDataHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleDataHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleDataHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleDataHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleDataHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleDataHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);
HSSFFont font = wb.createFont();
font.setFontHeight((short) (font_size / 0.05));
font.setFontName("Tahoma");
font.setColor(HSSFColor.BLACK.index);
styleDataHeader.setFont(font);
// Column Header
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 = "";
// System.out.println("***************** Size " + rd.reportColumnHeaderRows.size());
// for (int i = 0; i < rd.reportColumnHeaderRows.size(); i++) {
// for (int j = 0; j < rd.reportColumnHeaderRows.getColumnHeaderRow(i).size(); j++) {
// System.out.println("Column Title " + rd.reportColumnHeaderRows.getColumnHeaderRow(i).getColumnHeader(j).getColumnTitle()
// + " " + rd.reportColumnHeaderRows.getColumnHeaderRow(i).getColumnHeader(j).isVisible());
// }
// }
/* List dsList = rr.getDataSourceList().getDataSource();
HashMap dataColumnTypeHashMap = new HashMap();
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();
dataTypeMap.put(element1.getColId(), element1.getColType());
dataColumnTypeHashMap.put(element1.getColName(), element1);
}
}
*/
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, "
", " ");
header = Utils.replaceInString(header, "
", " ");
header = Utils.replaceInString(header, "
", " ");
header = strip.stripHtml(nvl(header).trim());
subtitle = Utils.replaceInString(subtitle, "
", " ");
subtitle = Utils.replaceInString(subtitle, "
", " ");
subtitle = Utils.replaceInString(subtitle, "
", " ");
subtitle = strip.stripHtml(nvl(subtitle).trim());
HSSFRow row = sheet.createRow(rowNum);
cellNum = 0;
row.createCell((short) cellNum).setCellValue(header);
sheet.addMergedRegion(new Region(rowNum, (short) cellNum, rowNum, (short) (columnRows)));
rowNum += 1;
row = sheet.createRow(rowNum);
cellNum = 0;
row.createCell((short) cellNum).setCellValue(subtitle);
sheet.addMergedRegion(new Region(rowNum, (short) cellNum, rowNum, (short) (columnRows)));
rowNum += 1;
}
for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext();) {
HSSFRow row = sheet.createRow(rowNum);
cellNum = -1;
/*if(rd.reportTotalRowHeaderCols!=null) {
cellNum +=1;
row.createCell((short) cellNum).setCellValue("Total");
row.createCell((short) cellNum).setCellStyle(styleDataHeader);
//row.getCell((short) cellNum).setCellStyle(styleDataHeader);
}*/
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);
//commented after bug reported by EPAT 01/17/2015
//sheet.addMergedRegion(new Region(rowNum, (short) cellNum, rowNum+columnRows, (short) (cellNum)));
//System.out.println(" **************** Row Header Title " + rhc.getColumnTitle() + " " + cellNum + " " );
//System.out.println(cellNum + " " + cellWidth.size());
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;
row.createCell((short) cellNum).setCellValue(ch.getColumnTitle());
// <%= ch.getColSpanHtml() %><%= ch.getRowSpanHtml() %>>
//
// |
} // if
} // for
*/
//cellNum = -1;
// Set mapSet = dataTypeMap.entrySet();
// Map.Entry me;
// String element, value ;
// for (Iterator iter = mapSet.iterator(); iter.hasNext();) {
// me=(Map.Entry)iter.next();
// element = (String) me.getKey();
// value = (String) me.getValue();
// System.out.println("DataTypeMap " + element + " " + value);
// }
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 Region(rowNum, (short) cellNum, rowNum, (short) (cellNum+colSpan)));
}
/* if (cellWidth.size() > cellNum) {
if (((Integer) cellWidth.get(cellNum)).intValue() < ch
.getColumnTitle().length())
cellWidth
.set((cellNum), new Integer(ch.getColumnTitle().length()));
} else
cellWidth.add((cellNum), new Integer(ch.getColumnTitle().length()));
*/ 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;
}
} // for
/* int cw = 0;
for (int i = 0; i < cellWidth.size(); i++) {
cw = ((Integer) cellWidth.get(i)).intValue() + 6;
sheet.setColumnWidth((short) (i), (short) ((cw * 8) / ((double) 1 / 20)));
}
*/
rowNum += 1;
} // for
// Data
// Create some cell styles.
//HSSFCellStyle styleDefault = wb.createCellStyle();
HSSFCellStyle styleCell = null;
HSSFCellStyle styleTotal = wb.createCellStyle();
HSSFCellStyle styleCurrencyTotal = wb.createCellStyle();
HSSFCellStyle styleDefaultTotal = wb.createCellStyle();
HSSFCellStyle styleCurrencyDecimalNumberTotal = wb.createCellStyle();
HSSFCellStyle styleDecimalNumberTotal = wb.createCellStyle();
HSSFCellStyle styleCurrencyNumberTotal = wb.createCellStyle();
// Create some fonts.
HSSFFont fontDefault = wb.createFont();
HSSFFont fontBold = 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("Tahoma");
// The default will be bold black tachoma 10pt text.
fontBold.setColor((short) HSSFFont.COLOR_NORMAL);
fontBold.setFontHeight((short) (font_size / 0.05));
fontBold.setFontName("Tahoma");
fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Style default will be normal with no background
styleDefault.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleDefault.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleDefault.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleDefault.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleDefault.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleDefault.setFillPattern(HSSFCellStyle.NO_FILL);
styleDefault.setFont(fontDefault);
styleDefault.setWrapText(true);
//Number
styleNumber.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleNumber.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleNumber.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleNumber.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleNumber.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleNumber.setFillPattern(HSSFCellStyle.NO_FILL);
styleNumber.setFont(fontDefault);
try {
styleNumber.setDataFormat((short)0x26);//HSSFDataFormat.getBuiltinFormat("(#,##0_);[Red](#,##0)"));
} catch (Exception e) {
}
//Decimal Number
styleDecimalNumber.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleDecimalNumber.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleDecimalNumber.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleDecimalNumber.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleDecimalNumber.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleDecimalNumber.setFillPattern(HSSFCellStyle.NO_FILL);
styleDecimalNumber.setFont(fontDefault);
styleDecimalNumber.setDataFormat((short)0x27);//HSSFDataFormat.getBuiltinFormat("(#,##0.00_);[Red](#,##0.00)"));
//Decimal Number
styleDecimalNumberTotal.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleDecimalNumberTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleDecimalNumberTotal.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleDecimalNumberTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleDecimalNumberTotal.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleDecimalNumberTotal.setFillPattern(HSSFCellStyle.NO_FILL);
styleDecimalNumberTotal.setFont(fontBold);
styleDecimalNumberTotal.setDataFormat((short)0x27);//HSSFDataFormat.getBuiltinFormat("(#,##0.00_);[Red](#,##0.00)"));
//CurrencyNumber
styleCurrencyDecimalNumber.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleCurrencyDecimalNumber.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleCurrencyDecimalNumber.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleCurrencyDecimalNumber.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleCurrencyDecimalNumber.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleCurrencyDecimalNumber.setFillPattern(HSSFCellStyle.NO_FILL);
styleCurrencyDecimalNumber.setFont(fontDefault);
styleCurrencyDecimalNumber.setDataFormat((short)8);//HSSFDataFormat.getBuiltinFormat("($#,##0.00_);[Red]($#,##0.00)"));
//currency number bold
styleCurrencyDecimalNumberTotal.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleCurrencyDecimalNumberTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleCurrencyDecimalNumberTotal.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleCurrencyDecimalNumberTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleCurrencyDecimalNumberTotal.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleCurrencyDecimalNumberTotal.setFillPattern(HSSFCellStyle.NO_FILL);
styleCurrencyDecimalNumberTotal.setFont(fontBold);
styleCurrencyDecimalNumberTotal.setDataFormat((short)8);//HSSFDataFormat.getBuiltinFormat("($#,##0.00_);[Red]($#,##0.00)"));
//CurrencyNumber
styleCurrencyNumber.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleCurrencyNumber.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleCurrencyNumber.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleCurrencyNumber.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleCurrencyNumber.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleCurrencyNumber.setFillPattern(HSSFCellStyle.NO_FILL);
styleCurrencyNumber.setFont(fontDefault);
styleCurrencyNumber.setDataFormat((short) 6);//HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
//CurrencyNumber
styleCurrencyNumberTotal.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleCurrencyNumberTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleCurrencyNumberTotal.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleCurrencyNumberTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleCurrencyNumberTotal.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleCurrencyNumberTotal.setFillPattern(HSSFCellStyle.NO_FILL);
styleCurrencyNumberTotal.setFont(fontBold);
styleCurrencyNumberTotal.setDataFormat((short) 6);//HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
//Date
styleDate.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleDate.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleDate.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleDate.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleDate.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleDate.setFillPattern(HSSFCellStyle.NO_FILL);
styleDate.setFont(fontDefault);
styleDate.setDataFormat((short)0xe);//HSSFDataFormat.getBuiltinFormat("m/d/yy"));
// Style for Total will be Bold with normal font with no background
styleTotal.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleTotal.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleTotal.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleTotal.setFillPattern(HSSFCellStyle.NO_FILL);
styleTotal.setDataFormat((short)0x28);//HSSFDataFormat.getBuiltinFormat("(#,##0.00_);[Red](#,##0.00)"));
styleTotal.setFont(fontBold);
styleCurrencyTotal.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleCurrencyTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleCurrencyTotal.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleCurrencyTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleCurrencyTotal.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleCurrencyTotal.setFillPattern(HSSFCellStyle.NO_FILL);
styleCurrencyTotal.setDataFormat((short)8);//HSSFDataFormat.getBuiltinFormat("($#,##0.00_);[Red]($#,##0.00)"));
styleCurrencyTotal.setFont(fontBold);
styleDefaultTotal.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleDefaultTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleDefaultTotal.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleDefaultTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleDefaultTotal.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleDefaultTotal.setFillPattern(HSSFCellStyle.NO_FILL);
styleDefaultTotal.setDataFormat((short)0x28);
////styleDefaultTotal.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00_);[Red]($#,##0.00)"));
styleDefaultTotal.setFont(fontBold);
firstPass = true;
// Declare a row object reference.
HSSFRow row = null;
// Declare a cell object reference.
HSSFCell cell = null;
//HSSFCell cellNumber = null;
//HSSFCell cellCurrencyNumber = null;
//HSSFCell cellDate = null;
//All the possible combinations of date format
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");
ResultSet rs = null;
Connection conn = null;
Statement st = null;
ResultSetMetaData rsmd = null;
CreationHelper createHelper = wb.getCreationHelper();
if(nvl(sql_whole).length() >0 && rr.getReportType().equals(AppConstants.RT_LINEAR)) {
try {
conn = ConnectionUtils.getConnection(rr.getDbInfo());
st = conn.createStatement();
System.out.println("************* Map Whole SQL *************");
System.out.println(sql_whole);
System.out.println("*****************************************");
rs = st.executeQuery(sql_whole);
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;
//if(rowCount%1000 == 0) wb.write(sos);
/*if(rd.reportTotalRowHeaderCols!=null) {
//cellNum = -1;
//for (rd.reportRowHeaderCols.resetNext(); rd.reportRowHeaderCols.hasNext();) {
cellNum += 1;
//RowHeaderCol rhc = rd.reportRowHeaderCols.getRowHeaderCol(0);
//if (firstPass)
// rhc.resetNext();
//RowHeader rh = rhc.getRowHeader(rowCount-1);
row.createCell((short) cellNum).setCellValue(rowCount);
row.getCell((short) cellNum).setCellStyle(styleDefault);
if (firstPass)
cellWidth.add(cellNum, new Integer((rowCount+"").length()));
else
cellWidth.set(cellNum, new Integer((rowCount+"").length()));
//} // for
}*/
firstPass = false;
//cellNum = -1;
for (dr.resetNext(); dr.hasNext();j++) {
//for (chr.resetNext(); chr.hasNext();) {
//ColumnHeader ch = chr.getNext();
styleCell = null;
DataValue dv = dr.getNext();
HtmlFormatter htmlFormat = dv.getCellFormatter();
if ((dr.isRowFormat() && !dv.isCellFormat()) && styles != null)
styleCell = (HSSFCellStyle) styles.get(nvl(dr.getFormatId(),"default"));
if (htmlFormat != null && dv.getFormatId() != null && styles != null)
styleCell = (HSSFCellStyle) styles.get(nvl(dv.getFormatId(),"default"));
String value = nvl((String)colHash.get(dv.getColId().toUpperCase()));
boolean bold = false;
if(dv.isVisible()) {
cellNum += 1;
cell = row.createCell((short) cellNum);
//System.out.println("Stripping HTML 1");
//cell.setCellValue(strip.stripHtml(dv.getDisplayValue()));
String dataType = (String) (dataTypeMap.get(dv.getColId()));
//System.out.println("Value " + value + " " + (( dataType !=null && dataType.equals("DATE")) || (dv.getColName()!=null && dv.getColName().toLowerCase().endsWith("date"))) );
if (dataType!=null && dataType.equals("NUMBER")){
//cellNumber = row.createCell((short) cellNum);
//cellNumber.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//cellNumber.setCellValue(dv.getDisplayValue());
//cellCurrencyNumber = 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("-$") )) {
//if (dv.getDisplayValue().startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempDollar = dv.getDisplayValue().trim();
tempDollar = tempDollar.replaceAll(" ", "").substring(0);
tempDollar = tempDollar.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempDollar);
//System.out.println("Before copy Value |" + tempDollar);
//tempDollar = String.copyValueOf(tempDollar.toCharArray(), 1, tempDollar.length()-1);
//System.out.println("After copy Value |" + tempDollar);
if ((tempDollar.indexOf(","))!= -1){
tempDollar = tempDollar.replaceAll(",", "");
}
//System.out.println("The final string 1 is "+tempDollar);
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.setCellStyle(styleDefault);
cell.setCellValue(tempDollar);
}
}else{
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
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("-$") )) {
//if (dv.getDisplayValue().startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempInt = value.trim();
tempInt = tempInt.replaceAll(" ", "").substring(0);
tempInt = tempInt.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempInt);
//System.out.println("Before copy Value |" + tempInt);
//tempInt = String.copyValueOf(tempInt.toCharArray(), 1, tempInt.length()-1);
//System.out.println("After copy Value |" + tempInt);
if ((tempInt.indexOf(","))!= -1){
tempInt = tempInt.replaceAll(",", "");
}
//System.out.println("The final string INT is "+tempInt);
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{
//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);//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);
}
}
//int temp = Integer.parseInt(value.trim());
// cell.setCellValue(temp);
//}else{
// cell.setCellValue(strip.stripHtml(value));
//}
}
}
}
}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")) ) {
//cellDate = row.createCell((short) cellNum);
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("mm/dd/yy"));
if(styleCell!=null) {
styleCell.setDataFormat((short) 0xe);//HSSFDataFormat.getBuiltinFormat("m/d/yy"));
cell.setCellStyle(styleCell);
} else
cell.setCellStyle(styleDate);
//String MY_DATE_FORMAT = "yyyy-MM-dd";
//value = nvl(value).length()<=0?nvl(dv.getDisplayValue()):value;
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) {
//System.out.println("ExcelDate " + HSSFDateUtil.getExcelDate(date));
cell.setCellValue(HSSFDateUtil.getExcelDate(date));
try {
String str = cell.getStringCellValue();
} catch (IllegalStateException ex) { /*cell.getCellStyle().setDataFormat((short)0);*/cell.setCellValue(value);}
} else {
/*cell.getCellStyle().setDataFormat((short)0);*/
cell.setCellValue(value);
}
//cellDate.setCellValue(date);
//cellDate.setCellValue(value); //cellDate.setCellValue(date);
//cellDate.setCellValue(dv.getDisplayValue());
}else if((dv.getDisplayTotal()!=null && dv.getDisplayTotal().equals("SUM(")) || (dv.getColName()!=null && dv.getColName().indexOf("999")!=-1)){
//cellNumber = row.createCell((short) cellNum);
//cellNumber.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//cellNumber.setCellValue(dv.getDisplayValue());
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("-$") )) {
//if (value.startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempDollar = value.trim();
tempDollar = tempDollar.replaceAll(" ", "").substring(0);
tempDollar = tempDollar.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempDollar);
//System.out.println("Before copy Value |" + tempDollar);
//tempDollar = String.copyValueOf(tempDollar.toCharArray(), 1, tempDollar.length()-1);
//System.out.println("After copy Value |" + tempDollar);
if ((tempDollar.indexOf(","))!= -1){
tempDollar = tempDollar.replaceAll(",", "");
}
//System.out.println("The final string 2IF is "+tempDollar);
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{
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
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("-$") )) {
//if (value.startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempInt = value.trim();
tempInt = tempInt.replaceAll(" ", "").substring(0);
tempInt = tempInt.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempInt);
//System.out.println("Before copy Value |" + tempInt);
//tempInt = String.copyValueOf(tempInt.toCharArray(), 1, tempInt.length()-1);
//System.out.println("After copy Value |" + tempInt);
if ((tempInt.indexOf(","))!= -1){
tempInt = tempInt.replaceAll(",", "");
}
//System.out.println("The final string INT 2 is "+tempInt);
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{
//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);//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);
}
}
//int temp = Integer.parseInt(dv.getDisplayValue().trim());
// cell.setCellValue(temp);
//}else{
// cell.setCellValue(strip.stripHtml(dv.getDisplayValue()));
//}
} else {
if(styleCell!=null) {
styleCell.setWrapText(true);
cell.setCellStyle(styleCell);
} else
cell.setCellStyle(styleDefault);
}
}
}
}
else {
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("General"));
if(styleCell!=null) {
styleCell.setWrapText(true);
cell.setCellStyle(styleCell);
} else
cell.setCellStyle(styleDefault);
cell.setCellValue(strip.stripHtml(value));
}
//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;
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()));
//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;
}
//System.out.println("2IF "+ (dr.isRowFormat()) + " " + (dv.isCellFormat()) + " " + (styles!=null));
if ((dr.isRowFormat() && !dv.isCellFormat()) && styles != null) {
//cell.setCellStyle((HSSFCellStyle) styles.get(nvl(dr.getFormatId(),"default")));
continue;
}
//System.out.println("3IF "+ (htmlFormat != null) + " " + (dv.getFormatId() != null) + " " + (bold == false) + " "+ (styles != null));
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);
} // dv.isVisible
}
rowNum += 1;
}
int cw = 0;
for (int i = 0; i < cellWidth.size(); i++) {
cw = ((Integer) cellWidth.get(i)).intValue() + 12;
// if(i!=cellWidth.size()-1)
sheet.setColumnWidth((short) (i), (short) ((cw * 8) / ((double) 1 / 20)));
// else
// sheet.setColumnWidth((short) (i + 1), (short) ((cw * 10) /
// ((double) 1 / 20)));
}
// 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);
//}
rd.reportDataTotalRow.resetNext();
DataRow drTotal = rd.reportDataTotalRow.getNext();
//cellNum = -1;
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) {
ex.printStackTrace();
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);
} finally {
try {
if(conn!=null)
conn.close();
if(st!=null)
st.close();
if(rs!=null)
rs.close();
} catch (SQLException ex) {
throw new RaptorException(ex);
}
}
/*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 Region(rowNum, (short) cellNum, rowNum, (short) (columnRows)));
rowNum += 1;
}*/
} else {
if(rr.getReportType().equals(AppConstants.RT_LINEAR)) {
int rowCount = 0;
for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext();) {
DataRow dr = rd.reportDataRows.getNext();
//List l = rd.getReportDataList();
//for (int dataRow = 0; dataRow < l.size(); dataRow++) {
rowCount++;
//DataRow dr = (DataRow) l.get(dataRow);
row = sheet.createRow(rowNum);
cellNum = -1;
if (rr.getReportType().equals(AppConstants.RT_LINEAR) && rd.reportTotalRowHeaderCols!=null) {
rd.reportRowHeaderCols.resetNext(0);
if(rd.reportTotalRowHeaderCols!=null) {
//cellNum = -1;
//for (rd.reportRowHeaderCols.resetNext(); rd.reportRowHeaderCols.hasNext();) {
//cellNum += 1;
//RowHeaderCol rhc = rd.reportRowHeaderCols.getRowHeaderCol(0);
//if (firstPass)
// rhc.resetNext();
//RowHeader rh = rhc.getRowHeader(rowCount-1);
//row.createCell((short) cellNum).setCellValue(rowCount);
//row.getCell((short) cellNum).setCellStyle(styleDefault);
//if (firstPass)
//cellWidth.add(cellNum, new Integer((rowCount+"").length()));
//else
//cellWidth.set(cellNum, new Integer((rowCount+"").length()));
//} // for
}
} 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;
//cellNum = -1;
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 = (HSSFCellStyle) styles.get(nvl(dr.getFormatId(),"default"));
if (htmlFormat != null && dv.getFormatId() != null && styles != null)
styleCell = (HSSFCellStyle) styles.get(nvl(dv.getFormatId(),"default"));
if(dv.isVisible()) {
cellNum += 1;
cell = row.createCell((short) cellNum);
//System.out.println("Stripping HTML 1");
//cell.setCellValue(strip.stripHtml(value));
String dataType = (String) (dataTypeMap.get(dv.getColId()));
//System.out.println(" The Display Value is ********"+value + " " + dv.getDisplayTotal() + " " + dv.getColName());
if (dataType!=null && dataType.equals("NUMBER")){
//cellNumber = row.createCell((short) cellNum);
//cellNumber.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//cellNumber.setCellValue(value);
//cellCurrencyNumber = 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("-$") )) {
//if (value.startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempDollar = value.trim();
tempDollar = tempDollar.replaceAll(" ", "").substring(0);
tempDollar = tempDollar.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempDollar);
//System.out.println("Before copy Value |" + tempDollar);
//tempDollar = String.copyValueOf(tempDollar.toCharArray(), 1, tempDollar.length()-1);
//System.out.println("After copy Value |" + tempDollar);
if ((tempDollar.indexOf(","))!= -1){
tempDollar = tempDollar.replaceAll(",", "");
}
//System.out.println("The final string 1 is "+tempDollar);
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{
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
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("-$") )) {
//if (value.startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempInt = value.trim();
tempInt = tempInt.replaceAll(" ", "").substring(0);
tempInt = tempInt.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempInt);
//System.out.println("Before copy Value |" + tempInt);
//tempInt = String.copyValueOf(tempInt.toCharArray(), 1, tempInt.length()-1);
//System.out.println("After copy Value |" + tempInt);
if ((tempInt.indexOf(","))!= -1){
tempInt = tempInt.replaceAll(",", "");
}
//System.out.println("The final string INT is "+tempInt);
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{
//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);//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);
}
}
//int temp = Integer.parseInt(value.trim());
// cell.setCellValue(temp);
//}else{
// cell.setCellValue(strip.stripHtml(value));
//}
}
}
}
}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")) ) {
//cellDate = row.createCell((short) cellNum);
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("mm/dd/yy"));
if(styleCell!=null) {
styleCell.setDataFormat((short)0xe); //HSSFDataFormat.getBuiltinFormat("m/d/yy"));
cell.setCellStyle(styleCell);
} else
cell.setCellStyle(styleDate);
//String MY_DATE_FORMAT = "yyyy-MM-dd";
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) {
//System.out.println("ExcelDate " + HSSFDateUtil.getExcelDate(date));
cell.setCellValue(HSSFDateUtil.getExcelDate(date));
try {
String str = cell.getStringCellValue();
} catch (IllegalStateException ex) { /*cell.getCellStyle().setDataFormat((short)0);*/cell.setCellValue(value);}
} else {
/*cell.getCellStyle().setDataFormat((short)0);*/
cell.setCellValue(value);
}
//cellDate.setCellValue(date);
//cellDate.setCellValue(value);
}else if((dv.getDisplayTotal()!=null && dv.getDisplayTotal().equals("SUM(")) || (dv.getColName()!=null && dv.getColName().indexOf("999")!=-1)){
//cellNumber = row.createCell((short) cellNum);
//cellNumber.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//cellNumber.setCellValue(value);
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("-$") )) {
//if (value.startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempDollar = value.trim();
tempDollar = tempDollar.replaceAll(" ", "").substring(0);
tempDollar = tempDollar.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempDollar);
//System.out.println("Before copy Value |" + tempDollar);
//tempDollar = String.copyValueOf(tempDollar.toCharArray(), 1, tempDollar.length()-1);
//System.out.println("After copy Value |" + tempDollar);
if ((tempDollar.indexOf(","))!= -1){
tempDollar = tempDollar.replaceAll(",", "");
}
//System.out.println("The final string 2IF is "+tempDollar);
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{
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
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("-$") )) {
//if (value.startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempInt = value.trim();
tempInt = tempInt.replaceAll(" ", "").substring(0);
tempInt = tempInt.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempInt);
//System.out.println("Before copy Value |" + tempInt);
//tempInt = String.copyValueOf(tempInt.toCharArray(), 1, tempInt.length()-1);
//System.out.println("After copy Value |" + tempInt);
if ((tempInt.indexOf(","))!= -1){
tempInt = tempInt.replaceAll(",", "");
}
//System.out.println("The final string INT 2 is "+tempInt);
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);
}
}
//int temp = Integer.parseInt(value.trim());
// cell.setCellValue(temp);
//}else{
// cell.setCellValue(strip.stripHtml(value));
//}
} else {
if(styleCell!=null) {
styleCell.setWrapText(true);
cell.setCellStyle(styleCell);
} else
cell.setCellStyle(styleDefault);
}
}
}
}
else {
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("General"));
if(styleCell!=null) {
styleCell.setWrapText(true);
cell.setCellStyle(styleCell);
} else
cell.setCellStyle(styleDefault);
cell.setCellValue(strip.stripHtml(value));
}
//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;
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()));
//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;
}
//System.out.println("2IF "+ (dr.isRowFormat()) + " " + (dv.isCellFormat()) + " " + (styles!=null));
if ((dr.isRowFormat() && !dv.isCellFormat()) && styles != null) {
//cell.setCellStyle((HSSFCellStyle) styles.get(nvl(dr.getFormatId(),"default")));
continue;
}
//System.out.println("3IF "+ (htmlFormat != null) + " " + (dv.getFormatId() != null) + " " + (bold == false) + " "+ (styles != null));
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 rowNames = dr.getRowValues();
for(dr.resetNext(); dr.hasNext(); rowCount++ ) {
if(first) {
if(rowNames!=null) {
for(int i=0; i0) {
cellNum += 1;
row.createCell((short) cellNum).setCellValue(strip.stripHtml(dv.getDisplayValue()));
//row.getCell((short) cellNum).setCellStyle(styleDefault);
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;
// if(i!=cellWidth.size()-1)
sheet.setColumnWidth((short) (i), (short) ((cw * 8) / ((double) 1 / 20)));
// else
// sheet.setColumnWidth((short) (i + 1), (short) ((cw * 10) /
// ((double) 1 / 20)));
}
} // for
}
}
String footer = (String) session.getAttribute("FOOTER_"+index);
if(nvl(footer).length()>0) {
footer = Utils.replaceInString(footer, "
", " ");
footer = Utils.replaceInString(footer, "
", " ");
footer = Utils.replaceInString(footer, "
", " ");
footer = strip.stripHtml(nvl(footer).trim());
row = sheet.createRow(rowNum);
cellNum = 0;
row.createCell((short) cellNum).setCellValue(footer);
sheet.addMergedRegion(new Region(rowNum, (short) cellNum, rowNum, (short) (columnRows)));
//sheet.addMergedRegion(new Region(rowNum, (short) cellNum, rowNum+columnRows, (short) (cellNum)));
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 Region(rowNum, (short) cellNum, rowNum, (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(HSSFWorkbook wb, int rowNum, int col, String reportTitle,
String reportDescr, HSSFSheet sheet) {
short s1 = 0, s2 = (short) (col-1);
rowNum += 1;
sheet.addMergedRegion(new Region(rowNum, s1, rowNum, s2));
HSSFRow row = null, row1 = null;
row = sheet.createRow(rowNum);
// Header Style
HSSFCellStyle styleHeader = wb.createCellStyle();
styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = wb.createFont();
font.setFontHeight((short) (font_header_title_size / 0.05)); //14
font.setFontName("Tahoma");
font.setColor(HSSFColor.BLACK.index);
styleHeader.setFont(font);
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue(reportTitle);
cell.setCellStyle(styleHeader);
HSSFHeader header = sheet.getHeader();
header.setCenter(HSSFHeader.font("Tahoma", "")+ HSSFHeader.fontSize((short) 9)+" " + reportTitle);
//header.setCenter(HSSFHeader.font("Tahoma", "")+ HSSFHeader.fontSize((short) 9)+reportTitle+"\n"+((Globals.getShowDescrAtRuntime() && nvl(reportDescr).length() > 0)?reportDescr:""));
// Report Description
if (Globals.getShowDescrAtRuntime() && nvl(reportDescr).length() > 0) {
rowNum += 1;
sheet.addMergedRegion(new Region(rowNum, s1, rowNum, s2));
HSSFCellStyle styleDescription = wb.createCellStyle();
styleDescription.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont fontDescr = wb.createFont();
fontDescr.setFontHeight((short) font_header_descr_size);
fontDescr.setFontName("Tahoma");
fontDescr.setColor(HSSFColor.BLACK.index);
styleDescription.setFont(fontDescr);
HSSFCell cellDescr = row.createCell((short) 0);
cellDescr.setCellValue(reportDescr);
cellDescr.setCellStyle(styleHeader);
}
if(Globals.disclaimerPositionedTopInCSVExcel()) {
rowNum += 1;
row = sheet.createRow(rowNum);
sheet.addMergedRegion(new Region(rowNum, s1, rowNum, s2));
HSSFCellStyle styleDescription = wb.createCellStyle();
styleDescription.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont fontDescr = wb.createFont();
fontDescr.setFontHeight((short) (font_size / 0.05)); //14
fontDescr.setFontName("Tahoma");
fontDescr.setColor(HSSFColor.BLACK.index);
fontDescr.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
styleDescription.setFont(fontDescr);
HSSFCell cellDescr = row.createCell((short) 0);
String disclaimer = Globals.getFooterFirstLine() + " " + Globals.getFooterSecondLine();
cellDescr.setCellValue(disclaimer);
cellDescr.setCellStyle(styleDescription);
}
rowNum += 1;
row = sheet.createRow(rowNum);
// System.out.println(" Last Row " + wb.getSheetAt(0).getLastRowNum());
}
private void paintExcelFooter(HSSFWorkbook wb, int rowNum, int col, HSSFSheet sheet) {
logger.debug(EELFLoggerDelegate.debugLogger, ("excel footer"));
//HSSFSheet sheet = wb.getSheet(getSheetName());
HSSFFooter footer = sheet.getFooter();
footer.setLeft(HSSFFooter.font("Tahoma", "")+ HSSFFooter.fontSize((short) font_footer_size)+ "Page " + HSSFFooter.page()
+ " of " + HSSFFooter.numPages() );
footer.setCenter(HSSFFooter.font("Tahoma", "")+ HSSFFooter.fontSize((short) font_footer_size)+Globals.getFooterFirstLine()+"\n"+Globals.getFooterSecondLine());
//footer.setCenter(HSSFFooter.font("Tahoma", "Italic")+ HSSFFooter.fontSize((short) 16))+Globals.getFooterSecondLine());
/* footer.font("Tahoma");
short s1 = 0, s2 = (short) (col-1);
rowNum += 1;
sheet.addMergedRegion(new Region(rowNum, s1, rowNum, s2));
HSSFRow row = null, row1 = null;
row = sheet.createRow(rowNum);
// Header Style
HSSFCellStyle styleFooter = wb.createCellStyle();
styleFooter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = wb.createFont();
font.setFontHeight((short) (10 / 0.05));
font.setFontName("Tahoma");
font.setColor(HSSFColor.BLACK.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
styleFooter.setFont(font);
HSSFCell cell = row.createCell((short) 0);
debugLogger.debug(Globals.getFooterFirstLine());
cell.setCellValue(Globals.getFooterFirstLine());
cell.setCellStyle(styleFooter);
rowNum += 1;
sheet.addMergedRegion(new Region(rowNum, s1, rowNum, s2));
row = sheet.createRow(rowNum);
cell = row.createCell((short) 0);
debugLogger.debug(Globals.getFooterSecondLine());
cell.setCellValue(Globals.getFooterSecondLine());
cell.setCellStyle(styleFooter);
*/
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 {
ReportRuntime rr;
if(requestFlag == 2)
rr = (ReportRuntime) request.getSession().getAttribute(
AppConstants.SI_REPORT_RUNTIME);
else
rr = (ReportRuntime) request.getAttribute(
AppConstants.SI_REPORT_RUNTIME);
HSSFWorkbook wb = new HSSFWorkbook();
HashMap styles = new HashMap();
if (rr != null)
styles = loadStyles(rr, wb);
String xlsFName = AppUtils.generateUniqueFileName(request, rr.getReportName(), AppConstants.FT_XLS);
logger.debug(EELFLoggerDelegate.debugLogger, ("Xls File name " +
AppUtils.getTempFolderPath()
+ xlsFName));
FileOutputStream xlsOut = new FileOutputStream(AppUtils.getTempFolderPath()
+ xlsFName);
// BufferedWriter xlsOut = new BufferedWriter(new
// FileWriter(AppUtils
// .getTempFolderPath()
// + xlsFName));
int col = 0;
//System.out.println("Row Header Count " + rd.reportRowHeaderCols.getRowCount());
//System.out.println("Total Count " + rd.getTotalColumnCount());
if (!rd.reportRowHeaderCols.hasNext())
col = rd.getTotalColumnCount();
else
col = rd.getTotalColumnCount();
int rowNum = 0;
HSSFSheet 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.getParamNameValuePairsforPDFExcel(request, 1) != null) {
paintExcelParams(wb,rowNum,col,rr.getParamNameValuePairsforPDFExcel(request, 1), rr.getFormFieldComments(request), sheet, reportTitle, reportDescr);
} // if
rowNum = sheet.getLastRowNum();
//System.out.println(" rowNum after Params " + rowNum);
paintExcelData(wb, rowNum, col, rd, styles,rr, sheet, "", xlsOut, request);
if (Globals.getPrintFooterInDownload() ) {
rowNum = sheet.getLastRowNum();
rowNum += 2;
paintExcelFooter(wb, rowNum, col, sheet);
}
//response.setContentType("application/vnd.ms-excel");
//response.setHeader("Content-disposition", "attachment;filename=download_all_"
// + user_id + ".xls");
wb.write(xlsOut);
xlsOut.flush();
xlsOut.close();
return xlsFName;
} catch (Exception e) {
e.printStackTrace();
(new ErrorHandler()).processError(request, "Exception saving data to EXCEL file: "
+ e.getMessage());
return null;
}
} // saveAsExcelFile
public void createExcelFileContent(Writer out, ReportData rd, ReportRuntime rr, HttpServletRequest request,
HttpServletResponse response, String user_id, int type) throws IOException, RaptorException {
// Adding utility for downloading Dashboard reports.
HashMap styles = new HashMap();
HttpSession session = request.getSession();
ServletOutputStream sos = null;
BufferedInputStream buf = null;
HSSFWorkbook wb = null;
// if(session.getAttribute(AppConstants.SI_DASHBOARD_REP_ID)!=null)
// ReportRuntime rrDashboard = (ReportRuntime) request.getSession().getAttribute(AppConstants.SI_REPORT_RUNTIME);
String formattedDate = "";
String xlsFName = "";
int returnValue = 0;
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;
}
if(isDashboard) {
try {
formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date());
xlsFName = "dashboard"+formattedDate+user_id+".xls";
FileInputStream xlsIn = null;
POIFSFileSystem fileSystem = null;
buf = null;
FileOutputStream xlsOut = null;
/* try {
xlsIn = new FileInputStream (AppUtils.getTempFolderPath()
+ xlsFName);
}
catch (FileNotFoundException e) {
System.out.println ("File not found in the specified path.");
e.printStackTrace ();
}
if(xlsIn != null) {
fileSystem = new POIFSFileSystem (xlsIn);
wb = new HSSFWorkbook(fileSystem);
} else {
xlsOut = new FileOutputStream(AppUtils.getTempFolderPath()
+ xlsFName);
wb = new HSSFWorkbook();
}
*/
Map reportRuntimeMap = null;
Map reportDataMap = null;
//Map reportDisplayTypeMap = null;
reportRuntimeMap = (TreeMap) request.getSession().getAttribute(AppConstants.SI_DASHBOARD_REPORTRUNTIME_MAP);
reportDataMap = (TreeMap) request.getSession().getAttribute(AppConstants.SI_DASHBOARD_REPORTDATA_MAP);
//reportDisplayTypeMap = (TreeMap) request.getSession().getAttribute(AppConstants.SI);
HSSFSheet sheet = null;
if(reportRuntimeMap!=null) {
//ServletOutputStream sos = response.getOutputStream();
Set setReportRuntime = reportRuntimeMap.entrySet();
Set setReportDataMap = reportDataMap.entrySet();
Iterator iter2 = setReportDataMap.iterator();
int count = 0;
for(Iterator iter = setReportRuntime.iterator(); iter.hasNext(); ) {
count++;
try {
xlsIn = new FileInputStream (AppUtils.getTempFolderPath()
+ xlsFName);
}
catch (FileNotFoundException e) {
System.out.println ("File not found in the specified path.");
//e.printStackTrace ();
}
if(xlsIn != null) {
fileSystem = new POIFSFileSystem (xlsIn);
wb = new HSSFWorkbook(fileSystem);
xlsOut = new FileOutputStream(AppUtils.getTempFolderPath()
+ xlsFName);
} else {
xlsOut = new FileOutputStream(AppUtils.getTempFolderPath()
+ xlsFName);
wb = new HSSFWorkbook();
}
Map.Entry entryData = (Entry) iter2.next();
Map.Entry entry = (Entry) iter.next();
//String rep_id = (String) entry.getKey();
ReportRuntime rrDashRep = (ReportRuntime) entry.getValue();
ReportData rdDashRep = (ReportData) entryData.getValue();
//styles = loadStyles(rrDashRep, wb);
int col = 0;
String reportTitle = (nvl(rrDashRep.getReportTitle()).length()>0?rrDashRep.getReportTitle():rrDashRep.getReportName());
String reportDescr = rrDashRep.getReportDescr();
if (!rdDashRep.reportRowHeaderCols.hasNext())
col = rdDashRep.getTotalColumnCount();
else
col = rdDashRep.getTotalColumnCount();
if(col==0) col=10;
int rowNum = 0;
String formattedReportName = new HtmlStripper().stripSpecialCharacters(rrDashRep.getReportName());
try {
sheet = wb.createSheet(formattedReportName);
sheet.getPrintSetup().setLandscape(true);
styles = loadStyles(rrDashRep, wb);
} catch (IllegalArgumentException ex) { wb.write(xlsOut);xlsOut.flush();xlsOut.close();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();
String sql_whole = rrDashRep.getWholeSQL();
returnValue = paintExcelData(wb, rowNum, col, rdDashRep, styles,rrDashRep, sheet, sql_whole, xlsOut, request);
if( returnValue == 0 ) {
if (Globals.getPrintFooterInDownload()) {
rowNum = sheet.getLastRowNum();
rowNum += 2;
paintExcelFooter(wb, rowNum, col, sheet);
}
//wb.write(sos);
wb.write(xlsOut);
//TODO Remove comment
xlsOut.flush();
xlsOut.close();
wb = null;
} else {
//xlsOut.flush();
//xlsOut.close();
//response.reset();
//response.setContentType("application/vnd.ms-excel");
// RequestDispatcher dispatcher = request.getRequestDispatcher("raptor.htm?r_action=report.message");
// request.setAttribute("message", Globals.getUserDefinedMessageForMemoryLimitReached());
// try {
// dispatcher.forward(request, response);
// } catch (ServletException ex) {}
}
}
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename="+"dashboard"+formattedDate+user_id+".xls");
sos = response.getOutputStream();
xlsIn = new FileInputStream (AppUtils.getTempFolderPath()
+ xlsFName);
buf = new BufferedInputStream(xlsIn);
int readBytes = 0;
byte [] bOut = new byte [4096];
//read from the file; write to the ServletOutputStream
//while ((readBytes = buf.read()) != -1)
while ((readBytes = buf.read (bOut, 0, 4096))> 0) {
buf.available();
sos.write (bOut, 0, readBytes);
}
//sos.write(readBytes);
}
} catch (IOException ex) { ex.printStackTrace(); throw ex;}
finally {
if (sos != null)
sos.close();
if (buf != null)
buf.close();
}
File f = new File (AppUtils.getTempFolderPath()
+ xlsFName);
if(f.exists()) f.delete();
} else {
wb = new HSSFWorkbook();
// PrintWriter xlsOut = new PrintWriter(out).;
setSheetName(Globals.getSheetName());
//ServletOutputStream sos = response.getOutputStream();
//PrintWriter outWriter = response.getWriter();
if (rr != null)
styles = loadStyles(rr, wb);
/* int col = 0;
if (!rd.reportRowHeaderCols.hasNext())
col = rd.getTotalColumnCount();
else
col = rd.getTotalColumnCount() + 1;
int rowNum = 0;
String reportTitle = rr.getReportName();
String reportDescr = rr.getReportDescr();
// if (Globals.getPrintTitleInDownload() && reportTitle != null) {
HSSFSheet sheet = wb.createSheet(getSheetName());
System.out.println(" Title " + Globals.getPrintTitleInDownload());
if (Globals.getPrintTitleInDownload()&& reportTitle != null ) {
paintExcelHeader(wb, rowNum, col, reportTitle, reportDescr);
rowNum = wb.getSheetAt(0).getLastRowNum();
} else
rowNum = 0;
System.out.println(" Params " + Globals.getPrintParamsInDownload());
if (Globals.getPrintParamsInDownload() && rr.getParamNameValuePairs() != null) {
paintExcelParams(wb,rowNum,col,rr.getParamNameValuePairs());
} // if
paintExcelData(wb, rowNum, col, rd, styles);
rowNum = wb.getSheetAt(0).getLastRowNum();
*/
int col = 0;
//System.out.println("Row Header Count " + rd.reportRowHeaderCols.getRowCount());
//System.out.println("Total Count " + rd.getTotalColumnCount());
String reportTitle = (nvl(rr.getReportTitle()).length()>0?rr.getReportTitle():rr.getReportName());
String reportDescr = rr.getReportDescr();
col = getColumnCountForDownloadFile(rr,rd);
/*if (!rd.reportRowHeaderCols.hasNext())
col = rd.getTotalColumnCount();
else
col = rd.getTotalColumnCount();
*/
int rowNum = 0;
HSSFSheet 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) {
ArrayList 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();
String formattedReportName = new HtmlStripper().stripSpecialCharacters(rr.getReportName());
formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date());
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename="+formattedReportName+formattedDate+user_id+".xls");
sos = response.getOutputStream();
if(type == 3 && rr.getSemaphoreList()==null && !(rr.getReportType().equals(AppConstants.RT_CROSSTAB)) ) { //type = 3 is whole
//String sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE);
String sql_whole = rr.getWholeSQL();
returnValue = paintExcelData(wb, rowNum, col, rd, styles,rr, sheet, sql_whole, sos, request);
} else if(type == 2) {
returnValue = paintExcelData(wb, rowNum, col, rd, styles,rr, sheet, "", sos, request);
} else {
//String sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE);
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, col, rd, styles,rr, sheet, "", sos, request);
} else {
rd = rr.loadReportData(-1, AppUtils.getUserID(request), downloadLimit,request, true);
returnValue = paintExcelData(wb, rowNum, col, rd, styles,rr, sheet, "", sos, request);
}
} else {
returnValue = paintExcelData(wb, rowNum, col, rd, styles,rr, sheet, rr.getWholeSQL(), sos, 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
wb.write(sos);
sos.flush();
sos.close();
wb = null;
} else {
//sos.flush();
//sos.close();
/* response.reset();
RequestDispatcher dispatcher = request.getRequestDispatcher("/raptor.htm?action=raptor&r_action=report.message");
request.setAttribute("message", Globals.getUserDefinedMessageForMemoryLimitReached());
try {
dispatcher.forward(request, response);
} catch (ServletException ex) {}
*/
}
}
}
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();
//File f = new File(request.(arg0)("/"));
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);
//PrintWriter txtOut = new PrintWriter(out);
//response.setContentType("application/notepad");
//response.setHeader("Content-disposition", "attachment;filename=download_all_"+AppUtils.getUserID(request)+".txt");
ServletOutputStream sos = response.getOutputStream();
//No Report Title for flat file.
// if (Globals.getPrintTitleInDownload() && reportTitle != null) {
// txtOut.println();
// txtOut.println("\"" + reportTitle + "\"");
// txtOut.println();
// if (Globals.getShowDescrAtRuntime() && nvl(reportDescr).length() > 0) {
// txtOut.println("\"" + reportDescr + "\"");
// txtOut.println();
// }
// } // if
// No Params either
// int count = 0;
// if (Globals.getPrintParamsInDownload() && reportParamNameValues != null) {
// for (Iterator iter = reportParamNameValues.iterator(); iter.hasNext();) {
// count += 1;
// if(count == 1) txtOut.println();
// IdNameValue value = (IdNameValue) iter.next();
// txtOut.println(value.getId() + " = " + value.getName());
// if(!iter.hasNext()) txtOut.println();
// } // for
// } // if
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.flush();
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 styles = new HashMap();
HttpSession session = request.getSession();
ServletOutputStream sos = null;
BufferedInputStream buf = null;
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.
//setSheetName(Globals.getSheetName());
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;
}
//boolean isDashboard = (session.getAttribute(AppConstants.SI_DASHBOARD_REP_ID)!=null);
ArrayList sheetArrayList = new ArrayList();
Map reportRuntimeMap = null;
Map reportDataMap = null;
ArrayList reportIDList = new ArrayList();
//Map reportDisplayTypeMap = null;
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();
// Total Columns visible in excel
//col = getColumnCountForDownloadFile(rr, rd);
int rowNum = 0;
XSSFSheet sheet = null;
//save the template
String filename = "";
String extension = "";
String sheetRef = null;
FileOutputStream os = null; //template file
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();
os = new FileOutputStream(AppUtils.getTempFolderPath()+ filename+"T."+ nvls(extension, "xlsx"));
if(count==1) {
if(nvl(rr.getTemplateFile()).length()>0) {
readTemplate = new FileInputStream(org.openecomp.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);
}
if(rrDashRep!=null)
styles = loadXSSFStyles(rrDashRep, wb, styles);
String 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();
}
os.close();
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();
outStream.close();
inF.close();
}
FileInputStream xlsIn = null;
POIFSFileSystem fileSystem = null;
buf = 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();
//filename = templateFilename.substring(0, templateFilename.lastIndexOf("."))+"_"+formattedDate+user_id;
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");
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();
fw.close();
fileOutTemp.flush();
fileOutTemp.close();
//Step 3. Substitute the template entry with the generated data
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();
outF.close();
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();
outStream.close();
inF.close();
}
}
} 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) {
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();
//wb = null;
os.close();
} else {
os = new FileOutputStream(AppUtils.getTempFolderPath()+ filename+"T."+ nvls(extension, "xlsx"));
FileInputStream readTemplate = new FileInputStream(org.openecomp.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);
//sheet = wb.getSheet(getSheetName());
sheetRef = sheet.getPackagePart().getPartName().getName();
wb.write(os);
os.flush();
readTemplate.close();
//wb = null;
os.close();
}
//Step 2. Generate XML file.
File tmp = File.createTempFile("sheet", ".xml");
FileOutputStream fileOutTemp = new FileOutputStream(tmp);
Writer fw = new OutputStreamWriter(fileOutTemp, XML_ENCODING);
//String sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE);
String sql_whole = "";
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();
fw.close();
fileOutTemp.flush();
fileOutTemp.close();
//Step 3. Substitute the template entry with the generated data
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();
outF.close();
}
//get servlet output stream
response.reset();
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"));
buf = new BufferedInputStream(new FileInputStream(AppUtils.getTempFolderPath()+filename + "."+ nvls(extension, "xlsx")));
int readBytes = 0;
//read from the file; write to the ServletOutputStream
while ((readBytes = buf.read()) != -1)
sos.write(readBytes);
buf.close();
sos.flush();
sos.close();
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 {
ZipFile zip = new ZipFile(zipfile);
ZipOutputStream zos = new ZipOutputStream(out);
@SuppressWarnings("unchecked")
Enumeration en = (Enumeration) zip.entries();
while (en.hasMoreElements()) {
ZipEntry ze = en.nextElement();
if(!ze.getName().equals(entry)){
zos.putNextEntry(new ZipEntry(ze.getName()));
InputStream is = zip.getInputStream(ze);
copyStream(is, zos);
is.close();
}
}
zos.putNextEntry(new ZipEntry(entry));
InputStream is = new FileInputStream(tmpfile);
copyStream(is, zos);
zos.flush();
zos.close();
is.close();
zip.close();
}
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(Writer out, ReportData rd,
ReportRuntime rr, HttpServletRequest request, HttpServletResponse response)
throws RaptorException {
//ArrayList reportParamNameValues = rr.getParamNameValuePairs();
//String reportTitle = rr.getReportName();
//String reportDescr = rr.getReportDescr();
PrintWriter csvOut = new PrintWriter(out);
ServletOutputStream sos = null;
BufferedInputStream buf = null;
String fileName = "";
String formattedReportName = new HtmlStripper().stripSpecialCharacters(rr.getReportName());
String formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date());
String fName = formattedReportName+formattedDate+AppUtils.getUserID(request);
boolean raw = AppUtils.getRequestFlag(request, "raw");
String sql_whole = (String) request.getAttribute(AppConstants.RI_REPORT_SQL_WHOLE);
String csvFName = fName+".csv";
String zipFName = fName+".zip";
if(true) {
try {
fileName = AppUtils.getTempFolderPath()+""+csvFName;
csvOut = new PrintWriter(new BufferedWriter(
new OutputStreamWriter(
new FileOutputStream(fileName), "UTF-8")), false);
} catch (FileNotFoundException fex) {
fex.printStackTrace();
}
catch (UnsupportedEncodingException fex1) {
fex1.printStackTrace();
}
}
HtmlStripper strip = new HtmlStripper();
ResultSet rs = null;
//OracleConnection conn = null;
//OracleStatement st = null;
//Connection conO = null;
//Statement stO = null;
Connection conn = null;
Statement st = null;
ResultSetMetaData rsmd = null;
ColumnHeaderRow chr = null;
int mb = 1024*1024;
Runtime runtime = Runtime.getRuntime();
String valueName = "";
if(!raw) {
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) {
ArrayList paramsList = rr.getParamNameValuePairsforPDFExcel(request, 1);
if(paramsList.size()<=0) {
paramsList = (ArrayList) request.getSession().getAttribute(AppConstants.SI_FORMFIELD_DOWNLOAD_INFO);
}
int paramSeq = 0;
for (Iterator iter = paramsList.iterator(); iter.hasNext();) {
IdNameValue value = (IdNameValue) iter.next();
//System.out.println("\"" + value.getId() + " = " + value.getName() + "\"");
if(nvl(value.getId()).trim().length()>0 && (!nvl(value.getId()).trim().equals("BLANK"))) {
paramSeq += 1;
if(paramSeq <= 1) {
csvOut.print("\"" + "Run-time Parameters" + "\"");
csvOut.println();
//strBuf.append("Run-time Parameters\n");
}
csvOut.print("\"" + value.getId() +":" + "\",");
valueName = 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();
//strBuf.append(value.getId()+": "+ value.getName()+"\n");
}
} //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);
if (!rr.getReportType().equals(AppConstants.RT_HIVE))
sql_whole = rr.getWholeSQL();
else
sql_whole = rr.getReportSQL();
if(nvl(sql_whole).length()>0) {
try {
conn = ConnectionUtils.getConnection(rr.getDbInfo());
st = conn.createStatement();
//conn.setDefaultRowPrefetch(1000);
//st.setFetchDirection(ResultSet.TYPE_FORWARD_ONLY);
//st.setFetchSize(1000);
System.out.println("************* Map Whole SQL *************");
System.out.println(sql_whole);
System.out.println("*****************************************");
rs = st.executeQuery(sql_whole);
//st.setFetchSize(1000);
rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
HashMap colHash = new HashMap();
String title = "";
if(rd!=null) {
/*if(rd.reportTotalRowHeaderCols!=null) {
csvOut.print("\"" + "#" + "\",");
}*/
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()) {
/* 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++;
//if(!raw) {
colHash = new HashMap();
for (int i = 1; i <= numberOfColumns; i++) {
colHash.put(rsmd.getColumnLabel(i).toUpperCase(), rs.getString(i));
}
/*if(rd.reportDataTotalRow!=null) {
csvOut.print("\"" + rowCount + "\",");
}*/
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("\"" + strip.stripCSVHtml(nvl((String)colHash.get(ch.getColId().toUpperCase()))) + "\",");
}
}
csvOut.println();
/*} else {
for (int i = 1; i <= numberOfColumns; i++) {
csvOut.print("\"" + strip.stripCSVHtml( rs.getString(i)) + "\",");
}
csvOut.println();
}*/
}
if(rd.reportDataTotalRow!=null) {
for (rd.reportDataTotalRow.resetNext(); rd.reportDataTotalRow.hasNext();) {
DataRow dr = rd.reportDataTotalRow.getNext();
csvOut.print("\"" + "Total" + "\",");
dr.resetNext();dr.getNext();
for (; dr.hasNext();) {
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 (SQLException ex) {
throw new RaptorException(ex);
} catch (ReportSQLException ex) {
throw new RaptorException(ex);
} catch (Exception 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) {
throw new RaptorException(ex);
}
}
if(!raw) {
if(!Globals.disclaimerPositionedTopInCSVExcel()) {
if(Globals.getShowDisclaimer()) {
csvOut.print("\"" + Globals.getFooterFirstLine() + "\",");
csvOut.println();
csvOut.print("\"" + Globals.getFooterSecondLine() + "\",");
csvOut.println();
}
}
}
// csvOut.flush();
} 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 + "\",");
}
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();
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();
}
}
}
//csvOut.flush();
} else {
csvOut.print("\"No Data Found \"");
}
}
csvOut.flush();
csvOut.close();
/*
if (Globals.getPrintTitleInDownload() && reportTitle != null) {
csvOut.println();
csvOut.println("\"" + reportTitle + "\"");
csvOut.println();
if (Globals.getShowDescrAtRuntime() && nvl(reportDescr).length() > 0) {
csvOut.println("\"" + reportDescr + "\"");
csvOut.println();
}
} // if
if (Globals.getPrintParamsInDownload() && reportParamNameValues != null) {
csvOut.println();
for (Iterator iter = reportParamNameValues.iterator(); iter.hasNext();) {
IdNameValue value = (IdNameValue) iter.next();
csvOut.println("\"" + value.getId() + " = " + value.getName() + "\"");
} // for
csvOut.println();
} // if
*/
if(true && !raw) {
try {
//final int BUFFER = 2048;
//fis.read(buf,0,buf.length);
int size = 0;
byte[] buffer = new byte[1024];
//CRC32 crc = new CRC32();
//PrintStream fos = new PrintStream(new WriterOutputStream(out));
//BufferedOutputStream dest = new BufferedOutputStream(fos, BUFFER);
//ZipOutputStream s = new ZipOutputStream(dest);
ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(AppUtils.getTempFolderPath()+""+zipFName));
FileInputStream fis = new FileInputStream(fileName);
//s.setLevel(6);
ZipEntry entry = new ZipEntry(csvFName);
//crc.reset();
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();
fis.close();
// Finish zip process
zos.close();
} catch(Exception e) {
e.printStackTrace();
}
}
response.reset();
java.io.File file = null;
if(true && !raw) {
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition","attachment;filename="+fName+".zip");
file = new java.io.File(AppUtils.getTempFolderPath()+""+fName+".zip");
} else {
response.setContentType("application/csv");
response.setHeader("Content-disposition","attachment;filename="+fName+".csv");
file = new java.io.File(AppUtils.getTempFolderPath()+""+fName+".csv");
}
FileInputStream fileIn = null;
int c;
try {
sos = response.getOutputStream();
fileIn = new FileInputStream(file);
buf = new BufferedInputStream(fileIn);
byte [] bOut = new byte [4096];
//read from the file; write to the ServletOutputStream
//while ((readBytes = buf.read()) != -1)
int readBytes = 0;
while ((readBytes = buf.read (bOut, 0, 4096))> 0) {
buf.available();
sos.write (bOut, 0, readBytes);
}
} catch (IOException ex) {
ex.printStackTrace();
}
catch(Exception e) {
e.printStackTrace();
} finally {
try {
if (sos != null)
sos.close();
if (buf != null)
buf.close();
} catch (Exception e1) {
e1.printStackTrace();
}
}
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 saveCSVPageFile(HttpServletRequest request, ReportData rd,
ArrayList reportParamNameValues, String reportTitle, String reportDescr) {
try {
String formattedReportName = new HtmlStripper().stripSpecialCharacters(reportTitle);
String formattedDate = new SimpleDateFormat("MMddyyyyHHmm").format(new Date());
String csvFName = formattedReportName+formattedDate+AppUtils.getUserID(request)+".csv";
//String csvFName = AppUtils.generateFileName(request, AppConstants.FT_CSV);
BufferedWriter csvOut = new BufferedWriter(new FileWriter(AppUtils
.getTempFolderPath()
+ csvFName));
createCSVFileContent(csvOut, rd, reportParamNameValues, reportTitle, reportDescr);
csvOut.close();
return csvFName;
} catch (Exception e) {
(new ErrorHandler()).processError(request, "Exception saving data to CSV file: "
+ e.getMessage());
return null;
}
} // saveCSVPageFile
*/
// public String saveAsFlatFile(HttpServletRequest request, ReportData rd,
// ReportRuntime rr, String reportTitle, String reportDescr) {
// try {
// String csvFName = AppUtils.generateFileName(request, AppConstants.FT_TXT);
//
// BufferedWriter txtOut = new BufferedWriter(new FileWriter(AppUtils
// .getTempFolderPath()
// + csvFName));
// createFlatFileContent(txtOut, rd, rr, reportTitle, reportDescr);
// txtOut.close();
//
// return csvFName;
// } catch (Exception e) {
// (new ErrorHandler()).processError(request, "Exception saving data to CSV file: "
// + e.getMessage());
// return null;
// }
// } // saveCSVPageFile
public String saveXMLFile(HttpServletRequest request, String reportName, String reportXML) {
try {
String xmlFName = AppUtils.generateUniqueFileName(request, reportName, AppConstants.FT_XML);
PrintWriter xmlOut = new PrintWriter(new BufferedWriter(new FileWriter(new File(
AppUtils.getTempFolderPath() + xmlFName))));
xmlOut.println(reportXML);
xmlOut.close();
//return AppUtils.getTempFolderURL()
// + java.net.URLEncoder.encode(java.net.URLDecoder.decode(xmlFName));
return java.net.URLEncoder.encode(java.net.URLDecoder.decode(xmlFName));
} catch (Exception 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"))) {
// The report runtime is already in the session
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
*/
// Report is NOT in the session => load from the database
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);
// If it is dashboard type then report can be viewed without specific privilege to report
String dashboardId = AppUtils.getRequestValue(request, AppConstants.RI_DASHBOARD_ID);
//System.out.println("USSSSSSSSSSSSERID " + userID);
//System.out.println("PDF " + AppUtils.getRequestNvlValue(request, "pdfAttachmentKey") );
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
//rr.setXmlFileName(saveXMLFile(request, rr.getReportName(), reportXML));
if (rDisplayContent) {
//System.out.println("In rDisplayContent ");
rr.setParamValues(request, false,true);
//if (requestFlag==2)
request.getSession().setAttribute(AppConstants.SI_REPORT_RUNTIME, rr);
}
if(inSchedule) {
//System.out.println("In 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());
}
// System.out.println("Report ID B4 Id in reportHandler "
// + ( request.getSession().getAttribute(AppConstants.SI_REPORT_RUNTIME)!=null?((ReportRuntime)request.getSession().getAttribute(AppConstants.SI_REPORT_RUNTIME)).getReportID():"Not in session"));
// System.out.println("requestFlag " + requestFlag);
if(requestFlag==2 && !rDisplayContent) {
//System.out.println("In Request Flag ");
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);
}
// System.out.println("Report ID B4 Id in reportHandler "
// + ( request.getSession().getAttribute(AppConstants.SI_REPORT_RUNTIME)!=null?((ReportRuntime)request.getSession().getAttribute(AppConstants.SI_REPORT_RUNTIME)).getReportID():"Not in session"));
//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 {
//System.out.println("********* ReportID " + reportID);
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;
//ReportDefinition rdef = null;
if (rdef != null)
if (isReportIDBlank || reportID.equals(rdef.getReportID())) {
// The report definition is already in the session
return rdef;
}
ReportRuntime rr = (ReportRuntime) request.getSession().getAttribute(
AppConstants.SI_REPORT_RUNTIME);
if (rr != null)
if (isReportIDBlank || reportID.equals(rr.getReportID())) {
// The report runtime is in the session => create report
// definition from it
rdef = new ReportDefinition(rr, request);
String userID = AppUtils.getUserID(request);
rdef.generateWizardSequence(request);
// rdef.checkUserWriteAccess(userID);
request.getSession().setAttribute(AppConstants.SI_REPORT_DEFINITION, rdef);
return rdef;
} // if
// Report is NOT in the session => load from the database
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 ) {
SHEET_NAME = sheet_name;
}
public String getSheetName() {
return SHEET_NAME;
}
/**
* 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("" +
"" );
_out.write("\n");
}
public void endSheet() throws IOException {
_out.write("");
_out.write("");
}
/**
* Insert a new row
*
* @param rownum 0-based row number
*/
public void insertRow(int rownum) throws IOException {
_out.write("\n");
this._rownum = rownum;
}
/**
* Insert row end marker
*/
public void endRow() throws IOException {
_out.write("
\n");
}
public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("");
_out.write(""+value+"");
_out.write("");
}
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("");
_out.write(""+value+"");
_out.write("");
}
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 loadXSSFStyles(ReportRuntime rr, XSSFWorkbook wb, Map loadedStyles) {
XSSFCellStyle styleDefault = wb.createCellStyle();
//System.out.println("Load Styles");
// Style default will be normal with no background
XSSFFont fontDefault = wb.createFont();
XSSFDataFormat xssffmt = wb.createDataFormat();
// The default will be plain .
fontDefault.setColor((short) HSSFFont.COLOR_NORMAL);
fontDefault.setFontHeight((short) (font_size / 0.05));
fontDefault.setFontName("Tahoma");
styleDefault.setAlignment(XSSFCellStyle.ALIGN_CENTER);
styleDefault.setBorderBottom(XSSFCellStyle.BORDER_THIN);
styleDefault.setBorderTop(XSSFCellStyle.BORDER_THIN);
styleDefault.setBorderLeft(XSSFCellStyle.BORDER_THIN);
styleDefault.setBorderRight(XSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleDefault.setFillPattern(XSSFCellStyle.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 hashMapStyles = new HashMap();;
Map hashMapFonts = new HashMap();
hashMapFonts.put("default", fontDefault);
hashMapStyles.put("default", styleDefault);
XSSFCellStyle styleLeftDefault = wb.createCellStyle();
styleLeftDefault.setAlignment(XSSFCellStyle.ALIGN_LEFT);
styleLeftDefault.setBorderBottom(XSSFCellStyle.BORDER_THIN);
styleLeftDefault.setBorderTop(XSSFCellStyle.BORDER_THIN);
styleLeftDefault.setBorderLeft(XSSFCellStyle.BORDER_THIN);
styleLeftDefault.setBorderRight(XSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleLeftDefault.setFillPattern(XSSFCellStyle.NO_FILL);
styleLeftDefault.setFont(fontDefault);
hashMapStyles.put("defaultLeft", styleLeftDefault);
XSSFCellStyle styleDate = wb.createCellStyle();
styleDate.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
styleDate.setDataFormat(xssffmt.getFormat("d-mmm-yy"));
styleDate.setAlignment(XSSFCellStyle.ALIGN_CENTER);
styleDate.setBorderBottom(XSSFCellStyle.BORDER_THIN);
styleDate.setBorderTop(XSSFCellStyle.BORDER_THIN);
styleDate.setBorderLeft(XSSFCellStyle.BORDER_THIN);
styleDate.setBorderRight(XSSFCellStyle.BORDER_THIN);
// styleDefault.setFillForegroundColor(HSSFColor.YELLOW.index);
styleDate.setFillPattern(XSSFCellStyle.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(XSSFCellStyle.SOLID_FOREGROUND);
rowHeaderStyle.setFont(headerFont);
rowHeaderStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
rowHeaderStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
rowHeaderStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
rowHeaderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
hashMapStyles.put("header", rowHeaderStyle);
XSSFCellStyle boldStyle = wb.createCellStyle();
//headerFont = wb.createFont();
//headerFont.setBold(true);
boldStyle.setFont(headerFont);
boldStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
boldStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
boldStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
boldStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
boldStyle.setAlignment(HorizontalAlignment.CENTER);
hashMapStyles.put("title", boldStyle);
XSSFCellStyle cellStyle = null;
if (semList == null || semList.getSemaphore() == null) {
hashMapStyles.put("default", styleDefault);
} /*else {
for (Iterator iter = semList.getSemaphore().iterator(); iter.hasNext();) {
SemaphoreType sem = (SemaphoreType) iter.next();
if(!semColumnList.contains(sem.getSemaphoreId())) continue;
//System.out.println("SemphoreId ----> " + sem.getSemaphoreId());
FormatList fList = sem.getFormatList();
List formatList = fList.getFormat();
for (Iterator fIter = formatList.iterator(); fIter.hasNext();) {
FormatType fmt = (FormatType) fIter.next();
if(fmt!=null){
//if (fmt.getLessThanValue().length() > 0) {
cellStyle = wb.createCellStyle();
XSSFFont cellFont = wb.createFont();
//System.out.println("Format Id " + fmt.getFormatId());
if (nvl(fmt.getBgColor()).length() > 0) {
// System.out.println("Load Styles " +
// fmt.getFormatId()
// + " " +fmt.getBgColor() + " " +
// ExcelColorDef.getExcelColor(fmt.getBgColor()));
cellStyle.setFillForegroundColor(ExcelColorDef.getExcelColor(fmt
.getBgColor()));
cellStyle.setFillPattern(HSSFCellStyle.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.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
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("Tahoma");
//cellFont.setFontHeight((short) (10 / 0.05));
if(nvl(fmt.getFontSize()).length()>0) {
try {
cellFont.setFontHeight((short) (Integer.parseInt(fmt.getFontSize()) / 0.05));
} catch(NumberFormatException e){
cellFont.setFontHeight((short) (font_size / 0.05));
}
}
else
cellFont.setFontHeight((short) (font_size / 0.05));
cellStyle.setFont(cellFont);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
hashMapStyles.put(fmt.getFormatId(), cellStyle);
} else {
//hashMapStyles.put(fmt.getFormatId(), styleDefault);
hashMapStyles.put("default", styleDefault);
}
}
}
}*/
loadedStyles.putAll(hashMapStyles);
return loadedStyles;
}
private void generate(XSSFWorkbook wb, SpreadsheetWriter sw, Map 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;
/*short s1 = 0, s2 = (short) (col-1);
rowNum += 1;
sw.insertRow(rowNum);
int styleIndex = styles.get("header").getIndex();
sw.createCell(rowNum, reportTitle, styleIndex);
//header.setCenter(HSSFHeader.font("Tahoma", "")+ HSSFHeader.fontSize((short) 9)+reportTitle+"\n"+((Globals.getShowDescrAtRuntime() && nvl(reportDescr).length() > 0)?reportDescr:""));
// Report Description
if (Globals.getShowDescrAtRuntime() && nvl(reportDescr).length() > 0) {
sw.createCell(rowNum, reportDescr, styleIndex);
}
rowNum += 2;
sw.insertRow(rowNum);*/
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, "
", " ");
header = Utils.replaceInString(header, "
", " ");
header = Utils.replaceInString(header, "
", " ");
header = strip.stripHtml(nvl(header).trim());
subtitle = Utils.replaceInString(subtitle, "
", " ");
subtitle = Utils.replaceInString(subtitle, "
", " ");
subtitle = Utils.replaceInString(subtitle, "
", " ");
subtitle = strip.stripHtml(nvl(subtitle).trim());
//XSSFRow row = sheet.createRow(rowNum);
sw.insertRow(rowNum);
cellNum = 0;
//XSSFCell cell = row.createCell(cellNum);
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();
/* cell.setCellValue(Utils.excelEncode(header));
cell.setCellStyle(styles.get("title"));
*/ //sw.createCell(cellNum,Utils.excelEncode(header), styles.get("title").getIndex());
// sheet.addMergedRegion(new CellRangeAddress(rowNum+1, rowNum+1, cellNum+1, columnRows));
rowNum += 1;
// row = sheet.createRow(rowNum);
sw.insertRow(rowNum);
cellNum = 0;
/* cell = row.createCell(cellNum);
cell.setCellValue(Utils.excelEncode(subtitle));
cell.setCellStyle(styles.get("title"));
*/ //sw.createCell(cellNum,Utils.excelEncode(header), styles.get("title").getIndex());
sheet.addMergedRegion(new CellRangeAddress(rowNum+1, rowNum+1, cellNum+1, columnRows));
sw.createCell(cellNum, Utils.excelEncode(subtitle));
sw.endRow();
//sheet.addMergedRegion(new CellRangeAddress(rowNum+1, rowNum+1, cellNum+1, columnRows));
/* sw.insertRow(rowNum);
cellNum = 0;
sw.createCell(cellNum,Utils.excelEncode(subtitle), styles.get("title").getIndex());
sheet.addMergedRegion(new CellRangeAddress(rowNum+1, rowNum+1, cellNum+1, columnRows));
*/ rowNum += 1;
}
cellNum = 0;
String title = "";
for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext();) {
sw.insertRow(rowNum);
cellNum = -1;
/*if(rd.reportTotalRowHeaderCols!=null) {
cellNum +=1;
sw.createCell(cellNum, "No.", styles.get("header").getIndex());
//row.getCell((short) cellNum).setCellStyle(styleDataHeader);
}*/
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());
//sheet.addMergedRegion(new Region(rowNum, (short) cellNum, rowNum+columnRows, (short) (cellNum)));
//System.out.println(" **************** Row Header Title " + rhc.getColumnTitle() + " " + cellNum + " " );
//System.out.println(cellNum + " " + cellWidth.size());
} // 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());
}
//sheet.addMergedRegion(new Region(rowNum, (short) cellNum, rowNum, (short) (cellNum+colSpan)));
}
if(colSpan > 0)
cellNum += colSpan;
}
} // for
rowNum += 1;
} // for
sw.endRow();
//All the possible combinations of date format
CreationHelper createHelper = wb.getCreationHelper();
HashMap 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));
ResultSet rs = null;
Connection conn = null;
Statement st = null;
ResultSetMetaData rsmd = null;
if(nvl(sql_whole).length() >0 && (rr.getReportType().equals(AppConstants.RT_LINEAR) || rr.getReportType().equals(AppConstants.RT_HIVE) )) {
try {
conn = ConnectionUtils.getConnection(rr.getDbInfo());
st = conn.createStatement();
logger.debug(EELFLoggerDelegate.debugLogger, ("************* Map Whole SQL *************"));
logger.debug(EELFLoggerDelegate.debugLogger, (sql_whole));
logger.debug(EELFLoggerDelegate.debugLogger, ("*****************************************"));
rs = st.executeQuery(sql_whole);
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.getColumnName(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;
//if(rowCount%1000 == 0) wb.write(sos);
/*if(rd.reportTotalRowHeaderCols!=null) {
//cellNum = -1;
//for (rd.reportRowHeaderCols.resetNext(); rd.reportRowHeaderCols.hasNext();) {
cellNum += 1;
//RowHeaderCol rhc = rd.reportRowHeaderCols.getRowHeaderCol(0);
//if (firstPass)
// rhc.resetNext();
//RowHeader rh = rhc.getRowHeader(rowCount-1);
sw.createCell(cellNum, rowCount, styleDefaultCell.getIndex());
//} // for
}*/
firstPass = false;
//cellNum = -1;
for (dr.resetNext(); dr.hasNext();j++) {
styleCell = null;
//for (chr.resetNext(); chr.hasNext();) {
//ColumnHeader ch = chr.getNext();
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;
//System.out.println("Stripping HTML 1");
//cell.setCellValue(strip.stripHtml(dv.getDisplayValue()));
String dataType = (String) (dataTypeMap.get(dv.getColId()));
//System.out.println("Value " + value + " " + (( dataType !=null && dataType.equals("DATE")) || (dv.getColName()!=null && dv.getColName().toLowerCase().endsWith("date"))) );
if (dataType!=null && dataType.equals("NUMBER")){
//cellNumber = row.createCell((short) cellNum);
//cellNumber.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//cellNumber.setCellValue(dv.getDisplayValue());
//cellCurrencyNumber = row.createCell((short) cellNum);
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("-$") )) {
//if (dv.getDisplayValue().startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempDollar = dv.getDisplayValue().trim();
tempDollar = tempDollar.replaceAll(" ", "").substring(0);
tempDollar = tempDollar.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempDollar);
//System.out.println("Before copy Value |" + tempDollar);
//tempDollar = String.copyValueOf(tempDollar.toCharArray(), 1, tempDollar.length()-1);
//System.out.println("After copy Value |" + tempDollar);
if ((tempDollar.indexOf(","))!= -1){
tempDollar = tempDollar.replaceAll(",", "");
}
//System.out.println("The final string 1 is "+tempDollar);
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{
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
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("-$") )) {
//if (dv.getDisplayValue().startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempInt = value.trim();
tempInt = tempInt.replaceAll(" ", "").substring(0);
tempInt = tempInt.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempInt);
//System.out.println("Before copy Value |" + tempInt);
//tempInt = String.copyValueOf(tempInt.toCharArray(), 1, tempInt.length()-1);
//System.out.println("After copy Value |" + tempInt);
if ((tempInt.indexOf(","))!= -1){
tempInt = tempInt.replaceAll(",", "");
}
//System.out.println("The final string INT is "+tempInt);
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{
//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(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) {
//System.out.println("ExcelDate " + HSSFDateUtil.getExcelDate(date));
Calendar cal=Calendar.getInstance();
cal.setTime(date);
//sw.createCell(cellNum, cal,styles.get(nvl(/*dv.getFormatId()*/"","default")).getIndex());
//if(styleRowCell!=null)
sw.createCell(cellNum, cal, cellStyle.getIndex());
//else if (styleCell!=null)
//sw.createCell(cellNum, cal, cellStyle.getIndex());
//else
//sw.createCell(cellNum, cal, cellStyle.getIndex());
} else {
//cell.getCellStyle().setDataFormat((short)0);
//if(styleRowCell!=null)
sw.createCell(cellNum, Utils.excelEncode(value), cellStyle.getIndex());
//else if (styleCell!=null)
//sw.createCell(cellNum, Utils.excelEncode(value), cellStyle.getIndex());
//else
//sw.createCell(cellNum, Utils.excelEncode(value), cellStyle.getIndex());
}
//cellDate.setCellValue(date);
//cellDate.setCellValue(value); //cellDate.setCellValue(date);
//cellDate.setCellValue(dv.getDisplayValue());
} else if((dv.getDisplayTotal()!=null && dv.getDisplayTotal().equals("SUM(")) || (dv.getColName()!=null && dv.getColName().indexOf("999")!=-1)){
//cellNumber = row.createCell((short) cellNum);
//cellNumber.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//cellNumber.setCellValue(dv.getDisplayValue());
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("-$") )) {
//if (value.startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempDollar = value.trim();
tempDollar = tempDollar.replaceAll(" ", "").substring(0);
tempDollar = tempDollar.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempDollar);
//System.out.println("Before copy Value |" + tempDollar);
//tempDollar = String.copyValueOf(tempDollar.toCharArray(), 1, tempDollar.length()-1);
//System.out.println("After copy Value |" + tempDollar);
if ((tempDollar.indexOf(","))!= -1){
tempDollar = tempDollar.replaceAll(",", "");
}
//System.out.println("The final string 2IF is "+tempDollar);
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{
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
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("-$") )) {
//if (value.startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempInt = value.trim();
tempInt = tempInt.replaceAll(" ", "").substring(0);
tempInt = tempInt.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempInt);
//System.out.println("Before copy Value |" + tempInt);
//tempInt = String.copyValueOf(tempInt.toCharArray(), 1, tempInt.length()-1);
//System.out.println("After copy Value |" + tempInt);
if ((tempInt.indexOf(","))!= -1){
tempInt = tempInt.replaceAll(",", "");
}
//System.out.println("The final string INT 2 is "+tempInt);
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{
//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(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 {
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("General"));
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("-$") )) {
//cell.setCellStyle(styleCurrencyTotal);
}
else {
//cell.setCellStyle(styleTotal);
}
} else {
//cell.setCellStyle(styleDefaultTotal);
}
bold = true;
}
//System.out.println("2IF "+ (dr.isRowFormat()) + " " + (dv.isCellFormat()) + " " + (styles!=null));
if ((dr.isRowFormat() && !dv.isCellFormat()) && styles != null) {
//cell.setCellStyle((HSSFCellStyle) styles.get(nvl(dr.getFormatId(),"default")));
continue;
}
//System.out.println("3IF "+ (htmlFormat != null) + " " + (dv.getFormatId() != null) + " " + (bold == false) + " "+ (styles != null));
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);
} // dv.isVisible
}
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();
//rd.reportDataTotalRow.getNext();
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);
} finally {
try {
if(conn!=null)
conn.close();
if(st!=null)
st.close();
if(rs!=null)
rs.close();
} catch (SQLException ex) {
throw new RaptorException(ex);
}
}
String footer = (String) session.getAttribute("FOOTER_"+index);
if(nvl(footer).length()>0) {
footer = Utils.replaceInString(footer, "
", " ");
footer = Utils.replaceInString(footer, "
", " ");
footer = Utils.replaceInString(footer, "
", " ");
footer = strip.stripHtml(nvl(footer).trim());
rowNum += 1;
sw.insertRow(rowNum);
cellNum = 0;
sw.createCell(cellNum, footer.replaceAll("&", "&"), styleDefaultCell.getIndex());
sw.endRow();
rowNum += 1;
}
if(Globals.getShowDisclaimer()) {
rowNum += 1;
sw.insertRow(rowNum);
cellNum = 0;
sw.createCell(cellNum, org.openecomp.portalsdk.analytics.system.Globals.getFooterFirstLine().replaceAll("&", "&"), styleDefaultCell.getIndex());
sw.endRow();
rowNum += 1;
sw.insertRow(rowNum);
cellNum = 0;
sw.createCell(cellNum, org.openecomp.portalsdk.analytics.system.Globals.getFooterSecondLine().replaceAll("&", "&"), styleDefaultCell.getIndex());
sw.endRow();
}
} else {
//start data from rd
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) {
//cellNum = -1;
//for (rd.reportRowHeaderCols.resetNext(); rd.reportRowHeaderCols.hasNext();) {
//a commented to suppress rownum
//a cellNum += 1;
//RowHeaderCol rhc = rd.reportRowHeaderCols.getRowHeaderCol(0);
//if (firstPass)
// rhc.resetNext();
//RowHeader rh = rhc.getRowHeader(rowCount-1);
//a sw.createCell(cellNum, rowCount, styleDefaultCell.getIndex());
//} // for
}
}
firstPass = false;
//cellNum = -1;
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);
//System.out.println("Stripping HTML 1");
//cell.setCellValue(strip.stripHtml(value));
String dataType = (String) (dataTypeMap.get(dv.getColId()));
//System.out.println(" The Display Value is ********"+value + " " + dv.getDisplayTotal() + " " + dv.getColName());
if (dataType!=null && dataType.equals("NUMBER")){
//cellNumber = row.createCell((short) cellNum);
//cellNumber.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//cellNumber.setCellValue(value);
//cellCurrencyNumber = row.createCell((short) cellNum);
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("-$") )) {
//if (value.startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempDollar = value.trim();
tempDollar = tempDollar.replaceAll(" ", "").substring(0);
tempDollar = tempDollar.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempDollar);
//System.out.println("Before copy Value |" + tempDollar);
//tempDollar = String.copyValueOf(tempDollar.toCharArray(), 1, tempDollar.length()-1);
//System.out.println("After copy Value |" + tempDollar);
if ((tempDollar.indexOf(","))!= -1){
tempDollar = tempDollar.replaceAll(",", "");
}
//System.out.println("The final string 1 is "+tempDollar);
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{
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
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("-$") )) {
//if (value.startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempInt = value.trim();
tempInt = tempInt.replaceAll(" ", "").substring(0);
tempInt = tempInt.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempInt);
//System.out.println("Before copy Value |" + tempInt);
//tempInt = String.copyValueOf(tempInt.toCharArray(), 1, tempInt.length()-1);
//System.out.println("After copy Value |" + tempInt);
if ((tempInt.indexOf(","))!= -1){
tempInt = tempInt.replaceAll(",", "");
}
//System.out.println("The final string INT is "+tempInt);
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{
//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(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());
}
}
//int temp = Integer.parseInt(value.trim());
// cell.setCellValue(temp);
//}else{
// cell.setCellValue(strip.stripHtml(value));
//}
}
}
}
}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);
//sw.createCell(cellNum, cal,styles.get(nvl(/*dv.getFormatId()*/"","default")).getIndex());
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 {
/*cell.getCellStyle().setDataFormat((short)0);*/
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());
}
//cellDate.setCellValue(date);
//cellDate.setCellValue(value);
}else if((dv.getDisplayTotal()!=null && dv.getDisplayTotal().equals("SUM(")) || (dv.getColName()!=null && dv.getColName().indexOf("999")!=-1)){
//cellNumber = row.createCell((short) cellNum);
//cellNumber.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//cellNumber.setCellValue(value);
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("-$") )) {
//if (value.startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempDollar = value.trim();
tempDollar = tempDollar.replaceAll(" ", "").substring(0);
tempDollar = tempDollar.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempDollar);
//System.out.println("Before copy Value |" + tempDollar);
//tempDollar = String.copyValueOf(tempDollar.toCharArray(), 1, tempDollar.length()-1);
//System.out.println("After copy Value |" + tempDollar);
if ((tempDollar.indexOf(","))!= -1){
tempDollar = tempDollar.replaceAll(",", "");
}
//System.out.println("The final string 2IF is "+tempDollar);
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{
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
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("-$") )) {
//if (value.startsWith("$")){
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0.00);($#,##0.00)"));
String tempInt = value.trim();
tempInt = tempInt.replaceAll(" ", "").substring(0);
tempInt = tempInt.replaceAll("\\$", "").substring(0);
//System.out.println("SUBSTRING |" + tempInt);
//System.out.println("Before copy Value |" + tempInt);
//tempInt = String.copyValueOf(tempInt.toCharArray(), 1, tempInt.length()-1);
//System.out.println("After copy Value |" + tempInt);
if ((tempInt.indexOf(","))!= -1){
tempInt = tempInt.replaceAll(",", "");
}
//System.out.println("The final string INT 2 is "+tempInt);
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{
//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(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());
}
}
//int temp = Integer.parseInt(value.trim());
// cell.setCellValue(temp);
//}else{
// cell.setCellValue(strip.stripHtml(value));
//}
} else {
sw.createCell(cellNum, "", styles.get(nvl(/*dv.getFormatId(),*/"","default")).getIndex());
}
}
}
}
else {
//styleDefault.setDataFormat(HSSFDataFormat.getBuiltinFormat("General"));
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;
}
//System.out.println("2IF "+ (dr.isRowFormat()) + " " + (dv.isCellFormat()) + " " + (styles!=null));
if ((dr.isRowFormat() && !dv.isCellFormat()) && styles != null) {
//cell.setCellStyle((HSSFCellStyle) styles.get(nvl(dr.getFormatId(),"default")));
continue;
}
//System.out.println("3IF "+ (htmlFormat != null) + " " + (dv.getFormatId() != null) + " " + (bold == false) + " "+ (styles != null));
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; tmp0) {
footer = Utils.replaceInString(footer, "
", " ");
footer = Utils.replaceInString(footer, "
", " ");
footer = Utils.replaceInString(footer, "
", " ");
footer = strip.stripHtml(nvl(footer).trim());
rowNum += 1;
sw.insertRow(rowNum);
cellNum = 0;
sw.createCell(cellNum, footer.replaceAll("&", "&"), styleDefaultCell.getIndex());
sw.endRow();
rowNum += 1;
}
if(Globals.getShowDisclaimer()) {
rowNum += 1;
sw.insertRow(rowNum);
cellNum = 0;
sw.createCell(cellNum, org.openecomp.portalsdk.analytics.system.Globals.getFooterFirstLine().replaceAll("&", "&"), styleDefaultCell.getIndex());
sw.endRow();
rowNum += 1;
sw.insertRow(rowNum);
cellNum = 0;
sw.createCell(cellNum, org.openecomp.portalsdk.analytics.system.Globals.getFooterSecondLine().replaceAll("&", "&"), styleDefaultCell.getIndex());
sw.endRow();
}
}
// end data from rd
}
// System.out.println(" Last Row " + wb.getSheetAt(0).getLastRowNum());
}
private void paintXSSFExcelParams(XSSFWorkbook wb,int rowNum,int col,ArrayList paramsList, String customizedParamInfo, XSSFSheet sheet, String reportTitle, String reportDescr) throws IOException {
//HSSFSheet sheet = wb.getSheet(getSheetName());
int cellNum = 0;
XSSFRow row = null;
short s1 = 0, s2 = (short) 1;
HtmlStripper strip = new HtmlStripper();
// Name Style
XSSFCellStyle styleName = wb.createCellStyle();
//styleName.setFillBackgroundColor(HSSFColor.GREY_80_PERCENT.index);
styleName.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
//styleName.setFillPattern(HSSFCellStyle.SPARSE_DOTS);
styleName.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleName.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleName.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleName.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleName.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleName.setDataFormat((short)0);
XSSFFont font = wb.createFont();
font.setFontHeight((short) (font_size / 0.05));
font.setFontName("Tahoma");
font.setColor(HSSFColor.BLACK.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
styleName.setFont(font);
//Data Style
// Create some fonts.
XSSFFont 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("Tahoma");
fontDefault.setItalic(true);
// Style default will be normal with no background
XSSFCellStyle styleValue = wb.createCellStyle();
styleValue.setDataFormat((short)0);
styleValue.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleValue.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleValue.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleValue.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleValue.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleValue.setFillForegroundColor(HSSFColor.YELLOW.index);
styleValue.setFillPattern(HSSFCellStyle.NO_FILL);
styleValue.setFont(fontDefault);
XSSFCell cell = null;
XSSFCellStyle styleDescription = wb.createCellStyle();
styleDescription.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// styleDescription.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// styleDescription.setBorderTop(HSSFCellStyle.BORDER_THIN);
// styleDescription.setBorderRight(HSSFCellStyle.BORDER_THIN);
// styleDescription.setBorderLeft(HSSFCellStyle.BORDER_THIN);
XSSFFont fontDescr = wb.createFont();
fontDescr.setFontHeight((short) (font_header_descr_size / 0.05));
fontDescr.setFontName("Tahoma");
fontDescr.setColor(HSSFColor.BLACK.index);
fontDescr.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
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();
//System.out.println("\"" + value.getId() + " = " + value.getName() + "\"");
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("Run-time Parameters");
cellDescr.setCellStyle(styleDescription);
strBuf.append(reportTitle+"\n");
//strBuf.append("Run-time Parameters\n");
}
row = sheet.createRow(++rowNum);
cellNum = 0;
//System.out.println("RowNum " + rowNum + " " + value.getId() + " " +value.getName());
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);
//strBuf.append(value.getId()+": "+ value.getName()+"\n");
}
} //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 { /*if ((p.get(k) instanceof PdfPTable))*/
pdfTable = ((PdfPTable)p.get(k));
}
//todo: Logic for parsing pdfTable should be added after upgrading to iText 5.0.0
//s = Utils.replaceInString(s, ",", "|");
s = s.replaceAll(",", "|");
s = s.replaceAll("~", ",");
if(s.indexOf(":")!= -1) {
//System.out.println("|"+s+"|");
row = sheet.createRow(++rowNum);
cell = row.createCell((short) 0);
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, s1, s2));
cellDescr = row.createCell((short) 0);
cellDescr.setCellValue("Run-time Parameters");
cellDescr.setCellStyle(styleDescription);
//strBuf.append("Run-time Parameters\n");
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);
} /*else if(name != null && name.length() > 0) {
value = st.nextToken();
if(value.endsWith("]]"))value = value.substring(0, 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);
//strBuf.append(name.trim()+": "+ value.trim()+"\n");
}
/* 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(i!=cellWidth.size()-1)
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) {
//ex.printStackTrace();
}
}
}
/* 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 Region(rowNum, s1, rowNum, s2));
cell.setCellValue(strip.stripHtml(customizedParamInfo));
}
*/
/* rowNum += 2;
row = sheet.createRow(rowNum);*/
} // if
Iterator iterCheck = paramsList.iterator();
if(iterCheck.hasNext()) {
rowNum += 2;
row = sheet.createRow(rowNum);
}
header.setCenter(HSSFHeader.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 {
//response.setContentType("application/vnd.ms-excel");
//response.setHeader("Content-disposition",
// "attachment; filename=" +
// "Example.xls" );
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("\n" +
"" + rr.getReportName() + "\n" +
"\n" );
System.out.println("HTML-Excel Generation Triggered: " + new java.util.Date());
csvOut.print("");
if (Globals.getPrintParamsInCSVDownload()) {
ArrayList paramsList = rr.getParamNameValuePairsforPDFExcel(request, 1);
int paramSeq = 0;
for (Iterator iter = paramsList.iterator(); iter.hasNext();) {
IdNameValue value = (IdNameValue) iter.next();
//System.out.println("\"" + value.getId() + " = " + value.getName() + "\"");
if(nvl(value.getId()).trim().length()>0 && (!nvl(value.getId()).trim().equals("BLANK"))) {
paramSeq += 1;
if(paramSeq <= 1) {
csvOut.println("| " + "Run-time Parameters" + " |
");
//strBuf.append("Run-time Parameters\n");
}
csvOut.println("| " + value.getId() +" | ");
csvOut.println("" + value.getName().replaceAll("~",",")+ " | ");
csvOut.println("
");
//strBuf.append(value.getId()+": "+ value.getName()+"\n");
}
} //for
csvOut.println("| |
");
csvOut.println("| |
");
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 callable = new ExecuteQuery(st, sql_whole, downloadLimit);
ExecutorService executor = new ScheduledThreadPoolExecutor(5);
System.out.println("Time Started" + new java.util.Date());
Future future = executor.submit(callable);
try {
rs = future.get(900, TimeUnit.SECONDS);
} catch (TimeoutException ex) {
System.out.println("Cancelling Query");
st.cancel();
System.out.println("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("");
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
while(rs.next()) {
csvOut.println("");
/* 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));
}
for (chr.resetNext(); chr.hasNext();) {
ColumnHeader ch = chr.getNext();
if(ch.isVisible()) {
csvOut.println("| " + strip.stripCSVHtml(nvl((String)colHash.get(ch.getLinkColId().toUpperCase()))) + " | ");
}
}
csvOut.println("
");
}
System.out.println("Downloaded Rows in HTML-Excel " + rowCount + " : "+ new java.util.Date());
if(rowCount == 0) {
csvOut.print("| No Data Found |
");
} else {
}
} else {
csvOut.println("| No Data Found |
");
}
csvOut.println("
\n");
} catch (SQLException ex) {
throw new RaptorException(ex);
} catch (ReportSQLException ex) {
throw new RaptorException(ex);
} catch (Exception 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) {
throw new RaptorException(ex);
}
}
//csvOut.flush();
} else {
boolean firstPass = true;
int numberOfColumns = 0;
if(rd!=null) {
for (rd.reportColumnHeaderRows.resetNext(); rd.reportColumnHeaderRows.hasNext();) {
chr = rd.reportColumnHeaderRows.getNext();
csvOut.println("");
for (rd.reportRowHeaderCols.resetNext(1); rd.reportRowHeaderCols.hasNext();) {
RowHeaderCol rhc = rd.reportRowHeaderCols.getNext();
if (firstPass) {
numberOfColumns++;
csvOut.print("| " + rhc.getColumnTitle() + " | ");
}
//csvOut.print(",");
} // for
for (chr.resetNext(); chr.hasNext();) {
ColumnHeader ch = chr.getNext();
if(ch.isVisible()) {
if(firstPass) numberOfColumns++;
csvOut.print("" + ch.getColumnTitle() + " | ");
//for (int i = 1; i < ch.getColSpan(); i++)
//csvOut.print(",");
}
} // for
firstPass = false;
csvOut.println("
");
} // for
firstPass = true;
for (rd.reportDataRows.resetNext(); rd.reportDataRows.hasNext();) {
DataRow dr = rd.reportDataRows.getNext();
csvOut.println("");
for (rd.reportRowHeaderCols.resetNext(1); rd.reportRowHeaderCols.hasNext();) {
RowHeaderCol rhc = rd.reportRowHeaderCols.getNext();
if (firstPass)
rhc.resetNext();
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
//csvOut.flush();
} else {
csvOut.println("| No Data Found |
");
}
}
csvOut.println(" \n