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.
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?
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.
User contributions licensed under CC BY-SA 3.0