HYTOS

using ID2.Manager.Data.Models;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.IO;

using System.IO.Compression;

using GemBox.Spreadsheet;

namespace ID2.Manager.Common.Helpers

{

    public class ID2Excel : IDisposable

    {

        readonly Informations informations = Informations.Instance;

        public void Dispose()

        {

            try

            {

            }

            catch (Exception)

            {

                throw;

            }

            finally

            {

                GC.Collect(2);

                GC.Collect(2);

            }

        }

        public ID2Excel() { }

        private ProjectInfo GetProject(string project)

        {

            //List<ProjectInfo> projects = informations.ProjectList.Where(x => x.GroupID.Equals(informations.ActiveProject.ProjectID)).ToList();

            List<ProjectInfo> projects = (from allPrj in informations.ProjectList

                                          join actPrj in informations.ActiveProjects on allPrj.GroupID equals actPrj.ProjectID

                                          select allPrj).ToList();

            ProjectInfo prjInfo = projects.FirstOrDefault(x => x.Code.Equals(project));

            if (prjInfo != null) return prjInfo;

            prjInfo = projects.FirstOrDefault(x => x.Name.Equals(project));

            if (prjInfo != null) return prjInfo;

            return prjInfo ?? new ProjectInfo();

        }

        private UserInfo GetUser(string user)

        {

            UserInfo userInfo = informations.UserList.Where(x => x.ID.Equals(user)).FirstOrDefault();

            if (userInfo != null) return userInfo;

            userInfo = informations.UserList.Where(x => x.Name.Equals(user)).FirstOrDefault();

            if (userInfo != null) return userInfo;

            return userInfo ?? new UserInfo();

        }

        private string GetColumnName(int column)

        {

            int dividend = column;

            string columnName = string.Empty;

            while (dividend > 0)

            {

                int modulo = (dividend - 1) % 26;

                columnName = Convert.ToChar(65 + modulo) + columnName;

                dividend = (dividend - modulo) / 26;

            }

            return columnName;

        }

        private System.Drawing.Image GetImage(string base64String)

        {

            System.Drawing.Image result = null;

            var str = CompressHelper.DecompressString(base64String);

            byte[] imageBytes = Convert.FromBase64String(str);

            using (MemoryStream ms = new MemoryStream(imageBytes))

            {

                result = System.Drawing.Image.FromStream(ms);

            }

            return result;

        }

        private byte[] ExcelToImageData(string base64String)

        {

            var str = CompressHelper.DecompressString(base64String);

            return Convert.FromBase64String(str);

        }

        public ImportResult ExcelDataImport(List<ExcelData> ExcelData)

