springboot之导出（下载）excel文件

一.maven的依赖见第一篇博文
二.建立BaseFrontController类用于下载excel

package com.nyjk.origin.web.excel;

import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.util.StringUtils;
import org.springframework.validation.annotation.Validated;

import java.io.InputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.Map;

/**
 * @ClassName:
 * @Author Mr GuoQing
 * @Date: 2020/1/15 08:57
 * @Description:
 */
@Validated
public class BaseFrontController {

    /**
     * slf4j 日志 logger
     */
    protected final Logger logger = LoggerFactory.getLogger(this.getClass());

    /**
     * 下载文件,纯SpringMVC的API来完成
     *
     * @param is 文件输入流
     * @param name 文件名称,带后缀名
     *
     * @throws Exception
     */
    public ResponseEntity<byte[]> buildResponseEntity(InputStream is, String name) throws Exception {
        logger.info(">>>>>>>>>>>>>>>>>>>>开始下载文件>>>>>>>>>>");
        if (this.logger.isDebugEnabled())
            this.logger.debug("download: " + name);
        HttpHeaders header = new HttpHeaders();
        String fileSuffix = name.substring(name.lastIndexOf('.') + 1);
        fileSuffix = fileSuffix.toLowerCase();

        Map<String, String> arguments = new HashMap<String, String>();
        arguments.put("xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        arguments.put("xls", "application/vnd.ms-excel");

        String contentType = arguments.get(fileSuffix);
        header.add("Content-Type", (StringUtils.hasText(contentType) ? contentType : "application/x-download"));
        if(is !=null && is.available()!=0){
            header.add("Content-Length", String.valueOf(is.available()));
            header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8"));
            byte[] bs = IOUtils.toByteArray(is);
            logger.info(">>>>>>>>>>>>>>>>>>>>结束下载文件-有记录>>>>>>>>>>");
            logger.info(">>>>>>>>>>结束导出excel>>>>>>>>>>");
            return new ResponseEntity<>(bs, header, HttpStatus.OK);
        }else{
            String string="数据为空";
            header.add("Content-Length", "0");
            header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8"));
            logger.info(">>>>>>>>>>>>>>>>>>>>结束下载文件-无记录>>>>>>>>>>");
            logger.info(">>>>>>>>>>结束导出excel>>>>>>>>>>");
            return new ResponseEntity<>(string.getBytes(), header, HttpStatus.OK);
        }
    }
}

三.执行查询语句

 public ResponseEntity<byte[]> exportCityExcel(HttpServletRequest request, HttpServletResponse response) {
        try {
            logger.info(">>>>>>>>>>开始导出excel>>>>>>>>>>");
            List<CityDO> cityDOS = cityDOMapper.selectAll();
            // 造几条数据
            BaseFrontController baseFrontController = new BaseFrontController();
            return baseFrontController.buildResponseEntity(exportCity(list), "城市信息.xls");
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(">>>>>>>>>>导出excel 异常，原因为：" + e.getMessage());
        }
        return null;
    }

四.执行封装方法构造表格和数据一一对应

 // 城市
    private InputStream exportCity(List<City> list){
        ByteArrayOutputStream output = null;
        InputStream inputStream1 = null;
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 保留1000条数据在内存中
        SXSSFSheet sheet = wb.createSheet();
        // 设置报表头样式
        CellStyle header = ExcelFormatUtil.headSytle(wb);// cell样式
        CellStyle content = ExcelFormatUtil.contentStyle(wb);// 报表体样式

        // 每一列字段名
        String[] strs = new String[] {"城市id","城市名","省级名"};
        int[] ints = new int[] { 5000, 5000,5000};
        ExcelFormatUtil.initTitleEX(sheet, header, strs, ints);

        if (list != null && list.size() > 0) {
            logger.info(">>>>>>>>>>>>>>>>>>>>开始遍历数据组装单元格内容>>>>>>>>>>");
            for(int i = 0 ; i < list.size(); i++ ){
                City city = list.get(i);
                SXSSFRow row = sheet.createRow(i + 1);
                int j = 0;

                SXSSFCell cell = row.createCell(j++);
                cell.setCellValue(city.getId());   // 根据表格设置属性

                cell = row.createCell(j++);
                cell.setCellValue(city.getName());

                cell = row.createCell(j++);
                cell.setCellValue(city.getProvinceDO().getName());
            }
        }
        try {
            output = new ByteArrayOutputStream();
            wb.write(output);
            inputStream1 = new ByteArrayInputStream(output.toByteArray());
            output.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (output != null) {
                    output.close();
                    if (inputStream1 != null)
                        inputStream1.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return inputStream1;

    }

结束，溜溜球。