CompletionService的使用分页查数据，写入excel

最新推荐文章于 2023-02-11 01:16:42 发布

我不痛，棍子痛

最新推荐文章于 2023-02-11 01:16:42 发布

阅读量374

点赞数

分类专栏：基础文章标签： excel数据下载

本文链接：https://blog.csdn.net/kissandlove/article/details/103474296

版权

基础专栏收录该内容

8 篇文章 0 订阅

订阅专栏

背景：商户中心对账单文件下载。

产品需求：商户对结算明细的数据2.5w分一个文件。个人2.5w数据在一个excel文件中，不会导致打开文件慢，但是数据在写入excel只能多线程。对于文件的导出主要是防止数据在内存中大量存在不能及时的写到磁盘，故去和产品沟通一把。建议将数据分2k条写入一个excel文件，数据获取从mysql也是这个数据量级去分页查询。理论上会合理些，沟通后意见并未采纳（一般都挺难采纳，一句我理解你们技术的实现上可能更好但我不想商户整合2k的数据到2.5w CV大法。该场景就是伪命题，都对接我们支付平台了还能手动对账不成？？？？？？？产品妹子还是很强势呐！！！！！）。那么只能实现产品需求。。。。。。

实现方案：将各个业务类型的数据通过定时获取写入到数据库（暂时还缺少数据平台），通过CountDownLatch判断各个业务类型的数据新增到统一表merchant_settle_detail。执行过后才操作excel文件导出的任务，2.5w一个写线程写的数据通过分页查询获得

线程池分组：

package com.XX.merchant.service.task;

import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.XX.merchant.util.constants.ThreadConstants;

/**
 * <p>Title: ThreadTool.java</p>
 * <p>Description: 商户部件线程池管理</p>
 * <p>Copyright: Copyright (c) 2017 XXXX技术有限公司</p>
 * <p>Company: XXXX技术有限公司</p>
 * <p>Author:xxxx</p>
 * <p>CreateTime:2019年11月27日上午11:34:31
 */
@Configuration
public class ThreadToolsManager {

	// 商户线程池  不可和query的线程复用 商户线程(父)释放比query线程(子)晚 
	@Bean(value = "settleMerchantNoThreadPool")
	public ExecutorService buildConsumerQueueThreadPool( ) {
		int cpuCount = Runtime.getRuntime().availableProcessors();
		ExecutorService settleMerchantNoThreadPool = new ThreadPoolExecutor(cpuCount +1, cpuCount +1, 60L, TimeUnit.SECONDS,
					new ArrayBlockingQueue<>(1024), new RenameThreadFactory(ThreadConstants.BULID_SETTLE_DETAIL_POOL));
		return settleMerchantNoThreadPool;

	}
	
	@Bean(value = "querySettleDataPageThreadPool")
	public ExecutorService queryPageMerchantNoThreadPool( ) {
		int cpuCount = Runtime.getRuntime().availableProcessors();
		ExecutorService querySettleDataPageThreadPool = new ThreadPoolExecutor(cpuCount +1, cpuCount +1, 60L, TimeUnit.SECONDS,
				new LinkedBlockingQueue<>(1024), new RenameThreadFactory(ThreadConstants.QUERY_SETTLE_DATA_PAGE_POOL));
		return querySettleDataPageThreadPool;
	}
}

线程名规则重写：

package com.XXXX.merchant.service.task;

import com.XXXX.merchant.util.constants.ThreadConstants;
import org.apache.commons.lang3.StringUtils;

import java.util.concurrent.ThreadFactory;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * <p>Title: RenameThreadFactory.java</p>
 * <p>Description: 线程分组重命名工厂</p>
 * <p>Copyright: Copyright (c) 2017 XXXX技术有限公司</p>
 * <p>Company: XXXX技术有限公司</p>
 * <p>Author:xxxx</p>
 * <p>CreateTime:2019年11月27日上午11:37:38
 */
public class RenameThreadFactory implements ThreadFactory{
	
	// 线程组
	private final String prefix;
	// 守护线程标识
    private final boolean daemoThread;
    private final ThreadGroup threadGroup;
    // 默认组序号
    private final static AtomicInteger defaultGroupThreadNumber=new AtomicInteger(1);
    // 个性化组线程序号
    private final AtomicInteger personalizedThreadNum = new AtomicInteger(1);
	
	@Override
	public Thread newThread( Runnable r ) {
		String name = prefix + personalizedThreadNum.getAndIncrement();
        Thread thread = new Thread(threadGroup, r, name, 0);
        thread.setDaemon(daemoThread);
        return thread;
	}

	public RenameThreadFactory ( ) {
		this(ThreadConstants.DEFAULT_PREFIX , false);
	}
	
	public RenameThreadFactory(String prefix) {
		this(prefix, false);
	}
	
