通过SSIS动态创建Excel表

在 SSIS 中使用执行 SQL 任务 - 创建的 Excel 文件 - 包含多个列和不同的数据类型。这里的问题是 INT/Money 列在 Excel 中显示为文本列，即使 Excel 表是使用 Int/Money 数据类型创建的

我尝试过使用双精度 CY 数据类型，但没有成功。

CREATE TABLE `Employer` (
`MEMBERSHIP NUMBER` VARCHAR(30),
`RETIREE #` VARCHAR(12),
`COPID` CHAR(6),
`PERSON LAST NAME` VARCHAR(150),
`FIRST NAME` VARCHAR(150),
`RETIREE PLAN` CHAR(15),
`PLAN NAME` CHAR(200),
`BILL GROUP` INT,
`BRANCH ID` CHAR(3),
`CONTRACT NUMBER` CHAR(5),
`PBP` CHAR(3),
`BRANCH NAME` VARCHAR(150),
`COVERAGE MONTH` DATE,
`DUE AMOUNT` INT
)

预期输出应采用输入数据类型格式。

OLE DB 数据类型和 Excel

即使您可以使用 OLE DB 命令在 Excel 中创建表，Excel 文件也不是数据库，其中的列具有数据类型。 OLE DB 提供程序只读取表格数据，并且需要为每一列指定数据类型。因此，在处理 Excel 时必须指定列数据类型，即使 Excel 能够在同一列中存储多种数据类型。

使用 OLE DB 提供程序读取 Excel 的缺点之一是，如果某一列包含混合数据类型，它只会读取主导数据类型，并将所有剩余数据类型转换为NULL.

• 从列中具有混合数据类型的 Excel 导入数据 (SSIS) https://munishbansal.wordpress.com/2009/12/15/importing-data-from-excel-having-mixed-data-types-in-a-column-ssis/
• Excel 列中的混合数据类型 http://microsoft-ssis.blogspot.com/2011/06/mixed-data-types-in-excel-column.html

Excel 数字格式属性

另一方面，Excel 中有一个称为数字格式的单元格属性，用于更改值在 Excel 中的显示方式。您可以使用此属性将值显示为货币。

• 将数字设置为货币格式 https://support.office.com/en-gb/article/format-numbers-as-currency-0a03bb38-1a07-458d-9e30-2b54366bc7a4
• 如何在 Excel 2010 中将数字显示为货币 https://www.dummies.com/software/microsoft-office/excel/how-to-display-numbers-in-excel-2010-as-currency/

Solution

您可以使用两种方法来解决该问题：

(1) 使用脚本任务更改列号格式

您可以添加脚本任务并使用 Interop.Excel.dll 程序集更改整个列的数字格式。

• 在 C# 中将 Excel 列（或单元格）格式化为文本？ https://stackoverflow.com/questions/2067926/format-an-excel-column-or-cell-as-text-in-c
• 使用 C# 设置整个 Excel 列的格式 https://stackoverflow.com/questions/24972371/format-entire-excel-column-using-c-sharp

(2) 创建Excel模板文件

您可以创建一个 Excel 文件并根据需要更改列数字格式，并使用该文件作为模板。您可以将文件复制到目标目录，然后执行数据导入阶段。

• 快速入门：设置工作表中的数字格式 https://support.office.com/en-gb/article/quick-start-format-numbers-in-a-worksheet-c9fba2a2-7015-4fa5-b4e6-a29cb679e406