I am trying to make the bubble chart from a tabular structure. But not able to get the desired output. Can anyone help me out?
Data:
Status Amount Paid Amount Paid Actuals
Closed 725.464118 725.464118
Released 658.3 658.3
In Progress 986.458 500.438
There is no option to set the chart data from tabular structure. You can either have chart data in excel sheet from where you set the range or you need to manually add the chart series data point values for respective chart categories.
using (Presentation pres = new Presentation())
{
ISlide slide = pres.Slides[0];
//Creating the default chart
IChart chart = slide.Shapes.AddChart(ChartType.Bubble, 0, 0, 400, 400);
//Getting the default chart data worksheet index
int defaultWorksheetIndex = 0;
//Accessing the chart data worksheet
IChartDataWorkbook fact = chart.ChartData.ChartDataWorkbook;
//Delete demo series
chart.ChartData.Series.Clear();
chart.ChartData.Categories.Clear();
fact.Clear(defaultWorksheetIndex);
//Add new series
chart.ChartData.Series.Add(fact.GetCell(defaultWorksheetIndex, 1, 0, "Series 1"), chart.Type);
chart.ChartData.Series.Add(fact.GetCell(defaultWorksheetIndex, 2, 0, "Series 2"), chart.Type);
chart.ChartData.Series.Add(fact.GetCell(defaultWorksheetIndex, 3, 0, "Series 3"), chart.Type);
chart.ChartData.Series.Add(fact.GetCell(defaultWorksheetIndex, 4, 0, "Series 4"), chart.Type);
//Take first chart series
IChartSeries series = chart.ChartData.Series[0];
series.DataPoints.AddDataPointForBubbleSeries(fact.GetCell(defaultWorksheetIndex, 1, 1, 10),
fact.GetCell(defaultWorksheetIndex, 1, 2, 30),
fact.GetCell(defaultWorksheetIndex, 1, 3, 40));
series.Marker.Size = 10;
series.Marker.Symbol = MarkerStyleType.Circle;
series = chart.ChartData.Series[1];
series.DataPoints.AddDataPointForBubbleSeries(fact.GetCell(defaultWorksheetIndex, 2, 1, 20),
fact.GetCell(defaultWorksheetIndex, 2, 2, 100),
fact.GetCell(defaultWorksheetIndex, 2, 3, 50));
series.Marker.Size = 10;
series.Marker.Symbol = MarkerStyleType.Circle;
//Take second chart series
series = chart.ChartData.Series[2];
series.PlotOnSecondAxis = true;
series.DataPoints.AddDataPointForBubbleSeries(fact.GetCell(defaultWorksheetIndex, 3, 1, 50),
fact.GetCell(defaultWorksheetIndex, 3, 2, 70),
fact.GetCell(defaultWorksheetIndex, 3, 3, 50));
series.Marker.Size = 10;
series.Marker.Symbol = MarkerStyleType.Circle;
series = chart.ChartData.Series[3];
series.DataPoints.AddDataPointForBubbleSeries(fact.GetCell(defaultWorksheetIndex, 4, 1, 30),
fact.GetCell(defaultWorksheetIndex, 4, 2, 60),
fact.GetCell(defaultWorksheetIndex, 4, 3, 80));
series.Marker.Size = 10;
series.Marker.Symbol = MarkerStyleType.Circle;
chart.Axes.SecondaryVerticalAxis.IsNumberFormatLinkedToSource = false;
chart.Axes.SecondaryVerticalAxis.NumberFormat = "###.##%";
chart.Axes.SecondaryHorizontalAxis.CrossType = CrossesType.Maximum;
chart.Axes.SecondaryHorizontalAxis.IsVisible = false;
chart.ChartTitle.Overlay = false;
pres.Save(outFileName, SaveFormat.Pptx);
}
For setting the chart data range, you may please visit following thread.
@mudassir.fayyaz - I have the data in the excel itself and I want to create a bubble chart for the same. Somehow, I am not able to upload the image and excel upload is not available. That’s why I had given the same data above. Could you please assist with that?
See the sample code on how to create bubble chart for your reference (I used a simple template file for the data source):
e.g
Sample code:
Workbook workbook = new Workbook(@"e:\test2\Bk_Bubble1.xlsx");
Worksheet datasheet = workbook.Worksheets["Sheet1"];
Cells cells = datasheet.Cells;
Worksheet chartsheet = workbook.Worksheets[workbook.Worksheets.Add()];
chartsheet.Type = SheetType.Chart;
chartsheet.Name = "Chart2";
int chartIndex = chartsheet.Charts.Add(ChartType.Bubble, 0, 0, 25, 15);
Chart chart = chartsheet.Charts[chartIndex];
chart.PlotArea.Area.ForegroundColor = Color.Transparent;
chart.PlotArea.Border.IsVisible = false;
chart.ChartArea.Border.IsVisible = false;
chart.NSeries.Add("=Sheet1!D2", true);
chart.NSeries[0].XValues = "Sheet1!$B2";
chart.NSeries[0].Name = datasheet.Cells[1, 0].Value.ToString();
chart.NSeries[0].BubbleSizes = "=Sheet1!C2";
//Set the image for the series' fill format
//Similarly you may set Series' point fill format.
FileStream fs = File.OpenRead(@"e:\test\school.jpg");
byte[] data = new byte[fs.Length];
fs.Read(data, 0, data.Length);
chart.NSeries[0].Area.FillFormat.ImageData = data;
chart.NSeries.Add("=Sheet1!D4", true);
chart.NSeries[1].XValues = "Sheet1!$B4";
chart.NSeries[1].Name = datasheet.Cells[3, 0].Value.ToString();
chart.NSeries[1].BubbleSizes = "=Sheet1!C4";
chart.NSeries.Add("=Sheet1!D3", true);
chart.NSeries[2].XValues = "Sheet1!$B3";
chart.NSeries[2].Name = datasheet.Cells[2, 0].Value.ToString();
chart.NSeries[2].BubbleSizes = "=Sheet1!C3";
chart.NSeries.Add("=Sheet1!D5", true);
chart.NSeries[3].XValues = "Sheet1!$B5";
chart.NSeries[3].Name = datasheet.Cells[4, 0].Value.ToString();
chart.NSeries[3].BubbleSizes = "=Sheet1!C5";
chart.NSeries.IsColorVaried = true;
//Set the legend position type
chart.Legend.Position = LegendPositionType.Top;
workbook.Save(@"e:\test2\out1_BubblesChart1.xlsx");
If you still could not make it work for your bubble chart in Excel file, please zip your Excel files (current output and expected output) and image and post us here, we will check it soon. Also paste your sample code (runnable), we will check it soon.
Is there a way I can see your input excel from where you plotted the bubble chart?
You may please zip the files and share the desired excel file with data along with source presentation (if any) and desired presentation with chart that you want to create with excel data.
I also suggest you to please visit this documentation link for meeting your exact needs as well.