使用 SQL Server 命令通过 ADO.NET 读取 excel? [英] Read excel via ADO.NET with SQL Server commands?

问题描述

我可以使用此代码读取 XLS 文件:

I can read XLS file with this code :

string path =@"c:\r\1.xlsx";
OleDbConnection MyConnection = new OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + @"';HDR=Yes;Jet OLEDB:Engine Type=37");
OleDbDataAdapter MyCommand = new OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
DataSet DtSet = new DataSet();
MyCommand.Fill(DtSet);
...
...

但是 - 当我增强查询以包含一些 SQL Server 命令时，例如

However - when I enhance the query to include some SQL Server commands like

 select *,case when 1=1 then 'a' else 'b' end as rr  from [Sheet1$]

它去BANG

我知道 OLEDB 在幕后使用 access jet/ace.

I know that OLEDB is using access jet/ace behind the scenes.

这里如何使用纯 T-SQL 查询?

How can use pure T-SQL query here?

推荐答案

查询excel必须使用IIF

You have to use IIF in querying excel

select *,
    IIF(1 = 1, 'a', 'b') as rr  
from [Sheet1$]

而且，要创建多 case 语句，只需将它们嵌套，如下所示:

And, to create a multiple case statement, just nest them, like this:

select *,
    IIF(1 = 1, 'a', IIF( 2 = 2, 'c', 'b')) as rr  
from [Sheet1$]

至于是否可以使用纯MSSQL查询，我不相信任何可以与excel一起使用的连接都支持CASE语句.因此，您将不得不使用上述解决方案

As to whether you can use a pure MSSQL query, I do not believe that any connection that you can use with excel supports the CASE statement. So, you will have to use the above solution

这篇关于使用 SQL Server 命令通过 ADO.NET 读取 excel?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！