一、基于框架
1.IDE
IntelliJ IDEA2.软件环境
Spring bootmysqlmybatisorg.apache.poi二、环境集成
1.创建spring boot项目工程
略过
2.maven引入poi
<!--数据导出依赖 excel--><!-- /artifact/org.apache.poi/poi --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><!-- /artifact/org.apache.poi/poi-ooxml --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><!-- /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: .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: .6.11*/@Controller@RequestMapping(value = "/exportdata")public class ExportDataController {@AutowiredUserInfoService userInfoService;@AutowiredExportDataService 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.javapackage com.mcrazy.apios.mapper;import com.mcrazy.apios.model.UserInfo;import org.apache.ibatis.annotations.Mapper;import java.util.List;import java.util.Map;@Mapperpublic interface UserInfoMapper {/*** @desc 查询所有用户信息* @return 返回多个用户List* */List<Map<String,Object>> queryUserInfoResultListMap();}
UserInfoMapper.xml
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/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;@Servicepublic class UserInfoService {@AutowiredUserInfoMapper userInfoMapper;/*** @desc 查询所有用户信息* @return 返回多个用户List* */public List<Map<String,Object>> queryUserInfoResultListMap() {List<Map<String,Object>> list = userInfoMapper.queryUserInfoResultListMap();return list;}}
(2). 生成excel文件和导出
ExportDataService.javapackage 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: .6.11*/@Servicepublic class ExportDataService {@AutowiredExportExcelUtil 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: .6.12*/@Servicepublic 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=-12-19&end_start=-12-19
得到xlsx文件，查看数据
