现在的项目需要把用户反馈的信息表中的数据导出到excel的需求。之前做过类似的事情,但是时间已经久远,只能网上搜了一下,对于struts2,基本使用poi的方法,但是感觉网上的方法比较乱及不靠谱(没有开vpn 去google),就把之前的老项目用到的方法拿出来,整理如下,希望对大家有用。确实很简单(对于使用,里面的原理没有深入了解,可能我这边使用的是基本功能,如果需要细致研究,去apache下载jar包的时候,顺便把源码下载下来,这样就可以一目了然了。)
不多说,先从架构(使用步骤,从前端到后端,方式和action走的路径一致,只是多了对excel流的处理):
- 前端jsp页面,加个点击的按钮或者超链接;
<a href="feedBackReportAction_exportExcel.action">导出</a>
- struts.xml中加个action:
<action name="feedBackReportAction_exportExcel" class="feedBackReportAction" method="exportExcel">
<result name="excel" type="stream">
<param name="contentType">
application/vnd.ms-excel
</param>
<param name="inputName">excelStream</param>
<param name="contentDisposition">
attachment;filename="${fileName}"
</param>
<param name="bufferSize">1024</param>
</result>
<result name="input" type="redirect">feedBackWebAction_queryAll.action</result>
</action>
从action可以看出,result中 name为excel的类型为stream,并且有几个参数,对于自己的项目,其他的不需要动,只是需要注意"${fileName}",这个是后面action中定义的一个String名称,作为下载时显示的xls的文件名。当然名称为input的result跳转的地址需要自己定义的。
- action只是很简单的类:
其中的exportExcel方法如下:
很简单吧,我需要显示的bean就是数据库中的表,不需要作其他的操作,如果有需要,自己填充到Map中,上文提到的fileName也在这赋值了。其中public String exportExcel() throws IOException{ HttpServletRequest request = ServletActionContext.getRequest(); List<FeedBack> listFeedBack = feedBackService.getAll(); if ( listFeedBack == null ) return ERROR; Map beans = new HashMap(); beans.put("feedback", listFeedBack); SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); // 填充报表 filePath += "feedBackTemplet.xls"; fileName = "用户反馈信息报表.xls"; beans.put("createDate", df.format(new Date())); excelStream = new ExcelReport().makeReportFromTemplet(request .getRealPath("/WEB-INF") + filePath, beans); if (excelStream == null) { return INPUT; } return "excel"; }
这句是核心,把beans的内容,写到excel模板中。用的类----ExcelReport。就是所谓的poi。这个类的代码,会贴到此文章的最后。excelStream = new ExcelReport().makeReportFromTemplet(request .getRealPath("/WEB-INF") + filePath, beans);
- poi帮助类 (直接贴代码)
Report.java接口
import java.io.InputStream;
import java.util.Collection;
import java.util.Map;
public interface Report {
@SuppressWarnings("rawtypes")
public void makeReport(Collection collection, String filePath);
public void makeReport(String[] dataStr, String filePath);
@SuppressWarnings("rawtypes")
public void makeReport(Collection collection, String[] collumHead, String filePath);
/**
* 按模板生成报表,使用jxls设置报表模板,用于通过浏览器下载报表
* @param templetFileName 模板文件绝对路径+模板文件名
* @param beans 模板参数对象集合
* @return InputStream
*/
@SuppressWarnings("rawtypes")
public InputStream makeReportFromTemplet(String templetFileName, Map beans);
/**
* 按模板生成报表,使用jxls设置报表模板,直接生成本地文件
* @param templetFileName
* @param beans
* @param targetFileName
*/
@SuppressWarnings("rawtypes")
public void makeReportFromTemplet(String templetFileName, Map beans, String targetFileName);
}
继承类,即action调用的类
ExcelReport.java
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Collection;
import java.util.Map;
import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.Configuration;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
/**
* Excel报表类
*
*/
public class ExcelReport implements Report {
public static final String POSTIFIX = ".xls";
protected String reportName;
protected Workbook workBook = new HSSFWorkbook();
protected Sheet sheet;
protected String sheetName;
protected InputStream excelStream;
public ExcelReport(String reportName, String sheetName) {
super();
this.reportName = reportName;
this.sheetName = sheetName;
sheet = workBook.createSheet(sheetName);
}
public ExcelReport() {
super();
}
@SuppressWarnings({ "rawtypes" })
@Override
public void makeReport(Collection collection, String filePath) {
}
@Override
public void makeReport(String[] dataStr, String filePath) {
}
@SuppressWarnings("rawtypes")
@Override
public void makeReport(Collection collection, String[] collumHead,
String filePath) {
}
@SuppressWarnings("rawtypes")
@Override
public InputStream makeReportFromTemplet(String templetFileName, Map beans) {
Configuration config = new Configuration();
XLSTransformer transformer = new XLSTransformer(config);
InputStream is = null;
try {
is = new FileInputStream(templetFileName);
try {
workBook = transformer.transformXLS(is, beans);
} catch (ParsePropertyException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
// 产生POI输出流
ByteArrayOutputStream os = new ByteArrayOutputStream();
workBook.write(os);
excelStream = new ByteArrayInputStream(os.toByteArray());
is.close();
} catch (IOException ie) {
ie.printStackTrace();
}
return excelStream;
}
@SuppressWarnings("rawtypes")
@Override
public void makeReportFromTemplet(String templetFileName, Map beans,
String targetFileName) {
Configuration config = new Configuration();
XLSTransformer transformer = new XLSTransformer(config);
try{
try {
transformer.transformXLS(templetFileName, beans, targetFileName);
} catch (ParsePropertyException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
}catch(IOException ie){
ie.printStackTrace();
}
}
public String getReportName() {
return reportName;
}
public void setReportName(String reportName) {
this.reportName = reportName;
}
public Workbook getWorkBook() {
return workBook;
}
public void setWorkBook(HSSFWorkbook workBook) {
this.workBook = workBook;
}
public Sheet getSheet() {
return sheet;
}
public void setSheet(HSSFSheet sheet) {
this.sheet = sheet;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public InputStream getExcelStream() {
return excelStream;
}
public void setExcelStream(InputStream excelStream) {
this.excelStream = excelStream;
}
}
注意,这里需要说明添加的jar包(这是我加的jar包):poi-3.10-FINAL-20140208.jar,jxls-core-1.0.5.jar,poi-ooxml-3.10-FINAL-20140208.jar,commons-digester-2.1.jar,commons-jexl-2.1.1.jar;稍后打个包,可以去免费去下载,不用单独去下载,有的包必须翻墙才能下载。
打包地址:点击这额
- 最后,就差excel模板了
在action中的exportExcel中,模板存放路径:工程/WEB-INF/excelTemplet/feedBackTemplet.xls,内容为:由于ubuntu系统,只能屏幕截图了:
这个平铺的就是excel,这里面的布局,都是自己调整好的,和平常操作excel一样,想如何显示,就如何设计。
其中以$开头的,就是上文beans传过来的值,还有下面jx:forEach循环,如果传过来的是对象的list,就可以在此循环打印出来。
ok,想要说的就这些!
一切准备好,就可以下载了。
资料:
1、
commons-jexl
2、http://jxls.sourceforge.net/reference/simplebeans.html
附录:
1、 action 代码:
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.apache.struts2.ServletActionContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
import com.opensymphony.xwork2.ActionSupport;
import com.wshouyou.bean.FeedBack;
import com.wshouyou.service.FeedBackService;
import com.wshouyou.util.ExcelReport;
@Component("feedBackReportAction")
@Scope("prototype")
public class FeedBackReportAction extends ActionSupport {
private static final long serialVersionUID = 1L;
@Autowired
private FeedBackService feedBackService;
private InputStream excelStream;
private String fileName="";
private String filePath=File.separator+"excelTemplet"+File.separator;
@SuppressWarnings({ "rawtypes", "unchecked", "deprecation" })
public String exportExcel() throws IOException{
HttpServletRequest request = ServletActionContext.getRequest();
List<FeedBack> listFeedBack = feedBackService.getAll();
if ( listFeedBack == null )
return ERROR;
Map beans = new HashMap();
beans.put("feedback", listFeedBack);
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
// 填充报表
filePath += "feedBackTemplet.xls";
fileName = "用户反馈信息报表.xls";
beans.put("createDate", df.format(new Date()));
excelStream = new ExcelReport().makeReportFromTemplet(request
.getRealPath("/WEB-INF")
+ filePath, beans);
if (excelStream == null) {
return INPUT;
}
return "excel";
}
public String getFileName() {
try {
fileName = new String(fileName.getBytes(), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public String getFilePath() {
return filePath;
}
public void setFilePath(String filePath) {
this.filePath = filePath;
}
public InputStream getExcelStream() {
return excelStream;
}
public void setExcelStream(InputStream excelStream) {
this.excelStream = excelStream;
}
}