	public RenameThreadFactory ( String prefix , boolean daemo ) {
		this.prefix = (StringUtils.isNotEmpty(prefix) ? prefix
				: ThreadConstants.DEFAULT_PREFIX) + defaultGroupThreadNumber.getAndIncrement() + ThreadConstants.THREAD_SPLIT;
		daemoThread = daemo;
		SecurityManager s = System.getSecurityManager();
		threadGroup = (s == null) ? Thread.currentThread().getThreadGroup() : s.getThreadGroup();
	}
}

多商户情况下的商户线程以及某一商户2w写一个线程

package com.XXXX.merchant.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.CompletionService;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorCompletionService;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.LinkedBlockingQueue;

import javax.annotation.Resource;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.XXXX.merchant.dal.model.ClearingStatementEnum;
import com.XXXX.merchant.service.ClearingStatementService;
import com.XXXX.merchant.service.UploadSettleBillService;
import com.XXXX.merchant.service.task.ClearingSettleQueryTask;
import com.XXXX.merchant.util.DateUtil;
import com.XXXX.merchant.util.ExcelUtil;
import com.XXXX.merchant.util.ExportExcelZipUtils;
import com.XXXX.merchant.util.FtpUtil;
import com.XXXX.merchant.util.bean.domain.UploadSettleBill;
import com.XXXX.merchant.util.constants.Constants;
import com.XXXX.merchant.util.enums.EnumBase;

import lombok.extern.slf4j.Slf4j;

/**
 * <p>Title: UploadSettleBillServiceImpl.java</p>
 * <p>Description: 上传结算单至ftp实现类</p>
 * <p>Copyright: Copyright (c) 2017XXXX技术有限公司</p>
 * <p>Company:XXXX技术有限公司</p>
 * <p>Author:xxxx</p>
 * <p>CreateTime:2019年11月27日下午2:38:44
 */
@Service
@Slf4j
public class UploadSettleBillServiceImpl implements UploadSettleBillService {

	@Resource
	ClearingStatementService clearingStatementService;

	@Resource(name = "settleMerchantNoThreadPool")
	private ExecutorService settleMerchantNoThreadPool;
	@Resource(name = "querySettleDataPageThreadPool")
	private ExecutorService querySettleDataPageThreadPool;

	@Value("${merchant.excel.tempPath}")
	private String tempPath;

	@Resource
	FtpUtil ftpUtil;

	@Resource
	RedisUtil redisUtil;

	@Override
	public boolean bulidClearingSettleData( UploadSettleBill query ) {
		try {
			Date clearingDate = query.getClearingDate();
			if (null == clearingDate) {
				clearingDate = DateUtil.parser(DateUtil.getZoneTimeDate(new Date(), ((8 * 60) - (Constants.GMT_NO * 60))),
						DateUtil.POINT_DATA_LONG_DTM_PART_FORMAT);
			}
			List< String > merchantNoList = query.getMerchantNoList();
			if (CollectionUtils.isEmpty(merchantNoList)) {
				merchantNoList = clearingStatementService.getDistanceMerchantNoList(clearingDate);
				if (CollectionUtils.isEmpty(merchantNoList)) {
					log.info("merchantNoList is null,can not bulid clearing settle data!");
					return Boolean.TRUE;
				}
			}
			final Date queryDate = clearingDate;
			merchantNoList.stream().forEach(merchantNo -> settleMerchantNoThreadPool.execute(( ) -> {
				int lockFlag = redisUtil.lockIgnoreException(CachePrefixUtil.BULID_CLEARING_SETTLE+merchantNo+ Constants.EXCEL_SPLIT+queryDate, CachePrefixUtil.ONE_HOUR * 3);
				if(RedisUtil.LOCK_STATUS_SUCCESS == lockFlag) {
					String year = DateUtil.getYear();
					String month = DateUtil.getMonth(queryDate);
					String day = DateUtil.getDay();// 补跑产生的多线程delAllFile 问题
					String execlTemp = tempPath + File.separator + Constants.BILL_TYPE_SETTLE + File.separator + year + File.separator
							+ month + File.separator +day + File.separator + merchantNo;

					int total = clearingStatementService.queryClearingStatementPages(merchantNo, queryDate);
					if (0 == total) {
						log.info("{} settle data no record in {}", merchantNo, queryDate);
						return;
					}
					int tempLastOne = total % Constants.EXCEL_SIZE_COUNT;
					int countTimes = total / Constants.EXCEL_SIZE_COUNT;
					int queryClearTimes = (0 == tempLastOne) ? countTimes : countTimes + 1;
					try {
						LinkedBlockingQueue< String > runQueue = new LinkedBlockingQueue< String >(queryClearTimes);
						List< String > fileNames = new ArrayList< String >();
						CompletionService< String > completionService = null;
						for (int i = 0; i < queryClearTimes; i++) {
							if(i == (queryClearTimes-1) && tempLastOne != 0) {
								total = tempLastOne;
							}
							completionService = new ExecutorCompletionService(querySettleDataPageThreadPool, runQueue);
							completionService.submit(new ClearingSettleQueryTask(merchantNo, queryDate, i, execlTemp,total));
						}
						for (int i = 0; i < queryClearTimes; i++) {
							String response = completionService.take().get();
							fileNames.add(response);
						}
						String targetZip = Constants.FTP_FILE_ZIP_PREFIX + Constants.EXCEL_SPLIT + merchantNo + Constants.EXCEL_SPLIT
								+ DateUtil.toMailDateDtPartString(queryDate) + Constants.FTP_FILE_ZIP_SUFFIX;
						String targetZipPath = execlTemp + File.separator + targetZip;
						ExportExcelZipUtils.toZipFiles(targetZipPath, fileNames);
						InputStream is = new FileInputStream(targetZipPath);
						ftpUtil.uploadClearingSettleFile(year, month, merchantNo, Constants.CLEARING_SETTLE_REPORT, targetZip, is);
					} catch (ExecutionException e) {
						log.error("bulidClearingSettleData ExecutionException e", e);
					} catch (InterruptedException e) {
						log.error("bulidClearingSettleData InterruptedException e", e);
					} catch (FileNotFoundException e) {
						log.error("bulidClearingSettleData FileNotFoundException e", e);
					}finally {
						ExportExcelZipUtils.delAllFile(execlTemp);
						redisUtil.unLockIgnoreException(CachePrefixUtil.BULID_CLEARING_SETTLE+merchantNo+ Constants.EXCEL_SPLIT+queryDate);
					}
				}else {
					log.info("merchantNo : {} in date {} get lock failed, please try later again", merchantNo, queryDate);
				}
				
			}));
			 settleMerchantNoThreadPool.awaitTermination(Long.MAX_VALUE, TimeUnit.SECONDS);
		} catch (ParseException e) {
			log.error("set default query date error!", e);
			return Boolean.FALSE;
		} catch (Exception e) {
			log.error("settle rollback error!", e);
			return Boolean.FALSE;
		}
		return Boolean.TRUE;
	}
	
