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

EPPlus ReadWrite Excel files in NET Core using C

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,

What is EPPLus

EPPlus is a .NET open-source library for easily managing Microsoft Excel files in .xlsx format. With EPPlus, developers can create, read, write, and modify Excel spreadsheets programmatically without the need for Microsoft Office to be installed on the system.

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.

EPPlus ReadWrite Excel file in NET Core using

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;

epplus read excel, epplus read excel to datatable, epplus free, epplus read row, epplus read excel from stream c#, epplus list to excel, epplus xls, epplus license, epplus documentation pdf,

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.

blank

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

ReadWrite Excel file in NET Core using EPPlus

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,

ReadWrite Excel file in NET Core using EPPlus

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:

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.



12 thoughts on “EPPlus – How to Read/Write Excel files in C# .NET Core

  1. how can I manage reading Boolean a column?
    JsonConvert.DeserializeObject(JsonConvert.SerializeObject(excelasTable)) says that cannot convert a string to bool.

  2. Thank you for this. There is actually a problem with your code:
    If a cell in the first row is empty, the values behind that cell end up in the wrong column.

    1. Thanks Nijhuis! I appreciate your finding on the code. I shall correct it soon.
      Have a great day !

    1. Hi Kaikai, Sorry for the delay in reply. The above Write to excel logic should work as-is for you. You can have one excel file with multiple sheets per student.

  3. Hi, you showed an example where the excel file is in the same folder with main class files. Where and how to indicate a path to our excel file in different localization. Regards

    1. Hello Hank – For simplification, I have put excel file in the same folder as my bin or .exe location but you can provide the location explicitly like below,
      [cc lang=”Csharp”]List userDetails = ReadFromExcel>(@”C:\Users\TestDataRead.xlsx”);[/cc]

      Same way you can specify the file location for write operation like

      [cc lang=”Csharp”] WriteToExcel(@”C:\Users\Exceloutput\TestDataWrite.xlsx”);[/cc]

      Please let me know if you have any other questions. Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *