Programmatically getting the total no. of rows having data in excel sheet using C#

Question

Mine is C# console application. I am trying to get the count of rows which are having data of an excel sheet programmatically, using C# with the reference of EPPlus Library. I want to read all the records of an excel spreadsheet using for loop. So I need to specify the upper limit of that loop.

code i used:-

FileInfo existingFile = new FileInfo(FilePath);
        using (ExcelPackage package = new ExcelPackage(existingFile))
        {
            // get the second worksheet in the workbook
            ExcelWorksheet worksheet = package.Workbook.Worksheets[2];

        // output the data in column 2
        StringBuilder sb = new StringBuilder();

        for (int row = 2; row < 7; row++)
        {
            sb.Append("<tr>");
            for (int col = 2; col < 11; col++)
            {
                if (col == 7)
                    sb.Append("<td valign='top' border='1'><a href='https://na7.salesforce.com/'" + worksheet.Cells[row, col].Value + ">" + "https://na7.salesforce.com/" + worksheet.Cells[row, col].Value + "</td>");
                else
                    sb.Append("<td valign='top' border='1'>" + worksheet.Cells[row, col].Value + "</td>");
            }
            sb.Append("</tr>");
        }
    }

Now am statically mentioning that the code must retrieve 7 rows [for (int row = 2; row < 7; row++)]. Here the 7 must be calculated dynamically (during the run-time).

Kindly popup your points here to work it out. Thanks in advance.

@SiddharthRout This will NOT work if he uses EPPLUS (which is avery good decision) — Christian Sauer
– Christian Sauer, Commented Sep 11, 2013 at 6:42
@ChristianSauer: Yeah you r right. I didn't notice EPPLUS :) — Siddharth Rout
– Siddharth Rout, Commented Sep 11, 2013 at 6:45

Christian Sauer · Accepted Answer · 2013-09-11 06:47:21Z

1

You could do something like this:

var rowCount =  worksheet .Dimension.End.Row

This returns the number of the last row with data. Than you can simply loop until this number.

Obviously, this will not work if you have garbage like comments at the end of yor sheet, than there will more rows.

answered Sep 11, 2013 at 6:47

Christian Sauer

11.2k12 gold badges57 silver badges88 bronze badges

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

Collectives™ on Stack Overflow

Programmatically getting the total no. of rows having data in excel sheet using C#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related