针对Poi导出excel场景一 单工作簿,单sheet页
一 业务背景。数据量35w左右,原样导出成 单excel文件,只有一个sheet页面。项目局限,poi 3.8jar 不能升级;Mybatis操作oracle 数据库。
二 实现思路。
1.使用SXSSF,而不使用XSSF或HSSF。
HSSF 导出行数太少,不够用。
XSSF 导出行数多,但内存消耗大。
从POI 3.8版本开始,提供了一种基于XSSF的低内存占用的API---- SXSSF
2.数据量大,使用多线程实现。
3.使用main方法方式测试。
三 项目
1.jar包+Myabtis配置文件创建
自己用到的jar包,可能有多余的,自行添加,下载链接如下。
链接:https://pan.baidu.com/s/1q3NTbxsJ5OlePc7pjj4gbA
提取码:ILYl
配置文件如下:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<!-- 配置数据库连接信息 -->
<dataSource type="POOLED">
<property name="driver"
value="oracle.jdbc.driver.OracleDriver" />
<!--如下是数据库的ip和端口,请根据自己实际情况修改,如果你是Mysql数据库,请自行修改链接类,jar和ip以及端口 -->
<property name="url"
value="jdbc:oracle:thin:@#.#.#.#:1521:orcl" />
<property name="username" value="qy_inte" />
<property name="password" value="inte" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 注册Co_ToExData_zfmxMapper.xml文件, xml位于excel这个包下,所以resource写成excel/Co_ToExData_zfmxMapper.xml。命名自行考虑-->
<mapper resource="excel/Co_ToExData_zfmxMapper.xml"/>
</mappers>
</configuration>
2.代码和mapper.xml 如下
mapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<!--可以根据自己的实际需要构建你想要的VO类,映射,以及sql语句书写 -->
<mapper namespace="excel.Co_ToExData_zfmxMapper">
<!--构建VO类,映射关系 -->
<resultMap type="excel.Co_ToExData_zfmx"
id="myExDataResultMap">
<result column="c_1" property="c_1" />
<result column="c_2" property="c_2" />
<result column="c_3" property="c_3" />
<result column="c_4" property="c_4" />
<result column="c_5" property="c_5" />
<result column="c_6" property="c_6" />
<result column="c_7" property="c_7" />
<result column="c_8" property="c_8" />
<result column="c_9" property="c_9" />
<result column="c_10" property="c_10" />
<result column="c_11" property="c_11" />
<result column="c_12" property="c_12" />
<result column="c_13" property="c_13" />
<result column="c_14" property="c_14" />
<result column="c_15" property="c_15" />
<result column="c_16" property="c_16" />
<result column="c_17" property="c_17" />
<result column="c_18" property="c_18" />
<result column="c_19" property="c_19" />
<result column="c_20" property="c_20" />
<result column="c_21" property="c_21" />
<result column="c_22" property="c_22" />
<result column="c_23" property="c_23" />
<result column="c_24" property="c_24" />
<result column="c_25" property="c_25" />
<result column="c_26" property="c_26" />
<result column="c_27" property="c_27" />
<result column="c_28" property="c_28" />
<result column="c_29" property="c_29" />
<result column="c_30" property="c_30" />
<result column="c_31" property="c_31" />
<result column="c_32" property="c_32" />
<result column="c_33" property="c_33" />
<result column="c_34" property="c_34" />
<result column="c_35" property="c_35" />
<result column="c_36" property="c_36" />
<result column="c_37" property="c_37" />
<result column="c_38" property="c_38" />
<result column="c_39" property="c_39" />
<result column="c_40" property="c_40" />
<result column="c_41" property="c_41" />
<result column="c_42" property="c_42" />
<result column="c_43" property="c_43" />
<result column="c_44" property="c_44" />
</resultMap>
<select id="selectZFTZByWorkflowno"
resultMap="myExDataResultMap" parameterType="map">
select c_1,
c_2,
c_3,
c_4,
c_5,
c_6,
c_7,
c_8,
c_9,
c_10,
c_11,
c_12,
c_13,
c_14,
c_15,
c_16,
c_17,
c_18,
c_19,
c_20,
c_21,
c_22,
c_23,
c_24,
c_25,
c_26,
c_27,
c_28,
c_29,
c_30,
c_31,
c_32,
c_33,
c_34,
c_35,
c_36,
c_37,
c_38,
c_39,
c_40,
c_41,
c_42,
c_43,
c_44
from
(select rownum rn, t.*
from (select *
from Co_ToExData_zfmx ctz
where
ctz.workflowno =#{workflowno}) t)
WHERE RN BETWEEN #{starNum} AND
#{endNum}
and workflowno = #{workflowno}
</select>
<select id="selectCountZFTZ" resultType="java.lang.Integer"
parameterType="java.lang.String">
select count(1) FROM Co_ToExData_zfmx ctz where
ctz.workflowno =#{workflowno}
</select>
</mapper>
vo类如下:
package excel;
public class Co_ToExData_zfmx {
//注,根据自己的实际情况调整,字段以及字段类型
private String c_1 ;
private String c_2 ;
private String c_3 ;
private String c_4 ;
private String c_5 ;
private String c_6 ;
private String c_7 ;
private String c_8 ;
private String c_9 ;
private String c_10;
private String c_11;
private String c_12;
private String c_13;
private String c_14;
private String c_15;
private String c_16;
private String c_17;
private String c_18;
private String c_19;
private String c_20;
private String c_21;
private String c_22;
private String c_23;
private String c_24;
private String c_25;
private String c_26;
private String c_27;
private String c_28;
private String c_29;
private String c_30;
private String c_31;
private String c_32;
private String c_33;
private String c_34;
private String c_35;
private String c_36;
private String c_37;
private String c_38;
private String c_39;
private String c_40;
private String c_41;
private String c_42;
private String c_43;
private String c_44;
//注,get,set方法此处省略,记得自己补充完整
}
实现类如下 ExcelListMore :
package excel;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.FutureTask;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;
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.apache.tools.zip.ZipEntry;//在 ant.jar 里 有对应的zip包
import org.apache.tools.zip.ZipOutputStream;
/**
* 多线程查库,list.addall(),再写入 excel 中的方式。
*/
public class ExcelListMore {
private static Logger logger = Logger.getLogger(ExcelListMore.class.getName());
// 思路
// 直接写出,单个 excel文件,1个 sheet页, 用 XLSX 方式写
// 多线程查库,最终结果整合,转为2维数组
// sheet页写入 单线程操作
// 压缩包
// 删除 生成的 excel文件
/**
* 导出Excel
*
* @param outPath 输出文件路径名(需要优化)
* @param session 自测数据库连接参数 最终形态需要删除
* @throws Exception
*/
public void exportExcel(String workflowno,SqlSession session) throws Exception {
// 装载文件
FileOutputStream outputStream = null;
// 创建一个工作簿
Workbook workbook = new SXSSFWorkbook();
// 真实数据条数,需要sql 查询 项目中的是mapper 形式获取
String selctCount = "excel.Co_ToExData_zfmxMapper.selectCountZFTZ";//映射sql的标识字符串
//查询总条数
int dataCount = (Integer) session.selectOne(selctCount, workflowno);
// 校验数据!!!
if (dataCount == 0) {
throw new Exception("通知单生成excel,无结果集!");
}
// excel写入 workbook工作簿,sizeData总数,workflowno 流水号, session 数据库连接 ;返回值 计划名
//session 最终需要删除
String planName = writeExcel(workbook, dataCount, workflowno,session);
// 设置输出路径和 输出文件名
// 填写值,日期填写
String outStr = "D:/";
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// 生成文件名称
String outName = planName + sdf.format(date) + ".xlsx";
// 输出路径
String outPath = outStr + outName;
try {
outputStream = new FileOutputStream(outPath);
// 写出excel
// 怎么查看sheet里的数据行
int tnum = workbook.getSheetAt(0).getPhysicalNumberOfRows();
System.out.println("获取总共多少有数据的行数!!!!!!!!!!!!!!!!!!" + tnum);
workbook.write(outputStream);
} catch (Exception e) {
logger.error("支付通知写出excel报错!", e);
} finally {
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
// 压缩方法,注意路径问题,需要调整
writeZip(outPath, outName);
// 需要关闭流之后删除
File deleteFile = new File(outPath);
deleteFile.delete();
}
/**
* 写入Excel数据
*
* @param workbook Workbook xlsx 样式
* @param dataCount 总数据量
* @param workflowno 流水号
* @param session
*/
public String writeExcel(Workbook workbook, int dataCount, String workflowno,SqlSession session) {
// 创建工作表
Sheet sheet = workbook.createSheet();
workbook.setSheetName(0, "支付通知");
// 创建表头 根据自己的实际情况调整
Row row = sheet.createRow(0);
String[] heads = { "交易序列号", "交易类别", "支付流水号", "受托人年金计划编码", "年金计划名称", "企业年金计划登记号", "企业编码", "企业名称", "组织机构代码",
"个人编码", "员工编号", "姓名", "证件类型", "证件号码", "社会保障号码", "未归属金额", "企业缴费归属比例", "企业缴费总个税金额", "支付类型", "支付方式",
"支付周期", "支付期数", "是否为最后一期", "支付起始月份", "本次支付金额", "支付总金额", "受益人姓名", "受益人证件类型", "受益人证件号码", "受益人社会保障号码",
"受益人开户银行", "受益人开户银行行号", "受益人开户银行所在省", "受益人开户银行所在市", "受益人账户户名", "受益人银行账号", "受益人支付金额", "应纳税额", "实际领取额",
"摘要", "非投资赎回金额", "受托人申请编号", "估值日期", "受益人开户网点" };
Cell cell = null;
for (int i = 0; i < heads.length; i++) {
cell = row.createCell(i);
cell.setCellValue(heads[i]);
}
// 假数据量!!!! 需要删除!!!!!
// dataCount = 100001;
// 初始化页大小
int pageSize = 0;
if (dataCount < 50000) {
pageSize = 25000;
} else if (dataCount < 200000) {
pageSize = 50000;
} else {
pageSize = 100000;
}
// 总页数
int pageCount = (dataCount + pageSize - 1) / pageSize;
// 开启多线程,但是需要有返回值,总的结果集!!!!
List<Co_ToExData_zfmx> exDatalist = new ArrayList<Co_ToExData_zfmx>();
// FutureTask的get方法是线程阻塞的,必须要等待线程都执行完成后,才能获取到结果
List<FutureTask> futureTaskList = new ArrayList<FutureTask>();
// 开始构建多线程
for (int page = 0; page < pageCount; page++) {
// 开始starNum,结束 endNum
int starNum = page * pageSize + 1;
// 结束endNum;dataCount 数据总数
int endNum = ((page + 1) * pageSize < dataCount) ? (page + 1) * pageSize : dataCount;
// Callable + FutureTask 实现多线程,返回值
MyCallable mainCall = new MyCallable(starNum, endNum, session, workflowno);
FutureTask<List<Co_ToExData_zfmx>> mainFuture = new FutureTask<List<Co_ToExData_zfmx>>(mainCall);
// 为后面获取返回值做准备
futureTaskList.add(mainFuture);
// 构建线程
Thread thread = new Thread(mainFuture, "下标为" + page + "的线程!");
thread.start();
}
for (FutureTask<?> futureTask : futureTaskList) {
// FutureTask的get方法是线程阻塞的,必须要等待线程都执行完成后,才能获取到结果
try {
List<Co_ToExData_zfmx> mainList = (List<Co_ToExData_zfmx>) futureTask.get();
exDatalist.addAll(mainList);
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ExecutionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 对象值转化成2维数组
String[][] tArr5 = listToArrayWay(exDatalist);
// 起始行 为 1!!!!!!!!!! 如何写活
writeToSheet(sheet, 1, tArr5);
// 返回计划名称,要考虑为空的情况么?
return exDatalist.get(0).getC_5();
}
// 压缩文件 outPath 文件路径
public void writeZip(String outPath, String zipname) {
// 压缩包名字
OutputStream outputStream = null;
// 装载文件
BufferedInputStream inputStream = null;
try {
outputStream = new BufferedOutputStream(new FileOutputStream("D:/" + zipname + ".zip"));
} catch (FileNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
ZipOutputStream zipOut = new ZipOutputStream(outputStream);
// 设置中文编码
zipOut.setEncoding("gbk");
try {
File file = new File(outPath); // 定义要压缩的文件
// 需要的是一个字符串名字
ZipEntry entry = new ZipEntry(file.getName());
zipOut.putNextEntry(entry);
inputStream = new BufferedInputStream(new FileInputStream(file));
int n;
byte[] buffer = new byte[1024 * 10];
while ((n = inputStream.read(buffer)) != -1) {
zipOut.write(buffer, 0, n);
}
zipOut.flush();
zipOut.closeEntry();
zipOut.close();
} catch (Exception e) {
logger.error("压缩文件出错!", e);
} finally {
if (zipOut != null)
try {
zipOut.close();
} catch (IOException e) {
e.printStackTrace();
}
if (outputStream != null)
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
if (inputStream != null)
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* @param sheet
* @param number 开始写入数据的下标行号
* @param tArr5 2维数组形式的数据源
* @return
*/
private void writeToSheet(Sheet sheet, int number, String[][] tArr5) {
Row row = null;
Cell cell = null;
int tRow = tArr5.length;// 多少行
int tCol = tArr5[0].length; // 多少列
// 行开始
for (int i = 0; i < tRow; i++) {
// 第二列值
row = sheet.createRow(i + number);
for (int j = 0; j < tCol; j++) {
cell = row.createCell(j);// 建立新cell
cell.setCellValue(tArr5[i][j]);
}
}
}
// 结果集转化为2维数组!!!
public String[][] listToArrayWay(List<Co_ToExData_zfmx> list) {
Object o = list.get(0);
String[] filedNames = getFiledName(o);
int filedNum = filedNames.length;
int listSize = list.size();
List<Method> methods = getGetField(filedNames, o);
String[][] arrs = new String[listSize][filedNum];
int i = 0;
for (Object object : list) {
int j = 0;
for (Method method : methods) {
Object value = null;
try {
value = method.invoke(object, new Object[] {});
} catch (Exception e) {
// TODO Auto-generated catch block
System.out.println("属性不存在" + e);
}
arrs[i][j] = (String) value;
j++;
}
i++;
}
return arrs;
}
// 获取obj属性 集合
private String[] getFiledName(Object o) {
try {
Field[] fields = o.getClass().getDeclaredFields();
String[] fieldNames = new String[fields.length];
for (int i = 0; i < fields.length; i++) {
fieldNames[i] = fields[i].getName();
// System.out.println("===i:"+i+"======="+fields[i].getName());
}
return fieldNames;
} catch (SecurityException e) {
e.printStackTrace();
System.out.println(e.toString());
}
return null;
}
// 获取属性 method 集合
private List<Method> getGetField(String[] fieldNames, Object o) {
List<Method> methods = new ArrayList<Method>();
for (String fieldName : fieldNames) {
String firstLetter = fieldName.substring(0, 1).toUpperCase();
String getter = "get" + firstLetter + fieldName.substring(1);
Method method = null;
try {
method = o.getClass().getMethod(getter, new Class[] {});
} catch (NoSuchMethodException e) {
System.out.println("属性不存在");
continue;
}
// Object value = method.invoke(o, new Object[] {});
methods.add(method);
}
return methods;
}
/***
* 线程体查库操作
*/
private static class MyCallable implements Callable<List<Co_ToExData_zfmx>> {
private Integer starNum;// 查询开始行
private Integer endNum;// 查询结束行
private SqlSession session;// 传的数据库连接 最终项目需要改动,是查库的连接
private String workflowno; // 工作流水号
@Override
public List<Co_ToExData_zfmx> call() throws Exception {
//就是一个查库 操作!!!
String selctList = "excel.Co_ToExData_zfmxMapper.selectZFTZByWorkflowno";//映射sql的标识字符串
Map<String,Object> parameters= new HashMap<String,Object>();
parameters.put("starNum",starNum);
parameters.put("endNum",endNum);
parameters.put("workflowno",workflowno);
List<Co_ToExData_zfmx> list = session.selectList(selctList, parameters);
// 查询结果存储在list内
// 返回查询结果
System.out.println("------------------观察是否是多线程查询,交错执行");
return list;
}
public MyCallable(Integer starNum, Integer endNum, SqlSession session, String workflowno) {
super();
this.starNum = starNum;
this.endNum = endNum;
this.session = session;
this.workflowno = workflowno;
}
}
//main入口
public static void main(String[] args) throws Exception {
//时间戳,开始时间
long start = System.currentTimeMillis();
//mybatis的配置文件
String resource = "conf.xml";
//使用类加载器加载mybatis的配置文件(它也加载关联的映射文件)
InputStream is = MybatisTest.class.getClassLoader().getResourceAsStream(resource);
//构建sqlSession的工厂
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
//创建能执行映射文件中sql的sqlSession
SqlSession session = sessionFactory.openSession();
// 为了查库 把session 传进去了
ExcelListMore exportMor= new ExcelListMore();
exportMor.exportExcel("test0005", session);
long end = System.currentTimeMillis();
long count = end - start;
System.out.println("总用时间" + count);
session.close();
}
}
三 总结和疑问
1.10w数据量写入,单线程操作和多线程查库再写入操作时间差别不大。效率基本一致。
2.单sheet页 多线程 查库+写入时,数据不能同步,受限代码
// 开始构建多线程
for (int page = 0; page < pageCount; page++) {
// 开始starNum,结束 endNum
int starNum = page * pageSize + 1;
// 结束endNum;dataCount 数据总数
int endNum = ((page + 1) * pageSize < dataCount) ? (page + 1) * pageSize : dataCount;
// Callable + FutureTask 实现多线程,返回值
MyCallable mainCall = new MyCallable(starNum, endNum, session, workflowno);
FutureTask<List<Co_ToExData_zfmx>> mainFuture = new FutureTask<List<Co_ToExData_zfmx>>(mainCall);
// 为后面获取返回值做准备
futureTaskList.add(mainFuture);
// 构建线程
Thread thread = new Thread(mainFuture, "下标为" + page + "的线程!");
thread.start();
}
原因是,for 内 new 了新类,再创建了线程类,导致锁不住。
故,变种为,多线程查库,结果集汇总,再单线程写入sheet页。
3.转为2维数组操作excel写入时,比vo.getName()的方式快很多,不明实际原因。
**结语:**如有大牛解惑不剩感激。同时写给哪些奋斗的人,共勉。希望对一些人有所帮助。