Finding the last filled row in Excel sheet in c#

Question

I have a requirement to fine how many rows in the Excel sheet are filled with the data i.e rows which are not empty. I need to do that using c#.

Currently I am using the following code :

Excel.Application excelApp = new Excel.Application();
            string workbookPath = "C:\\ScriptTest\\bin\\Debug\\SDownloadScripts\\ExcelResult.xlsx";
            Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
        0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
        true, false, 0, true, false, false);
            Excel.Sheets excelSheets = excelWorkbook.Worksheets;
            string currentSheet = "Sheet1";
            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
            //long i = excelWorksheet.UsedRange.Rows.Count;
            int lastUsedRow = excelWorksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell,

Type.Missing).Row;
            return lastUsedRow;
        }

My sheet has only four rows filled but i getting 65536. I need 4 as a result i.e no of rows filled with some data. Please suggest.

Just to shed some additional light on this, Excel considers a cell non-empty if it has a style applied. I wish it didn't, but that's what it does. — Maurice Reeves
– Maurice Reeves, Commented May 9, 2014 at 11:09

SamuraiJack · Accepted Answer · 2013-09-04 06:48:29Z

3

Try this..

string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\xxxxxx\Desktop\1-8-13-ct.xlsx';Extended Properties=Excel 12.0;Persist Security Info=False";
    //Create Connection to Excel work book
    OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);
    //Create OleDbCommand to fetch data from Excel
     OleDbCommand cmd = new OleDbCommand("select count(*) from [Sheet1$]", excelConnection);
    con.Open();
    int rows = (int)cmd.ExecuteScalar();
  con.Close();

answered Sep 4, 2013 at 6:48

SamuraiJack

5,62919 gold badges103 silver badges214 bronze badges

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

2 Comments

priya Over a year ago

Thanks a lot Arbaaz. After so much of effort your solution helped me a lot.

SamuraiJack Over a year ago

You're Welcome. I am glad I could be any help. :)

maheshsringeri · Accepted Answer · 2013-09-04 06:49:59Z

private DataSet CreateDataSource(string strFilePath, string strSheetName) { DataSet myDataSet; myDataSet = null; try {

        if (strSheetName.Length > 0)
        {
            StringBuilder strConnectionString = new StringBuilder();
            strConnectionString.AppendFormat("Provider={0};", "Microsoft.ACE.OLEDB.12.0");
            strConnectionString.AppendFormat("Data Source={0};", strFilePath);
            strConnectionString.Append("Extended Properties=");
            strConnectionString.Append(((char)34).ToString()); //start of trickypart
            strConnectionString.Append("Excel 12.0 Xml;");
            // always treat contents of cells as text, which gives us full control/responsibility for casting to numeric when ncessary
            strConnectionString.Append(((char)34).ToString()); // end of tricky part
            string str = strConnectionString.ToString();

            OleDbConnection conn = new OleDbConnection(str);

            OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM [" + strSheetName + "$] where QuestionDescription <>''", str);
            myDataSet = new DataSet();
            myCommand.Fill(myDataSet);
        }
        else
        {
            trError.Visible = true;
            lblError.Text = "File is Invalid format";
        }
    }
    catch
    {
        trError.Visible = true;
        lblError.Text = "Invalid format!!";
    }
    return myDataSet;
}

for using above code you can query for to get non blank rows. the result will be stored into dataset. you can get non empty cell count from dataset. To this code work you need to use the "Microsoft.ACE.OLEDB.12.0" provider.

Community · Accepted Answer · 2017-05-23 12:05:11Z

0

I found some threads on stackoverflow asking a similar question. Maybe these already solve your question. This answer seems to be what you are looking for.

Depending on your result, the documentation for XlCelltype and the outcommented line I assume that the usedRange in your sheet is bigger than the actually used area. If the other solution doesn't work and you have no empty rows between your datarows you maybe could try searching for the first row in a column which has an empty cell (this row-1 should than be your needed rowcount).

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Sep 4, 2013 at 6:50

Onsokumaru

5412 gold badges6 silver badges15 bronze badges

Collectives™ on Stack Overflow

Finding the last filled row in Excel sheet in c#

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related