        {

            ImportResult result = new ImportResult();

            StringBuilder sbErrMsg = new StringBuilder();

            try

            {

                int rowCount = ExcelData.Max(x => x.ROW_INDEX);

                int columnCount = ExcelData.Max(x => x.COUMMN_INDEX);

                int exRow = 9;

                #region Excel 유효성검사

                //Excel 포멧체크

                if (rowCount < 10 || columnCount != 49)

                {

                    result.Error = "Please, check the excel.";

                    return result;

                }

                #region 엑셀 필수값 체크(9: ID2 Project, 12 : DWG_ID)

                //ExcelData.Where(col => col.ROW_INDEX > exRow)

                //         .Where(col => col.COUMMN_INDEX > 8 && col.COUMMN_INDEX < 18 && col.ROW_INDEX > exRow && string.IsNullOrEmpty(col.VALUE))

                //         .ToList()

                //         .ForEach(p => sbErrMsg.Append(", " + p.TopLeftCell));

                ExcelData.Where(col => col.ROW_INDEX > exRow)

                         .Where(col => (col.COUMMN_INDEX == 9 || col.COUMMN_INDEX == 12) && string.IsNullOrEmpty(col.VALUE))

                         .ToList()

                         .ForEach(p => sbErrMsg.Append(", " + p.TopLeftCell));

                if (sbErrMsg.Length > 0)

                {

                    string errMsg = sbErrMsg.ToString().Substring(2);

                    if (errMsg.Length > 100)

                    {

                        errMsg = $"{errMsg.Substring(0, 100)}...";

                    }

                    result.Error = $"Please, check null value in excel.\n{errMsg}";

                    return result;

                }

                #endregion

                #region 엑셀 도면명 중복 값 체크

                ExcelData.Where(col => col.COUMMN_INDEX == 12 && col.ROW_INDEX > exRow)

                         .GroupBy(g => g.ROW_INDEX)

                         .Select(p => new

                         {

                             rowIndex = p.Key,

                             docNo = p.Select(x => x.VALUE.ToString()).FirstOrDefault()

                         })

                         .GroupBy(g => g.docNo)

                         .Where(p => p.Count() > 1)

                         .Select(p => p.Select(x => x.rowIndex.ToString())

                                       .Aggregate((x, y) => x.ToString() + "," + y.ToString())

                                       .ToString())

                         .ToList()

                         .ForEach(p => sbErrMsg.Append("\n" + p.ToString()));

                if (sbErrMsg.Length > 0)

                {

                    sbErrMsg.Insert(0, "\n중복 된 도면명 Excel row : ");

                    string errMsg = sbErrMsg.ToString();

                    if (errMsg.Length > 100)

                    {

                        errMsg = $"{errMsg.Substring(0, 100)}...";

                    }

                    result.Error = $"Please, check the duplicate value in excel.\n{errMsg}";

                    return result;

                }

                #endregion

                #endregion

                result.documents = new List<Documents>();

                result.Images = new List<System.Drawing.Image>();

                foreach (var row in ExcelData.Where(row => row.ROW_INDEX > exRow).GroupBy(x => x.ROW_INDEX))

                {

                    var document = new Documents();

                    //document.DocID = Guid.NewGuid().ToString();

                    int toreview = 0;

                    int frreview = 0;

                    int id2work = 0;

                    foreach (var cell in row.OrderBy(x => x.COUMMN_INDEX))

                    {

                        var value = cell.VALUE.DefalutValue();

                        switch (cell.COUMMN_INDEX)

                        {

                            case 9:

                                var prjInfo = this.GetProject(value);

                                //document.RefProjectCode = prjInfo.Code;

                                document.RefProjectCode = string.IsNullOrEmpty(value) ? value : value.Trim();

                                if (!string.IsNullOrEmpty(document.RefProjectCode))

                                {

                                    document.Team = prjInfo.Team;

                                }

                                break;

                            case 10:

                                document.System = value;

                                break;

                            case 11:

                                document.SubSystemCode = value;

                                break;

                            case 12:

                                document.DocumentNo = string.IsNullOrEmpty(value) ? value : value.Trim();

                                break;

                            case 13:

                                document.PersonInCharge = this.GetUser(value).ID;

                                break;

                            case 14:

                                document.Worker = this.GetUser(value).ID;

                                break;

                            case 15:

                                document.AVEVAPersonInCharge = this.GetUser(value).ID;

                                break;

                            case 16:

                                document.AVEVAWorker = this.GetUser(value).ID;

                                break;

                            case 17:

                                document.JobLevel = value;

                                break;

                            case 18:

                                document.RevisonNo = value;

                                break;

                            case 19:

                                document.ToIsDiscussion = value;

                                break;

                            case 20:

                                document.ToRemarks = value;

                                break;

                            case 21:

                                document.ToCreator = this.GetUser(value).ID;

                                break;

                            case 22:

                                if (value != null)

                                {

                                    if (document.AttFiles == null)

                                    {

                                        document.AttFiles = new List<AttFileInfo>();

                                    }

                                    document.AttFiles.Add(new AttFileInfo

                                    {

                                        FileID = Guid.NewGuid().ToString(),

                                        //RefID = document.DocID,

                                        Category = "toreview",

                                        FileType = "image/png",

                                        FileName = "ClipBoard",

                                        FilePath = "ClipBoard",

                                        FileExtension = ".png",

                                        CreatedDate = DateTime.Now,

                                        Creator = document.ToCreator,

                                        FileData = ExcelToImageData(value)

                                    });

                                    document.ToCapture = ++toreview;

                                }

                                break;

                            case 23:

                                document.FrReviewStatus = value;

                                break;

                            case 24:

                                document.FrRemarks = value;

                                break;

                            case 25:

                                document.FrCreator = this.GetUser(value).ID;

                                break;

                            case 26:

                                if (value != null)

                                {

                                    if (document.AttFiles == null)

                                    {

                                        document.AttFiles = new List<AttFileInfo>();

                                    }

                                    document.AttFiles.Add(new AttFileInfo

                                    {

                                        FileID = Guid.NewGuid().ToString(),

                                        //RefID = document.DocID,

                                        Category = "frreview",

                                        FileType = "image/png",

                                        FileName = "ClipBoard",

                                        FilePath = "ClipBoard",

                                        FileExtension = ".png",

                                        CreatedDate = DateTime.Now,

                                        Creator = document.FrCreator,

                                        FileData = ExcelToImageData(value)

                                    });

                                    document.FrCapture = ++frreview;

                                }

                                break;

                            case 27:

                                document.ID2StartDate = string.IsNullOrEmpty(value) ? (DateTime?)null : Convert.ToDateTime(value);

                                break;

                            case 28:

                                document.ID2EndDate = string.IsNullOrEmpty(value) ? (DateTime?)null : Convert.ToDateTime(value);

                                break;

                            case 29:

                                document.ID2Status = value;

                                break;

                            case 30:

                                document.ID2Issues = value;

                                break;

                            case 31:

                                if (value != null)

                                {

                                    if (document.AttFiles == null)

                                    {

                                        document.AttFiles = new List<AttFileInfo>();

                                    }

                                    document.AttFiles.Add(new AttFileInfo

                                    {

                                        FileID = Guid.NewGuid().ToString(),

                                        //RefID = document.DocID,

                                        Category = "id2work",

                                        FileType = "image/png",

                                        FileName = "ClipBoard",

                                        FilePath = "ClipBoard",

                                        FileExtension = ".png",

                                        CreatedDate = DateTime.Now,

                                        Creator = document.FrCreator,

                                        FileData = ExcelToImageData(value)

                                    });

                                    document.ID2Capture = ++id2work;

                                }

                                break;

                            case 32:

                                document.ReplyModifications = value;

                                break;

                            case 33:

                                document.ReplyRequester = this.GetUser(value).ID;

                                break;

                            case 34:

                                document.IsConvert = value;

                                break;

                            case 35:

                                document.AVEVAConvertDate = string.IsNullOrEmpty(value) ? (DateTime?)null : Convert.ToDateTime(value);

                                break;

                            case 36:

                                document.AVEVAWorkDate = string.IsNullOrEmpty(value) ? (DateTime?)null : Convert.ToDateTime(value);

                                break;

                            case 37:

                                document.AVEVAStatus = value;

                                break;

                            case 38:

                                document.AVEVAIssues = value;

                                break;

                            case 39:

                                document.AVEVAReviewDate = string.IsNullOrEmpty(value) ? (DateTime?)null : Convert.ToDateTime(value);

                                break;

                            case 40:

                                document.ProdReviewer = this.GetUser(value).ID;

                                break;

                            case 41:

                                document.ProdIsResult = value;

                                break;

                            case 42:

                                document.ProdRemarks = value;

                                break;

                            case 43:

                                document.ClientReviewer = this.GetUser(value).ID;

                                break;

                            case 44:

                                document.ClientIsResult = value;

                                break;

                            case 45:

                                document.ClientRemarks = value;

                                break;

                            case 46:

                                document.DTIsGateWay = value;

                                break;

                            case 47:

                                document.DTIsImport = value;

                                break;

                            case 48:

                                document.DTIsRegSystem = value;

                                break;

                            case 49:

                                document.DTRemarks = value;

                                break;

                        }

                    }

                    result.documents.Add(document);

                }

            }

            catch (Exception)

            {

                throw;

            }

            return result;

        }

