Java查询SQL Server数据生成Excel文件并导出(支持百万级数据导出)

于 2024-12-20 14:22:44 发布
阅读量739 收藏 5
点赞数 25
分类专栏： Java 文章标签： java excel 开发语言
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/shenjqiang/article/details/144580839
版权

要在 Java 中查询 SQL Server 数据并将结果生成 Excel 文件并导出，你可以使用以下步骤和库：
1.    JDBC 连接到 SQL Server:
•    使用 JDBC 驱动程序连接到 SQL Server 数据库。
2.    Apache POI 库:
•    使用 Apache POI 库来创建和操作 Excel 文件。
步骤详解


1. 添加依赖


首先，在你的项目中添加所需的依赖项。如果你使用的是 Maven，可以在 pom.xml 中添加以下依赖：

<!-- JDBC Driver for SQL Server -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>9.4.0.jre8</version>
</dependency>

<!-- Apache POI for Excel -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

如果你使用的是 Gradle，可以在 build.gradle 中添加以下依赖：

dependencies {
    implementation 'com.microsoft.sqlserver:mssql-jdbc:9.4.0.jre8'
    implementation 'org.apache.poi:poi-ooxml:5.2.3'
}

2. 编写 Java 代码（数据量较少）


以下是一个完整的 Java 示例代码，展示如何连接到 SQL Server 数据库，查询数据，并将结果生成 Excel 文件并导出（数据量较少）。

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class SqlServerToExcel {

    // 数据库连接信息
    private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=YourDatabaseName";
    private static final String USER = "yourUsername";
    private static final String PASSWORD = "yourPassword";

    // SQL 查询语句
    private static final String QUERY = "SELECT * FROM YourTableName";

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Data");

        try {
            // 1. 连接到数据库
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = connection.createStatement();
            resultSet = statement.executeQuery(QUERY);

            // 2. 获取元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 3. 创建标题行
            Row headerRow = sheet.createRow(0);
            for (int i = 1; i <= columnCount; i++) {
                Cell cell = headerRow.createCell(i - 1);
                cell.setCellValue(metaData.getColumnName(i));
            }

            // 4. 填充数据行
            int rowNum = 1;
            while (resultSet.next()) {
                Row row = sheet.createRow(rowNum++);
                for (int i = 1; i <= columnCount; i++) {
                    Cell cell = row.createCell(i - 1);
                    Object value = resultSet.getObject(i);
                    if (value instanceof Number) {
                        cell.setCellValue(((Number) value).doubleValue());
                    } else {
                        cell.setCellValue(value.toString());
                    }
                }
            }

            // 5. 自动调整列宽
            for (int i = 0; i < columnCount; i++) {
                sheet.autoSizeColumn(i);
            }

            // 6. 写入文件
            try (FileOutputStream fileOut = new FileOutputStream("data.xlsx")) {
                workbook.write(fileOut);
            }

            System.out.println("Excel 文件已生成: data.xlsx");

        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
                if (workbook != null) workbook.close();
            } catch (SQLException | IOException e) {
                e.printStackTrace();
            }
        }
    }
}

3. 编写 Java 代码（数据量较大）

