Excel Returns Null After Line 144 using C# Microsoft Interop Excel

Question

In C#, I've opened an Excel spreadsheet and I am iterating through it. Whenever I get to line 144, the spreadsheet value returns null for every single line following it. Looking at the spreadsheet, it is clearly not null for lines 144 - 250. I tried saving it in different versions but that didn't work. I've tried copying and pasting into a brand new worksheet. Nothing has worked. Here is my code:

        // initiate Excel
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open(lblFileName.Text, 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);

        //Get the used Range
        Excel.Range usedRange = xlWorkSheet.UsedRange;

        //Iterate the rows in the used range
        string customerNumber;
        string amount;
        int i = 0;
        decimal dAmount = 0;

        bool runProcess = true;
        bool hasHeader = false;
        string cellValue;
        foreach (Excel.Range row in usedRange.Rows)
        {
            i++; 
            // Column 1 is customer number
            // THE FOLLOWING LINE RETURNS NULL STARTING AT ROW 144
            cellValue = row.Cells[i, 1].Value2 == null ? "" : row.Cells[i, 1].Value2.ToString();
            if (!(cellValue.Length >= 5 && cellValue.Length <= 20))
            {
                    MessageBox.Show(cellValue + " Must be between 5 and 20 Characters - File Will Not Be Processed Row#" + i.ToString());
                    runProcess = false;
            }

        }
        // run process
        // release EXCEL

I did try row.Cells[i, 1].Value2, row.Cells[i, 1].Value and row.Cells[i, 1].Text and got the same results for all. I am including "using Excel = Microsoft.Office.Interop.Excel;"

Sample Data from Excel

 4492605768531895  15.95
 4492605768536544  19.95
 4492605768565459  11.95
 4492605739542347  14.95
 4492605768635795  25.95

Is your sample data from the erroneous rows or is it just generic data from your spreadsheet? — James Gould
– James Gould, Commented Sep 27, 2016 at 14:32
It's from the erroneous rows. I have copied data from my good rows into the erroneous rows and the error persists. — Missy
– Missy, Commented Sep 27, 2016 at 14:33
Do all the values in your spreadsheet start with 44 or is it when they begin to start with 44 that you run into an error? Just trying to narrow it down :-) — James Gould
– James Gould, Commented Sep 27, 2016 at 14:37
To follow up on @Mis's answer, do all of your values exceed the precision of 15 digits? — James Gould
– James Gould, Commented Sep 27, 2016 at 14:42

Magnetron · Accepted Answer · 2016-09-27 16:18:47Z

1

I think it has something to do with the Range object size. I tried replace the foreach to this and it worked (for me the null was occurring in line 102)

int maxRows = usedRange.Rows.Count;
for (int i=1;i<=maxRows;i++)
{
    // Column 1 is customer number
   // THE FOLLOWING LINE RETURNS NULL STARTING AT ROW 144
   cellValue = xlWorkSheet.Cells[i, 1].Value2 == null ? "" : xlWorkSheet.Cells[i, 1].Value2.ToString();

    if (!(cellValue.Length >= 5 && cellValue.Length <= 20))
    {
        MessageBox.Show(cellValue + " Must be between 5 and 20 Characters - File Will Not Be Processed Row#" + i.ToString());
        runProcess = false;
    }

}

answered Sep 27, 2016 at 16:18

Magnetron

8,8232 gold badges31 silver badges46 bronze badges

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

1 Comment

Missy Over a year ago

Thank you so much. I will try it and let you know what the outcome is.

Collectives™ on Stack Overflow

Excel Returns Null After Line 144 using C# Microsoft Interop Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related