using Aitex.Core.RT.Log;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.IO;
using System.Linq;
using System.Net.Mime;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Input;
using System.Xml;
using System.Xml.Serialization;
using Aitex.Common.Util;
using Aitex.Core.Common.DeviceData;
using Aitex.Core.RT.Event;
using Aitex.Core.UI.MVVM;
using Aitex.Core.Util;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using MECF.Framework.Common.CommonData;
using MECF.Framework.Common.DataCenter;
using MECF.Framework.Common.Equipment;
using MECF.Framework.Common.OperationCenter;
using OpenSEMI.ClientBase;
using SciChart.Charting.Visuals.RenderableSeries;
using SciChart.Core.Extensions;
using Color = System.Drawing.Color;
using VirgoUI.Client.Models.Sys;

namespace VirgoUI.Client.Models.PMs
{
    public class RFCalibrationViewModel : ModuleUiViewModelBase, ISupportMultipleSystem
    {
        public bool SystemInManual { get; set; }
        public bool IsNextEnable { get; set; }
        public bool IsSaveEnable { get; set; } = true;

        private AITRfData _currentSelectionRF;
        public AITRfData CurrentSelectionRF
        {
            get { return _currentSelectionRF; }
            set
            {
                _currentSelectionRF = value;
                NotifyOfPropertyChange("CurrentSelectionRF");
            }
        }

        #region RF1
        private AITRfData _rf1;
        [Subscription("Rf.DeviceData")]
        public AITRfData Rf1
        {
            get
            {
                return _rf1;
            }
            set
            {
                _rf1 = value;
            }
        }

        private AITRfData _rfBias1;
        [Subscription("BiasRf.DeviceData")]
        public AITRfData RfBias1
        {
            get
            {
                return _rfBias1;
            }
            set
            {
                _rfBias1 = value;
            }
        }

        #endregion
  

        public ObservableCollection<IRenderableSeries> RenderableSeries { get; set; }
        public ICalibrationTableViewModelParameter CustomParameter { get; set; }

        public class NotifiableCalibrationTableItem : NotifiableItem
        {
            public string DisplayName { get; set; }

            public bool IsSelected { get; set; }

            [XmlIgnore]
            public bool IsSelectedEnable { get; set; } = true;

            public float _setPoint;
            public float SetPoint
            {
                get
                {
                    return _setPoint;
                }
                set
                {
                    _setPoint = value;
                    InvokePropertyChanged("SetPoint");
                }
            }

            [XmlIgnore]
            public bool SetPointEnable { get; set; } = true;

            public float _forwardPowerUI;
            public float ForwardPowerUI
            {
                get
                {
                    return _forwardPowerUI;
                }
                set
                {
                    _forwardPowerUI = value;
                    InvokePropertyChanged("ForwardPowerUI");
                }
            }

            private float _forwardPowerMeter;
            public float ForwardPowerMeter
            {
                get
                {
                    return _forwardPowerMeter;
                }
                set
                {
                    _forwardPowerMeter = value;
                    InvokePropertyChanged("ForwardPowerMeter");
                    InvokePropertyChanged("Difference");
                    InvokePropertyChanged("DifferenceBackground");
                }
            }

            public float ReflectedPower { get; set; }

            public float Difference
            {
                get
                {
                    if (SetPoint == 0)
                        return 0;
                    return (ForwardPowerMeter - SetPoint) * 100 / SetPoint;
                }
            }

            public string DifferenceBackground
            {
                get
                {
                    return Math.Abs(Difference) <= 3 ? "LightGreen" : "Red";
                }
            }
        }

        public ObservableCollection<CalibrationTableItem> CalibrationItems { get; set; }
        private List<int> selectedIndexs = new List<int>();
        public ObservableCollection<NotifiableCalibrationTableItem> TableData { get; set; }

        private int _tableDataSelectedIndex;
        public int TableDataSelectedIndex
        {
            get { return _tableDataSelectedIndex; }
            set
            {
                _tableDataSelectedIndex = value;
                NotifyOfPropertyChange("TableDataSelectedIndex");
            }
        }

        private CalibrationTableItem _currentSelection;
        public CalibrationTableItem CurrentSelection
        {
            get { return _currentSelection; }
            set
            {
                _currentSelection = value;
                ChangeSelection(_currentSelection);

                NotifyOfPropertyChange(nameof(CurrentSelection));
            }
        }

