如果列名不从第一行开始，如何使用OLEDB查询从Excel获取列名

I am facing 2 issues in OLEDB query

我在OLEDB查询中面临2个问题

(Problem 1)

I need to get the column names of user uploaded excel sheet. My connection string for excel is

我需要获取用户上传的excel表的列名。我的excel连接字符串是

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+fileNameAndPath+";
Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";

Then I will use this command

然后我将使用此命令

connection.Open();

DataTable schemaTable = connection.GetSchema("Columns");
Taking column name from each datarow like this
string name = (string)row["COLUMN_NAME"];

It is working fine, if the column name is present in first row of excel. But it will give the column name as "F1,F2,....." if the column name row doesn't start from first row. How can I solve this? is there any way to tell in query, from which row to take the column names?

如果列名在excel的第一行中,它工作正常。但如果列名行不从第一行开始,它将给列名称“F1,F2,.....”。我怎么解决这个问题?有没有办法在查询中告诉,从哪一行取列名?

(Problem 2)

Like the column header I need to select data from a particular range from excel. say like take data from row 3 to row 12. So I used the query like this,

像列标题一样,我需要从excel的特定范围中选择数据。比如从第3行到第12行获取数据。所以我使用了这样的查询,

Select RepName, Country from [Sheet1$3:12].

But it is throwing the exception,message like this "No value given for one or more required parameters."

但它抛出异常,这样的消息“没有给出一个或多个必需参数的值。”

Is any syntax error in my query?

我的查询中是否有语法错误?

if so can any one please tell how to correct the query?

如果可以,请告诉我们如何更正查询?

1 个解决方案

#1

Problem 1 can not be solved through OLEDB, Excel is not a database and the only thing it can do is use the first row as column names or if the first row is blank, F1 etc.

问题1无法通过OLEDB解决,Excel不是数据库,它唯一能做的就是使用第一行作为列名,或者第一行是空白,F1等。

The only way you can fix this is to use interop to either delete the blank rows and then query via OLEDB or parse the whole sheet through interop.

解决这个问题的唯一方法是使用interop删除空白行,然后通过OLEDB查询或通过互操作解析整个表。

#1