read and write excel file in csharp using epplus 
Edit
Murugan Andezuthu Dharmaratnam | 
17 August 2022 | 
857
17 August 2022 | 857
Below is the sample code and Extension method to read write to an excel file using EPPlus. I am using the free version EPPLus 4.0.5.0.
Sample Code
var worksheets = (@"\Files\" "filaname.ext".GetWorkSheetNames();
var excel = filename.Read("Manifest", true);
int Skip = 0;
foreach (var item in excel)
{
if (Skip == 0)
{
Skip = Skip 1;
continue;
}
var expObject = (IDictionary<string, object>)item;
Manifest manifest = new Manifest();
new BaseData(manifest, CRUDType.Insert, baseData);
manifest.ManifestQuantity = expObject["QTY".toExpandoString()].toFloat();
manifest.Quantity = expObject["QTY".toExpandoString()].toFloat();
}
public static string toExpandoString(this String value)
{
value = value.Replace(" ", "");
value = value.ToLower();
value = value.Trim();
return value;
}
-----------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OfficeOpenXml;
using System.Drawing;
using OfficeOpenXml.Style;
using System.IO;
using System.Reflection;
using System.Web;
using System.Dynamic;
using Leedhar.Common;
namespace Leedhar.Excel
{
public class Excel
{
public dynamic Read(String fileName, bool hasHeading = false, String workSheetName = null)
{
String[] ExcelColumn = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ" };
try
{
List<dynamic> retval = new List<dynamic>();
List<String> Heading = new List<string>();
byte[] file = File.ReadAllBytes(GetFilePath(fileName));
MemoryStream ms = new MemoryStream(file);
using (ExcelPackage ep = new ExcelPackage(ms))
{
if (ep.Workbook.Worksheets.Count <= 0)
{
return null;
}
else
{
workSheetName = String.IsNullOrEmpty(workSheetName) ? ep.Workbook.Worksheets.FirstOrDefault().Name : workSheetName;
var worksheet = ep.Workbook.Worksheets[workSheetName];
// Heading
foreach (var firstRowCell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])
{
if (hasHeading == true)
{
if (!String.IsNullOrEmpty(firstRowCell.Text))
{
Heading.Add(firstRowCell.Text);
}
}
else
{
if (!String.IsNullOrEmpty(firstRowCell.Address))
{
Heading.Add(firstRowCell.Address);
}
}
}
// RowStart = 2 if hasHeading = true;
int rowStart = hasHeading ? rowStart = 1 : rowStart = 2;
// Data
for (int i = rowStart; i <= worksheet.Dimension.End.Row; i )
{
dynamic dynamicRow = new ExpandoObject();
int column = 1;
for(int c=0; c < Heading.Count; c )
{
String cell = ExcelColumn[c] (i).ToString();
ExcelRangeBase columnCell = worksheet.Cells[i, 1, i, Heading.Count][cell];
((IDictionary<string, object>)dynamicRow).Add(Heading[c].ToLower().Replace(" ", ""), columnCell.Text);
}
//foreach (var columnCell in worksheet.Cells[i, 1, i, Heading.Count])
//{
// ((IDictionary<string, object>)dynamicRow).Add(Heading[column - 1].ToLower().Replace(" ",""), columnCell.Text);
// column = column 1;
//}
retval.Add(dynamicRow);
}
}
}
return retval;
}
catch (Exception ex)
{
throw ex;
}
}
public List<string> GetWorkSheetNamess(String fileName)
{
try
{
List<string> retval = new List<string>();
byte[] file = File.ReadAllBytes(GetFilePath(fileName));
MemoryStream ms = new MemoryStream(file);
using (ExcelPackage ep = new ExcelPackage(ms))
{
if (ep.Workbook.Worksheets.Count <= 0)
return null;
else
{
foreach (var worksheet in ep.Workbook.Worksheets)
{
retval.Add(worksheet.Name);
}
}
}
return retval;
}
catch (Exception ex)
{
throw ex;
}
}
public bool WriteToFile(String FileName, Stream stream)
{
using (var fileStream = File.Create(GetFilePath(FileName)))
{
var buffer = new byte[512000];
int readCount;
while ((readCount = stream.Read(buffer, 0, buffer.Length)) != 0)
fileStream.Write(buffer, 0, readCount);
}
return true;
}
public string GetFilePath(String fileName)
{
//is web app
if (HttpRuntime.AppDomainAppId != null)
{
string retval = HttpContext.Current.Server.MapPath(fileName);
return retval;
}
else
{
String appPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
return appPath "\\" fileName;
//is windows app
}
}
public bool WriteToCell(ref ExcelWorksheet ws, String Value, String Cell, bool bold = false, Color? color = null)
{
try
{
Color bgColor = color == null ? Color.White : color.Value;
ws.Cells[Cell].Value = Value;
ws.Cells[Cell].Style.Font.Bold = bold;
ws.Cells[Cell].AutoFitColumns();
ws.Cells[Cell].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells[Cell].Style.Fill.BackgroundColor.SetColor(bgColor);
}
catch (Exception ex)
{
throw;
}
return true;
}
public MemoryStream WriteToExcel(String FileName, String FilePath, List<dynamic> dynamic, Dictionary<String, String> HeaderData)
{
bool header = true;
String[] ExcelColumn = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
string sPath = System.Web.Hosting.HostingEnvironment.MapPath("~/" FilePath "/");
string vpath = @"\" FilePath @"\";
if (!System.IO.File.Exists(Path.Combine(sPath FileName)))
{
return null;
}
var worksheets = Path.Combine(vpath FileName).GetWorkSheetNames();
byte[] file2 = System.IO.File.ReadAllBytes(Path.Combine(sPath FileName));
MemoryStream ms = new MemoryStream(file2);
String workSheetName = "";
MemoryStream epStream;
using (ExcelPackage ep = new ExcelPackage(ms))
{
if (ep.Workbook.Worksheets.Count <= 0)
{
return null;
}
else
{
workSheetName = String.IsNullOrEmpty(workSheetName) ? ep.Workbook.Worksheets.FirstOrDefault().Name : workSheetName;
var worksheet = ep.Workbook.Worksheets[workSheetName];
if (header)
{
int iHeader = 0;
foreach (var headerElement in HeaderData)
{
String HeaderText = headerElement.Value;
(ExcelColumn[iHeader] (1).ToString()).WriteToCell(ref worksheet, HeaderText, false, System.Drawing.Color.LightBlue);
iHeader = iHeader 1;
}
}
for (int i = 0; i < dynamic.Count; i )
{
String Key = "";
String Value = "";
foreach (System.Collections.Generic.KeyValuePair<String, object> item in dynamic[i])
{
Key = item.Key;
Value = item.Value != null ? item.Value.ToString() : "";
int Column = HeaderData.DictionaryIndex(Key);
if (Column == -1)
continue;
if (header)
{
(ExcelColumn[Column] (i 2).ToString()).WriteToCell(ref worksheet, Value, false);
}
else
{
(ExcelColumn[Column] (i 1).ToString()).WriteToCell(ref worksheet, Value, false);
}
}
}
epStream = new MemoryStream(ep.GetAsByteArray());
}
}
return epStream;
}
}
}
------------------------------------------
Extensions
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Leedhar.Excel
{
public static class Extensions
{
public static List<String> GetWorkSheetNames(this string fileName)
{
try
{
return new Excel().GetWorkSheetNamess(fileName);
}
catch (Exception ex)
{
throw ex;
}
}
#region Read Excel Data
public static dynamic Read(this string fileName)
{
try
{
return new Excel().Read(fileName, false);
}
catch (Exception ex)
{
throw;
}
}
public static dynamic Read(this string fileName, bool hasHeading)
{
try
{
return new Excel().Read(fileName, hasHeading);
}
catch (Exception ex)
{
throw ex;
}
}
public static dynamic Read(this string fileName, string workSheetName)
{
try
{
if (String.IsNullOrEmpty(workSheetName))
throw new Exception("Worksheet Name is Empty");
return new Excel().Read(fileName, true, workSheetName);
}
catch (Exception ex)
{
throw ex;
}
}
public static dynamic Read(this string fileName, String workSheetName, bool hasHeading)
{
try
{
if (String.IsNullOrEmpty(workSheetName))
throw new Exception("Worksheet Name is Empty");
return new Excel().Read(fileName, hasHeading, workSheetName);
}
catch (Exception ex)
{
throw ex;
}
}
public static bool WriteToCell(this string Cell, ref ExcelWorksheet ws, string Value, bool bold = false, Color? color = null)
{
return new Excel().WriteToCell(ref ws, Value, Cell, bold, color);
}
public static MemoryStream WriteToExcel(this String FileName, String FilePath, List<dynamic> dynamic, Dictionary<String, String> HeaderData)
{
return new Excel().WriteToExcel(FileName, FilePath, dynamic, HeaderData);
}
#endregion
//public static bool Create(String fileName)
//{
// byte[] file = File.ReadAllBytes(GetFilePath(fileName));
// MemoryStream ms = new MemoryStream(file);
// using (ExcelPackage ep = new ExcelPackage(ms))
// {
// if (ep.Workbook.Worksheets.Count <= 0)
// Console.WriteLine("Your Excel file does not contain any work sheets");
// else
// {
// _Create(ep, "MEA");
// Stream epStream = new MemoryStream(ep.GetAsByteArray());
// WriteToFile(fileName, epStream);
// }
// }
// //ExcelPackage ep = new ExcelPackage();
// //_Create(ep, "LIST", RepairCentre, SHIP_MONTH, failure_only, PROD_GROUP, BUSORG_NAME, RMA_DTL_NO);
// return true;
//}
//private static bool _Create(ExcelPackage p, string sheetName)
//{
// p.Workbook.Worksheets.Add(sheetName);
// ExcelWorksheet ws = p.Workbook.Worksheets[1];
// ws.Name = sheetName; //Setting Sheet's name
// ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
// ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
// fillCell(ref ws, "TITLE 1", "A1", true, Color.LightGray);
// fillCell(ref ws, "TITLE 2", "B1", true, Color.LightGray);
// fillCell(ref ws, "TITLE 3", "C1", true, Color.LightGray);
// fillCell(ref ws, "TITLE 4", "D1", true, Color.LightGray);
// fillCell(ref ws, "TITLE 5", "E1", true, Color.LightGray);
// //ListService _ListService = new ListService();
// //ViewModels.Lists oLists = _ListService.GetAll();
// List<String> oLists = new List<String>();
// int i = 2;
// foreach (var bl in oLists)
// {
// //fillCell(ref ws, "Data", "A" i.ToString(), false);
// //fillCell(ref ws, "Data", "B" i.ToString(), false);
// i = i 1;
// }
// return true;
//}
}
}
public List<dynamic> ToExpand(List<ILeedhar> ObjectList)
{
List<dynamic> retval = new List<dynamic>();
foreach (var obj in ObjectList)
{
System.Reflection.PropertyInfo[] propertyInfo = ObjectList[0].GetType().GetProperties();
dynamic dynamicRow = new ExpandoObject();
for (int i = 0; i < propertyInfo.Length; i )
{
String Name = propertyInfo[i].Name;
String Value = obj.GetType().GetProperty(propertyInfo[i].Name).GetValue(obj, null) == null ? "" : obj.GetType().GetProperty(propertyInfo[i].Name).GetValue(obj, null).ToString();
((IDictionary<string, object>)dynamicRow).Add(Name, Value);
}
retval.Add(dynamicRow);
}
return retval;
}
public List<dynamic> ToExpand(List<ILeedhar> ObjectList)
{
List<dynamic> retval = new List<dynamic>();
foreach (var obj in ObjectList)
{
System.Reflection.PropertyInfo[] propertyInfo = ObjectList[0].GetType().GetProperties();
dynamic dynamicRow = new ExpandoObject();
for (int i = 0; i < propertyInfo.Length; i )
{
String Name = propertyInfo[i].Name;
String Value = obj.GetType().GetProperty(propertyInfo[i].Name).GetValue(obj, null) == null ? "" : obj.GetType().GetProperty(propertyInfo[i].Name).GetValue(obj, null).ToString();
((IDictionary<string, object>)dynamicRow).Add(Name, Value);
}
retval.Add(dynamicRow);
}
return retval;
}
public void AddProperty(ExpandoObject expando, string propertyName, object propertyValue)
{
// ExpandoObject supports IDictionary so we can extend it like this
var expandoDict = expando as IDictionary<string, object>;
if (expandoDict.ContainsKey(propertyName))
expandoDict[propertyName] = propertyValue;
else
expandoDict.Add(propertyName, propertyValue);
}
public int DictionaryIndex(Dictionary<String, String> dictionary, String Key)
{
int i = 0;
foreach (var dct in dictionary)
{
if (dct.Key == Key)
{
return i;
}
i = i 1;
}
return -1;
}