Java实现Excel导入数据库
下面我将介绍几种在Java中实现Excel导入数据库的常用方法,包括使用Apache POI和EasyExcel等库。
（图片来源网络，侵删）
使用Apache POI
Apache POI是Java操作Office文档最常用的库之一。
添加依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
实现代码
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class ExcelImportToDatabase {
public static void main(String[] args) {
String excelFilePath = "path/to/your/excel/file.xlsx";
String dbUrl = "jdbc:mysql://localhost:3306/your_database";
String dbUser = "username";
String dbPassword = "password";
try {
List<List<String>> data = readExcel(excelFilePath);
importToDatabase(data, dbUrl, dbUser, dbPassword);
System.out.println("数据导入成功！");
} catch (Exception e) {
e.printStackTrace();
}
}
// 读取Excel文件
public static List<List<String>> readExcel(String filePath) throws Exception {
List<List<String>> data = new ArrayList<>();
try (InputStream is = ExcelImportToDatabase.class.getResourceAsStream(filePath);
Workbook workbook = new XSSFWorkbook(is)) {
Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
for (Row row : sheet) {
List<String> rowData = new ArrayList<>();
for (Cell cell : row) {
switch (cell.getCellType()) {
case STRING:
rowData.add(cell.getStringCellValue());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
rowData.add(cell.getDateCellValue().toString());
} else {
rowData.add(String.valueOf(cell.getNumericCellValue()));
}
break;
case BOOLEAN:
rowData.add(String.valueOf(cell.getBooleanCellValue()));
break;
case FORMULA:
rowData.add(cell.getCellFormula());
break;
default:
rowData.add("");
}
}
data.add(rowData);
}
}
return data;
}
// 导入数据库
public static void importToDatabase(List<List<String>> data, String dbUrl, String dbUser, String dbPassword)
throws SQLException {
// 假设我们要导入到名为user的表中，表结构为(id, name, age)
String sql = "INSERT INTO user (id, name, age) VALUES (?, ?, ?)";
try (Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
// 跳过表头（第一行）
for (int i = 1; i < data.size(); i++) {
List<String> row = data.get(i);
pstmt.setInt(1, Integer.parseInt(row.get(0)));
pstmt.setString(2, row.get(1));
pstmt.setInt(3, Integer.parseInt(row.get(2)));
pstmt.addBatch();
// 每执行1000条提交一次
if (i % 1000 == 0) {
pstmt.executeBatch();
}
}
// 执行剩余的批次
pstmt.executeBatch();
}
}
}
使用EasyExcel
EasyExcel是阿里巴巴开源的Excel处理工具,相比POI更节省内存。
添加依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
实现代码
首先创建一个实体类映射Excel数据：
import com.alibaba.excel.annotation.ExcelProperty;
public class UserData {
@ExcelProperty("ID")
private Integer id;
@ExcelProperty("姓名")
private String name;
@ExcelProperty("年龄")
private Integer age;
// getters and setters
}
然后实现导入功能：
（图片来源网络，侵删）
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
public class EasyExcelImportDemo {
public static void main(String[] args) {
String excelFilePath = "path/to/your/excel/file.xlsx";
String dbUrl = "jdbc:mysql://localhost:3306/your_database";
String dbUser = "username";
String dbPassword = "password";
// 读取Excel数据
List<UserData> dataList = new ArrayList<>();
EasyExcel.read(excelFilePath, UserData.class, new AnalysisEventListener<UserData>() {
@Override
public void invoke(UserData data, AnalysisContext context) {
dataList.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
System.out.println("Excel读取完成");
}
}).sheet().doRead();
// 导入数据库
try {
importToDatabase(dataList, dbUrl, dbUser, dbPassword);
System.out.println("数据导入成功！");
} catch (Exception e) {
e.printStackTrace();
}
}
public static void importToDatabase(List<UserData> dataList, String dbUrl, String dbUser, String dbPassword)
throws Exception {
String sql = "INSERT INTO user (id, name, age) VALUES (?, ?, ?)";
try (Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
for (UserData data : dataList) {
pstmt.setInt(1, data.getId());
pstmt.setString(2, data.getName());
pstmt.setInt(3, data.getAge());
pstmt.addBatch();
// 每执行1000条提交一次
if (dataList.size() > 1000 && dataList.indexOf(data) % 1000 == 0) {
pstmt.executeBatch();
}
}
pstmt.executeBatch();
}
}
}
使用Spring Boot集成EasyExcel
如果使用Spring Boot，可以更方便地实现Excel导入。
添加依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
实现代码
Controller层：
import com.alibaba.excel.EasyExcel;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@RestController
@RequestMapping("/excel")
public class ExcelController {
@PostMapping("/import")
public String importExcel(@RequestParam("file") MultipartFile file) {
try {
List<UserData> dataList = EasyExcel.read(file.getInputStream())
.head(UserData.class)
.sheet()
.doReadSync();
// 调用服务层处理数据
userService.batchSave(dataList);
return "导入成功";
} catch (IOException e) {
e.printStackTrace();
return "导入失败: " + e.getMessage();
}
}
}
Service层：
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
import java.util.List;
@Service
public class UserService {
@Resource
private UserRepository userRepository;
@Transactional
public void batchSave(List<UserData> dataList) {
List<User> users = dataList.stream()
.map(data -> new User(data.getId(), data.getName(), data.getAge()))
.collect(Collectors.toList());
userRepository.saveAll(users);
}
}
注意事项
- 数据验证：在导入前应对Excel数据进行验证，确保数据格式正确。
- 批量处理：使用批量插入可以提高性能，避免单条插入导致的性能问题。
- 事务管理：确保在导入过程中使用事务，保证数据一致性。
- 错误处理：添加适当的错误处理机制，记录导入失败的数据。
- 内存管理：对于大文件，使用EasyExcel等流式处理工具可以避免内存溢出。
- 表头处理：注意跳过Excel中的表头行（如果不需要导入）。
方法可以根据实际需求选择使用,EasyExcel在处理大文件时性能更优，而Apache POI功能更全面。
（图片来源网络，侵删）
