POI 多线程多sheet导出数据

拼搏现实的明天。 2023-06-11 12:24 35阅读 0赞

import org.apache.commons.beanutils.PropertyUtilsBean;
    import org.apache.commons.lang.StringUtils;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    
    import java.beans.PropertyDescriptor;
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.text.SimpleDateFormat;
    import java.util.*;
    import java.util.concurrent.CountDownLatch;
    import java.util.concurrent.ExecutorService;
    import java.util.concurrent.Executors;
    
    /*** * POI 多线程多sheet导出数据 */
    public class ThreadExcelUtils { 
    
    	/** * 定义每个 sheet 最多数量 */
    	public static final Integer EXCEL_MAX_CNT = 50000;
    
    	private Workbook wb;
    	private String fileName;
    	private String filePath;
    	private String[] hearders;
    	private String[] fields;
    
    	public Workbook getWb() { 
    		return wb;
    	}
    
    	public String getFileName() { 
    		return fileName;
    	}
    
    	public String getFilePath() { 
    		return filePath;
    	}
    
    	public String[] getHearders() { 
    		return hearders;
    	}
    
    	public String[] getFields() { 
    		return fields;
    	}
    
    	/** * @param fileName * 文件名称 * @param filePath * 文件路径 * @param hearders * 文件头 * @param fields * 字段属性 */
    	public ThreadExcelUtils(String fileName, String filePath,
    			String[] hearders, String[] fields) { 
    		this.wb = new HSSFWorkbook();
    		this.fileName = fileName;
    		this.filePath = filePath;
    		this.hearders = hearders;
    		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 = "D:\\excelFile";
    		String filename = "测试表 " + getDate(new Date()) + ".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 * 数据 */
    	public void exportExcelToFilePath(List<Map<String, Object>> list)
    			throws Exception { 
    		int excelSize = EXCEL_MAX_CNT; // 每个Excel文件条数
    		int totalCount = list.size(); // 查询结果总条数
    		int pageCount = 0;// 总sheet页个数
    		int numPage = totalCount % excelSize; // 是否整页数
    		if (numPage > 0)
    			pageCount = totalCount / excelSize + 1;
    		else
    			pageCount = totalCount / excelSize;
    		// 创建线程池 多sheet多线程写入 线程数 为sheet页的 1/4
    		Integer threadNumber = pageCount / 4;
    		if (threadNumber == 0)
    			threadNumber = 1;
    		ExecutorService threadPool = Executors.newFixedThreadPool(threadNumber);
    		// 创建栅栏 等待任务完成
    		CountDownLatch countDownLatch = new CountDownLatch(pageCount);
    		// 循环遍历投递任务
    		for (int i = 1; i <= pageCount; i++) { 
    			ThraedExcel thraedExcel = new ThraedExcel(list, i, pageCount,
    					numPage, this);
    			thraedExcel.setCountDownLatch(countDownLatch);
    			threadPool.execute(thraedExcel);
    		}
    		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();
    		}
    		// System.out.println("文件写入完成");
    		// 立即销毁线程池
    		threadPool.shutdownNow();
    	}
    
    	private static String getDate(Date date) { 
    		SimpleDateFormat simpleDateFormat = new SimpleDateFormat(
    				"yyyyMMddHHmmss");
    		String format = simpleDateFormat.format(date);
    		return format;
    	}
    
    	/** * JavaBean转Map * * @param obj * @return */
    	public 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);
    			int length = descriptors.length;
    			for (int i = 0; i < length; i++) { 
    				String name = descriptors[i].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;
    	}
    
    	/*** * 线程写入sheet */
    	private static class ThraedExcel implements Runnable { 
    
    		private List<Map<String, Object>> list;// 数据
    		private Integer sheetNumber;// 当前sheet页
    		private Integer totalSheetCount;// 总数据
    		private int numPage; // 是否整页数
    		private Integer excelSize;
    		private ThreadExcelUtils threadExcelUtils;
    		// 栅栏对象
    		private CountDownLatch countDownLatch;
    
    		/** * @param list * 总数据 * @param sheetNumber * 当前sheet页 * @param totalSheetCount * 总sheet页 * @param numPage * 是否整数 */
    		public ThraedExcel(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;// 当前线程对象
    		}
    
    		public void setCountDownLatch(CountDownLatch countDownLatch) { 
    			this.countDownLatch = countDownLatch;
    		}
    
    		@Override
    		public void run() { 
    			List<Map<String, Object>> sheetList = null;
    			if (totalSheetCount > 1) { 
    				if (numPage == 0) { 
    					sheetList = list.subList((sheetNumber - 1) * excelSize,
    							excelSize * sheetNumber);
    				} else { 
    					if (sheetNumber == 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)
    				this.countDownLatch.countDown();
    		}
    
    		/*** * 写出数据 */
    		private void createWorkBook(List<Map<String, Object>> sheetList) { 
    			Sheet sheet = null;
    			Row row = null;
    			synchronized (ThreadExcelUtils.class) { 
    				String fileName = threadExcelUtils.getFileName();
    				Workbook wb = threadExcelUtils.getWb();
    				sheet = wb.createSheet(fileName + "_" + this.sheetNumber);
    				row = sheet.createRow(0);
    			}
    			String[] header = threadExcelUtils.getHearders();
    			String[] fields = threadExcelUtils.getFields();
    			// 设置标题
    			for (int i = 0; i < header.length; i++) { 
    				row.createCell(i).setCellValue(header[i]);
    			}
    			// 开始写入数据
    			if (sheetList != null && sheetList.size() > 0) { 
    				int dataLength = sheetList.size();
    				for (int i = 0; i < dataLength; i++) { 
    					if (i == 3 || i == 8 || i == 15) { 
    						sheet.setColumnWidth(i, 20 * 256);
    					} else { 
    						sheet.setColumnWidth(i, 15 * 256);
    					}
    
    					Row row1 = sheet.createRow(i + 1);
    					Object obj = sheetList.get(i);
    					Map<String, Object> map = (obj instanceof Map) ? (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();
    									}
    								}
    							}
    						}
    					}
    				}
    			}
    		}
    	}
    }