import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class ExcelToSqlConverter {
// Excel文件所在目录
private static final String EXCEL_DIR = "C:\";
// SQL语句模板
private static final String SQL_TEMPLATE = "INSERT INTO table (%s) VALUES (%s)%s";
public static void main(String[] args) {
// 获取Excel文件列表
List<File> excelFiles = getExcelFiles(EXCEL_DIR);
for (File excelFile : excelFiles) {
// 读取Excel文件数据
List<List<String>> data = readExcelData(excelFile);
// 转换为SQL语句并输出到控制台
printSqlStatements(data);
}
}
/**
* 获取指定目录下的Excel文件列表
*/
private static List<File> getExcelFiles(String dir) {
List<File> excelFiles = new ArrayList<>();
File folder = new File(dir);
if(folder.isFile() && folder.getName().endsWith(".xls")){
excelFiles.add(folder);
}
return excelFiles;
}
/**
* 读取Excel文件数据
*/
private static List<List<String>> readExcelData(File excelFile) {
List<List<String>> data = new ArrayList<>();
try (InputStream is = new FileInputStream(excelFile);
Workbook workbook = WorkbookFactory.create(is)) {
Sheet sheet = workbook.getSheetAt(0);
int rowCount = sheet.getLastRowNum() + 1;
for (int i = 0; i < rowCount; i++) {
Row row = sheet.getRow(i);
if (row != null) {
List<String> rowValues = new ArrayList<>();
int cellCount = row.getLastCellNum();
for (int j = 0; j < cellCount; j++) {
Cell cell = row.getCell(j);
if (cell != null) {
rowValues.add(cell.toString());
} else {
rowValues.add("");
}
}
data.add(rowValues);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return data;
}
/**
* 将数据转换为SQL语句并输出到控制台
*/
private static void printSqlStatements(List<List<String>> data) {
if (data.isEmpty()) {
return;
}
List<String> header = data.get(0);
String columns = String.join(",", header.subList(0, header.size()));
for (int i = 1; i < data.size(); i++) {
List<String> rowValues = data.get(i);
String values = String.join(",", rowValues.subList(0, rowValues.size()));
String sql = String.format(SQL_TEMPLATE, columns, values,";");
System.out.println(sql);
}
}
}
java execl转换sql语句工具类
于 2023-05-15 17:10:17 首次发布
该代码示例展示了如何用Java编程,通过ApachePOI库读取Excel文件内容,并将其转换为SQLINSERT语句。程序首先获取指定目录下的Excel文件,然后读取每张工作表的数据,最后将数据转化为SQL语句并打印到控制台。
摘要由CSDN通过智能技术生成