教你怎么将表格数据转化成Insert into语句

最新推荐文章于 2024-06-02 16:57:29 发布

C空间c

最新推荐文章于 2024-06-02 16:57:29 发布

阅读量1.2k

点赞数 1

CC 4.0 BY-SA版权

文章标签： maven java spring boot

本文链接：https://blog.youkuaiyun.com/Ccyy325108/article/details/121422327

本文介绍了一种使用Java实现的工具类，该工具类能够从Excel文件中读取数据并生成相应的SQL插入语句，方便地将数据导入到数据库中。文章提供了完整的代码示例，并强调了处理依赖冲突及正确读取Excel表格的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.项目中添加依赖的maven

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.2.0</version>
</dependency>

2.在项目中定义一个工具类ReaderTableToSqlUtils

内容如下：

package cn.com.epicc.ecoop.jd.insured.service;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;

/**
* @author ：Cc
* @date ：Created in 2021/11/19 14:33
* @description：读取表格生成对应的sql文件
* @modified By：
* @version: V1.0.0
*/
public class ReaderTableToSqlUtils {

public static void main(String[] args) throws Exception {
writeStore("路径.xlsx");
}

private static void writeStore(String path) throws Exception {
Workbook workbook = new XSSFWorkbook(new File(path));
//读取excel中的sheet表名
Sheet sheet = workbook.getSheet("sheet表名");
int totalRow = sheet.getLastRowNum();
System.out.println("总共：" + totalRow);
//循环独处表中每一行中每一列的值
for (int i = 1; i <= totalRow; i++) {
Row row = sheet.getRow(i);
String COMCODE = resultContent(row.getCell(1));
String MAKECODE = resultContent(row.getCell(2));
String AGENTCODE = resultContent(row.getCell(3));
String HANDLERCODE = resultContent(row.getCell(4));
String HANDLER1CODE = resultContent(row.getCell(5));
String OPERATORCODE = resultContent(row.getCell(6));
String APPROVERCODE = resultContent(row.getCell(7));
String RATIONTYPE = "";
String storeSql = "insert into 表名 (AREACODE,COMCODE,MAKECODE,AGENTCODE," +
"HANDLERCODE,HANDLER1CODE,OPERATORCODE,APPROVERCODE,RATIONTYPE,.......)"
+ " values('" +"主键"+i + "','" + COMCODE + "','" + MAKECODE + "','" + AGENTCODE + "','"
+ HANDLERCODE + "','"+HANDLER1CODE+"','"+OPERATORCODE+"','"+APPROVERCODE+"','"+RATIONTYPE+"');";
System.out.println(storeSql);
//将sql写入文档中
writeTxtCount("存入sql文件.sql", storeSql);
}
}

//传入文件路径和生成的sql
private static boolean writeTxtCount(String path, String content) {
BufferedWriter bufferedWriter = null;
try {
bufferedWriter = new BufferedWriter(new FileWriter(path, true));
bufferedWriter.write(content + "\r\n");
return true;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
bufferedWriter.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return false;
}