How to set points as "Set as Total" in Aspose.Cells Charts?

Aspose.Cells Product Family
, , , ,
1

Setastotal.jpg (47.2 KB)
Setastotal2.png (15.6 KB)
SampleSheet.zip (54.2 KB)

Hi everyone,

I’m currently working on generating charts using Aspose.Cells in my application. I have a requirement where I need to set some points in the chart as “Set as Total”. However, I couldn’t find any functionality or method in the Aspose.Cells library that allows me to achieve this. I need to set multiple points as total according to my case.

I have already tried exploring the documentation and searching online, but I couldn’t find any relevant information. I have also checked the available properties and methods for the Chart and ChartPoint classes, but none of them seem to provide an option for setting points as “Set as Total”.

Could someone please guide me on how to achieve this functionality in Aspose.Cells? Is there any workaround or alternative approach that I can use to set points as “Set as Total” in the generated charts?

Any help or suggestions would be greatly appreciated. Thank you in advance!

Best regards,

2

@yunusbayrak
Would you like to provide your sample file and test code? If you can provide the expected result file, it will be very helpful for us to locate the issue. We will check it soon.

1 Like
3

@yunusbayrak,

Could you please try:
e.g.,

//Set 6th chart (data) point in the first series as Total
chart.NSeries[0].LayoutProperties.Subtotals = new int[] { 6 };
1 Like
4

@John.He
Sure, I’ve attached the sample excel sheet and sample code, But in my case the rows may be increased and I will set the chart series according to the row/year count.

@amjad.sahi
I’ve tried the code but ,it didn’t help.

I’ve attached the sample sheet to the first message.

Here is the code:

var recordStartRowInx = 3;
var yearColumnInx = 2;

Chart? chart = worksheet.Charts?.FirstOrDefault(x => x.Name =="Graphiq5");        

chart.NSeries[0].Values = $"{Helper.CellIndexToName(recordStartRowInx, yearColumnInx)}:{Helper.CellIndexToName(recordStartRowInx, yearColumnInx)}";
chart.NSeries[0].XValues = $"{Helper.CellIndexToName(recordStartRowInx, yearColumnInx - 1)}:{Helper.CellIndexToName(recordStartRowInx , yearColumnInx - 1)}";
var totalColumnIndexList = new List<int>();
//set some points as total column ?
for (int i = 0; i < chart.NSeries[0].Points.Count; i++)
{//to get 
    if(i%4 == 0)
    {
        totalColumnIndexList.Add(i+1);
    }
   ChartPoint point = chart.NSeries[0].Points[i];
}
chart.NSeries[0].LayoutProperties.Subtotals = totalColumnIndexList.ToArray();
5

@yunusbayrak
By testing on the latest version v24.8 using the following sample code, we can see that the relevant data has been correctly set. Please refer to the attachment. result.zip (136.3 KB)

Would you like to provide your expected result file? We will check it soon.

Workbook wb = new Workbook(filePath + "SampleSheet.xlsx");
var recordStartRowInx = 3;
var yearColumnInx = 2;
Worksheet worksheet = wb.Worksheets[0];
Chart chart = worksheet.Charts?.FirstOrDefault(x => x.Name == "Graphiq5");

//chart.NSeries[0].Values = $"{CellsHelper.CellIndexToName(recordStartRowInx, yearColumnInx)}:{CellsHelper.CellIndexToName(recordStartRowInx, yearColumnInx)}";
//chart.NSeries[0].XValues = $"{CellsHelper.CellIndexToName(recordStartRowInx, yearColumnInx - 1)}:{CellsHelper.CellIndexToName(recordStartRowInx, yearColumnInx - 1)}";
            
var totalColumnIndexList = new List<int>();
//set some points as total column ?
for (int i = 0; i < chart.NSeries[0].Points.Count; i++)
{//to get 
    if (i % 4 == 0)
    {
        totalColumnIndexList.Add(i + 1);
    }
    ChartPoint point = chart.NSeries[0].Points[i];
}

chart.NSeries[0].LayoutProperties.Subtotals = totalColumnIndexList.ToArray();

wb.Save(filePath + "out_net.xlsx");
1 Like
6

@John.He
The expected output should have only total rows marked as “Set as total”,
here is the expected output
expected_out_net.zip (76.8 KB)

And marking the rows as total in the sample sheet won’t help when I change the series according to the year count. I need to do it in the code.

Thanks

7

@yunusbayrak
The index of data points starts with 0. Due to setting an incorrect index array, the expected result was not obtained. Please refer to the following example code. Please refer to the attachment. result.zip (112.0 KB)

Workbook wb = new Workbook(filePath + "SampleSheet.xlsx");
//var recordStartRowInx = 3;
//var yearColumnInx = 2;
Worksheet worksheet = wb.Worksheets[0];
Chart chart = worksheet.Charts?.FirstOrDefault(x => x.Name == "Graphiq5");

//chart.NSeries[0].Values = $"{CellsHelper.CellIndexToName(recordStartRowInx, yearColumnInx)}:{CellsHelper.CellIndexToName(recordStartRowInx, yearColumnInx)}";
//chart.NSeries[0].XValues = $"{CellsHelper.CellIndexToName(recordStartRowInx, yearColumnInx - 1)}:{CellsHelper.CellIndexToName(recordStartRowInx, yearColumnInx - 1)}";
            
var totalColumnIndexList = new List<int>();
//set some points as total column ?
for (int i = 0; i < chart.NSeries[0].Points.Count; i++)
{//to get 
    if (i % 4 == 0)
    {
        //we have modified this line
        totalColumnIndexList.Add(i);
    }
    ChartPoint point = chart.NSeries[0].Points[i];
}
            
chart.NSeries[0].LayoutProperties.Subtotals = totalColumnIndexList.ToArray();

wb.Save(filePath + "out_net.xlsx");

Hope helps a bit.

1 Like
8

Thank you @John.He @amjad.sahi
It’s perfectly worked when I try this in separate empty project.
But it didn’t work on main project, I guess I need to check my logic.

Many thanks.

9

@yunusbayrak,

We’re glad your issue has been resolved. Feel free to reach out to us anytime if you have additional questions or feedback.

1 Like