s

HTML Attributes

read and write excel file in csharp using epplus edit button Edit

author
Murugan Andezuthu Dharmaratnam | calendar 17 August 2022 | 545

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;
        }