SQL 使用 SSIS 将数据导出到 Excel 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14109622/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export data to Excel file using SSIS
提问by Shaggy
I have SSIS Package which Exports Data from table to Excel file
我有将数据从表导出到 Excel 文件的 SSIS 包
Control Flow :-
控制流 ：-
Data Flow :-
数据流 ：-
This is My Step :-
这是我的步骤：-
- Drop Excel Table
- Create Excel Table with format as of my Select Query which i used to retrieve data from database
- Insert Data from Database to Excel file
- 删除 Excel 表格
- 创建 Excel 表格，格式与我用来从数据库中检索数据的选择查询相同
- 将数据从数据库插入 Excel 文件
I Used Query Like Select * From Table Where --Some Condition
我用过 Query Like Select * From Table Where --Some Condition
I retrieve 3000 rows out of 10000 rows and put that 3000 rows in my excel sheet. But when open my excel sheet i saw scrollbar which goes till 10000th row and ends hence my excel sheet size also increses . how can i reduce my excel sheet size ? my excel sheet contains only 3000 rows then why blank cells which goes till 10000th row ?
我从 10000 行中检索了 3000 行，并将那 3000 行放入我的 Excel 工作表中。但是当打开我的 excel 表时，我看到滚动条一直到第 10000 行并结束，因此我的 excel 表大小也增加了。我怎样才能减少我的 Excel 工作表的大小？我的 excel 表只包含 3000 行，那么为什么空白单元格一直到第 10000 行？
SQL Server 2008 & Visual Studio 2008 with BIDS
带有 BIDS 的 SQL Server 2008 和 Visual Studio 2008
回答by
I believe your issue is around the method in which you are using to create the file. You have two alternatives and both should fix your issue:
我相信您的问题与您用来创建文件的方法有关。您有两种选择，都可以解决您的问题：
Solution #1:
解决方案#1：
You can create an Excel file with those predefined columns, essentially your empty output file - this would act as your 'Template File'. Your flow would then be this:
您可以使用这些预定义的列创建一个 Excel 文件，本质上是您的空输出文件 - 这将充当您的“模板文件”。您的流程将是这样的：
- File System Task - Copy template file to output or working directory (rename if necessary)
- OLEDB Source Task - Query your source for the data (3000)
- Data Conversion Task
- Excel Destination Task - Put data into new Excel file
- 文件系统任务 - 将模板文件复制到输出或工作目录（必要时重命名）
- OLEDB 源任务 - 查询数据源 (3000)
- 数据转换任务
- Excel 目标任务 - 将数据放入新的 Excel 文件中
Note: You already have steps 2 thru 3 complete, you just need to make sure you are connecting to the new Excel file. Also, to clarify, step 1 is outside the Control Flow Task.
注意：您已经完成了第 2 步到第 3 步，您只需要确保连接到新的 Excel 文件即可。此外，澄清一下，第 1 步不在控制流任务中。
This way is helpful because you always have a blank and consistently formatted Excel file to copy and work with.
这种方式很有帮助，因为您始终有一个空白且格式一致的 Excel 文件可供复制和使用。
Solution #2:
解决方案#2：
The other option is to use a Script Task and create the Excel file - you could also load the data into the file in this task. This requires some basic understanding of VB.NET or C#. Basically you would need to get a XLS library (like NPOI). This is more complicated, but gives you the best functionality.
另一种选择是使用脚本任务并创建 Excel 文件 - 您也可以在此任务中将数据加载到文件中。这需要对 VB.NET 或 C# 有一些基本的了解。基本上你需要一个 XLS 库（比如NPOI）。这更复杂，但可以为您提供最佳功能。
I recommend you try solution #1 and see how that works for you.
我建议您尝试解决方案 #1，看看它对您有什么作用。
回答by praveen
Drop table SheetNamedoesn't delete the sheet instead it just deletes the row. If for the 1st time you have loaded 10K rows and then again executed the package by restricting the number of rows to 3K ,the excel file will still contain those 10K empty rows as it retains the sheet along with the empty spaces.
Drop table SheetName不会删除工作表，而只是deletes the row. 如果您第一次加载了 10K 行，然后再次通过将行数限制为 3K 来执行包，则 Excel 文件仍将包含那些 10K 空行，因为它保留了工作表和 empty spaces.
You can use script task to delete the sheet using COM obects .But for that you need to place the Excel PIA(Primary Interop Assemply) to make it visible for VSA or else create a new excel file every time the package runs
您可以使用脚本任务使用 COM obects 删除工作表。但是为此您需要放置 Excel PIA（主要互操作程序集）以使其对 VSA 可见，否则每次包运行时都创建一个新的 excel 文件
Else as suggested by Nicarus use File System Taskto delete the existing file and create a new Excel file on every execution .
Nicarus 建议使用 ElseFile System Task删除现有文件并在每次执行时创建一个新的 Excel 文件。
Diagram :
图表 ：
File System Task :
文件系统任务：
Use the same components and the query for Create Tableusing Execute SQL taskand your DFT
使用相同的组件和查询Create Table使用Execute SQL task和您的DFT
