Write nullable integer values back to spreadsheet


I am using Excel as a reporting tool, for my desktop application. The app is written in C# (VS 2019).

The gist of my code is to obtain a range object from the worksheet, populate an array of null-able integers and write it back out to the same worksheet.

Basic worksheet structure

So far I have obtained a range of cells, where startRow is the first row highlighted in yellow:

Range startRow = (Range) input.Range["E" + row.ToString(), "K" + row.ToString()];

Initialise the local array as:

double?[,] startArray = new double?[1, 7];

Set the relevant values:

startArray[1,2] = 9.75;

And finally, write the data back:

  startRow.Value2 = startArray;

When I write the array back, I get the error

The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))

In this case, I assume that it is because I have some null-able values in the array, as if I do not use a null-able array and as a result its values are zero by default, this works OK, but wites zeros out into the spreadsheet.

I did try using:

string[,] startArray = new string[1, 7];

which does work, but any fields that does have a value, are not seen as numeric and causes Excel formulae not to work.

So to my question, can I get the array to write back to Excel with nulls, or do I have to revert to using the cell object and only update the cells which contain a value?

asked on Stack Overflow Feb 4, 2020 by gilesrpa • edited Feb 4, 2020 by Konrad Rudolph

1 Answer


As it happens, just the fact of writing this post has enabled me to fix it.

The simple answer is to write directly to the range only the values which contain a value.

So something like this:

for (int i = 0; i < 7; i++)
    if (startArray[0, i] != null) startRow[1, i + 1] = startArray[0, i];

As for EPPlus, it looks a fantastic product, but as I already pay for an Excel license would find this a little over kill as a paid license would also be needed for EPPlus.

answered on Stack Overflow Feb 4, 2020 by gilesrpa

User contributions licensed under CC BY-SA 3.0