Interop Excel UsedRange Rows Count incorrect

Question

I am trying to read an excel spreadsheet into memory but when I use worksheet.UsedRange.Rows.Count, the value return is incorrect. I have 1670 rows of data in my spreadsheet but the row count brings back 694 rows.

var excelApp = new Microsoft.Office.Interop.Excel.Application {Visible = false};
var workbook = excelApp.Workbooks.Open(_mirrorFileName,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);

var worksheet = (Worksheet)workbook.Worksheets[1];
var excelRange = worksheet.UsedRange;            
var valueArray = (object[,])excelRange.Value[XlRangeValueDataType.xlRangeValueDefault];

var rowCount = worksheet.UsedRange.Rows.Count;

Should I be using UsedRange to find the row count or is there another method???

I dont seee how `UsedRange.Rows.Count` can return lesser number of rows than what is present. If I put a value in cell A1 & another value in cell A1048576, `UsedRange.Rows.Count` returns 1048576. — shahkalpesh, Jun 17 '14 at 13:04
change var valueArray = (object[,])excelRange.Value[XlRangeValueDataType.xlRangeValueDefault]; by var valueArray = (object[,])excelRange.Value; — Rama, Feb 19 '15 at 15:55

score 7 · Accepted Answer · answered Jun 17 '14 at 13:13

Try the following sample code,

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel; 

namespace WindowsApplication1
{
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        Excel.Application xlApp ;
        Excel.Workbook xlWorkBook ;
        Excel.Worksheet xlWorkSheet ;
        Excel.Range range ;

        string str;
        int rCnt = 0;
        int cCnt = 0;

        xlApp = new Excel.ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        range = xlWorkSheet.UsedRange;

        rCnt = range.Rows.Count;
        cCnt = range.Columns.Count;

        xlWorkBook.Close(true, null, null);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);
    }
}

Declare variable Excel.Range range and then use it.

This works great! But I still don't get why my original code is incorrect? — wakthar, Jun 17 '14 at 13:31
Actually you have used **var** to create your object like **var worksheet = (Worksheet)workbook.Worksheets[1];** Instated of that just use the **Microsoft Excel xx.0 Object Library** to create objects variables like **Excel.Worksheet xlWorkSheet; Excel.Range range;** — Thirusanguraja Venkatesan, Jun 17 '14 at 13:40
it is skipping null value and when you will use in loop will be incorrect — Vazgen Torosyan, Dec 07 '15 at 18:25
I know this is a 7 year old question. I had a similar issue, but I had to take the start row of the used range into account. Using sheet.UsedRange.Row + sheet.UsedRange.Rows.Count resolved my issue. — Dick Bos, Oct 11 '21 at 09:47

Interop Excel UsedRange Rows Count incorrect

1 Answers1

Linked