Sample function on how to generate an excel

Advantages of below method is Query and the template columns are dynamic
----------------------------------------------------------------------------------------------------

public static int generateTemplateDashboard(String MONTH,String YEAR,String TEMPLATE_NAME)
{
int response=0;
String responseStr="<OperationResult><Status/><StatusMessage/><FilePath/></OperationResult>";
Document doc = new Document();
String queryText="";
QueryObject query = null;
BusObjectIterator wholsaleItems=null;
int templateData=0;
int queryTemplateConfig=0;
int temptableHeaderData=0;
String excelHeaders[]=null;
String columnNames[]=null;
String dataTypes[]=null;
String columnData[]=null;
int responseData=0;
try{
oDoc=BSF.getXMLDocument();
response = doc.parseString(responseStr);
//Loading Query Template from configuration
queryTemplateConfig = oDoc.load(installDir + "\\com\\vw\\mars\\ExportXLQueriesConfig.xml");
queryText = Node.getData(XPath.getFirstMatch(".//QUERY[@TEMPLATE_NAME='"+TEMPLATE_NAME+"']", null, queryTemplateConfig));
logger.log(Severity.WARN, "Logging the query text as "+queryText);
if(queryText==null || queryText.equalsIgnoreCase(""))
throw new BsfRuntimeException("Query Template not found from the configuration");
query = new QueryObject(queryText);
query.addParameter("MONTH", "MARS_WHOLSALE_FORECASTS.MONTH", QueryObject.PARAM_STRING, MONTH);
query.addParameter("YEAR", "MARS_WHOLSALE_FORECASTS.YEAR", QueryObject.PARAM_STRING, YEAR);
wholsaleItems = query.getObjects();
if(logger.isWarningEnabled())
logger.log(Severity.WARN, "Query got executed with data "+Node.writeToString(query.execute().getDatasetNode(), true));
//Write the header of the excel
HSSFWorkbook wb = null;
HSSFSheet marsSheet = null;
Row header = null;
String filePath = props.getProperty("mars.download.directory");
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String currentTimeStamp = sdf.format(getCurrentDate());
String fileName = "Dashboard_"+YEAR + "_" + MONTH + "_" + currentTimeStamp + ".xls";
String abPth = "";
abPth = filePath + "\\"+TEMPLATE_NAME;
logger.log(Severity.WARN, "Logging absolute path as "+abPth);
//Creating the directory if it is not present
File createFolders = new File(filePath + "\\" + YEAR + "\\" + MONTH);
createFolders.mkdirs();
logger.log(Severity.WARN,"Directory created");
wb = new HSSFWorkbook(); // new HSSFWorkbook();
marsSheet = wb.createSheet(TEMPLATE_NAME);
header = marsSheet.createRow(0);

//Read the header columns from ExportConfig.xml
templateData = oDoc.load(installDir+ "\\com\\vw\\mars\\ExportXLConfig.xml");
logger.log(Severity.WARN, "Logging Export XL Config file "+Node.writeToString(templateData,true));
temptableHeaderData = XPath.getFirstMatch(".//*[local-name()='filetype'][@name='"+TEMPLATE_NAME+"']", null, templateData);
logger.log(Severity.WARN, "Logging template header data node "+Node.writeToString(temptableHeaderData,true));

int length = XPath.getMatchingNodes(".//*[local-name()='field']", null, temptableHeaderData).length;
excelHeaders = new String[length];
columnNames = new String[length];
dataTypes = new String[length];
columnData = new String[length];
for (int i = 0; i < length; i++) {
excelHeaders[i] = Node.getAttribute(XPath.getFirstMatch(".//*[local-name()='field'][@cell='" + i + "']",null, temptableHeaderData), "header_name", "");
columnNames[i] = Node.getAttribute(XPath.getFirstMatch(".//*[local-name()='field'][@cell='" + i + "']",null, temptableHeaderData), "name", "");
dataTypes[i] = Node.getAttribute(XPath.getFirstMatch(".//*[local-name()='field'][@cell='" + i + "']",null, temptableHeaderData), "datatype", "");
}
logger.log(Severity.WARN,"Finished reading the template and stored all the headers with datatypes into string arrays ");
for (int i = 0; i < excelHeaders.length; i++)
header.createCell(i).setCellValue(excelHeaders[i]);
logger.log(Severity.WARN, "Finished writing headers of the dashboard excel");
responseData = TupleHandling.makeTupleSet(wholsaleItems, null);
int tupleRecords[] = XPath.getMatchingNodes(
".//*[local-name()='tuple']", null, responseData);
for (int i = 0; i < tupleRecords.length; i++) {
header = marsSheet.createRow(i + 1);
for (int j = 0; j < columnNames.length; j++) {
columnData[j] = Node.getData(Node.getFirstChild(XPath.getFirstMatch(".//*[local-name()='"+ columnNames[j]+ "']", null,tupleRecords[i])));
logger.log(Severity.WARN, "Reading Column value as "+columnData[j]);
}
if (tupleRecords[i] > 0)
Node.delete(tupleRecords[i]);
logger.log(Severity.WARN,"Deleted the tupleRecord[i]");
for (int j = 0; j < columnData.length; j++)
{
if(dataTypes[j].equalsIgnoreCase("NUMERIC"))
{
header.createCell(j).setCellValue(Integer.valueOf(columnData[j]));
header.getCell(j).setCellType(Cell.CELL_TYPE_NUMERIC);
}
else if(dataTypes[j].equalsIgnoreCase("STRING"))
{
header.createCell(j).setCellValue(columnData[j]);
header.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
}
}
}
for (int i = 0; i < excelHeaders.length; i++)
marsSheet.autoSizeColumn(i);
FileOutputStream fileOutputStream = new FileOutputStream(filePath + "\\" + YEAR + "\\" + MONTH + "\\"+ fileName);
wb.write(fileOutputStream);
fileOutputStream.close();
logger.log(Severity.WARN, "Successfully written the excel template at "+filePath);
Node.setDataElement(response, "Status", "SUCCESS");
Node.setDataElement(response, "StatusMessage", "Application is successful in generating zonal allocation dashboard excel");
Node.setDataElement(response, "FilePath", filePath);
}
catch(Exception e)
{
Node.setDataElement(response, "Status", "FAILED");
Node.setDataElement(response, "StatusMessage", "Application has encountered exception while generating dashboard with details "+e.getMessage());
logger.log(Severity.ERROR,"Exception in generateTemplateDashboard ",e);
}
finally{
if(Node.isValidNode(templateData))
Node.delete(templateData);
if(Node.isValidNode(temptableHeaderData))
Node.delete(temptableHeaderData);
if(Node.isValidNode(queryTemplateConfig))
Node.delete(queryTemplateConfig);
if(Node.isValidNode(responseData))
Node.delete(responseData);
query = null;
wholsaleItems = null;
}
return response;
}