Excel的数据导入数据库中。
一、一般我会这样做：
- 通过POI读取需要导入的Excel；
- 以文件名为表名、列头为列名、并将数据拼接成sql；
- 通过JDBC或mybatis插入数据库；
操作起来，如果文件比较多，数据量都很大的时候，会非常慢。
访问之后，感觉没什么反应，实际上已经在读取 + 入库了，只是比较慢而已。
读取一个10万行的Excel，居然用了191s，我还以为它卡死了呢！
private void readXls(String filePath, String filename) throws Exception {
@SuppressWarnings(“resource”)
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(filePath));
// 读取第一个工作表
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
// 总行数
int maxRow = sheet.getLastRowNum();
StringBuilder insertBuilder = new StringBuilder();
insertBuilder.append(“insert into “).append(filename).append(” ( UUID,”);
XSSFRow row = sheet.getRow(0);
for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
insertBuilder.append(row.getCell(i)).append(“,”);
}
insertBuilder.deleteCharAt(insertBuilder.length() – 1);
insertBuilder.append(” ) values ( “);
StringBuilder stringBuilder = new StringBuilder();
for (int i = 1; i <= maxRow; i++) {
XSSFRow xssfRow = sheet.getRow(i);
String id = “”;
String name = “”;
for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
if (j == 0) {
id = xssfRow.getCell(j) + “”;
} else if (j == 1) {
name = xssfRow.getCell(j) + “”;
}
}
boolean flag = isExisted(id, name);
if (!flag) {
stringBuilder.append(insertBuilder);
stringBuilder.append(‘\”).append(uuid()).append(‘\”).append(“,”);
for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
stringBuilder.append(‘\”).append(value).append(‘\”).append(“,”);
}
stringBuilder.deleteCharAt(stringBuilder.length() – 1);
stringBuilder.append(” )”).append(“\n”);
}
}
List collect = Arrays.stream(stringBuilder.toString().split(“\n”)).collect(Collectors.toList());
int sum = JdbcUtil.executeDML(collect);
}
private static boolean isExisted(String id, String name) {
String sql = “select count(1) as num from ” + static_TABLE + ” where ID = ‘” + id + “‘ and NAME = ‘” + name + “‘”;
String num = JdbcUtil.executeSelect(sql, “num”);
return Integer.valueOf(num) > 0;
}
private static String uuid() {
return UUID.randomUUID().toString().replace(“-“, “”);
}