How To remove #NA from Excel Range in C#

Question

I have been trying to figure this out for a while now. But solutions like this and this are not solving my problem.

I'm adding data from Database to Excel with the following code :-

//Adding Table values in Excel
            int sum = 0;
            object[,] arr = new object[result.Rows.Count, result.Columns.Count];
            for (int r = 0; r < result.Rows.Count; r++)
            {
                DataRow dr = result.Rows[r];
                for (int c = 0; c < result.Columns.Count; c++)
                {
                    arr[r, c] = dr[c];
                }
                    sum = sum + Convert.ToInt32(dr[5]);
            }


            Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)ws.Cells[20, 1];
            Microsoft.Office.Interop.Excel.Range c4 = (Microsoft.Office.Interop.Excel.Range)ws.Cells[result.Rows.Count + 20, result.Columns.Count];

            Microsoft.Office.Interop.Excel.Range range1 = ws.get_Range(c3, c4);
            range1.Value = arr;

Now on the line range1.Value = arr; it is adding another row with all columns value as #N/A when I debug the code the value shown there is -2146826246.

First, I don't understand why is it adding a row of it's own at the end? Second, why the values are coming as #N/A and how to resolve this?

Vityata · Answer 1 · 2019-11-02T15:32:42.253

The #N/A error in Excel (#N/V in German) shows up, when the range is bigger than the array. Thus, if the myRange is [5,5] and the myArray is [4,4], the

myRange.Value = myArray would return this:

How to avoid it - do not assign small array to a bigger range. Or loop through the range and check for # sign. Then do something with it. This is some code to "play" with, seeing how it assigns values from array to range:

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

namespace excelTest
{
    class Program
    {
        static void Main()
        {
            Excel.Application excel = new Excel.Application
            {
                Visible = true,
                DisplayAlerts = false
            };

            Excel.Workbook wbk = excel.Workbooks.Add(Type.Missing);
            Excel.Worksheet wks = wbk.Worksheets[1];

            Excel.Range cellA1 = wks.Cells[1, 1];
            Excel.Range cellE5 = wks.Cells[5, 5];
            Excel.Range myRange = wks.get_Range(cellA1, cellE5);

            int[,] myArray = new int[4, 4];
            for (int col = 0; col < myArray.GetLength(0); col++)
            {
                for (int row = 0; row < myArray.GetLength(1); row++)
                {
                    myArray[col, row] = (col + row) * 2;
                }
            }
            myRange.Value = myArray;
        }

This is the Excel.Workbook Open:

public static Excel.Workbook Open(Excel.Application excelInstance,
        string fileName, bool readOnly = false, bool editable = true,
        bool updateLinks = true)
{
    Excel.Workbook book = excelInstance.Workbooks.Open(
        fileName, updateLinks, readOnly,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing);
    return book;
}

How To remove #NA from Excel Range in C#

1 Answers1