SpringBoot+Poi导出excel的功能实现

前言:配置
1.pom文件

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.7</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.7</version>
		</dependency>
		

2.封装的工具类

package cn.ahyjkj.common.config;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.util.List;
import java.util.Map;

/**
 * 导出Excel文档工具类
 *
 * */
public class ExcelUtil {

    /**
     * 创建excel文档，
     * @param list 数据
     * @param keys list中map的key数组集合
     * @param columnNames excel的列名
     * */
    public static Workbook createWorkBook(List<Map<String, Object>> list,String []keys,String columnNames[]) {
        // 创建excel工作簿
        Workbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        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();

        cs.setAlignment(CellStyle.ALIGN_CENTER); // 居中
        cs2.setAlignment(CellStyle.ALIGN_CENTER); // 居中

        //、设置边框:
        cs.setBorderBottom(CellStyle.BORDER_THIN); //下边框
        cs.setBorderLeft(CellStyle.BORDER_THIN);//左边框
        cs.setBorderTop(CellStyle.BORDER_THIN);//上边框
        cs.setBorderRight(CellStyle.BORDER_THIN);//右边框

        cs2.setBorderBottom(CellStyle.BORDER_THIN); //下边框
        cs2.setBorderLeft(CellStyle.BORDER_THIN);//左边框
        cs2.setBorderTop(CellStyle.BORDER_THIN);//上边框
        cs2.setBorderRight(CellStyle.BORDER_THIN);//右边框

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体显示

        cs.setFont(f);//选择需要用到的字体格式

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());


        //设置列名
        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 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow(i);
            // 在row行上创建一个方格
            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;
    }

}

一. 页面代码

  注:前端页面请求不能使用aja异步请求,否则不提示下载框(已踩坑,谨记)

  vm.layPlugins.layer.confirm('确定导出数据吗？', function (index) {
       //ids是我请求后台的参数(不能使用ajax异步)
      window.location.href=contextpath+"/audit/export?ids="+idList;
        vm.layPlugins.layer.close(index)
         })

二 . 后端代码

1.controller层

  /**
     *导出excel格式数据
     * @param ids 审计id数组
     * @return
     */
    @RequestMapping(value = "/export",produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
    public void exportExcel(@RequestParam Long[] ids, HttpServletResponse response) throws IOException {
        auditService.exportExcel(ids,response);
    }

2.service与serviceImpl层

 /**
     * 导出excel格式数据
     * @param ids 审计id数组
     */
     void exportExcel(Long[] ids, HttpServletResponse response) throws IOException;

   @Override
    public void exportExcel(Long[] ids, HttpServletResponse response) throws IOException {

        //表头数
        String[] header = {"审计单位", "审计年度", "项目名称", "项目类别", "审计金额(元)","被审计单位", "单位性质","委托外包","状态"};
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        //文件名
        String fileName = sdf.format(new Date()).toString() + ".xls";
        //封装的方法需要的key 后期excel对应表头
        String keys[] = {"username","year","projectName","projectType","auditSum","unitName","unitType","epiboly","state"};//map中的key
        List<AuditEntity> auditList = auditDao.selectBatchIds(Arrays.asList(ids));
        List<Map<String, Object>> list= createExcelRecord(auditList);
        //通用代码
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            ExcelUtil.createWorkBook(list, keys, header).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).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;
            // Simple read/write loop.
            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();
        }



    }

封装的方法

 private List<Map<String, Object>> createExcelRecord(List<AuditEntity> list) {
        List<Map<String, Object>> listmap = new ArrayList<>();
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("sheetName", "sheet1");
        listmap.add(map);
        AuditEntity audit = null;
        String projectType = null;
        String unitType;
        String epiboly;
        String state;

        for (int i = 0; i < list.size(); i++) {
            audit = list.get(i);

            if (list.get(i).getProjectType()==0){
                projectType="财政收支";
            }else if (list.get(i).getProjectType()==1){
                projectType="固定资产投资";
            }else if (list.get(i).getProjectType()==2){
                projectType="内部控制和风险管理";
            } else if (list.get(i).getProjectType()==3){
                projectType="经济责任";
            } else if (list.get(i).getProjectType()==4){
                projectType="其他";
            }
            if (list.get(i).getUnitType()==0){
                unitType="事业单位";
            }else {
                unitType="企业单位";
            }
            if (list.get(i).getEpiboly()==0){
                epiboly="是";
            }else {
                epiboly="否";
            }
            if (list.get(i).getState()==0){
                state="未提交";
            }else {
                state="已提交";
            }
            Map<String, Object> mapValue = new HashMap<String, Object>();

                mapValue.put("username", audit.getUsername());
                mapValue.put("year", audit.getYear());
                mapValue.put("projectName", audit.getProjectName());
                mapValue.put("projectType",projectType);
                mapValue.put("auditSum",audit.getAuditSum());
                mapValue.put("unitName", audit.getUnitName());
                mapValue.put("unitType", unitType);
                mapValue.put("epiboly", epiboly);
                mapValue.put("state", state);

                listmap.add(mapValue);

        }
        return listmap;
    }