一般般帅
上一篇:认识Prometheus
Excel生成SQL小工具
1038 2023-05-30 2023-05-30
前言：日常工作中，经常会遇到excel文件内容需要转成sql的数据形式，虽然之前写过一个小工具，但一直以来没有抽时间好好优化代码，基本上是能用就行。这次特地抽出一点时间，对之前的代码做下重构，这里也分享给大家。
一、pom文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>demo</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.9-rc</version>
</dependency>
<dependency>
<groupId>com.ibeetl</groupId>
<artifactId>beetlsql</artifactId>
<version>2.13.0.RELEASE</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.71</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
</dependencies>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
</project>
二、源代码
package zj;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import org.apache.poi.poifs.crypt.Decryptor;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.Date;
/**
* 可以通过本工具，快速将excel转换为相应的插入sql（csv也大同小异）
* 1.带密码的excel result_20230529_151402.xlsx/dpvixyp5 测试通过
* 2.无密码的excel result_20230228_113125.xlsx 测试通过
* 3.isMysqlOrHive true/false 测试通过
* @author hk
* @date 2023-05-30 21:00
*/
public class GenerateSqlByExcel {
/** 是否开启debug模式 */
private static boolean isDebug = false;
/** true-匹配mysql或hive，false-匹配phoenix */
private static boolean isMysqlOrHive = false;
/** 批量插入条数 */
private static int batchInsertCount = 100;
/** excel 根目录 */
private static String rootDir = "/Users/huangkui/local_git/demo";
/** 默认sql命名为 日期+excel文件名.sql */
private static String sqlDir = "/Users/huangkui/local_git/demo/sql";
/** excel文件路径 */
private static String[] files = new String[] {
rootDir + "/result_20230529_151402.xlsx"
};
/** excel密码，如果有的话 */
private static String[] pwds = new String[] {
"dpvixyp5"
};
/** 需要写入的表 */
private static String[] tables = new String[] {
"hk.t_test"
};
private static String[] sqlFile = new String[files.length];
public static void main(String[] args) throws Exception {
FileUtil.mkdir(new File(sqlDir));
for (int i = 0; i < files.length ; i++) {
// yyyy-MM-dd HH:mm:ss
String suffix = DateUtil.format(new Date(), "MM-dd HH:mm:SS");
String path = files[i].substring(rootDir.length())
.replace(".", "&" +suffix +".")
.replace(".xlsx", ".sql")
.replace(".xls", ".sql");
sqlFile[i] = sqlDir + path;
dealExcel(i);
System.out.println("处理文件索引" + i + "成功");
}
}
private static void dealExcel(int fileIndex) throws Exception {
File file = new File(files[fileIndex]);
System.out.println("文件索引" + fileIndex + ": 是否存在=" + file.exists() + " 绝对路径=" + file.getAbsolutePath());
System.out.println("sqlFile:" + sqlFile[fileIndex]);
// 输出至sql文件
FileWriter fw = new FileWriter(sqlFile[fileIndex], false);
Workbook workbook;
if (pwds.length != 0 && pwds.length - 1 >= fileIndex) {
// 需要解密
POIFSFileSystem pfs = new POIFSFileSystem(new File(files[fileIndex]));
EncryptionInfo encInfo = new EncryptionInfo(pfs);
Decryptor decryptor = Decryptor.getInstance(encInfo);
decryptor.verifyPassword(pwds[fileIndex]);
workbook = new XSSFWorkbook(decryptor.getDataStream(pfs));
} else {
workbook = new XSSFWorkbook(files[fileIndex]);
}
Sheet sheet = workbook.getSheetAt(0);
// 第一行 获取表头
Row firstRow = sheet.getRow(0);
int rowNos = sheet.getLastRowNum();
int columnNum = firstRow.getLastCellNum();
StringBuilder fixedStr;
if (isMysqlOrHive) {
fixedStr = new StringBuilder("insert into " + tables[fileIndex] + "(");
} else {
fixedStr = new StringBuilder("upsert into " + tables[fileIndex] + "(");
}
for (int j = 0; j < columnNum; j++) {
Cell cell = firstRow.getCell(j);
if (cell != null) {
if (isMysqlOrHive) {
if (j != columnNum - 1) {
fixedStr.append("`").append(cell.getStringCellValue()).append("`").append(",");
} else {
fixedStr.append("`").append(cell.getStringCellValue()).append("`");
}
} else {
if (j != columnNum - 1) {
fixedStr.append(cell.getStringCellValue()).append(",");
} else {
fixedStr.append(cell.getStringCellValue());
}
}
}
}
fixedStr.append(") values");
writeFile(fixedStr, fw);
for (int i = 1; i <= rowNos; i++) {
StringBuilder str = new StringBuilder(" (");
for (int j = 0; j < columnNum; j++) {
Cell cell = sheet.getRow(i).getCell(j);
if (isDebug) {
printCellInfo(cell, firstRow);
}
if (cell != null) {
if (j != columnNum - 1) {
if (cell.getCellType().equals(CellType.NUMERIC)) {
if (!needFormat(cell.getNumericCellValue())) {
str.append(cell.getNumericCellValue()).append(",");
} else {
str.append(formatBigNumber(cell.getNumericCellValue())).append(",");
}
} else {
str.append("'").append(cell.getStringCellValue()).append("',");
}
} else {
if (cell.getCellType().equals(CellType.NUMERIC)) {
if (!needFormat(cell.getNumericCellValue())) {
str.append(cell.getNumericCellValue());
} else {
str.append(formatBigNumber(cell.getNumericCellValue()));
}
} else {
str.append("'").append(cell.getStringCellValue()).append("'");
}
}
}
}
// 这里可对每一行生成的sql做具体处理
String exactStr = str.toString();
if (i % batchInsertCount == 0 || i == rowNos) {
writeFile(exactStr + ");\n", fw);
if (i != rowNos) {
writeFile(fixedStr, fw);
}
} else {
writeFile(exactStr + "),", fw);
}
}
fw.flush();
fw.close();
}
private static boolean needFormat(double number) {
return number > 1501738894 || number < -1501738894;
}
private static String formatBigNumber(double bigNumber) {
// 数字超过15亿，一般为日期类型
if (bigNumber > 1501738894 || bigNumber < -1501738894) {
DecimalFormat df = new DecimalFormat("0");
String s = df.format(bigNumber);
return s.substring(0, s.length() - 1);
}
return "'" + bigNumber + "'";
}
private static void writeFile(CharSequence str, FileWriter fw) throws IOException {
if (isDebug) {
System.out.print(str);
}
fw.append(str);
}
private static void printCellInfo(Cell cell, Row firstRow) {
System.out.println(cell.getRowIndex() + "行" + cell.getColumnIndex() + "列 key=" + firstRow.getCell(cell.getColumnIndex()) + ",value=" + cell + ",type=" + cell.getCellType());
}
}
总访问次数: 51次, 一般般帅 创建于 2023-05-30, 最后更新于 2023-05-30
欢迎关注微信公众号，第一时间掌握最新动态！