JAVA-操作excel模版进行写入

需求描述 :
依据excel模版进行数据写入(简单数据类型),超过X条进行excel拆分
PS :
模版为简单模版,需注意列表行数、sheet名称

MAVEN :

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.15</version>
</dependency>
<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.15</version>
</dependency>

模版 :
代码 ExcelUtil.java :

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelUtil {
	
	private static final String TEMPLATE_PATH = "D:/test01.xlsx"; // 模版路径 (需确认是否放在项目,如果放在项目需要做改动)
	private static final String SHEET_NAME = "实时结算"; // sheet名称
	private static final String OUT_PATH = "D:/"; // 输出路径
	private static String FILE_NAME = "结算批次号_支付商户号_"; // 文件名称
	private static final int SPLIT_SIZE = 3; // 多少条进行excel拆分
	
	/**
	 * 测试类
	 */
	public static void main(String[] args) {
		try {
			List<TWalletOrderDetail> details = new ArrayList<TWalletOrderDetail>();
			details.add(TWalletOrderDetail.builder().tradeTime("2019-12-12").build());
			details.add(TWalletOrderDetail.builder().tradeTime("2019-12-13").build());
			details.add(TWalletOrderDetail.builder().tradeTime("2019-12-14").build());
			details.add(TWalletOrderDetail.builder().tradeTime("2019-12-15").build());
			writeExcel("APPMT-测试", "2019-12-12 10:00:00 - 2019-12-13 10:10:10", "100.00 | 10", "100.00 | 10", "10.00 | 10", details);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * excel数据写入 - 这里应该改成实体类较好. map不合适. 
	 * @param store 门店ID-门店名称
	 * @param trade_time 交易时间范围
	 * @param trade 交易 - 金额（元）|笔数
	 * @param settle 结算 - 金额（元）|笔数
	 * @param fee 手续费 - 金额（元）|笔数
	 */
	public static void writeExcel(String store, String trade_time, String trade, String settle, String fee, List<TWalletOrderDetail> details) throws Exception {
		Map<String, String> params = new HashMap<String, String>();
		params.put("store", store); // 门店ID-门店名称
		params.put("trade_time", trade_time); // 交易时间范围
		params.put("trade", trade); // 交易 - 金额（元）|笔数
		params.put("settle", settle); // 结算 - 金额（元）|笔数
		params.put("fee", fee); // 手续费 - 金额（元）|笔数
		// 对明细进行拆分
		List<List<TWalletOrderDetail>> splitList = splitList(details, SPLIT_SIZE);
		for (int i = 0; i < splitList.size(); i++) {
			InputStream is = null;
			try {
				// 模版路径 (需确认是否放在项目,如果放在项目需要做改动)
				is = new FileInputStream(new File(TEMPLATE_PATH));
				Workbook wb = WorkbookFactory.create(is);
				// 需要依据实际情况进行文件名的改动
				String fileName = FILE_NAME + (i + 1) + ".xlsx";
				writwData(wb, params, OUT_PATH + fileName, splitList.get(i));
				System.err.println("done");
			}catch (Exception e) {
				throw e;
			}finally {
				if(is != null) {
					is.close();
				}
			}
		}
	}
	
	private static void writwData(Workbook wb, Map<String, String> params, String outPath, List<TWalletOrderDetail> details) throws Exception {
		Sheet sheet = wb.getSheet(SHEET_NAME);
		// 数据替换
		replace(sheet, params);
		// 写列表数据
		writeRows(sheet, details);
		// 数据刷新写入
		OutputStream out = null;
		try {
			out = new FileOutputStream(new File(outPath));
			wb.write(out);
		}catch (Exception e) {
			throw e;
		}finally {
			if(out != null) {
				out.close();
			}
		}
		
	}
	
	/**
	 * 写列表数据
	 */
	private static void writeRows(Sheet sheet, List<TWalletOrderDetail> details) {
		if(CollectionUtils.isEmpty(details)) {
			return;
		}
		for (int i = 0; i < details.size(); i++) {
			TWalletOrderDetail tWalletOrderDetail = details.get(i);
			// 创建HSSFRow对象
			Row row = sheet.createRow(8 + i);
			row.createCell(0).setCellValue(tWalletOrderDetail.getTradeTime()); // 第一列
			row.createCell(1).setCellValue(tWalletOrderDetail.getTradeTime()); // 第二列
			// ...... 进行补充就OK
		}
	}
	
	/**
	 * 数据替换
	 */
	@SuppressWarnings("unused")
	private static void replace(Sheet sheet, Map<String, String> params) throws Exception{
		int trLength = sheet.getLastRowNum();
		int trLengthAgain = 6;
		for (int i = 0; i < trLength; i++) {
			Row row = sheet.getRow(i);
			if (checkNullRow(row)) {
				trLengthAgain -= 1;
				continue;
			}
			int minColIx = row.getFirstCellNum();
			int maxColIx = row.getLastCellNum();
			for (int colIx = minColIx; colIx < maxColIx; colIx++) {
				Cell cell = row.getCell(colIx);
				String runText = cell.getStringCellValue();
				if (StringUtils.isEmpty(runText)) {
					continue;
				}
				Matcher matcher = matcher(runText);
				if (matcher.find()) {
					while ((matcher = matcher(runText)).find()) {
						runText = matcher.replaceFirst(String.valueOf(params.get(matcher.group(1))));
					}
					cell.setCellValue(runText);
				}
			}
		}
	}
	
	/**
	 * 正则匹配字符串
	 */
	private static Matcher matcher(String str) {
		Pattern pattern = Pattern.compile("\\{(.+?)\\}", Pattern.CASE_INSENSITIVE);
		Matcher matcher = pattern.matcher(str);
		return matcher;
	}
	
	// 判断空行
	private static boolean checkNullRow(Row row) {
		return row == null || row.getCell(0) == null || StringUtils.isEmpty(row.getCell(0).getStringCellValue());
	}

	/**
	* 按指定大小，分隔集合，将集合按规定个数分为n个部分
	*/
	public static List<List<TWalletOrderDetail>> splitList(List<TWalletOrderDetail> list, int len) {
		if (list == null || list.size() == 0 || len < 1) {
			return null;
		}
		List<List<TWalletOrderDetail>> result = new ArrayList<List<TWalletOrderDetail>>();
		int size = list.size();
		int count = (size + len - 1) / len;
		for (int i = 0; i < count; i++) {
			List<TWalletOrderDetail> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
			result.add(subList);
		}
		return result;
	}
	
}