Upload File Excel using EPPlus

ExcelData.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Astra.PartOnline.Domain.Models
{
    public class ExcelData
    {
        public int No { get; set; }
        public string Material_No { get; set; }
        public int Quantity { get; set; }
    }
}

ExcelPackageExtensions.cs

using OfficeOpenXml;
using OfficeOpenXml.Table;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Astra.PartOnline.Domain.Models.MaintainOrder;
using Astra.PartOnline.Common;

namespace Astra.PartOnline.Business.Business.ExportExcel
{
    public static class ExcelPackageExtensions
    {
        public static IEnumerable<T> ConvertSheetToObjects<T>(this ExcelWorksheet worksheet) where T : new()
        {
            //DateTime Conversion
            var convertDateTime = new Func<double, DateTime>(excelDate =>
            {
                if (excelDate < 1)
                    throw new ArgumentException("Excel dates cannot be smaller than 0.");

                var dateOfReference = new DateTime(1900, 1, 1);

                if (excelDate > 60d)
                    excelDate = excelDate - 2;
                else
                    excelDate = excelDate - 1;
                return dateOfReference.AddDays(excelDate);
            });

            //Get the properties of T
            var tprops = (new T())
                .GetType()
                .GetProperties()
                .ToList();

            //Cells only contains references to cells with actual data
            var groups = worksheet.Cells
                .GroupBy(cell => cell.Start.Row)
                .ToList();

            //Assume the second row represents column data types (big assumption!)
            var types = groups
                .Skip(1)
                .First()
                .Select(rcell => rcell.Value.GetType())
                .ToList();

            //Assume first row has the column names
            var colnames = groups
                .First()
                .Select((hcell, idx) => new { Name = hcell.Value.ToString(), index = idx })
                .Where(o => tprops.Select(p => p.Name).Contains(o.Name))
                .ToList();

            //Everything after the header is data
            var rowvalues = groups
                .Skip(1) //Exclude header
                .Select(cg => cg.Select(c => c.Value).ToList());

            //var data = rowvalues.ToList();

            //Create the collection container
            var collection = rowvalues
                .Select(row =>
                {
                    var tnew = new T();
                    if (row.Count == colnames.Count) {
                        colnames.ForEach(colname =>
                        {

                            //This is the real wrinkle to using reflection - Excel stores all numbers as double including int
                            var val = row[colname.index];
                            var type = types[colname.index];
                            var prop = tprops.First(p => p.Name == colname.Name);

                            //If it is numeric it is a double since that is how excel stores all numbers
                            if (type == typeof(double))
                            {
                                //Unbox it
                                var unboxedVal = (double)val;

                                //FAR FROM A COMPLETE LIST!!!
                                if (prop.PropertyType == typeof(Int32))
                                    prop.SetValue(tnew, (int)unboxedVal);
                                else if (prop.PropertyType == typeof(double))
                                    prop.SetValue(tnew, unboxedVal);
                                else if (prop.PropertyType == typeof(DateTime))
                                    prop.SetValue(tnew, convertDateTime(unboxedVal));
                                else if (prop.PropertyType == typeof(int?))
                                    prop.SetValue(tnew, (int)unboxedVal, null);
                                else
                                    throw new NotImplementedException(String.Format("Type '{0}' not implemented yet!", prop.PropertyType.Name));
                            }
                            else
                            {
                                //Its a string
                                prop.SetValue(tnew, val);
                            }
                        });
                    }
                    
                    return tnew;
                });


            //Send it back
            return collection;
        }
        
        static List<UploadData> data { get; set; }
        static List<string> message { get; set; }
        public static IEnumerable<UploadData> Convert<T>(this ExcelWorksheet worksheet,out List<string> errorMessage) where T : UploadData
        {
            message = new List<string>() ;
            data = new List<UploadData>();
            Int32 no = 0;
            for (int i = 2; i <= worksheet.Dimension.End.Row; i++)
            {
                no++;
                if (worksheet.Cells[i, 2].Value==null) {
                    message.Add(string.Format("No.{0} ({1})", no,Message.WarningPartNoNotFound));
                    continue;
                }
                
                if (worksheet.Cells[i, 3].Value == null) {
                    message.Add(string.Format("No.{0} ({1})", no, Message.WarningOrderTypeNotFound));
                    continue;
                }

                if(worksheet.Cells[i, 4].Value==null)
                {
                    message.Add(string.Format("No.{0} ({1})", no, Message.WarningFromDate));
                    continue;
                }

                if (worksheet.Cells[i, 5].Value==null)
                {
                    message.Add(string.Format("No.{0} ({1})", no, Message.WarningToDate));
                    continue;
                }
                
                DateTime FromDate = AppConst.StringToDate(worksheet.Cells[i, 4].Value.ToString());
                DateTime ToDate = AppConst.StringToDate(worksheet.Cells[i, 5].Value.ToString());
                DateTime today = DateTime.Now;

                TimeSpan todayVsFromDate = FromDate.Subtract(today);
                if (todayVsFromDate.TotalDays < -1)
                {
                    message.Add(string.Format("No.{0} ({1})", no, Message.WarningFromDateNow));
                    continue;
                }

                TimeSpan toDateVsFromDate = FromDate.Subtract(ToDate);
                if (toDateVsFromDate.TotalDays > 0)
                {
                    Console.WriteLine(" To Date must be greather than From Date");
                    message.Add(string.Format("No.{0} ({1})", no, Message.WarningToDateFromDate));
                    continue;
                }
                
                string OrderType = worksheet.Cells[i, 3].Value.ToString();
                string PartNo = worksheet.Cells[i, 2].Value.ToString();
                string CustomerNo = (worksheet.Cells[i, 6].Value == null) ? "" : worksheet.Cells[i, 6].Value.ToString();
                string CustomerGroupCode = (worksheet.Cells[i, 7].Value == null) ? "" : worksheet.Cells[i, 7].Value.ToString();
                string BranchCode = (worksheet.Cells[i, 8].Value == null) ? "" : worksheet.Cells[i, 8].Value.ToString();

                data.Add(new UploadData
                {
                    No = no,
                    PartNo =PartNo,
                    OrderType= OrderType,
                    FromDate= FromDate,
                    ToDate= ToDate,
                    CustomerNo= CustomerNo,
                    CustomerGroupCode= CustomerGroupCode,
                    BranchCode= BranchCode
                });
                
            }

            errorMessage = message;
            return data.AsEnumerable();
        }
    }
}

