package com.telehot.xzsp.stats.number.service;
import com.google.common.util.concurrent.ThreadFactoryBuilder;
import org.apache.commons.beanutils.PropertyUtilsBean;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import java.beans.PropertyDescriptor;
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.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadFactory;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
/**
* 版权:(C) 版权所有 2000-2019 上海天好电子商务股份有限公司苏州分公司
* <简述>
* <详细描述> POI 多线程多sheet导出数据
*
* @author lichaojie
* @version V1.0
* @see
* @since
*/
public class ThreadExcelUtils {
/**
* 定义每个 sheet 最多数量 5w条
*/
static final Integer EXCEL_MAX_CNT = 50000;
/**
* 工作薄
*/
private HSSFWorkbook wb;
/**
* 文件名
*/
private String fileName;
/**
* 路径
*/
private String filePath;
/**
* 标题名称
*/
private String[] headers;
/**
* 字段名称
*/
private String[] fields;
public HSSFWorkbook getWb() {
return wb;
}
public String getFileName() {
return fileName;
}
public String getFilePath() {
return filePath;
}
public String[] getFields() {
return fields;
}
/**
* @param fileName 文件名称
* @param filePath 文件路径
* @param headers 文件头
* @param fields 字段属性
*/
ThreadExcelUtils(String fileName, String filePath,
String[] headers, String[] fields) {
this.wb = new HSSFWorkbook();
this.fileName = fileName;
this.filePath = filePath;
this.headers = headers;
this.fields = fields;
}
public static void main(String[] args) throws Exception {
List<Map<String, Object>> lists = new ArrayList<Map<String, Object>>();
// 造数据
for (int i = 0; i < 10000; i++) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("name1", "张三" + i);
map.put("name2", "李四" + i);
map.put("name3", "王五" + i);
lists.add(map);
}
String[] header = {"姓名1", "姓名2", "姓名3"};
String[] fileNames = {"name1", "name2", "name3"};
String filePath = "E:\\open";
String filename = "测试表.xls";
ThreadExcelUtils utils = new ThreadExcelUtils(filename, filePath,
header, fileNames);
System.out.println("开始执行导出.......");
long start = System.currentTimeMillis();
utils.exportExcelToFilePath(lists);
long end = System.currentTimeMillis();
System.out.println("耗时:" + (end - start) / 1000 + "秒");
}
/**
* @param list 数据
*/
void exportExcelToFilePath(List<Map<String, Object>> list)
throws Exception {
// 每个Excel文件条数
int excelSize = EXCEL_MAX_CNT;
// 查询结果总条数
int totalCount = list.size();
// 总sheet页个数
int pageCount;
// 是否整页数
int numPage = totalCount % excelSize;
if (numPage > 0) {
pageCount = totalCount / excelSize + 1;
} else {
pageCount = totalCount / excelSize;
}
// 创建线程池 多sheet多线程写入 线程数 为sheet页的总数量
int threadNumber = pageCount;
if (threadNumber == 0) {
threadNumber = 1;
}
//定义线程的名字
ThreadFactory threadFactory = new ThreadFactoryBuilder().setNameFormat("gjjExcel-pool-%d").build();
//核心线程数,最大线程数,线程空闲时间,时间单位,任务队列容量,线程工厂
ExecutorService threadPool = new ThreadPoolExecutor(threadNumber, threadNumber,
0L, TimeUnit.MILLISECONDS,
new LinkedBlockingQueue<>(), threadFactory);
// 创建栅栏 等待任务完成
CountDownLatch countDownLatch = new CountDownLatch(pageCount);
// 循环遍历投递任务
for (int i = 1; i <= pageCount; i++) {
ThreadExcel threadExcel = new ThreadExcel(list, i, pageCount,
numPage, this);
threadExcel.setCountDownLatch(countDownLatch);
threadPool.execute(threadExcel);
}
//阻塞主线程,等所有的子线程执行完成
countDownLatch.await();
//下载到本地
// Workbook wb = getWb();
// File file = new File(filePath);
// FileOutputStream fout = new FileOutputStream(new File(file, fileName));
// try {
// wb.write(fout);
// } catch (IOException e) {
// e.printStackTrace();
// }
// 立即销毁线程池
threadPool.shutdownNow();
}
/**
* JavaBean转Map
*
* @param obj Object
* @return Map<String, Object>
*/
private static Map<String, Object> beanToMap(Object obj) {
Map<String, Object> params = new HashMap<String, Object>(0);
try {
PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
PropertyDescriptor[] descriptors = propertyUtilsBean
.getPropertyDescriptors(obj);
for (PropertyDescriptor descriptor : descriptors) {
String name = descriptor.getName();
if (!StringUtils.equals(name, "class")) {
params.put(name, propertyUtilsBean.getNestedProperty(obj,
name));
}
}
} catch (Exception e) {
System.err.println("bean 转Map出错");
e.printStackTrace();
}
return params;
}
/**
* 获取 标题名称
*
* @return headers 标题名称
*/
public String[] getHeaders() {
return this.headers;
}
/**
* 设置 标题名称
*
* @param headers 标题名称
*/
public void setHeaders(String[] headers) {
this.headers = headers;
}
/***
* 线程写入sheet
*/
private static class ThreadExcel implements Runnable {
/**
* 数据
*/
private List<Map<String, Object>> list;
/**
* 当前sheet页码
*/
private Integer sheetNumber;
/**
* 总数据
*/
private Integer totalSheetCount;
/**
* 是否整页数
*/
private int numPage;
/**
* excel sheet长度
*/
private Integer excelSize;
/**
* 工具类
*/
private ThreadExcelUtils threadExcelUtils;
/**
* 栅栏对象 计数器
*/
private CountDownLatch countDownLatch;
/**
* @param list 总数据
* @param sheetNumber 当前sheet页
* @param totalSheetCount 总sheet页
* @param numPage 是否整数
*/
ThreadExcel(List<Map<String, Object>> list, Integer sheetNumber,
Integer totalSheetCount, Integer numPage,
ThreadExcelUtils threadExcelUtils) {
this.list = list;// 总数据
this.sheetNumber = sheetNumber;// 当前sheet页
this.totalSheetCount = totalSheetCount;// 总sheet页
this.numPage = numPage;// 是否整除
this.excelSize = ThreadExcelUtils.EXCEL_MAX_CNT;// 没个sheet最大数量
this.threadExcelUtils = threadExcelUtils;// 当前线程对象
}
@Override
public void run() {
//链式编程
System.out.println(Thread.currentThread().getName() + "---执行中---");
List<Map<String, Object>> sheetList = null;
if (totalSheetCount > 1) {
// 是否整除
if (numPage == 0) {
sheetList = list.subList((sheetNumber - 1) * excelSize,
excelSize * sheetNumber);
} else {
if (sheetNumber.equals(totalSheetCount)) {
sheetList = list.subList((sheetNumber - 1) * excelSize,
list.size());
} else {
sheetList = list.subList((sheetNumber - 1) * excelSize,
excelSize * (sheetNumber));
}
}
} else {
sheetList = list;
}
// 开始写入数据
createWorkBook(sheetList);
if (this.countDownLatch != null) {
//计数器减1
this.countDownLatch.countDown();
}
}
/**
* 设置表头
*
* @param wb 工作薄
* @param row 行
*/
public void setTitle(HSSFWorkbook wb, HSSFRow row) {
//设置标题行高
row.setHeight((short) 750);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
// 创建一个居中格式
style.setAlignment(HorizontalAlignment.CENTER);
// 设置背景色
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 14);
//粗体显示
font.setBold(true);
style.setFont(font);
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
String[] header = threadExcelUtils.getHeaders();
// 设置标题
for (int i = 0; i < header.length; i++) {
row.createCell(i).setCellValue(header[i]);
row.getCell(i).setCellStyle(style);
}
}
/***
* 写出数据
*/
private void createWorkBook(List<Map<String, Object>> sheetList) {
HSSFSheet sheet = null;
HSSFRow row = null;
synchronized (ThreadExcelUtils.class) {
HSSFWorkbook wb = threadExcelUtils.getWb();
sheet = wb.createSheet("sheet" + this.sheetNumber);
// 默认列宽
sheet.setDefaultColumnWidth(20);
row = sheet.createRow(0);
// 设置标题
setTitle(wb, row);
}
String[] fields = threadExcelUtils.getFields();
// 开始写入数据
if (sheetList != null && sheetList.size() > 0) {
int dataLength = sheetList.size();
for (int i = 0; i < dataLength; i++) {
Row row1 = sheet.createRow(i + 1);
Object obj = sheetList.get(i);
Map<String, Object> map = (obj != null) ? (Map<String, Object>) obj : beanToMap(obj);
int length = fields.length;
for (int j = 0; j < length; j++) {
String key = fields[j];
Object value = map.get(key);
if (value != null) {
// 不晓得 此处为啥有线程安全问题
synchronized (ThreadExcelUtils.class) {
if (value instanceof Date) {
SimpleDateFormat sdf = new SimpleDateFormat(
"yyyy-MM-dd");
String format = sdf.format(value);
row1.createCell(j).setCellValue(format);
} else {
try {
row1.createCell(j).setCellValue(
value.toString());
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
}
}
}
/**
* 获取 数据
*
* @return list 数据
*/
public List<Map<String, Object>> getList() {
return this.list;
}
/**
* 设置 数据
*
* @param list 数据
*/
public void setList(List<Map<String, Object>> list) {
this.list = list;
}
/**
* 获取 当前sheet页码
*
* @return sheetNumber 当前sheet页码
*/
public Integer getSheetNumber() {
return this.sheetNumber;
}
/**
* 设置 当前sheet页码
*
* @param sheetNumber 当前sheet页码
*/
public void setSheetNumber(Integer sheetNumber) {
this.sheetNumber = sheetNumber;
}
/**
* 获取 总数据
*
* @return totalSheetCount 总数据
*/
public Integer getTotalSheetCount() {
return this.totalSheetCount;
}
/**
* 设置 总数据
*
* @param totalSheetCount 总数据
*/
public void setTotalSheetCount(Integer totalSheetCount) {
this.totalSheetCount = totalSheetCount;
}
/**
* 获取 是否整页数
*
* @return numPage 是否整页数
*/
public int getNumPage() {
return this.numPage;
}
/**
* 设置 是否整页数
*
* @param numPage 是否整页数
*/
public void setNumPage(int numPage) {
this.numPage = numPage;
}
/**
* 获取 excel sheet长度
*
* @return excelSize excel sheet长度
*/
public Integer getExcelSize() {
return this.excelSize;
}
/**
* 设置 excel sheet长度
*
* @param excelSize excel sheet长度
*/
public void setExcelSize(Integer excelSize) {
this.excelSize = excelSize;
}
/**
* 获取 工具类
*
* @return threadExcelUtils 工具类
*/
public ThreadExcelUtils getThreadExcelUtils() {
return this.threadExcelUtils;
}
/**
* 设置 工具类
*
* @param threadExcelUtils 工具类
*/
public void setThreadExcelUtils(ThreadExcelUtils threadExcelUtils) {
this.threadExcelUtils = threadExcelUtils;
}
/**
* 获取 栅栏对象
*
* @return countDownLatch 栅栏对象
*/
public CountDownLatch getCountDownLatch() {
return this.countDownLatch;
}
/**
* 设置 栅栏对象
*
* @param countDownLatch 栅栏对象
*/
void setCountDownLatch(CountDownLatch countDownLatch) {
this.countDownLatch = countDownLatch;
}
}
}
一个sheet页一个线程导出,可支持百万级数据量导出。自己测试五十多万数据从查询数据库到导出大概用了47秒。