Working with formula by importing data into Workbook using Aspose.Cells for .NET in C#

Aspose.Cells Product Family
1
I am inserting data into my worksheet starting at cell A4.
I am guaranteed to always have at least one row.
Here is my code to set the formula (I want to count the rows of data) on the worksheet and then calculate the formulas:

worksheet.Cells.ImportDataTable(dataTable, false, "A4");
worksheet.Cells["B2"].Formula = string.Format("=SUBTOTAL(3,B$4:B${0})", worksheet.Cells.MaxRow);
workbook.CalculateFormula();

My resulting workbook shows the following formula in "B2" after I generate the workbook:
=SUBTOTAL(3,B$3:B$4)
Which results in a sum of 2 and the result should be 1.

This ONLY occurs when I have ONE row.

On a side note, should I be using =SUBTOTAL(3,B$3:B$4) or =COUNTA(B$4:B$4)?

Also, when writing the datatable with NULL values in the cell, both the SUBTOTAL and COUNTA formula will not register the cell as being blank. After the workbook is generated when I click in the cell there is no data. I hit delete and enter and the formula then registers that the cell is empty and the formula picks it up. Any thoughts?
2

Hi Pouya,

Thanks for your posting and using Aspose.Cells.

Please download and use the latest version: Aspose.Cells for .NET (Latest Version) and see if it makes any difference.

If your issue persist, then please provide us a runnable sample code replicating this issue with the latest version. It will help us look into your issue precisely and we will be able to update you asap.

3

Attached you will find a solution that contains sample code that replicates this issue.

I would like the resulting workbook to display the following correct information:
B2 should display 1 (because there is only one row)
O2 should display 0 (because there is no data)
P2 should display 0 (because there is no data)
Q2 should display 0 (because there is no data)
4

Hi Pouya,

Thanks for your sample project and using Aspose.Cells.

Actually, worksheet.Cells.MaxRow gives the index of maximum row, this index is 0-based. It means, if your maximum row is 10, then it will give you 9. Similarly, if your maximum row is 11, then MaxRow will give you 10.

So, in order to get actual maximum row, you will have to add 1 in MaxRow.

It means, you will have to change your following code. Changes are highlighted in red.

worksheet.Cells[“B2”].Formula = string.Format("=COUNTA(B$4:B${0})", worksheet.Cells.MaxRow);
worksheet.Cells[“O2”].Formula = string.Format("=COUNTA(O$4:O${0})", worksheet.Cells.MaxRow);
worksheet.Cells[“P2”].Formula = string.Format("=COUNTA(P$4:P${0})", worksheet.Cells.MaxRow);
worksheet.Cells[“Q2”].Formula = string.Format("=COUNTA(Q$4:Q${0})", worksheet.Cells.MaxRow);

into this

worksheet.Cells[“B2”].Formula = string.Format("=COUNTA(B$4:B${0})", worksheet.Cells.MaxRow + 1);
worksheet.Cells[“O2”].Formula = string.Format("=COUNTA(O$4:O${0})", worksheet.Cells.MaxRow + 1);
worksheet.Cells[“P2”].Formula = string.Format("=COUNTA(P$4:P${0})", worksheet.Cells.MaxRow + 1);
worksheet.Cells[“Q2”].Formula = string.Format("=COUNTA(Q$4:Q${0})", worksheet.Cells.MaxRow + 1);

I have attached the output xlsx file generated after the above code change for your reference. As you can see, it has correct formulas and their values are also correct.

5

Yes, that fixes the count problem.

What about the issue where COUNTA is not picking up the NULL value previously written?
6

Hi Pouya,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your first issue is resolved. For second issue, please explain the issue in more detail.

Do you see any problem with the attached file as it is generated by your code?

Attachment: WorkbookWithIssue.xlsx

It looks fine to me and I do not see any issue with the COUNTA formula.

7

I have attached another solution with the problem.

The last 3 columns of the generated workbook should be zero since there is no data.
They are counting as 1.

The problem seems to be that string.Empty or “” will not be considered as empty or “no data” in excel.
Is that an ASPOSE issue?
8

Hi Pouya,

Thanks for your sample project and using Aspose.Cells.

Actually, you are inserting Empty string in those cells, therefore the cells are not being considered empty or “no data” cells.

You should use null instead of empty string “”. It will fix your issue.

So, please change your following code.

dataRow[“Parent Org”] = “Advantage Health Solutions”;
dataRow[“Contract”] = “H5508”;
dataRow[“Opt Supp Problem Summary”] = “”;
dataRow[“Margin Problem”] = “”;
dataRow[“Retention Problem”] = “”;
_dataTable.Rows.Add(dataRow);

into this

dataRow[“Parent Org”] = “Advantage Health Solutions”;
dataRow[“Contract”] = “H5508”;
dataRow[“Opt Supp Problem Summary”] = null;
dataRow[“Margin Problem”] = null;
dataRow[“Retention Problem”] = null;
_dataTable.Rows.Add(dataRow);

Now, Aspose.Cells will insert no data in these cells. And COUNTA formula will return correct result.

9

Ugh…

Thank you…