Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Friday, 27 March 2020

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:


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.



Click imagination hunt to read the latest blogs.

Posted By – Manish Kumar Gautam

No comments:

Post a comment

Featured post

Think that makes you rich and richer

 Napolean said: “You can think and grow rich, but if you can be brought up like most people with work and you won't starve, this wil...