	/**
	 * D:\application\merchant\temp\settle\2019\12\200000040001 下生成零时文件
	 * @param merchantNo
	 * @param queryDate
	 * @param dataList
	 * @param time
	 * @param path
	 * @param pageTime
	 * @return
	 */
	@Override
	public String createExcel( String merchantNo, Date queryDate, List< ClearingStatementEnum > dataList, int time,String path, int pageTime ) {
		ClearingStatementEnum clearingStatement = new ClearingStatementEnum();
		String columnName = "";
		String mailDateDtPartString = DateUtil.toMailDateDtPartString(queryDate);
		String[] titles = ExcelUtil.settleDetailTitle;
		final List< String[] > datas = new ArrayList<>();
		for (int k = 0; k < dataList.size(); k++) {
			clearingStatement = dataList.get(k);
			final String[] data = new String[titles.length];
			for (int j = 0; j < titles.length; j++) {
				/**
				 * 反射得到当前列的值
				 */
				String value = null;
				try {
					Method getMethod = ClearingStatementEnum.class
							.getMethod(ExcelUtil.COLUNM_METHOD_CLEARINGSTATEMENT.get(titles[j]));
					columnName = ExcelUtil.COLUNM_METHOD_CLEARINGSTATEMENT.get(titles[j]);
					if (StringUtils.equals(columnName, ExcelUtil.COLUNM_METHOD_CLEARINGSTATEMENT.get(titles[4]))) {
						value = DateUtil.toPointDtmPart((Date) getMethod.invoke(clearingStatement));
					} else if (StringUtils.equalsAny(columnName, ExcelUtil.COLUNM_METHOD_CLEARINGSTATEMENT.get(titles[6]),
							ExcelUtil.COLUNM_METHOD_CLEARINGSTATEMENT.get(titles[7]))) {
						value = ((EnumBase) getMethod.invoke(clearingStatement)).getValue();
					} else {
						value = (String) getMethod.invoke(clearingStatement);
					}
					data[j] = value;
				} catch (Exception e) {
					log.error("ClearingStatementEnum have not method {}",
							ExcelUtil.COLUNM_METHOD_CLEARINGSTATEMENT.get(titles[j]));
				}
			}
			datas.add(data);
		}
		String fileName = Constants.FTP_FILE_PREFIX + Constants.EXCEL_SPLIT + merchantNo + Constants.EXCEL_SPLIT
				+ mailDateDtPartString + Constants.EXCEL_SPLIT + ++time + Constants.EXCEL_FILE_SUFFIX;
		return ExportExcelZipUtils.excelToLocalForPage(titles, datas, path, fileName,pageTime);
	}

}

注意：目前目录没有到日只到月，线程补跑和当日线程同时执行情况下存在多线程问题，ExportExcelZipUtils.delAllFile(execlTemp);会出现问题。

String day = DateUtil.getDay();// 补跑产生的多线程delAllFile 问题
String execlTemp = tempPath + File.separator + Constants.BILL_TYPE_SETTLE + File.separator + year + File.separator
+ month + File.separator +day + File.separator + merchantNo;

