Aspose cells RemoveFormulas is not removing the formulas

Aspose.Cells Product Family
1

Hi Team,

In C# .NET we are using the code below to remove the formulas from worksheets but even after the execution we could be able to see the formulas in worksheet cells.

            _workbook.Worksheets.ForEach(worksheet => worksheet.Cells.RemoveFormulas());
2

@sgovindan,

The code should remove formulas and replace the formulas with calculated values in the cells. Don’t you get this behavior/result? If so, kindly do provide your template Excel file. We will check your issue soon.

PS. please zip the Excel file prior attaching here.

3

Hi @amjad.sahi , please find the sample console app, input and output excel file.

Problem 1) If you see created_one.xlsx file below correct formula is not getting calculated

&=Subtotal1:OppLineItems.SALESPRICE

Problem 2) We want to remove all the formulas even if the formula is wrong, how to do that. The example below formulas is not valid even though we want to remove them from the worksheet.

&=Subtotal1:OppLineItems.OPPORTUNITY_LINEITEM_SALESPRICE

Exel.zip (17.3 KB)

Aspose_ticket_excel.zip (182.0 KB)

4

@sgovindan
Thank you for your feedback and the provided sample files. By testing with sample files and code on the latest version v24.12, we can reproduce the issue. The smart tags in the template file are still retained after calling WorkbookDesigner.Process(false).

As a temporary solution. If you want to delete these smart markers, please refer to the following example code. Please refer to the attachment. out_net.zip (8.8 KB)

DataSet ds = new DataSet();

DataTable dtMaster = new DataTable("Master");
DataColumn dtcol1 = new DataColumn("ACCOUNT");
DataColumn dtcol2 = new DataColumn("ACCOUNT_CREATEDBY_NAME");
dtMaster.Columns.Add(dtcol1);
dtMaster.Columns.Add(dtcol2);
DataRow dr = dtMaster.NewRow();
dr["ACCOUNT"] = "Test_Account";
dr["ACCOUNT_CREATEDBY_NAME"] = "Test_user";
dtMaster.Rows.Add(dr);
ds.Tables.Add(dtMaster);

DataTable lineIteams = new DataTable("OppLineItems");
DataColumn lineIteamscol1 = new DataColumn("Id");
lineIteamscol1.DataType = typeof(Int32);
DataColumn lineIteamscol2 = new DataColumn("Name");
DataColumn lineIteamscol3 = new DataColumn("SALESPRICE");
lineIteamscol3.DataType = typeof(Int32);
lineIteams.Columns.Add(lineIteamscol1);
lineIteams.Columns.Add(lineIteamscol2);
lineIteams.Columns.Add(lineIteamscol3);
DataRow drlineIteams = lineIteams.NewRow();
drlineIteams["Id"] = 1;
drlineIteams["Name"] = "lineIteam1";
drlineIteams["SALESPRICE"] = 10;
lineIteams.Rows.Add(drlineIteams);

DataRow drlineIteams2 = lineIteams.NewRow();
drlineIteams2["Id"] = 2;
drlineIteams2["Name"] = "lineIteam2";
drlineIteams2["SALESPRICE"] = 20;
lineIteams.Rows.Add(drlineIteams2);
ds.Tables.Add(lineIteams);
ds.AcceptChanges();


Console.WriteLine($"Start.....");
Workbook wkb = new Workbook(filePath + "TemplateNewAll.xlsx");
WorkbookDesigner _designer = new WorkbookDesigner { Workbook = wkb };
_designer.SetDataSource(ds);
_designer.Process(false);

wkb.CalculateFormula(true);

foreach (Worksheet worksheet in wkb.Worksheets)
{
    worksheet.Cells.RemoveFormulas();
    RemoveSmartMarkers(worksheet.Cells);
}


// Save the Workbook as .xlsx file.
wkb.Save(filePath + "out_net.xlsx");
Console.WriteLine($"End.....");

private static void RemoveSmartMarkers(Cells cells)
{
    IEnumerator iter = cells.GetEnumerator();
    while (iter.MoveNext())
    {
        Cell currCell = (Cell)iter.Current;
        string str = currCell.StringValue;
        if (!string.IsNullOrEmpty(str) && str.StartsWith("&="))
        {
            currCell.PutValue("");
        }
    }
}

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-57613

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

6

@sgovindan
“&=Subtotal1 ” subtotal smart marker should follow by the data smart marker.
Why do you add as the fine? We can not know how to process it.

7

@sgovindan,

Moreover, your issue (Ticket ID: “CELLSNET-57613”) has been resolved now. The fix/enhancement will be incorporated in the upcoming release (Aspose.Cells v25.1), scheduled for release either before the end of this week or in the next week early. You will be notified once the new version is published.

8

Thanks for your support.

9

@sgovindan,

You are welcome.