        private string _GeneratorSerialNumber;
        public string GeneratorSerialNumber
        {
            get { return _GeneratorSerialNumber; }
            set
            {
                _GeneratorSerialNumber = value;
                NotifyOfPropertyChange(nameof(GeneratorSerialNumber));
            }
        }

        private string _SensorSerialNumber;
        public string SensorSerialNumber
        {
            get { return _SensorSerialNumber; }
            set
            {
                _SensorSerialNumber = value;
                NotifyOfPropertyChange(nameof(SensorSerialNumber));
            }
        }

        private double _RFPhysicalMaxPower;
        public double RFPhysicalMaxPower
        {
            get { return _RFPhysicalMaxPower; }
            set
            {
                _RFPhysicalMaxPower = value;
                NotifyOfPropertyChange(nameof(RFPhysicalMaxPower));
            }
        }

        private double _CurrentRFMaxPower;
        public double CurrentRFMaxPower
        {
            get { return _CurrentRFMaxPower; }
            set
            {
                _CurrentRFMaxPower = value;
                NotifyOfPropertyChange(nameof(CurrentRFMaxPower));
            }
        }

        private double _RFCalibratedMaxPower;
        public double RFCalibratedMaxPower
        {
            get { return _RFCalibratedMaxPower; }
            set
            {
                _RFCalibratedMaxPower = value;
                NotifyOfPropertyChange(nameof(RFCalibratedMaxPower));
            }
        }

        private bool _IsSelectedAllEnable;
        public bool IsSelectedAllEnable
        {
            get { return _IsSelectedAllEnable; }
            set
            {
                _IsSelectedAllEnable = value;
                NotifyOfPropertyChange(nameof(IsSelectedAllEnable));
            }
        }


        public RFCalibrationViewModel()
        {
            DisplayName = "Calibration Table ";

            CalibrationItems = new ObservableCollection<CalibrationTableItem>();
            TableData = new ObservableCollection<NotifiableCalibrationTableItem>();
            RenderableSeries = new ObservableCollection<IRenderableSeries>();
            IsSelectedAllEnable = true;
        }

        protected override void OnInitialize()
        {
            base.OnInitialize();
            if (CustomParameter != null && CustomParameter.Items != null && CalibrationItems.IsEmpty())
            {
                foreach (var item in CustomParameter.Items)
                {
                    if (GetRFEnablebyDisplayName(item.DisplayName))
                        CalibrationItems.Add(new CalibrationTableItem()
                        {
                            DisplayName = item.DisplayName,
                            ItemEnableScName = item.ItemEnableScName,
                            ItemTableScName = item.ItemTableScName,
                        });
                }
            }

        }

        protected override void OnActivate()
        {
            base.OnActivate();
        }

        private AITRfData GetRFbyDisplayName(string displayName)
        {
            switch (displayName)
            {
                case "Source RF":
                    return Rf1;
                case "Bias RF":
                    return RfBias1;
            }
            return null;
        }
        private bool GetRFEnablebyDisplayName(string displayName)
        {
            switch (displayName)
            {
                case "Bias RF":
                    return (bool)QueryDataClient.Instance.Service.GetConfig($"{SystemName}.BiasRf.EnableBiasRF");
            }
            return true;
        }

        protected void ChangeSelection(CalibrationTableItem item)
        {

            if (item == null)
            {
                TableData.Clear();
                return;
            }


            var rf = GetRFbyDisplayName(item.DisplayName);
            if (rf == null) return;


            GeneratorSerialNumber = QueryDataClient.Instance.Service.GetConfig($"{rf.Module}.{rf.DeviceName}.GeneratorSerialNumber").ToString();
            SensorSerialNumber = QueryDataClient.Instance.Service.GetConfig($"{rf.Module}.{rf.DeviceName}.SensorSerialNumber").ToString();
            RFPhysicalMaxPower = (double)QueryDataClient.Instance.Service.GetConfig($"{rf.Module}.{rf.DeviceName}.RFPhysicalMaxPower");
            CurrentRFMaxPower = (double)QueryDataClient.Instance.Service.GetConfig($"{rf.Module}.{rf.DeviceName}.CurrentRFMaxPower");
            RFCalibratedMaxPower = (double)QueryDataClient.Instance.Service.GetConfig($"{rf.Module}.{rf.DeviceName}.RFCalibratedMaxPower");

            var tableValues = QueryDataClient.Instance.Service.GetConfig(item.ItemTableDetailScName);
            if (tableValues == null)
                return;

            var scValue = (string)tableValues;

            if (string.IsNullOrEmpty(scValue))
            {
                InitTableData(item);
                return;
            }

            try
            {
                var tableData = CustomXmlSerializer.Deserialize<List<NotifiableCalibrationTableItem>>(scValue);
                //tableData = tableData.OrderBy(x => x.SetPoint).ToList();
                TableData = new ObservableCollection<NotifiableCalibrationTableItem>(tableData);
                NotifyOfPropertyChange(nameof(TableData));
            }
            catch (Exception ex)
            {
                InitTableData(item);
            }


        }

