SpringMVC + Apache POI 实现WEB中Excel下载功能

SpringMVC + Apache POI 实现WEB中Excel下载功能

项目中需要将web页面中的报表导出成Excel,在网上搜寻了写资料,实现了相关功能,如图1所示:

项目后台架构采用的是SpringMVC+Spring+Mybatis,通过引入Apache POI实现excel的下载功能。

导出效果如图2所示:

首先页面中点击“导出Excel”按钮就会触发如下js代码:

function getXls()
{
var selectVal = dijit.byId('DRSSelectFacId').get('value');
var beginTimeVal = dijit.byId('DRSBeginTime').get('displayedValue');
var endTimeVal = dijit.byId('DRSEndTime').get('displayedValue');

var url = "report/getDRSExcel.do?"+"fac_id="+selectVal+"&beginTime="+beginTimeVal+"&endTime="+endTimeVal;
window.open(url,"_self");
}

这段js代码的主要功能是将选择条件返回给后台,请求相应的数据并生成excel。

jsp相关代码如下:

<div style="margin-top:10px;">
<label for="DRSSelectFacId">选择电场:</label><span id="DRSSelectFacId" ></span>    
<label for="DRSBeginTime">起始日期:</label><span id="DRSBeginTime" ></span>    
<label for="DRSEndTime">截止日期:</label><span id="DRSEndTime" ></span>    
<span id="DRSbutton1" ></span>    
<span id="DRSbutton2" ></span>
</div>

下面是JAVA后台控制层代码:

@RequestMapping(value = "/report/getDRSExcel.do")
public void getDRSExcel(
@RequestParam(value = "fac_id", required = true) String fac_id,
@RequestParam(value = "beginTime", required = true) String beginTime,
@RequestParam(value = "endTime", required = true) String endTime,
HttpServletRequest request, HttpServletResponse response)
{
logger.info("/report/getDRSExcel.do?fac_id=" + fac_id + "&beginTime="
+ beginTime + "&endTime=" + endTime);
try {
this.daliyRepShortService.getXls(fac_id,beginTime,endTime,request,response);
} catch (ParseException e) {
e.printStackTrace();
}
}

这里调用了业务层代码如下:

private List<Map<String, Object>> createExcelRecord(List<Fc_dailyreport> projects) {
List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
Map<String, Object> map = new HashMap<String, Object>();
map.put("sheetName", "短期预测日报");
listmap.add(map);
Fc_dailyreport project=null;

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
DecimalFormat fnum = new DecimalFormat("##0.0000");
for (int j = 0; j < projects.size(); j++) {
project=projects.get(j);
Map<String, Object> mapValue = new HashMap<String, Object>();
mapValue.put("dtime", sdf.format(project.getDtime()));
mapValue.put("cap", project.getCap());
mapValue.put("p", project.getP());
mapValue.put("fore_p", project.getFore_p());
mapValue.put("rmse", fnum.format(project.getRmse()*100)+"%");
mapValue.put("mae", fnum.format(project.getMae()*100)+"%");
mapValue.put("qualified_rate", project.getQualified_rate()+"%");
mapValue.put("colrel", project.getColrel());
mapValue.put("uploadrate", project.getUploadrate()+"%");
mapValue.put("qxuploadrate", project.getQxuploadrate()+"%");
listmap.add(mapValue);
}
return listmap;
}

public void getXls(String fac_id,String beginTime,String endTime,HttpServletRequest request, HttpServletResponse response)
throws ParseException
{
String fileName="短期预测日报";
//1.
List<Fc_dailyreport> projects = getXlsData(fac_id,beginTime,endTime);
//2.
List<Map<String,Object>> list=createExcelRecord(projects);
//3.
String columnNames[]={"时间","容量(MW)","实际功率(MW)","预测功率(MW)","均方误差(%)","平均绝对误差(%)","合格率(%)","相关系数","上传率(%)","气象上传率(%)"};//列名
String keys[] = {"dtime","cap","p","fore_p","rmse","mae","qualified_rate","colrel","uploadrate","qxuploadrate"};//map中的key
//4.
ExcelUtil.ExcelSingleOutputStream(list,keys,columnNames,fileName,request,response);
}

