本文介绍如何利用Java的Apache POI库批量从数据库提取数据并生成Excel表格，涵盖依赖导入、数据获取、表头生成及Excel文件输出等关键步骤。
1.前言
入职后做的第一个小需求，按照产品的要求将数据库中想要的信息摘取出来生成一张Excel表。因可能涉及公司，以下代码示例有改动且没有实际运行过，均只代表逻辑思路
2.导入依赖
此功能需要的依赖如下
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
这里注意其版本一定要一致，不一致会报错
3.获取数据库数据
写一个UserInfoMapper.java和UserInfoMapper.xml和UserInfoService.java从数据库获取信息
@Mapper
public interface UserInfoMapper {
/**
* @desc 查询所有用户信息
* @return 返回多个用户List
* */
List<Map<String,Object>> queryUserInfoResultListMap();
}
<mapper namespace="com.xiaomi.youpin.content.videoclean.mapper.UserInfoMapper">
<select id="queryUserInfoResultListMap" resultType="HashMap">
select * from user_info
</select>
</mapper>
@Service
public class UserInfoService {
@Autowired
UserInfoMapper userInfoMapper;
public List<Map<String,Object>> queryUserInfoResultListMap() {
List<Map<String,Object>> list = userInfoMapper.queryUserInfoResultListMap();
return list;
}
}
这里不是只能从数据库拿数据来生成表格，可根据实际情况改动，只需要最后能拿到一个List<Map<Object,Object>>结构的list即可，每个Map中的key-value都是对应着每个单元格中的表头和对应字段，一个Map中的多个key-value组成一行表格，一个list中的多个Map组成一张表格。
4.生成表格表头
这个ColumnTitleMap用来确立数据和表头的对应关系的，用法见注释和下文
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
/**
* @ClassName ColumnTitleMap
* @Description 数据导出，生成excel文件时的列名称
* @Author qiwen
* @Data 2020/8/4 15:59
*/
public class ColumnTitleMap {
private Map<String, String> columnTitleMap = new HashMap<String, String>();
private ArrayList<String> titleKeyList = new ArrayList<String>();
public ColumnTitleMap() {
initUserInfoColu();
initUserInfoTitleKeyList();
}
/**
* mysql用户表需要导出字段--显示名称对应集合
*/
private void initUserInfoColu() {
columnTitleMap.put("uuid", "内容ID");
columnTitleMap.put("contentType", "内容类型");
columnTitleMap.put("title", "标题");
columnTitleMap.put("publishedTime", "发布时间");
columnTitleMap.put("uid", "作者ID");
columnTitleMap.put("nickname", "昵称");
columnTitleMap.put("readingNum", "阅读量");
columnTitleMap.put("likes", "点赞量");
columnTitleMap.put("src", "视频地址");
columnTitleMap.put("link", "内容链接");
}
/**
* mysql用户表需要导出字段集
*/
private void initUserInfoTitleKeyList() {
titleKeyList.add("uuid");
titleKeyList.add("contentType");
titleKeyList.add("title");
titleKeyList.add("publishedTime");
titleKeyList.add("uid");
titleKeyList.add("nickname");
titleKeyList.add("readingNum");
titleKeyList.add("likes");
titleKeyList.add("src");
titleKeyList.add("link");
}
public Map<String, String> getColumnTitleMap() {
return columnTitleMap;
}
public ArrayList<String> getTitleKeyList() {
return titleKeyList;
}
}
3.Controller
这里这个Controller作为这个程序的入口
import com.alibaba.fastjson.JSON;
import com.xiaomi.youpin.content.videoclean.entity.ColumnTitleMap;
import com.xiaomi.youpin.content.videoclean.service.ExportDataService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* @ClassName ExporDataController
* @Description 数据导出api控制器
* @Author qiwen
* @Data 2020/8/4 16:54
*/
@Controller
@Slf4j
@RequestMapping(value = "/")
public class ExporDataController {
@Autowired
UserInfoService userInfoService;
@Autowired
ExportDataService exportDataService;
@GetMapping(value = "/excel")
@ResponseBody
public void getUserInfoEx() {
try {
List<Map<String,Object>> userList = userInfoService.queryUserInfoResultListMap();
ArrayList<String> titleKeyList= new ColumnTitleMap().getTitleKeyList();
Map<String, String> titleMap = new ColumnTitleMap().getColumnTitleMap();
exportDataService.exportDataToEx(response, titleKeyList, titleMap, userList);
} catch (Exception e) {
log.info("Exception: "+e.toString());
return "出错";
}
}
}
这里的userInfoService用来获取数据库数据（本文为虚假数据），exportDataService用来输出Excel表格，ColumnTitleMap()用来获取表头
4.生成Excel文件
ExportDataService.java调用工具类ExportExcelUtil.java导出Excel表格
import com.xiaomi.youpin.content.videoclean.util.ExportExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* @ClassName ExportDataService
* @Description 数据导出服务
* @Author qiwen
* @Data 2020/8/4 17:32
*/
@Slf4j
@Service
public class ExportDataService {
@Autowired
ExportExcelUtil exportExcelUtil;
/*导出用户数据表*/
public void exportDataToEx(ArrayList<String> titleKeyList, Map<String, String> titleMap, List<Map<String, Object>> src_list) {
try {
exportExcelUtil.expoerDataExcel(titleKeyList, titleMap, src_list);
} catch (Exception e) {
log.error("Exception: " + e.toString());
}
}
}
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* @ClassName ExportExcelUtil
* @Description 导出工具类
* @Author qiwen
* @Data 2020/8/4 17:07
*/
@Component
public class ExportExcelUtil {
/*写入文件的上级目录*/
@Value("${fileWritePath}")
private String fileWritePath;
/*生成Excel表格*/
public void expoerDataExcel(ArrayList<String> titleKeyList,
Map<String, String> titleMap, List<Map<String, Object>> srcList) throws IOException {
Date date = new Date();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd :HH:mm:ss");
String xlsFileName = dateFormat.format(date) + ".xlsx";
Workbook wb = new SXSSFWorkbook(100); //创建Excel文件
Sheet sheet = null; //工作表对象
Row nRow = null; //行对象
Cell nCell = null; //列对象
int rowNo = 0; //总行号
int pageRowNo = 0; //页行号
for (int k = 0; k < srcList.size(); k++) {
Map<String, Object> srcMap = srcList.get(k);
//写入300000条后切换到下个工作表
if (rowNo % 100000 == 0) {
wb.createSheet("工作簿" + (rowNo / 100000));//创建新的sheet对象
sheet = wb.getSheetAt(rowNo / 100000); //动态指定当前的工作表
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("");
}
}
}
File writeFile = new File(fileWritePath + "/" + xlsFileName);
writeFile.createNewFile();
FileOutputStream out = new FileOutputStream(writeFile);
wb.write(out);
wb.close();
out.flush();
out.close();
// 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();
}
}
这里将最后生成的Excel文件放入了指定目录下，文件名是用时间命名的。注释掉的代码替换掉上方File操作之后的代码，再在方法入参传入HttpServletResponse response， ExportDataService 中的方法也传入response，可以实现在访问Controller后浏览器下载Excel文件。
Workbook wb = new SXSSFWorkbook(100);
处理xls的excel用的workbook是HSSFWorkbook，
处理xlsx的excel用的是XSSFWorkbook。
上面两个类导出excel的时候数据会驻留在内存中，所以当数据量大的时候容易造成内存溢出。SXSSFWorkbook是用来生成海量excel数据文件,主要原理是借助临时存储空间生成excel。SXSSF是XSSF的一个与API兼容的流扩展，在需要生成非常大的电子表格时使用，堆空间有限。SXSSF通过限制对滑动窗口中的行的访问来实现其低内存占用，而XSSF允许访问文档中的所有行。当将旧行写入磁盘时，不再在窗口中的旧行变得不可访问。这里的100是指内存中存100条数据后会把数据写入磁盘。
尾声
本文所说方法基本逻辑就是把数据放在list里，在用POI的相关依赖输出Excel，但当数据量过大时，Workbook那里可以用SXSSFWorkbook解决，但list过大怎么解决？
这里一个提供一个思路就是查数据库的时候分页查，总之就是将大数据量分批次处理应该就可以了。
作者纯小白，有错误欢迎指正。
扫一扫
1098
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
