How to style a row using POI

/*To style a row a cellStyle instance to be used in POI API and below steps are to be performed*/

/*Create an instance of HSSFCellStyle and it can be created as below
Below wb corresponds to HSSFWorkbook() class
wb = new HSSFWorkbook();
 */

HSSFCellStyle cellStyle = wb.createCellStyle();

/*Then a setFillForegroundColor method to be called using the color index. The color index is a short variable and defined as below*/

cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

/*A setFillPattern method is to be called with another short variable to be sent as a variable*/

cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

/*Finally a cellStyle variable is ready and can be used to apply style but note that this style will be over written when you try to write value in the cell*/
/*The same style should be applied when writing the cellValue as shown further in the code*/

header.setRowStyle(cellStyle);

/*This creates a cell with value as 'Sample value' not this will remove the styling applied to the row and hence the styling should be applied again*/ 

header.createCell(1).setCellValue("Sample value");

/*As explained before the styling pattern is applied again*/

header.getCell(1).setCellStyle(cellStyle);
fileOutputStream = new FileOutputStream(filePath +"\\ExcelUtilities.xls");
wb.write(fileOutputStream);
fileOutputStream.close();

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

Read content from XML File and write into EXCEL

package com.DH.Explorations;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import com.eibus.xml.nom.Document;
import com.eibus.xml.nom.Node;
import com.eibus.xml.nom.XMLException;
import com.eibus.xml.xpath.XPath;

public class WriteExcel {

public static void main(String[] args) throws IOException, XMLException {
// TODO Auto-generated method stub
HSSFWorkbook workbook=new HSSFWorkbook();
Document doc = new Document();
int configXML=0;
configXML = doc.load("D:\\bFOIntegrations\\WorkOrderInfo.xml");
System.out.println(Node.writeToString(configXML, true));
HSSFSheet Sheet=workbook.createSheet("WorkOrderRecords") ;
int []workOrderItems = XPath.getMatchingNodes(".//WorkItemRecord", null, configXML);
for(int i=0;i<workOrderItems.length;i++)
{
Row row = Sheet.createRow(i);
String bFO = Node.getAttribute(XPath.getFirstMatch(".//Keys", null, workOrderItems[i]), "bFO");
String BO = Node.getAttribute(XPath.getFirstMatch(".//Keys", null, workOrderItems[i]), "BO");
String Status = Node.getDataElement(workOrderItems[i], "Status", "");
String statusMsg =Node.getDataElement(workOrderItems[i], "StatusMsg", "");
Cell cell_0 = row.createCell(0);
Cell cell_1 = row.createCell(1);
Cell cell_2 = row.createCell(2);
Cell cell_3 = row.createCell(3);
cell_0.setCellValue(bFO);
cell_1.setCellValue(BO);
cell_2.setCellValue(Status);
cell_3.setCellValue(statusMsg);
}
File fileDirectory;
String FilePath="D:\\temp";
fileDirectory = new File(FilePath);
if(!fileDirectory.exists())
fileDirectory.mkdirs();
FilePath=FilePath+"\\"+(new Date().getTime())+".xls";
FileOutputStream out = new FileOutputStream(new File(FilePath));
workbook.write(out);
}
}

Fastest Approach to Generate Excel File

The given jar fastexcel-0.5.1-2010-12-26.jar can write thousands of records into an excel file with in few seconds.

Here is the sample code:
public Static String fastExcelSample() {
File fileObj=new File("testFile.xls");
String contentData[]={"FAST","EXCEL","EXAMPLE","TEST"};
Workbook wb=FastExcel.createWriteableWorkbook(fileObj);
wb.open();
Sheet sheet=wb.addStreamSheet("SheetA");
sheet.addRow(contentData);
wb.close();
}
addRow() method adds the Row into the sheet.  For multiple rows we can iterate and add multiple rows.
contentData[ ]  can be an object, which holds all the cell values of row and then write it into Excel file.
I have used it, it could able to write 20,000 records(each record 40 columns) in less than 20sec time.
Please find the attached jar file.


https://drive.google.com/file/d/0B25humeDHAtFMmVsNHVmenBmc3c/edit?usp=sharing