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

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<String, String> 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

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

}

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

List

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

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

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文件，查看数据

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文件，查看数据

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文件，查看数据

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文件，查看数据

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。