How to export excel file with xlsx extension in displaytag

Question

We used displaytag for exporting the files in xls format but I want it in xlsx format. Is there any way to convert the excel file to new format?

I change the extension in display tag from xls to xlsx

<display:setProperty name="export.excel" value="true"/>
<display:setProperty name="export.excel.filename" value="assignedUserGroup.xlsx" />

but it gives me "File extension is not valid" when I opened it in office 2007 or 2010.

score 7 · Accepted Answer · edited Jun 20 '20 at 09:12

This is very useful question.

Step 1 : Make a package com.displaytag.export.views

Step 2 : Make a class myExcel2007ExportView.java in the above package.

Step 3 : Copy and paste following code in that file

package com.displaytag.export.views;


import java.io.IOException;
import java.io.OutputStream;
import java.util.Iterator;
import javax.servlet.jsp.JspException;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.displaytag.export.BinaryExportView;
import org.displaytag.model.Column;
import org.displaytag.model.ColumnIterator;
import org.displaytag.model.HeaderCell;
import org.displaytag.model.RowIterator;
import org.displaytag.model.TableModel;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.lowagie.text.BadElementException;

public class myExcel2007ExportView implements BinaryExportView{

 //private static Log log = LogFactory.getLog(myExcel2007ExportView.class);
private TableModel model;
private boolean exportFull;
private boolean header;
private boolean decorated;
private XSSFWorkbook workbook;
private XSSFSheet sheet;
private int rowCount=0;
private CellStyle normalstyle=null;
private CellStyle headerstyle=null;
private CellStyle captionstyle=null;

private XSSFFont defaultFont;
private XSSFFont headerFont;
private XSSFFont captionFont;

public String getMimeType() {
     return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
}

public void setParameters(TableModel tableModel, boolean exportFullList, boolean includeHeader,
       boolean decorateValues)
{
   this.model = tableModel;
   this.exportFull = true;
   this.header = includeHeader;
   this.decorated = decorateValues;
}

public boolean outputPage() {
    return false;
}

protected void initTable() throws BadElementException
{
    //Blank workbook
    workbook = new XSSFWorkbook();
     
    //Create a blank sheet
    sheet = workbook.createSheet("Server Management System");
    
    
    defaultFont= workbook.createFont();
    defaultFont.setFontHeightInPoints((short)10);
    defaultFont.setFontName("Arial");
    defaultFont.setColor(IndexedColors.BLACK.getIndex());
    defaultFont.setBold(false);
    defaultFont.setItalic(false);

    headerFont= workbook.createFont();
    headerFont.setFontHeightInPoints((short)10);
    headerFont.setFontName("Arial");
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    headerFont.setBold(true);
    headerFont.setItalic(false);
    
    captionFont= workbook.createFont();
    captionFont.setFontHeightInPoints((short)15);
    captionFont.setFontName("Arial");
    captionFont.setColor(IndexedColors.BLACK.getIndex());
    captionFont.setBold(true);
    captionFont.setItalic(false);
    
    
    normalstyle= workbook.createCellStyle(); 
    normalstyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
    normalstyle.setFillPattern(CellStyle.BIG_SPOTS);
    normalstyle.setAlignment(CellStyle.ALIGN_CENTER);
    normalstyle.setFont(defaultFont);
    
    headerstyle= workbook.createCellStyle(); 
    headerstyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    headerstyle.setFillPattern(CellStyle.BIG_SPOTS);
    headerstyle.setAlignment(CellStyle.ALIGN_CENTER);
    headerstyle.setFont(headerFont);
    
    captionstyle= workbook.createCellStyle(); 
    captionstyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    captionstyle.setFillPattern(CellStyle.BIG_SPOTS);
    captionstyle.setAlignment(CellStyle.ALIGN_CENTER);
    captionstyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    captionstyle.setFont(captionFont);
    
}

protected void generateXLSXTable() throws JspException, BadElementException
{
    //adding caption
    if(this.model.getCaption()!=null && this.model.getCaption()!="")
    {
     Row blankrow = sheet.createRow(rowCount++);
     Row row = sheet.createRow(rowCount++);
     
     row.setHeight((short)5000);
     int columnCount=0;
     Cell captionCell = row.createCell(columnCount++);
     captionCell.setCellValue(this.model.getCaption());
     captionCell.setCellStyle(captionstyle);
     sheet.addMergedRegion(new CellRangeAddress(
             rowCount-1, //first row (0-based)
             rowCount-1, //last row  (0-based)
             columnCount-1, //first column (0-based)
             columnCount+5  //last column  (0-based)
     ));
     Row blankrow2 = sheet.createRow(rowCount++);
    }
    
    if (this.header)
    {
        generateHeaders();
    }
    
    generateRows();
    
    //adding footer
    if(this.model.getFooter()!=null && this.model.getFooter()!="")
    {
     Row blankrow = sheet.createRow(rowCount++);
     Row blankrow2 = sheet.createRow(rowCount++);
     Row row = sheet.createRow(rowCount++);
     
     row.setHeight((short)1000);
     int columnCount=0;
     Cell captionCell = row.createCell(columnCount++);
     captionCell.setCellValue(this.model.getFooter());
     captionCell.setCellStyle(captionstyle);
     sheet.addMergedRegion(new CellRangeAddress(
             rowCount-1, //first row (0-based)
             rowCount-1, //last row  (0-based)
             columnCount-1, //first column (0-based)
             columnCount+5  //last column  (0-based)
     ));
    }
}

protected void generateHeaders() throws BadElementException
{
    Iterator<HeaderCell> iterator = this.model.getHeaderCellList().iterator();
    Row row = sheet.createRow(rowCount++);
    
    int columnCount=0;
    while (iterator.hasNext())
    {
        sheet.autoSizeColumn(columnCount);
        HeaderCell headerCell = iterator.next();

        String columnHeader = headerCell.getTitle();

        if (columnHeader == null)
        {
            columnHeader = StringUtils.capitalize(headerCell.getBeanPropertyName());
        }

        Cell hdrCell = row.createCell(columnCount++);
        hdrCell.setCellValue(columnHeader);
        hdrCell.setCellStyle(headerstyle);
    }
}

protected void generateRows() throws JspException, BadElementException
{
    // get the correct iterator (full or partial list according to the exportFull field)
    RowIterator rowIterator = this.model.getRowIterator(this.exportFull);
    // iterator on rows
    while (rowIterator.hasNext())
    {
        Row newrow = sheet.createRow(rowCount++);
        org.displaytag.model.Row row = rowIterator.next();
        // iterator on columns
        ColumnIterator columnIterator = row.getColumnIterator(this.model.getHeaderCellList());
        int columnCount=0;
        while (columnIterator.hasNext())
        {
            Column column = columnIterator.nextColumn();

            // Get the value to be displayed for the column
            Object value = column.getValue(this.decorated);

            Cell Cell = newrow.createCell(columnCount++);
            Cell.setCellValue(ObjectUtils.toString(value));
        }
    }
    
}

@Override
public void doExport(OutputStream out) throws IOException, JspException {
    
    try {
        // Initialize the table with the appropriate number of columns
        initTable();
        
        generateXLSXTable();
        
    } catch (BadElementException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
           
    workbook.write(out);
    out.close();
}
}

Note: You can modify this class as per your need.

Step 4 : In resource folder create a file displaytag.properties

Step 5 : Copy and Paste following code in it

export.amount=list
sort.amount=list
export.decorated=true

export.types=csv excel xml pdf rtf [mymedia]

export.[mymedia]=true
export.[mymedia].class=com.displaytag.export.views.myExcel2007ExportView

export.excel=true
export.csv=true
export.xml=true
export.pdf=true


export.pdf.include_header=true
export.excel.include_header=true

export.rtf=true
export.rtf.label=<span class="export rtf">RTF </span>
export.rtf.include_header=true
export.rtf.filename=

export.[mymedia].label=<span class="export excel">Excel 2007 </span>
export.[mymedia].include_header=true
export.[mymedia].filename=

Notice: Make sure you do not change the order of properties

Step 6: in jsp file

 <display:table id="l" name="yourList" pagesize="10" requestURI="/yourAction.action" style="width:100%" export="true">
                            
    
<display:setProperty name="export.csv.filename" value="YourReport.csv" />
<display:setProperty name="export.excel.filename" value="YourReport.xls" />
<display:setProperty name="export.xml.filename" value="YourReport.xml" />
<display:setProperty name="export.rtf.filename" value="YourReport.rtf" />
<display:setProperty name="export.pdf.filename" value="YourReport.pdf" />
<display:setProperty name="export.[mymedia].filename" value="YourReport.xlsx" />
                    
                
<display:caption media="csv xml excel pdf rtf [mymedia]"> yourcaption</display:caption>
                            
<display:column property="id" titleKey="selectlogdetails.logid"  sortable="false" />
//other columns
                        
<display:footer media="csv xml excel pdf rtf [mymedia]"> yourfooter
</display:footer>
</display:table>

Notice : Do not forget to add [mymedia] in caption, footer and also do not forget to set file name as below.

 <display:setProperty name="export.[mymedia].filename" value="YourReport.xlsx" />

Step 7: Copy and paste following jar files in your WEB-INF/lib folder

displaytag-1.2.jar

displaytag-export-poi-1.2.jar

displaytag-portlet-1.2.jar

commons-lang-2.3 or above

commons-beanutils-1.7.0 or above

commons-collections-3.1 or above

commons-logging-1.1.jar

itext-1.3 or above

slf4j-api-1.4.2 or above

slf4j-log4j12-1.4.2 or above

poi-3.2-FINAL.jar

poi-3.9.jar

poi-ooxml-3.9.jar

poi-ooxml-schemas-3.9.jar

stax-api-1.0.1.jar

xmlbeans-2.6.0.jar

commons-codec-1.5.jar

dom4j-1.6.1.jar

log4j-1.2.15.jar

Notice : If I forgot any dependency jar then please check it with maven.

Step 8: Run the project and Enjoy :) Also let me know if any error is there. Also if you are using any decorator in your displaytag then you have to write code for it.

