Export with ExcelExport Component
The easiest way to do the export is to use the ExcelExport component.
Export Manually
This is a quick how to for getting the contents of an html table rendered with the dataTable control to be exported to MS-Excel. This example make use of two session beans. One session bean is the normal backing bean. The second session bean shown below will be one that stores the html data table in a string and spits it back out to excel.
Step 1. Create your JSF dataTable as you normally would.
<t:dataTable
id="mainDataTable"
rendered="#{!empty(backingBean.tableList)}"
value="#{backingBean.tableList}"
var="row">
<t:column sortable="true">
<f:facet name="header">
<h:outputText value="My Column"/>
</f:facet>
<h:outputText value="#{row.datafield}"/>
<f:facet name="footer"/>
</t:column>
</t:dataTable>Step 2: Capture this dataTable into a string variable in a session bean using the buffer control. Don't forget to output this buffered table so that your users can see it!
<t:buffer into="#{export.htmlBuffer}">
<t:dataTable
id="mainDataTable"
rendered="#{!empty(backingBean.tableList)}"
value="#{backingBean.tableList}"
var="row">
<t:column sortable="true">
<f:facet name="header">
<h:outputText value="My Column"/>
</f:facet>
<h:outputText value="#{row.datafield}"/>
<f:facet name="footer"/>
</t:column>
</t:dataTable>
</t:buffer>
<h:outputText value="#{export.htmlBuffer}" escape="false"/>Step 3: Add a command link that will call a method in your session bean to export your html dataTable out to excel.
<h:commandLink action="#{export.exportHtmlTableToExcel}">
<h:outputText value="Export table to Excel"/>
</h:commandLink>
<t:buffer into="#{export.htmlBuffer}">
<t:dataTable
id="mainDataTable"
rendered="#{!empty(backingBean.tableList)}"
value="#{backingBean.tableList}"
var="row">
<t:column sortable="true" >
<f:facet name="header">
<h:outputText value="My Column"/>
</f:facet>
<h:outputText value="#{row.datafield}"/>
<f:facet name="footer"/>
</t:column>
</t:dataTable>
</t:buffer>
<h:outputText value="#{export.htmlBuffer}" escape="false"/>Below is what your export session bean might look like. This bean has two export methods. Both methods assume that only one html table will be in the htmlBuffer String. Both methods rely on the joda lib to generate filenames.
The first exportHtmlTableToExcel just sends what was captured by the buffer control back out to the users and changes the content type to application/vnd.ms-excel which should cause Excel to launch if its installed on the users PC. The upside to this approach is that Excel will know how to deal with the html you give it. The downside is that Excel will just treat the html as html.
The second method will give you a bit more control. This method uses both the htmlparser lib found on sourceforge and the POI lib which is part of the Apache Jakata project. The htmlparser lib lets you parse and get info about the html data you are examining. The POI lib actually lets you create and control an Excel file. Once you create and populate this file you can then write out this file to a stream. This method is more complex but gives you more control over what your users will be presented with in their Excel file. I tried to demo some ways of dealing with the html header and footer and how it might be presented to the users. Some of these file attributes include word wrapping, pane freezing and paper size.
Now, this second method may not be the best way to approach this problem but it does work and can serve as a good starting place for your own export method. It should also give you an idea of what you can do with the htmlparser and POI libs. All of this is of course made possible by the very cool and useful buffer control.
package com.gesker.bean;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.regex.Pattern;
import javax.faces.context.FacesContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.htmlparser.Node;
import org.htmlparser.Parser;
import org.htmlparser.filters.TagNameFilter;
import org.htmlparser.util.NodeList;
import org.htmlparser.util.ParserException;
import org.htmlparser.util.ParserUtils;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFFooter;
public class Export {
private String htmlBuffer;
public Export() {
}
public void exportHtmlTableToExcel() throws IOException{
//Set the filename
DateTime dt = new DateTime();
DateTimeFormatter fmt = DateTimeFormat.forPattern("yyyy-MM-dd_HHmmss");
String filename = dt.toString(fmt) + ".xls";
//Setup the output
String contentType = "application/vnd.ms-excel";
FacesContext fc = FacesContext.getCurrentInstance();
filename = fc.getExternalContext().getUserPrincipal().toString() + "-"+ filename;
HttpServletResponse response = (HttpServletResponse)fc.getExternalContext().getResponse();
response.setHeader("Content-disposition", "attachment; filename=" + filename);
response.setContentType(contentType);
//Write the table back out
PrintWriter out = response.getWriter();
out.print(htmlBuffer);
out.close();
fc.responseComplete();
}
public void exportHtmlTableAsExcel() throws IOException{
int rowCount = 0;
int colCount = 0;
//Set the filename
DateTime dt = new DateTime();
DateTimeFormatter fmt = DateTimeFormat.forPattern("yyyy-MM-dd_HHmmss");
String filename = dt.toString(fmt);
// Create Excel Workbook and Sheet
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(filename);
HSSFHeader header = sheet.getHeader();
header.setCenter(filename);
//Setup the output
String contentType = "application/vnd.ms-excel";
FacesContext fc = FacesContext.getCurrentInstance();
filename = fc.getExternalContext().getUserPrincipal().toString() + "-"+ filename;
HttpServletResponse response = (HttpServletResponse)fc.getExternalContext().getResponse();
filename = filename + ".xls";
response.setHeader("Content-disposition", "attachment; filename=" + filename);
response.setContentType(contentType);
ServletOutputStream out = response.getOutputStream();
try{
htmlBuffer = "<html>" + htmlBuffer + "</html>";
Parser parser = new Parser();
parser.setInputHTML(htmlBuffer);
NodeList nodelist = parser.parse(null);
Node node = nodelist.elementAt(0);
//nodelist.visitAllNodesWith(new TableVisitor());
NodeList tableList = nodelist.extractAllNodesThatMatch(new TagNameFilter("TABLE"), true);
NodeList headList = tableList.extractAllNodesThatMatch(new TagNameFilter("THEAD"), true);
NodeList bodyList = tableList.extractAllNodesThatMatch(new TagNameFilter("TBODY"), true);
NodeList footList = tableList.extractAllNodesThatMatch(new TagNameFilter("TFOOT"), true);
NodeList rowList = tableList.extractAllNodesThatMatch(new TagNameFilter("TR"), true);
//Create a ParserUtils var
ParserUtils pu = new ParserUtils();
//Set rowCount to size of rowList
rowCount = rowList.size();
HSSFFont boldFont = wb.createFont();
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle boldStyle = wb.createCellStyle();
boldStyle.setFont(boldFont);
boldStyle.setWrapText(true);
//Loop through excel 'Rows'
for ( int i = 0; i < rowList.size(); i++ ) {
HSSFRow row;
String htmlRow = rowList.elementAt(i).toHtml().trim();
String[] splitHtmlRow;
if(i == 0 && headList.size() == 1){
row = sheet.createRow((short) i);
htmlRow = htmlRow.replace("<th></th>","<th> </th>");
htmlRow = htmlRow.replace("<TH></TH>","<th> </th>");
splitHtmlRow = pu.splitTags(htmlRow, new String[] {"tr", "th", "a"}, true, false);
/*if your column headers don't links use
splitHtmlRow = pu.splitTags(htmlRow, new String[] {"tr", "th"}, true, false); */
colCount = splitHtmlRow.length;
} else if(i == 1 && footList.size() == 1){
row = sheet.createRow((short) rowList.size() - 1);
htmlRow = pu.trimTags(htmlRow, new String[]{"span"},false,false);
htmlRow = htmlRow.replace("<td></td>","<td> </td>");
htmlRow = htmlRow.replace("<TD></TD>","<td> </td>");
splitHtmlRow = pu.splitTags(htmlRow, new String[] {"tr", "td"}, true, false);
colCount = splitHtmlRow.length;
} else {
if (footList.size() == 1){
row = sheet.createRow((short) i - 1);
} else {
row = sheet.createRow((short) i);
}
htmlRow = htmlRow.replace("<td></td>","<td> </td>");
htmlRow = htmlRow.replace("<TD></TD>","<td> </td>");
//if your h:dataTable has columnclass attribute uncomment 2 lines below
//htmlRow = htmlRow.replace("</span>", " </span>");
//splitHtmlRow = ParserUtils.splitTags(htmlRow, new String[]{"tr", "td", "span"}, true, false);
splitHtmlRow = pu.splitTags(htmlRow, new String[] {"tr", "td"}, true, false);
colCount = splitHtmlRow.length;
}
//Loop through excel 'Columns'
for (int j = 0; j < splitHtmlRow.length; j++){
HSSFCell cell = row.createCell((short) j);
// Calculate what the column width should be.
// Increase if the current width is samller than
// the calculated width.
int width = splitHtmlRow[j].length() * 325;
if(width > sheet.getColumnWidth((short)j)){
sheet.setColumnWidth((short)j, (short)width);
}
//Wrap Text in the Cell for the Header Row
if(i == 0 && headList.size() == 1){
// Determine the width of the column head
Pattern p = Pattern.compile(" ");
String[] splitHead = p.split(splitHtmlRow[j]);
int wordCnt = splitHead.length;
for (int q = 0; q < splitHead.length; q++){
if(splitHead[q].length() * 325 > width)
width = splitHead[q].length() * 325;
sheet.setColumnWidth((short)j, (short)width);
}
// Dertermin the height of the column head
int height = wordCnt * 275;
if(row.getHeight() < height){
row.setHeight((short)height);
}
// Add new line to cell content and make the cell
// word wrap
splitHtmlRow[j] = splitHtmlRow[j].replaceAll(" ", " \n");
//Set Cell to boldStyle
cell.setCellStyle(boldStyle);
}
//Populate Cell
if(splitHtmlRow[j] == null){
cell.setCellValue("");
}else{
cell.setCellValue(splitHtmlRow[j]);
}
}
}
}catch(ParserException p){ p.printStackTrace(); }
// Do stuff the Excel SpreaSheet
// Freeze Panes on First Row
sheet.createFreezePane(0,1);
// Row 1 Repeats on each page
wb.setRepeatingRowsAndColumns(0,0,0,0,1);
// Set Print Area, Footer
wb.setPrintArea(0, 0, colCount, 0, rowCount);
HSSFFooter footer = sheet.getFooter();
footer.setCenter("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages());
// Fit Sheet to 1 page wide but very long
sheet.setAutobreaks(true);
HSSFPrintSetup ps = sheet.getPrintSetup();
ps.setFitWidth((short)1);
ps.setFitHeight((short)9999);
sheet.setGridsPrinted(true);
sheet.setHorizontallyCenter(true);
ps.setPaperSize(HSSFPrintSetup.LETTER_PAPERSIZE);
if(colCount > 5){ps.setLandscape(true);}
if(colCount > 10){ps.setPaperSize(HSSFPrintSetup.LEGAL_PAPERSIZE);}
if(colCount > 14){ps.setPaperSize(HSSFPrintSetup.EXECUTIVE_PAPERSIZE);}
// Set Margins
ps.setHeaderMargin((double) .35);
ps.setFooterMargin((double) .35);
sheet.setMargin(HSSFSheet.TopMargin, (double) .50);
sheet.setMargin(HSSFSheet.BottomMargin, (double) .50);
sheet.setMargin(HSSFSheet.LeftMargin, (double) .50);
sheet.setMargin(HSSFSheet.RightMargin, (double) .50);
//Write out the spreadsheet
wb.write(out);
out.close();
fc.responseComplete();
}
public String getHtmlBuffer() {
return htmlBuffer;
}
public void setHtmlBuffer(String htmlBuffer) {
this.htmlBuffer = htmlBuffer;
}
}