How to request excel file in C # using detailed request

Question

The following code automatically returns data from the table to the grid

[
        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 table I'm using has columns named A, etc. (standard column names only) and sheet name are accounts.

I have a problem with the request ...

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

How can I make a 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 the sheet, column B is B on the sheet, and columnG is G on the sheet

Other possible alternatives:

• I have data that excel propagates to a DataTable, how can I request a DataTAble
• I read about a DataView object that it can take a table and return a table processed according to ( <dataviewObject>.RowFilter = "where..."), but I don't know how to use the query that I want.

Answer 1

- :

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

Answer 2

, DataTable , 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

, :

• Microsoft KB : GROUP BY GROUP BY Visual #.NET

• .NET 3.5, Linq ref: LINQ DataTable -

Answer 3

SUM SQL?

, A, B, C, D .. Excel ColumnA, ColumnB, ColumnC, ColumnD .. SQL-?

, : : " ColumnA, ColumnB, ColumnC [Accounts $]", : " * [ $]"?

, Excel . :

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

" " - "HDR = ". , Excel, , DataTable Select DataView RowFilter, .

Does this help or even solve the issue?