Post
Ask Question

Create Excel and Convert to PDF Using Free API

Introduction

For my current project I was looking for a free library to create Microsoft Excel files on a server and converting these Excel files to a PDF in C#. As a first try for creating the Excel files I used the free library EPPlus with good results. Since EPPlus doesn't support coversion of Excel files to PDF, hence I'm trying to use another free library, Spire.Xls for converting to PDF. The facts proved that it is the right choice, it is very simple and fast to complete the conversion and the results are quite perfect. The best thing I found using EPPlus.dll and Spire.Xls.dll is that there is no need to install Microsoft Excel or Office. Here I want to share the solution that I implemented with you, hoping it might help someone.

Application Overview

I will introduce my entire solution in two parts.

The first part describes how to create Excel with cell ranges, images, cell styling (Border, Color, Fill, Font, Number, Alignments) by EPPlus.

Detailed procedure for the first part

Step 1: Create an empty Excel and add a sheet

  1. ExcelPackage pck = new ExcelPackage();  
  2. var ws = pck.Workbook.Worksheets.Add("Data");  
Step 2: Add cell ranges and set cell styling
  1. AddData(ws);  
  2. public static void AddData(ExcelWorksheet worksheet)  
  3. {  
  4.    worksheet.Cells["A1"].Value = "AdventureWorks Inc.";  
  5.    using (ExcelRange r = worksheet.Cells["A1:G1"])  
  6.    {  
  7.       //Merge from A1 to G1 cells into a cell  
  8.       r.Merge = true;  
  9.       //Set the style of this cell   
  10.       r.Style.Font.SetFromFont(new Font("Britannic Bold", 22, FontStyle.Italic));  
  11.       r.Style.Font.Color.SetColor(Color.White);  
  12.       r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;  
  13.       r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;  
  14.       r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));  
  15.       r.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);  
  16.       worksheet.Cells["A2"].Value = "Year-End Sales Report";  
  17.       using (ExcelRange cell = worksheet.Cells["A2:G2"])  
  18.       { 
  19.              cell.Merge = true;
  20.              cell.Style.Font.SetFromFont(new Font("Britannic Bold", 18, FontStyle.Italic));  
  21.              cell.Style.Font.Color.SetColor(Color.Black);  
  22.              cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;  
  23.              cell.Style.Fill.PatternType = ExcelFillStyle.Solid;  
  24.              cell.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));  
  25.              cell.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
  26.       }  

  28.        //Set the value and style for cells["A4:G4"]  
  29.        worksheet.Cells["A4"].Value = "Name";  
  30.        worksheet.Cells["B4"].Value = "Job";  
  31.        worksheet.Cells["C4"].Value = "Region";  
  32.        worksheet.Cells["D4"].Value = "Monthly";  
  33.        worksheet.Cells["E4"].Value = "Quota";  
  34.        worksheet.Cells["F4"].Value = "Sales";  
  35.        worksheet.Cells["G4"].Value = "Quota";  
  36.        worksheet.Cells["A4:G4"].Style.Fill.PatternType = ExcelFillStyle.Solid;  
  37.        worksheet.Cells["A4:G4"].Style.Fill.BackgroundColor.SetColor(Color.DarkGray);  
  38.        worksheet.Cells["A4:G4"].Style.Font.Bold = true;  
  39.        worksheet.Cells["A4:G4"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); 
  40.    }  
  41. }  
Step 3: Add pictures and set Excel picture styling
  1. AddPictures(ws);  
  2. public static void AddPictures(ExcelWorksheet ws)  
  3. {    
  4.       Image image = Image.FromFile("..\\..\\Butterfly01.jpg");  
  5.       OfficeOpenXml.Drawing.ExcelPicture pic = ws.Drawings.AddPicture("Pic1",image);  
  6.       //Set the postion of drawing picture  
  7.       pic.SetPosition(150, 100);  
  8.       pic.Border.LineStyle = eLineStyle.Solid;  
  9.       pic.Border.Fill.Color = Color.DarkCyan;  
  10.       pic.Fill.Style = eFillStyle.SolidFill;  
  11.       pic.Fill.Color = Color.White;  
  12.       pic.Fill.Transparancy = 50;  
  13.       image = Image.FromFile("..\\..\\Butterfly02.jpg");  
  14.       pic = ws.Drawings.AddPicture("Pic2", image);  
  15.       pic.SetPosition(300, 110);  
  16.       //Set the size of drawing picture  
  17.       pic.SetSize(150);  
  18. }  
Step 4: Save to Excel file
  1. FileInfo info = new FileInfo(@"Drawing.xlsx");  
  2.   
  3. pck.SaveAs(info);  
Screenshot of the created Excel file:

excel file with image

The second part shows using Spire.Xls to convert an Excel file to PDF.

Simple code for the second part
  1. Workbook workbook = new Workbook();  
  2. //Load excel file  
  3. workbook.LoadFromFile(info.Name);  
  4. //Save excel file to pdf file.  
  5. workbook.SaveToFile("result.pdf", Spire.Xls.FileFormat.PDF); 
