SpringBoot:实现导出数据生成excel文件返回

一、基于框架

1.IDE

• IntelliJ IDEA

2.软件环境

• Spring boot
• mysql
• mybatis
• org.apache.poi

二、环境集成

1.创建spring boot项目工程

略过

2.maven引入poi

<!--数据导出依赖 excel-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml-schemas</artifactId>
   <version>3.17</version>
</dependency>
<!--数据导出依赖 End excel-->

三、代码实现

此处以导出云端mysql数据中的用户表为例（数据为虚假数据）

1.配置xls表格表头

此处我创建一个class(ColumnTitleMap)来维护需要导出的mysql表和xls表头显示的关系
代码注释已经清晰明了，就不再赘述

/**
 * @desc:数据导出，生成excel文件时的列名称集合
 * @author: chao
 * @time: 2018.6.11
 */
public class ColumnTitleMap {
    private Map<String, String> columnTitleMap = new HashMap<String, String>();
    private ArrayList<String> titleKeyList = new ArrayList<String> ();

    public ColumnTitleMap(String datatype) {
        switch (datatype) {
            case "userinfo":
                initUserInfoColu();
                initUserInfoTitleKeyList();
                break;
            default:
                break;
        }

    }
    /**
     * mysql用户表需要导出字段--显示名称对应集合
     */
    private void initUserInfoColu() {
        columnTitleMap.put("id", "ID");
        columnTitleMap.put("date_create", "注册时间");
        columnTitleMap.put("name", "名称");
        columnTitleMap.put("mobile", "手机号");
        columnTitleMap.put("email", "邮箱");
        columnTitleMap.put("pw", "密码");
        columnTitleMap.put("notice_voice", "语音通知开关");
        columnTitleMap.put("notice_email", "邮箱通知开关");
        columnTitleMap.put("notice_sms", "短信通知开关");
        columnTitleMap.put("notice_push", "应用通知开关");
    }

    /**
     * mysql用户表需要导出字段集
     */
    private void initUserInfoTitleKeyList() {
        titleKeyList.add("id");
        titleKeyList.add("date_create");
        titleKeyList.add("name");
        titleKeyList.add("mobile");
        titleKeyList.add("email");
        titleKeyList.add("pw");
        titleKeyList.add("notice_voice");
        titleKeyList.add("notice_email");
        titleKeyList.add("notice_sms");
        titleKeyList.add("notice_push");
    }

    public Map<String, String> getColumnTitleMap() {
        return columnTitleMap;
    }

    public ArrayList<String> getTitleKeyList() {
        return titleKeyList;
    }
}

2.controller

提供对外接口，ExportDataController.java

package com.mcrazy.apios.controller;

import com.mcrazy.apios.service.ExportDataService;
import com.mcrazy.apios.service.UserInfoService;
import com.mcrazy.apios.util.datebase.columntitle.ColumnTitleMap;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @desc:数据导出api控制器
 * @author: chao
 * @time: 2018.6.11
 */

@Controller
@RequestMapping(value = "/exportdata")
public class ExportDataController {
    @Autowired
    UserInfoService userInfoService;
    @Autowired
    ExportDataService exportDataService;

    /**
     * @api: /apios/exportdata/excel/
     * @method: GET
     * @desc: 导出数据，生成xlsx文件
     * @param response 返回对象
     * @param date_start 筛选时间，开始(预留，查询时并未做筛选数据处理)
     * @param date_end 筛选时间，结束(预留，查询时并未做筛选数据处理)
     */
    @GetMapping(value = "/excel")
    public void getUserInfoEx(
            HttpServletResponse response,
            @RequestParam String date_start,
            @RequestParam String date_end
    ) {
        try {
            List<Map<String,Object>> userList = userInfoService.queryUserInfoResultListMap();
            ArrayList<String> titleKeyList= new ColumnTitleMap("userinfo").getTitleKeyList();
            Map<String, String> titleMap = new ColumnTitleMap("userinfo").getColumnTitleMap();
            exportDataService.exportDataToEx(response, titleKeyList, titleMap, userList);
        } catch (Exception e) {
            //
            System.out.println(e.toString());
        }
    }
}

3.service

(1).用户表数据

• UserInfoMapper.java

package com.mcrazy.apios.mapper;

import com.mcrazy.apios.model.UserInfo;
import org.apache.ibatis.annotations.Mapper;

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


