【原创】POI 生成Excel文件并下载

ι 版权声明：本文为博主原创文章，未经博主允许不得转载。

效果图：

 

实现

1.在pom中添加依赖：

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>

2.ExcelUtil工具类：

package com.feicuiedu.survey.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
import java.util.Map;

/**
 * Created by Bella on 2018/2/5.
 */
public class ExcelUtil {
    /**
     * 创建excel文档，
     *  list 数据
     * @param keys list中map的key数组集合
     * @param columnNames excel的列名
     * */
    public static HSSFWorkbook createWorkBook(List<Map<String, Object>> list, String []keys, String columnNames[]) {
        // 创建excel工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建第一个sheet页，并命名
        HSSFSheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        // 设置列宽
        for(int i=0;i<keys.length;i++){
            //最后一列为附件URL地址,列宽设置大一些
            if(i==(keys.length-1)){
                sheet.setColumnWidth((short) i, (short) (200*120));
            }else{
                sheet.setColumnWidth((short) i, (short) (50*60));
            }
        }

        // 创建第一行，并设置其单元格格式
        HSSFRow row = sheet.createRow((short) 0);
        row.setHeight((short)500);
        // 单元格格式(用于列名)
        HSSFCellStyle cs = wb.createCellStyle();
        HSSFFont f = wb.createFont();
        f.setFontName("宋体");
        f.setFontHeightInPoints((short) 10);
        f.setBold(true);
        cs.setFont(f);
        cs.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cs.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cs.setLocked(true);
        cs.setWrapText(true);//自动换行
        //设置列名
        for(int i=0;i<columnNames.length;i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }

        //设置首行外,每行每列的值(Row和Cell都从0开始)
        for (short i = 1; i < list.size(); i++) {
            HSSFRow row1 = sheet.createRow((short) i);
            String flag = "";
            //在Row行创建单元格
            for(short j=0;j<keys.length;j++){
                HSSFCell cell = row1.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
                if(list.get(i).get(keys[j])!=null){
                    if("优".equals(list.get(i).get(keys[j]).toString())){
                        flag = "优";
                    }else if("差".equals(list.get(i).get(keys[j]).toString())) {
                        flag = "差";
                    }
                }
            }
            //设置该行样式
            HSSFFont f2 = wb.createFont();
            f2.setFontName("宋体");
            f2.setFontHeightInPoints((short) 10);
            if("优".equals(flag)){
                HSSFCellStyle cellStyle = wb.createCellStyle();
                cellStyle.setFont(f2);
                cellStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
                cellStyle.setLocked(true);
                cellStyle.setWrapText(true);//自动换行
                cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());// 设置背景色
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                //依次为每个单元格设置样式
                for(int m=0;m<keys.length;m++){
                    HSSFCell hssfCell = row1.getCell(m);
                    hssfCell.setCellStyle(cellStyle);
                }
            }else if("差".equals(flag)){
                HSSFCellStyle cellStyle2 = wb.createCellStyle();
                cellStyle2.setFont(f2);
                cellStyle2.setAlignment(HorizontalAlignment.CENTER);// 左右居中
                cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
                cellStyle2.setLocked(true);
                cellStyle2.setWrapText(true);//自动换行
                cellStyle2.setFillForegroundColor(HSSFColor.HSSFColorPredefined.RED.getIndex());// 设置背景色
                cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                for(int m=0;m<keys.length;m++){
                    HSSFCell hssfCell = row1.getCell(m);
                    hssfCell.setCellStyle(cellStyle2);
                }
            }else{
                HSSFCellStyle cs2 = wb.createCellStyle();
                cs2.setFont(f2);
                cs2.setAlignment(HorizontalAlignment.CENTER);// 左右居中
                cs2.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
                cs2.setLocked(true);
                cs2.setWrapText(true);//自动换行
                for(int m=0;m<keys.length;m++){
                    HSSFCell hssfCell = row1.getCell(m);
                    hssfCell.setCellStyle(cs2);
                }
            }
        }
        return wb;
    }

    //生成并下载Excel
    public static void downloadWorkBook(List<Map<String,Object>> list,
                                        String keys[],
                                        String columnNames[],
                                        String fileName,
                                        HttpServletResponse response) throws IOException{
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            ExcelUtil.createWorkBook(list,keys,columnNames).write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "iso-8859-1"));
        ServletOutputStream out = response.getOutputStream();
        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 (final IOException e) {
            throw e;
        } finally {
            if (bis != null)
                bis.close();
            if (bos != null)
                bos.close();
        }
    }
}

