本文主要是介绍mysql预留字段easypoi_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表头显示的关系
代码注释已经清晰明了，就不再赘述
/**
* @desc:数据导出，生成excel文件时的列名称集合
* @author: chao
* @time: 2018.6.11
*/
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;
}
}
/**
* 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 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;
/**
* @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> 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 {
/**
* @desc 查询所有用户信息
* @return 返回多个用户List
* */
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;
/**
* @desc 查询所有用户信息
* @return 返回多个用户List
* */
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;
/**
* @desc:数据导出服务
* @author: chao
* @time: 2018.6.11
*/
@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;
/**
* @desc:数据导出，生成excel文件
* @author: chao
* @time: 2018.6.12
*/
@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
Map 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
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
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文件，查看数据
以上这篇SpringBoot 导出数据生成excel文件返回方式就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持脚本之家。
这篇关于mysql预留字段easypoi_SpringBoot 导出数据生成excel文件返回方式的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
