相关文章推荐
绅士的斑马  ·  【QT】子类化QThread实现多线程 - ...·  昨天    · 
踢足球的洋葱  ·  全网首发！Kaggle操作完整指南！ - 墨天轮·  1 月前    · 
无邪的钢笔  ·  西藏和平解放与繁荣发展_西藏自治区人民政府·  3 月前    · 
朝气蓬勃的沙滩裤  ·  居延汉简：汉代边军的生活实录_地区_烽火_河西·  9 月前    · 
月球上的红金鱼  ·  广佛高速免费通行了，下一个会是谁？·  1 年前    · 
奔跑的猴子  ·  薛沫沫重生归来成为总裁小甜心，看她虐渣、撕婊 ...·  1 年前    · 
Code  ›  多线程读写大量数据到excel_多线程写excel_不要做发抖的小喵喵的博客
date string 线程阻塞 多线程
https://blog.csdn.net/qq_22899021/article/details/94015787
爱听歌的抽屉
1 年前

↵起因是这样的，用户要下载对账单明细，也就是交易明细，我们公司的数据库的设计是，一天一张表，类似于trace_20190708,trace_20190709  .......  这样的类型，所以设计生产者的时候是，一个线程负责某天的表，线程数不要超过查询的天数总和（例如读取20190720-20190730 这十天的数据，那么线程数不能超过10个，也不能过多，看自己系统内存情况），否则，会有线程被一直阻塞。

注 ：其实也可以多线程，读同一张表，这里提供下思路，一般是分页读取，我们可以a线程读取（select * from user limit 0,10  下面就直接用limit x，y代表了）limit 2n,100   ,B线程读取limit 2n+1,100 这样写设计sql读取，这样设计的目的是，不用写锁，若是多个线程用同一个 limit n，100，这样设计，那么会用并发现象，那么就要加锁，比如我在读limit 10,20  ，那么你要读limt30,20 你要等我读完，但是我多线程就是为了读取速度快，并发执行，加锁等待了，多线程的意义何在，所以在sql 上做些文章就好。 

package com.example.demo.test;
 * @Author: myc
 * @Description:
 * @Date 下午 4:51 2019/6/26 0026
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
 * 测试POI
 * @author alex
