如何根据表格数据创建动态Excel表格 [英] how to create dynamic excel sheets based on table data

问题描述

给定 SQL Server 中的一个表，其中包含来自三个源表的合并数据，其中包括一个名为 OFFICE 的列，用于区分彼此的记录.

Given one table in SQL Server which holds consolidated data from three source tables including one column called OFFICE which differentiates the records from each other.

三个源表保存来自三个办公室的数据.

The three source tables hold data from three offices.

我想动态创建一个 Excel 文件，该文件将根据三个不同的办公室(例如 office1、office2、office3)在一个工作簿中包含 3 个工作表，从而导致每个工作表都包含根据其办公室的相关数据.

I want to create an Excel file dynamically which will have 3 sheets in one workbook based on the three different different offices (ex. office1, office2, office3) resulting in each sheet containing the relevant data according to its office.

请推荐一种在 SSIS 中使用动态 Excel 目标的方法，因为我不想使用创建模板文件然后将该模板复制到目标 excel 文件的方法.

Please recommend an approach using dynamic Excel destination in SSIS as I don't want to use an approach which creates a template file and then copies that template to destination excel file.

推荐答案

虽然这可以使用 scipt 任务和 C# 来完成，但在以下位置演示了一个更简单的解决方案http://www.rafael-salas.com/2006/12/import-header-line-tables-_116683388696570741.html

While this can be accomplished using a scipt task and C#, a far easier solution is demonstrated at http://www.rafael-salas.com/2006/12/import-header-line-tables-_116683388696570741.html

和后续

http://www.rafael-salas.com/2008/03/ssis-and-dynamic-excel-destinations_01.html#!

但为了总结相关细节，您需要使用执行 SQL 任务"在运行时动态创建工作表，然后再将其用作目标.

But to summarize the relevant details, you need to use an 'Execute SQL Task' to dynamically create the sheet at runtime prior to using it as a destination.

创建一个新变量来保存工作表名称，并在迭代它们时将此变量设置为您正在使用的 Office.

Create a new variable to hold the Sheet name and set this variable to the Office you are working with as you iterate through them.

此外，创建一个变量来保存将创建每个工作表的 Create table 语句.例如，

Also, create a variable to hold the Create table statement that will create each sheet. For example,

"CREATE TABLE "+ @[User::SheetName] + "(HeaderID INTEGER, HeaderName NVARCHAR(50), LineID INTEGER、LineName NVARCHAR(50)、LineDetails NVARCHAR(50))"

"CREATE TABLE "+ @[User::SheetName] + "(HeaderID INTEGER, HeaderName NVARCHAR(50), LineID INTEGER, LineName NVARCHAR(50), LineDetails NVARCHAR(50))"

并将 For Each 容器内的 Execute SQL 任务的 SQLSourceType 属性设置为 Variable，然后选择您创建的 Variable 来保存 create 语句.

and Set the SQLSourceType Property of the Execute SQL task inside of the For Each container to Variable and choose the Variable you created to hold the create statement.

在 Excel 目标组件中，将数据访问模式更改为表名称或视图名称变量"，然后从变量下拉列表中选择您创建的工作表名称变量.

In the Excel Destination Component, Change the data access mode to ‘Table Name or View Name Variable’ and choose the sheet name variable you created from the variable dropdown list.

这篇关于如何根据表格数据创建动态Excel表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！