SpringBoot 导出数据生成excel文件返回方式

1.IDE

IntelliJ IDEA

2.软件环境

Spring boot

mysql

mybatis

org.apache.poi

1.创建spring boot项目工程

略过

2.maven引入poi

 org.apache.poi
 poi
 3.17



 org.apache.poi
 poi-ooxml
 3.17



 org.apache.poi
 poi-ooxml-schemas
 3.17

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

1.配置xls表格表头

此处我创建一个class(ColumnTitleMap)来维护需要导出的mysql表和xls表头显示的关系

代码注释已经清晰明了，就不再赘述

public class ColumnTitleMap {
 private Map columnTitleMap = new HashMap();
 private ArrayList titleKeyList = new ArrayList ();

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

 }
 
 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", "应用通知开关");
 }

 
 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 getColumnTitleMap() {
  return columnTitleMap;
 }

 public ArrayList 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;



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

 
 @GetMapping(value = "/excel")
 public void getUserInfoEx(
   HttpServletResponse response,
   @RequestParam String date_start,
   @RequestParam String date_end
 ) {
  try {
   List> userList = userInfoService.queryUserInfoResultListMap();
   ArrayList titleKeyList= new ColumnTitleMap("userinfo").getTitleKeyList();
   Map 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 {
 
 List> queryUserInfoResultListMap();
}

UserInfoMapper.xml

 
  select * from user_info
 

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;
 
 
 public List> queryUserInfoResultListMap() {
  List> 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;


@Service
public class ExportDataService {
 @Autowired
 ExportExcelUtil exportExcelUtil;

 
 public void exportDataToEx(HttpServletResponse response, ArrayList titleKeyList, Map titleMap, List> 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;


@Service
public class ExportExcelUtil {

 public void expoerDataExcel(HttpServletResponse response, ArrayList titleKeyList, Map titleMap, List> 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 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