        public ImportResult GemboxImport(string fileName)

        {

            ImportResult result = new ImportResult();

            StringBuilder sbErrMsg = new StringBuilder();

            try

            {

                var exFile = ExcelFile.Load(fileName);

                var ws = exFile.Worksheets[0];

                int rowCount = ws.Rows.Count;

                int columnCount = ws.CalculateMaxUsedColumns();

                int exRow = 8;

                #region Excel 유효성검사

                //Excel 포멧체크

                if (rowCount < 10 || columnCount != 49)

                {

                    result.Error = "Please, check the excel.";

                    return result;

                }

                #region 엑셀 필수값 체크(도면 : 이름,담당자, 난이도, Typical)

                ws.Rows.SelectMany(row => row.AllocatedCells)

                       .Where(col => col.Column.Index > 8 && col.Column.Index < 18 && col.Row.Index > exRow && col.Value == null)

                       .ToList()

                       .ForEach(p => sbErrMsg.Append(", " + p.Column.Name + p.Row.Name));

                if (sbErrMsg.Length > 0)

                {

                    string errMsg = sbErrMsg.ToString().Substring(2);

                    if (errMsg.Length > 100)

                    {

                        errMsg = $"{errMsg.Substring(0, 100)}...";

                    }

                    result.Error = $"Please, check null value in excel.\n{errMsg}";

                    return result;

                }

                #endregion

                #region 엑셀 도면명 중복 값 체크

                ws.Rows.SelectMany(row => row.AllocatedCells)

                                             .Where(col => col.Column.Index == 12 && col.Row.Index > exRow)

                                             .GroupBy(g => g.Row.Index)

                                             .Select(p => new

                                             {

                                                 rowIndex = p.Key,

                                                 docNo = p.Select(x => x.Value.ToString()).FirstOrDefault()

                                             })

                                             .GroupBy(g => g.docNo)

                                             .Where(p => p.Count() > 1)

                                             .Select(p => p.Select(x => (x.rowIndex + 1).ToString())

                                                           .Aggregate((x, y) => x.ToString() + "," + y.ToString())

                                                           .ToString())

                                             .ToList()

                                             .ForEach(p => sbErrMsg.Append("\n" + p.ToString()));

                if (sbErrMsg.Length > 0)

                {

                    sbErrMsg.Insert(0, "\n중복 된 도면명 Excel row : ");

                    string errMsg = sbErrMsg.ToString();

                    if (errMsg.Length > 100)

                    {

                        errMsg = $"{errMsg.Substring(0, 100)}...";

                    }

                    result.Error = $"Please, check the duplicate value in excel.\n{errMsg}";

                    return result;

                }

                #endregion

                #endregion

                result.documents = new List<Documents>();

                ws.Rows.Where(row => row.Index > exRow)

                       .ToList()

                       .ForEach(p =>

                       {

                           try

                           {

                               result.documents.Add(new Documents()

                               {

                                   RefProjectCode = ws.Rows[p.Index].Cells[8].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[8].Value.ToString(),

                                   System = ws.Rows[p.Index].Cells[9].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[9].Value.ToString(),

                                   SubSystemCode = ws.Rows[p.Index].Cells[10].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[10].Value.ToString(),

                                   DocumentNo = ws.Rows[p.Index].Cells[11].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[11].Value.ToString(),

                                   PersonInCharge = ws.Rows[p.Index].Cells[12].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[12].Value.ToString()).ID,

                                   Worker = ws.Rows[p.Index].Cells[13].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[13].Value.ToString()).ID,

                                   AVEVAPersonInCharge = ws.Rows[p.Index].Cells[14].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[14].Value.ToString()).ID,

                                   AVEVAWorker = ws.Rows[p.Index].Cells[15].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[15].Value.ToString()).ID,

                                   JobLevel = ws.Rows[p.Index].Cells[16].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[16].Value.ToString(),

                                   RevisonNo = ws.Rows[p.Index].Cells[17].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[17].Value.ToString(),

                                   ToIsDiscussion = ws.Rows[p.Index].Cells[18].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[18].Value.ToString(),

                                   ToRemarks = ws.Rows[p.Index].Cells[19].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[19].Value.ToString(),

                                   ToCreator = ws.Rows[p.Index].Cells[20].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[20].Value.ToString()).ID,

                                   //toreview-21

                                   FrReviewStatus = ws.Rows[p.Index].Cells[22].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[22].Value.ToString(),

                                   FrRemarks = ws.Rows[p.Index].Cells[23].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[23].Value.ToString(),

                                   FrCreator = ws.Rows[p.Index].Cells[24].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[24].Value.ToString()).ID,

                                   //frreview-25

                                   ID2StartDate = ws.Rows[p.Index].Cells[26].IsNullOrEmpty() ? (DateTime?)null : Convert.ToDateTime(ws.Rows[p.Index].Cells[26].Value?.ToString()),

                                   ID2EndDate = ws.Rows[p.Index].Cells[27].IsNullOrEmpty() ? (DateTime?)null : Convert.ToDateTime(ws.Rows[p.Index].Cells[27].Value?.ToString()),

                                   ID2Status = ws.Rows[p.Index].Cells[28].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[28].Value.ToString(),

                                   ID2Issues = ws.Rows[p.Index].Cells[29].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[29].Value.ToString(),

                                   //id2work-30

                                   ReplyModifications = ws.Rows[p.Index].Cells[31].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[31].Value.ToString(),

                                   ReplyRequester = ws.Rows[p.Index].Cells[32].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[32].Value.ToString()).ID,

