多个sheet Excel 数据 导入数据库 如何实现?
将 Excel 文件中的多个 sheet 导入数据库,一般有以下几种实现方式:
- 使用 JDBC 直接插入。可以使用 Java 的 JDBC 接口直接连接数据库,然后读取 Excel 文件中的数据,并将数据插入到数据库中。这种方式比较直接,但需要编写大量的 JDBC 代码,对 Excel 文件格式的支持也比较有限。
- 使用第三方库。市面上有很多 Java 的第三方库可以用来读取 Excel 文件,如 Apache POI、JExcelAPI、EasyExcel 等。这些库通常都提供了比较简单易用的 API,可以方便地读取 Excel 文件中的数据,并将数据插入到数据库中。
- 先将 Excel 文件转换成 CSV 文件,再导入数据库。Excel 文件可以先转换成 CSV 文件,然后使用 JDBC 直接将数据插入到数据库中。CSV 文件相对于 Excel 文件来说,结构更加简单,处理起来也更加方便。
无论使用哪种方式,都需要注意以下几个问题:
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
使用 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
使用 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
使用 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 文件读写库进行读写。
需要根据实际情况选择合适的实现方式,综合考虑内存占用、性能、功能等因素。
结语
如果这篇文章对您有所帮助,或者有所启发的话,求一键三连:点赞、评论、收藏➕关注,您的支持是我坚持写作最大的动力。