poi读取Excel批量生成sql

场景

线上环境数据出现混乱，需要在系统中批量替换某一属性。提供了一个新旧属性对比的Excel文档。如下图所示：

实现方式

1、利用Excel自带concat公式去批量生成sql，再手动复制到文件中。
2、采用POI去读取文件，然后生成到文件中。

Java代码

import org.apache.commons.lang3.tuple.Pair;
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.BufferedWriter;
import java.io.FileInputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Author ada
 * @Date 2022/8/27 4:28 PM
 * @Version 1.0
 */
public class SqlExcutor {
    public static void main(String[] args) throws IOException {
        StringBuilder sql = new StringBuilder();
        sql.append("\nupdate table1 set attr_new = '{attrNew}' where attr = '{attrOld}' and sys_logic_del = 0;");
        sql.append("\nupdate table2 set attr_new = '{attrNew}' where attr = '{attrOld}' and sys_logic_del = 0;");

        List<String> sheetNames = Arrays.asList("sheet1", "sheet2");
        List<String> columnRules = Arrays.asList("1-attrOld", "2-attrNew");

        String inputPath = "/Users/ada/Downloads/2022.xlsx";
        String outPath = "/Users/ada/Downloads/test.sql";

        List<Pair<Integer, String>> columnRuleList = createColumnRules(columnRules);
        List<Map<String, String>> dataList = readExcel(inputPath, sheetNames, columnRuleList);
        List<String> sqlList = createSql(sql.toString(), dataList);
        outToFile(outPath, sqlList);
    }

    public static void outToFile(String outPath, List<String> sqlList) throws IOException {
        System.out.println("生成sql文件开始");
        Path path = Paths.get(outPath);
        try (BufferedWriter writer =
                     Files.newBufferedWriter(path, StandardCharsets.UTF_8)) {
            for (String sql : sqlList) {
                writer.write(sql);
            }
        }
        System.out.println("生成sql文件结束");
    }

    public static List<Pair<Integer, String>> createColumnRules(List<String> columnRules) {
        List<Pair<Integer, String>> columnRuleList = new ArrayList<>();
        columnRules.forEach(str -> {
            String[] strArr = str.split("-");
            columnRuleList.add(Pair.of(Integer.parseInt(strArr[0]) - 1, strArr[1]));
        });
        return columnRuleList;
    }

    public static List<Map<String, String>> readExcel(String path, List<String> sheetNames, List<Pair<Integer, String>> columnRuleList) throws IOException {
        System.out.println("开始读取Excel");
        List<Map<String, String>> dataList = new ArrayList<>();

        FileInputStream fileInputStream = new FileInputStream(path);
        XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
        sheetNames.forEach(sheetName -> {
            XSSFSheet sheet = sheets.getSheet(sheetName);
            System.out.println("扫描sheet[" + sheetName + "]开始**");
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < rows; i++) {
                Map<String, String> dataMap = new HashMap<>();
                XSSFRow row = sheet.getRow(i);
                for (Pair<Integer, String> pair : columnRuleList) {
                    XSSFCell cell = row.getCell(pair.getLeft());
                    if (cell == null) {
                        System.out.println("第" + i + "行无[" + pair.getRight() + "]终止扫描");
                        break;
                    }
                    dataMap.put(pair.getRight(), cell.getStringCellValue());
                }
                if (!dataMap.isEmpty()) {
                    dataList.add(dataMap);
                }
            }
            System.out.println("扫描sheet[" + sheetName + "]结束**");
        });
        System.out.println("读取Excel结束");
        return dataList;
    }

    public static List<String> createSql(String sql, List<Map<String, String>> dataList) {
        System.out.println("*生成sql开始");
        if (dataList == null || dataList.isEmpty()) {
            return null;
        }
        List<String> sqlList = new ArrayList<>();
        dataList.forEach(map -> {
            String newSql = assembleCode(sql, map);
            sqlList.add(newSql);
        });
        System.out.println("生成sql结束");
        return sqlList;
    }

    public static String assembleCode(String expression, Map<String, String> params) {
        if (params == null || params.isEmpty()) {
            return null;
        }
        List<String> variableList = getExpressionParam(expression);
        for (String str : variableList) {
            for (String key : params.keySet()) {
                if (key.equals(str)) {
                    expression = expression.replace("{" + key + "}", params.get(key));
                }
            }
        }
        return expression;
    }

    /**
     * 获取表达式之中的变量.
     *
     * @param expressions 编码表达式.
     * @return List
     */
    public static List<String> getExpressionParam(String expressions) {
        List<String> dataList = new ArrayList<>();
        String regex = "\\{(.*?)}";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(expressions);
        while (matcher.find()) {
            dataList.add(matcher.group(1));
        }
        return dataList;
    }

}```