Get SUM from excel expressoion

Aspose.Cells Product Family
,
1

I have different data sources to create dynamic tables in excel. I need to get the sum of each group. But grouping didn’t help that. here is a sample

image.png (7.7 KB)

2

@ishankapr
Would you like to provide your sample file and test code? We will check it soon.

3

Template_Dynamic.zip (17.7 KB)

Im using excel template and expressions for this

4

@John.He found anything?

5

@ishankapr
Please change cell Q6 to ‘=SUM (Q5: Q5)’. We can obtain the correct results by adding some data and testing with the following sample code. Please refer to the attachment. result.zip (18.7 KB)

// Create a new workbook and access its worksheet
Workbook workbook = new Workbook(filePath + "Template_Dynamic.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

List<ClearedOtherTransactions> clearedOtherTransactions = new List<ClearedOtherTransactions>();
clearedOtherTransactions.Add(new ClearedOtherTransactions("07.24.2024", "00001763", "", "Receivable Batch for 000064 = 19/09/2024", 80));
clearedOtherTransactions.Add(new ClearedOtherTransactions("07.25.2024", "00001763", "", "Receivable Batch for 000064 = 19/09/2024", 90));
clearedOtherTransactions.Add(new ClearedOtherTransactions("07.26.2024", "00001763", "", "Receivable Batch for 000064 = 19/09/2024", 80.5));

List<UnClearedPayments> unClearedPayments = new List<UnClearedPayments>();
unClearedPayments.Add(new UnClearedPayments("07.24.2024", "00001763", "", "Receivable Batch for 000064 = 19/09/2024", 80));
unClearedPayments.Add(new UnClearedPayments("07.25.2024", "00001763", "", "Receivable Batch for 000064 = 19/09/2024", 90));
unClearedPayments.Add(new UnClearedPayments("07.26.2024", "00001763", "", "Receivable Batch for 000064 = 19/09/2024", 80.5));

// Create a workbook designer
WorkbookDesigner designer = new WorkbookDesigner(workbook);

// Set the data source and process smart marker tags
designer.SetDataSource("ClearedOtherTransactions", clearedOtherTransactions);
designer.SetDataSource("UnClearedPayments", unClearedPayments);
designer.Process();

designer.Workbook.CalculateFormula();
// Save the workbook
workbook.Save(filePath + "out_net.xlsx", SaveFormat.Xlsx);

class UnClearedPayments
{
    private string date;
    private string transactionId;
    private string transactionType;
    private string descripton;
    private double amount;
    public UnClearedPayments(string date, string transactionId, string transactionType, string descripton, double amount)
    {
        this.date = date;
        this.transactionId = transactionId;
        this.transactionType = transactionType;
        this.descripton = descripton;
        this.amount = amount;
    }

    public string Date { get => date; set => date = value; }
    public string TransactionId { get => transactionId; set => transactionId = value; }
    public string TransactionType { get => transactionType; set => transactionType = value; }
    public string Description { get => descripton; set => descripton = value; }
    public double Amount { get => amount; set => amount = value; }
}
class ClearedOtherTransactions
{
    private string date;
    private string transactionId;
    private string transactionType;
    private string descripton;
    private double amount;
    public ClearedOtherTransactions(string date, string transactionId, string transactionType, string descripton, double amount)
    {
        this.date = date;
        this.transactionId = transactionId;
        this.transactionType = transactionType;
        this.descripton = descripton;
        this.amount = amount;
    }

    public string Date { get => date; set => date = value; }
    public string TransactionId { get => transactionId; set => transactionId = value; }
    public string TransType { get => transactionType; set => transactionType = value; }
    public string Payments_Description { get => descripton; set => descripton = value; }
    public double Payments_Amount { get => amount; set => amount = value; }
}

If you still have questions, please provide your test code and we will check it soon.

6

@John.He i just using data sources and the excel template. without much coding with smart makers. so i expect some expression like this “&=UnClearedPayments.Amount(group:sum)” . because in one excel i have several data sets to show. this =SUM (Q5: Q5) will be dynamic while looping through the data. thanks for the reply (y)

7

@ishankapr
You are welcome. Thank you for your feedback. Additionally, regarding smart markers, you can also refer to the following documents.

8

designer.Workbook.CalculateFormula();

I think i forgot to add this line. Thank you very much it’s working now. You saved lot of time

9

@ishankapr,

You are welcome and it’s good to know you have resolved the issue. Indeed, it is always preferable to call the Workbook.CalculateFormula() method to calculate formulas in the spreadsheet.

1 Like