ImportExcelBussiness.cs

using System.Collections.Generic;
using Astra.PartOnline.Common;
using Astra.PartOnline.Domain.Models;
using Astra.PartOnline.Business.Retriever.Order;
using Astra.PartOnline.Business.Validator.Order;

namespace Astra.PartOnline.Business.Business
{
    public class ImportExcelBussiness : IImportExcelBussiness
    {
        #region Properties
        private string CompanyCode { get; set; }
        private string Filename { get; set; }
        public bool IsErrorList { get; set; }
        public List<string> ErrorList { get; set; }
        public List<MaterialDetailData> MaterialDetailDatas { get; set; }
        #endregion

        #region ctor
        public ImportExcelBussiness(string fileName,string companyCode)
        {
            this.IsErrorList = false;
            this.ErrorList = new List<string>();
            this.MaterialDetailDatas = new List<MaterialDetailData>();
            this.CompanyCode = companyCode;
            this.Filename = fileName;
        }
        #endregion

        public IResult Execute()
        {
            IResult result = new ResultStatus();
            result.SetErrorStatus(Message.ErrorOrderDataNotFound);

            //Retrieve Data
            IUploadOrderRetriever upload = new UploadOrderRetriever(this.Filename);
            result = upload.Retrieve();
            if (!result.IsSuccess) return result;

            //Validator Upload data
            IUploadDataValidator uploadData = new UploadDataValidator(this.CompanyCode, upload.ExcelDatas);
            result = uploadData.Validate();
            if (!result.IsSuccess) return result;


            this.ErrorList = uploadData.ErrorList;
            this.IsErrorList = uploadData.IsError;
            this.MaterialDetailDatas = uploadData.MaterialDetailDatas;
            result.SetSuccessStatus(Message.SuccessUploadData);
            return result;
        }
    }
}

UploadController.cs

[Route("upload")]
[HttpPost]
public async Task<IHttpActionResult> UploadTemplate()
{
    UploadResponse response = new UploadResponse();

    var httpRequest = HttpContext.Current.Request;

    if (httpRequest.Files.Count > 0)
    {
        var httpPostedFile  = httpRequest.Files["uploadFile"];
        var companyCode = httpRequest.Form["CompanyCode"];

        if (!Path.GetExtension(httpPostedFile.FileName).Equals(".xlsx") && !Path.GetExtension(httpPostedFile.FileName).Equals(".xls"))
        {
            response.Acknowledge = AcknowledgeType.Failure;
            response.Message = "File not found, Please upload the correct file (.xlsx or .xls)";

            return Ok(response);   
        }

        if (httpPostedFile != null)
        {
            var fileSavePath = Path.Combine(HttpContext.Current.Server.MapPath("~/Template"), httpPostedFile.FileName);

            httpPostedFile.SaveAs(fileSavePath);

            //Get Data
            IImportExcelBussiness importExcel = new ImportExcelBussiness(fileSavePath, companyCode);
            IResult result = await Task.Run(() => importExcel.Execute());

            response.MaterialDataList = importExcel.MaterialDetailDatas;
            response.ErrorList = importExcel.ErrorList;
            response.IsErrorList = importExcel.IsErrorList;
            response.Message = result.Message;
            response.Acknowledge = (result.IsSuccess) ? AcknowledgeType.Success : AcknowledgeType.Failure;

            //Delete File
            if (System.IO.File.Exists(fileSavePath))
            {
                System.IO.File.Delete(fileSavePath);
            }
        }

    }
    else
    {
        response.Acknowledge = AcknowledgeType.Failure;
        response.Message = "File not found, Please upload the correct file (.xlsx or .xls)";

        return Ok(response);
    }

    return Ok(response);
}

