Excel导入数据库的Java代码
时间: 2024-05-08 13:16:24 浏览: 13
在导入Excel数据到数据库时,首先需要选择一个合适的 Java 框架或工具,以实现读取和解析 Excel 文件。一些常用的选择包括 Apache POI、JExcelAPI 和 OpenXLS。
以 Apache POI 为例,下面是一个简单的 Java 代码,用于从 Excel 文件读取数据并将其导入到数据库:
```
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelToDatabase {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname", "username", "password");
PreparedStatement statement = connection.prepareStatement("INSERT INTO tablename (column1, column2, column3) VALUES (?, ?, ?)");
FileInputStream file = new FileInputStream("file.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case NUMERIC:
statement.setDouble(cell.getColumnIndex() + 1, cell.getNumericCellValue());
break;
case STRING:
statement.setString(cell.getColumnIndex() + 1, cell.getStringCellValue());
break;
}
}
statement.executeUpdate();
}
file.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
```
该代码使用了 Apache POI 库,用于读取 Excel 文件并将数据导入到 MySQL 数
阅读全文