多个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 文件读写库进行读写。

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

结语

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

目录
相关文章
|
2月前
|
Python
如何根据Excel某列数据为依据分成一个新的工作表
在处理Excel数据时,我们常需要根据列值将数据分到不同的工作表或文件中。本文通过Python和VBA两种方法实现该操作:使用Python的`pandas`库按年级拆分为多个文件,再通过VBA宏按班级生成新的工作表,帮助高效整理复杂数据。
|
2月前
|
数据采集 数据可视化 数据挖掘
用 Excel+Power Query 做电商数据分析:从 “每天加班整理数据” 到 “一键生成报表” 的配置教程
在电商运营中,数据是增长的关键驱动力。然而,传统的手工数据处理方式效率低下,耗费大量时间且易出错。本文介绍如何利用 Excel 中的 Power Query 工具,自动化完成电商数据的采集、清洗与分析,大幅提升数据处理效率。通过某美妆电商的实战案例,详细拆解从多平台数据整合到可视化报表生成的全流程,帮助电商从业者摆脱繁琐操作,聚焦业务增长,实现数据驱动的高效运营。
|
12天前
|
SQL 关系型数据库 MySQL
如何将Excel表的数据导入RDS MySQL数据库?
本文介绍如何通过数据管理服务DMS将Excel文件(转为CSV格式)导入RDS MySQL数据库,涵盖建表、编码设置、导入模式选择及审批执行流程,并提供操作示例与注意事项。
|
4月前
|
存储 安全 大数据
网安工程师必看!AiPy解决fscan扫描数据整理难题—多种信息快速分拣+Excel结构化存储方案
作为一名安全测试工程师,分析fscan扫描结果曾是繁琐的手动活:从海量日志中提取开放端口、漏洞信息和主机数据,耗时又易错。但现在,借助AiPy开发的GUI解析工具,只需喝杯奶茶的时间,即可将[PORT]、[SERVICE]、[VULN]、[HOST]等关键信息智能分类,并生成三份清晰的Excel报表。告别手动整理,大幅提升效率!在安全行业,工具党正碾压手动党。掌握AiPy,把时间留给真正的攻防实战!官网链接:https://www.aipyaipy.com,解锁更多用法!
|
2月前
|
Python
将Excel特定某列数据删除
将Excel特定某列数据删除
|
7月前
|
分布式计算 Hadoop 大数据
从Excel到Hadoop:数据规模的进化之路
从Excel到Hadoop:数据规模的进化之路
147 10
|
7月前
|
存储 关系型数据库 分布式数据库
PolarDB开源数据库进阶课18 通过pg_bulkload适配pfs实现批量导入提速
本文介绍了如何修改 `pg_bulkload` 工具以适配 PolarDB 的 PFS(Polar File System),从而加速批量导入数据。实验环境依赖于 Docker 容器中的 loop 设备模拟共享存储。通过对 `writer_direct.c` 文件的修改,替换了一些标准文件操作接口为 PFS 对应接口,实现了对 PolarDB 15 版本的支持。测试结果显示,使用 `pg_bulkload` 导入 1000 万条数据的速度是 COPY 命令的三倍多。此外,文章还提供了详细的步骤和代码示例,帮助读者理解和实践这一过程。
253 0
|
9月前
|
关系型数据库 MySQL Linux
Linux下mysql数据库的导入与导出以及查看端口
本文详细介绍了在Linux下如何导入和导出MySQL数据库,以及查看MySQL运行端口的方法。通过这些操作,用户可以轻松进行数据库的备份与恢复,以及确认MySQL服务的运行状态和端口。掌握这些技能,对于日常数据库管理和维护非常重要。
368 8
|
9月前
|
存储 Java easyexcel
招行面试:100万级别数据的Excel,如何秒级导入到数据库?
本文由40岁老架构师尼恩撰写,分享了应对招商银行Java后端面试绝命12题的经验。文章详细介绍了如何通过系统化准备,在面试中展示强大的技术实力。针对百万级数据的Excel导入难题,尼恩推荐使用阿里巴巴开源的EasyExcel框架,并结合高性能分片读取、Disruptor队列缓冲和高并发批量写入的架构方案,实现高效的数据处理。此外,文章还提供了完整的代码示例和配置说明,帮助读者快速掌握相关技能。建议读者参考《尼恩Java面试宝典PDF》进行系统化刷题,提升面试竞争力。关注公众号【技术自由圈】可获取更多技术资源和指导。
|
9月前
|
数据采集 数据可视化 数据挖掘
利用Python自动化处理Excel数据:从基础到进阶####
本文旨在为读者提供一个全面的指南,通过Python编程语言实现Excel数据的自动化处理。无论你是初学者还是有经验的开发者,本文都将帮助你掌握Pandas和openpyxl这两个强大的库,从而提升数据处理的效率和准确性。我们将从环境设置开始,逐步深入到数据读取、清洗、分析和可视化等各个环节,最终实现一个实际的自动化项目案例。 ####
1674 10

热门文章

最新文章