How to Create Chart Using Excel Data in PowerPoint in C#, VB.NET

When creating charts from scratch in PowerPoint slide, an Excel sheet with some dummy data will automatically be generated. The dummy data can be overwritten with the data from data source as well as from an existing Excel file. This article demonstrates how we can create chart in PowerPoint using the data in Excel file.

This solution requires Spire.Presneation.dll and Spire.Xls.dll to be added as references in project. Please download Spire.Office and reference the corresponding DLLs from it.

Here is the Excel sheet containing our sample data.

Step 1: Create a Presentation document.

Presentation ppt = new Presentation();

Step 2: Append a column chart in the first slide.

RectangleF rect = new RectangleF(40, 100, 550, 320);
IChart chart = ppt.Slides[0].Shapes.AppendChart(ChartType.ColumnClustered, rect);

Step 3: Clear the default dummy data.

chart.ChartData.Clear(0, 0, 5, 5);

Step 4: Load an existing Excel file to Workbook instance and get the first worksheet.

Workbook wb = new Workbook();
wb.LoadFromFile("data.xlsx");
Worksheet sheet = wb.Worksheets[0];

Step 5: Import data from the worksheet to chart table.

for (int r = 0; r < sheet.AllocatedRange.RowCount; r++)
{
    for (int c = 0; c < sheet.AllocatedRange.ColumnCount; c++)
    {
        chart.ChartData[r, c].Value = sheet.Range[r + 1, c + 1].Value2;
    }
}

Step 6: Set the series label and categories labels.

chart.Series.SeriesLabel = chart.ChartData["B1", "B1"];
chart.Categories.CategoryLabels = chart.ChartData["A2","A13"];

Step 7: Set the series values.

chart.Series[0].Values = chart.ChartData["B2","B13"];

Step 8: Save the file.

ppt.SaveToFile("chart.pptx",Spire.Presentation.FileFormat.Pptx2013);

Output:

How to Create Chart Using Excel Data in PowerPoint in C#, VB.NET

Full Code:

[C#]

using System;
using Spire.Presentation;
using System.Drawing;
using Spire.Presentation.Charts;
using Spire.Xls;

namespace CreateChartFromExcelData
{
    class Program
    {
        static void Main(string[] args)
        {
            //initialize an instance of Presentation class
            Presentation ppt = new Presentation();
            RectangleF rect = new RectangleF(40, 100, 550, 320);
            IChart chart = ppt.Slides[0].Shapes.AppendChart(ChartType.ColumnClustered, rect);

            //clear the default dummy data
            chart.ChartData.Clear(0, 0, 5, 5);

            //load an existing Excel file to Workbook object
            Workbook wb = new Workbook();
            wb.LoadFromFile("data.xlsx");
            Worksheet sheet = wb.Worksheets[0];

            //import data from the sheet to chart table
            for (int r = 0; r < sheet.AllocatedRange.RowCount; r++)
            {
                for (int c = 0; c < sheet.AllocatedRange.ColumnCount; c++)
                {
                    chart.ChartData[r, c].Value = sheet.Range[r + 1, c + 1].Value2;
                }
            }

            //add chart title
            chart.ChartTitle.TextProperties.Text = "Monthly Sales Report";
            chart.ChartTitle.TextProperties.IsCentered = true;
            chart.ChartTitle.Height = 30;
            chart.HasTitle = true;

            //set the series label
            chart.Series.SeriesLabel = chart.ChartData["B1", "B1"];

            //set the category labels
            chart.Categories.CategoryLabels = chart.ChartData["A2","A13"];

            //set the series values
            chart.Series[0].Values = chart.ChartData["B2","B13"];

            //save the file
            ppt.SaveToFile("chart.pptx",Spire.Presentation.FileFormat.Pptx2013);
        }
    }
}

[VB.NET]

Imports Spire.Presentation
Imports System.Drawing
Imports Spire.Presentation.Charts
Imports Spire.Xls

Namespace CreateChartFromExcelData
    Class Program
        Private Shared Sub Main(args As String())
			'initialize an instance of Presentation class
			Dim ppt As New Presentation()

            Dim rect As New RectangleF(40, 100, 550, 320)

            Dim chart As IChart = ppt.Slides(0).Shapes.AppendChart(ChartType.ColumnClustered, rect)

			'clear the default dummy data
			chart.ChartData.Clear(0, 0, 5, 5)

			'load an existing Excel file to Workbook object
			Dim wb As New Workbook()

            wb.LoadFromFile("data.xlsx")
			Dim sheet As Worksheet = wb.Worksheets(0)

			'import data from the sheet to chart table
			For r As Integer = 0 To sheet.AllocatedRange.RowCount - 1
				For c As Integer = 0 To sheet.AllocatedRange.ColumnCount - 1

                    chart.ChartData(r, c).Value = sheet.Range(r + 1, c + 1).Value2
                Next

            Next

			'add chart title
			chart.ChartTitle.TextProperties.Text = "Monthly Sales Report"
			chart.ChartTitle.TextProperties.IsCentered = True
            chart.ChartTitle.Height = 30
			chart.HasTitle = True

			'set the series label

            chart.Series.SeriesLabel = chart.ChartData("B1", "B1")

			'set the category labels

            chart.Categories.CategoryLabels = chart.ChartData("A2", "A13")

			'set the series values

            chart.Series(0).Values = chart.ChartData("B2", "B13")

			'save the file
			ppt.SaveToFile("chart.pptx", Spire.Presentation.FileFormat.Pptx2013)
		End Sub

    End Class
End Namespace

How to Create Chart Using Excel Data in PowerPoint in C#, VB.NET

See Also