Java使用POI实现大数据量导出
项目架构:springboot 项目 单体服务 -----下一篇是使用dubbo的服务
1)第一步：pom引入poi对应的jar包：
<dependency><groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.0</version>
</dependency>
工具类：
package com.alibaba.poi;/**
* @description EXCEL常量类
*/
public class ExcelConstant {
/**
* 每个sheet存储的记录数 100W
*/
public static final Integer PER_SHEET_ROW_COUNT = 1000000;
/**
* 每次向EXCEL写入的记录数(查询每页数据大小) 20W
*/
public static final Integer PER_WRITE_ROW_COUNT = 200000;
/**
* 每个sheet的写入次数 5
*/
public static final Integer PER_SHEET_WRITE_COUNT = PER_SHEET_ROW_COUNT / PER_WRITE_ROW_COUNT;
}
PoiUtil:
package com.alibaba.poi;import com.alibaba.service.WriteExcelDataDelegated;
import com.alibaba.utils.DateUtil;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
/**
* @description POI导出工具类
*/
public class PoiUtil {
private final static Logger logger = LoggerFactory.getLogger(PoiUtil.class);
/**
* 初始化EXCEL(sheet个数和标题)
*
* @param totalRowCount 总记录数
* @param titles 标题集合
* @return XSSFWorkbook对象
*/
public static SXSSFWorkbook initExcel(Integer totalRowCount, String[] titles) {
// 在内存当中保持 100 行 , 超过的数据放到硬盘中在内存当中保持 100 行 , 超过的数据放到硬盘中
SXSSFWorkbook wb = new SXSSFWorkbook(100);
Integer sheetCount = ((totalRowCount % ExcelConstant.PER_SHEET_ROW_COUNT == 0) ?
(totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT) : (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT + 1));
// 根据总记录数创建sheet并分配标题
for (int i = 0; i < sheetCount; i++) {
SXSSFSheet sheet = wb.createSheet("sheet" + (i + 1));
SXSSFRow headRow = sheet.createRow(0);
for (int j = 0; j < titles.length; j++) {
SXSSFCell headRowCell = headRow.createCell(j);
headRowCell.setCellValue(titles[j]);
}
}
return wb;
}
/**
* 下载EXCEL到本地指定的文件夹
*
* @param wb EXCEL对象SXSSFWorkbook
* @param exportPath 导出路径
*/
public static void downLoadExcelToLocalPath(SXSSFWorkbook wb, String exportPath) {
FileOutputStream fops = null;
try {
fops = new FileOutputStream(exportPath);
wb.write(fops);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != wb) {
try {
wb.dispose();
} catch (Exception e) {
e.printStackTrace();
}
}
if (null != fops) {
try {
fops.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 下载EXCEL到浏览器
*
* @param wb EXCEL对象XSSFWorkbook
* @param response
* @param fileName 文件名称
* @throws IOException
*/
public static void downLoadExcelToWebsite(SXSSFWorkbook wb, HttpServletResponse response, String fileName) throws IOException {
response.setHeader("Content-disposition", "attachment; filename="
+ new String((fileName + ".xlsx").getBytes("utf-8"), "ISO8859-1"));//设置下载的文件名
OutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
wb.write(outputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != wb) {
try {
wb.dispose();
} catch (Exception e) {
e.printStackTrace();
}
}
if (null != outputStream) {
try {
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 导出Excel到本地指定路径
*
* @param totalRowCount 总记录数
* @param titles 标题
* @param exportPath 导出路径
* @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
* @throws Exception
*/
public static final void exportExcelToLocalPath(Integer totalRowCount, String[] titles, String exportPath, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {
logger.info("开始导出：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
// 初始化EXCEL
SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);
// 调用委托类分批写数据
int sheetCount = wb.getNumberOfSheets();
for (int i = 0; i < sheetCount; i++) {
SXSSFSheet eachSheet = wb.getSheetAt(i);
for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {
int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
int endRowCount = startRowCount + pageSize - 1;
writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);
}
}
// 下载EXCEL
PoiUtil.downLoadExcelToLocalPath(wb, exportPath);
logger.info("导出完成：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
}
/**
* 导出Excel到浏览器
*
* @param response
* @param totalRowCount 总记录数
* @param fileName 文件名称
* @param titles 标题
* @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
* @throws Exception
*/
public static final void exportExcelToWebsite(HttpServletResponse response, Integer totalRowCount, String fileName, String[] titles, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {
logger.info("开始导出：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
// 初始化EXCEL
SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);
// 调用委托类分批写数据
int sheetCount = wb.getNumberOfSheets();
for (int i = 0; i < sheetCount; i++) {
SXSSFSheet eachSheet = wb.getSheetAt(i);
for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {
int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
int endRowCount = startRowCount + pageSize - 1;
writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);
}
}
// 下载EXCEL
PoiUtil.downLoadExcelToWebsite(wb, response, fileName);
logger.info("导出完成：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
}
}
DateUtil工具类：处理时间格式
package com.alibaba.utils;import java.text.SimpleDateFormat;
import java.util.Date;
/**
* @description 日期工具类
*/
public class DateUtil {
public static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";
/**
* 将日期转换为字符串
*
* @param date DATE日期
* @param format 转换格式
* @return 字符串日期
*/
public static String formatDate(Date date, String format) {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
return simpleDateFormat.format(date);
}
}
WriteExcelDataDelegated接口package com.alibaba.service;import org.apache.poi.xssf.streaming.SXSSFSheet;
public interface WriteExcelDataDelegated {
/**
* EXCEL写数据委托类 针对不同的情况自行实现
*
* @param eachSheet 指定SHEET
* @param startRowCount 开始行
* @param endRowCount 结束行
* @param currentPage 分批查询开始页
* @param pageSize 分批查询数据量
* @throws Exception
*/
public abstract void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception;
}
目前为止工具类完毕：
controller层：
package com.alibaba.controller;import com.alibaba.ExprotForm;
import com.alibaba.service.UserService;
import com.alibaba.utils.ResultJSON;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
@RestController
@RequestMapping(value = "/user")
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/export")
public ResultJSON exportUser(ExprotForm form, HttpServletResponse response) throws Exception {
userService.export(form, response);
return new ResultJSON();
}
}
service接口：
package com.alibaba.service;import com.alibaba.ExprotForm;
import javax.servlet.http.HttpServletResponse;
/**
* @description 用户SERVICE
*/
public interface UserService {
/**
* 导出用户EXCEL
*
* @param form
* @return VOID
* @throws Exception
*/
void export(ExprotForm form, HttpServletResponse response) throws Exception;
}
实现类：
package com.alibaba.service.impl;import com.alibaba.ExprotForm;
import com.alibaba.dto.QryIntegrationDTO;
import com.alibaba.entity.SIntgration;
import com.alibaba.mapper.SIntgrationMapper;
import com.alibaba.poi.IntegrationSourceEnum;
import com.alibaba.poi.PoiUtil;
import com.alibaba.service.UserService;
import com.alibaba.service.WriteExcelDataDelegated;
import com.alibaba.utils.DateUtil;
import com.alibaba.vo.IntegrationListVO;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private SIntgrationMapper intgrationMapper;
@Autowired
private HttpServletRequest request;
@Override
public void export(ExprotForm form,HttpServletResponse response) throws Exception {
QryIntegrationDTO dto = new QryIntegrationDTO();
// 总记录数
Integer totalRowCount = intgrationMapper.qryIntegrationCount(dto);
// 导出EXCEL文件名称
// 标
String[] titles = {"序号", "积分编号", "会员ID", "姓名", "会员手机号", "积分收支", "积分变化", "积分余额", "时间", "备注"};
// 开始导入
//Integer totalRowCount, String[] titles, String exportPath, WriteExcelDataDelegated writeExcelDataDelegated
//exportExcelToWebsite exportExcelToLocalPath
PoiUtil.exportExcelToWebsite( response, totalRowCount, "002.xlsx",titles, new WriteExcelDataDelegated() {
@Override
public void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception {
PageHelper.startPage(currentPage, pageSize);
List<IntegrationListVO> userVOList = intgrationMapper.qryIntegration(dto);
// List<SIntgration> userVOList = intgrationMapper.selectByExample(null);
int num = 0;
if (!CollectionUtils.isEmpty(userVOList)) {
for (int i = startRowCount; i <= endRowCount; i++) {
num++;
SXSSFRow eachDataRow = eachSheet.createRow(i);
if ((i - startRowCount) < userVOList.size()) {
IntegrationListVO vo = userVOList.get(i - startRowCount);
eachDataRow.createCell(0).setCellValue(num);
eachDataRow.createCell(1).setCellValue(vo.getScoreId());
eachDataRow.createCell(2).setCellValue(vo.getUserid());
eachDataRow.createCell(3).setCellValue(vo.getRealname() == null ? "" : vo.getRealname());
eachDataRow.createCell(4).setCellValue(vo.getPhone() == null ? "" : vo.getPhone());
//积分支出/收入（0：收入 1：支出）
if ("1".equals(vo.getFlag())) {
eachDataRow.createCell(5).setCellValue("" + vo.getScore());
} else {
eachDataRow.createCell(5).setCellValue("+" + vo.getScore());
}
String description = IntegrationSourceEnum.getStatusState(vo.getScoreType()).getDescription();
eachDataRow.createCell(6).setCellValue(description);
eachDataRow.createCell(7).setCellValue(vo.getLeftScore());
if (null != vo.getCreateTime()) {
eachDataRow.createCell(8).setCellValue(vo.getCreateTime());
}
eachDataRow.createCell(9).setCellValue(vo.getRemark() == null ? "" : vo.getRemark());
}
}
}
}
});
}
}
以上是 Java使用POI实现大数据量导出 的全部内容， 来源链接： utcz.com/z/511344.html
