如何导出excel文件与xlsx扩展在displaytag [英] How to export excel file with xlsx extension in displaytag

问题描述

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

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

 < display：setProperty name =export.excelvalue =true/> 
< display：setProperty name =export.excel.filenamevalue =assignedUserGroup.xlsx/> 
  

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

解决方案

这是非常有用的问题。
$ b

步骤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实现BinaryExportView {
 
 // private static Log log = LogFactory.getLog（myExcel2007ExportView.class）; 
私人TableModel模型; 
 private boolean exportFull; 
 private boolean header; 
私人布尔装饰; 
私人XSSFWorkbook工作簿; 
私人XSSFSheet表; 
 private int rowCount = 0; 
 private CellStyle normalstyle = null; 
 private CellStyle headerstyle = null; 
 private CellStyle captionstyle = null; 
 
私人XSSFFont defaultFont; 
 private XSSFFont headerFont; 
私人XSSFFont captionFont; 
 
 public String getMimeType（）{
 returnapplication / 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 
 {
 //空白工作簿
 workbook = new XSSFWorkbook（）; 
 
 //创建一个空白页
 sheet = workbook.createSheet（服务器管理系统）; 
 
 
 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 
 {
 //添加标题
 if（this.model.getCaption （）！= null&& this.model.getCaption（）！=）
 {
行blankrow = sheet.createRow（rowCount ++）; 
 Row row = sheet.createRow（rowCount ++）; 
 
 row.setHeight（（short）5000）; 
 int columnCount = 0; 
单元格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，//第一列（基于0）
 columnCount + 5 //最后一列（基于0）
））; 
行blankrow2 = sheet.createRow（rowCount ++）; 
} 
 
 if（this.header）
 {
 generateHeaders（）; 
} 
 
 generateRows（）; 
 
 //添加页脚
 if（this.model.getFooter（）！= null&& this.model.getFooter（）！=）
 {
行blankrow = sheet.createRow（rowCount ++）; 
行blankrow2 = sheet.createRow（rowCount ++）; 
 Row row = sheet.createRow（rowCount ++）; 
 
 row.setHeight（（short）1000）; 
 int columnCount = 0; 
单元格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，//第一列（基于0）
 columnCount + 5 //最后一列（基于0）
））; 
} 
} 
 
 protected void generateHeaders（）throws BadElementException 
 {
迭代器< 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（））; 
} 
 
单元格hdrCell = row.createCell（columnCount ++）; 
 hdrCell.setCellValue（columnHeader）; 
 hdrCell.setCellStyle（headerstyle）; 
} 
} 
 
 protected void generateRows（）throws JspException，BadElementException 
 {
 //获取正确的迭代器（根据exportFull字段）
 RowIterator rowIterator = this.model.getRowIterator（this.exportFull）; 
 //行
上的迭代器（）rowBar（） 
 org.displaytag.model.Row row = rowIterator.next（）; 
 //列的迭代器
 ColumnIterator columnIterator = row.getColumnIterator（this.model.getHeaderCellList（））; 
 int columnCount = 0; 
 while（columnIterator.hasNext（））
 {
列列= columnIterator.nextColumn（）; 
 
 //获取列显示的值
 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 {
 //使用适当数量的列初始化表
 initTable（）; 
 
 generateXLSXTable（）; 
 
} catch（BadElementException e）{
 // TODO自动生成的catch块
 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 < /跨度> 
 export。[mymedia] .include_header = true 
 export。[mymedia] .filename = 
  

注意：确保您不更改属性顺序

步骤6： code> jsp file

 < display：table id =lname = yourListpagesize =10requestURI =/ yourAction.actionstyle =width：100％export =true> 
 
 
< display：setProperty name =export.csv.filenamevalue =YourReport.csv/> 
< display：setProperty name =export.excel.filenamevalue =YourReport.xls/> 
< display：setProperty name =export.xml.filenamevalue =YourReport.xml/> 
< display：setProperty name =export.rtf.filenamevalue =YourReport.rtf/> 
< display：setProperty name =export.pdf.filenamevalue =YourReport.pdf/> 
< display：setProperty name =export。[mymedia] .filenamevalue =YourReport.xlsx/> 
 
 
< display：caption media =csv xml excel pdf rtf [mymedia]> yourcaption< /显示：字幕> 
 
< display：column property =idtitleKey =selectlogdetails.logidsortable =false/> 
 //其他列
 
< display：footer media =csv xml excel pdf rtf [mymedia]> yourfooter 
< / display：footer> 
< / display：table> 
  

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

 < display：setProperty name =export。[mymedia]。 filenamevalue =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或以上

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

LOG4 j-1.2.15.jar

注意：如果我忘记了任何依赖项，那么请检查它与maven。 strong>

步骤8：运行项目并享受:)另外让我知道是否有任何错误。另外，如果您在 displaytag 中使用任何装饰器，那么您必须为其编写代码。

谢谢

创建资源文件夹如果您使用eclipse右键单击以下资源，请执行以下

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

使用netBeans IDE 7.4

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.

解决方案

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

Give name res and make a properties file in it

using netBeans IDE 7.4

这篇关于如何导出excel文件与xlsx扩展在displaytag的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！