java - 如何在displaytag中导出带有xlsx扩展名的excel文件

Question

我们使用 displaytag 以 xls 格式导出文件，但我希望以 xlsx 格式导出文件。有什么方法可以将Excel文件转换为新格式吗？

我将显示标记中的扩展名从 xls 更改为 xlsx

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

但当我在 Office 2007 或 2010 中打开它时，它给我“文件扩展名无效”。

Answer 1

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

第 1 步:制作包com.displaytag.export.views

第2步:在上面的包中创建一个类myExcel2007ExportView.java。

第 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();
}
}

注意:您可以根据需要修改此类。

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

第 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=

注意:确保不要更改属性的顺序

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

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

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

第 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

注意:如果我忘记了任何依赖项 jar，请使用 maven 检查它。

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

谢谢

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

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

使用 netBeans IDE 7.4