Exception while reading a cell with datatype number and cell is empty.

Oct 7, 2014 at 1:43 PM

I am getting a runtime exception 'Input string was not in a correct format' when I am trying to read a cell of type 'Number' and when its blank in the excel sheet.

Below is the code while writing the excel. Here I m explicitly inserting blank when the value is zero and setting the datatype as XLCellValues.Number.
var locationValue = GetLocValue(1);
var cellindex = "A1";
.SetValue(locationValue  == 0 ? string.Empty : locationValue.ToString())
Below is the code while reading the generated excel. Its throwing an exception when m tring the read the same cell "A1" when its blank.
var LocationId = worksheet.Cell("A1").Value; // Runtime exception 'Input string was not in a correct format' when its blank.
Does anyone came across such scenario? Any solution for this?
Oct 7, 2014 at 4:46 PM
That's because you're setting the data type of number to a cell that contains a string. You probably meant to set the cell format/style as number. For that you can use the number format id of 2 (Excel's default). See https://closedxml.codeplex.com/wikipage?title=NumberFormatId%20Lookup%20Table
Oct 9, 2014 at 9:07 AM
Edited Oct 9, 2014 at 9:07 AM
Thanks MDeLeon for ur reply.

The exception issue is solved, but Arithmetic operations are not working when i try to reference those cells.

In the generated execl, Cell A3 is supposed to be 300, but it is showing 0 in-spite there are values under A1 and A2.
var locationValue1 = 100;
var cellindex = "A1";
.SetValue(locationValue1  == 0 ? string.Empty : locationValue1.ToString());
worksheetProposal.Cell(cellindex ).Style.NumberFormat.NumberFormatId = 2;

var locationValue2 = 200;
cellindex = "A2";
.SetValue(locationValue2  == 0 ? string.Empty : locationValue2.ToString());
worksheetProposal.Cell(cellindex ).Style.NumberFormat.NumberFormatId = 2;

cellindex = "A3";
var formula = "SUM(A1:A2)";
worksheetProposal.Cell(cellindex ).SetFormulaA1(formula);
See the attachment for the excel sheet.

Oct 10, 2014 at 6:16 AM
Does anyone has a solution for this??
Oct 10, 2014 at 2:05 PM
The problem is in your code. You're trying to add 2 strings.