Read/Write Excel file in .NET using DocumentFormat.OpenXml （.NET 5.x）

Getting Started 

Today, in this article we shall cover below,

Create a .NET Core application
Configure OpenXML Nuget package
Read Excel File
Write Excel file

step 1.Create a .NET Core application

step 2. Install from Nuget package manager,

Install-Package Open-XML-SDK 

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

step 3.

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

using System.Text;



using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Nop.Admin.Controllers
{

    /// <summary>
    /// come from:https://www.thecodebuzz.com/read-excel-file-in-dotnet-core-2-1/  
    /// </summary>
    public class ExcelController : Controller
    {
        // GET: ExcelController
        public ActionResult Index()
        {
            return View();
        }

        // GET: ExcelController/Details/5
        public ActionResult Details()
        {
            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)
            {

            }

            return View();
        }

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 really need to use any other third-party tools/libraries. This SDK works best!

Useful links

1.Write code for workbook styles by giving your own font size, color, font name, border properties, cell style formats etc  using openxml

https://www.c-sharpcorner.com/article/creating-excel-file-using-openxml/