Blank cell is not considered as blank

Aspose.Cells Product Family
1

I have issue calculating the formula using aspose, if data in the cell is empty and checking the condition in excel formula, that leads to error. I am using the below code to insert values in the workbook, if data empty it will insert the empty string. In the image attached, the table is generated through aspose and result is error, and table 2 was typed manually and it gives the result. And the formula is =SUMPRODUCT((K12:K15=“productB”)*1,(L12:L15<>"")*1,(M12:M15<>"")*1,(N12:N15<>"")*1,(O12:O15<>"")*1,O12:O15,M12:M15+N12:N15+L12:L15)

          for (int rowIndex = 0; rowIndex < rows.Count; rowIndex++)
                {
                    for (int columnIndex = 0; columnIndex < _columnHeaders.Count(); columnIndex++)
                    {
                        string cellValue = string.Empty;
                        if (rows[rowIndex].ContainsKey(_columnHeaders[columnIndex]))
                        {
                            cellValue = rows[rowIndex][_columnHeaders[columnIndex]];
                        }
                        worksheetData.Cells[rowIndex + 2, columnIndex].PutValue(cellValue);
                    }
                }

image.png (3.8 KB)

2

@kiran5388

Your query is related to Aspose.Cells. So, we are moving this forum thread to Aspose.Cells forum where you will be guided appropriately.

3

@kiran5388,

Could you please zip and attached the following.

  1. a standalone VS.NET console application
  2. Template Excel file (if any).

Once we have the above resources, we will start evaluating your issue on our end.

4

@kiran5388
If string.Empty is set, the value of the cell is changed as string, so SUMPRODUCT could not get the correct value.Please change your codes
string cellValue = string.Empty;
as
string cellValue = null;

5

@kiran5388,

Please note, Aspose.Cells works the same way as MS Excel does. MS Excel takes blank cells as null values, so it works for SUMPRODUCT formula/function in it. For Aspose.Cells, you got to input cell value as “null”, so SUMPRODUCT should work properly.

Hope, this helps a bit.

6

yeah, I figured it out yesterday. Its working. Thanks though.

7

@kiran5388,

Good to know that it is already sorted out. In the event of further queries or issue, feel free to write us back.