I have a Single Series Bar chart in Power point. In its chart data column B & C are hidden. Column D is bound to Chart Series. Now I want to edit the values of the chart. I want to know how can I get to know
Which column is bound to that series?
Is there any way to find max column and max row index in chart data?
How can I delete Hidden Row or Column from Chart Data Workbook?
Is there any way to apply all properties of 1 series to another newly added series (something like format painter)?
I have observed your requirements and like to share that every series data point and category is mapped to some cell in chart data workbook. You can access the cells from chart data workbook or directly using chart data point as well. The following sample code will help you in accessing the row, column and worksheet index for particular series data point and also updating that.
public static void UpdateChartData()
{
Presentation pres = new Presentation("C:\\Aspose Data\\TestChart.pptx");
IChart chart = (IChart)pres.Slides[0].Shapes[0];
IChartDataWorkbook wb = chart.ChartData.ChartDataWorkbook;
IChartSeries series = chart.ChartData.Series[0];
//The following will give the column and row number
int iCol=series.DataPoints[0].Value.AsCell.Column;
int iRow = series.DataPoints[0].Value.AsCell.Row;
IChartDataWorksheet worksheet = series.DataPoints[0].Value.AsCell.ChartDataWorksheet;
//First way:Updating value from DataPoint
series.DataPoints[0].Value.Data = 2;
//Second way: Updating data from worksheet
wb.GetCell(worksheet.Index, iRow, iCol).Value = 4;
//getting max row and column index for series
int maxDatapoints = series.DataPoints.Count;
int iMaxCol = series.DataPoints[maxDatapoints - 1].Value.AsCell.Column;
int iMaxrow = series.DataPoints[maxDatapoints - 1].Value.AsCell.Row;
pres.Save("C:\\Aspose Data\\SavedChart.pptx", Aspose.Slides.Export.SaveFormat.Pptx);
}
For your following query:
You need to use Aspose.Cells to manage the chart data workbook. Aspose.Slides only provide limited support to manage chart data workbook for manipulating chart data.
You have to copy the properties from one series to another as there is not default way available to achieve this.
How can we manage ChartDataWorkbook using Aspose.Cells?
Also how can I change Name of Series and Category?
The problem which we are facing is that we need to edit a chart which already has some series and categories. Now I need to edit this chart which may have different no. of series and categories. If I clear all previous series and categories and bind with new then all the formatting lost. How can we avoid that?
If I delete one series from chart, its column should also be deleted which is not deleted and causes problem.
I have observe your requirements. For your following requirement, please append the following code at the end of previously shared code to update the chart series and categories names.
//Change series name
//For Bar charts, series name is always first row
wb.GetCell(worksheet.Index, 0, iCol).Value = "New Series";
//Change category name
chart.ChartData.Categories[0].AsCell.Value = "New Cat";
//Second way
//For Bar charts, the category name is always first column
//The datapoint row points to respective category number cell
//The following code gets the cell for first chart category mapped to first datapoint of series
wb.GetCell(worksheet.Index, iRow, 0).Value = "New Category";
Aspose.Slides allows to add chart series and categories from scratch. I suggest you to please visit documentation article, Formatting chart entities for your convenience. You may also use ReadWorkbookStream() and WriteWorkbookStream() methods offered by ChartData to read and write the workbooks for chart data.
//Read the internal chart workbook stream
MemoryStream mem = chart.ChartData.ReadWorkbookStream();
mem.Position = 0;
//Now using Aspose.Cells to read workbook stream
Aspose.Cells.Workbook workbooktem = new Workbook(mem);
//Now make your changes as desired
MemoryStream outstream=new System.IO.MemoryStream();
workbooktem.Save(outstream, Aspose.Cells.SaveFormat.Xlsx);
outstream.Position = 0;
//now setting modified workbook for chart data
chart.ChartData.WriteWorkbookStream(outstream);
I also suggest you to please visit this documentation link for your convenience as well. I hope the shared information will be helpful.