使用 C# 和 OleDB 读取 Excel - 如何识别已使用的范围？

发布于 2024-10-17 03:35:50 字数 1471 浏览 5 评论 0原文

我正在使用旧的 OleDBConnections 和 OleDBCommands 读取 Excel 文件。如何识别excel表格的使用范围？

例如，如果我要使用 InterOp，我总是可以这样做 -

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xWb;
Excel.Worksheet xWs;
Excel.Range range;

xWb = xlApp.Workbooks.Open(@"D:\Technical\C#\WorkingFolder\HelloWorld.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xWs = (Excel.Worksheet)xWb.Worksheets.get_Item(1);
range = xWs.UsedRange;

现在，我得到了所有已使用列的列表。有了OleDB，我该如何获取它。现在，即使我要确定列列表，我怎么知道行在此处结束。

基本上我的要求是这样的。

我即将收到 Excel (2003) 文件。数据将始终包含标题（即列名称）。这并不意味着数据将始终从 A1:J200 填充，

它也可以从 A5:J204 填充。

那么，在使用 OleDBCommand

string sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Technical\C#\WorkingFolder\HelloWorld.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
string sCommandString = "SELECT Employee_Id, First_Name FROM [Sheet1$A1:J200]";
OleDbConnection xlConnection = new OleDbConnection(sConnectionString);
xlConnection.Open();
OleDbCommand xlCommand = new OleDbCommand(sCommandString, xlConnection);
OleDbDataAdapter tempDataAdapter = new OleDbDataAdapter();
tempDataAdapter.SelectCommand = xlCommand;
DataTable tempDataTable = new DataTable("Whatever_I_Want");
tempDataAdapter.Fill(tempDataTable);

Now 时，sCommandString 行

的硬编码值为 A1:J200，如果我收到的 excel 的数据范围为 A5:204，我该怎么办？

任何帮助都会很棒！

原文

I am reading an Excel file using good old OleDBConnections and OleDBCommands. How do I identify the used range of the excel sheet?

For example, if I were to use InterOp, I can always do this -

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xWb;
Excel.Worksheet xWs;
Excel.Range range;

xWb = xlApp.Workbooks.Open(@"D:\Technical\C#\WorkingFolder\HelloWorld.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xWs = (Excel.Worksheet)xWb.Worksheets.get_Item(1);
range = xWs.UsedRange;

Now, I get a list of all the used columns. With OleDB, how do I get it. Now, even if I were to nail down the column list, how will I know that the rows end here.

Basically, my requirement is this.

I am about to receive an Excel (2003) file. The data will always include Headers (i.e., column name). It does not mean that the data will always be populated from A1:J200

It can also be from A5:J204 too.

So, in using OleDBCommand

string sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Technical\C#\WorkingFolder\HelloWorld.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
string sCommandString = "SELECT Employee_Id, First_Name FROM [Sheet1$A1:J200]";
OleDbConnection xlConnection = new OleDbConnection(sConnectionString);
xlConnection.Open();
OleDbCommand xlCommand = new OleDbCommand(sCommandString, xlConnection);
OleDbDataAdapter tempDataAdapter = new OleDbDataAdapter();
tempDataAdapter.SelectCommand = xlCommand;
DataTable tempDataTable = new DataTable("Whatever_I_Want");
tempDataAdapter.Fill(tempDataTable);

Now, the line

sCommandString has the hardcoded value of A1:J200, what if the excel I receive has the data range as A5:204, what will I do?

Any help would be great!

分享到QQ

分享到微博