java接收excel文件并存到数据库
时间: 2024-05-03 14:22:23 浏览: 8
以下是一个简单的Java代码示例,演示如何接收Excel文件并将其存储到数据库中:
首先,您需要添加以下依赖项:
```xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
```
然后,您可以使用以下代码来处理Excel文件并将其存储到数据库中:
```java
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
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.xssf.usermodel.XSSFWorkbook;
public class ExcelToDatabase {
public static void main(String[] args) {
String jdbcUrl = "jdbc:mysql://localhost:3306/testdb";
String username = "root";
String password = "password";
String filePath = "path/to/excel/file.xlsx";
String tableName = "mytable";
try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
FileInputStream inputStream = new FileInputStream(new File(filePath));
Workbook workbook = new XSSFWorkbook(inputStream)) {
Sheet sheet = workbook.getSheetAt(0);
Row headerRow = sheet.getRow(0);
int columnCount = headerRow.getLastCellNum();
StringBuilder createTableQuery = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
.append(tableName).append(" (");
for (int i = 0; i < columnCount; i++) {
Cell cell = headerRow.getCell(i);
String columnName = cell.getStringCellValue();
createTableQuery.append(columnName).append(" VARCHAR(255), ");
}
createTableQuery.setLength(createTableQuery.length() - 2);
createTableQuery.append(")");
try (PreparedStatement createTableStmt = conn.prepareStatement(createTableQuery.toString())) {
createTableStmt.executeUpdate();
}
StringBuilder insertQuery = new StringBuilder("INSERT INTO ")
.append(tableName).append(" VALUES (");
for (int i = 0; i < columnCount; i++) {
insertQuery.append("?, ");
}
insertQuery.setLength(insertQuery.length() - 2);
insertQuery.append(")");
try (PreparedStatement insertStmt = conn.prepareStatement(insertQuery.toString())) {
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
for (int j = 0; j < columnCount; j++) {
Cell cell = row.getCell(j);
String cellValue = "";
if (cell != null) {
switch (cell.getCellType()) {
case NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case STRING:
cellValue = cell.getStringCellValue();
break;
default:
cellValue = "";
}
}
insertStmt.setString(j + 1, cellValue);
}
insertStmt.executeUpdate();
}
}
System.out.println("Excel data imported to database successfully.");
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
```
在上面的代码中:
- `jdbcUrl`是数据库的URL。
- `username`和`password`是数据库的用户名和密码。
- `filePath`是Excel文件的路径。
- `tableName`是要将数据插入的数据库表的名称。
- `createTableQuery`用于创建数据库表的SQL查询。
- `insertQuery`用于将数据插入数据库表的SQL查询。
- 对于每个工作表,我们首先创建一个包含所有列的表。然后,我们使用`PreparedStatement`将每行数据插入到表中。
请注意,上面的代码示例仅适用于包含单个工作表的Excel文件。如果您的Excel文件包含多个工作表,则需要适当修改代码。