HYTOS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using GemBox.Spreadsheet;

using System.Threading.Tasks;

namespace SPPID.Utill

{

    static class ExcelUtill

    {

        const string SPPID_MappingCell = "N";

        readonly static string _LicenseKey = "EXK0-W4HZ-N518-IMEW";

        public static void LoadMappingExcelFile(string filePath, Dictionary<string, string> mapping)

        {

            try

            {

                SpreadsheetInfo.SetLicense(_LicenseKey);

                ExcelFile excelFile = ExcelFile.Load(filePath);

                foreach (ExcelWorksheet sheet in excelFile.Worksheets)

                {

                    if (sheet.Name == "Equipment" ||

                        sheet.Name == "Equipment Components" ||

                        sheet.Name == "Instrumentation" ||

                        sheet.Name == "Piping")

                    {

                        SetMappingSheet(sheet, mapping);

                    }

                }

            }

            catch (Exception ex)

            {

                Log.WriteLine(ex);

            }

        }

        private static void SetMappingSheet(ExcelWorksheet sheet, Dictionary<string, string> mapping)

        {

            for (int i = 1; i < sheet.Rows.Count; i++)

            {

                ExcelRow row = sheet.Rows[i];

                object AValue = row.Cells[0].Value;

                if (AValue != null && !string.IsNullOrEmpty(AValue.ToString()))

                {

                    // ID2 Name

                    object mappingCellValue = row.Cells[13].Value;

                    if (mappingCellValue != null && !string.IsNullOrEmpty(mappingCellValue.ToString()))

                    {

                        string sMappingCellValue = mappingCellValue.ToString();

                        string symbolPath = GetSymbolPath(row);

                        if (!string.IsNullOrEmpty(symbolPath))

                        {

                            if (mapping.ContainsKey(sMappingCellValue))

                                mapping[sMappingCellValue] = symbolPath;

                            else

                                mapping.Add(sMappingCellValue, symbolPath);

                        }

                    }

                }

            }

        }

        private static string GetSymbolPath(ExcelRow row)

        {

            StringBuilder sb = new StringBuilder();

            for (int i = 1; i < 6; i++)

            {

                object cellValue = row.Cells[i].Value;

                if (cellValue != null && !string.IsNullOrEmpty(cellValue.ToString()))

                {

                    sb.Append(@"\" + cellValue.ToString());

                }

                else

                    break;

            }

            object symbolName = row.Cells[8].Value;

            if (symbolName != null && !string.IsNullOrEmpty(symbolName.ToString()))

            {

                sb.Append(@"\" + symbolName.ToString());

                return sb.ToString();

            }

            else

            {

                return "";

            }

        }

    }

}