Looping through each cell in excel/csv sheet

Question

This question is related to my last post (link). I want to go through each cell so that I can clean my sheet of non-ascii value (function ReturnCleanASCII ) is being used for this. However I am getting a null value when I am going through the cells.

Code

foreach (Excel.Range range in xlRange.Cells){
          Console.WriteLine(range.Value2.ToString());
}

I am using the code above to go through each cell. Is this a incorrect way to go through each cell?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\username\Desktop\Error Records.csv");
            Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;

            int lastUsedRow = xlWorksheet.Cells.Find("*", System.Reflection.Missing.Value,
                System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious,
                false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;

            int lastUsedColumn = xlWorksheet.Cells.Find("*", System.Reflection.Missing.Value,
                System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlPrevious,
                false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Column;

            int lastColumnCount = lastUsedColumn;



            for (int i = 1; i <= lastUsedColumn; i++)
            {
                for (int j = 1; j <= lastUsedRow; j++)
                {
                    xlWorksheet.Cells[j, (lastColumnCount + 1)] = "Testing data 134";
                }
            }

            foreach (Excel.Range range in xlRange.Cells)
            {
                Console.WriteLine(range.Value2.ToString());
            }


            xlWorksheet.Cells[1, (lastUsedColumn + 1)] = "Title";
            xlWorkbook.Save();
            //cleanup
            GC.Collect();
            GC.WaitForPendingFinalizers();

            //rule of thumb for releasing com objects:
            //  never use two dots, all COM objects must be referenced and released individually
            //  ex: [somthing].[something].[something] is bad

            //release com objects to fully kill excel process from running in the background
            Marshal.ReleaseComObject(xlRange);
            Marshal.ReleaseComObject(xlWorksheet);

            //close and release
            var data = ReturnCleanASCII(xlWorksheet.ToString());
            xlWorkbook.SaveAs("C:\\Users\\username\\Desktop\\Errors_four.csv".Trim(), Excel.XlFileFormat.xlCSV);
            xlWorkbook.Close();
            Marshal.ReleaseComObject(xlWorkbook);

            //quit and release
            xlApp.Quit();
            Marshal.ReleaseComObject(xlApp);

        }

        public static string ReturnCleanASCII(string s)
        {
            StringBuilder sb = new StringBuilder(s.Length);
            foreach (char c in s.ToCharArray())
            {
                if ((int)c > 127) // you probably don't want 127 either
                    continue;
                if ((int)c < 32)  // I bet you don't want control characters 
                    continue;
                if (c == ',')
                    continue;
                if (c == '"')
                    continue;
                sb.Append(c);
            }
            return sb.ToString();
        }
    }
}

error:

Cannot perform runtime binding on a null reference

stacktrace:

   at CallSite.Target(Closure , CallSite , Object )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
   at CallSite.Target(Closure , CallSite , Object )
   at ConsoleApplication1.Program.Main(String[] args) in C:\Users\username\documents\visual studio 2015\Projects\ConsoleApplication1\ConsoleApplication1\Program.cs:line 44
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

@gavsta707, yes. It breaks on Console.Writeline code with error Cannot perform runtime binding on a null reference — Maddy, Jun 29 '17 at 17:58
Stacktraces don't read things. They express the error. You need to share that with us if you want us to have any clue what is happening — Joe Phillips, Jun 29 '17 at 18:03

score 1 · Accepted Answer · answered Jun 29 '17 at 18:14

1

Use Convert.ToString(range.Value2) rather than range.Value2.ToString()

See: Cannot perform runtime binding on a null reference - Empty Excel Cells

answered Jun 29 '17 at 18:14

Joe Phillips

49,743
32
103
159

I found this by copying and pasting your error message and then adding the word "excel" to it. It was the first result in google. – Joe Phillips Jun 29 '17 at 18:16
Thank you for the help and I did try that. I guess I have been stuck on it for a while so might have missed that. – Maddy Jun 29 '17 at 18:27

Looping through each cell in excel/csv sheet

1 Answers1