JXLS+SpringMVC导出Excel
- maven导入jar包
<!-- springMVC jxls Excel 导出 start-->
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-core</artifactId>
<version>1.0.6</version>
</dependency>
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>1.0.6</version>
</dependency>
<!-- springMVC jxls Excel 导出 end-->
- 新建JxlsExcelUtil.java
package com.htdz.ydkx.export;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.servlet.view.AbstractView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Map;
public class JxlsExcelUtil extends AbstractView {
private String exportFileName;
private String templatePath;
private static final String CONTENT_TYPE="application/vnd.ms-excel";
/**
* @param templatePath 模版相对于当前classpath路径
* @param exportFileName 导出文件名
*/
public JxlsExcelUtil(String templatePath, String exportFileName) {
this.templatePath = templatePath;
this.exportFileName = exportFileName;
setContentType(CONTENT_TYPE);
}
@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
InputStream is = null;
OutputStream os = null;
try{
os = response.getOutputStream();
response.setContentType(getContentType());
//解决导出文件名中文乱码问题
String filename = new String(exportFileName.getBytes("gb2312"),"iso8859-1");
response.setHeader("content-disposition", "attachment;filename=" + filename + ".xls");
//获取excel模板
is = JxlsExcelUtil.class.getClassLoader().getResourceAsStream(templatePath);
//转换成excel并输出
XLSTransformer transformer = new XLSTransformer();
Workbook workbook = transformer.transformXLS(is,model);
//将内容写入输出流并把缓存的内容全部输出
workbook.write(os);
os.flush();
os.close();
is.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
- 手工创建需要导出的excel模板
- Controller中的代码
@RequestMapping(params = "exportCustInfoXls")
public ModelAndView exportCustInfoXls(HttpServletRequest request,HttpServletResponse response,DataGrid dataGrid){
//参数
String phone = request.getParameter("phone");
//查询数据
List<BrandProduct> brandProductList = brandProductInfo.getBrandProduct((dataGrid.getPage()-1)*dataGrid.getRows(),
dataGrid.getRows(),phone);
ModelMap modelMap = new ModelMap();
modelMap.put("brandInfos", brandProductList);
return new ModelAndView(new JxlsExcelUtil("jxls/template/brandInfo.xls","客户对应的品牌信息"), modelMap);
}