在getXls方法中,getXlsData(fac_id,beginTime,endTime);主要是根据前端的查询条件参数获取所要的数据,这里采用的是mybatis实现,由于本文的主旨是与excel相关的,这里就不说明mybatis如何实现数据的获取。

第二步是创建excel的数据,如方法createExcelRecord(projects);所示。list中第一项的sheetName用来命名Excel中的sheet。剩余list中的数据数excel中的没一行的数据。在getXls方法中的columnNames对应excel的第一行的列名,可参考图2. keys与createExcelRecord中的相关名字一一对应(这里也与DAO值的pojo类的属性名字一一对应)。

接下来就需要了解ExcelUtil.ExcelSingleOutputStream(list,keys,columnNames,fileName,request,response);这一段代码是做什么的了。

首选看类ExcelUtil:

package com.shr.util;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelUtil {

public static Workbook createSingleWorkBook(List<Map<String, Object>> list,String []keys,String columnNames[]) {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
for(int i=0;i<keys.length;i++)
{
sheet.setColumnWidth((short) i, (short) (35.7 * 150));
}

Row row = sheet.createRow((short) 0);

CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();

Font f = wb.createFont();
Font f2 = wb.createFont();

f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBoldweight(Font.BOLDWEIGHT_BOLD);

f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());

cs.setFont(f);
cs.setFillForegroundColor(IndexedColors.AQUA.getIndex());
cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setAlignment(CellStyle.ALIGN_CENTER);

cs2.setFont(f2);
cs2.setBorderLeft(CellStyle.BORDER_THIN);
cs2.setBorderRight(CellStyle.BORDER_THIN);
cs2.setBorderTop(CellStyle.BORDER_THIN);
cs2.setBorderBottom(CellStyle.BORDER_THIN);
cs2.setAlignment(CellStyle.ALIGN_CENTER);
for(int i=0;i<columnNames.length;i++){
Cell cell = row.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
for (short i = 1; i < list.size(); i++) {
Row row1 = sheet.createRow((short) i);
for(short j=0;j<keys.length;j++){
Cell cell = row1.createCell(j);
cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
cell.setCellStyle(cs2);
}
}
return wb;
}

public static void ExcelSingleOutputStream(List<Map<String, Object>> list,String []keys,String columnNames[],
String fileName, HttpServletRequest request, HttpServletResponse response)
{
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
createSingleWorkBook(list,keys,columnNames).write(os);
} catch (IOException e2) {
e2.printStackTrace();
}
ExcelOutputStream(fileName,request,response,os);
}

private static void ExcelOutputStream( String fileName, HttpServletRequest request, HttpServletResponse response,ByteArrayOutputStream os)
{
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);

response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
try {
response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "iso-8859-1"));
} catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
}
ServletOutputStream out = null;
try {
out = response.getOutputStream();
} catch (IOException e1) {
e1.printStackTrace();
}
BufferedInputStream bis = null;
BufferedOutputStream bos = null;

try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch ( IOException e) {
e.printStackTrace();
} finally {
if (bis != null)
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
if (bos != null)
try {
bos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}

这里的createSingleWorkBook方法用来根据业务层中的相关数据生成的excel,这时候生成的excel是驻留在内存中的,所以需要其输出,请参照方法ExcelSingleOutputStream和ExcelOutputStream(这里将一个方法拆分成两个是因为原项目中还有其他的情况考虑,本文只罗列出一种相对简单的情况,所以这样不要差异,可以将这两个方法看成一个也无妨,主要是向页面输出这个生成的Excel。


欢迎支持笔者的作品《深入理解Kafka: 核心设计与实践原理》和《RabbitMQ实战指南》,同时欢迎关注笔者的微信公众号:朱小厮的博客(ID: hiddenkafka)。
本文作者: 朱小厮

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×