同一天多线程创建相同的文件名的问题需要通过分布式锁解决

2.5w一个excel的写线程

package com.XXXX.merchant.service.task;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.CompletionService;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorCompletionService;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import com.XXXX.merchant.dal.model.ClearingStatementEnum;
import com.XXXX.merchant.service.context.MyContextHodler;
import com.XXXX.merchant.service.impl.ClearingStatementServiceImpl;
import com.XXXX.merchant.service.impl.UploadSettleBillServiceImpl;
import com.XXXX.merchant.util.constants.Constants;

import lombok.extern.slf4j.Slf4j;

/**
 * <p>Title: ClearingSettleQueryTask.java</p>
 * <p>Description: 分页读取数据库数据</p>
 * <p>Copyright: Copyright (c) 2017 XXXX技术有限公司</p>
 * <p>Company: XXXX技术有限公司</p>
 * <p>Author:xxxx</p>
 * <p>CreateTime:2019年12月4日下午3:28:59
 */
@Slf4j
public class ClearingSettleQueryTask implements Callable< String >{
	private String merchantNo;
	private Date queryDate;
	private int time;
	private String path;
	private int total;

	
	public ClearingSettleQueryTask ( ) {}

	public ClearingSettleQueryTask ( String merchantNo , Date queryDate , int time, String path, int total ) {
		this.merchantNo = merchantNo;
		this.queryDate = queryDate;
		this.time = time;
		this.path = path;
		this.total = total;
	}

	@Override
	public String call( ) throws Exception {
		String fileName = "";
		try {
			int temp = (total > Constants.EXCEL_SIZE_COUNT) ? Constants.EXCEL_SIZE_COUNT : total;
			int index = temp % Constants.CLEAR_SETTLE_SIZE;
			int countTimes = temp / Constants.CLEAR_SETTLE_SIZE;
			int queryIndex = (index == 0) ? countTimes : countTimes + 1;
			List< ClearingStatementEnum > dataList = new ArrayList< ClearingStatementEnum >();
			ClearingStatementServiceImpl clearingStatementService = (ClearingStatementServiceImpl) MyContextHodler
					.getBean("clearingStatementServiceImpl");
			UploadSettleBillServiceImpl uploadSettleBillService = (UploadSettleBillServiceImpl) MyContextHodler
					.getBean("uploadSettleBillServiceImpl");
			int pageSize = Constants.CLEAR_SETTLE_SIZE;
			for (int i = 0; i < queryIndex; i++) {
				if (i == (queryIndex - 1)) {
					pageSize = index;
				}
				dataList = clearingStatementService.getClearingSettleForExcel(merchantNo, queryDate,
						time * Constants.EXCEL_SIZE_COUNT + i * Constants.CLEAR_SETTLE_SIZE, pageSize);
				if (null != dataList) {
					fileName = uploadSettleBillService.createExcel(merchantNo, queryDate, dataList, time, path, i);
				}
			}
		} catch (Exception e) {
			log.error("{} query settle data exception in {}", merchantNo, queryDate, e);
		}
		//System.out.println("time:"+time+"======total:"+total);
		return fileName;
	}


	public String getMerchantNo( ) {
		return merchantNo;
	}

	public void setMerchantNo( String merchantNo ) {
		this.merchantNo = merchantNo;
	}

	public Date getQueryDate( ) {
		return queryDate;
	}

	public void setQueryDate( Date queryDate ) {
		this.queryDate = queryDate;
	}

	public int getTime( ) {
		return time;
	}

	public void setTime( int time ) {
		this.time = time;
	}

	public String getPath( ) {
		return path;
	}

	public void setPath( String path ) {
		this.path = path;
	}

	public int getTotal( ) {
		return total;
	}

	public void setTotal( int total ) {
		this.total = total;
	}

	
}

Excel表头和反射字段配置

package com.XXXX.merchant.util;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import com.XXXX.merchant.util.bean.request.ExportTradeTitle;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class ExcelUtil {

    public static final String[] settleDetailTitle ={"Merchant Name","Merchant ID",......,"Record Type","Payment Currency","Payment Amount","Settlement Currency","Income","Expenses","Transaction fee",......};
    

    
    public static HashMap<String,String> COLUNM_METHOD_CLEARINGSTATEMENT = new HashMap<String,String>();

    static{
    	COLUNM_METHOD_CLEARINGSTATEMENT.put("Merchant Name", "getMerchantName");
    	COLUNM_METHOD_CLEARINGSTATEMENT.put("Merchant ID", "getMerchantId");
    	......
    	COLUNM_METHOD_CLEARINGSTATEMENT.put("Record Type", "getRecordType");
    	COLUNM_METHOD_CLEARINGSTATEMENT.put("Payment Currency", "getPaymentCurrency");
    	COLUNM_METHOD_CLEARINGSTATEMENT.put("Payment Amount", "getPaymentAmount");
    	COLUNM_METHOD_CLEARINGSTATEMENT.put("Settlement Currency", "getClearingCurrency");
    	COLUNM_METHOD_CLEARINGSTATEMENT.put("Income", "getIncome");
    	COLUNM_METHOD_CLEARINGSTATEMENT.put("Expenses", "getExpenses");
    	COLUNM_METHOD_CLEARINGSTATEMENT.put("Transaction fee", "getTransactionFee");
    	......
    }
}

