Gembox null value breaking formula

Question

I am creating an Excel spread sheet using C# and Gembox.

I get the data from SQLServer in a datatable and I have a situation where if a cell in the datatable is null, putting that into the excel object results in any formulas that rely on that Excel cell breaking.

e.g. I get this:

You can see that if the P4 2015 column has an empty cell then the formula in the var column breaks. If I select the cell in Excel and press Delete then Enter the formula works.

So Gembox is putting something into the cell which breaks the formula but I'm not sure what. I tried to check for Null values and insert an empty string instead but that doesn't work.

Has anyone seen this before, or know how to resolve it? (Note - I need the cell to be empty - I don't want to put a 0 in there).

GemBox.Spreadsheet will not write anything to cell if it doesn't have any value. Nevertheless what version of GemBox.Spreadsheet are you using? Also what formula you have there (I was unable to reproduce this with simple "=A1+B1+C1+D1" formula)? Last just for confirmation you're using InsertDataTable method to import DataTable into ExcelWorksheet, is that correct? — Mario Z, Jun 16 '16 at 17:21
@MarioZ Gembox 3.7 for .Net 4.5. The formula is a simple one, =E60-F60 and the data doesn't match the excel table so I'm looping through rows/columns to get what I require (It's a very complex set of SQL queries to create the above table). I managed to resolve by checking for `string.IsNullOrEmpty` and specifically inserting a `null` object into the Excel cell. I'll add my answer now. — Percy, Jun 17 '16 at 08:55

score 2 · Accepted Answer · answered Jun 17 '16 at 08:59

I managed to get around this error using the following:

Basically I check the value I am inserting into the Excel cell - if it is an empty string then or null then I specifically insert null into the spreadsheet.

if (string.IsNullOrWhiteSpace(value))
{
    worksheet.Cells[columnCode + row].Value = null;
    format = "0";
}
else
{
    //insert as normal...
}

I think all the issues I had were caused by an empty string inserted into a number column on the spreadsheet.

Gembox null value breaking formula

1 Answers1