将 Excel 文件中的多个 sheet 导入数据库,一般有以下几种实现方式:
无论使用哪种方式,都需要注意以下几个问题:
Excel 文件格式的兼容性问题。不同版本的 Excel 文件可能存在格式差异,需要进行测试和兼容性处理。
数据的类型和格式问题。Excel 文件中的数据类型和格式可能需要进行转换和处理,以适配数据库中的数据类型和格式要求。
数据的一致性问题。如果 Excel 文件中的数据有重复或冲突,需要进行处理,以保证数据的一致性和完整性。
综上所述,将 Excel 文件中的多个 sheet 导入数据库的实现方式有多种,具体使用哪种方式,还需要根据实际情况进行评估和选择。
处理 普通数据的 Excel 文件,需要考虑到内存和性能的问题,以下是一个基于流式读取和写入的示例代码:
// 获取 Excel 文件输入流
InputStream is = new BufferedInputStream(new FileInputStream(filePath));
Workbook workbook = WorkbookFactory.create(is);
// 遍历每个 Sheet
for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
String sheetName = sheet.getSheetName();
System.out.println("开始处理 Sheet:" + sheetName);
// 准备写入的输出流
OutputStream os = new BufferedOutputStream(new FileOutputStream(outputDir + "/" + sheetName + ".xlsx"));
// 设置写入的 Sheet 名称
SXSSFWorkbook writer = new SXSSFWorkbook(new XSSFWorkbook(), 10000);
SXSSFSheet outSheet = writer.createSheet(sheetName);
// 读取并写入 Sheet 的标题行
Row titleRow = sheet.getRow(0);
Row outTitleRow = outSheet.createRow(0);
for (int i = 0; i < titleRow.getLastCellNum(); i++) {
outTitleRow.createCell(i).setCellValue(titleRow.getCell(i).getStringCellValue());
}
// 逐行读取并写入数据
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
Row outRow = outSheet.createRow(i);
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
if (cell != null) {
switch (cell.getCellType()) {
case BLANK:
outRow.createCell(j, CellType.BLANK);
break;
case BOOLEAN:
outRow.createCell(j, CellType.BOOLEAN).setCellValue(cell.getBooleanCellValue());
break;
case ERROR:
outRow.createCell(j, CellType.ERROR).setCellValue(cell.getErrorCellValue());
break;
case FORMULA:
outRow.createCell(j, CellType.FORMULA).setCellFormula(cell.getCellFormula());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
outRow.createCell(j, CellType.NUMERIC).setCellValue(cell.getDateCellValue());
} else {
outRow.createCell(j, CellType.NUMERIC).setCellValue(cell.getNumericCellValue());
}
break;
case STRING:
outRow.createCell(j, CellType.STRING).setCellValue(cell.getStringCellValue());
break;
default:
outRow.createCell(j, CellType.BLANK);
break;
}
}
}
// 每隔 10000 行进行一次缓存写入
if (i % 10000 == 0) {
((SXSSFSheet) outSheet).flushRows();
}
}
// 最后写入缓存的数据
writer.write(os);
os.flush();
os.close();
writer.dispose();
System.out.println("处理 Sheet:" + sheetName + " 完成");
}
// 关闭输入流
is.close();
上述示例代码使用了 Apache POI 的流式读取和写入方式,可以有效地处理大量数据。为了避免内存溢出,采用了缓存写入的方式,每隔一定数量的行进行一次写入操作。
使用 Apache POI 实现将 Excel 文件中的多个 sheet 导入到数据库的 Java 代码:
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 ExcelImporter {
private static final String DB_URL = "jdbc:mysql://localhost:3306/mydatabase";
private static final String DB_USER = "myuser";
private static final String DB_PASSWORD = "mypassword";
private static final String INSERT_SQL = "INSERT INTO mytable (col1, col2, col3) VALUES (?, ?, ?)";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
FileInputStream file = new FileInputStream("myexcel.xlsx");
Workbook workbook = new XSSFWorkbook(file);
int numSheets = workbook.getNumberOfSheets();
for (int i = 0; i < numSheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
for (Row row : sheet) {
String col1 = null;
String col2 = null;
int col3 = 0;
for (Cell cell : row) {
int columnIndex = cell.getColumnIndex();
switch (columnIndex) {
case 0:
col1 = cell.getStringCellValue();
break;
case 1:
col2 = cell.getStringCellValue();
break;
case 2:
col3 = (int) cell.getNumericCellValue();
break;
default:
// Ignore other columns
break;
}
}
PreparedStatement statement = conn.prepareStatement(INSERT_SQL);
statement.setString(1, col1);
statement.setString(2, col2);
statement.setInt(3, col3);
statement.executeUpdate();
}
}
System.out.println("Import successful");
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
在上面的代码中,首先通过 FileInputStream 和 Workbook 对象读取 Excel 文件中的数据,然后通过 for 循环遍历每个 sheet 和每行数据,并将数据插入到数据库中。在读取单元格数据时,可以根据单元格的列索引和数据类型进行类型转换和赋值。最后通过 PreparedStatement 执行 SQL 插入语句,将数据插入到数据库中。
需要注意的是,上面的代码只是一个简单的示例,还需要根据实际情况进行修改和完善,比如加入异常处理、事务管理等功能。
使用 JExcelAPI 实现将 Excel 文件中的多个 sheet 导入到数据库的 Java 代码:
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
public class ExcelImporter {
private static final String DB_URL = "jdbc:mysql://localhost:3306/mydatabase";
private static final String DB_USER = "myuser";
private static final String DB_PASSWORD = "mypassword";
private static final String INSERT_SQL = "INSERT INTO mytable (col1, col2, col3) VALUES (?, ?, ?)";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
Workbook workbook = Workbook.getWorkbook(new File("myexcel.xls"));
int numSheets = workbook.getNumberOfSheets();
for (int i = 0; i < numSheets; i++) {
Sheet sheet = workbook.getSheet(i);
for (int j = 1; j < sheet.getRows(); j++) {
String col1 = null;
String col2 = null;
int col3 = 0;
for (int k = 0; k < sheet.getColumns(); k++) {
Cell cell = sheet.getCell(k, j);
switch (k) {
case 0:
col1 = cell.getContents();
break;
case 1:
col2 = cell.getContents();
break;
case 2:
col3 = Integer.parseInt(cell.getContents());
break;
default:
// Ignore other columns
break;
}
}
PreparedStatement statement = conn.prepareStatement(INSERT_SQL);
statement.setString(1, col1);
statement.setString(2, col2);
statement.setInt(3, col3);
statement.executeUpdate();
}
}
System.out.println("Import successful");
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
在上面的代码中,首先通过 Workbook 对象读取 Excel 文件中的数据,然后通过 for 循环遍历每个 sheet 和每行数据,并将数据插入到数据库中。在读取单元格数据时,可以根据单元格的行索引、列索引和数据类型进行类型转换和赋值。最后通过 PreparedStatement 执行 SQL 插入语句,将数据插入到数据库中。
需要注意的是,上面的代码只是一个简单的示例,还需要根据实际情况进行修改和完善,比如加入异常处理、事务管理等功能。另外,JExcelAPI 只支持旧版的 .xls 格式,不支持 .xlsx 格式
。
使用 EasyExcel 实现将 Excel 文件中的多个 sheet 导入到数据库的 Java 代码:
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.Sheet;
import java.util.ArrayList;
import java.util.List;
public class ExcelImporter {
private static final String DB_URL = "jdbc:mysql://localhost:3306/mydatabase";
private static final String DB_USER = "myuser";
private static final String DB_PASSWORD = "mypassword";
private static final String INSERT_SQL = "INSERT INTO mytable (col1, col2, col3) VALUES (?, ?, ?)";
public static void main(String[] args) {
List<List<Object>> data = new ArrayList<>();
EasyExcel.read("myexcel.xlsx", new MyEventListener()).sheet().doRead();
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
PreparedStatement statement = conn.prepareStatement(INSERT_SQL);
for (List<Object> row : data) {
statement.setString(1, (String) row.get(0));
statement.setString(2, (String) row.get(1));
statement.setInt(3, (Integer) row.get(2));
statement.addBatch();
}
statement.executeBatch();
System.out.println("Import successful");
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
static class MyEventListener extends AnalysisEventListener<Object> {
private List<Object> row = new ArrayList<>();
@Override
public void invoke(Object data, AnalysisContext context) {
row.add(data);
if (context.getCurrentRowNum() == 0) {
// Ignore the header row
row.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// Ignore
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// Ignore
}
}
}
在上面的代码中,首先通过 EasyExcel 对象读取 Excel 文件中的数据,然后通过 AnalysisEventListener 监听器将每行数据存储到一个 List 中,最后将 List 中的数据插入到数据库中。需要注意的是,在处理每行数据时,需要根据数据类型进行类型转换和赋值。此外,EasyExcel 支持 .xlsx 和 .xls 格式的 Excel 文件,但由于 .xlsx 格式的文件在读取时需要占用大量内存,因此建议在处理大量数据时使用 .xls 格式。
需要注意的是,上面的代码只是一个简单的示例,还需要根据实际情况进行修改和完善,比如加入异常处理、事务管理等功能。另外,EasyExcel 还提供了很多高级功能,比如读取大量数据时的分页读取、读取时的数据转换和验证等。可以根据实际需求进行使用。
除了使用 Apache POI 和 EasyExcel 这两个库之外,还有其他的实现方式,比如:
使用 OpenCSV:OpenCSV 是一个轻量级的 CSV 格式文件读写库,也支持读写 Excel 文件。与 Apache POI 相比,它的内存占用更少,但功能相对较少。
使用 JExcelAPI:JExcelAPI 是一个老牌的 Java Excel 文件读写库,也支持读写多个 sheet。与 Apache POI 相比,它的内存占用更少,但功能相对较少。
使用 Excel Streaming Reader:Excel Streaming Reader 是一个基于 SAX 的 Excel 文件读取库,能够高效地读取大型 Excel 文件。与 Apache POI 相比,它的内存占用更少,但功能相对较少。
使用 CSV 文件代替 Excel 文件:如果数据量不是很大,并且不需要使用 Excel 特有的功能,可以将 Excel 文件转换为 CSV 格式文件,然后使用 OpenCSV 或其他的 CSV 文件读写库进行读写。
需要根据实际情况选择合适的实现方式,综合考虑内存占用、性能、功能等因素。
如果这篇文章对您有所帮助,或者有所启发的话,求一键三连:点赞、评论、收藏➕关注,您的支持是我坚持写作最大的动力。