Import Data to Excel, Insert a Chart and Convert Excel to PDF

Background

I have tried to find some free, third-party libraries to help me manipulate Excel files in C# in my work instead of the Microsoft.Office.Interop.Excel objects. This article shows how to import data to Excel, insert a chart and convert Excel to PDF via two free libraries that don't require that Microsoft Excel be installed in your machine.

Import Data to Excel

I created an XML file to save the data from and there is how it looks:

Now, read the XML schema and data into the DataSet and return a DataTable that contains the data.

static DataTable LoadData()
{
    DataSet ds = new DataSet();
    ds.ReadXml("data.xml");
    ds.ReadXmlSchema("data-schema.xml");
    return ds.Tables[0];
}

Then I'm going to create an Excel file via ClosedXML with some worksheet style settings and call the method LoadData to import the DataTable to Excel.

To use ClosedXML you must reference the DocumentFormat.OpenXml.dll.

Add this namespace:

using ClosedXML.Excel;

Using the code:

XLWorkbook workbook = new XLWorkbook();
IXLWorksheet worksheet = workbook.Worksheets.Add("Data");
DataTable dt = LoadData();
worksheet.Cell(2, 5).Value = "Reports";
worksheet.Range(2, 5, 2, 8).Merge();
//Set title
worksheet.Cell(3, 5).Value = "Name";
worksheet.Cell(3, 6).Value = "Aug";
worksheet.Cell(3, 7).Value = "Sep";
worksheet.Cell(3, 8).Value = "Oct";
//Load data
worksheet.Cell(4, 5).InsertData(dt.AsEnumerable());
//Style settings
worksheet.Columns().AdjustToContents();
worksheet.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.RangeUsed().Style.Border.OutsideBorder = XLBorderStyleValues.Medium;
worksheet.RangeUsed().Style.Border.InsideBorder = XLBorderStyleValues.Medium;
worksheet.SetShowGridLines(false);
worksheet.Range(4, 6, 9, 8).SetDataType(XLCellValues.Number);
workbook.SaveAs("Sample.xlsx");

Insert a Chart

Since ClosedXML doesn't support this feature, I find another third-party library: Free Spire.Xls, which is a community sponsor of ClosedXML.

The namespace to be used:

using Spire.Xls;
using Spire.Xls.Charts;

Using the code:

Workbook book = new Workbook();
book.LoadFromFile("Sample.xlsx");
Worksheet sheet = book.Worksheets["Data"];
//Add chart and set chart data range
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = sheet.Range["E3:H9"];
chart.SeriesDataFromRange = false;
//Chart border
chart.ChartArea.Border.Weight = ChartLineWeightType.Medium;
chart.ChartArea.Border.Color = Color.SandyBrown;
//Chart position
chart.LeftColumn = 2;
chart.TopRow = 11;
chart.RightColumn = 12;
chart.BottomRow = 21;
//Chart title
chart.ChartTitle = "Sample Title";
chart.ChartTitleArea.Font.FontName = "Calibri";
chart.ChartTitleArea.Font.Size = 13;
chart.ChartTitleArea.Font.IsBold = true;
//Chart axis
chart.PrimaryCategoryAxis.Title = "Name";
chart.PrimaryCategoryAxis.Font.Color = Color.Blue;
chart.PrimaryValueAxis.Title = "Amount";
chart.PrimaryValueAxis.HasMajorGridLines = false;
chart.PrimaryValueAxis.MaxValue = 10000;
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
//Chart legend
chart.Legend.Position = LegendPositionType.Right;
book.SaveToFile("Result.xlsx", ExcelVersion.Version2010);

Convert Excel to PDF

Free Spire.Xls can make the conversion of Excel to PDF while ClosedXML doesn't support that. Just the following one of line code is needed:

//---After insert a Chart---
book.SaveToFile("Result.pdf", FileFormat.PDF);