Thank you

To make a resource folder If you are using eclipse right click on java resources as below

resource folder

Give name res and make a properties file in it

eclipse

using netBeans IDE 7.4

netBeans

i created displaytag.properties file in default package and when i'm running my project it gives me an Exception `SEVERE: Servlet.service() for servlet [default] in context with path [/reportnguccGraph] threw exception java.lang.IllegalStateException: Cannot call sendError() after the response has been committed` — amit bhardwaj, May 08 '14 at 11:14
see my answer for creating a resorce folder. I gave name `res` to the source folder created and I have put `displaytag.properties` file in it. In `src` folder there are java files and in `res` we put resource files. — prem30488, May 08 '14 at 11:17
i'm using netbeans and my displaytag.properties file is in default package with other xml files just like yours but it gives me an exception when the response is coming to jsp — amit bhardwaj, May 08 '14 at 11:39
project only gives exception when i add displaytag.properties file to src folder. i still don't understand how to add it in resource folder u want me to make a simple folder in src folder or another source folder. the exception is 'SEVERE: Servlet.service() for servlet jsp threw exception java.lang.NullPointerException' — amit bhardwaj, May 09 '14 at 06:47
I'm using netbeans 7.3.1 and my project is on struts2 and hibernate so tell me how to make resource folder. — amit bhardwaj, May 09 '14 at 07:06
See my answer. I have uploaded my net beans project's structure. — prem30488, May 09 '14 at 09:30
in my netbeans classes folder is not shown in the structure so i open my workspace and copy the displaytag.properties file in it now there is no exception but the export link is also not showing i set the property of excel and [mymedia] but there is not even a single export option — amit bhardwaj, May 09 '14 at 10:07
You can create classes folder in that if it is not there.did you use `export=true` in displaytag ? — prem30488, May 09 '14 at 10:14
ofCourse that much i know about display tag. in stacktrace i found `at org.apache.jsp.contaque.reports.campaignRep.campRepDailyHourly_jsp._jspService(campRepDailyHourly_jsp.java:188)` when there is exception this is the servelet and at line 188 there is a object of org.displaytag.tag.Tabletag class `int _jspx_eval_display_005ftable_005f0 = _jspx_th_display_005ftable_005f0.doStartTag();` and this `_jspx_th_display_005ftable_005f0` is the obj i think this give the null pointer ......do u know the reason?? — amit bhardwaj, May 09 '14 at 10:52
are you using decorator? or are you using jsp:include or tiles? — prem30488, May 09 '14 at 10:57
no i'm not using decorator for all tables but i'm using tiles — amit bhardwaj, May 09 '14 at 11:00
hey parth is there any problem if i'm using tiles in my project. — amit bhardwaj, May 09 '14 at 11:39
yes dear. The jsp containing displaytag can not be used with `tiles:insertattribute` or `jsp:include`. :( — prem30488, May 09 '14 at 11:44
Copy paste main template to jsp and instead of inserting tile, replace the `jsp` code with it. In short you need to copy your master page and has to replace your code with `jsp:include`. Also see this web.xml for installing export filter. file See here -> https://github.com/prem30488/RemoteServerManagementSystem/blob/master/WebContent/WEB-INF/web.xml. But in most cases even though we install export filter `displaytag` does not work with `jsp:include` or `tiles:inserattribute` — prem30488, May 09 '14 at 11:47
ResponseOverridefilter is in my web.xml from starting and find a way to use the export with tiles. — amit bhardwaj, May 09 '14 at 12:10
hey parth i made a new project without tiles but still it gives the same exception and i also add the export filter... — amit bhardwaj, May 12 '14 at 06:57
yes. it is also working in my all projects. Have a look here http://www.displaytag.org/10/export_filter.html. — prem30488, May 12 '14 at 08:05
my table caption is ` Campaign Summary Range Hourly
From To ` now in excel file html tags is also shown how can i edit so that only text will appear on excel file — amit bhardwaj, May 12 '14 at 08:37
Media decorator class is a simple decorator class or different and which method i have to override in this class to avoid html tags from download — amit bhardwaj, May 12 '14 at 11:17

How to export excel file with xlsx extension in displaytag

1 Answers1

Linked