Java--使用POI进行Excel多线程写操作的demo

偏执的太偏执、 2022-05-31 06:36 819阅读 0赞

最近项目中要使用 [POI][] 进行Excel大数据量写入的功能，以下是alex写的一个简单demo。其中，使用了ThreadPoolExecutor、CountDownLatch。由于POI的sheet中使用TreeMap存储行数据集，所以对Sheet的行创建是非线程安全的，所以进行了同步操作。

package com.alex.frameworkDemo.poi;
    
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.concurrent.CountDownLatch;
    import java.util.concurrent.ExecutorService;
    import java.util.concurrent.Executors;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    
    /**
     * 测试POI
     * @author alex
     *
     */
    public class PoiTest {
    
    	public static void main(String[] args) {
    		multiThreadWrite();
    	}
    	
    	/**
    	 * 使用多线程进行Excel写操作，提高写入效率。
    	 */
    	public static void multiThreadWrite() {
    		/**
    		 * 使用线程池进行线程管理。
    		 */
    		ExecutorService es = Executors.newCachedThreadPool();
    		/**
    		 * 使用计数栅栏
    		 */
    		CountDownLatch doneSignal = new CountDownLatch(3);
    
    		HSSFWorkbook wb;
    		try {
    			wb = new HSSFWorkbook(new FileInputStream("E:\\temp\\poiTest.xls"));
    			HSSFSheet sheet = wb.getSheetAt(0);
    			es.submit(new PoiWriter(doneSignal, sheet, 0, 19999));
    			es.submit(new PoiWriter(doneSignal, sheet, 20000, 39999));
    			es.submit(new PoiWriter(doneSignal, sheet, 40000, 59999));
    			/**
    			 * 使用CountDownLatch的await方法，等待所有线程完成sheet操作
    			 */
    			doneSignal.await();
    			es.shutdown();
    			FileOutputStream os = new FileOutputStream("E:\\temp\\poiTest.xls");
    			wb.write(os);
    			os.flush();
    			os.close();
    			System.out.println("Excel completed......");
    		} catch (FileNotFoundException e) {
    			e.printStackTrace();
    		} catch (IOException e) {
    			e.printStackTrace();
    		} catch (InterruptedException e) {
    			e.printStackTrace();
    		}
    	}
    	
    	/**
    	 * 测试基本的POI写操作
    	 */
    	public static void poiBasicWriteTest() {
    		try {
    			HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(
    					"E:\\temp\\poiTest.xls"));
    			HSSFSheet sheet = wb.getSheetAt(0);
    			HSSFRow row = sheet.createRow(0);
    			HSSFCell contentCell = row.createCell(0);
    			contentCell.setCellValue("abc");
    			FileOutputStream os = new FileOutputStream("E:\\temp\\poiTest.xls");
    			wb.write(os);
    			os.flush();
    		} catch (FileNotFoundException e) {
    			e.printStackTrace();
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    	}
    	
    	/**
    	 * sheet的row使用treeMap存储的，是非线程安全的，所以在创建row时需要进行同步操作。
    	 * @param sheet
    	 * @param rownum
    	 * @return
    	 */
    	private static synchronized HSSFRow getRow(HSSFSheet sheet, int rownum) {
    		return sheet.createRow(rownum);
    	}
    	
    	/**
    	 * 进行sheet写操作的sheet。
    	 * @author alex
    	 *
    	 */
    	protected static class PoiWriter implements Runnable {
    
    		private final CountDownLatch doneSignal;
    
    		private HSSFSheet sheet;
    
    		private int start;
    
    		private int end;
    
    		public PoiWriter(CountDownLatch doneSignal, HSSFSheet sheet, int start,
    				int end) {
    			this.doneSignal = doneSignal;
    			this.sheet = sheet;
    			this.start = start;
    			this.end = end;
    		}
    
    		public void run() {
    			int i = start;
    			try {
    				while (i <= end) {
    					HSSFRow row = getRow(sheet, i);
    					HSSFCell contentCell = row.getCell(0);
    					if (contentCell == null) {
    						contentCell = row.createCell(0);
    					}
    					contentCell.setCellValue(i + 1);
    					++i;
    				}
    			} catch (Exception e) {
    				e.printStackTrace();
    			} finally {
    				doneSignal.countDown();
    				System.out.println("start: " + start + " end: " + end
    						+ " Count: " + doneSignal.getCount());
    			}
    		}
    
    	}
    }

[POI]: http://poi.apache.org/