Generate Excel Chart from DataTable and add the chart to word document in C#

Introduction

We need to add dynamic chart based on data from database to word document from code. Finally, found a third-party library Spire.Office for my needs, which is clean and standalone. This article shows how to import data to Excel, generate an Excel chart and save chart as image, finally add the chart image into word document in C#.

Step 1: Import Data to Excel.There is an XML file to save the data from and this is how it looks:

Now, lets Export database to excel by reading the data into the DataSet and returns a DataTable that contains the data.

private DataTable LoadData() 
        {   
          DataSet dataSet = new System.Data.DataSet();
          dataSet.ReadXml("data.xml");
          return dataSet.Tables[0]; 
        }  

Step 2: Generate Chart. Lets use this data to generate an Excel Pie Chart.

private void CreatePieChart(Worksheet sheet)

{
      sheet.Name = "Chart data";
      sheet.GridLinesVisible = false;

      Chart chart = sheet.Charts.Add(ExcelChartType.Pie);
      DataTable datatable = LoadData();

      //dataTable from DataBase
     sheet.InsertDataTable(datatable, true, 1, 1);
     chart.PlotArea.Fill.Visible = false;

      //Set region of chart data
      chart.DataRange = sheet.Range["B2:B5"];
      chart.SeriesDataFromRange = false;

     //Set position of chart
     chart.LeftColumn = 1;
     chart.TopRow = 6;
     chart.RightColumn = 9;
     chart.BottomRow = 25;

     //Chart title
     chart.ChartTitle = "Sales";
     chart.ChartTitleArea.IsBold = true;
     chart.ChartTitleArea.Size = 12;

     Spire.Xls.Charts.ChartSerie cs = chart.Series[0];
     cs.CategoryLabels = sheet.Range["A2:A5"];
     cs.Values = sheet.Range["B2:B5"];
     cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
  }

Step 3: Save Excel chart as Image. In this article, method workbook.SaveChartAsImage(); is used to save chart as image and returns the image.

public Image ChartToImage()
        {
            Workbook workbook = new Workbook();
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];
            CreatePieChart(sheet);
            Image image= workbook.SaveChartAsImage(sheet, 0);
            return image;
      } 

Step 4: Insert the chart image to word document.

public void AddCharttoWord()
 {
     Document doc = new Document();
     Section section = doc.AddSection();
     Paragraph paragraph = section.AddParagraph();
     Image image = ChartToImage();
     paragraph.AppendPicture(image);
     doc.SaveToFile("Result.docx",Spire.Doc.FileFormat.Docx);
   }

Effective screenshot of add dynamic chart based on data from database to word document: