java导入excel到数据库
时间: 2023-09-13 07:07:58 浏览: 36
你可以使用 Apache POI 库来读取 Excel 文件,并使用 JDBC 连接数据库将数据插入到数据库中。以下是一个简单的示例代码:
```java
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
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 ExcelToDatabase {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "password";
String driver = "com.mysql.jdbc.Driver";
String excelFilePath = "path/to/excel/file.xlsx";
String tableName = "table_name";
try {
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement pstmt = null;
FileInputStream fileInputStream = new FileInputStream(new File(excelFilePath));
Workbook workbook = WorkbookFactory.create(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
Row headerRow = sheet.getRow(0);
int colCount = headerRow.getLastCellNum();
String[] colNames = new String[colCount];
for (int i = 0; i < colCount; i++) {
Cell cell = headerRow.getCell(i);
colNames[i] = cell.getStringCellValue();
}
String sql = "INSERT INTO " + tableName + " (" + String.join(",", colNames) + ") VALUES (" + String.join(",", new String[colCount]).replace(",", "?,") + "?)";
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
pstmt = conn.prepareStatement(sql);
for (int j = 0; j < colCount; j++) {
Cell cell = row.getCell(j);
switch (cell.getCellType()) {
case STRING:
pstmt.setString(j + 1, cell.getStringCellValue());
break;
case NUMERIC:
pstmt.setDouble(j + 1, cell.getNumericCellValue());
break;
case BOOLEAN:
pstmt.setBoolean(j + 1, cell.getBooleanCellValue());
break;
default:
pstmt.setString(j + 1, "");
break;
}
}
pstmt.executeUpdate();
}
pstmt.close();
conn.close();
System.out.println("Data imported successfully!");
} catch (Exception e) {
e.printStackTrace();
}
}
}
```
在此示例中,我们首先使用 Apache POI 库读取 Excel 文件,并获取工作表中的数据。然后,我们使用 JDBC 连接到数据库,并使用 PreparedStatement 将数据插入到数据库中。最后,我们关闭 PreparedStatement 和 Connection 对象,并打印一条成功消息。请注意,此示例只处理字符串、数字和布尔类型的单元格。如果您的 Excel 文件包含其他类型的单元格,则需要进行适当的更改。