如何使用一个详细的查询来查询Excel文件在C＃ [英] How to query excel file in C# using a detailed query

问题描述

下面的代码从电子表格数据返回到网格完美

  [
串excelConnectString =供应商= Microsoft.Jet.OLEDB.4.0; + 
数据源=+ excelFileName +; + 
扩展属性= Excel的8.0;; 
 
 OleDbConnection的objConn =新的OleDbConnection（excelConnectString）; 
 OleDbCommand的objCmd =新的OleDbCommand（SELECT * FROM [账户$]，objConn）; 
 
 OleDbDataAdapter的objDatAdap =新OleDbDataAdapter的（）; 
 objDatAdap.SelectCommand = objCmd; 
的DataSet DS =新的DataSet（）; 
 objDatAdap.Fill（DS）; 
 fpDataSet_Sheet1.DataSource = DS; //用数据填充
] 
  

网格

我使用的电子表格已经从一个名为列等等（只是标准的列名）
和表名称为帐户。

我有一个与查询问题...

  [OleDbCommand的objCmd =新的OleDbCommand（SELECT * FROM [账户$]，objConn） ] 
  

我怎样才能让查询字符串这样的...

 选择< columnA>，< columnB>，SUM< columnG>从[账户$]集团以< columnA>，< columnB>中
  

..所以它返回此查询的结果。

请注意：columnA是一家位于表，columnB为b上的工作表和columnG为G上页

其他可能的替代方案：

1. 我的那个Excel试算成一个DataTable对象中的数据，我怎么可以查询DataTable对象


2. 我读到一DataView对象，它可以把一个表并返回根据操纵表（< dataviewObject> .RowFilter =其中...），但我不知道如何使用我想查询

解决方案

你想是这样的：

  SELECT SUM（[NameOfFieldAsPerHeader]）FROM [账户$] 
  

或

  SELECT [ForExampleEmployeeID]，SUM（[NameOfFieldAsPerHeader]）FROM [帐户$] 
 GROUP BY [ForExampleEmployeeID] 
  

或

  SELECT [ForExampleEmployeeID]，年份（[SomeDate]），SUM（[NameOfFieldAsPerHeader]）
 FROM [帐户$] 
 GROUP BY [ ForExampleEmployeeID]，年份（[SomeDate]）
  

或

  SELECT [ForExampleEmployeeID]，年份（[SomeDate]），SUM（[NameOfFieldAsPerHeader]）
 FROM [帐户$] 
，其中年份（[SomeDate] ）> 2000 
 GROUP BY [ForExampleEmployeeID]，年份（[SomeDate]）
  

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.

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

Other possible Alternatives:

1. I have the data of that excel spread into a DataTable object, how can I query the DataTAble object
2. 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.

解决方案

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])

这篇关于如何使用一个详细的查询来查询Excel文件在C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！