【Excel】Excel 拆分以及批量导入开发
1 前言
最近在忙项目上线的事情，那么上线前要确保系统流程或者基础档案的正确性，往往会做几次数据的准确性验证。具体的说就是把客户的一个月甚至两个月的不同公司下的各种订单数据、库存数据，导入到系统中，看每个公司的订单数据、所消耗的库存数量、流程能不能正确的跑下去等方面进行对比来验证。
奈何系统的导入做的真是拉跨，导入几十条慢的，更何况要导入的都是上万、几十万的数据，这就需要我们的产品、测试一点点拆分 Excel，来导入，消耗大量的时间，所以就写了点代码，辅助他们导入。
2 具体开发
导入的 Excel 都是明细级别的，并且订单号都是统一放入到每个 Excel 的订单备注这一列，所以分两步走：
（1）拆分：按订单号进行拆分，把他们拆分到各个 Excel 中（这里我用的 POI)
（2）导入：然后批量将拆分好的 Excel 进行导入（通过线程池来并发导入，并发数量取决于最大线程数，我目前暂时设置的 16）
这是 Excel 数据，大概长这个样子：
我们来看第一步拆分，我这里就直接贴简化代码了哈，注释也写的比较清晰，我就不啰嗦了哈：
package com.example.demo; import com.google.common.collect.Maps; import org.apache.commons.compress.utils.Lists; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.*; import java.util.stream.Collectors; /** * @author: kuku * @description */ public class Demo { /** * 数据文件绝对路径 */ private static String dataFileDir = "D:\\data\\导入模版.xlsx"; /** * 拆分文件存放的位置 */ private static String outDir = "D:\\data\\data\\"; public static void main(String[] args) throws Exception { // 数据文件、以及流 File dataFile = new File(dataFileDir); FileInputStream fileInputStream = new FileInputStream(dataFile); // 读到数据文件 XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); XSSFSheet sheet = workbook.getSheetAt(0); // ！！！下边四个参数需要根据每个数据文件改写 // 内容行列的边界，比如列的边界在第40列 行的边界在12400行 // 因为 Excel 不标准，通过 POI 可以获取 sheet 的 lastRow 但是不准都，所以直接自己定义 int columns = 40; int rows = 12400; // 分组的在第几列，拆分的依据 int groupColumn = 6; // 数据从第几行开始读取 int dataRow = 2; // 存放数据 List<Map<Integer, Object>> dataList = Lists.newArrayList(); // 遍历读取 for (int i = 2; i < rows; i++) { // 读取一行 XSSFRow row = sheet.getRow(i); // 存放一行的数据，用 map key = 第几列 val = 数据值 Map<Integer, Object> dataMap = Maps.newLinkedHashMap(); for (int j = 0; j < columns; j++) { // 遍历该行的每一列 XSSFCell cell = row.getCell(j); // 不为空的再读取 if (Objects.nonNull(cell)) { // 因为日期用 poi 读取的是整数所以这里特殊处理下 if (j == 0) { Date date = cell.getDateCellValue(); DateFormat formater = new SimpleDateFormat("yyyy/M/d"); dataMap.put(j, formater.format(date)); } else { // 这里一些小数呀、下拉选择框的获取方式需要特别注意下，要根据 poi 的类型来调用不同的方法 // 否则小数精度、订单号变为科学记数等会出错，下拉的拿的值也不对，要注意 String value = getValue(cell); dataMap.put(j, value); } } } // 打印该行数据，看看精度、日期什么的都对不对 System.out.println(String.format("%s==%s", dataMap.get(groupColumn), dataMap)); dataList.add(dataMap); } // 分组 Map<String, List<Map<Integer, Object>>> orderMap = dataList.stream().collect(Collectors.groupingBy(i -> String.valueOf(i.get(groupColumn)))); // 分组后，然后拆分到不同的 Excel 我这里是顺序的拆 // 因为拆分完互不影响了就，可以考虑将上边的分组的 map 用一个线程安全的 map, 然后并发的进行拆，提升下速度 Set<String> orderNos = orderMap.keySet(); for (String orderNo : orderNos) { // 每个订单的数据 list List<Map<Integer, Object>> data = orderMap.get(orderNo); // 每一个都写入到一个新的excel 中 XSSFWorkbook newWorkbook = new XSSFWorkbook(); XSSFSheet resource = newWorkbook.createSheet("资源模板"); for (int i = 0; i < data.size(); i++) { Map<Integer, Object> dataMap = data.get(i);
// + dataRow 从第几行开始写 XSSFRow row = resource.createRow(i + dataRow); for (int j = 0; j < columns; j++) { Object val = dataMap.get(j); if (Objects.nonNull(val)) { XSSFCell cell = row.createCell(j); cell.setCellValue((val.toString())); } } } newWorkbook.write(new FileOutputStream(outDir + orderNo + ".xlsx")); } } /** * 获取不同 cell 的值 * @param cell * @return */ private static String getValue(Cell cell) { String value = null; switch (cell.getCellType()) { case STRING: value = formatStringValue(cell); break; case NUMERIC: value = formatDoubleValue(cell); break; case BOOLEAN: value = formatBooleanValue(cell); break; case FORMULA: value = formatFormulaValue(cell); break; case BLANK: value = null; break; default: throw new RuntimeException("表格有不支持的列类型:" + cell.getCellType()); } return value; } /** * 字符串型的 * @param cell * @return */ private static String formatStringValue(Cell cell) { return cell == null ? null : cell.getStringCellValue(); } /** * 数值型的 * @param cell * @return */ private static String formatDoubleValue(Cell cell) { Double d = cell.getNumericCellValue(); String s = d.toString(); if (s.contains("E")) { cell.setCellType(CellType.STRING); return cell.getStringCellValue(); } else { return s; } } /** * 布尔型的 * @param cell * @return */ private static String formatBooleanValue(Cell cell) { Boolean b = cell.getBooleanCellValue(); return String.valueOf(b); } /** * 公式型的 * @param cell * @return */ private static String formatFormulaValue(Cell cell) { return cell.getCellFormula(); } }
拆完大概长这个样子，记得打开几个看看精度、数据都对不对哈：
然后就是调接口进行导入了：
package com.example.demo; import cn.hutool.http.HttpUtil; import com.google.common.collect.Sets; import java.io.File; import java.util.Set; import java.util.concurrent.ExecutorService; import java.util.concurrent.LinkedBlockingQueue; import java.util.concurrent.ThreadPoolExecutor; import java.util.concurrent.TimeUnit; import java.util.concurrent.atomic.AtomicInteger; /** * @author: kuku * @description */ public class Demo2 { /** * 线程池 * 无界队列 */ private static final ExecutorService EXECUTOR_SERVICE = new ThreadPoolExecutor( 16, 16, 20L, TimeUnit.SECONDS, new LinkedBlockingQueue<>() ); /** * 拆分的数据文件路径 */ private static String outDir = "D:\\data\\data\\"; /** * 接口地址 */ private static String REQUEST = "http://xxx"; /** * 特殊处理，比如排除哪些不处理 */ private static Set<String> NO_SET = Sets.newHashSet(); /** * 计数器，方便查看当前进度 */ private static AtomicInteger total = new AtomicInteger(0); public static void main(String[] args) { // 文件目录 File outFile = new File(outDir); // 所有文件 File[] files = outFile.listFiles(); for (File file : files) { // 订单号文件信息 String name = file.getName(); String[] split = name.split("\\."); String orderNo = split[0]; if (!NO_SET.contains(orderNo)) { // 依次放进线程池 EXECUTOR_SERVICE.submit(() -> { // 接口 token 什么的记得放 String res = HttpUtil .createPost(REQUEST) .form("file", file) .execute().body(); System.out.println(String.format("当前：%s, 当前单号：%s, 导入结果：%s", total.incrementAndGet(), file.getName(), res.replaceAll("\\r\\n|\\r|\\n", ""))); }); } } } }
这就是导入了。
你也可以将两步合到一步里，拆完直接导入，但是还是严谨点，拆完先看看数据都对不对，然后再导。
3 小结
好啦，到这里就结束了，有哪里写的不好的地方欢迎指正哈。
