项目中需要将web页面中的报表导出成Excel，在网上搜寻了写资料，实现了相关功能，如图1所示：
项目后台架构采用的是SpringMVC+Spring+Mybatis，通过引入Apache POI实现excel的下载功能。
导出效果如图2所示：
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。
