excel转sql小工具

转载

测评员老杜 2024-10-30 14:05:00

一、说明

公司要迁移历史数据到另一个数据库中，历史数据只能由Excel导出提供。因此写了这个小工具用于将Excel中的数据拼接为INSERT语句，用于项目初始化时一次性导入。
Excel表头中的字段与表中字段映射关系由配置文件进行匹配，支持spEL表达式。

二、相关代码

主要的maven依赖

<!-- easyexcel框架，用于读取excel中的数据 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
        </dependency>
        <!-- lombok减少代码量，业务逻辑中没有使用，可不用 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>
        <!-- hutool一些静态工具，业务逻辑中没有使用，可不用 -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.6.5</version>
        </dependency>

Excel2SqlUtils.java //功能入口，包含main函数

import com.alibaba.excel.EasyExcel;
import com.xxx.xx.admin.xx.manager.listener.Excel2SqlListener;
import lombok.Data;
import org.apache.commons.lang3.StringUtils;
import org.springframework.expression.Expression;
import org.springframework.expression.ExpressionParser;
import org.springframework.expression.spel.standard.SpelExpressionParser;
import org.yaml.snakeyaml.Yaml;
import org.yaml.snakeyaml.constructor.Constructor;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.List;
import java.util.Objects;


@Data
public class Excel2SqlUtils {
    private String configYamlPath;
    private String db;
    private String table;
    private List<Entry> entryList;

    @Data
    public static class Entry {
        private String colName;
        private String sqlField;
        private String defaultValue;
    }

    public static void main(String[] args) throws FileNotFoundException {

        // 历史数据路径，必须是excel文件。可以填写绝对路径
        String excelPath = "doc/config/file/model.xlsx";
        // 配置文件路径，excel表头和数据库字段映射关系。可以填写绝对路径
        String configPath = "doc/config/model.yml";
        // 生成的sql文件路径。可以填写绝对路径
        String outPath = "doc/db/model.sql";

        EasyExcel.read(excelPath, new Excel2SqlListener(configPath, outPath)).sheet().doRead();

    }


    /**
     * 解析配置文件，获取映射关系
     * @param configYamlPath 配置文件路径
     */
    public static Excel2SqlUtils load(String configYamlPath) throws FileNotFoundException {
        Objects.requireNonNull(configYamlPath, "config yaml path can not be empty");
        Yaml yaml = new Yaml(new Constructor(Excel2SqlUtils.class));
        InputStream in = new FileInputStream(configYamlPath);
        Excel2SqlUtils csvMapConfig = yaml.load(in);
        Objects.requireNonNull(csvMapConfig, "yaml load error");
        Objects.requireNonNull(csvMapConfig.getEntryList(), "config entryList can not be empty");
        csvMapConfig.getEntryList().forEach(entry -> {
            Objects.requireNonNull(entry.getSqlField(), "the sql field of entryList item can not be null");
        });
        csvMapConfig.setConfigYamlPath(configYamlPath);
        return csvMapConfig;
    }

    /**
     * 获取默认值、置换spEL表达式的值
     * @param defaultValue 默认值
     */
    public static String getDefaultValue(String defaultValue) {
        if (StringUtils.isBlank(defaultValue)) {
            return "null";
        }

        if ((defaultValue).startsWith("${")) {
            // 创建 SpEL 表达式解析器
            ExpressionParser parser = new SpelExpressionParser();
            defaultValue = defaultValue.replace("${", "").replace("}", "");
            Expression expression = parser.parseExpression(defaultValue);
            return String.valueOf(expression.getValue());
        }
        return defaultValue;
    }
}

Excel2SqlListener.java // 拼接的主要业务逻辑，这里用了easyexcel包

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.xxx.xx.admin.util.Excel2SqlUtils;
import lombok.SneakyThrows;

import java.io.FileWriter;
import java.io.IOException;
import java.util.*;

/**
 * 解析Excel文件，拼接成sql语句
 */
public class Excel2SqlListener extends AnalysisEventListener<Map<Integer, String>> {
    private static final int BATCH_COUNT = 1000;
    List<Map<Integer, String>> list = new ArrayList<>();
    String configPath;
    private Excel2SqlUtils csvMapConfig;
    Map<String, Integer> excelHeadMap = new HashMap<>();
    // 配置文件中的字段映射
    Map<String, Excel2SqlUtils.Entry> col2Entry = new LinkedHashMap<>();
    StringBuilder sqlHeadBuilder = new StringBuilder();
    StringBuilder sqlValueBuilder = new StringBuilder();
    String outPath;
    FileWriter fileWriter;

    public Excel2SqlListener(String configPath, String outPath) {
        this.configPath = configPath;
        this.outPath = outPath;
    }

