TheCodeBuzz

Best Practices for Software Development

  • Home
  • Series
    • .NET Core
    • Design Pattern
    • Code Smell
    • Angular
    • React
    • Vue.js
    • IoT
  • About

C# NPOI – How to Read and Write Excel files in .NET

Home >.NET Core>C# NPOI – How to Read and Write Excel files in .NET

C# NPOI – How to Read and Write Excel files in .NET

Excel file in C#.NET Core using NPOI

Today in this article we shall see how to use NPOI to perform Read and Write Excel files in C#.NET Core.

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

We also looked at another easy-to-use library called EPPlus too.

Today, in this article we shall cover below using NPOI,

  • You don't need Microsoft Office
    • What is NPOI
  • Getting Started
  • Read the content of the Excel file using C# NPOI
  • Export/Write the data to an Excel file using NPOI
  • Summary

Please see the below references if interested to know more about them.

  • Read/Write Excel file .NET Core using OpemXML SDK
  • Read/Write Excel file in .NET Core using EPPlus

We shall be looking into one more simple approach to dealing with Office Excel using the NPOI .NET Core Library.

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 possible and easy-to-implement approach.

What is NPOI

NPOI is an open-source.NET framework library that offers a wide range of APIs for managing Excel documents, making it a great option for developers to automate operations involving spreadsheets.

Utilizing NPOI, you can efficiently manipulate data, add formatting, read existing Excel files, and add formulas.

It offers a dependable solution for Excel integration in C# applications and is actively maintained and free to use, saving time and resources for developers.

It supports several Excel file types, including.xls and.xlsx, providing versatility and compatibility with multiple Excel versions.

Getting Started

Let’s create a .NET Core project, you can choose any project template. Here we shall use the .NET Core 3.1 or .NET 6 Console project.

NPOI excel read write c#

NuGet’s package name is NPOI.

Let’s install this package.

PM> Install-Package NPOI -Version <version>

OR

Please install from Nuget Package Manager,

NPOI Nuget package

Note: Please use the latest available version

Let’s look at a simple Excel file with the below column and row details. Let’s try to read the file using our NPOI API,

excel npoi read

Read the content of the Excel file using C# NPOI

POC example as below. This ready to use API can be used in .NET Core console, or Test project or ASP.NET Core application or logic can be modified or improved as per your requirements.

static string ReadExcel()
        {
            DataTable dtTable = new DataTable();
            List<string> rowList = new List<string>();
            ISheet sheet;
            using (var stream = new FileStream("TestData.xlsx", FileMode.Open))
            {
                stream.Position = 0;
                XSSFWorkbook xssWorkbook = new XSSFWorkbook(stream);
                sheet = xssWorkbook.GetSheetAt(0);
                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;
                for (int j = 0; j < cellCount; j++)
                {
                    ICell cell = headerRow.GetCell(j);
                    if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue;
                    {
                        dtTable.Columns.Add(cell.ToString());
                    } 
                }
                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue;
                    if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            if (!string.IsNullOrEmpty(row.GetCell(j).ToString()) && !string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))
                            {
                                rowList.Add(row.GetCell(j).ToString());
                            }
                        }
                    }
                    if(rowList.Count>0)
                    dtTable.Rows.Add(rowList.ToArray());
                    rowList.Clear(); 
                }
            }
            return JsonConvert.SerializeObject(dtTable);
        }

After executing the above API, one can easily read all the Excel details.

  • In the above example, I have used the file “testdata.xlsx” as input. I have kept this file in the output directory bin\Debug\netcoreapp3.1.

  • Also if needed you can keep the file locally like ex. 
    string strDoc = @”C:\Users\Public\Documents\testdata .xlsx” .

I am showing the results as JSON out as shown below,

One can map the above output details to respective class objects using the mapping logic of their choice.


Export/Write the data to an Excel file using NPOI

We shall now look at creating or writing the data into an Excel file.

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

blank

I am creating a new Excel file in the same project folder to keep everything simple. (Excel file will get created in the ‘bin’ folder of the project). The complete sample API is as below.

static void WriteExcel()
        {
            List<UserDetails> persons = new List<UserDetails>()
            {
                new UserDetails() {ID="1001", Name="ABCD", City ="City1", Country="USA"},
                new UserDetails() {ID="1002", Name="PQRS", City ="City2", Country="INDIA"},
                new UserDetails() {ID="1003", Name="XYZZ", City ="City3", Country="CHINA"},
                new UserDetails() {ID="1004", Name="LMNO", City ="City4", Country="UK"},
           };

            // Lets converts our object data to Datatable for a simplified logic.
            // Datatable is most easy way to deal with complex datatypes for easy reading and formatting.

            DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons), (typeof(DataTable)));
            var memoryStream = new MemoryStream();
             
            using (var fs = new FileStream("Result.xlsx", FileMode.Create, FileAccess.Write))
            {
                IWorkbook workbook = new XSSFWorkbook();
                ISheet excelSheet = workbook.CreateSheet("Sheet1");

                List<String> columns = new List<string>();
                IRow row = excelSheet.CreateRow(0);
                int columnIndex = 0;
               
                foreach (System.Data.DataColumn column in table.Columns)
                {
                    columns.Add(column.ColumnName);
                    row.CreateCell(columnIndex).SetCellValue(column.ColumnName);
                    columnIndex++;
                }

                int rowIndex = 1;
                foreach (DataRow dsrow in table.Rows)
                {
                    row = excelSheet.CreateRow(rowIndex);
                    int cellIndex = 0;
                    foreach (String col in columns)
                    {
                        row.CreateCell(cellIndex).SetCellValue(dsrow[col].ToString());
                        cellIndex++;
                    }

                    rowIndex++;
                }
                workbook.Write(fs);
            }

        }

Once you execute ‘workbook.Write(fs)‘ will save the file to the ‘bin’ folder location. You can modify the location of the generated file as needed.

After executing the above API, a new Excel file will be created with the above custom objects transformed into respective Excel Columns and row details as below,

C# read excel NPOI

That’s all, we just learned how to import and export data to/from Excel in a simple way in a .NET Core framework-based application using the NPOI library.

Please visit the GitHub link for the complete code.

Other References :

  • Read/Write Excel file .NET Core using OpemXML SDK
  • Read/Write Excel file in .NET Core using EPPlus

Are you dealing with any complex scenarios? Please let me know and sound off your comments below!

Summary

The NPOI library provides us with easy control while dealing with the Office Excel file. Today we learned, how to import and export data to/from Excel in the .NET Core framework using the NPOI library.

The legacy so-called applications like Excel or Word are no more legacy and can be used on any platform using simple libraries like NPOI etc.



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.



facebookShare on Facebook
TwitterPost on X
FollowFollow us
PinterestSave

Post navigation

Read CSV files in C# .NET Core with examples
Python HTTP GET and POST with examples
get-free-ebook-micro-services-guideline
blank

©TheCodeBuzz 2024. All rights reserved. Privacy Policy.

To serve the best User Experience on the website, we use essential cookies. By clicking “Continue”, Please give consent to our privacy policy.
SettingsContinue to Site
Manage consent

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
CookieDurationDescription
cookielawinfo-checkbox-analytics11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional11 monthsThe cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy11 monthsThe cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytics
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Others
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.
SAVE & ACCEPT
RSS
Follow by Email
Facebook
fb-share-icon
X (Twitter)
Post on X
LinkedIn
Share