                                   IsConvert = ws.Rows[p.Index].Cells[33].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[33].Value.ToString(),

                                   AVEVAConvertDate = ws.Rows[p.Index].Cells[34].IsNullOrEmpty() ? (DateTime?)null : Convert.ToDateTime(ws.Rows[p.Index].Cells[34].Value.ToString()),

                                   AVEVAWorkDate = ws.Rows[p.Index].Cells[35].IsNullOrEmpty() ? (DateTime?)null : Convert.ToDateTime(ws.Rows[p.Index].Cells[35].Value.ToString()),

                                   AVEVAStatus = ws.Rows[p.Index].Cells[36].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[36].Value.ToString(),

                                   AVEVAIssues = ws.Rows[p.Index].Cells[37].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[37].Value.ToString(),

                                   AVEVAReviewDate = ws.Rows[p.Index].Cells[38].IsNullOrEmpty() ? (DateTime?)null : Convert.ToDateTime(ws.Rows[p.Index].Cells[38].Value.ToString()),

                                   ProdReviewer = ws.Rows[p.Index].Cells[39].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[39].Value.ToString()).ID,

                                   ProdIsResult = ws.Rows[p.Index].Cells[40].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[40].Value.ToString(),

                                   ProdRemarks = ws.Rows[p.Index].Cells[41].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[41].Value.ToString(),

