We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Subtotal

Hello,

I want each sub total in column F would be =IF(D=0,0,(E/D)*100) (yellow line, in my enclosed xls)

how to do this with Aspose Cells?

many thanks

Regards

Hi Winanjaya,


Thank you for contacting Aspose support.

By looking at your provided spreadsheet we think your requirement cannot be achieved using MS Excel. If you could find a way to achieve your goal (setting custom formula for subtotal) using MS Excel application then please share it here so we could try to provide a solution while using Aspose.Cells API. Please note, Aspose.Cells APIs follow MS Excel guidelines in their implementation, therefore anything supported by MS Excel can be achieved using the API & vice versa.

You may consider using the Shared Formula option exposed by Aspose.Cells API to apply a custom formula on a range of cells. Please check the below provided code snippet and attached resultant spreadsheet for your reference.

C#

//Instantiate a Workbook from existing file
Workbook workbook = new Workbook(file);

//Get the cells collection in the first worksheet
Cells cells = workbook.Worksheets[“Profit Comm”].Cells;

//Apply the shared formula in the range i.e…, F6:F24
cells[“F6”].SetSharedFormula("=IF(D7=0,0,(E6/D6)*100)", 19, 1);

//Save the excel file
workbook.Save(myDir + “output.xlsx”, SaveFormat.Xlsx);

Please let us know if we have misunderstood your requirements.

Hello,


Yes, you are right!..
and it resolved by using SetSharedFormula…

Thanks a lot!

Hi,


Good to know that setting shared formulas works for your requirements. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.