Java Poi导出Excel

针对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（）的方式快很多，不明实际原因。
**结语：**如有大牛解惑不剩感激。同时写给哪些奋斗的人，共勉。希望对一些人有所帮助。