struts使用POI导出数据到excel模板
网上有很多导出数据到excel文件的案例，本案例是把数据导出到指定的excel模板上，模板获取数据也是动态的，参考来自：
https://blog.csdn.net/richerg85/article/details/42459471
这里需要说明添加的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；下面直接上代码
1. 前端页面exportExcel.jsp
<button type="button" οnclick="exportExcel()">导出Excel</button>
通过button时间导出excel，下面是页面事件部分
function isEmpty(str) {
if(typeof str== null || str== "" || str== "undefined") {
return true;
} else {
return false;
}
}
function exportExcel() {
// 1.先通过条件做筛选统计，如果已做条件筛选才可以导出excel
var reportState_options = $("#exprotState option:selected");
//使用jQuery去掉字符创前后空格$.trim()
var reportState = $.trim(reportState_options.val()); // 报告状态
var modality_options = $("#modality option:selected");
var modality = $.trim(modality_options.val()); // 设备类型
var hospitalName = document.getElementById("applyHospitalName").value; // 远程请求医院
var patientName = document.getElementById("patientName").value; // 病人姓名
//获远程请求时间选中项的内容
var days_options=$("#days2 option:selected");
var days=$.trim(days_options.val()); // 远程请求时间
var startDate = document.getElementById("startDate2").value; // 开始时间
var endDate = document.getElementById("endDate2").value; // 结束时间
if (isEmpty(days) && (isEmpty(startDate) && isEmpty(endDate))) {
alert("请先至少按时间筛选并查询出结果");
return false;
}
// 2.根据前端的筛选条件调用后端导出方法
window.location.href="eport/exportCostExcel?filter.exReportState="+reportState+"&filter.modality="+modality+"&filter.hospitalName="+hospitalName+"&filter.patientName="+patientName+"&filter.days="+days+"&filter.startDate="+startDate+"&filter.endDate="+endDate;
}
2. ReportService接口
import java.io.InputStream;
import java.util.Collection;
import java.util.Map;
public interface ReportService {
@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);
}
3.ExcelReport类
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;
public class ExcelReport implements ReportService {
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 = (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;
}
}
4.ReportManageAction.java 类（这里只截取了具体实现的方法）
private InputStream excelStream;
private String fileName="";
private String filePath=File.separator+File.separator;
/**
* TODO (新增待上线)导出统计数据到excel
*/
public String exportCostExcel() throws IOException{
HttpServletRequest request = ServletActionContext.getRequest();
if(StringUtils.isNotEmpty(filter.getStartDate()) && StringUtils.isNotEmpty(filter.getEndDate())) {
filter.setDays(null);
}
reportList.setDataSource(expertService.getMedicalList(this.getLoginUser().getUserName(), filter));
filter.setHospitalNameList(expertService.getLinkHosptialByExpert(this.getLoginUser().getUserName()));
filter.setExpertIDList(expertService.getLinkExpertByHosptial(this.getLoginUser().getUserName()));
List<ReportView> viewList = reportList.getAllData(); // 获取需要统计的数据(ReportView需要查询的数据对象)
if ( viewList == null )
return ERROR;
Map beans = new HashMap();
beans.put("reportList", viewList);
beans.put("account", viewList.size());
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
// 填充报表
filePath += "excelTemplate.xls";
fileName = "费用统计表.xls";
beans.put("startDate", filter.getStartDate());
beans.put("endDate", filter.getEndDate());
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;
}
5.struts.xml 配置请求路径
<action name="exportCostExcel" class="cn.fuego.remote.medical.manage.web.action.report.ReportManageAction" method="exportCostExcel">
<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">/client/manage/costCount.jsp</result>
</action>
6. excel文件下载模板路径放在WebRoot/WEB-INF/excelTemplate.xls 下
7. excelTemplate.xls文件