SSIS Excel源连接。这是什么用来读取Excel？ [英] SSIS Excel Source Connection. What does it use to read Excel?
问题描述
使用SSIS，我可以使用OLE连接与喷气机驾驶员读取Excel或者我可以使用Excel连接
Using SSIS, I Can use an OLE Connect with a Jet driver to read Excel or I can use a separate Connection type of "Excel Connection"
两者似乎有问题，阅读文件，合并单元格，因为我害怕。
Both seem to have issues reading files with merges cells, as I feared.
我很好奇SSIS用来连接到Excel当Excel连接字。
I am curious what SSIS is using to connect to Excel when an "Excel Connection" is used.
除了VBA，你会建议用于读取已经合并单元格，公式，格式等一个Excel文件，在服务器上？我使用Excel 2003中。
Other than VBA, what would you suggest for reading an Excel file that has merged cells, formulas, formatting, etc, on a server? I am using Excel 2003.
更新
下面是我用来读取XLS的code：
Here's the code that I use to read the XLS:
private static void GetExcelSheets(string filePath)
{
string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'", filePath, "no");
OleDbConnection excelConnection = new OleDbConnection(connectionString);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
cmdExcel.Connection = excelConnection;
if (excelConnection.State == ConnectionState.Open)
{
excelConnection.Close();
}
excelConnection.Open();
OleDbCommand oleDbCommand = new OleDbCommand();
oleDbCommand.CommandType = System.Data.CommandType.Text;
oleDbCommand.Connection = excelConnection;
OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(oleDbCommand);
DataTable dtExcelSheetName = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[5]["Table_Name"].ToString();
oleDbCommand.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
oleDbDataAdapter.SelectCommand = oleDbCommand;
DataTable dtExcelRecords = new DataTable();
oleDbDataAdapter.Fill(dtExcelRecords);
excelConnection.Dispose();
}
当我读为preadsheet看起来是这样的：
When I read a spreadsheet that looks like this:
，然后使用调试器可视化显示的数据集，我看到这一点。需要注意的是，我盘旋在源极s preadsheet数据从丢失的数据表显示：
and then use a debugger Visualizer to show the dataset, I see this. Note that the data in the source spreadsheet that I circled is missing from the displayed DataTable:
对于有关使用Microsoft OLE DB提供程序为Jet 4.0的Excel连接点。在SSIS，有两个单独的连接对象。第一个是一个Excel连接对象。它的属性页看起来是这样的：
Regarding the point about the Excel Connection using Microsoft OLE DB Provider for Jet 4.0. In SSIS, there are two separate connection objects. The first is an Excel Connect Object. It's Property page looks like this:
The second is an OLE Connection object using a JET Driver and pointing to Excel.
时的Excel连接对象确实使用了4.0的Jet驱动程序，它是做同样的事情只是一种速记的方式或者是这些conenction类型以某种方式？
Is the Excel Connection object really uses the 4.0 Jet driver, is it just a shorthand way of doing the same thing or are these conenction types really different in some way?
推荐答案
据微软称：
Excel的连接管理器使用了Microsoft OLE DB提供程序为Jet 4.0和其支持的Excel ISAM（索引顺序访问方法）驱动程序来连接并读取和写入数据到Excel数据源。
The Excel connection manager uses the Microsoft OLE DB Provider for Jet 4.0 and its supporting Excel ISAM (Indexed Sequential Access Method) driver to connect and read and write data to Excel data sources.
源代码的 MSDN
Source: MSDN
至于如何正确导入Excel工作表与合并单元格：你需要确保你有最左边的列被映射在你的转换，以获得该值。任何细胞不是最左边的在合并组单元将空。我认为这是一个合理的预期，因为SSIS通常与数据库源，其中有合并单元格的概念。
As for how to properly import Excel sheets with merged cells: you need to make sure that you have the left-most column being mapped in your transformations in order to get the value. Any cell that is not the left-most in a merged set of cells will be null. I think this is a reasonable expectation, as SSIS is typically used with database sources, which have no concept of "merged" cells.
这篇关于SSIS Excel源连接。这是什么用来读取Excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
