CODEWITHSUNDEEP
c#excel
Rui Martins
Rui Martins

Reputation: 2184

How do I sum all rows of a specific header in an excel file with c#?

I have a excel table with 1 sheet. That sheet has headers in row 1. One of the headers is Amount.

I want to read all rows from that header and get the sum of it independently of the number or rows, which is never the same, into a variable of type float.

I'm doing this with c#.

I open the workbook, I get the active sheet and then nothing, I get blocked.

How do I go about this? Rui Martins

Upvotes: 1

Views: 598

Answers (2)

Steve
Steve

Reputation: 216293

You could use OleDB instead of Excel.Interop

string con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\test.xls;" + 
              "Extended Properties='Excel 8.0;HDR=Yes;'";
using(OleDbConnection c = new OleDbConnection(con))
{
    c.Open();
    string selectString = "SELECT SUM(Amount) FROM [Sheet1$]";
    using(OleDbCommand cmd1 = new OleDbCommand(selectString))
    {
       cmd1.Connection = c;
 var result = cmd1.ExecuteScalar();
       Console.WriteLine(result);
    }
 }

This example use the old Microsoft.Jet.OleDB.4.0 provider, but works equally with the new Microsoft.ACE.OLEDB.12.0

Upvotes: 2

zbugs
zbugs

Reputation: 601

Take a look at this article, you should be able to loop through the rows and get the total by adding the cell values altogether.

MSDN article on how to retrieve excel cell values.

Upvotes: 0

Related Questions