@Mapper
public interface UserInfoMapper {
    /**
     * @desc 查询所有用户信息
     * @return 返回多个用户List
     * */
    List<Map<String,Object>> queryUserInfoResultListMap();
}

• UserInfoMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.mcrazy.apios.mapper.UserInfoMapper">
    <select id="queryUserInfoResultListMap" resultType="HashMap">
        select * from user_info
    </select>
</mapper>

• UserInfoService.java

package com.mcrazy.apios.service;

import com.mcrazy.apios.mapper.UserInfoMapper;
import com.mcrazy.apios.model.UserInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

@Service
public class UserInfoService {
    @Autowired
    UserInfoMapper userInfoMapper;
    
    /**
     * @desc 查询所有用户信息
     * @return 返回多个用户List
     * */
    public List<Map<String,Object>> queryUserInfoResultListMap() {
        List<Map<String,Object>> list = userInfoMapper.queryUserInfoResultListMap();
        return  list;
    }
}

(2). 生成excel文件和导出

• ExportDataService.java

package com.mcrazy.apios.service;

import com.mcrazy.apios.util.datebase.ExportExcelUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @desc:数据导出服务
 * @author: chao
 * @time: 2018.6.11
 */
@Service
public class ExportDataService {
    @Autowired
    ExportExcelUtil exportExcelUtil;

    /*导出用户数据表*/
    public void exportDataToEx(HttpServletResponse response, ArrayList<String> titleKeyList, Map<String, String> titleMap, List<Map<String,Object>> src_list) {
        try {
            exportExcelUtil.expoerDataExcel(response, titleKeyList, titleMap, src_list);
        } catch (Exception e) {
            System.out.println("Exception: " + e.toString());
        }
    }
}

• 导出工具封装，ExportExcelUtil.java

package com.mcrazy.apios.util.datebase;

import com.mcrazy.apios.util.object.DateUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @desc:数据导出，生成excel文件
 * @author: chao
 * @time: 2018.6.12
 */
@Service
public class ExportExcelUtil {

    public void expoerDataExcel(HttpServletResponse response, ArrayList<String> titleKeyList, Map<String, String> titleMap, List<Map<String,Object>> src_list) throws IOException {

        String xlsFile_name = DateUtils.currtimeToString14() + ".xlsx";     //输出xls文件名称
        //内存中只创建100个对象
        Workbook wb = new SXSSFWorkbook(100);           //关键语句
        Sheet sheet = null;     //工作表对象
        Row nRow = null;        //行对象
        Cell nCell = null;      //列对象

        int rowNo = 0;      //总行号
        int pageRowNo = 0;  //页行号

        for (int k=0;k<src_list.size();k++) {
            Map<String,Object> srcMap = src_list.get(k);
            //写入300000条后切换到下个工作表
            if(rowNo%300000==0){
                wb.createSheet("工作簿"+(rowNo/300000));//创建新的sheet对象
                sheet = wb.getSheetAt(rowNo/300000);        //动态指定当前的工作表
                pageRowNo = 0;      //新建了工作表,重置工作表的行号为0
                // -----------定义表头-----------
                nRow = sheet.createRow(pageRowNo++);
                // 列数 titleKeyList.size()
                for(int i=0;i<titleKeyList.size();i++){
                    Cell cell_tem = nRow.createCell(i);
                    cell_tem.setCellValue(titleMap.get(titleKeyList.get(i)));
                }
                rowNo++;
                // ---------------------------
            }
            rowNo++;
            nRow = sheet.createRow(pageRowNo++);    //新建行对象

            // 行，获取cell值
            for(int j=0;j<titleKeyList.size();j++){
                nCell = nRow.createCell(j);
                if (srcMap.get(titleKeyList.get(j)) != null) {
                    nCell.setCellValue(srcMap.get(titleKeyList.get(j)).toString());
                } else {
                    nCell.setCellValue("");
                }
            }
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + xlsFile_name);
        response.flushBuffer();
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        wb.close();
        outputStream.flush();
        outputStream.close();
    }
}

三、运行

至此，所有代码工作已经做完，把程序运行起来，在浏览器调用接口，会自动下载到电脑中

• 浏览器打开：

http://192.168.1.70:8080/apios/exportdata/excel/?time_start=2018-12-19&end_start=2018-12-19

• 效果
  

• 得到xlsx文件，查看数据