public class PoiTest {
    public static void main(String[] args) {
         * 使用线程池进行线程管理。
        ExecutorService es = Executors.newFixedThreadPool(20);
         * 使用计数栅栏
        CountDownLatch doneSignal = new CountDownLatch(1);
        String xlsFile = "d:/xlsx/poiSXXFSBigData" + "2019010" + ".xlsx";
        PoiExcel.multiThreadWrite(es, doneSignal, xlsFile);
        es.shutdown();

读写excel ,这里采用了生产者-消费者模式，将读数据库的数据，与写入excel，两个步骤分离，采用了blockqueue。考虑到一个excel的工作簿数据的容量大概是100W行，所以将其分多个工作簿，处理，这样就可以读写更多数据。但是有个问题是，多线程下，读写的并发问题，在多个消费者的情况下，会有些问题，所以建议开一个消费者就好。
 

sheet.setRandomAccessWindowSize(windowSize);注意这个设置，这个设置是将数据读到内存中windowSize个，超过这个数后就写入到磁盘零时文件中。若是设置为 -1，那么数据会全部读取到内存中，这样数据过多内存会被撑爆
 

package com.example.demo.test;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.SheetUtil;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicInteger;
 * @Author: myc
 * @Description: 用Poi读写大量数据
 * @Date 下午 2:12 2019/6/28 0028
public class PoiExcel {
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
    protected static volatile SXSSFWorkbook wb = new SXSSFWorkbook(100);
     * 总行号
    protected static AtomicInteger rowNo = new AtomicInteger(0);
     * 页行号
    protected static AtomicInteger pageRowNo = new AtomicInteger(0);
    protected static AtomicInteger count = new AtomicInteger(0);
    public static BlockingQueue<List<PoiEntity>> queue = new ArrayBlockingQueue(5);
    public static BlockingQueue<String> sqlQueue = new LinkedBlockingQueue();
     * excel 每个sheet大小
    protected static final int MAX_SHEET_PAGE_SIZE = 200000;
     * 查询总记录数
    protected static int TOTAL_COUNT;
     * 判断CountDownLatch，当所有线程运行结束，就写入到文件流中
    protected static AtomicInteger DONESIGNAL_COUNT;
     * 表名数组
    protected static List<String> tableNameArr = new ArrayList<>();
     * 读取数据库中存储的数据行数
    public static int PAGE_SIZE = 50000;
     * 工作表对象
    protected static volatile SXSSFSheet sheet;
     * excel工作簿数组
    protected static ArrayList<SXSSFSheet> sheetList = new ArrayList<>();
     * cell列数
    private static final int cellLength = 10;
    private static final int windowSize = 200;
    public void customerData(CountDownLatch doneSignal) throws Exception {
        List list = queue.take();
        int len = list.size();
        for (int i = 0; i < len; i++) {
            Row row_value = null;
            synchronized (rowNo) {
                if (rowNo.get() % MAX_SHEET_PAGE_SIZE == 0) {
                    if (count.get() < sheetList.size()) {
                        sheet = sheetList.get(count.get());
                        pageRowNo.set(0);        //每当新建了工作表就将当前工作表的行号重置为0
                        createHeader();
                        setColumnWidthByType(sheet, cellLength);
                        count.incrementAndGet();
                row_value = sheet.createRow(pageRowNo.incrementAndGet());
                rowNo.incrementAndGet();
            addToRow(i, row_value, list);
        System.out.println("sheet name " + sheet.getSheetName() + " rowNo " + rowNo + " DONESIGNAL_COUNT " + DONESIGNAL_COUNT);
        DONESIGNAL_COUNT.getAndAdd(-list.size());
        list.clear();
        if (DONESIGNAL_COUNT.get() == 0) {
            doneSignal.countDown();
    protected void addToRow(int i, Row row_value, List _list) {
        List<PoiEntity> list = _list;
        Cell cel0_value = row_value.createCell(0);
        cel0_value.setCellValue(list.get(i).getTraceNo());
        Cell cel2_value = row_value.createCell(1);
        cel2_value.setCellValue(list.get(i).getMerchantNum());
        Cell cel3_value = row_value.createCell(2);
        cel3_value.setCellValue(list.get(i).getTotalFee() + "");
        Cell cel4_value = row_value.createCell(3);
        cel4_value.setCellValue(list.get(i).getMerchantName());
        Cell cel5_value = row_value.createCell(4);
        cel5_value.setCellValue(list.get(i).getDynamicType());
        Cell cel6_value = row_value.createCell(5);
        cel6_value.setCellValue(list.get(i).getTransBegin());
        Cell cel7_value = row_value.createCell(6);
        cel7_value.setCellValue(list.get(i).getTransStatus());
        Cell cel8_value = row_value.createCell(7);
        cel8_value.setCellValue(list.get(i).getRefundFee() + "");
        Cell cel9_value = row_value.createCell(8);
        cel9_value.setCellValue(list.get(i).getOutTransNo());
        Cell cel10_value = row_value.createCell(9);
        cel10_value.setCellValue(list.get(i).getRateFee());
        //Cell cel11_value = row_value.createCell(10);
        // cel11_value.setCellValue(list.get(i).getBankType());
          /*   Cell cel12_value = row_value.createCell(11);
        cel2_value.setCellValue(list.get(i).getTerminalNum());*/
     * 定义表头
    protected void createHeader() {
        Row row = sheet.createRow(0);
        Cell cel0 = row.createCell(0);
        cel0.setCellValue("traceNo");
        Cell cel2 = row.createCell(1);
        cel2.setCellValue("merchantNum");
        Cell cel3 = row.createCell(2);
        cel3.setCellValue("totalFee");
        Cell cel4 = row.createCell(3);
        cel4.setCellValue("merchantName");
        Cell cel5 = row.createCell(4);
        cel5.setCellValue("dynamicType");
        Cell cel6 = row.createCell(5);
        cel6.setCellValue(" transBegin ");
        Cell cel7 = row.createCell(6);
        cel7.setCellValue("transStatus");
        Cell cel8 = row.createCell(7);
        cel8.setCellValue("refundFee");
        Cell cel9 = row.createCell(8);
        cel9.setCellValue("outTransNo");
        Cell cel10 = row.createCell(9);
        cel10.setCellValue("rateFee");
        //Cell cel11 = row.createCell(10);
        // cel11.setCellValue("bankType");
         /*   Cell cel2 = row.createCell(11);
        cel2.setCellValue("terminalNum");*/
    protected static int getListCount(List tableNameArr) throws Exception {
        PreparedStatement stmt = DbUtils.getStm();
        int listCount = 0;
        for (int i = 0; i < tableNameArr.size(); i++) {
            String queryCount = "select count(1) from  " + tableNameArr.get(i);
            ResultSet rsTotal = stmt.executeQuery(queryCount);
            rsTotal.next();
            listCount += rsTotal.getInt(1);
        return listCount;
     * 生成工作簿
     * @param sheet
    protected static void setSheet(SXSSFSheet sheet) {
        int size = 1;
        if (TOTAL_COUNT > MAX_SHEET_PAGE_SIZE) {
            size = TOTAL_COUNT / MAX_SHEET_PAGE_SIZE == 0 ? TOTAL_COUNT / MAX_SHEET_PAGE_SIZE : (TOTAL_COUNT / MAX_SHEET_PAGE_SIZE) + 1;
        for (int i = 0; i < size; i++) {
            /**建立新的sheet对象*/
            sheet = wb.createSheet("我的第" + i + "个工作簿");
            sheet.setRandomAccessWindowSize(windowSize);
            /**动态指定当前的工作表*/
            sheet = wb.getSheetAt(i);
            sheetList.add(sheet);
     * 根据类型指定excel文件的列宽
    private void setColumnWidthByType(SXSSFSheet sheet, int titleLength) {
        sheet.trackAllColumnsForAutoSizing();
        for (int i = 0; i < titleLength; i++) {
            int columnWidth = sheet.getRow(0).getCell(i).getStringCellValue().length();//获取表头的宽度
            int autowidth = (int) SheetUtil.getColumnWidth(sheet, i, false, 1, sheet.getLastRowNum());
            if (columnWidth > autowidth) {
                sheet.setColumnWidth(i, (int) 400.0D * (columnWidth + 1));
            } else {
                sheet.autoSizeColumn(i);
     * 添加表名
     * @param dateList
    private static void addTable(List<String> dateList) {
        dateList.forEach(date -> {
            /**兴业*/
            tableNameArr.add("xingye_bill_download_day_101590267206_" + date);
            tableNameArr.add("xingye_bill_download_day_101540080217_" + date);
            /**汇付*/
            tableNameArr.add("posp_huifu_detail_day_" + date);
     * 添加sql
     * @param dateList
     * @return
    private static List<String> getSqlList(List<String> dateList, String where) {
        List<String> listSql = new ArrayList<>();
        dateList.forEach(date -> {
            /**兴业*/
            String sql1 = "select id,merchant_num merchantNum,merchant_name merchantName,trans_type dynamicType,trans_time transBegin" +
                    "                ,trans_status transStatus,total_fee totalFee,refund_fee refundFee,third_merchant_num outTransNo," +
                    "                rate_fee rateFee ,trace_num traceNo from  xingye_bill_download_day_101590267206_" + date
                    + " WHERE  id >  ownId   " + where + "  ORDER BY id ASC  LIMIT  " + PAGE_SIZE;
            String sql2 = "select id,merchant_num merchantNum,merchant_name merchantName,trans_type dynamicType,trans_time transBegin" +
                    "                ,trans_status transStatus,total_fee totalFee,refund_fee refundFee,third_merchant_num outTransNo," +
                    "                rate_fee rateFee ,trace_num traceNo from  xingye_bill_download_day_101540080217_" + date
                    + " WHERE  id >   ownId   " + where + "   ORDER BY id ASC  LIMIT  " + PAGE_SIZE;
            /**汇付*/
            String sql3 = "select id,merchant_num merchantNum,merchant_name merchantName ,trade_type dynamicType,DATE_FORMAT(trade_date,'%Y-%m-%d-%H-%i-%s') transBegin " +
                    "                ,trade_status transStatus,total_fee totalFee,recorded_money refundFee,outside_order_num outTransNo, " +
                    "                rate_fee rateFee ,trace_num traceNo  from  posp_huifu_detail_day_" + date
                    + " WHERE  id >   ownId   " + where + "   ORDER BY id ASC  LIMIT  " + PAGE_SIZE;
            listSql.add(sql1);
            listSql.add(sql2);
            listSql.add(sql3);
        return listSql;
     * 得到两个日期之间的天数，数组
     * @param dBegin
     * @param dEnd
     * @return
    public static List<String> findDates(Date dBegin, Date dEnd) {
        List lDate = new ArrayList();
        lDate.add(sdf.format(dBegin));
        Calendar calBegin = Calendar.getInstance();
        // 使用给定的 Date 设置此 Calendar 的时间
        calBegin.setTime(dBegin);
        Calendar calEnd = Calendar.getInstance();
        // 使用给定的 Date 设置此 Calendar 的时间
        calEnd.setTime(dEnd);
        // 测试此日期是否在指定日期之后
        while (dEnd.after(calBegin.getTime())) {
            // 根据日历的规则，为给定的日历字段添加或减去指定的时间量
            calBegin.add(Calendar.DAY_OF_MONTH, 1);
            lDate.add(sdf.format(calBegin.getTime()));
        return lDate;
     * 使用多线程进行Excel写操作，提高写入效率。
    public static void multiThreadWrite(ExecutorService es, CountDownLatch doneSignal, String xlsFile) {
        try {
            /**预生产数据*/
            long startTime = System.currentTimeMillis();    //开始时间
            Date begin = sdf.parse("20190602");
            Date end = sdf.parse("20190602");
            List<String> dateList = findDates(begin, end);
            addTable(dateList);
            TOTAL_COUNT = getListCount(tableNameArr);
            DONESIGNAL_COUNT = new AtomicInteger(TOTAL_COUNT);
            setSheet(sheet);
            sheet = sheetList.get(0);
            String where = "";
            getSqlList(dateList, where).forEach(sql -> {
                try {
                    sqlQueue.put(sql);
                } catch (InterruptedException e) {
                    e.printStackTrace();
            /**多线程处理数据*/
            int size = sqlQueue.size();
            /**最多创建10个线程，确保线程不在开始时就被阻塞*/
            if (size > 0) {
                size = size > 12 ? 10 : size;
                for (int i = 0; i < size; i++) {
                    es.submit(new PoiProductor(doneSignal));
            PoiExcel poiExcel = new PoiExcel();
            es.submit(new PoiWriter(doneSignal, poiExcel));
            doneSignal.await();
            System.out.println("read  finish execute time: " + (System.currentTimeMillis() - startTime) / 1000 + " s");
            FileOutputStream os = new FileOutputStream(xlsFile);
            wb.write(os);
            os.flush();
            os.close();
            System.out.println(" outPutStream finish execute time: " + (System.currentTimeMillis() - startTime) / 1000 + " s");
        } catch (Exception e) {
            e.printStackTrace();

从数据库读数 生产者   ↵
 

package com.example.demo.test;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CountDownLatch;
 * @Author: myc
 * @Description: 获取对账单表中的数据并放在queue中 ,这里每个线程对应一张表,读完可读剩余的某一张表,保证原子性。
 * 新建的线程个数，不要超过sqlQueue的大小，否则会有线程阻塞消耗内存。
 * @Date 下午 5:37 2019/6/28 0028
public class PoiProductor implements Runnable {
    private final CountDownLatch doneSignal;
    public PoiProductor(CountDownLatch doneSignal) {
        this.doneSignal = doneSignal;
    @Override
    public void run() {
        List list = null;
        String sql = null;
        int id = 0;
        while (true) {
            try {
                //synchronized (doneSignal) {
                if (null == list || list.size() < 1) {
                    id = 0;
                    if (PoiExcel.sqlQueue.size() > 0) {
                        sql = PoiExcel.sqlQueue.take();
                    } else {
                        break;
                if (null != list && list.size() > 0) {
                    id = getId(list);
                list = getListData(sql, id);
                PoiExcel.queue.put(list);
            } catch (Exception e) {
                e.printStackTrace();
    public static int getId(List list) throws Exception {
        List<PoiEntity> _list = list;
        int id = _list.get(_list.size() - 1).getId();
        return id;
    private static void addToList(List _list, ResultSet rs) throws SQLException, ParseException {
        List<PoiEntity> list = _list;
        while (rs.next()) {
            String dynamicType = getDynamicType(rs);
            String transStatus = getTransStatus(rs);
            PoiEntity usera = new PoiEntity();
            usera.setId((rs.getInt("id")));
            usera.setTraceNo((rs.getString("traceNo")));
            usera.setMerchantNum((rs.getString("merchantNum")));
            usera.setTotalFee(rs.getBigDecimal("totalFee"));
            usera.setMerchantName(rs.getString("merchantName"));
            // usera.setTerminalNum(rs.getString("terminalNum"));
            usera.setDynamicType(dynamicType);
            usera.setTransBegin(rs.getString("transBegin"));
            usera.setTransStatus(transStatus);
            usera.setRefundFee(rs.getBigDecimal("refundFee"));
            usera.setOutTransNo(rs.getString("outTransNo"));
            //  usera.setBankType(rs.getString("bankType"));
            usera.setRateFee(rs.getString("rateFee"));
            list.add(usera);
    private static String getTransStatus(ResultSet rs) throws SQLException {
        String transStatus = rs.getString("transStatus");
      /*  if (transStatus.contains("weixin")) {
            transStatus = "微信";
        return transStatus;
    private static String getDynamicType(ResultSet rs) throws SQLException {
        String dynamicType = rs.getString("dynamicType");
      /*  if (dynamicType.contains("weixin")) {
            dynamicType = "微信支付";
        if (dynamicType.contains("alipay")) {
            dynamicType = "支付宝支付";
        return dynamicType;
    public static List getListData(String sql, int id) throws Exception {
        List _list = new ArrayList();
        PreparedStatement stmt = DbUtils.getStm();
        //posp_merchant_account_stats_detail_day_20190607
        sql = sql.replace("ownId", id + "");
        ResultSet rs = stmt.executeQuery(sql);
        if (null != rs) {
            addToList(_list, rs);
            return _list;
        } else {
            _list.clear();
            return null;

将数据写入到excel     --消费者
 

package com.example.demo.test;
import java.util.concurrent.CountDownLatch;
 * @Author: myc  进行sheet写操作
 * @Description:
 * @Date 上午 9:58 2019/6/28 0028
public class PoiWriter implements Runnable {
    private final CountDownLatch doneSignal;
    private PoiExcel poiExcel;
    public PoiWriter(CountDownLatch doneSignal,PoiExcel poiExcel) {
        this.doneSignal = doneSignal;
        this.poiExcel = poiExcel;
    @Override
    public void run() {
        try {
            while (true) {
                if(doneSignal.getCount() <= 0){
                    break;
                poiExcel.customerData(doneSignal);
        } catch (Exception e) {
            e.printStackTrace();
        } /*finally {
            doneSignal.countDown();
            System.out.println(" Count: " + doneSignal.getCount() + " thread name " + Thread.currentThread().getName());

package com.example.demo.test;
import java.math.BigDecimal;
 * @Author: myc
 * @Description:
 * @Date 上午 9:31 2019/7/1 0001
public class PoiEntity {
    private Integer id;
    private String merchantNum;
    private String merchantName;
    private String terminalNum;
    private String dynamicType;
    private String transBegin;
    private String transStatus;
    private BigDecimal totalFee;
    private BigDecimal refundFee;
    private String outTransNo;
    private String bankType;
    private String rateFee;
    private String traceNo;
    public Integer getId() {
        return id;
    public void setId(Integer id) {
        this.id = id;
    public String getMerchantNum() {
        return merchantNum;
    public void setMerchantNum(String merchantNum) {
        this.merchantNum = merchantNum;
    public String getMerchantName() {
        return merchantName;
    public void setMerchantName(String merchantName) {
        this.merchantName = merchantName;
    public String getTerminalNum() {
        return terminalNum;
    public void setTerminalNum(String terminalNum) {
        this.terminalNum = terminalNum;
    public String getDynamicType() {
        return dynamicType;
    public void setDynamicType(String dynamicType) {
        this.dynamicType = dynamicType;
    public String getTransBegin() {
        return transBegin;
    public void setTransBegin(String transBegin) {
        this.transBegin = transBegin;
    public String getTransStatus() {
        return transStatus;
    public void setTransStatus(String transStatus) {
        this.transStatus = transStatus;
    public BigDecimal getTotalFee() {
        return totalFee;
    public void setTotalFee(BigDecimal totalFee) {
        this.totalFee = totalFee;
    public BigDecimal getRefundFee() {
        return refundFee;
    public void setRefundFee(BigDecimal refundFee) {
        this.refundFee = refundFee;
    public String getOutTransNo() {
        return outTransNo;
    public void setOutTransNo(String outTransNo) {
        this.outTransNo = outTransNo;
    public String getBankType() {
        return bankType;
    public void setBankType(String bankType) {
        this.bankType = bankType;
    public String getRateFee() {
        return rateFee;
    public void setRateFee(String rateFee) {
        this.rateFee = rateFee;
    public String getTraceNo() {
        return traceNo;
    public void setTraceNo(String traceNo) {
        this.traceNo = traceNo;

package com.example.demo.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
 * @Author: myc
 * @Description:
 * @Date 下午 5:43 2019/6/28 0028
public class DbUtils {
    static String url = "jdbc:mysql://localhost:3306/posp?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";
    static String user = "root";
    static String password = "root";
    public static PreparedStatement getStm() throws Exception {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        //获取数据库连接
        Connection conn = null;
        PreparedStatement stmt = null;
        conn = DriverManager.getConnection(url, user, password);
        stmt = conn.prepareStatement("");
        return stmt;
                    ↵起因是这样的，用户要下载对账单明细，也就是交易明细，我们公司的数据库的设计是，一天一张表，类似于trace_20190708,trace_20190709 ....... 这样的类型，所以设计生产者的时候是，一个线程负责某天的表，线程数不要超过查询的天数总和（例如读取20190720-20190730 这十天的数据，那么线程数不能超过10个，也不能过多，看自己系统内存情况），否则，会有线...
				
用easyExcel，多线程，生产者-消费者模式写入excel
阿里easyexcel 插件
easyexcel 项目git地址为: https://github.com/alibaba/easyexcel
官网地址：https://alibaba-easyexcel.github.io
写excel类
public class WriteExcel {
    public static voi...

				
本案例采用的poi读取大数据的excel文件
usermodel模式对excel操作前需要将文件全部转入内存，对较大文件来说内存开销很大。但是其使用简单。
eventusermodel模式采用事件模型，对文件边读取边处理，内存消耗较低，效率高，因为不用等待文件全部装入内存。但使用较复杂。
对12万条数据，7M大小的文件也能正常运行。无需设置vm的内存空间

    private String prefix = "G:/JAVA资料/";
    private String readFilename = prefix + "curblock-笔试-1504_人口、人口密度统计年鉴_20191113.xlsx";
    private String writeFilename = prefix + "new.xlsx";
				
@Test
    public void test() throws InterruptedException, ExecutionException {
        String resStr = "{\"languageCode\":\"en-US\",\"categoryType\":\"\",\"categoryLevel\":\"\"}";
        GoodsPriceSellListDTO condition = JSON.parseObject(resStr, GoodsPr..

				
官网介绍及实例
SXSSF通过限制对滑动窗口中的行的访问来实现其低内存占用
默认窗口大小windowSize为100，由SXSSFWorkbook.DEFAULT_WINDOW_SIZE定义。
可以通过新的SXSSFWorkbook（int windowSize）在工作簿构建时指定窗口大小 
SXSSFWorkbook wb1 = new SXSSFWorkbook(100);
也可以通过SXSSF

				
周五的时候，同事突然问我有没有做过多线程写Excel的数据，看我一时没理解，同事说就是多线程往workbook中写数据。说起来Excel的操作之前做的很多了，但是重来没考虑过这么做，不过既然提起了，而且网上也有相关内容，何不自己尝试一下？于是自己便尝试用自己微薄的技术水平来实现下这个逻辑。
首先考虑需要哪些东西：
首先我们需要一个生成和处理数据的类：WriteDataUtils；
pa...

今天是2018.03.22，已经很久没有更新博客了。。这段时间一直挺忙的，也收获很多。最近一个excel导出的任务让我搞了好久，想想踩过的坑，就想上来小结一番。
------------------------------------------------------分割线------------------------------------------------------
public class ExcelExporter implements Runnable {
    private int startRow;
    private int endRow;
    private String fileName;
    public ExcelExporter(int startRow, int endRow, String fileName) {
        this.startRow = startRow;
        this.endRow = endRow;
        this.fileName = fileName;
    @Override
    public void run() {
        try {
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Sheet1");
            // 创建表头
            Row headerRow = sheet.createRow();
            for (int i = ; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
            // 填充数据
            for (int i = startRow; i <= endRow; i++) {
                Row row = sheet.createRow(i - startRow + 1);
                for (int j = ; j < data[i].length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(data[i][j]);
            // 导出Excel文件
            FileOutputStream outputStream = new FileOutputStream(fileName);
            workbook.write(outputStream);
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
// 创建线程池
ExecutorService executorService = Executors.newFixedThreadPool(10);
// 分批导出Excel文件
int batchSize = 100000;
for (int i = ; i < data.length; i += batchSize) {
    int startRow = i;
    int endRow = Math.min(i + batchSize - 1, data.length - 1);
    String fileName = "data_" + startRow + "_" + endRow + ".xlsx";
    executorService.execute(new ExcelExporter(startRow, endRow, fileName));
// 关闭线程池
executorService.shutdown();
while (!executorService.isTerminated()) {
    Thread.sleep(100);
 
推荐文章
绅士的斑马  ·  【QT】子类化QThread实现多线程 - 李春港 - 博客园
昨天
踢足球的洋葱  ·  全网首发！Kaggle操作完整指南！ - 墨天轮
1 月前
无邪的钢笔  ·  西藏和平解放与繁荣发展_西藏自治区人民政府
3 月前
朝气蓬勃的沙滩裤  ·  居延汉简：汉代边军的生活实录_地区_烽火_河西
9 月前
月球上的红金鱼  ·  广佛高速免费通行了，下一个会是谁？
1 年前
奔跑的猴子  ·  薛沫沫重生归来成为总裁小甜心，看她虐渣、撕婊。校园、职场都叱咤风云。顺便攻略千年冰山吸血鬼总裁。《重生成血族总裁的小甜点》1~51_哔哩哔哩_bilibili
1 年前
今天看啥   ·   Py中国   ·   codingpro   ·   藏经阁   ·   小百科   ·   link之家   ·   卧龙AI搜索
删除内容请联系邮箱 2879853325@qq.com
Code - 代码工具平台
© 2024 ~ 沪ICP备11025650号