Free Support Forum - aspose.com

How to sort the chart data of a MSO chart?

Hi,

How to sort the particular column(Series) of the chart?

If my input data is coming in blow order and I want to sort the data of the “Series 3”.

Series 1 Series 2 Series 3
Category 1 4.3 2.4 1.2
Category 2 2.5 4.4 5.1
Category 3 1.9 4.9 3.2

After sort the data should coming in below order.
Series 1 Series 2 Series 3
Category 1 4.3 2.4 1.2
Category 3 1.9 4.9 3.2
Category 2 2.5 4.4 5.1

Thanks,
Laxmikanta

Hi Laxmikanta,


Thanks for inquiring Aspose.Slides.

I have observed your requirement and regret to share that sorting the values in chart data is unavailable in Aspose.Slides. You can either devise your own approach for accessing the series values and sorting them or try using Aspose.Cells for sorting values. Aspose.Slides allows to export chart to workbook in memory stream. You can load the stream in Workbook using Aspose.Cells and saves back to memory stream. Then you can write the memory stream to chart data. The following sample code will help you better in this.

public static void SortChartValues()
{
PresentationEx pres = new PresentationEx();
SlideEx slide = pres.Slides[0];

ChartEx chart = slide.Shapes.AddChart(ChartTypeEx.ClusteredColumn, 20, 20, 400, 400);


int ind = 1;
int Ser1StartColIndex = chart.ChartData.Series[ind].Values[0].Column;
int Ser1StartRowIndex = chart.ChartData.Series[ind].Values[0].Row;

int Ser1EndColIndex = chart.ChartData.Series[ind].Values[chart.ChartData.Series[0].Values.Count - 1].Column;
int Ser1EndRowIndex = chart.ChartData.Series[ind].Values[chart.ChartData.Series[0].Values.Count - 1].Row;

Workbook wb = null;
MemoryStream mem = new MemoryStream();
mem = chart.ChartData.ReadWorkbookStream();
mem.Position = 0;
wb = new Workbook(mem);

//Get the workbook datasorter object.
DataSorter sorter = wb.DataSorter;

//Set the first order for datasorter object.
sorter.Order1 = Aspose.Cells.SortOrder.Ascending;

//Define the first key.
sorter.Key1 = 1;

//Set the first order for datasorter object.
sorter.Order1 = Aspose.Cells.SortOrder.Ascending;

//Define the first key.
sorter.Key1 = 2;


//Create a cells area (range).
CellArea ca = new CellArea();

//Specify the start row index.
ca.StartRow = Ser1StartRowIndex;

//Specify the start column index.
ca.StartColumn = Ser1StartColIndex;

//Specify the last row index.
ca.EndRow = Ser1EndRowIndex;

//Specify the last column index.
ca.EndColumn = Ser1EndColIndex;

//Sort data in the specified data range (A1:B14)
// sorter.Sort(wb.Worksheets[0].Cells, ca);
sorter.Sort(wb.Worksheets[0].Cells, Ser1StartRowIndex, Ser1StartColIndex, Ser1EndRowIndex, Ser1EndColIndex);


MemoryStream mem2 = new MemoryStream();
Aspose.Cells.SaveOptions sav = wb.SaveOptions;

//Save the excel file.
wb.Save(mem2, Aspose.Cells.SaveFormat.Xlsx);
mem2.Position = 0;
chart.ChartData.WriteWorkbookStream(mem2);

pres.Save(“D:\Aspose Data\GenChart.pptx”, Aspose.Slides.Export.SaveFormat.Pptx);
}


I also like to add further that for more information related to sorting using Aspose.Cells you can contact Aspose.Cells team for further guidance.

Many Thanks,