一、excel数据导出限制
1. Excel 2003及以下的版本。一张表最大支持**65536**行数据，256列。也就是说excel2003完全不可能满足百万数据导出的需求。
2. Excel 2007-2010版本。一张表最大支持**1048576**行，16384列；
3. 在针对超过1048576的数据量的数据导出的时候，就不能简单的导出到Excel的单个sheet页中了，必须得采用程序拆分的方式将数据存放到不同的sheet中，如下图所示：
在对一个sheet表中存储数据超过一定数据量的时候，将剩下的数据存放在一个新的sheet页中。
二、HSSFworkbook,XSSFworkbook,SXSSFworkbook区别
HSSFworkbook
是操作Excel2003以前（包括2003）的版本，扩展名是.xls；
poi导出excel最常用的方式；但是此种方式的局限就是导出的行数至多为65535行，超出65536条后系统就会报错。此方式因为行数不足七万行所以一般不会发生内存不足的情况（OOM）
XSSFWorkbook
是操作Excel2007后的版本，扩展名是.xlsx；
这种形式的出现是为了突破HSSFWorkbook的65535行局限。其对应的是excel2007(1048576行，16384列)扩展名为“.xlsx”，最多可以导出104万行，不过这样就伴随着一个问题---OOM内存溢出，原因是你所创建的book sheet row cell等此时是存在内存的并没有持久化。
SXSSFWorkbook
是操作Excel2007后的版本，扩展名是.xlsx；
从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的SXSSF方式。对于大型excel文件的创建，一个关键问题就是，要确保不会内存溢出。其实，就算生成很小的excel（比如几Mb），它用掉的内存是远大于excel文件实际的size的。如果单元格还有各种格式（比如，加粗，背景标红之类的），那它占用的内存就更多了。对于大型excel的创建且不会内存溢出的，就只有SXSSFWorkbook了。它的原理很简单，用硬盘空间换内存（就像hash map用空间换时间一样）。
SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到。
SXSSF与XSSF的对比：
1. 在一个时间点上，只可以访问一定数量的数据
2. 不再支持Sheet.clone()
3. 不再支持公式的求值
4. 在使用Excel模板下载数据时将不能动态改变表头，因为这种方式已经提前把excel写到硬盘的了就不能再改了
5. 针对于大数据量的情况下使用SXSSF导出比XSSF导出效率要高得多。
三、SXSSFWorkbook导出excel示例
1. 引入依赖
<!--poi-ooxml-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>2. 编写SxssfWorkbookUtil
package com.yuanian.common.utils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import java.util.List;
import java.util.Map;
/**
* @author zhahsh
* @date 2021/1/20
*/
public class SxssfWorkbookUtil {
public static XSSFCellStyle headCellStyle;
/**
* 设置单元格样式1
* @param sxssfWorkbook 工作簿
* @return 单元格样式
*/
public static XSSFCellStyle getAndSetXSSFCellStyleOne(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
XSSFDataFormat format = (XSSFDataFormat) sxssfWorkbook.createDataFormat();
// 是否自动换行
xssfCellStyle.setWrapText(false);
// 水平居中
xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 前景颜色
xssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
xssfCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
// 边框
xssfCellStyle.setBorderBottom(BorderStyle.THIN);
xssfCellStyle.setBorderRight(BorderStyle.THIN);
xssfCellStyle.setBorderTop(BorderStyle.THIN);
xssfCellStyle.setBorderLeft(BorderStyle.THIN);
xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
xssfCellStyle.setDataFormat(format.getFormat("0"));
return xssfCellStyle;
}
/**
* 设置单元格样式2
* @param sxssfWorkbook 工作簿
* @return 单元格样式
*/
public static XSSFCellStyle getAndSetXSSFCellStyleTwo(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
XSSFDataFormat format = (XSSFDataFormat) sxssfWorkbook.createDataFormat();
// 是否自动换行
xssfCellStyle.setWrapText(false);
// 水平居中
xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
// 边框
xssfCellStyle.setBorderBottom(BorderStyle.THIN);
xssfCellStyle.setBorderRight(BorderStyle.THIN);
xssfCellStyle.setBorderTop(BorderStyle.THIN);
xssfCellStyle.setBorderLeft(BorderStyle.THIN);
xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 垂直居中
xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
xssfCellStyle.setDataFormat(format.getFormat("0"));
return xssfCellStyle;
}
/**
* 获取并设置header样式
*/
public static XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle headCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
Font font = sxssfWorkbook.createFont();
// 字体大小
font.setFontHeightInPoints((short) 14);
// 字体粗细
font.setBold(true);
// 将字体应用到样式上面
headCellStyle.setFont(font);
return headCellStyle;
}
/**
* 根据sheet页和head内容填充sheet页头部
* @param sheet sheet页
* @param map 题头
*/
public static final void createTableHeader(Sheet sheet, Map<Integer, List<ExcelBean>> map) {
int startIndex = 0;//cell起始位置
int endIndex = 0;//cell终止位置
for (Map.Entry<Integer, List<ExcelBean>> entry : map.entrySet()) {
Row row = sheet.createRow(entry.getKey());
List<ExcelBean> excels = entry.getValue();
for (int x = 0; x < excels.size(); x++) {
//合并单元格
if (excels.get(x).getCols() > 1) {
if (x == 0) {
endIndex += excels.get(x).getCols() - 1;
CellRangeAddress range = new CellRangeAddress(0, 0, startIndex, endIndex);
sheet.addMergedRegion(range);
startIndex += excels.get(x).getCols();
} else {
endIndex += excels.get(x).getCols();
CellRangeAddress range = new CellRangeAddress(0, 0, startIndex, endIndex);
sheet.addMergedRegion(range);
startIndex += excels.get(x).getCols();
}
Cell cell = row.createCell(x);
cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容
if (excels.get(x).getCellStyle() != null) {
cell.getCellStyle().cloneStyleFrom(excels.get(x).getCellStyle());// 设置格式
}
cell.getCellStyle().cloneStyleFrom(headCellStyle);
} else {
Cell cell = row.createCell(x);
cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容
cell.setCellStyle(headCellStyle);
}
}
}
}
}3. 编写程序代码
while (rs.next()){
//当数据量超过1000000行的时候，重新生成一张sheet页，将剩余的数据网新的sheet页中写
if (rowIndex >= 1000000){
temp++;
Sheet sheet1 = workbook.createSheet("转换数据".concat(String.valueOf(temp)));
SxssfWorkbookUtil.createTableHeader(sheet1, map);
sheets.add(sheet1);
rowIndex = 1;
}
Row row = sheets.get(temp).createRow(rowIndex);
int width = 300;
for(int j=0;j<count;j++){
Cell cell = row.createCell(j);
String value = "";
if (ObjectUtil.isNotNull(rs.getObject(j+1))){
value = String.valueOf(rs.getObject(j+1));
}
if (j == count - 1){
switch (value) {
//0=未转换；1=转换成功；2=存在未转换数据
case "0":
value = "未转换";
break;
case "1":
value = "转换成功";
break;
case "2":
value = "存在未转换数据";
}
}
cell.setCellType(STRING);
cell.setCellValue(value);
// 获得最大列宽
if (!StringUtils.isNULL(value)) {
width = value.getBytes().length * 300;
cell.setCellStyle(fontStyle2);
}else {
cell.setCellStyle(fontStyle1);
}
// 还未设置，设置当前
if (widths.size() <= j) {
widths.add(width);
continue;
}
// 比原来大，更新数据
if (width > widths.get(j)) {
widths.set(j, width);
}
}
rowIndex++;
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