3.service 接口：

    String export(HttpServletResponse response,
                         Integer regionid,
                         Integer schoolid,
                         Integer majorid,
                         String beginDate,
                         String endDate);

4.service 接口实现类：

    /**
     * 导出Excel表
     * @param regionid 大区id
     * @param schoolid 校区id
     * @param majorid 专业id
     * @param beginDate 开始日期
     * @param endDate 结束日期
     * @return
     */
    public String export(HttpServletResponse response,
                         Integer regionid,
                         Integer schoolid,
                         Integer majorid,
                         String beginDate,
                         String endDate){
        try {
            List<ProjectAuditListVo> projectAuditListVoList = projectAuditMapper.query(0,regionid,schoolid,majorid,null,beginDate,endDate,null,null);
            String fileName="项目审核表";
            List<Map<String,Object>> list=createExcelRecord(projectAuditListVoList);
            String columnNames[] = {"大区","校区","专业","面授教师","在线教师","班级",
                    "项目所处阶段","作品人次","项目提交日期","项目得分",
                    "得分等级","原因","项目评价","附件"};//列名
            String keys[] = {"regionName","schoolName","majorName","teacherName","onlineTeacherName",
                    "className","stage","workNum", "submitTime","score","rank","reason","evaluate","attachment",};//map中的key
            ExcelUtil.downloadWorkBook(list,keys,columnNames,fileName,response);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return "excel";
    }

    /**
     * 创建Excel表中的记录
     * @param projectAuditListVoList
     * @return
     */
    private List<Map<String, Object>> createExcelRecord(List<ProjectAuditListVo> projectAuditListVoList){
        List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
        try {
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("sheetName", "sheet1");
            listmap.add(map);
            for (int j = 0; j < projectAuditListVoList.size(); j++) {
                ProjectAuditListVo projectAuditListVo=projectAuditListVoList.get(j);
                Map<String, Object> mapValue = new HashMap<String, Object>();
                mapValue.put("regionName",projectAuditListVo.getRegionName());
                mapValue.put("schoolName",projectAuditListVo.getSchoolName());
                mapValue.put("majorName",projectAuditListVo.getMajorName());
                mapValue.put("teacherName",projectAuditListVo.getTeacherName());
                mapValue.put("onlineTeacherName",projectAuditListVo.getOnlineTeacherName());
                mapValue.put("className",projectAuditListVo.getClassName());
                mapValue.put("stage",projectAuditListVo.getStage());
                mapValue.put("workNum",projectAuditListVo.getWorkNum());
                mapValue.put("submitTime", DateTimeUtil.dateToStr(projectAuditListVo.getSubmitTime(),"yyyy-MM-dd"));
                mapValue.put("score",projectAuditListVo.getScore());
                mapValue.put("rank",projectAuditListVo.getRank());
                mapValue.put("reason",projectAuditListVo.getReason());
                mapValue.put("evaluate",projectAuditListVo.getEvaluate());
                String attachmentURL = projectAuditListVo.getAttachment()==null?"无":FileUtil.getUploadPath()+projectAuditListVo.getAttachment();
                mapValue.put("attachment",attachmentURL);
                listmap.add(mapValue);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return listmap;
    }

其中，

List<ProjectAuditListVo> projectAuditListVoList = projectAuditMapper.query(0,regionid,schoolid,majorid,null,beginDate,endDate,null,null);

为调用dao 层方法，获取 Excel中数据源。此处，dao层方法省略

5.controller层：

    @RequestMapping(value = "/export",produces = {"application/vnd.ms-excel;charset=UTF-8"})
    @ResponseBody
    public String export(HttpServletResponse response,
                         @RequestParam(value = "regionid",required = false) Integer regionid,
                         @RequestParam(value = "schoolid",required = false) Integer schoolid,
                         @RequestParam(value = "majorid",required = false) Integer majorid,
                         @RequestParam(value = "beginDate",required = false) String beginDate,
                         @RequestParam(value = "endDate",required = false) String endDate){

        return iProjectAuditService.export(response,regionid,schoolid,majorid,beginDate,endDate);
    }

6.访问export接口，则会自动下载生成的Excel文件至本地。Excel文件效果图如上图所示。