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:

XML File View

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

  1. static DataTable LoadData()  
  2. {  
  3.     DataSet ds = new DataSet();  
  4.     ds.ReadXml("data.xml");  
  5.     ds.ReadXmlSchema("data-schema.xml");  
  6.     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:

  1. using ClosedXML.Excel; 

Using the code:

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

Excel File Format

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. using Spire.Xls;  
  2. using Spire.Xls.Charts; 

Using the code:

  1. Workbook book = new Workbook();  
  2. book.LoadFromFile("Sample.xlsx");  
  3. Worksheet sheet = book.Worksheets["Data"];  
  4. //Add chart and set chart data range  
  5. Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);  
  6. chart.DataRange = sheet.Range["E3:H9"];  
  7. chart.SeriesDataFromRange = false;  
  8. //Chart border  
  9. chart.ChartArea.Border.Weight = ChartLineWeightType.Medium;  
  10. chart.ChartArea.Border.Color = Color.SandyBrown;  
  11. //Chart position  
  12. chart.LeftColumn = 2;  
  13. chart.TopRow = 11;  
  14. chart.RightColumn = 12;  
  15. chart.BottomRow = 21;  
  16. //Chart title  
  17. chart.ChartTitle = "Sample Title";  
  18. chart.ChartTitleArea.Font.FontName = "Calibri";  
  19. chart.ChartTitleArea.Font.Size = 13;  
  20. chart.ChartTitleArea.Font.IsBold = true;  
  21. //Chart axis  
  22. chart.PrimaryCategoryAxis.Title = "Name";  
  23. chart.PrimaryCategoryAxis.Font.Color = Color.Blue;  
  24. chart.PrimaryValueAxis.Title = "Amount";  
  25. chart.PrimaryValueAxis.HasMajorGridLines = false;  
  26. chart.PrimaryValueAxis.MaxValue = 10000;  
  27. chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;  
  28. //Chart legend  
  29. chart.Legend.Position = LegendPositionType.Right;  
  30. book.SaveToFile("Result.xlsx", ExcelVersion.Version2010); 

 Chart Representation

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:

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


PDF File Format