UploadDataValidator.cs

using System.Collections.Generic;
using System.Linq;
using Astra.PartOnline.Common;
using Astra.PartOnline.Domain.Models;
using Astra.PartOnline.DataAccess.PetaPoco.Interfaces;
using Astra.PartOnline.DataAccess.PetaPoco.Implementations;
using Astra.PartOnline.DataAccess.UnitOfService;
using Astra.PartOnline.DataAccess.PetaPoco.Entity;

namespace Astra.PartOnline.Business.Validator.Order
{
    public class UploadDataValidator : IUploadDataValidator
    {
        #region Properties
        public List<string> ErrorList { get; set; }
        public bool IsError { get; set; }
        private List<string> ListMaterialNo { get; set; }
        private List<ExcelData> ExcelDatas { get; set; }
        private string CompanyCode { get; set; }
        private UnitOfService UnitOfService { get; set; }
        private List<Material> Materials { get; set; }
        public List<MaterialDetailData> MaterialDetailDatas { get; set; }
        #endregion

        #region ctor
        public UploadDataValidator(string companyCode,List<ExcelData> excelDatas)
        {
            this.IsError = false;
            this.CompanyCode = companyCode;
            this.ExcelDatas = excelDatas;
            this.UnitOfService = new UnitOfService();
            this.ErrorList = new List<string>();
            this.ListMaterialNo = new List<string>();
            this.Materials = new List<Material>();
            this.MaterialDetailDatas = new List<MaterialDetailData>();
        }
        #endregion

        #region Validate
        public IResult Validate()
        {
            IResult result = new ResultStatus();
            result.SetErrorStatus(Message.ErrorItemNotFound);

            //Check material has item 0
            List<KeyValuePair<string, int>> QuantityList = new List<KeyValuePair<string, int>>();
            ListMaterialNo = ExcelDatas.Select(item =>
            {
                string materialNo = string.Empty;
                if (item.Quantity <= 0) {
                    ErrorList.Add(string.Format("{0} (quantity 0)", item.Material_No));
                }
                else
                {
                    QuantityList.Add(new KeyValuePair<string, int>(item.Material_No, (int)item.Quantity));
                    materialNo = item.Material_No;
                }
                return materialNo;
            }).ToList();

            //Get Data
            using (IUnitOfWork ouw=new PetaPocoUnitOfWorkProvider().GetUnitOfWork()) {
                string company = (this.CompanyCode.Equals(AppConst.COMPANY_DSO_CODE)) ? AppConst.COMPANY_DSO : AppConst.COMPANY_ISO;
                string key = string.Format("MaterialNo in (@MaterialNo) AND IsActive=1 AND ModifiedBy=@Company");
                this.Materials = UnitOfService.PetaMaterial.Fetch(ouw, key, new { MaterialNo=this.ListMaterialNo.ToArray(), Company= company });
            }

            //check material has not found
            MaterialDetailDatas = this.Materials.Select(item => new MaterialDetailData
            {
                MaterialNo = item.MaterialNo,
                MaterialDescription = item.MaterialDescription,
                RetailPrice = item.RetailPrice,
                Quantity = QuantityList.Where(x => x.Key.Equals(item.MaterialNo)).SingleOrDefault().Value

            }).ToList();

            ListMaterialNo = ListMaterialNo
            .Where(item=>!MaterialDetailDatas.Select(x=>x.MaterialNo).ToList().Contains(item))
            .Select(item =>
            {
                ErrorList.Add(string.Format("{0} (not valid)",item));
                return item;
            }).ToList();

            if (ErrorList.Count > 0) IsError=true;

            result.SetSuccessStatus(Message.SuccessGetData);
            return result;
        }
        #endregion
    }
}

UploadOrderRetriever.cs

using System.Collections.Generic;
using System.IO;
using System.Linq;
using Astra.PartOnline.Business.Business.ExportExcel;
using Astra.PartOnline.Common;
using Astra.PartOnline.Domain.Models;
using OfficeOpenXml;

namespace Astra.PartOnline.Business.Retriever.Order
{
    public class UploadOrderRetriever : IUploadOrderRetriever
    {
        #region Properties
        private string FileName { get; set; }
        public List<ExcelData> ExcelDatas { get; set; }
        #endregion

        #region ctor
        public UploadOrderRetriever(string fileName)
        {
            this.FileName = fileName;
            this.ExcelDatas = new List<ExcelData>();
        }
        #endregion

        #region Retrive
        public IResult Retrieve()
        {
            IResult result = new ResultStatus();
            result.SetErrorStatus(Message.ErrorItemNotFound);

            using (ExcelPackage pakcage = new ExcelPackage(new FileInfo(this.FileName))) {
                ExcelWorkbook workbook = pakcage.Workbook;
                ExcelWorksheet worksheet = workbook.Worksheets.First();
                ExcelDatas = worksheet.ConvertSheetToObjects<ExcelData>().ToList();
            }
            if (ExcelDatas.Count <= 0) return result;

            result.SetSuccessStatus(Message.SuccessGetData);
            return result;
        }
        #endregion
    }
}