                                   ClientReviewer = ws.Rows[p.Index].Cells[42].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[42].Value.ToString()).ID,

                                   ClientIsResult = ws.Rows[p.Index].Cells[43].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[43].Value.ToString(),

                                   ClientRemarks = ws.Rows[p.Index].Cells[44].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[44].Value.ToString(),

                                   DTIsGateWay = ws.Rows[p.Index].Cells[45].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[45].Value.ToString(),

                                   DTIsImport = ws.Rows[p.Index].Cells[46].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[46].Value.ToString(),

                                   DTIsRegSystem = ws.Rows[p.Index].Cells[47].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[47].Value.ToString(),

                                   DTRemarks = ws.Rows[p.Index].Cells[48].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[48].Value.ToString()

                                   /*

                                   //UID = string.Empty,

                                   //Type = this.radTextBoxInsulationType.Text,

                                   //TempFrom = ws.Rows[exRow].Cells[p.Column.Index].IsNullOrEmpty() ? 0 : Convert.ToSingle(ws.Rows[exRow].Cells[p.Column.Index].Value),

                                   //TempTo = ws.Rows[exRow + 2].Cells[p.Column.Index].IsNullOrEmpty() ? 0 : Convert.ToSingle(ws.Rows[exRow + 2].Cells[p.Column.Index].Value),

                                   //NPS = ws.Rows[p.Row.Index].Cells[0].IsNullOrEmpty() ? 0 : Convert.ToSingle(ws.Rows[p.Row.Index].Cells[0].Value),

                                   //Thickness = p.IsNullOrEmpty() ? 0 : Convert.ToSingle(p.Value)

                                   RefProjectCode = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   DocumentNo = ws.Rows[p.Index].Cells[6].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[6].Value.ToString(),

                                   PersonInCharge = ws.Rows[p.Index].Cells[7].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[7].Value.ToString()).ID,

                                   JobLevel = ws.Rows[p.Index].Cells[8].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[8].Value.ToString(),

                                   //IsTypical = ws.Rows[p.Index].Cells[9].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[9].Value.ToString(),

                                   RevisonNo = ws.Rows[p.Index].Cells[10].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[10].Value.ToString(),

                                   ToIsDiscussion = ws.Rows[p.Index].Cells[11].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[11].Value.ToString(),

                                   ToRemarks = ws.Rows[p.Index].Cells[12].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[12].Value.ToString(),

                                   ToCreator = ws.Rows[p.Index].Cells[13].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[13].Value.ToString()).ID,

                                   //ToCapture = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   //ToIsMarkup = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   FrReviewStatus = ws.Rows[p.Index].Cells[16].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[16].Value.ToString(),

                                   FrRemarks = ws.Rows[p.Index].Cells[17].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[17].Value.ToString(),

                                   FrCreator = ws.Rows[p.Index].Cells[18].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[18].Value.ToString()).ID,

                                   //FrCapture = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   //FrIsMarkup = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   //IsID2Work = ws.Rows[p.Index].Cells[21].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[21].Value.ToString(),

                                   //ID2Connection = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   ID2StartDate = ws.Rows[p.Index].Cells[23].IsNullOrEmpty() ? (DateTime?)null : Convert.ToDateTime(ws.Rows[p.Index].Cells[23].Value?.ToString()),

                                   ID2EndDate = ws.Rows[p.Index].Cells[24].IsNullOrEmpty() ? (DateTime?)null : Convert.ToDateTime(ws.Rows[p.Index].Cells[24].Value?.ToString()),

                                   //ID2JobTime = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   ID2Status = ws.Rows[p.Index].Cells[26].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[26].Value.ToString(),

                                   ID2Issues = ws.Rows[p.Index].Cells[27].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[27].Value.ToString(),

                                   //AVEVAConnection = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   AVEVAConvertDate = ws.Rows[p.Index].Cells[29].IsNullOrEmpty() ? (DateTime?)null : Convert.ToDateTime(ws.Rows[p.Index].Cells[29].Value.ToString()),

                                   AVEVAReviewDate = ws.Rows[p.Index].Cells[30].IsNullOrEmpty() ? (DateTime?)null : Convert.ToDateTime(ws.Rows[p.Index].Cells[30].Value.ToString()),

                                   //AVEVAWorkDate 추가필요

                                   AVEVAStatus = ws.Rows[p.Index].Cells[31].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[31].Value.ToString(),

                                   AVEVAIssues = ws.Rows[p.Index].Cells[32].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[32].Value.ToString(),

                                   ProdReviewer = ws.Rows[p.Index].Cells[35].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[35].Value.ToString()).ID,

                                   ProdIsResult = ws.Rows[p.Index].Cells[36].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[36].Value.ToString(),

                                   ProdRemarks = ws.Rows[p.Index].Cells[37].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[37].Value.ToString(),

                                   ClientReviewer = ws.Rows[p.Index].Cells[38].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[38].Value.ToString()).ID,

                                   ClientIsResult = ws.Rows[p.Index].Cells[39].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[39].Value.ToString(),

                                   ClientRemarks = ws.Rows[p.Index].Cells[40].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[40].Value.ToString(),

                                   DTIsGateWay = ws.Rows[p.Index].Cells[41].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[41].Value.ToString(),

                                   DTIsImport = ws.Rows[p.Index].Cells[42].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[42].Value.ToString(),

                                   DTIsRegSystem = ws.Rows[p.Index].Cells[43].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[43].Value.ToString(),

                                   DTRemarks = ws.Rows[p.Index].Cells[44].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[44].Value.ToString()

                                   */

                               });

                           }

                           catch (Exception ex)

                           {

                               throw new Exception($"Excel Import Row :{p.Index} Error.", ex);

                           }

                       });

            }

            catch (Exception)

            {

                throw;

            }

            return result;

        }

    }

}