excel基础类

package com.XXXX.merchant.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jp.merchant.util.constants.Constants;
import com.opensymphony.webwork.ServletActionContext;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import sun.applet.Main;

/**
 * <p>Title: ClearingSettleQueryTask.java</p>
 * <p>Description: Excel基类</p>
 * <p>Copyright: Copyright (c) 2017 XXXX技术有限公司</p>
 * <p>Company: XXXX技术有限公司</p>
 * <p>Author:xxxx</p>
 * <p>CreateTime:2019年12月4日下午3:28:59
 */
@Slf4j
public class ExportExcelZipUtils {


   ......

	public static String toZipFiles( String execlTemp, List< String > fileNameList ) {
		final File[] files = new File[fileNameList.size()];
        for (int i = 0; i < fileNameList.size(); i++) {
            files[i] = new File(fileNameList.get(i));
        }
        // 压缩文件
        final File zipFile = new File(execlTemp);
        // 将excel文件压缩成zip文件
        final byte[] buf = new byte[1024];
        ZipOutputStream zipOut = null;
        FileInputStream fis = null;

        try {
            zipOut = new ZipOutputStream(new FileOutputStream(zipFile));
            for (int i = 0; i < files.length; i++) {
                fis = new FileInputStream(files[i]);
                zipOut.putNextEntry(new ZipEntry(files[i].getName()));
                int len = 0;
                while ((len = fis.read(buf)) > 0) {
                    zipOut.write(buf, 0, len);
                }
                zipOut.closeEntry();
                fis.close();
            }
        } catch (final Exception e) {
        	execlTemp = null;
            log.error("failed to create zip file");
        } finally {
            if (zipOut != null) {
                try {
                    zipOut.close();
                } catch (final IOException e) {
                    log.error("failed to close ZipOutputStream");
                }
            }
            if (fis != null) {
                try {
                    fis.close();
                } catch (final IOException e) {
                    log.error("failed to close FileInputStream");
                }
            }
        }
        return execlTemp;
	}



    /**
     *  删除文件夹下的所有文件
     * @param path
     * @return
     */
    public static boolean delAllFile(String path) {
        boolean flag = false;
        File file = new File(path);
        if (!file.exists()) {
            return flag;
        }
        if (!file.isDirectory()) {
            return flag;
        }
        String[] tempList = file.list();
        File temp = null;
        for (int i = 0; i < tempList.length; i++) {
            if (path.endsWith(File.separator)) {
                temp = new File(path + tempList[i]);
            } else {
                temp = new File(path + File.separator + tempList[i]);
        }
            if (temp.isFile()) {
                temp.delete();
            }
            if (temp.isDirectory()) {
                delAllFile(path + File.separator + tempList[i]);//先删除文件夹里面的文件
                delFolder(path + File.separator + tempList[i]);//再删除空文件夹
                flag = true;
            }
        }
        return flag;
    }


	public static String excelToLocalForPage( String[] titles, List< String[] > datas, String path, String fileName, int pageTime ) {
		log.info("excelToLocalForPage to local");
		String finalFileName = path + File.separator + fileName;

		Workbook workbook = null;
        Sheet sheet = null;
        Row row = null;
        OutputStream fos = null;
        try {
            File temp = new File(path);
            if (!temp.exists()){
                temp.mkdirs();
            }
            
            if(pageTime == 0){
            	workbook = new HSSFWorkbook();
                sheet = workbook.createSheet("sheet1");
                row = sheet.createRow(0);
                for (int i = 0; i < titles.length; i++) {
                    row.createCell(i).setCellValue(titles[i]);
                }
            }else {
            	POIFSFileSystem poifsFileSystem=new POIFSFileSystem(new FileInputStream(finalFileName));
            	workbook = new HSSFWorkbook(poifsFileSystem);
            	sheet = workbook.getSheet("sheet1");
            }
            int startPage = pageTime*Constants. CLEAR_SETTLE_SIZE;
            for (int i = 0; i < datas.size(); i++) {
            
                final String[] vals = datas.get(i);
                row = sheet.createRow(startPage + 1 +i);
                for (int j = 0; j < vals.length; j++) {
                    row.createCell(j).setCellValue(vals[j]);
                }
            }

            fos = new FileOutputStream(finalFileName);
            workbook.write(fos);
            fos.flush();
        } catch (final Exception e) {
            log.error("create excel Exception e",e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (final IOException e) {
                    log.error("workbook关闭失败", e);
                }
            }
            if (fos != null) {
                try {
                    fos.close();
                } catch (final IOException e) {
                    log.error("输出流关闭失败", e);
                }
            }
        }
		return finalFileName;
	}

	
}

