How to use EPPLUS to generate Excel in C#

Hi developers, the article is a continuation of the development in Excel series.

In this blog, we will go step by step to create Excel with option 2 which is EPPlus, in fact, most recommended because of its easy to use and numerous functionalities.

EPPlus

About

EPPlus is a third-party .NET library with the source package from ExcelPackage that reads and writes data to/from Excel files using Open Office XML format (xlxs). With EPPlus you can have multiple sheets with complete customizations.

Create a new Console Application

1. Open Visual Studio.

2. Create a New Console Application (.NET Framework).

3. Type a name for your project in the Name field.

Click OK.

The new project appears in Solution Explorer.

Add References

In Solution Explorer, right-click on your references folder and then click Manage NuGet Package and search for EPPlus by EPPlus Software AB.

If you want to use are reliable version then search for version 4.5.3.3.

Else want to go with the latest version then directly click install and add some configurations.

Click Install.

If you have installed the latest version add the configuration else skip the configuration part. You need to add some configuration in the app.config file of your project which says you are using the NonCommercial version of the library.

<appSettings>

    <!--The license context used-->

    <add key="EPPlus:ExcelPackage.LicenseContext" value="NonCommercial" />

</appSettings>

Practical Implementations

1. Open the Program.cs/Class1.cs file.

2. Add the below namespace before starts developing an Excel file.

using OfficeOpenXml;

using OfficeOpenXml.Style;

3. Your complete code will look like this:

using OfficeOpenXml;

using OfficeOpenXml.Style;

using System;

using System.IO;

namespace DemoConsoleApplication

{

    public class CreateExcelFileUsingEPPLUS

    {

        //Check the EPPLUS App.config we are using the Non-commercial license product

        public static void Main(string[] args)

        {

            //Creating Static Calls data

            var Calls = new[]

            {

                new

                {

                    CallId="101",

                    Company="2ML Real Estate",

                    Contact="Max Louis",

                    Phone="(4655767)98-876"

                },

                new

                {

                    CallId="102",

                    Company="100 Club of Houston",

                    Contact="Rick Robert",

                    Phone="(565767)798-876"

                }

            };

            //Install EPPLUS from nuget package manager console.

            //Creating an instance of ExcelPackage class

            ExcelPackage excel = new ExcelPackage();

            //Name of the sheet

            var worksheet = excel.Workbook.Worksheets.Add("Sheet1");

            //Setting the properties of the worksheet

            worksheet.TabColor = System.Drawing.Color.Black;

            worksheet.DefaultRowHeight = 12;

            //Setting the properties of the first row

            worksheet.Row(1).Height = 20;

            worksheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            worksheet.Row(1).Style.Font.Bold = true;

            //Setting the header of the Excel sheet

            worksheet.Cells[1, 1].Value = "S.No";

            worksheet.Cells[1, 2].Value = "Call Id";

            worksheet.Cells[1, 3].Value = "Company";

            worksheet.Cells[1, 4].Value = "Contact";

            worksheet.Cells[1, 5].Value = "Phone";

            //Inserting the Call data into excel

            //sheet by using the for each loop

            //As we have header value in the first row 

            //so, we will start with the second row

            int recordIndex = 2;

            foreach (var Call in Calls)

            {

                worksheet.Cells[recordIndex, 1].Value = recordIndex - 1;

                worksheet.Cells[recordIndex, 2].Value = Call.CallId;

                worksheet.Cells[recordIndex, 3].Value = Call.Company;

                worksheet.Cells[recordIndex, 4].Value = Call.Contact;

                worksheet.Cells[recordIndex, 5].Value = Call.Phone;

                recordIndex++;

            }

           

            //Column width AutoFit() method here. 

            worksheet.Column(1).AutoFit();

            worksheet.Column(2).AutoFit();

            worksheet.Column(3).AutoFit();

            worksheet.Column(4).AutoFit();

            worksheet.Column(5).AutoFit();

            //File name with .xlsx extension 

            string strPath = @"E:\DemoEPPLUS.xlsx";

            //Checking if file exists then create else delete then re-create

            if (File.Exists(strPath))

                File.Delete(strPath);

            //Creating Excel file

            FileStream fileStream = File.Create(strPath);

            fileStream.Close();

            //Write calls content to excel file 

            File.WriteAllBytes(strPath, excel.GetAsByteArray());

            //Disposing Excel object

            excel.Dispose();

            Console.WriteLine("Excel File created successfully");

            Console.ReadKey();

        }

    }

}

Output

S.No	Call Id	Company	Contact	Phone
1	101	2ML Real Estate	Max Louis	(4655767)98-876
2	102	100 Club of Houston	Rick Robert	(565767)798-876

  

Related Questions:

#newtoprgmtry the first program

Ques- What does the workbook mean?

Ans- The workbook is like a book with one or multiple sheets.

Ques- What are the different aspect which EPPlus cover?

Ans- EPPlus supports Cell styling, Formulae, Auto height, validation, Charts, and, many more...

Hope you find the article helpful and interesting. 

Comment us your way/any other way to create Excel.

Previous – How to create Excel file in C#

Next – EPPlus Life Hacks in C#

Click imagination hunt to read the latest blogs.

Posted By – Manish Kumar Gautam