        private void InitTableData(CalibrationTableItem item)
        {
            var tableData = new List<NotifiableCalibrationTableItem>();
            for (int i = 0; i < 20; i++)
            {
                tableData.Add(new NotifiableCalibrationTableItem() { DisplayName = item.DisplayName, SetPoint = (i + 1) * 100, ForwardPowerUI = (i + 1) * 100, ForwardPowerMeter = (i + 1) * 100 });
            }

            TableData = new ObservableCollection<NotifiableCalibrationTableItem>(tableData);
            NotifyOfPropertyChange(nameof(TableData));
        }


        public void Save()
        {
            if (CurrentSelection == null)
                return;

            string detailData = CustomXmlSerializer.Serialize(TableData);
            string data = "";
            foreach (var item in TableData)
            {
                data += $"{item.SetPoint}#{item.ForwardPowerMeter};";
            }

            var rf = GetRFbyDisplayName(CurrentSelection.DisplayName);
            if (rf == null)
                return;
            InvokeClient.Instance.Service.DoOperation("System.SetConfig", $"{rf.Module}.{rf.DeviceName}.GeneratorSerialNumber", GeneratorSerialNumber);
            InvokeClient.Instance.Service.DoOperation("System.SetConfig", $"{rf.Module}.{rf.DeviceName}.SensorSerialNumber", SensorSerialNumber);
            InvokeClient.Instance.Service.DoOperation("System.SetConfig", $"{rf.Module}.{rf.DeviceName}.RFPhysicalMaxPower", RFPhysicalMaxPower);
            InvokeClient.Instance.Service.DoOperation("System.SetConfig", $"{rf.Module}.{rf.DeviceName}.CurrentRFMaxPower", CurrentRFMaxPower);
            InvokeClient.Instance.Service.DoOperation("System.SetConfig", $"{rf.Module}.{rf.DeviceName}.RFCalibratedMaxPower", RFCalibratedMaxPower);

            InvokeClient.Instance.Service.DoOperation("System.SetConfig", CurrentSelection.ItemTableScName, data);

            InvokeClient.Instance.Service.DoOperation("System.SetConfig", CurrentSelection.ItemTableDetailScName, detailData);

            Reload();

            MessageBox.Show("Save Successful !");
        }

        public void Reload()
        {
            ChangeSelection(CurrentSelection);
        }

        public void Start()
        {
            if (CurrentSelection == null)
                return;
            ////CurrentCalibrationTableItems = new List<NotifiableCalibrationTableItem>();
            //foreach (var item in TableData)
            //{
            //    if (item.IsSelected)
            //    {
            //        item.ForwardPowerUI = 0;
            //        item.ForwardPowerMeter = 0;
            //        item.ReflectedPower = 0;
            //        //CurrentCalibrationTableItems.Add(item);
            //    }
            //}
            selectedIndexs = new List<int>();
            for (int i = 0; i < TableData.Count; i++)
            {
                if (TableData[i].IsSelected)
                {
                    TableData[i].ForwardPowerUI = 0;
                    TableData[i].ForwardPowerMeter = 0;
                    TableData[i].ReflectedPower = 0;
                    selectedIndexs.Add(i);
                }

                IsSelectedAllEnable = false;
                TableData[i].SetPointEnable = false;
                TableData[i].IsSelectedEnable = false;
                TableData[i].InvokePropertyChanged("SetPointEnable");
                TableData[i].InvokePropertyChanged("IsSelectedEnable");
            }

            IsNextEnable = true;
            IsSaveEnable = false;
            Next();
        }

