Reading Data from Excel and Appending Data without Saving a Physical Path Using EPPlus Package

Introduction

This article is about reading data from Excel and appending extra data without saving a physical path using the EPPlus package.

EP Plus is a open-source 3rd party DLL . We use the EP Plus package to write data to excel sheet. It supports multiple properties of spreadsheets, like cell ranges, cell styles, data validations, comments, etc. The library is available for use via NuGet.

The following Classes are available in EP Plus Package:

Name	Description
ExcelPackage	Reprasent Excel 2007/2010 XLSX file package
ExcelAddress	Range address with the address property
ExcelAddressBase	A range address
ExcelBackgroundImage	Am image that files the background of worksheet
ExcelAddressCell	A single cell address
ExcelCellBase	Base class containg cell address manipulating methods
ExcelColumn	Reprasents one or more columns with the worksheet
ExcelComment	An ExcelCell Comment
ExcelCommentCollection	Collection of Excelcomment objects
ExcelEncryption	How and if the workbook is encrypted
ExcelHeaderFooter	Reprasents header and footer on an Excel worksheet
ExcelHeaderFooterText	Print header and footer
ExcelHyperLink	HyperlinkClass
ExcelNamedRange	A named range
ExcelNamedRangeCollection	Collection of named ranges

For reading data from an excel sheet and adding extra data, we have to use the ExcelPackage class.

Create a WebAPI Project.

File --> new --> Project -->ASP.NET Web Application and give name EPPlusExcel

Select Empty application and web API

After Creating Web API right click on the Controllers folder add the new controller (ProxyController.cs)

Right-click on the project -->Manage NuGet Packages --> search for EPPlus and install latest version of package

Add the Code in Post method for reading data bytes, and then append the extra data into Excel.

MemoryStream

The MemoryStream class creates streams that have a memory as a backing store instead of a disk or a network connection. MemoryStream encapsulates data stored as an unsigned byte array that is initialized upon creation of a memory stream object, or an array can be created as empty.

A memorystream is constructed from this byte array containing the byteexcel's data

ExcelPackage

The first thing you do is to create an instance for the ExcelPackage Class. To do that, you first need to add a using directive to OfficeOpenXml namespace at the top of your file (Namespace block). This is the top namespace in EP Plus.

The following syntax is used for byte data to ExcelPackage through MemoryStream

Syntax

ExcelPackage package=new ExcelPackage(memoryStreamObject);

Now Excelpackage has a workbook with byte data in the form of worksheets, where we can add multiple worksheets data as a bulk.

Build the application and test the API through Postman or filler...