How to query excel file in C# using a detailed query

Question

Hi - 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 ,,SUM from [Accounts$] group by ,"

..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 ( .RowFilter = "where...") , but I don't know how to use the query I want.

-Thanks for help

Answer 1

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

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?

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$]" ?

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:

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.

Does this help or even address your question?

Answer 2

simply great man.this is one of those few things which i was searching around for a long time.one more link that i got ,not that much handy but helps alot.

Answer 3

http://www.dotnetjohn.com/articles.aspx?articleid=54

Answer 4

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.

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:

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

2. If you are using .NET 3.5 you may use Linq ref : LINQ DataTable Query - Group Aggregation

Answer 5

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