如何使用详细查询在C＃中查询excel文件

The following code returns data from a spreadsheet into a grid perfectly

以下代码将电子表格中的数据完美地返回到网格中

[
        string excelConnectString = "Provider = Microsoft.Jet.OLEDB.4.0;" +
            "Data Source = " + excelFileName + ";" +
            "Extended Properties = Excel 8.0;";

        OleDbConnection objConn = new OleDbConnection(excelConnectString);
        OleDbCommand objCmd = new OleDbCommand("Select * From [Accounts$]", objConn);

        OleDbDataAdapter objDatAdap = new OleDbDataAdapter();
        objDatAdap.SelectCommand = objCmd;
        DataSet ds = new DataSet();
        objDatAdap.Fill(ds);
        fpDataSet_Sheet1.DataSource = ds;//fill a grid with data
]

The spreadsheet I'm using has columns named from A and so on( just standard column names ) and the sheet name is Accounts.

我正在使用的电子表格包含以A等命名的列（仅标准列名称），工作表名称为Accounts。

I have a problem with the query ...

我的查询有问题...

  [OleDbCommand objCmd = new OleDbCommand("Select * From [Accounts$]", objConn);]

How can I make the query string like this...

如何使查询字符串像这样...

"Select <columnA>,<columnB>,SUM<columnG> from [Accounts$] group by <columnA>,<columnB>"

..so that it returns the results of this query

..然后它返回此查询的结果

Note : columnA is A on Sheet , columnB is B on Sheet and columnG is G on Sheet

注意：columnA是Sheet上的A，columnB是Sheet上的B，columnG是Sheet上的G.

Other possible Alternatives:

其他可能的替代方案：

I have the data of that excel spread into a DataTable object, how can I query the DataTAble object
我将excel的数据传播到DataTable对象中，如何查询DataTAble对象
I read about a DataView object that it can take a table and return the table manipulated according to (<dataviewObject>.RowFilter = "where...") , but I don't know how to use the query I want.
我读了一个DataView对象，它可以获取一个表并返回根据（ .RowFilter =“where ...”）操作的表，但我不知道如何使用我想要的查询。

3 个解决方案

#1

Do you want something like:

你想要这样的东西：

 SELECT Sum([NameOfFieldAsPerHeader]) FROM [Accounts$]

要么

 SELECT [ForExampleEmployeeID], Sum([NameOfFieldAsPerHeader]) FROM [Accounts$]
 GROUP BY [ForExampleEmployeeID]

要么

 SELECT [ForExampleEmployeeID], Year([SomeDate]), Sum([NameOfFieldAsPerHeader]) 
 FROM [Accounts$]
 GROUP BY [ForExampleEmployeeID], Year([SomeDate])

要么

 SELECT [ForExampleEmployeeID], Year([SomeDate]), Sum([NameOfFieldAsPerHeader]) 
 FROM [Accounts$]
 WHERE Year([SomeDate])>2000
 GROUP BY [ForExampleEmployeeID], Year([SomeDate])

#2

If you don't want to do Group by then DataTable class has a method called Compute that executes few SQL functions.
The following functions are supported : COUNT, SUM, MIN, MAX, AVG, STDEV, VAR.

如果你不想做Group by，那么DataTable类有一个名为Compute的方法，它执行很少的SQL函数。支持以下功能：COUNT，SUM，MIN，MAX，AVG，STDEV，VAR。

string salary = empTable.Compute("SUM( Salary )", "").ToString();
string averageSalaryJan = empTable.Compute("AVG( Salary )", "Month = 1").ToString();
// Assuming you have month stored in Month column and Salary stored in Salary column

Other alternatives you can explore are:

您可以探索的其他替代方案是：

You may use the Microsoft KB article HOW TO: Implement a DataSet GROUP BY Helper Class in Visual C# .NET

您可以使用Microsoft知识库文章如何：在Visual C＃.NET中实现DataSet GROUP BY Helper类
If you are using .NET 3.5 you may use Linq ref : LINQ DataTable Query - Group Aggregation

如果您使用的是.NET 3.5，则可以使用Linq ref：LINQ DataTable Query - Group Aggregation

#3

In your example, does SUM represent a potential column name or a SQL funciton?

在您的示例中，SUM表示潜在的列名称还是SQL函数？

Are you trying to get your query so that you're able to reference Column A, B, C, D, etc... from the Excel sheet as ColumnA, ColumnB, ColumnC, ColumnD, etc... in your SQL query?

您是否尝试获取查询，以便能够在Excel表格中引用列A，B，C，D等...作为ColumnA，ColumnB，ColumnC，ColumnD等在SQL查询中？

I suppose I mean: do you want to write your query as this: "Select ColumnA, ColumnB, ColumnC from [Accounts$]", rather than this: "Select * from [Accounts$]" ?

我想我的意思是：你想写下你的查询：“从[Accounts $]中选择ColumnA，ColumnB，ColumnC”，而不是：“从[Accounts $]中选择*”？

If so, you can put column headers in the first row of your Excel sheet and treat those as the column names in the connection string. To do this, make your connection string line look like this:

如果是这样，您可以将列标题放在Excel工作表的第一行，并将其作为连接字符串中的列名称。为此，请使连接字符串行如下所示：

excelConnectString = "Provider = Microsoft.Jet.OLEDB.4.0;" + "Data Source = " + excelFileName + ";" + "Extended Properties = Excel 8.0; HDR=Yes;";

The different is the "HDR=Yes" portion in the Extended Properties section. Once you're able to reference the columns in your Excel sheet by using column names, you should be able to use DataTable's Select method and DataView's RowFilter property as you mentioned.

不同的是扩展属性部分中的“HDR =是”部分。一旦您能够使用列名称引用Excel工作表中的列，您就应该能够使用DataTable的Select方法和DataView的RowFilter属性。

Does this help or even address your question?

这有助于甚至解决您的问题吗？

#1