        public void Next()
        {
            if (selectedIndexs.Count > 0)
            {
                TableDataSelectedIndex = selectedIndexs[0];
                RFCalibration(TableData[TableDataSelectedIndex]);
                selectedIndexs.RemoveAt(0);
            }
            else
            {
                CalcResult();
            }
        }

        public void End()
        {
            var rf = GetRFbyDisplayName(CurrentSelection.DisplayName);
            if (rf == null)
                return;
            InvokeClient.Instance.Service.DoOperation($"{rf.Module}.{rf.DeviceName}.{AITRfOperation.SetPower}", 0);
            InvokeClient.Instance.Service.DoOperation($"{rf.Module}.{rf.DeviceName}.{AITRfOperation.SetPowerOnOff}", "false");
            selectedIndexs = new List<int>();
            TableDataSelectedIndex = 0;
            CalcResult();
        }

        public void SelectedAll()
        {
            if (TableData != null && TableData.Count > 0) { }
            TableData.ForEachDo(x =>
            {
                x.IsSelected = true;
                x.InvokePropertyChanged("IsSelected");
            });
        }

        public void UnselectedAll()
        {
            if (TableData != null && TableData.Count > 0)
                TableData.ForEachDo(x =>
                {
                    x.IsSelected = false;
                    x.InvokePropertyChanged("IsSelected");
                });
        }


        public void Export()
        {
            bool? result;
            string savePath;

            Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
            dlg.DefaultExt = ".xlsx"; // Default file extension 
            dlg.FileName = $"RFCalibration{DateTime.Now.ToString("yyyyMMddhhmmss")}";
            dlg.Filter = "Excel数据表格文件(*.xlsx)|*.xlsx"; // Filter files by extension 
            result = dlg.ShowDialog(); // Show open file dialog box
            savePath = dlg.FileName;

            if (result != true) // Process open file dialog box results
                return;

            if (File.Exists(dlg.FileName))
            {
                File.Delete(dlg.FileName);
            }

            if (result == true)
            {
                using (MemoryStream mem = new MemoryStream())
                {
                    using (var temp = File.OpenRead(PathManager.GetCfgDir() + "RFCalibration导出模板.xlsx"))
                    {
                        temp.CopyTo(mem);
                    }

                    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(mem, true))
                    {
                        WorkbookPart wbPart = doc.WorkbookPart;
                        Worksheet worksheet = wbPart.WorksheetParts.First().Worksheet;
                        //statement to get the sheetdata which contains the rows and cell in table  
                        SheetData sheetData = worksheet.GetFirstChild<SheetData>();

                        SharedStringTablePart shareStringPart;
                        if (wbPart.GetPartsOfType<SharedStringTablePart>().Any())
                            shareStringPart = wbPart.GetPartsOfType<SharedStringTablePart>().First();
                        else
                            shareStringPart = wbPart.AddNewPart<SharedStringTablePart>();

                        uint currentRowIndex = 2;
                        var currentRow = sheetData.GetRow(currentRowIndex);
                        uint[] lineStyles = currentRow.Elements<Cell>().Select(c => c.StyleIndex.Value).ToArray();
                        currentRow.RemoveAllChildren();
                        
                        currentRow.AppendChild(new Cell().SetValue(GeneratorSerialNumber, shareStringPart,
                            styleIndex: lineStyles[0]));
                        currentRow.AppendChild(new Cell().SetValue(SensorSerialNumber, shareStringPart,
                            styleIndex: lineStyles[1]));
                        currentRow.AppendChild(new Cell().SetValue(RFPhysicalMaxPower, shareStringPart,
                            styleIndex: lineStyles[2]));
                        currentRow.AppendChild(new Cell().SetValue(CurrentRFMaxPower, shareStringPart,
                            styleIndex: lineStyles[3]));
                        currentRow.AppendChild(new Cell().SetValue(RFCalibratedMaxPower, shareStringPart,
                            styleIndex: lineStyles[4]));
                        
                        currentRowIndex = 4;

                        for (int i = 0; i < TableData.Count; i++)
                        {
                            Row row = new Row() { Height = 30 };
                            row.RowIndex = currentRowIndex; //设置行号

                            row.AppendChild(new Cell().SetValue(TableData[i].SetPoint, shareStringPart,
                                styleIndex: lineStyles[0]));
                            row.AppendChild(new Cell().SetValue(TableData[i].IsSelected.ToString(), shareStringPart,
                                styleIndex: lineStyles[1]));
                            row.AppendChild(new Cell().SetValue(TableData[i].ForwardPowerUI, shareStringPart,
                                styleIndex: lineStyles[2]));
                            row.AppendChild(new Cell().SetValue(TableData[i].ForwardPowerMeter, shareStringPart,
                                styleIndex: lineStyles[3]));
                            row.AppendChild(new Cell().SetValue(TableData[i].ReflectedPower, shareStringPart,
                                styleIndex: lineStyles[4]));
                            row.AppendChild(new Cell().SetValue(TableData[i].Difference, shareStringPart,
                                styleIndex: lineStyles[5]));

                            sheetData.AppendChild(row);
                            currentRowIndex++;
                        }

                        wbPart.Workbook.Save();
                        doc.SaveAs(savePath);
                        doc.Close();
                    }

                    mem.Close();
                }
            }
        }
        private void CalcResult()
        {
            IsNextEnable = false;
            IsSaveEnable = true;
            RenderableSeries = new ObservableCollection<IRenderableSeries>();
            var ForwardPowerMeterLine = new ChartDataLine(CurrentSelection.DisplayName + " ForwardPowerMeter/SetPoint", CurrentSelection.DisplayName + " ForwardPowerMeter/SetPoint", System.Windows.Media.Color.FromRgb(Color.Red.R, Color.Red.G, Color.Red.B));
            var SetPointLine = new ChartDataLine(CurrentSelection.DisplayName + " SetPoint/SetPoint", CurrentSelection.DisplayName + " SetPoint/SetPoint", System.Windows.Media.Color.FromRgb(Color.Blue.R, Color.Blue.G, Color.Blue.B));
            for (int i = 0; i < TableData.Count; i++)
            {
                if (TableData[i].IsSelected)
                {
                    ForwardPowerMeterLine.Append(TableData[i].SetPoint, TableData[i].ForwardPowerMeter);
                    SetPointLine.Append(TableData[i].SetPoint, TableData[i].SetPoint);
                }
                IsSelectedAllEnable = true;
                TableData[i].SetPointEnable = true;
                TableData[i].IsSelectedEnable = true;
                TableData[i].InvokePropertyChanged("SetPointEnable");
                TableData[i].InvokePropertyChanged("IsSelectedEnable");
            }
            RenderableSeries.Add(ForwardPowerMeterLine);
            RenderableSeries.Add(SetPointLine);
            if (TableData.Count(x => x.IsSelected) == 0)
                return;
            RFCalibratedMaxPower = CurrentRFMaxPower * 100 / (100 - TableData.Where(x => x.IsSelected).Average(x => x.Difference));
        }

