spring-excel视图

Springmvc集成了poi插件，在做报表导出的时候可以直接使用excel视图
实现方式，只需要自己写的视图view继承spring提供的AbstractExcelView，实现抽象类中的buildExcelDocument抽象方法即可。抽象类中已经实现了对excel视图的渲染，直接用reponse获取输出流，下载写入excel文件。抽象的buildExcelDocument方法是让使用者按自己的需求封装Excel需要的数据。

public class DefaultExcelView extends AbstractExcelView {
    @Override
    protected void buildExcelDocument(Map<String, Object> map, HSSFWorkbook workbook, HttpServletRequest request,
            HttpServletResponse response) throws Exception {

        try {
            String filename = (String) map.get("filename");
            if (filename == null || filename.trim().length() == 0) {
                filename = System.currentTimeMillis() + ".xls";
            }
            String agent = request.getHeader("USER-AGENT").toLowerCase();
            //添加块-根据浏览器类型处理文件名称
            if(agent.indexOf("msie")>-1){
                response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));// 设定输出文件头
            }
            else{  //firefox/safari不转码
                response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("GB2312"), "ISO-8859-1"));
            }
            this.buildExcelDocument(map, workbook);
        } catch (Exception e) {
            log.error("buildExcelDocument-error:", e);
            throw e;
        } 
    }
    
    /**
     * 构建Excel文本
     * @param map
     * @param workbook
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    protected void buildExcelDocument(Map<String, Object> map, HSSFWorkbook workbook) throws Exception {
        String sheetname = (String) map.get("sheetname");
        
        if (sheetname == null || sheetname.trim().length() == 0) {
            sheetname = "Sheet1";
        }
        
        List<List<Object>> contents = (List<List<Object>>) map.get("contents");
        if (contents == null) {
            throw new RuntimeException("DefaultExcelView Attribute[contents] in Model can't be null! ");
        }
        ExcelWriter exporter = new ExcelWriter();
        exporter.setWorkbook(workbook);
        exporter.writeSheet(sheetname, contents);
    }

}

ExcelWriter类中实现了对Excel页签的写入方法，旧版的poi插件是直接将数据写入缓存一次性写入Excel，如果导出的数据量很大的话容易造成内存溢出，推荐使用较新版本的poi插件

 <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.1</version>
</dependency>

新版的poi插件支持从内存加载数据的同时将数据写入磁盘，写入磁盘的数据是不再占用内存的，这样可以避免内存溢出。

public class DownLoadExcelView extends AbstractView {
    private static final Logger logger = Logger.getLogger(DownLoadExcelView.class);
    private final int ROW_ACCESS_WINDOW_SIZE = 10000;
    @Override
    protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
        SXSSFWorkbook wb = new SXSSFWorkbook(ROW_ACCESS_WINDOW_SIZE); // keep rows in memory, exceeding rows will be flushed to disk
        Sheet sh = wb.createSheet();
        List<List<Object>> contents = (List<List<Object>>) model.get("contents");
        int contentsSize = contents.size();
        for (int i = 0; i < contentsSize; i++) {
            List<Object> content = contents.get(i);
            Row c_row = sh.createRow(i);
            this.writeRow(c_row, content);
       }
        String filePath = (String) model.get("filePath");
        String fileName = (String)model.get("fileName");
        FileOutputStream out = new FileOutputStream(filePath);
        wb.write(out);
       out.close();
        wb.dispose();

        downLoadExcelFile(filePath,fileName,response);
    }

    private void downLoadExcelFile(String filePath,String fileName,HttpServletResponse response) throws IOException {
        File file = new File(filePath.replace("/", File.separator));
        if (!file.canRead()) {
            response.sendError(404);
            return;
        }
        response.reset();
        response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("GB2312"), "ISO-8859-1"));
        response.setContentType("application/octet-stream;charset=UTF-8");
        BufferedInputStream bis = null;
        try {
            bis = new BufferedInputStream(new FileInputStream(file));
            int count = -1;
            byte[] buffer = new byte[bis.available()];
            OutputStream os = response.getOutputStream();
            while ((count = bis.read(buffer)) > -1) {
                os.write(buffer, 0, count);
            }
            os.close();
        } catch (Exception e) {
            logger.error("下载失败", e);
        } finally {
            if (null != bis) {
                bis.close();
            }

        }
    }

    /**
     * 写入工作簿一行数据
     *
     * @param row
     * @param content
     * @throws Exception
     */
    protected void writeRow(Row row, List<Object> content) throws Exception {
        int contentSize = content.size();
        for (int i = 0; i < contentSize; i++) {
            Cell cell = row.createCell(i);
            Object val = content.get(i) == null ? "" : content.get(i);
            this.writeCell(cell, val);
        }
    }
    /**
     * 写入工作簿单元格数据
     * @param cell
     * @param val
     * @throws Exception
     */
    protected void writeCell(Cell cell, Object val) throws Exception {
        cell.setCellValue(String.valueOf(val));
    }
}

以上代码中，只保留了1w行的数据在内存中，超出1w行的数据被刷到的磁盘中。

spring-excel视图

推荐阅读更多精彩内容