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

Jerrylee02017
Posted by in C# category on for Beginner level | Points: 250 | Views : 465 red flag

Create Excel chart from DataTable and then insert the chart to word document
Recommendation
Read Global Variables in SQL Server - Part2 before this article.

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#.

Describe the topic with code snippets Explain the topic of the article with code snippets and description

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:


 

 

 

 

 




Recommendation
Read How to implement Remoting in ASP.NET after this article.
Page copy protected against web site content infringement by Copyscape

About the Author

Jerrylee02017
Full Name: Jerry Lee
Member Level: Starter
Member Status: Member
Member Since: 4/21/2017 3:36:19 AM
Country: China



Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)