多个sheet Excel 数据 导入数据库 如何实现?

简介: 多个sheet Excel 数据 导入数据库 如何实现?

多个sheet Excel 数据 导入数据库 如何实现?

将 Excel 文件中的多个 sheet 导入数据库,一般有以下几种实现方式:

  1. 使用 JDBC 直接插入。可以使用 Java 的 JDBC 接口直接连接数据库,然后读取 Excel 文件中的数据,并将数据插入到数据库中。这种方式比较直接,但需要编写大量的 JDBC 代码,对 Excel 文件格式的支持也比较有限。
  2. 使用第三方库。市面上有很多 Java 的第三方库可以用来读取 Excel 文件,如 Apache POI、JExcelAPI、EasyExcel 等。这些库通常都提供了比较简单易用的 API,可以方便地读取 Excel 文件中的数据,并将数据插入到数据库中。
  3. 先将 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 文件读写库进行读写。

需要根据实际情况选择合适的实现方式,综合考虑内存占用、性能、功能等因素。

结语

如果这篇文章对您有所帮助,或者有所启发的话,求一键三连:点赞、评论、收藏➕关注,您的支持是我坚持写作最大的动力。

目录
相关文章
|
7天前
|
存储 人工智能 Cloud Native
云栖重磅|从数据到智能:Data+AI驱动的云原生数据库
在9月20日2024云栖大会上,阿里云智能集团副总裁,数据库产品事业部负责人,ACM、CCF、IEEE会士(Fellow)李飞飞发表《从数据到智能:Data+AI驱动的云原生数据库》主题演讲。他表示,数据是生成式AI的核心资产,大模型时代的数据管理系统需具备多模处理和实时分析能力。阿里云瑶池将数据+AI全面融合,构建一站式多模数据管理平台,以数据驱动决策与创新,为用户提供像“搭积木”一样易用、好用、高可用的使用体验。
云栖重磅|从数据到智能:Data+AI驱动的云原生数据库
|
30天前
|
数据采集 存储 JavaScript
自动化数据处理:使用Selenium与Excel打造的数据爬取管道
本文介绍了一种使用Selenium和Excel结合代理IP技术从WIPO品牌数据库(branddb.wipo.int)自动化爬取专利信息的方法。通过Selenium模拟用户操作,处理JavaScript动态加载页面,利用代理IP避免IP封禁,确保数据爬取稳定性和隐私性。爬取的数据将存储在Excel中,便于后续分析。此外,文章还详细介绍了Selenium的基本设置、代理IP配置及使用技巧,并探讨了未来可能采用的更多防反爬策略,以提升爬虫效率和稳定性。
|
1月前
|
SQL 关系型数据库 MySQL
数据库导入SQL文件:全面解析与操作指南
在数据库管理中,将SQL文件导入数据库是一个常见且重要的操作。无论是迁移数据、恢复备份,还是测试和开发环境搭建,掌握如何正确导入SQL文件都至关重要。本文将详细介绍数据库导入SQL文件的全过程,包括准备工作、操作步骤以及常见问题解决方案,旨在为数据库管理员和开发者提供全面的操作指南。一、准备工作在导
172 0
|
9天前
|
SQL 关系型数据库 数据库
国产数据实战之docker部署MyWebSQL数据库管理工具
【10月更文挑战第23天】国产数据实战之docker部署MyWebSQL数据库管理工具
46 4
国产数据实战之docker部署MyWebSQL数据库管理工具
|
7天前
|
关系型数据库 分布式数据库 数据库
云栖大会|从数据到决策:AI时代数据库如何实现高效数据管理?
在2024云栖大会「海量数据的高效存储与管理」专场,阿里云瑶池讲师团携手AMD、FunPlus、太美医疗科技、中石化、平安科技以及小赢科技、迅雷集团的资深技术专家深入分享了阿里云在OLTP方向的最新技术进展和行业最佳实践。
|
27天前
|
数据处理 Python
Python实用记录(十):获取excel数据并通过列表的形式保存为txt文档、xlsx文档、csv文档
这篇文章介绍了如何使用Python读取Excel文件中的数据,处理后将其保存为txt、xlsx和csv格式的文件。
44 3
Python实用记录(十):获取excel数据并通过列表的形式保存为txt文档、xlsx文档、csv文档
|
6天前
|
Java API Apache
|
14天前
|
人工智能 Cloud Native 容灾
云数据库“再进化”,OB Cloud如何打造云时代的数据底座?
云数据库“再进化”,OB Cloud如何打造云时代的数据底座?
|
22天前
|
SQL 存储 关系型数据库
数据储存数据库管理系统(DBMS)
【10月更文挑战第11天】
73 3
|
29天前
|
easyexcel Java UED
SpringBoot中大量数据导出方案:使用EasyExcel并行导出多个excel文件并压缩zip后下载
在SpringBoot环境中,为了优化大量数据的Excel导出体验,可采用异步方式处理。具体做法是将数据拆分后利用`CompletableFuture`与`ThreadPoolTaskExecutor`并行导出,并使用EasyExcel生成多个Excel文件,最终将其压缩成ZIP文件供下载。此方案提升了导出效率,改善了用户体验。代码示例展示了如何实现这一过程,包括多线程处理、模板导出及资源清理等关键步骤。