ftp上传下载

package com.XXXX.merchant.util;

import com.XXXX.merchant.util.constants.Constants;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.commons.net.ftp.FTPReply;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.io.*;
import java.net.SocketException;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;


/**
 * <p>Title: UploadSettleBillServiceImpl.java</p>
 * <p>Description: ftp基类</p>
 * <p>Copyright: Copyright (c) 2017 XXXX技术有限公司</p>
 * <p>Company: XXXX技术有限公司</p>
 * <p>Author:xxxx</p>
 * <p>CreateTime:2019年11月27日下午2:38:44
 */
@Component
@Slf4j
public class FtpUtil {

	@Value("${merchant.ftp.host}")
	private String ftpHost;

	@Value("${merchant.ftp.userName}")
	private String ftpUserName;

	@Value("${merchant.ftp.password}")
	private String ftpPassword;

	@Value("${merchant.ftp.port}")
	private String port;

	/**
	 * 获取FTPClient对象
	 * @return
	 */
	public FTPClient getFTPClient( ) {
		FTPClient ftpClient = new FTPClient();
		try {
			ftpClient = new FTPClient();
			ftpClient.connect(ftpHost, Integer.parseInt(port));// 连接FTP服务器
			ftpClient.login(ftpUserName, ftpPassword);// 登陆FTP服务器
			if (!FTPReply.isPositiveCompletion(ftpClient.getReplyCode())) {
				log.info("未连接到FTP，用户名或密码错误。");
				ftpClient.disconnect();
			} else {
				log.info("FTP连接成功。");
			}
		} catch (SocketException e) {
			log.error("FTP的IP地址可能错误，请正确配置。");
		} catch (IOException e) {
			log.error("FTP的端口错误,请正确配置。");
		}
		return ftpClient;
	}

