TheCodeBuzz

Best Practices for Software Development

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

Read Excel Files in C# .NET Core

Home >.NET Core>Read Excel Files in C# .NET Core

Read and Write Excel Files in C# .NET Core

Read Excel files in C# .NET

Today in this article, we shall see how to Read Excel files in C# .NET-based application and we will also see examples for write operations.

You must be wondering if there is an easy way to work on Excel in the .NET Core framework.

Here I am going to talk about a very simple approach of using OpenXML SDK from Microsoft (which is free and open-source).

The OpenXML SDK provides API for working with Office Word, Excel, and PowerPoint documents.

This SDK can be installed from NuGet as a package.

Today, in this article we shall cover below,

  • You don't need Microsoft Office
  • Read Excel files in C# .NET using OpenXML API
  • Read Excel files in C# .NET as JSON
  • Read Excel with Empty cell values
  • Export/Create/Write Excel files in C# .NET using OpenXML
  • Summary

You don’t need Microsoft Office

Please note that Reading, and Creating Excel (.xls, .xlsx) in C# is possible without installing Microsoft Office.

Yes, you don’t need Microsoft Office to be installed on the machine.

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

If you are interested in a few more approaches like using EPPlus or NPOI library, kindly visit the below post for more details,

  • Read/Write Excel files in .NET Core using EPPlus
  • Read/Write Excel files in .NET Core using NPOI

Getting Started

Let’s create a .NET Core project, you can choose any project template, and this SDK works perfectly for all projects. Here to keep it simple I am using a Console .NET Core 3.1 C# application.

(The below logic works perfectly fine for older .NET Core or any new .NET 6 version)

c# excel read write

The NuGet package name is DocumentFormat.OpenXml. Let’s install this package,

PM> Install-Package Open-XML-SDK -Version 2.9.0 

Note: Please use the latest available version.

OR

Please install it from the NuGet package manager,

Read Excel files in C# .NET

Once you install this NuGet package, you get full library support on Reading, Writing Excel (.xlsx), Word (.doc), or Powerpoint(.ppt) files using C# API.

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

Read and Create/Write Excel file in .NET

Read Excel files in C# .NET using OpenXML API

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.

Sample methods are as below,

  static void ReadExcelFile()
        {
            try
            {
                //Lets open the existing excel file and read through its content . Open the excel using openxml sdk
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open("testdata.xlsx", false))
                {
                    //create the object for workbook part  
                    WorkbookPart workbookPart = doc.WorkbookPart;
                    Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild<Sheets>();
                    StringBuilder excelResult = new StringBuilder();

                    //using for each loop to get the sheet from the sheetcollection  
                    foreach (Sheet thesheet in thesheetcollection)
                    {
                        excelResult.AppendLine("Excel Sheet Name : " + thesheet.Name);
                        excelResult.AppendLine("----------------------------------------------- ");
                        //statement to get the worksheet object by using the sheet id  
                        Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;

                        SheetData thesheetdata = (SheetData)theWorksheet.GetFirstChild<SheetData>();
                        foreach (Row thecurrentrow in thesheetdata)
                        {
                            foreach (Cell thecurrentcell in thecurrentrow)
                            {
                                //statement to take the integer value  
                                string currentcellvalue = string.Empty;
                                if (thecurrentcell.DataType != null)
                                {
                                    if (thecurrentcell.DataType == CellValues.SharedString)
                                    {
                                        int id;
                                        if (Int32.TryParse(thecurrentcell.InnerText, out id))
                                        {
                                            SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);
                                            if (item.Text != null)
                                            {
                                                //code to take the string value  
                                                excelResult.Append(item.Text.Text + " ");
                                            }
                                            else if (item.InnerText != null)
                                            {
                                                currentcellvalue = item.InnerText;
                                            }
                                            else if (item.InnerXml != null)
                                            {
                                                currentcellvalue = item.InnerXml;
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    excelResult.Append(Convert.ToInt16(thecurrentcell.InnerText) + " ");
                                }
                            }
                            excelResult.AppendLine();
                        }
                        excelResult.Append("");
                        Console.WriteLine(excelResult.ToString());
                        Console.ReadLine();
                    }
                }
            }
            catch (Exception)
            {

            }
        }

After executing the above logic, 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
  • Also if needed you can keep the file locally for ex.
    string strDoc = @”C:\Users\Public\Documents\testdata.xlsx” then use the method as below,

using (SpreadsheetDocument doc = SpreadsheetDocument.Open(strDoc, false))

I am showing the results on a console,

Read and Create/Write Excel files in .NET Core

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

This way data can be transferred for further processing within the application if needed. For example for write operations, I have used a similar C# class UserDetails.

Read Excel files in C# .NET as JSON

If you are interested in converting Excel as JSON it’s easy too. Please see here to know more about the same.

  • Read Excel as JSON using OpenXML SDK

Read Excel with Empty cell values

If you are interested in exporting Excel in C# with empty cell values, please see here one approach to reading Excel data appropriately.

  • Read Excel with Empty cell values using OpenXML SDK


Export/Create/Write Excel files in C# .NET using OpenXML

Let’s try now to create or write the data in an Excel file.

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

Write data to Excel using OpenXML

Read Excel files in C# .NET

Here we need to use the OpenXML Create () C# method as shown below,

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 logic can be found below,

 static void WriteExcelFile()
        {
            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)));

            using (SpreadsheetDocument document = SpreadsheetDocument.Create("TestNewData.xlsx", SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                worksheetPart.Worksheet = new Worksheet(sheetData);

                Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
                Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };

                sheets.Append(sheet);

                Row headerRow = new Row();

                List<String> columns = new List<string>();
                foreach (System.Data.DataColumn column in table.Columns)
                {
                    columns.Add(column.ColumnName);

                    Cell cell = new Cell();
                    cell.DataType = CellValues.String;
                    cell.CellValue = new CellValue(column.ColumnName);
                    headerRow.AppendChild(cell);
                }

                sheetData.AppendChild(headerRow);

                foreach (DataRow dsrow in table.Rows)
                {
                    Row newRow = new Row();
                    foreach (String col in columns)
                    {
                        Cell cell = new Cell();
                        cell.DataType = CellValues.String;
                        cell.CellValue = new CellValue(dsrow[col].ToString());
                        newRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(newRow);
                }

                workbookPart.Workbook.Save();
            }
        }

Once you execute this API ‘workbookPart.Workbook.Save()’ will save the file to the ‘bin’ folder location. You can modify the location of the generated file if 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,

Write data to Excel using OpenXML

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.

Please visit the GitHub link for the complete code.

Other References :

EPPlus and EPOI for Reading and Writing of Excel file, kindly visit the below post for more details.

  • Read/Write Excel file in .NET Core using EPPlus
  • Read/Write Excel files in .NET Core using NPOI

If interested to know on how to download Excel (.xlsx) files, please see the below article,

  •  Return or Download File in ASP.NET Core WebAPI

Please use the below Tutorial to use the above code and quickly get started,

This was very much basic while dealing with Excel in .NET Core which we covered today in this article.

Hope this helps you get started.

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

Summary

The Open XML SDK provides us full control to deal with Office Word, Excel, and PowerPoint documents. As this SDK is free and open-source from Microsoft, we don’t need to use any other third-party tools/libraries. This SDK works best!



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

How to Explore Docker container’s file system
Export Dataset To Excel file C# .NET
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