使用脚本任务处理 Excel 文件
Integration Services 提供了 Excel 连接管理器、Excel 源和 Excel 目标,用于处理以 Microsoft Excel 文件格式存储在电子表格中的数据。 本主题中介绍的技术使用脚本任务获取有关可用的 Excel 数据库(工作簿文件)和表(工作表和指定范围)的信息。 可以轻松修改这些示例,以便与 Microsoft Jet OLE DB 访问接口支持的任何其他基于文件的数据源一起使用。
注意
如果希望创建可更方便地重用于多个包的任务,请考虑以此脚本任务示例中的代码为基础,创建自定义任务。 有关详细信息,请参阅 开发自定义任务。
配置用于测试示例的包
您可以配置单个包来测试本主题中的所有示例。 这些示例使用许多相同的包变量和相同的 .NET Framework 类。
将包配置为用于本主题中的示例
在 Integration Services 中创建新的 SQL Server Data Tools (SSDT) 项目,并打开默认的包进行编辑。
变量。 打开“变量”窗口并定义以下变量:
String
类型的ExcelFile
。 输入现有 Excel 工作簿的完整路径和文件名。String
类型的ExcelTable
。 输入以ExcelFile
变量值命名的工作簿中的现有工作簿或指定范围的名称。 此值区分大小写。Boolean
类型的ExcelFileExists
。Boolean
类型的ExcelTableExists
。String
类型的ExcelFolder
。 输入至少包含一个 Excel 工作簿的文件夹的完整路径。Object
类型的ExcelFiles
。Object
类型的ExcelTables
。
Imports 语句。 多数代码示例都要求您在脚本文件的开头处导入一个或两个下列 .NET Framework 命名空间:
System.IO
用于文件系统操作。System.Data.OleDb
用于将 Excel 文件作为数据源打开。
引用。 从 Excel 文件读取架构信息的代码示例在脚本项目中需要对
System.Xml
命名空间的附加引用。设置脚本组件的默认脚本语言,方法是使用“选项”对话框的“常规”页上的“脚本语言”选项。 有关详细信息,请参阅 General Page。
示例 1 说明:检查 Excel 文件是否存在
本示例可确定 ExcelFile
变量中指定的 Excel 工作簿文件是否存在,然后根据该结果设置 ExcelFileExists
变量的布尔值。 可以使用此布尔值在包的工作流中进行分支。
配置此脚本任务示例
将新的脚本任务添加到包,并将其名称
ExcelFileExists
更改为 。在“脚本任务编辑器”的“脚本”选项卡上,单击“ReadOnlyVariables”,然后使用下列任一方法输入属性值:
键入
ExcelFile
。-或-
单击属性字段旁边的省略号(...)按钮,然后在“选择变量”对话框中选择变量
ExcelFile
。
单击“ReadWriteVariables”,然后使用下列任一方法输入属性值:
键入
ExcelFileExists
。-或-
单击属性字段旁边的省略号(...)按钮,然后在“选择变量”对话框中选择变量
ExcelFileExists
。
单击“编辑脚本”以打开脚本编辑器。
在脚本文件的开头处添加针对
Imports
命名空间的System.IO
语句。添加以下代码。
示例 1 代码
Public Class ScriptMain
Public Sub Main()
Dim fileToTest As String
fileToTest = Dts.Variables("ExcelFile").Value.ToString
If File.Exists(fileToTest) Then
Dts.Variables("ExcelFileExists").Value = True
Else
Dts.Variables("ExcelFileExists").Value = False
End If
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
public class ScriptMain
{
public void Main()
{
string fileToTest;
fileToTest = Dts.Variables["ExcelFile"].Value.ToString();
if (File.Exists(fileToTest))
{
Dts.Variables["ExcelFileExists"].Value = true;
}
else
{
Dts.Variables["ExcelFileExists"].Value = false;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
示例 2 说明:检查 Excel 表是否存在
本示例可确定 ExcelTable
变量中指定的 Excel 工作表或命名范围是否存在于 ExcelFile
变量中指定的 Excel 工作簿文件中,然后根据该结果设置 ExcelTableExists
变量的布尔值。 可以使用此布尔值在包的工作流中进行分支。
配置此脚本任务示例
将新的脚本任务添加到包,并将其名称
ExcelTableExists
更改为 。在“脚本任务编辑器”的“脚本”选项卡上,单击“ReadOnlyVariables”,然后使用下列任一方法输入属性值:
键入
ExcelTable
并ExcelFile
用逗号分隔.
-或-
单击属性字段旁边的省略号(...)按钮,然后在 “选择变量 ”对话框中,选择
ExcelTable
和ExcelFile
变量。
单击“ReadWriteVariables”,然后使用下列任一方法输入属性值:
键入
ExcelTableExists
。-或-
单击属性字段旁边的省略号(...)按钮,然后在“选择变量”对话框中选择变量
ExcelTableExists
。
单击“编辑脚本”以打开脚本编辑器。
在脚本项目中添加对
System.Xml
程序集的引用。在脚本文件的开头处添加针对
Imports
和System.IO
命名空间的System.Data.OleDb
语句。添加以下代码。
示例 2 代码
Public Class ScriptMain
Public Sub Main()
Dim fileToTest As String
Dim tableToTest As String
Dim connectionString As String
Dim excelConnection As OleDbConnection
Dim excelTables As DataTable
Dim excelTable As DataRow
Dim currentTable As String
fileToTest = Dts.Variables("ExcelFile").Value.ToString
tableToTest = Dts.Variables("ExcelTable").Value.ToString
Dts.Variables("ExcelTableExists").Value = False
If File.Exists(fileToTest) Then
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fileToTest & _
";Extended Properties=Excel 8.0"
excelConnection = New OleDbConnection(connectionString)
excelConnection.Open()
excelTables = excelConnection.GetSchema("Tables")
For Each excelTable In excelTables.Rows
currentTable = excelTable.Item("TABLE_NAME").ToString
If currentTable = tableToTest Then
Dts.Variables("ExcelTableExists").Value = True
End If
Next
End If
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
public class ScriptMain
{
public void Main()
{
string fileToTest;
string tableToTest;
string connectionString;
OleDbConnection excelConnection;
DataTable excelTables;
string currentTable;
fileToTest = Dts.Variables["ExcelFile"].Value.ToString();
tableToTest = Dts.Variables["ExcelTable"].Value.ToString();
Dts.Variables["ExcelTableExists"].Value = false;
if (File.Exists(fileToTest))
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fileToTest + ";Extended Properties=Excel 8.0";
excelConnection = new OleDbConnection(connectionString);
excelConnection.Open();
excelTables = excelConnection.GetSchema("Tables");
foreach (DataRow excelTable in excelTables.Rows)
{
currentTable = excelTable["TABLE_NAME"].ToString();
if (currentTable == tableToTest)
{
Dts.Variables["ExcelTableExists"].Value = true;
}
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
示例 3 说明:获取文件夹中的 Excel 文件的列表
本示例使用由 ExcelFolder
变量值指定的文件夹中找到的 Excel 文件的列表来填充数组,然后将该数组复制到 ExcelFiles
变量。 您可以使用 Foreach 源变量枚举器循环访问数组中的文件。
配置此脚本任务示例
将新的脚本任务添加到包,并将其名称更改为 GetExcelFiles。
打开“脚本任务编辑器”,在“脚本”选项卡上单击“ReadOnlyVariables”,然后使用下列任一方法输入属性值:
键入
ExcelFolder
-或-
单击属性字段旁的省略号 (…) 按钮,然后在“选择变量”对话框中选择“ExcelFolder”变量。
单击“ReadWriteVariables”,然后使用下列任一方法输入属性值:
键入
ExcelFiles
。-或-
单击属性字段旁的省略号 (…) 按钮,然后在“选择变量”对话框中选择“ExcelFile”变量。
单击“编辑脚本”以打开脚本编辑器。
在脚本文件的开头处添加针对
Imports
命名空间的System.IO
语句。添加以下代码。
示例 3 代码
Public Class ScriptMain
Public Sub Main()
Const FILE_PATTERN As String = "*.xls"
Dim excelFolder As String
Dim excelFiles As String()
excelFolder = Dts.Variables("ExcelFolder").Value.ToString
excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN)
Dts.Variables("ExcelFiles").Value = excelFiles
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
public class ScriptMain
{
public void Main()
{
const string FILE_PATTERN = "*.xls";
string excelFolder;
string[] excelFiles;
excelFolder = Dts.Variables["ExcelFolder"].Value.ToString();
excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN);
Dts.Variables["ExcelFiles"].Value = excelFiles;
Dts.TaskResult = (int)ScriptResults.Success;
}
}
备用解决方案
如果不使用脚本任务将 Excel 文件列表收集到数组中,则还可以使用 ForEach 文件枚举器来循环访问文件夹中的所有 Excel 文件。 有关详细信息,请参阅使用 Foreach 循环容器循环遍历 Excel 文件和表。
示例 4 说明:获取 Excel 文件中的表的列表
本示例使用在 Excel 工作簿文件中找到的由 ExcelFile
变量值指定的工作表和指定范围列表来填充数组,然后将该数组复制到 ExcelTables
变量。 您可以使用 Foreach 源变量枚举器循环访问数组中的表。
注意
Excel 工作簿中的表列表同时包括工作表(具有 $ 后缀)和指定范围。 如果要从列表中只筛选出工作表或指定范围,则必须添加其他代码来实现这一点。
配置此脚本任务示例
将新的脚本任务添加到包中,并将其名称更改为 GetExcelTables。
打开“脚本任务编辑器”,在“脚本”选项卡上单击“ReadOnlyVariables”,然后使用下列任一方法输入属性值:
键入
ExcelFile
。-或-
单击属性字段旁的省略号 (…) 按钮,然后在“选择变量”对话框中选择“ExcelFile”变量。
单击“ReadWriteVariables”,然后使用下列任一方法输入属性值:
键入
ExcelTables
。-或-
单击属性字段旁的省略号 (…) 按钮,然后在“选择变量”对话框中选择“ExcelTables”变量。
单击“编辑脚本”以打开脚本编辑器。
在脚本项目中添加对
System.Xml
命名空间的引用。在脚本文件的开头处添加针对
Imports
命名空间的System.Data.OleDb
语句。添加以下代码。
示例 4 代码
Public Class ScriptMain
Public Sub Main()
Dim excelFile As String
Dim connectionString As String
Dim excelConnection As OleDbConnection
Dim tablesInFile As DataTable
Dim tableCount As Integer = 0
Dim tableInFile As DataRow
Dim currentTable As String
Dim tableIndex As Integer = 0
Dim excelTables As String()
excelFile = Dts.Variables("ExcelFile").Value.ToString
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & excelFile & _
";Extended Properties=Excel 8.0"
excelConnection = New OleDbConnection(connectionString)
excelConnection.Open()
tablesInFile = excelConnection.GetSchema("Tables")
tableCount = tablesInFile.Rows.Count
ReDim excelTables(tableCount - 1)
For Each tableInFile In tablesInFile.Rows
currentTable = tableInFile.Item("TABLE_NAME").ToString
excelTables(tableIndex) = currentTable
tableIndex += 1
Next
Dts.Variables("ExcelTables").Value = excelTables
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
public class ScriptMain
{
public void Main()
{
string excelFile;
string connectionString;
OleDbConnection excelConnection;
DataTable tablesInFile;
int tableCount = 0;
string currentTable;
int tableIndex = 0;
string[] excelTables = new string[5];
excelFile = Dts.Variables["ExcelFile"].Value.ToString();
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelFile + ";Extended Properties=Excel 8.0";
excelConnection = new OleDbConnection(connectionString);
excelConnection.Open();
tablesInFile = excelConnection.GetSchema("Tables");
tableCount = tablesInFile.Rows.Count;
foreach (DataRow tableInFile in tablesInFile.Rows)
{
currentTable = tableInFile["TABLE_NAME"].ToString();
excelTables[tableIndex] = currentTable;
tableIndex += 1;
}
Dts.Variables["ExcelTables"].Value = excelTables;
Dts.TaskResult = (int)ScriptResults.Success;
}
}
备用解决方案
如果不使用脚本任务将 Excel 表列表收集到数组中,则还可以使用 ForEach ADO.NET 架构行集枚举器来循环访问 Excel 工作簿文件中的所有表(即,工作表和指定范围)。 有关详细信息,请参阅使用 Foreach 循环容器循环遍历 Excel 文件和表。
显示示例的结果
如果已在同一个包中配置本主题的所有示例,则可以将所有脚本任务连接到用于显示所有示例输出的其他脚本任务。
配置用于显示本主题中的示例输出的脚本任务
将新的脚本任务添加到包中,并将其名称更改为 DisplayResults。
将四个示例脚本任务彼此连接,以便每个任务在前一个任务成功完成后运行,然后将第四个示例任务连接到 DisplayResults 任务。
在“脚本任务编辑器”中打开 DisplayResults 任务。
在“脚本”选项卡中单击“ReadOnlyVariables”然后使用下列任一方法添加配置用于测试示例的包中列出的七个变量:
键入用逗号分隔的每个变量的名称。
- 或 -
单击属性字段旁的省略号 (…) 按钮,然后在“”选择变量对话框中选择变量。
单击“编辑脚本”以打开脚本编辑器。
在脚本文件的开头处添加针对
Imports
和Microsoft.VisualBasic
命名空间的System.Windows.Forms
语句。添加以下代码。
运行包,然后检查消息框中显示的结果。
编写显示结果的代码
Public Class ScriptMain
Public Sub Main()
Const EOL As String = ControlChars.CrLf
Dim results As String
Dim filesInFolder As String()
Dim fileInFolder As String
Dim tablesInFile As String()
Dim tableInFile As String
results = _
"Final values of variables:" & EOL & _
"ExcelFile: " & Dts.Variables("ExcelFile").Value.ToString & EOL & _
"ExcelFileExists: " & Dts.Variables("ExcelFileExists").Value.ToString & EOL & _
"ExcelTable: " & Dts.Variables("ExcelTable").Value.ToString & EOL & _
"ExcelTableExists: " & Dts.Variables("ExcelTableExists").Value.ToString & EOL & _
"ExcelFolder: " & Dts.Variables("ExcelFolder").Value.ToString & EOL & _
EOL
results &= "Excel files in folder: " & EOL
filesInFolder = DirectCast(Dts.Variables("ExcelFiles").Value, String())
For Each fileInFolder In filesInFolder
results &= " " & fileInFolder & EOL
Next
results &= EOL
results &= "Excel tables in file: " & EOL
tablesInFile = DirectCast(Dts.Variables("ExcelTables").Value, String())
For Each tableInFile In tablesInFile
results &= " " & tableInFile & EOL
Next
MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information)
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
public class ScriptMain
{
public void Main()
{
const string EOL = "\r";
string results;
string[] filesInFolder;
//string fileInFolder;
string[] tablesInFile;
//string tableInFile;
results = "Final values of variables:" + EOL + "ExcelFile: " + Dts.Variables["ExcelFile"].Value.ToString() + EOL + "ExcelFileExists: " + Dts.Variables["ExcelFileExists"].Value.ToString() + EOL + "ExcelTable: " + Dts.Variables["ExcelTable"].Value.ToString() + EOL + "ExcelTableExists: " + Dts.Variables["ExcelTableExists"].Value.ToString() + EOL + "ExcelFolder: " + Dts.Variables["ExcelFolder"].Value.ToString() + EOL + EOL;
results += "Excel files in folder: " + EOL;
filesInFolder = (string[])(Dts.Variables["ExcelFiles"].Value);
foreach (string fileInFolder in filesInFolder)
{
results += " " + fileInFolder + EOL;
}
results += EOL;
results += "Excel tables in file: " + EOL;
tablesInFile = (string[])(Dts.Variables["ExcelTables"].Value);
foreach (string tableInFile in tablesInFile)
{
results += " " + tableInFile + EOL;
}
MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information);
Dts.TaskResult = (int)ScriptResults.Success;
}
}
随时了解 Integration Services
有关来自Microsoft的最新下载、文章、示例和视频,以及来自社区的所选解决方案,请访问 MSDN 上的 Integration Services 页面:
访问 MSDN 上的 Integration Services 页
若要获得有关这些更新的自动通知,请订阅该页上提供的 RSS 源。