	public boolean uploadClearingSettleFile( String year, String month, String merchantNo, String bussType, String fileName,
			InputStream is ) {
		boolean success = false;
		FTPClient ftpClient = null;
		InputStream oldIs = null;
		try {
			int reply;
			ftpClient = getFTPClient();
			reply = ftpClient.getReplyCode();
			if (!FTPReply.isPositiveCompletion(reply)) {
				ftpClient.disconnect();
				return success;
			}
			ftpClient.setControlEncoding("UTF-8"); // 中文支持
			ftpClient.setFileType(FTPClient.BINARY_FILE_TYPE);
			ftpClient.enterLocalPassiveMode();
			ftpClient.makeDirectory(Constants.FTP_TOP_PATH);
			ftpClient.changeWorkingDirectory(Constants.FTP_TOP_PATH);
			ftpClient.makeDirectory(merchantNo);
			ftpClient.changeWorkingDirectory(merchantNo);
			ftpClient.makeDirectory(year);
			ftpClient.changeWorkingDirectory(year);
			ftpClient.makeDirectory(month);
			ftpClient.changeWorkingDirectory(month);
			ftpClient.makeDirectory(bussType);
			ftpClient.changeWorkingDirectory(bussType);
			// 判断文件是否已经存在
			oldIs = ftpClient.retrieveFileStream(fileName);
			if (oldIs != null && ftpClient.getReplyCode() != FTPReply.FILE_UNAVAILABLE) {
				log.info("fileName : {} has existed,need delete then reStore!", fileName);
				ftpClient.deleteFile(fileName);
			}
			ftpClient.storeFile(fileName, is);

			ftpClient.logout();
			success = true;
		} catch (IOException e) {
			log.error("uploadFile IOException e", e);
		} finally {
			try {
				if (null != is) {
					is.close();
				}
				if (null != oldIs) {
					oldIs.close();
				}
				if (null != ftpClient && ftpClient.isConnected()) {
					ftpClient.disconnect();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return success;
	}

	public void downloadZipFtp( String ftpPath, OutputStream outputStream ) throws FileNotFoundException {
		FTPClient ftpClient = null;
		try {
			ftpClient = getFTPClient();
			// 中文支持
			ftpClient.setControlEncoding("UTF-8");
			ftpClient.setFileType(FTPClient.BINARY_FILE_TYPE);
			ftpClient.enterLocalPassiveMode();
			ftpClient.changeWorkingDirectory(ftpPath);
			FTPFile[] ftpFiles = ftpClient.listFiles();
			if (ftpFiles == null || ftpFiles.length == 0) {
				throw new FileNotFoundException("ftpPath:【" + ftpPath + "】 have no file in this path");
			}
			ZipOutputStream zipOut = null;
			InputStream inputStream = null;
			final byte[] buf = new byte[1024];
			try {
				zipOut = new ZipOutputStream(outputStream);
				for (FTPFile ftpFile : ftpFiles) {
					zipOut.putNextEntry(new ZipEntry(ftpFile.getName()));
					if (ftpFile.isFile()) {
						inputStream = ftpClient.retrieveFileStream(ftpFile.getName());
						int len = 0;
						while ((len = inputStream.read(buf)) > 0) {
							zipOut.write(buf, 0, len);
						}
						zipOut.closeEntry();
						inputStream.close();
					}
				}
			} catch (final Exception e) {
				log.error("failed to create zip file");
			} finally {
				if (zipOut != null) {
					try {
						zipOut.close();
					} catch (final IOException e) {
						log.error("failed to close ZipOutputStream");
					}
				}
				if (inputStream != null) {
					try {
						inputStream.close();
					} catch (final IOException e) {
						log.error("failed to close FileInputStream");
					}
				}
				ftpClient.logout();
			}
		} catch (FileNotFoundException e) {
			log.error("没有找到" + ftpPath + "文件");
			throw e;
		} catch (SocketException e) {
			log.error("连接FTP失败.");
		} catch (IOException e) {
			log.error("文件读取错误。");
		}
	}

}

int total 数据不共享，如果对象类型共享（merchantNo ）我们可以通过ThreadLocal task使用

调试过程中日志：

time:4=====i:0===queryData:[{"transactionFee":"0.12","updateTime":1575597760000},{"transactionFee":"0.12","updateTime":1575597760000}]
time:0=====i:0===queryData:[{"transactionFee":"0.12","updateTime":1575597760000},{"transactionFee":"0.12","updateTime":1575597760000}]
time:2=====i:0===queryData:[{"transactionFee":"0.12","updateTime":1575597760000},{"transactionFee":"0.12","updateTime":1575597760000}]
time:3=====i:0===queryData:[{"transactionFee":"0.12","updateTime":1575597760000},{"transactionFee":"0.12","updateTime":1575597760000}]
2019-12-06 14:10:14.330 [query-page-settle-detail-pool-3-thread-5] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
2019-12-06 14:10:14.334 [query-page-settle-detail-pool-3-thread-4] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
time:1=====i:0===queryData:[{"transactionFee":"0.12","updateTime":1575597760000},{"transactionFee":"0.12","updateTime":1575597760000}]
2019-12-06 14:10:14.337 [query-page-settle-detail-pool-3-thread-2] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
2019-12-06 14:10:14.340 [query-page-settle-detail-pool-3-thread-1] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
2019-12-06 14:10:14.425 [query-page-settle-detail-pool-3-thread-3] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
2019-12-06 14:10:14.636 [query-page-settle-detail-pool-3-thread-2] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 14:10:14.636 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 7, 2;] cost 9 ms;
time:1=====i:1===queryData:[{"transactionFee":"0.12","updateTime":1575597760000},{"transactionFee":"0.12","updateTime":1575597760000}]
2019-12-06 14:10:14.637 [query-page-settle-detail-pool-3-thread-3] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 14:10:14.637 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 12, 2;] cost 11 ms;
2019-12-06 14:10:14.637 [query-page-settle-detail-pool-3-thread-2] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
time:2=====i:1===queryData:[{"transactionFee":"0.12","updateTime":1575597760000},{"transactionFee":"0.12","updateTime":1575597760000}]
2019-12-06 14:10:14.638 [query-page-settle-detail-pool-3-thread-3] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
2019-12-06 14:10:14.657 [query-page-settle-detail-pool-3-thread-5] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 14:10:14.656 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 22, 2;] cost 27 ms;
2019-12-06 14:10:14.658 [query-page-settle-detail-pool-3-thread-4] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 14:10:14.658 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 17, 2;] cost 31 ms;
time:3=====i:1===queryData:[{"transactionFee":"0.12","updateTime":1575597760000},{"transactionFee":"0.12","updateTime":1575597760000}]
2019-12-06 14:10:14.661 [query-page-settle-detail-pool-3-thread-4] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
time:4=====i:1===queryData:[{"transactionFee":"0.12","updateTime":1575597760000},{"transactionFee":"0.12","updateTime":1575597760000}]
2019-12-06 14:10:14.664 [query-page-settle-detail-pool-3-thread-5] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
2019-12-06 14:10:14.675 [query-page-settle-detail-pool-3-thread-1] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 14:10:14.675 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 2, 2;] cost 45 ms;
time:0=====i:1===queryData:[{"transactionFee":"0.12","updateTime":1575597760000},{"transactionFee":"0.12","updateTime":1575597760000}]
2019-12-06 14:10:14.676 [query-page-settle-detail-pool-3-thread-1] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
2019-12-06 14:10:14.759 [query-page-settle-detail-pool-3-thread-2] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 14:10:14.759 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 9, 1;] cost 17 ms;
time:1=====i:2===queryData:[{"transactionFee":"0.12","updateTime":1575597760000}]
2019-12-06 14:10:14.759 [query-page-settle-detail-pool-3-thread-2] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
2019-12-06 14:10:14.762 [query-page-settle-detail-pool-3-thread-1] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 14:10:14.762 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 4, 1;] cost 16 ms;
time:0=====i:2===queryData:[{"transactionFee":"0.12","updateTime":1575597760000}]
2019-12-06 14:10:14.763 [query-page-settle-detail-pool-3-thread-1] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
2019-12-06 14:10:14.769 [query-page-settle-detail-pool-3-thread-3] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 14:10:14.769 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 14, 1;] cost 19 ms;
time:2=====i:2===queryData:[{"transactionFee":"0.12","updateTime":1575597760000}]
2019-12-06 14:10:14.770 [query-page-settle-detail-pool-3-thread-3] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
2019-12-06 14:10:14.780 [query-page-settle-detail-pool-3-thread-4] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 14:10:14.780 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 19, 1;] cost 22 ms;
time:3=====i:2===queryData:[{"transactionFee":"0.12","updateTime":1575597760000}]
2019-12-06 14:10:14.780 [query-page-settle-detail-pool-3-thread-4] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
2019-12-06 14:10:14.794 [query-page-settle-detail-pool-3-thread-5] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 14:10:14.794 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 24, 1;] cost 39 ms;
time:4=====i:2===queryData:[{"transactionFee":"0.12","updateTime":1575597760000}]
2019-12-06 14:10:14.795 [query-page-settle-detail-pool-3-thread-5] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local

