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

using ClosedXML.Excel;
class Program
{
    static void Main(string[] args)
    {
        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");
    }
    static DataTable LoadData()
    {
       DataSet ds = new DataSet();
        ds.ReadXml("data.xml");
        ds.ReadXmlSchema("data-schema.xml");
        return ds.Tables[0];
    }
}

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.

1. usingSpire.Xls;
2. usingSpire.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-line code is needed.

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