如何使用详细查询在C#中查询excel文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/207693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to query excel file in C# using a detailed query
提问by
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
- 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.
- 我有那个excel的数据传播到一个DataTable对象中，我如何查询DataTAable对象
- 我读到一个 DataView 对象，它可以获取一个表并返回根据 (
<dataviewObject>.RowFilter = "where...")操作的表，但我不知道如何使用我想要的查询。
回答by Jason Anderson
In your example, does SUMrepresent 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?
您是否正在尝试获取您的查询，以便您能够Column A, B, C, D, etc...像ColumnA, ColumnB, ColumnC, ColumnD, etc...在 SQL 查询中一样从 Excel 工作表中进行引用？
I suppose I mean: do you want to write your queryas this: Select ColumnA, ColumnB, ColumnC from [Accounts$], rather than this: Select * from [Accounts$]?
我想我的意思是：你想把你的query写成这样： Select ColumnA, ColumnB, ColumnC from [Accounts$]，而不是这样： Select * from [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 stringline look like this:
如果是这样，您可以将列标题放在 Excel 工作表的第一行，并将它们视为connection string. 为此，请使您的connection string行看起来像这样：
excelConnectString = "Provider = Microsoft.Jet.OLEDB.4.0;" + "Data Source = "
+ excelFileName + ";" + "Extended Properties = Excel 8.0; HDR=Yes;";
The different is the HDR=Yesportion 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 Selectmethod and DataView's RowFilter propertyas you mentioned.
不同的是HDR=Yes扩展属性部分中的部分。一旦您能够使用列名引用 Excel 工作表中的列，您应该能够使用 DataTable 的Select方法和DataView's RowFilter property您提到的。
Does this help or even address your question?
这是否有助于甚至解决您的问题？
回答by Binoj Antony
If you don't want to do Group bythen DataTable class has a method called Computethat 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
If you are using .NET 3.5 you may use Linq ref : LINQ DataTable Query - Group Aggregation
您可以使用 Microsoft KB 文章HOW TO：在 Visual C# .NET 中实现数据集 GROUP BY 助手类
如果您使用 .NET 3.5，您可以使用 Linq 参考：LINQ DataTable Query - Group Aggregation
回答by Fionnuala
Do you want something like:
你想要这样的东西：
SELECT Sum([NameOfFieldAsPerHeader]) FROM [Accounts$]
Or
或者
SELECT [ForExampleEmployeeID], Sum([NameOfFieldAsPerHeader]) FROM [Accounts$]
GROUP BY [ForExampleEmployeeID]
Or
或者
SELECT [ForExampleEmployeeID], Year([SomeDate]), Sum([NameOfFieldAsPerHeader])
FROM [Accounts$]
GROUP BY [ForExampleEmployeeID], Year([SomeDate])
Or
或者
SELECT [ForExampleEmployeeID], Year([SomeDate]), Sum([NameOfFieldAsPerHeader])
FROM [Accounts$]
WHERE Year([SomeDate])>2000
GROUP BY [ForExampleEmployeeID], Year([SomeDate])