Screenshot of the created PDF file:

excel to pdf convert file

The complete code of my solution is as follows:
  1. using OfficeOpenXml;  
  2. using OfficeOpenXml.Style;  
  3. using OfficeOpenXml.Drawing;  
  4. using OfficeOpenXml.Drawing.Chart;  
  5. using Spire.Xls;  
  6.   
  7. namespace ExcelToPDFwithEpplusAndSpireXls  
  8. {  
  9.     class Program  
  10.     {  
  11.         static void Main(string[] args)  
  12.         {  
  13.             FileInfo info = new FileInfo(@"Drawing.xlsx");  
  14.             ExcelPackage pck = new ExcelPackage();  
  15.             var ws = pck.Workbook.Worksheets.Add("Data");  
  16.             AddData(ws);  
  17.             AddPictures(ws);  
  18.             pck.SaveAs(info);  
  19.           
  20.             Workbook workbook = new Workbook();  
  21.             workbook.LoadFromFile(info.Name);  
  22.             workbook.SaveToFile("result.pdf", Spire.Xls.FileFormat.PDF);  
  23.             System.Diagnostics.Process.Start("result.pdf");  
  24.         }  
  25.         //Add data to worksheet.  
  26.         public static void AddData(ExcelWorksheet worksheet)  
  27.         {  
  28.             worksheet.Cells["A1"].Value = "AdventureWorks Inc.";  
  29.             using (ExcelRange r = worksheet.Cells["A1:G1"])  
  30.             {  
  31.                 //Merge from A1 to G1 cells into a cell  
  32.                 r.Merge = true;  
  33.                 //Set some styles of this cell   
  34.                 r.Style.Font.SetFromFont(new Font("Britannic Bold", 22, FontStyle.Italic));  
  35.                 r.Style.Font.Color.SetColor(Color.White);  
  36.                 r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;  
  37.                 r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;  
  38.                 r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));  
  39.                 r.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);  
  40.   
  41.                 worksheet.Cells["A2"].Value = "Year-End Sales Report";  
  42.                 using (ExcelRange cell = worksheet.Cells["A2:G2"])  
  43.                 {  
  44.                     cell.Merge = true;  
  45.                     cell.Style.Font.SetFromFont(new Font("Britannic Bold", 18, FontStyle.Italic));  
  46.                     cell.Style.Font.Color.SetColor(Color.Black);  
  47.                     cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;  
  48.                     cell.Style.Fill.PatternType = ExcelFillStyle.Solid;  
  49.                     cell.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));  
  50.                     cell.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);  
  51.                 }  
  52.                 //Set the value and styles for cells["A4:G4"]   
  53.                 worksheet.Cells["A4"].Value = "Name";  
  54.                 worksheet.Cells["B4"].Value = "Job";  
  55.                 worksheet.Cells["C4"].Value = "Region";  
  56.                 worksheet.Cells["D4"].Value = "Monthly";  
  57.                 worksheet.Cells["E4"].Value = "Quota";  
  58.                 worksheet.Cells["F4"].Value = "Sales";  
  59.                 worksheet.Cells["G4"].Value = "Quota";  
  60.                 worksheet.Cells["A4:G4"].Style.Fill.PatternType = ExcelFillStyle.Solid;  
  61.                 worksheet.Cells["A4:G4"].Style.Fill.BackgroundColor.SetColor(Color.DarkGray);  
  62.                 worksheet.Cells["A4:G4"].Style.Font.Bold = true;  
  63.                 worksheet.Cells["A4:G4"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);  
  64.             }  
  65.         }  
  66.         //Add pictures to worksheet  
  67.         public static void AddPictures(ExcelWorksheet ws)  
  68.         {    
  69.             Image image = Image.FromFile("..\\..\\Butterfly01.jpg");  
  70.             OfficeOpenXml.Drawing.ExcelPicture pic = ws.Drawings.AddPicture("Pic1",image);  
  71.             //Set the postion of drawing picture  
  72.             pic.SetPosition(150, 100);  
  73.   
  74.             pic.Border.LineStyle = eLineStyle.Solid;  
  75.             pic.Border.Fill.Color = Color.DarkCyan;  
  76.             pic.Fill.Style = eFillStyle.SolidFill;  
  77.             pic.Fill.Color = Color.White;  
  78.             pic.Fill.Transparancy = 50;  
  79.             image = Image.FromFile("..\\..\\Butterfly02.jpg");  
  80.             pic = ws.Drawings.AddPicture("Pic2", image);  
  81.             pic.SetPosition(300, 110);  
  82.             //Set the size of drawing picture  
  83.             pic.SetSize(150);  
  84.         }  
  85.     }
  86. }

 


Recommended Free Ebook
C# Corner Ebook

FileInfo in C#

 Download Now!
Similar Articles