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...