i:0,response:D:\application_server\XXXX-merchant\temp\settle\2019\12\110120119\Settlement_110120119_20191206_3.xls
i:1,response:D:\application_server\XXXX-merchant\temp\settle\2019\12\110120119\Settlement_110120119_20191206_4.xls
2019-12-06 14:59:28.996 [query-page-settle-detail-pool-3-thread-5] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 14:59:28.996 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 24, 1;] cost 26 ms;
2019-12-06 14:59:28.997 [query-page-settle-detail-pool-3-thread-5] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
i:2,response:D:\application_server\XXXX-merchant\temp\settle\2019\12\110120119\Settlement_110120119_20191206_5.xls
2019-12-06 14:59:29.002 [query-page-settle-detail-pool-3-thread-2] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 14:59:29.002 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 9, 1;] cost 20 ms;
2019-12-06 14:59:29.003 [query-page-settle-detail-pool-3-thread-2] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
i:3,response:D:\application_server\XXXX-merchant\temp\settle\2019\12\110120119\Settlement_110120119_20191206_2.xls
2019-12-06 14:59:29.009 [query-page-settle-detail-pool-3-thread-1] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 14:59:29.009 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 4, 1;] cost 15 ms;
2019-12-06 14:59:29.010 [query-page-settle-detail-pool-3-thread-1] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
i:4,response:D:\application_server\XXXX-merchant\temp\settle\2019\12\110120119\Settlement_110120119_20191206_1.xls
2019-12-06 14:59:29.092 [bulid-settle-detail-pool-2-thread-1] INFO [com.XXXX.merchant.util.FtpUtil:46]- FTP连接成功。

int total 数据不共享，如果对象类型共享我们可以通过ThreadLocal task使用
2019-12-06 16:51:49.965 [query-page-settle-detail-pool-3-thread-2] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
time:1======total:25
time:2======total:25
time:3======total:25
2019-12-06 16:51:50.032 [query-page-settle-detail-pool-3-thread-3] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 16:51:50.032 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 24, 1;] cost 12 ms;
time:4======total:25
2019-12-06 16:51:50.033 [query-page-settle-detail-pool-3-thread-3] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
time:6======total:1
time:0======total:25
2019-12-06 16:51:50.043 [query-page-settle-detail-pool-3-thread-2] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 16:51:50.043 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 20, 2;] cost 24 ms;
2019-12-06 16:51:50.044 [query-page-settle-detail-pool-3-thread-2] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
2019-12-06 16:51:50.052 [query-page-settle-detail-pool-3-thread-2] INFO [com.XXXX.merchant.dal.util.MyBatisSqlInterceptor:56]- 2019-12-06 16:51:50.052 com.XXXX.merchant.dal.dao.xxMapper.getClearingSettleForExcel [select * from merchant_settle_detail where CLEARING_DATE = STR_TO_DATE('2019-12-06 00:00:00','%Y-%m-%d') and MERCHANT_ID = '110120119' order BY CLEARING_TIME desc limit 22, 0;] cost 6 ms;
2019-12-06 16:51:50.053 [query-page-settle-detail-pool-3-thread-2] INFO [com.XXXX.merchant.util.ExportExcelZipUtils:377]- excelToLocalForPage to local
time:5======total:25

我不痛，棍子痛

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
CompletionService的使用分页查数据，写入excel

背景：商户中心对账单文件下载。产品需求：商户对结算明细的数据2.5w分一个文件。个人2.5w数据在一个excel文件中，不会导致打开文件慢，但是数据在写入excel只能多线程。对于文件的导出主要是防止数据在内存中大量存在不能及时的写到磁盘，故去和产品沟通一把。建议将数据分2k条写入一个excel文件，数据获取从mysql也是这个数据量级去分页查询。理论上会合理些，沟通后意见并未采纳（一般都挺...
复制链接

扫一扫