Write nullable integer values back to spreadsheet

0

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?

c#
excel
excel-interop
asked on Stack Overflow Feb 4, 2020 by gilesrpa • edited Feb 4, 2020 by Konrad Rudolph

1 Answer

0

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