导出百万级别的数据时，性能和内存管理变得尤为重要。以下是优化后的代码，旨在提高效率并减少内存占用。主要优化点包括：
1.    分页查询: 使用分页查询来避免一次性加载大量数据到内存中。
2.    流式写入: 使用 Apache POI 的 SXSSF（Streaming Usermodel API）来处理大数据量，SXSSF 是专门设计用于处理大数据量的流式 API。
3.    批量写入: 减少 I/O 操作次数，提高写入效率。
4.    资源管理: 更好的资源管理和释放。
以下是优化后的代码示例：

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class SqlServerToExcelOptimized {

    // 数据库连接信息
    private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=YourDatabaseName";
    private static final String USER = "yourUsername";
    private static final String PASSWORD = "yourPassword";

    // SQL 查询语句（带分页）
    private static final String QUERY = "SELECT * FROM YourTableName ORDER BY SomeColumn OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";

    // 分页大小
    private static final int PAGE_SIZE = 10000;

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        SXSSFWorkbook workbook = new SXSSFWorkbook(100); // 保留100行在内存中，其余行写入临时文件
        Sheet sheet = workbook.createSheet("Data");

        try {
            // 1. 连接到数据库
            connection = DriverManager.getConnection(URL, USER, PASSWORD);

            // 2. 创建标题行
            preparedStatement = connection.prepareStatement("SELECT TOP 1 * FROM YourTableName");
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            Row headerRow = sheet.createRow(0);
            for (int i = 1; i <= columnCount; i++) {
                Cell cell = headerRow.createCell(i - 1);
                cell.setCellValue(metaData.getColumnName(i));
            }

            // 3. 分页查询并填充数据
            int offset = 0;
            int totalRows = 0;
            do {
                preparedStatement = connection.prepareStatement(QUERY);
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(2, PAGE_SIZE);
                resultSet = preparedStatement.executeQuery();

                while (resultSet.next()) {
                    Row row = sheet.createRow(++totalRows);
                    for (int i = 1; i <= columnCount; i++) {
                        Cell cell = row.createCell(i - 1);
                        Object value = resultSet.getObject(i);
                        if (value instanceof Number) {
                            cell.setCellValue(((Number) value).doubleValue());
                        } else {
                            cell.setCellValue(value == null ? "" : value.toString());
                        }
                    }
                }
                offset += PAGE_SIZE;
            } while (resultSet.getRow() > 0);

            // 4. 写入文件
            try (FileOutputStream fileOut = new FileOutputStream("data_large.xlsx")) {
                workbook.write(fileOut);
            }

            System.out.println("Excel 文件已生成: data_large.xlsx");

        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } finally {
            // 清理临时文件
            workbook.dispose();

            // 关闭资源
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

4.代码说明


1.    分页查询:


•    使用 OFFSET ... FETCH NEXT ... ROWS ONLY 进行分页查询，每次查询 PAGE_SIZE 条记录。
•    offset 用于指定查询的起始位置，PAGE_SIZE 用于指定每页的记录数。


2.    SXSSFWorkbook:


•    使用 SXSSFWorkbook 来处理大数据量。SXSSFWorkbook 是 Apache POI 提供的一个流式 API，适用于处理大数据量的情况。
•    new SXSSFWorkbook(100) 表示保留 100 行在内存中，其余行写入临时文件，以减少内存占用。


3.    标题行:


•    使用 SELECT TOP 1 * FROM YourTableName 查询一条记录来获取列信息，并创建标题行。


4.    分页循环:


•    使用 do-while 循环进行分页查询，直到没有更多记录为止。
•    每次查询 PAGE_SIZE 条记录，并将其写入 Excel 文件。


5.    写入文件:


•    使用 FileOutputStream 将工作簿写入文件 data_large.xlsx。


6.    资源管理:


•    在 finally 块中关闭所有打开的资源，包括 ResultSet, PreparedStatement, Connection，并调用 workbook.dispose() 清理临时文件。


运行代码确保你的项目中包含了上述依赖项，然后运行 SqlServerToExcelOptimized 类的 main 方法。运行后，会在项目的根目录下生成一个名为 data_large.xlsx 的 Excel 文件，其中包含从 SQL Server 查询到的数据

java查询数据导出excel并返回给浏览器下载
隔山看海
12-15 4万+
效果图： 1.点击导出表按钮 2.接着就会出现下图 3.点击上图中的确定按钮再接着就会出现下图 4.点击上图中的保存按钮接着就会出现下图，浏览器下载完成后的提示 5.打开下载好的文件如下图 好了，废话不多少，上代码 jsp前端代码 &lt;div style="height:30px;"&gt; &lt;a&gt;时间：&lt;/a&gt; ...
从数据库查询数据 写入Excel 写出JAVA代码
最新发布
huanghm88的专栏
07-19 525
更改为您的数据库连接信息，并确保正确加载数据库驱动程序。此外，还需要包含Apache POI库以处理Excel文件。请注意，上述代码需要将。
百万级数据在Excel和Sql数据库之间相互导入、导出
08-02
百万级数据在Excel和SqlServer之间相互导入导出。
数据库大量数据导出Excel
09-17
应客户要求，导出数据库中的数据为Excel，只在一个Sheet中，Excel中的内容要与数据库里的内容一致（主要针对日期型数据）。 由于之前接触过POI，对POI的一些特性还是有一定的了解的，因此顺其自然的用POI去解决这个问题。POI3.8版本之前的版本处理大量数据的导出Excel效果不是很理想，主要在与Excel2003版本单个Sheet的行限制为65536，大量数据的导出得分多个Sheet，针对这一点，客户就不会满意。其次，在实验过程中，大数据量的导出很容易引发内存溢出，调整JVM的内存大小治标不治本。很多人建议保存为.CSV格式的文件。不过，.CSV方式导出也存在问题：首先，如果用excel来打开csv，超过65536行的数据都会看不见，这是Excel程序的问题。其次，如果要导出一个身份证号码，手机号码等纯数字构成的字符串，在excel中打开csv时，这些字段很容易被识别成数字，被误处理。 POI3.8以后的版本支持Excel2007高版本，单个Sheet的行数可达到百万，针对内存溢出问题，可通过设置内存数据保留数，每当计数到指定的数值时，刷新数据到硬盘,清理内存。
java excel导出
qq_44936245的博客
02-01 444
需求须要做一个将sql查出来的数据导出为excel的功能，问了一下chat，给了我一个方案，是使用EasyExcel，试了一下用不起来，各种报错。纠结了一阵，换了个方法，自己写写excel的逻辑。
SQL Server导出百万级数据
qq_41978323的博客
11-12 1807
SQL Server导出百万级数据 每次查询10 条数据，分页导出，100万条数据只需要导10次出来。 控制（10-1）就行，从10到1导出10次。 SELECT * FROM ( 　　SELECT top 100000 * -- 查询100000数据 FROM 表A t WHERE t.字段A NOT IN ( SELECT top ( ( 10-1 ) * 100000 ) 字段A FROM 表A ) 　　　　 ) AS b ORDER BY 字段A DESC
Java·Excel导出百万级数据
qq_43308318的博客
05-14 5738
Java·Excel导出百万级数据POI依赖介绍CSV格式介绍JXL依赖介绍使用依赖上下文配置Excel2003格式导出Excel2007格式CSV文本格式使用JXL依赖导出Excel POI依赖介绍 excel对行列的限制 | excel2003 | 65536 | 256| | excel2007 | 1048576 | 16384 | 1. Excel2003格式（java对象使用HSSFWorkbook） Excel2003支持每个工作表中最多有 65536 行和 256列。对于工作表能够支持的最大
java实现JSP表格数据导出到EXCEL文件
01-07
在Java Web开发中，将JSP表格数据导出到Excel文件是一种常见的需求，这有助于用户对大量数据进行处理、分析和存储。以下是如何使用Java来实现这一功能的关键知识点： 1. **JSP（JavaServer Pages）**: JSP是Java的...
掌握Excel与SQL Server数据导入导出技巧
- **使用T-SQL导出**：可以编写T-SQL脚本，通过查询SQL Server数据库中的数据，并使用BULK INSERT语句将结果导出到一个新创建的Excel文件中。 - **使用SQL Server Management Objects (SMO)**：SMO提供了一种编程...
NPOI实现SQL Server数据字典Excel导出工具
在本例中，作者提到了在导出数据字典时使用了NPOI，这说明他/她可能使用了NPOI库来读取数据并将其导入到Excel文件中。NPOI的主要功能包括： - **创建和编辑Word和Excel文件**：可以创建、修改、读取和写入Word文档...
java--把sql查询出来的结果导出到excel中
07-17
java--把sql查询出来的结果导出到excel中，导出功能，excel导出
使用HSSFWorkbook并简单对数据分批查询完成导出excel功能（java实现）
suchen0440的博客
04-28 2266
##需求及思路详解 根据产品及需求，我们需要实现对采购品各分类下的采购品进行导出功能。 （可能会出现的问题）：①因为要对采购品各分类下采购品进行导出excel文件的功能，所以要考虑到根目录及子目录下采购品数量及导出的不同。②因为dba对数据要求每次查询不大于8mb,所以要对查询数据进行分批查询。③：文件名乱码问题。④：导入所需jar包到pom文件，因博主是由公司自己封装的esaypoi框架，故在此...
JAVA 中使用 SQL 语句查询 EXCEL 文件数据
Sxb_Cry5tal的博客
07-01 424
有些业务数据保存在EXCEL文件中，怎么在JAVA应用程序里用SQL语句来查询这些数据，以便进行进一步的计算呢？ SQL 语言是基于数据库的查询语言，被查询的数据只能位于数据库中，所以通常的做法是先在数据库中创建临时表，再将EXCEL数据导入后用SQL语句来查询，最后再删除临时表。说起来简单，但是建表、读取EXCEL数据、导入数据库、SQL查询，每一步都意味着大量的编程工作。而且因为EXCEL文件的格式不同，查询需求不同，编写的程序并不能通用，下次还需重新编写程序。 而集算器SPL提供了这种查询功能，完
sql server导出大批量数据
weixin_34232363的博客
01-03 2034
使用sqlserver导出数据的时候,如果数据量大于65536那么就要使用xlsx,最大行数为104万   如果导出的时候报错,则需要在本机安装以下程序:   https://www.cnblogs.com/jinianjun/archive/2011/12/05/2276679.html...
java sql 查询 导出到excel_java将sql数据导出到excel中?
weixin_42361018的博客
02-25 765
importjava.io.File;importjxl.Workbook;importjxl.write.Label;importjxl.write.WritableSheet;importjxl.write.WritableWorkbook;importjava.sql.*;importjavax.swing.*;importjava...import java.io.File;import...
java查询SQL Server数据库条数
yin_xing_ye的博客
11-21 3339
下面来说一下如何查询数据库中的一个表中的数据总条数 连接数据库的方法就不写了 这是主要方法 Connection conn; Statement st; ResultSet rs; conn=DButil.getConnection(); String sql="select count(*) from tablename"; st=con...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Favor_Yang

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值