HYTOS

using GemBox.Spreadsheet;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace ID2.Manager.Common.Helpers

{

    public static class GemboxHelper

    {

        public static bool IsNumber(this ExcelCell cell)

        {

            if(cell.ValueType == CellValueType.Int)

            {

                return true;

            }

            else

            {

                return false;

            }

        }

        public static bool IsString(this ExcelCell cell)

        {

            if (cell.ValueType == CellValueType.String)

            {

                return true;

            }

            else

            {

                return false;

            }

        }

        public static bool IsDateTime(this ExcelCell cell)

        {

            if (cell.ValueType == CellValueType.DateTime)

            {

                return true;

            }

            else

            {

                return false;

            }

        }

        public static bool IsNullOrEmpty(this ExcelCell cell)

        {

            if(cell.IsString())

                return string.IsNullOrEmpty(cell.StringValue);

            else

                return cell.Value == null;

        }

    }

}

using ID2.Manager.Data.Models;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using GemBox.Spreadsheet;

using ID2.Manager.Controller.Controllers;

namespace ID2.Manager.Common.Helpers

{

    public class ID2Excel :IDisposable

    {

        private List<UserInfo> UserlList = new List<UserInfo>();

        public void Dispose()

        {

        }

        public ID2Excel()

        {

            UserlList = new UserController().GetAllUserInfo().ToList();

        }

        private UserInfo GetUser(string user)

        {

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

            if (userInfo != null) return userInfo;

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

            if (userInfo != null) return userInfo;

            return userInfo ?? new UserInfo();

        }

        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 != 45)

                {

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

                    return result;

                }

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

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

                       .Where(col => col.Column.Index > 5 && col.Column.Index < 10 && 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 == 6 && 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()

                               {

                                   //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()),

                                   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;

        }

    }

}

using ID2.Manager.Data.Models;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace ID2.Manager.Common.Helpers

{

    public class ImportResult

    {

        public string Error { get; set; }

        public List<Documents> documents { get; set; }

    }

}

    <Compile Include="Helpers\GemboxHelper.cs" />

    <Compile Include="Helpers\ID2Excel.cs" />

    <Compile Include="Helpers\ImportResult.cs" />

  <ItemGroup />

    <PackageReference Include="GemBox.Spreadsheet">

      <Version>47.0.1268</Version>

    </PackageReference>

    <ProjectReference Include="..\ID2.Manager.Controller\ID2.Manager.Controller.csproj">

      <Project>{83DACC10-43B1-4E38-ABEF-E5EF9492A82D}</Project>

      <Name>ID2.Manager.Controller</Name>

    </ProjectReference>

            this.tableLayoutPanelCondition.ColumnStyles.Add(new System.Windows.Forms.ColumnStyle(System.Windows.Forms.SizeType.Absolute, 74F));

            this.radDropDownListFrReviewStatus.Location = new System.Drawing.Point(345, 32);

            this.radDropDownListFrReviewStatus.Size = new System.Drawing.Size(111, 20);

            this.radDropDownListToIsDiscussion.Size = new System.Drawing.Size(111, 20);

            this.radLabelFrReviewStatus.Location = new System.Drawing.Point(259, 34);

            this.radLabelAVEVAStatus.Location = new System.Drawing.Point(259, 62);

            this.radLabelIsID2Work.Location = new System.Drawing.Point(463, 34);

            this.radLabelJobLevel.Location = new System.Drawing.Point(463, 6);

            this.radButtonSearch.Location = new System.Drawing.Point(879, 4);

            this.radDropDownListProject.Size = new System.Drawing.Size(111, 20);

            this.radLabelDocumentNo.Location = new System.Drawing.Point(667, 6);

            this.radTextBoxDocumentNo.Location = new System.Drawing.Point(753, 4);

            this.radTextBoxDocumentNo.Size = new System.Drawing.Size(111, 20);

            this.radLabelPersonInCharge.Location = new System.Drawing.Point(259, 6);

            this.radDropDownListPersonInCharge.Location = new System.Drawing.Point(345, 4);

            this.radDropDownListPersonInCharge.Size = new System.Drawing.Size(111, 20);

            this.radDropDownListJobLevel.Location = new System.Drawing.Point(549, 4);

            this.radDropDownListJobLevel.Size = new System.Drawing.Size(111, 20);

            this.radLabel10.Location = new System.Drawing.Point(259, 90);

            this.radDropDownListIsID2Work.Location = new System.Drawing.Point(549, 32);

            this.radDropDownListIsID2Work.Size = new System.Drawing.Size(111, 20);

            this.radDropDownListID2Status.Size = new System.Drawing.Size(111, 20);

            this.radDropDownListAVEVAStatus.Location = new System.Drawing.Point(345, 60);

            this.radDropDownListAVEVAStatus.Size = new System.Drawing.Size(111, 20);

            this.radDropDownListProdIsResult.Size = new System.Drawing.Size(111, 20);

            this.radDropDownListClientIsResult.Location = new System.Drawing.Point(345, 89);

            this.radDropDownListClientIsResult.Size = new System.Drawing.Size(111, 20);

using ID2.Manager.Common.Helpers;

                    using(ID2Excel excel = new ID2Excel())

                    {

                       var result = excel.GemboxImport(ofd.FileName);

                        if(result.Error != null)

                        {

                            RadMessageBox.Show(result.Error, "Information", MessageBoxButtons.OK, RadMessageIcon.Info);

                        }

                        else

                        {

                            this.documents.AddRange(result.documents);

                            if (this.orgDocuments == null) this.orgDocuments = new List<Documents>();

                            this.DocumentListBinding();

                        }

                    }

                    //foreach (Documents appDoc in appendDocuments)

                    //{

                    //    GridViewRowInfo rowInfo = this.radGridViewDocuments.Rows.AddNew();

                    //    foreach (FieldInfo fieldInfo in appDoc.GetType().GetFields(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.DeclaredOnly))

                    //    {

                    //        if (fieldInfo.GetValue(appDoc) != null)

                    //        {

                    //            var cols = rowInfo.Cells.Where(x => fieldInfo.Name.Contains($"<{x.ColumnInfo.Name}>"));

                    //            if (cols.Any())

                    //            {

                    //                cols.FirstOrDefault().Value = fieldInfo.GetValue(appDoc);

                    //            }

                    //        }

                    //    }

                    //}

                }

            }

        }

        private void RadButtonElementExcelImport_Click_old(object sender, EventArgs e)

        {

            using (OpenFileDialog ofd = new OpenFileDialog()

            {

                Filter = "Excel files (*.xlsx)|*.xlsx",

                Title = "Open an Excel File",

                RestoreDirectory = true

            })

            {

                if (ofd.ShowDialog() == DialogResult.OK)

                {

                    //Error Message

                    StringBuilder sbErrMsg = new StringBuilder();

            Application.ThreadException += Application_ThreadException;

        private static void Application_ThreadException(object sender, System.Threading.ThreadExceptionEventArgs e)

        {

            logger.Error("Application Exception.", e.Exception);

        }