说明：
前面我们介绍了使用xls或xlsx模板导出excel数据。但是当数据量比较大时,这样的方式就会特别慢。导出2万条(每条数据占11列)数据时,使用模板的方式,会大约耗时20几分钟。那要导出百万千万甚至更多条数据呢？这时我们可以使用poi的SXSSFWorkbook来导出。
POI3.8之前的版本不支持大数据量处理，如果数据过多则经常报OOM错误，有时候调整JVM大小效果也不是太好。POI3.8版本新出来了SXSSFWorkbook,可以支持大数据量的操作。
3.8版本的POI对excel的导出操作，一般只使用HSSFWorkbook以及SXSSFWorkbook，HSSFWorkbook用来处理较少的数据量，SXSSFWorkbook用来处理大数据量以及超大数据量的导出。
HSSFWorkbook、XSSFWorkbook、SXSSFWorkbook的区别:
◎HSSFWorkbook一般用于Excel2003版及更早版本(扩展名为.xls)的导出。
◎XSSFWorkbook一般用于Excel2007版(扩展名为.xlsx)的导出。
◎SXSSFWorkbook一般用于大数据量的导出。
注：HSSFWorkbook和XSSFWorkbook的Excel Sheet导出条数上限(<=2003版)是65535行、256列,(>=2007版)是
1048576行,16384列,如果数据量超过了此上限,那么可以使用SXSSFWorkbook来导出。实际上上万条数据，
甚至上千条数据就可以考虑使用SXSSFWorkbook了。
本人测试时软硬件环境:Windows10、idea、jdk1.8
准备工作：在pom.xml中引入依赖
<!--poi-ooxml-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
注:因为本人还需要连库查询,所以本人还引入了MySQL的相关依赖,并在application.properties中进行了相关的数据库连接
配置;这里就不再一一给出了。
SXSSFWorkbook导出excel示例
import com.mapper.DemoMapper;
import com.model.ExcelExportModel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
public class SxxfWorkbookApplicationTests {
private final static Logger logger = LoggerFactory.getLogger(SxxfWorkbookApplicationTests.class);
@Autowired
private DemoMapper mapper;
@Test
public void contextLoads() {
// 导出的excel,全文件名
final String excelExportDestfilepath = "C:/Users/JustryDeng/Desktop/abc.xlsx";
FileOutputStream fos = null;
SXSSFWorkbook sxssfWorkbook = null;
try {
/// -> 从数据库中查询出要进行excel导出的数据
String timeLeft = "2018-10-10 00:00:00";
String timeRight = "2018-10-11 00:00:00";
long startTime0 = System.currentTimeMillis();
List<ExcelExportModel> list = mapper.queryData(timeLeft, timeRight);
long endTime0 = System.currentTimeMillis();
logger.info("查询数据总耗时:{} 毫秒; list数量为 {}", endTime0 - startTime0, list.size());
/// -> excel到处逻辑
long startTime = System.currentTimeMillis();
// 获取SXSSFWorkbook实例
sxssfWorkbook = new SXSSFWorkbook();
Sheet sheet = sxssfWorkbook.createSheet("我是Sheet");
// 冻结最左边的两列、冻结最上面的一行
// 即：滚动横向滚动条时，左边的第一、二列固定不动;滚动纵向滚动条时，上面的第一行固定不动。
sheet.createFreezePane(2, 1);
// 设置并获取到需要的样式
XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(sxssfWorkbook);
XSSFCellStyle xssfCellStyleOne = getAndSetXSSFCellStyleOne(sxssfWorkbook);
XSSFCellStyle xssfCellStyleTwo = getAndSetXSSFCellStyleTwo(sxssfWorkbook);
// 创建第一行,作为header表头
Row header = sheet.createRow(0);
// 循环创建header单元格(根据实际情况灵活创建即可)
for (int cellnum = 0; cellnum < 11; cellnum++) {
Cell cell = header.createCell(cellnum);
cell.setCellStyle(xssfCellStyleHeader);
// 判断单元格
if (cellnum == 0) {
cell.setCellValue("通话ID");
} else if (cellnum == 1) {
cell.setCellValue("绑定关系ID");
} else if (cellnum == 2) {
cell.setCellValue("主叫号码");
} else if (cellnum == 3) {
cell.setCellValue("被叫号码");
} else if (cellnum == 4) {
cell.setCellValue("中间号码");
} else if (cellnum == 5) {
cell.setCellValue("通话发生时间");
} else if (cellnum == 6) {
cell.setCellValue("通话开始时间");
} else if (cellnum == 7) {
cell.setCellValue("通话结束时间");
} else if (cellnum == 8) {
cell.setCellValue("通话时长(秒)");
} else if (cellnum == 9) {
cell.setCellValue("结束发起方");
} else {
cell.setCellValue("结束状态（即挂断原因）");
}
}
// 遍历创建行,导出数据
for (int rownum = 1; rownum <= list.size(); rownum++) {
Row row = sheet.createRow(rownum);
// 循环创建单元格
for (int cellnum = 0; cellnum < 11; cellnum++) {
Cell cell = row.createCell(cellnum);
// 根据行数,设置该行内的单元格样式
if (rownum % 2 == 1) { // 奇数
cell.setCellStyle(xssfCellStyleOne);
} else { // 偶数
cell.setCellStyle(xssfCellStyleTwo);
}
// 根据单元格所属,录入相应内容
// 将部分数字类型的字符串,转换为Long;以免导出excel后,单元格左上角有三
// 角形(这是excel检查到该单元格内的内容均为数字,但是单元格类型却不是
// 数字,给出的提示),转不转看自己需求灵活处理
if (cellnum == 0) {
cell.setCellValue((list.get(rownum - 1).getCallId()));
} else if (cellnum == 1) {
cell.setCellValue(list.get(rownum - 1).getBindId());
} else if (cellnum == 2) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Long.parseLong(list.get(rownum - 1).getCallNo()));
} else if (cellnum == 3) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Long.parseLong(list.get(rownum - 1).getPeerNo()));
} else if (cellnum == 4) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Long.parseLong(list.get(rownum - 1).getTelX()));
} else if (cellnum == 5) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Long.parseLong(list.get(rownum - 1).getCallTime()));
} else if (cellnum == 6) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Long.parseLong(list.get(rownum - 1).getStartTime()));
} else if (cellnum == 7) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Long.parseLong(list.get(rownum - 1).getFinishTime()));
} else if (cellnum == 8) {
cell.setCellValue(list.get(rownum - 1).getCallDuration());
} else if (cellnum == 9) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Long.parseLong(list.get(rownum - 1).getFinishType()));
} else {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(Long.parseLong(list.get(rownum - 1).getFinishState()));
}
}
}
// 在后面设置sheet
setSheet(sheet);
fos = new FileOutputStream(excelExportDestfilepath);
sxssfWorkbook.write(fos);
long endTime = System.currentTimeMillis();
logger.info("数据全部导出至excel总耗时:{} 毫秒!", endTime - startTime, list.size());
} catch (Exception e) {
logger.error("发生异常咯！", e);
} finally {
try {
if(sxssfWorkbook != null) {
// dispose of temporary files backing this workbook on disk -> 处
// 理SXSSFWorkbook导出excel时，产生的临时文件
sxssfWorkbook.dispose();
}
if(fos != null) {
fos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 设置sheet
*/
private void setSheet(Sheet sheet) {
// 设置各列宽度(单位为:字符宽度的1/256)
sheet.setColumnWidth(0, 32 * 256);
sheet.setColumnWidth(1, 32 * 256);
sheet.setColumnWidth(2, 20 * 256);
sheet.setColumnWidth(3, 20 * 256);
sheet.setColumnWidth(4, 20 * 256);
sheet.setColumnWidth(5, 20 * 256);
sheet.setColumnWidth(6, 20 * 256);
sheet.setColumnWidth(7, 20 * 256);
sheet.setColumnWidth(8, 20 * 256);
sheet.setColumnWidth(9, 20 * 256);
sheet.setColumnWidth(10, 32 * 256);
}
/**
* 获取并设置header样式
*/
private XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
Font font = sxssfWorkbook.createFont();
// 字体大小
font.setFontHeightInPoints((short) 14);
// 字体粗细
font.setBoldweight((short) 20);
// 将字体应用到样式上面
xssfCellStyle.setFont(font);
// 是否自动换行
xssfCellStyle.setWrapText(false);
// 水平居中
xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return xssfCellStyle;
}
/**
* 获取并设置样式一
*/
private 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(XSSFCellStyle.SOLID_FOREGROUND);
xssfCellStyle.setFillForegroundColor(IndexedColors.AQUA.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;
}
/**
* 获取并设置样式二
*/
private 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;
}
}
小知识：列宽自适应的设置(示例)
注：当设置宽度自适应时，设置时机需要放在设置完cell样式后面进行，如果放在开头设置，那么可能不起作用；
设置宽度自适应时不能同时设置自动换行，否者也可能导致宽度自适应失效。
注：autoSizeColumn方法对中文支不不是很好，必要的话可自己写部分逻辑来实现宽度自适应，可参照着源码进行编写。
测试一下:运行测试方法,控制台输出一下信息
再去C:/Users/JustryDeng/Desktop/下,看见abc.xlsx文件确实生成了，打开该excel文件，比对数据,发现数据准确无误。
由此可见->成功！
注：如果目标位置已存在同名(含后缀名)文件,那么会覆盖原来的那个文件。
注：导出同样数量的数据,使用poi的SXSSFWorkbook最快,其次是使用poi的HSSFWorkbook(或XSSFWorkbook),使用
其他(对poi进行了封装的)框架,效率会比较低，如使用模板导出excel。
^_^ 参考链接
https://blog.csdn.net/qq_34869143/article/details/76512289
https://zhidao.baidu.com/question/508958518.html
http://happyqing.iteye.com/blog/2190225