        public void RFCalibration(NotifiableCalibrationTableItem tableItem)
        {
            var rf = GetRFbyDisplayName(tableItem.DisplayName);
            if (rf == null)
                return;

            IsNextEnable = false;
            InvokeClient.Instance.Service.DoOperation($"{rf.Module}.{rf.DeviceName}.{AITRfOperation.SetPower}", tableItem.SetPoint);
            if (!rf.IsRfOn)
                InvokeClient.Instance.Service.DoOperation($"{rf.Module}.{rf.DeviceName}.{AITRfOperation.SetPowerOnOff}", "true");
            Task.Delay(10000).ContinueWith(x =>
            {
                UpdateForwardPower(tableItem);
                IsNextEnable = true;
            });

        }

        private void UpdateForwardPower(NotifiableCalibrationTableItem tableItem)
        {
            var tableDataItem = TableData.FirstOrDefault(x => x.SetPoint == tableItem.SetPoint);
            if (tableDataItem != null)
                tableDataItem.ForwardPowerUI = GetRFbyDisplayName(tableItem.DisplayName).ForwardPower;
        }


        protected override void InvokeAfterUpdateProperty(Dictionary<string, object> data)
        {
            SystemInManual = (bool)QueryDataClient.Instance.Service.GetData("System.IsIdle");
            if (CurrentSelection != null)
                CurrentSelectionRF = GetRFbyDisplayName(CurrentSelection.DisplayName);
            if (CurrentSelectionRF == null)
                CurrentSelectionRF = new AITRfData();
        }
    }

    public static class OpenXmlExcelExtentions
    {
        public static Sheet GetSheet(this WorkbookPart workbookPart, string sheetName)
        {
            return workbookPart.Workbook
                .GetFirstChild<Sheets>()
                .Elements<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
        }

        /// <summary>
        /// Given a worksheet and a row index, return the row.
        /// </summary>
        /// <param name="sheetData"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        public static Row GetRow(this SheetData sheetData, uint rowIndex)
        {
            return sheetData.
                  Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
        }
        public static Cell GetCell(this SheetData sheetData, string columnName, uint rowIndex)
        {
            Row row = GetRow(sheetData, rowIndex);

            if (row == null)
                return null;

            return row.Elements<Cell>().Where(c => string.Compare
                      (c.CellReference.Value, columnName +
                      rowIndex, true) == 0).FirstOrDefault();
        }

        // https://msdn.microsoft.com/en-us/library/office/cc861607.aspx
        // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet.
        // If the cell already exists, returns it.
        public static Cell GetOrCreateCell(this SheetData sheetData, string columnName, uint rowIndex)
        {
            string cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            return row.GetOrCreateCell(cellReference);
        }
        public static Cell GetOrCreateCell(this Row row, string cellReference)
        {
            // If there is not a cell with the specified column name, insert one.
            if (row.Elements<Cell>().Where(c => c?.CellReference?.Value == cellReference).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (cell.CellReference.Value.Length == cellReference.Length)
                    {
                        if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                        {
                            refCell = cell;
                            break;
                        }
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);
                return newCell;
            }
        }

        public static string GetValue(this Cell cell, SharedStringTablePart shareStringPart)
        {
            if (cell == null)
                return null;
            string cellvalue = cell.InnerText;
            if (cell.DataType != null)
            {
                if (cell.DataType == CellValues.SharedString)
                {
                    int id = -1;
                    if (Int32.TryParse(cellvalue, out id))
                    {
                        SharedStringItem item = GetItem(shareStringPart, id);
                        if (item.Text != null)
                        {
                            //code to take the string value
                            cellvalue = item.Text.Text;
                        }
                        else if (item.InnerText != null)
                        {
                            cellvalue = item.InnerText;
                        }
                        else if (item.InnerXml != null)
                        {
                            cellvalue = item.InnerXml;
                        }
                    }
                }
            }
            return cellvalue;
        }
        public static string GetValue(this Cell cell, string[] shareStringPartValues)
        {
            if (cell == null)
                return null;
            string cellvalue = cell.InnerText;
            if (cell.DataType != null)
            {
                if (cell.DataType == CellValues.SharedString)
                {
                    int id = -1;
                    if (Int32.TryParse(cellvalue, out id))
                    {
                        cellvalue = shareStringPartValues[id];
                    }
                }
            }
            return cellvalue;
        }

        public static Cell SetValue(this Cell cell, object value = null, SharedStringTablePart shareStringPart = null, int shareStringItemIndex = -1, uint styleIndex = 0)
        {
            if (value == null)
            {
                cell.CellValue = new CellValue();
                if (shareStringItemIndex != -1)
                {
                    cell.CellValue = new CellValue(shareStringItemIndex.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                }
            }
            else if (value is string str)
            {
                if (shareStringPart == null)
                {
                    cell.CellValue = new CellValue(str);
                    cell.DataType = new EnumValue<CellValues>(CellValues.String);
                }
                else
                {
                    // Insert the text into the SharedStringTablePart.
                    int index = shareStringPart.GetOrInsertItem(str, false);
                    // Set the value of cell
                    cell.CellValue = new CellValue(index.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                }
            }
            else if (value is int || value is short || value is long ||
              value is float || value is double || value is uint ||
              value is ulong || value is ushort || value is decimal)
            {
                cell.CellValue = new CellValue(value.ToString());
                cell.DataType = new EnumValue<CellValues>(CellValues.Number);
            }
            else if (value is DateTime date)
            {
                cell.CellValue = new CellValue(date.ToString("yyyy-MM-dd")); // ISO 861
                cell.DataType = new EnumValue<CellValues>(CellValues.Date);
            }
            else if (value is XmlDocument xd)
            {
                if (shareStringPart == null)
                {
                    throw new Exception("Param [shareStringPart] can't be null when value type is XmlDocument.");
                }
                else
                {
                    int index = shareStringPart.GetOrInsertItem(xd.OuterXml, true);
                    // Set the value of cell
                    cell.CellValue = new CellValue(index.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                }
            }

            if (styleIndex != 0)
                cell.StyleIndex = styleIndex;

            return cell;
        }

        // https://msdn.microsoft.com/en-us/library/office/gg278314.aspx
        // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text
        // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
        public static int GetOrInsertItem(this SharedStringTablePart shareStringPart, string content, bool isXml)
        {
            // If the part does not contain a SharedStringTable, create one.
            if (shareStringPart.SharedStringTable == null)
            {
                shareStringPart.SharedStringTable = new SharedStringTable();
            }

            int i = 0;

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if ((!isXml && item.InnerText == content) || (isXml && item.OuterXml == content))
                {
                    return i;
                }

                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem and return its index.
            if (isXml)
                shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(content));
            else
                shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(content)));
            shareStringPart.SharedStringTable.Save();

            return i;
        }
        private static SharedStringItem GetItem(this SharedStringTablePart shareStringPart, int id)
        {
            return shareStringPart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);
        }

        /// <summary>
        ///  https://docs.microsoft.com/en-us/office/open-xml/how-to-merge-two-adjacent-cells-in-a-spreadsheet
        /// </summary>
        /// <param name="worksheet"></param>
        /// <returns></returns>
        public static MergeCells GetOrCreateMergeCells(this Worksheet worksheet)
        {
            MergeCells mergeCells;
            if (worksheet.Elements<MergeCells>().Count() > 0)
            {
                mergeCells = worksheet.Elements<MergeCells>().First();
            }
            else
            {
                mergeCells = new MergeCells();

                // Insert a MergeCells object into the specified position.
                if (worksheet.Elements<CustomSheetView>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<CustomSheetView>().First());
                }
                else if (worksheet.Elements<DataConsolidate>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<DataConsolidate>().First());
                }
                else if (worksheet.Elements<SortState>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SortState>().First());
                }
                else if (worksheet.Elements<AutoFilter>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<AutoFilter>().First());
                }
                else if (worksheet.Elements<Scenarios>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<Scenarios>().First());
                }
                else if (worksheet.Elements<ProtectedRanges>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<ProtectedRanges>().First());
                }
                else if (worksheet.Elements<SheetProtection>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetProtection>().First());
                }
                else if (worksheet.Elements<SheetCalculationProperties>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetCalculationProperties>().First());
                }
                else
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
                }
                worksheet.Save();
            }
            return mergeCells;
        }

        /// <summary>
        ///  Given the names of two adjacent cells, merges the two cells.
        ///  Create the merged cell and append it to the MergeCells collection.
        ///  When two cells are merged, only the content from one cell is preserved:
        ///  the upper-left cell for left-to-right languages or the upper-right cell for right-to-left languages.
        /// </summary>
        /// <param name="mergeCells"></param>
        /// <param name="cell1Name"></param>
        /// <param name="cell2Name"></param>
        public static void MergeTwoCells(this MergeCells mergeCells, string cell1Name, string cell2Name)
        {
            MergeCell mergeCell = new MergeCell() { Reference = new StringValue(cell1Name + ":" + cell2Name) };
            mergeCells.Append(mergeCell);
        }

        public static IEnumerable<string> GetItemValues(this SharedStringTablePart shareStringPart)
        {
            foreach (var item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.Text != null)
                {
                    //code to take the string value
                    yield return item.Text.Text;
                }
                else if (item.InnerText != null)
                {
                    yield return item.InnerText;
                }
                else if (item.InnerXml != null)
                {
                    yield return item.InnerXml;
                }
                else
                {
                    yield return null;
                }
            };
        }
        public static XmlDocument GetCellAssociatedSharedStringItemXmlDocument(this SheetData sheetData, string columnName, uint rowIndex, SharedStringTablePart shareStringPart)
        {
            Cell cell = GetCell(sheetData, columnName, rowIndex);
            if (cell == null)
                return null;
            if (cell.DataType == CellValues.SharedString)
            {
                int id = -1;
                if (Int32.TryParse(cell.InnerText, out id))
                {
                    SharedStringItem ssi = shareStringPart.GetItem(id);
                    var doc = new XmlDocument();
                    doc.LoadXml(ssi.OuterXml);
                    return doc;
                }
            }
            return null;
        }
    }
}