Create Excel chart from DataTable and then insert the chart to word document
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:
