写一个excel入库到mysql的java程序
时间: 2024-05-14 14:14:50 浏览: 17
以下是一个简单的Excel入库到MySQL的Java程序,使用Apache POI和JDBC连接数据库:
```
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.ss.usermodel.WorkbookFactory;
public class ExcelToMySQL {
public static void main(String[] args) {
String excelFilePath = "path/to/excel/file.xlsx";
String url = "jdbc:mysql://localhost:3306/test";
String username = "username";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, username, password)) {
String sql = "INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)";
PreparedStatement statement = conn.prepareStatement(sql);
FileInputStream inputStream = new FileInputStream(excelFilePath);
Workbook workbook = WorkbookFactory.create(inputStream);
Sheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
Cell cell1 = row.getCell(0);
Cell cell2 = row.getCell(1);
Cell cell3 = row.getCell(2);
statement.setString(1, cell1.getStringCellValue());
statement.setDouble(2, cell2.getNumericCellValue());
statement.setString(3, cell3.getStringCellValue());
statement.executeUpdate();
}
workbook.close();
inputStream.close();
System.out.println("Data imported successfully!");
} catch (SQLException | IOException e) {
e.printStackTrace();
}
}
}
```
注意要替换程序中的excelFilePath、url、username、password、table_name和column1/2/3为实际值。程序只支持读取第一个sheet的前三列数据,可以根据需要进行修改。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)