poi+jxl实现模板式的百万级excel导出
简介
本⽂采⽤excel模板的⽅式实现excel导出，在excel2007及其以上版本，可实现百万级数据导出，在excel2003预计⼏万，超过会报内存溢出。
这套逻辑，关于导出，控制层和业务层⼏乎不⽤写太多代码
⼀、pom引⼊
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-core</artifactId>
<version>1.0.6</version>
</dependency>
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>1.0.1</version>
</dependency>
⼆、辅助SheetInfo类
import java.util.ArrayList;
import java.util.List;
error parse new
import urrent.atomic.AtomicInteger;
/**
* sheet实体类
*/
public class SheetInfo {
/
**
* sheet命名
*/
private String sheetName;
/**
* 列别名
*/
private List<String> colNames =new ArrayList<String>();
/**
* 数据别名
*/
private String dataAlias;
private int start =0;//替代⾏数
private int oldEnd =0;//结束⾏数
private int sheetNum =-1;//sheet数，默认-1,⽆sheet页
private int sheetNum =-1;//sheet数，默认-1,⽆sheet页private AtomicInteger cursor =new AtomicInteger();//游标位置
public String getSheetName(){
return sheetName;
}
public void setSheetName(String sheetName){
this.sheetName = sheetName;
}
public List<String>getColNames(){
return colNames;
}
public void setColNames(List<String> colNames){
}
public int getStart(){
return start;
}
public void setStart(int start){
this.start = start;
cursor.set(start);
}
public int getOldEnd(){
return oldEnd;
}
public void setOldEnd(int oldEnd){
this.oldEnd = oldEnd;
}
public String getDataAlias(){
return dataAlias;
}
public void setDataAlias(String dataAlias){
this.dataAlias = dataAlias;
}
public int getSheetNum(){
return sheetNum;
}
public void setSheetNum(int sheetNum){
this.sheetNum = sheetNum;
}
/**
* 得到游标，并将游标下移⼀位
* @author yyh 2020年4⽉14⽇下午2:29:37
* @return
* @since 2.1
*/
public int getCursorAndMove(){
AndIncrement();
}
}
三、在XLSTransformer扩展⼯具类
del.SheetInfo;
import net.ption.ParsePropertyException;
import net.ansformer.XLSTransformer;
import org.apache.ptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;
/**
* 基于XLSTransformer上的开发，可⽤于百万级数据的导出
* 注：只⽀持excel2007及其以上的excel
*/
public class XLSTransformerExtend  extends XLSTransformer {
private static final int DEFAULTMEMORYSIZE =100;
private List<SheetInfo> sheetInfos = null;
/**
* 导出数据逻辑处理
*/
public Workbook transformXLS(InputStream is, Map beanParams ,int memoryNum)throws ParsePropertyException {        Workbook workbook = null;
try{
//根据输⼊的excel模板，创建对于得workbook
workbook = ate(is);
if(workbook instanceof XSSFWorkbook){
//excel2007可导出百万级数据
workbook =transManyformWorkbook(workbook, beanParams ,memoryNum , null);
}else{
//此分⽀适⽤于excel2003，预计只能达到4W的数据量
transformWorkbook(workbook, beanParams);
}
}catch(IOException e){
<("deal excel fail!", e);
}
return workbook;
}
/**
* 导出数据逻辑处理
*/
public Workbook transformXLS(InputStream is, Map beanParams)throws ParsePropertyException {
ansformXLS(is, beanParams , DEFAULTMEMORYSIZE);
}
/
**
* 多次导出⼀个excel
* @author yyh 2020年3⽉16⽇下午6:56:47
* @param is
* @param beanParams
* @return
* @throws ParsePropertyException
* @throws InvalidFormatException
* @since 2.1
* @since 2.1
*/
public Workbook transformXLSByManyFQ(InputStream is, Map beanParams)throws ParsePropertyException, InvalidFormatException {
return transformXLSByManyFQ(is , beanParams , null);
}
/**
* 多次导出⼀个excel
* @author yyh 2020年3⽉16⽇下午6:56:47
* @param is
* @param beanParams
* @param sheetInfos
* @return
* @throws ParsePropertyException
* @throws InvalidFormatException
* @since 2.1
*/
public Workbook transformXLSByManyFQ(InputStream is, Map beanParams , List<SheetInfo> sheetInfos)throws ParsePropertyException, InvalidForm atException {
Workbook workbook = null;
try{
workbook = ate(is);
if(workbook instanceof XSSFWorkbook){
workbook =transManyformWorkbook(workbook, beanParams ,DEFAULTMEMORYSIZE , sheetInfos);
return workbook;
}
}catch(IOException e){
<("deal excel fail!", e);
}
return workbook;
}
/**
* ⽤于多次导出的下⼀次操作
* @param beanParams
* @param workbook
*/
public void next(Map beanParams , Workbook workbook){
dealWorkbook(workbook  , beanParams);
}
/**
* 多数据量处理代码
* @author yyh 2020年3⽉16⽇下午4:23:48
* @param workbook excel信息
* @param beanParams 数据量
* @param memoryNum 读取到内存的数量
* @return
* @since 2.1
*/
private Workbook transManyformWorkbook(Workbook workbook, Map beanParams ,int memoryNum , List<SheetInfo> sheetInfos){
if(sheetInfos == null || sheetInfos.size()==0){
this.sheetInfos =dealWorkbookSheet(workbook , beanParams);
}else{
this.sheetInfos = sheetInfos;
}
workbook =new SXSSFWorkbook((XSSFWorkbook)workbook , memoryNum);
dealWorkbook(workbook  , beanParams);
return workbook;
}
/**
* 处理excel
* @author yyh 2020年3⽉16⽇下午4:23:27
* @param workbook excel⽂件
* @param beanParams 数据信息
* @since 2.1
*/
private void dealWorkbook(Workbook workbook,  Map beanParams){
for(int i =0;i < sheetInfos.size();i++){
SheetInfo sheetInfo = (i);
if(sheetInfo == null || SheetNum()<0){
log.debug("sheet info error,so export fail!");
return;
}
Sheet sheet = SheetNum());
List<Map> datas =(List<Map>)((i).getDataAlias());
if(CollectionUtils.isEmpty(datas)){
log.debug("sheet data is empty, not export!");
return;
}
for(Map data : datas){
Row row = CursorAndMove());
for(int j =0;j < ColNames().size();j++){
Cell cell = ateCell(j);
cell.(ColNames().get(j))== null ?"": (ColNames().get(j))+"");
}
}
}
}
/**
* 处理excel的sheet
* @author yyh 2020年3⽉16⽇下午4:22:29
* @param workbook excel⽂件
* @param beanParams 参数
* @return
* @since 2.1
*/
private List<SheetInfo>dealWorkbookSheet(Workbook workbook, Map beanParams){
List<SheetInfo> sheetInfos =new ArrayList<SheetInfo>();
for(int i =0; i < NumberOfSheets();i++){
Sheet sheet = SheetAt(i);
SheetInfo sheetInfo =new SheetInfo();
sheetInfo.SheetName());
dealOldRowData(sheetInfo , sheet , beanParams);
Start()== OldEnd()){
continue;
}
sheetInfo.setSheetNum(i);
for(int j = Start();j <= OldEnd();j++){
Row row = Row(j);
}
sheetInfos.add(sheetInfo);
}
return sheetInfos;
}
/**
* 处理row数据和sheet信息
* @author yyh 2020年3⽉16⽇下午6:24:08
* @param sheetInfo 记录sheet信息
* @param sheet excel的sheet
* @param beanParams 数据
* @since 2.1
*/
private void dealOldRowData(SheetInfo sheetInfo, Sheet sheet , Map beanParams){
String replaceStr ="";
for(int i =1; i <= LastRowNum();i++){
Row row = Row(i);
//判断第⼀例是否符合为开始列
Cell cell = Cell(0);
String str ="";
if(cell != null && StringCellValue()!= null){
str = StringCellValue();

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。