EPPlus – Read/Write Excel files in C# .NET Core

Today in this article we will see the simple approach of How to Read/Write Excel files in .NET Core using EPPlus.

In our last post, we saw the usage of OpenXML SDK (open source SDK from Microsoft) to work with Office Word, Excel, and PowerPoint using the C# language.

Today, in this article we shall cover the below using EPPLus,

Please see below for your reference,

• Read/Write excel file .NET Core using OpemXML SDK

EPPLus provides API for working with Office Excel documents.

EPPlus is efficient and performs well, making it an excellent choice for applications that require Excel integration, reporting, data analysis, or data export functionalities.

This library provides a rich set of APIs to work with cells, rows, columns, formulas, formatting, charts, and more, making it suitable for a wide range of Excel-related tasks.

This library can be installed from NuGet as a package.

You don’t need Microsoft Office

Please note that Reading and Creating Excel in C# is possible without installing Microsoft Office.

Today we will see a few possible and easy-to-implement approaches.

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.

Here to keep it simple I am using a Console .NET Core application.

The NuGet Package name is EPPlus.

PM> Install-Package EPPlus 

[Note: Please use the latest available version]

Let’s look at a simple example, I have a simple Excel file with below columns and rows details. We shall first read this file programmatically.

Please make sure to add below using statements as needed,

using OfficeOpenXml;

Read the content of the Excel file using EPPlus

Please use the below ‘Ready to Use’ Method in your application.

This method returns the result as a type so that you can read the excel result in the type of your choice.

private static T ReadFromExcel<T>(string path, bool hasHeader = true)
        {
            using (var excelPack = new ExcelPackage())
            {
                //Load excel stream
                using (var stream = File.OpenRead(path))
                {
                    excelPack.Load(stream);
                }

                //Lets Deal with first worksheet.(You may iterate here if dealing with multiple sheets)
                var ws = excelPack.Workbook.Worksheets[0];

                //Get all details as DataTable -because Datatable make life easy :)
                DataTable excelasTable = new DataTable();
                foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                {
                    //Get colummn details
                    if (!string.IsNullOrEmpty(firstRowCell.Text))
                    {
                        string firstColumn = string.Format("Column {0}", firstRowCell.Start.Column);
                        excelasTable.Columns.Add(hasHeader ? firstRowCell.Text : firstColumn);
                    }
                }
                var startRow = hasHeader ? 2 : 1;
                //Get row details
                for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                {
                    var wsRow = ws.Cells[rowNum, 1, rowNum, excelasTable.Columns.Count];
                    DataRow row = excelasTable.Rows.Add();
                    foreach (var cell in wsRow)
                    {
                        row[cell.Start.Column - 1] = cell.Text;
                    }
                }
               //Get everything as generics and let end user decides on casting to required type
                var generatedType = JsonConvert.DeserializeObject<T>(JsonConvert.SerializeObject(excelasTable));
                return (T)Convert.ChangeType(generatedType, typeof(T));
            }
        }

JSON to Generic Type Conversion Dynamically

Please refer to this article – logic will generate type dynamically which can be cast to the appropriate type as needed.

Read Excel as JSON output

Excel as JSON will be generated using the below one-liner code.

string strJSON = JsonConvert.SerializeObject(excelasTable)

After executing the above API, The results will be printed on the console as below.

Here I have used the simple accessor class UserDetails.

This class will help in serializing and deserializing the Excel data.

public class UserDetails
    {
        public string ID { get; set; }
        public string Name { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
    }

Client-side Code

Below is an example of client-side code,

List<UserDetails> userDetails = ReadFromExcel<List<UserDetails>>(@"C:\Users\TestDataRead.xlsx");

Export/Write the data to an Excel file using EPPlus API

Let’s try exporting data to a new Excel file.

Here is the sample data/object which we want to save as an Excel file.

List<UserDetails> persons = new List<UserDetails>()
            {
                new UserDetails() {ID="9999", Name="ABCD", City ="City1", Country="USA"},
                new UserDetails() {ID="8888", Name="PQRS", City ="City2", Country="INDIA"},
                new UserDetails() {ID="7777", Name="XYZZ", City ="City3", Country="CHINA"},
                new UserDetails() {ID="6666", Name="LMNO", City ="City4", Country="UK"},
           };

Here we need to use ExcelPackage class to create a new Excel file with the required details.

Writing data to file and creating new Excel is just a 2-3 liner code.

Please make a note of below one line doing the magic of loading DataTable into the Excel sheet

I am creating a new Excel file in the same project folder to keep everything simple (the Excel file will get created in the ‘bin’ folder of the project)

private static void WriteToExcel(string path)
        {
            //Let use below test data for writing it to excel
            List<UserDetails> persons = new List<UserDetails>()
            {
                new UserDetails() {ID="9999", Name="ABCD", City ="City1", Country="USA"},
                new UserDetails() {ID="8888", Name="PQRS", City ="City2", Country="INDIA"},
                new UserDetails() {ID="7777", Name="XYZZ", City ="City3", Country="CHINA"},
                new UserDetails() {ID="6666", Name="LMNO", City ="City4", Country="UK"},
           };

            // let's convert our object data to Datatable for a simplified logic.
            // Datatable is the easiest way to deal with complex datatypes for easy reading and formatting. 
            DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons), (typeof(DataTable)));
            FileInfo filePath = new FileInfo(path);
            using (var excelPack = new ExcelPackage(filePath))
            {
                var ws = excelPack.Workbook.Worksheets.Add("WriteTest");
                ws.Cells.LoadFromDataTable(table, true, OfficeOpenXml.Table.TableStyles.Light8);
                excelPack.Save();
            }
        }

After executing the above API, a new Excel file will be created with the above custom object transformation into respective Excel columns and rows details as below,

That’s all, we just learned how to import and export data to/from Excel in the .NET Core framework.

Above ready to use API can be used in .NET Core console, or test project or ASP.Net Core application or logic can be modified as per your requirements. Please visit the GitHub link for the complete code.

I have validated the above approaches in .NET Core .NET 3.1 and found it to be working fine in all versions.

Other References:

• OpenXML SDK (open source SDK from Microsoft) to work with Office Word, Excel, and PowerPoint.
  • Read/Write Excel file .NET Core using OpemXML SDK

• NPOI library for reading and writing Excel files in .NET Core,
  • Read/Write Excel files in .NET Core using NPOI

Summary

Today we learned, how to import and export data to/from Excel in the .NET Core framework using the EPPlus library.

The legacy so-called applications like Excel are no more legacy and can be used on any platform. EPPlus makes it easy to deal with Excel files allowing us to perform reading or create spreadsheets easily.

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.

Share on Facebook

Post on X

Follow us

Save