    /**
     * 获取表头，读取excel数据之前执行一次
     * @param headMap 表头信息
     * @param context 上下文
     */
    @SneakyThrows
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        // 表头封装
        for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
            String key = entry.getValue();
            Integer value = entry.getKey();
            excelHeadMap.put(key, value);
        }

        // 获取配置文件中的字段映射
        this.csvMapConfig = Excel2SqlUtils.load(configPath);

        // 输出文件流
        fileWriter = new FileWriter(outPath);
        String path = csvMapConfig.getConfigYamlPath();
        fileWriter.write("-- 初始化 " + path.split("/")[2].split("\\.")[0] + " 数据");

        // 配置文件中的字段映射关系封装
        for (Excel2SqlUtils.Entry entry : csvMapConfig.getEntryList()) {
            col2Entry.put(entry.getColName(), entry);
        }

        // 拼接INSERT INTO语句前部分
        sqlHeadBuilder.append("INSERT INTO ")
                // 数据库名,是否添加看业务逻辑
//                .append(sqlKeywordWrap(dbName)).append(".")
                .append(sqlKeywordWrap(this.csvMapConfig.getTable()));
        sqlHeadBuilder.append(" (");
        for (Excel2SqlUtils.Entry fieldEntry : col2Entry.values()) {
            sqlHeadBuilder.append(sqlKeywordWrap(fieldEntry.getSqlField()));
            sqlHeadBuilder.append(",");
        }
        sqlHeadBuilder.deleteCharAt(sqlHeadBuilder.length() - 1);
        sqlHeadBuilder.append(") VALUES");
    }

    /**
     * 按照行读取excel数据，每行执行一次
     * @param data 每行数据
     * @param context 上下文
     */
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        sqlValueBuilder.append("\n (");

        // 根据配置文件中的字段，去获取excel文档中的值
        for (Map.Entry<String, Excel2SqlUtils.Entry> configEntry : col2Entry.entrySet()) {
            String keyConfig = configEntry.getKey();
            Excel2SqlUtils.Entry valueConfig = configEntry.getValue();

            // excel文档中的字段值
            String valueExcel = data.get(excelHeadMap.get(keyConfig));

            // 配置文档中的默认值
            if (valueExcel == null) {
                valueExcel = Excel2SqlUtils.getDefaultValue(valueConfig.getDefaultValue());
            } else if (valueExcel.contains("'")) {
                // 如果字符串中包含单引号，要进行转义
                valueExcel = valueExcel.replace("'", "''");
            }

            sqlValueBuilder.append(sqlValWrap(valueExcel));
            sqlValueBuilder.append(",");
        }
        sqlValueBuilder.deleteCharAt(sqlValueBuilder.length() - 1);
        sqlValueBuilder.append("),");

        // 批量INSERT，一千条封装为一组
        list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
            sqlValueBuilder.setLength(0);
        }
    }

    /**
     * 读取excel数据结束执行一次
     * @param context 上下文
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 防止最后一批数据不满1000条，需要单独处理
        saveData();
        System.out.println("所有数据解析完成！解析个数为：" + context.readRowHolder().getRowIndex());
        System.out.println("所有数据解析完成！行号为：" + (context.readRowHolder().getRowIndex() + 1));
        try {
            fileWriter.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    private static String sqlValWrap(String val) {
        return "'" + val + "'";
    }

    private static String sqlKeywordWrap(String keyword) {
        return '`' + keyword.trim() + '`';
    }

    /**
     * 输出
     */
    private void saveData() {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlValueBuilder.deleteCharAt(sqlValueBuilder.length() - 1);
        sqlValueBuilder.append(";");

        sqlBuilder.append(sqlHeadBuilder);
        sqlBuilder.append(sqlValueBuilder);

        try {
            fileWriter.write("\n");
            fileWriter.write(String.valueOf(sqlBuilder));
            fileWriter.write("\n");
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}

model.yml // 配置文件，配置excel表头和表字段的映射关系

# 数据库名
db: cs_linux
# 表名
table: cs_user
# 数据库字段配置
entryList:
  # excel中的表头列名
  - colName: 职员工号
    # 数据库字段名，不能为空
    sqlField: personnel_id
  - colName: 部门代码
    sqlField: dept_id
  - colName: 工作类型代码
    sqlField: work_type_cd
  - colName: 创建人
    sqlField: created_by
    defaultValue: 'system'
  - colName: 创建时间
    sqlField: created_at
#    默认值，当表中数据为空，或者字段不存在时，填入默认值。支持spEl表达式
    defaultValue: "${T(cn.hutool.core.date.DateUtil).now()}"
  - colName: 修改人
    sqlField: updated_by
    defaultValue: 'system'
  - colName: 修改日期
    sqlField: updated_at
    defaultValue: "${T(cn.hutool.core.date.DateUtil).now()}"
  - colName: UID
    sqlField: uuid
    defaultValue: "${T(java.util.UUID).randomUUID().toString()}"