项目启动时，读取指定文件夹下的所有Excel文件，进行数据库表的初始化

一、目标与思路：

1. 在系统启动时，读取Excel格式的配置文件，将读入的数据写入数据库，实现数据库表的初始化。
2. 考虑到这一系列的操作都由后端 / 实施去操控，因此，默认数据库表和Excel之间的映射准确无误的。
   下述解决方案的先决条件：
   (1) 数据库中有需要的表。
   (2) Excel表格与数据库表的字段，字段名和字段类型无误。
   (3) Excel中的字段集合 包含于 数据库表字段集合。

二、数据准备

（一） 数据库建表

CREATE TABLE TEST_DATA_INIT_1 (
  ID         NVARCHAR2(36) PRIMARY KEY,
  SNO        NVARCHAR2(20) UNIQUE,
  NAME       NVARCHAR2(50),
  AGE        NUMBER,
  GENDER     NUMBER,
  SCORE      NUMERIC,
  MONEY      NUMERIC,
  STARTDATE  DATE
);

CREATE TABLE TEST_DATA_INIT_2 (
  ID         NVARCHAR2(36) PRIMARY KEY,
  SNO        NVARCHAR2(20) UNIQUE,
  NAME       NVARCHAR2(50),
  AGE        NUMBER,
  GENDER     NUMBER,
  SCORE      NUMERIC,
  MONEY      NUMERIC,
  STARTDATE  DATE
);

（二） Excel示例

三、后端编码实现

（一）常量设计：Excel文件 → 后端 的映射关系

package com.....init.consts;

import java.io.File;

/**
 * 数据初始化相关常量
 * 
 * @Author LIUYINGDI
 * @Date: 2019-12-04
 */
public class InitialConst {

	/**
	 * 需要进行初始化的目录
	 */
	public static final String INIT_DICTORY_PATH = "." + File.separator + "init" + File.separator;

	/**
	 * .xls类型Excel文件的后缀
	 */
	public static final String XLS_SUFFIX = ".xls";

	/**
	 * .xlsx类型Excel文件的后缀
	 */
	public static final String XLSX_SUFFIX = ".xlsx";

	/**
	 * 已经完成数据初始化Excel文件的前缀
	 */
	public static final String FINISH_INIT_PREFIX = "FINISH_";

	/**
	 * Excel中Date类型格式
	 */
	public static final String DARE_FORMAT = "yyyy-mm-dd";

	/**
	 * 需要随机生成UUID的字段
	 */
	public static final String RANDOM_UUID = "--";

	/**
	 * 数据类型
	 */
	public interface DataType {

		public static final String _INTEGER = "Integer";

		public static final String _STRING = "String";

		public static final String _DOUBLE = "Double";

		public static final String _BIGDECIMAL = "BigDecimal";

		public static final String _DATE = "Date";
	}
}

（二）Excel工具类：多个Excel文件 → List<String[ ][ ]>

package com.....init.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.....common.utils.UUIDUtil;
import com.....init.consts.InitialConst;

/**
 * Excel工具类
 * 
 * @Author LIUYINGDI
 * @Date: 2019-12-03
 */
public class ExcelHelper {

	/**
	 * 解析需要进行初始化的Excel文件
	 */
	public static List<String[][]> parseExcel() throws IOException {
		List<String[][]> excelCellArrayList = new ArrayList<String[][]>();
		List<String> excelNameList = Arrays.asList(new File(InitialConst.INIT_DICTORY_PATH).list());
		for (String excelName : excelNameList) {
			System.out.println("#### [" + excelName + "] 文件解析中... ####");
			/* 判断文件是否为excel文件 */
			if (!excelName.endsWith(InitialConst.XLS_SUFFIX) && !excelName.endsWith(InitialConst.XLSX_SUFFIX)) {
				throw new RuntimeException("只能对.xls类型或.xlsx类型的文件进行初始化。");
			}
			/* 若文件已被初始化，则跳过 */
			if (excelName.startsWith(InitialConst.FINISH_INIT_PREFIX)) {
				System.out.println("#### [" + excelName + "] 文件已被初始化，已跳过... ####");
				continue;
			}
			/* 文件 */
			File file = new File(InitialConst.INIT_DICTORY_PATH + excelName);
			/* 获取Excel文件的IO流 */
			InputStream is = new FileInputStream(file);
			/* 创建Workbook工作簿对象，表示整个excel。根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象2003 : 2007 */
			Workbook workbook = new HSSFWorkbook(is);
			/* 获得当前sheet工作表 */
			Sheet sheet = workbook.getSheetAt(0);
			int maxRowNum = sheet.getLastRowNum();
			int maxColNum = -1;
			while (sheet.getRow(1).getCell(maxColNum + 1) != null) {
				maxColNum++;
			}
			/* 创建返回对象 */
			String[][] cellArray = new String[maxRowNum + 1][maxColNum + 1];
			/* 第一行(index=0)为表名 */
			cellArray[0][0] = sheet.getRow(0).getCell(0).getStringCellValue();
			/* 第二行(index=1)为字段名 */
			/* 第三行(index=2)为字段类型 */
			/* 第四行(index=3)开始为数据 */
			for (int curRowNum = 1; curRowNum <= maxRowNum; curRowNum++) {
				for (int curColNum = 0; curColNum <= maxColNum; curColNum++) {
					Cell cell = sheet.getRow(curRowNum).getCell(curColNum);
					if (cell.getCellType().equals(CellType._NONE)) {
						cellArray[curRowNum][curColNum] = null;
					} else if (cell.getCellType().equals(CellType.BLANK)) {
						cellArray[curRowNum][curColNum] = "";
					} else if (cell.getCellType().equals(CellType.STRING)) {
						cellArray[curRowNum][curColNum] = cell.getStringCellValue();
					} else if (cell.getCellType().equals(CellType.NUMERIC)) {
						cellArray[curRowNum][curColNum] = String.valueOf(cell.getNumericCellValue());
					} else {
						close(is, workbook);
						throw new RuntimeException("[" + excelName + "] 文件第" + curRowNum + "行, 第" + curRowNum + "列格式错误。");
					}
					if (curRowNum >= 3) {
						if (InitialConst.DataType._STRING.equals(cellArray[2][curColNum])) {
							if (InitialConst.RANDOM_UUID.equals(cellArray[curRowNum][curColNum])) {
								cellArray[curRowNum][curColNum] = UUIDUtil.getRandomUUIDString();
							}
							cellArray[curRowNum][curColNum] = "'" + cellArray[curRowNum][curColNum] + "'";
						} else if (InitialConst.DataType._DATE.equals(cellArray[2][curColNum])) {
							cellArray[curRowNum][curColNum] = "to_date('" + cellArray[curRowNum][curColNum] + "', '" + InitialConst.DARE_FORMAT + "')";
						}
					}
				}
			}
			/* 添加到返回对象中 */
			excelCellArrayList.add(cellArray);
			/* 关闭流和文件 */
			close(is, workbook);
			System.out.println("#### [" + excelName + "] 文件解析完成... ####");
			System.out.println("--------------------------");
		}
		return excelCellArrayList;
	}

