Write Excel File Using EP Plus In .Net Core

Introduction:

EPPlus is a very useful open-source third-party DLL for writing data in Excel. EPPlus supports cell ranges, cell style, charts, photos, shapes, comments, tables, pivot tables, data validation, conditional formatting, formula calculation, and other spreadsheet features.

EPPLus provides API for working with Office Excel documents. A.NET framework called EPPlus makes it easier to read and write Excel files in the Office OpenXML format. This library is available from NuGet as a package to be installed.

To read & write an excel file using EP Plus we need to follow the below steps:

1)  Create a .NET Core application
2)  Configure EPPLus Nuget package
3) Read Excel File
4) Perform Excel Conversion to JSON or custom types
5) Write data to an Excel file

Getting Started:

Let’s create a .NET Core project, you can choose any project template. EPPlus API works perfectly fine for any .NET Core Project template.

Step1: Create a new Application

Create a .NET Core console application in visual studio. Now install EPPlus package either from NuGet package manager or applying command in the package manager console.

Step2: Install EPPlus Packege

install EPPlus as you like either by Manage Nuget Packages for Solution or Package Manager Console.

Step3: Update Program.cs

Copy the following code and paste it to program.cs file.

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.IO;

namespace EPplus
{
    class Program
    {
        static void Main(string[] args)
        {
            {
                var Articles = new[]
                {
                new {
                    Id = "101", Name = "C#"
                },
                new {
                    Id = "102", Name = "Python"
                },
                new {
                    Id = "103", Name = "Java Script"
                },
                new {
                    Id = "104", Name = "Angular"
                },
                new {
                    Id = "105", Name = "React"
                },
                new {
                    Id = "106", Name = "Java"
                },
                new {
                    Id = "107", Name = "C++"
                },
                 new {
                    Id = "108", Name = "Laravel"
                },
                  new {
                    Id = "109", Name = "Ruby"
                }
            };

                ExcelPackage excel = new ExcelPackage();

                var workSheet = excel.Workbook.Worksheets.Add("EPPlus");

                workSheet.TabColor = System.Drawing.Color.Black;
                workSheet.DefaultRowHeight = 12;

                workSheet.Row(1).Height = 20;
                workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                workSheet.Row(1).Style.Font.Bold = true;

                workSheet.Cells[1, 1].Value = "S.No";
                workSheet.Cells[1, 2].Value = "Id";
                workSheet.Cells[1, 3].Value = "Name";
               
                int recordIndex = 2;

                foreach (var article in Articles)
                {
                    workSheet.Cells[recordIndex, 1].Value = (recordIndex - 1).ToString();
                    workSheet.Cells[recordIndex, 2].Value = article.Id;
                    workSheet.Cells[recordIndex, 3].Value = article.Name;
                    recordIndex++;
                }

                workSheet.Column(1).AutoFit();
                workSheet.Column(2).AutoFit();
                workSheet.Column(3).AutoFit();

                string path = "F:\\abc.xlsx";

                if (File.Exists(path))
                    File.Delete(path);

                FileStream objFileStrm = File.Create(path);
                objFileStrm.Close();

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

                excel.Dispose();
                Console.ReadKey();

            }
        }
    }
}

Now run the application. It will give excel file to the specific folder which you mentioned in the path variable.

OUTPUT: