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

Question

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

I just sit there looking at it and nothing comes out. I'm blocked. — Rui Martins
– Rui Martins, Commented Aug 28, 2012 at 15:06
How are you "getting the active sheet"? Does that sheet have something like Columns or Rows? — Hans Keﬆing
– Hans Keﬆing, Commented Aug 28, 2012 at 15:11

Steve · Accepted Answer · 2012-08-28 15:07:59Z

2

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

answered Aug 28, 2012 at 15:07

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rui Martins Over a year ago

In the case of this application. Since the workbook is actually open, I have to use Interop

Steve Over a year ago

Don't know, I have tried with workbook open and OleDb works, probably there are some options to prevent sharing of open workbooks.

Rui Martins Over a year ago

And there's another thing. I don't know where the file is located. It's the user of the aplication that opens it.

zbugs · Accepted Answer · 2012-08-28 15:07:01Z

0

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.

answered Aug 28, 2012 at 15:07

zbugs

6013 silver badges10 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related