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。