	/**
	 * 关闭流和文件
	 */
	private static void close(InputStream is, Workbook workbook) throws IOException {
		is.close();
		workbook.close();
	}

	/**
	 * 将解析过的Excel文件重命名
	 */
	public static void renameAll() {
		List<String> excelNameList = Arrays.asList(new File(InitialConst.INIT_DICTORY_PATH).list());
		for (String excelName : excelNameList) {
			/* 判断文件是否为excel文件 */
			if (!excelName.endsWith(InitialConst.XLS_SUFFIX) && !excelName.endsWith(InitialConst.XLSX_SUFFIX)) {
				throw new RuntimeException("只能对.xls类型或.xlsx类型的文件进行初始化。");
			}
			/* 若文件已被初始化，则跳过 */
			if (excelName.startsWith(InitialConst.FINISH_INIT_PREFIX)) {
				continue;
			}
			/* 文件 */
			File file = new File(InitialConst.INIT_DICTORY_PATH + excelName);
			/* 对文件重命名 */
			file.renameTo(new File(InitialConst.INIT_DICTORY_PATH + InitialConst.FINISH_INIT_PREFIX + excelName));
		}
	}
}

（三）SQL生成器：List<String[ ][ ]> → List< String>类型的SQL

package com.....init.utils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import com.....common.utils.DataUtils;

/**
 * SQL生成工具类
 * 
 * @Author LIUYINGDI
 * @Date: 2019-12-04
 */
public class SqlGenerator {

	/**
	 * 依据Excel数据集拼接SQL
	 */
	public static List<String> getInsertSql(List<String[][]> excelCellArrayList) {
		List<String> sqlList = new ArrayList<String>();
		for (int index = 0; index < excelCellArrayList.size(); index++) {
			String[][] cellArray = excelCellArrayList.get(index);
			List<String> columnNames = Arrays.asList(cellArray[1]);
			for (int row = 3; row < cellArray.length; row++) {
				StringBuffer sql = new StringBuffer();
				sql.append("INSERT INTO " + cellArray[0][0] + " (" + DataUtils.getInstance().convertList2String(columnNames, ", ", "", "") + ")\n");
				sql.append("VALUES (" + DataUtils.getInstance().convertList2String(Arrays.asList(cellArray[row]), ", ", "", "") + ")");
				sqlList.add(sql.toString());
			}
		}
		return sqlList;
	}
}

（四）实现CommandLineRunner接口，重写run方法，循环执行每条SQL（可批量）

package com.....init;

import java.io.IOException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

import com.....init.dao.StartupInitMapper;
import com.....init.pojo.SqlPojo;
import com.....init.utils.ExcelHelper;
import com.....init.utils.SqlGenerator;

@Component
public class StartupRunner implements CommandLineRunner {

	@Autowired
	StartupInitMapper startupInitMapper;

	@Override
	public void run(String... args) throws Exception {
		try {
			long start = System.currentTimeMillis();
			List<String[][]> excelCellArrayList = ExcelHelper.parseExcel();
			List<String> sqlList = SqlGenerator.getInsertSql(excelCellArrayList);
			for (String sql : sqlList) {
				/* for循环内，进行insert操作(可批量)即可，下述代码是获取Mybatis租户信息，无需参考 */
				System.out.println(sql);
				SqlPojo sqlPojo = new SqlPojo(sql);
				sqlPojo.setTenantName("gms"); // TODO 租户标识通过读取配置文件获取
				startupInitMapper.init(sqlPojo);
			}
			ExcelHelper.renameAll();
			long end = System.currentTimeMillis();
			long costs = (end - start) / 1000;
			System.out.println("#### 数据初始化成功！ #### 共消耗时长：" + costs + "秒");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}