Java 如何在displaytag中导出扩展名为xlsx的excel文件

How to export excel file with xlsx extension in displaytag

提问by amit bhardwaj

We used displaytagfor exporting the files in xlsformat but I want it in xlsxformat. Is there any way to convert the excel file to new format?

我们使用displaytag以xls格式导出文件，但我想要xlsx格式。有没有办法将excel文件转换为新格式？

I change the extension in display tag from xls to xlsx

我将显示标签中的扩展名从xls 更改为 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.

但是"File extension is not valid"当我在 2007 年或 2010 年在办公室打开它时它给了我。

采纳答案by Curiosity

This is very useful question.

这是一个非常有用的问题。

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

第 1 步：制作包裹com.displaytag.export.views

Step 2 :Make a class myExcel2007ExportView.javain the above package.

第 2 步：myExcel2007ExportView.java在上述包中创建一个类。

Step 3 :Copy and paste following code in that file

第 3 步：将以下代码复制并粘贴到该文件中

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

第 4 步：在资源文件夹中创建一个文件displaytag.properties

Step 5 :Copy and Paste following code in it

第5步：复制并粘贴以下代码

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 jspfile

第 6 步：在jsp文件中

 <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.

注意：不要忘记在标题、页脚中添加[mymedia]，也不要忘记设置文件名如下。

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

Step 7:Copy and paste following jarfiles in your WEB-INF/libfolder

第 7 步：将以下jar文件复制并粘贴到您的WEB-INF/lib文件夹中

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

displaytag-1.2.jar

displaytag-export-poi-1.2.jar

displaytag-portlet-1.2.jar

commons-lang-2.3 或以上

commons-beanutils-1.7.0 或更高版本

commons-collections-3.1 或以上

commons-logging-1.1.jar

itext-1.3 或以上

slf4j-api-1.4.2 或以上

slf4j-log4j12-1.4.2 或以上

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.

注意：如果我忘记了任何依赖 jar，请使用 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 displaytagthen you have to write code for it.

第 8 步：运行项目并享受 :) 如果有任何错误，请告诉我。此外，如果您在使用任何装饰器，displaytag则必须为其编写代码。

Thank you

谢谢

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

制作资源文件夹如果您使用的是eclipse，请右键单击java资源，如下所示

Give name res and make a properties file in it

命名 res 并在其中创建一个属性文件

using netBeans IDE 7.4

使用 netBeans IDE 7.4