using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows;
using System.Windows.Media.Imaging;
//using Aitex.Core.Equipment.SusceptorDefine;
using Aitex.Core.RT.Event;
using Aitex.Core.RT.Log;
using Aitex.Core.UI.View.Common;
using Aitex.Sorter.Common;
using ExcelLibrary.SpreadSheet;
using MECF.Framework.Common.OperationCenter;
using log4net.Repository.Hierarchy;
using MECF.Framework.Common.DataCenter;
using Excel = Microsoft.Office.Interop.Excel;
using MECF.Framework.Common.Utilities;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Aitex.Sorter.UI.ViewModel
{
    public class HistoryViewModel : UIViewModelBase
    {
        public ObservableCollection<HistoryProcessData> ProcessData { get; set; }
        public ObservableCollection<HistoryWaferData> WaferData { get; set; }
        public ObservableCollection<HistoryMoveData> MovementData { get; set; }
        public ObservableCollection<HistoryOCRData> OcrCodeData { get; set; }
        public ObservableCollection<HistoryCarrierData> CarrierData { get; set; }
        public string Checklist { get; set; }
        public string CheckCount { get; set; }
        private Visibility _VisibilityId ;
        public Visibility VisibilityId
        {
            get => _VisibilityId;
            set
            {
                _VisibilityId = value;
                InvokePropertyChanged("VisibilityId");
            }
        }
        private Visibility _VisibilityLotId;
        public Visibility VisibilityLotId 
        { 
            get { return _VisibilityLotId; }
            set
            {
                _VisibilityLotId = value;
                InvokePropertyChanged("VisibilityLotId");
            }
        }
        private Visibility _VisibilityRfid;
        public Visibility VisibilityRfid
        {
            get { return _VisibilityRfid; }
            set
            {
                _VisibilityRfid = value;
                InvokePropertyChanged("VisibilityRfid");
            }
        }
        private Visibility _VisibilityPC ;
        public Visibility VisibilityPC 
        {
            get { return _VisibilityPC ; }
            set
            {
                _VisibilityPC = value;
                InvokePropertyChanged("VisibilityPC");
            }
        }
        public List<string> SlotSelection { get { return Slot; } }
        private List<string> Slot = new List<string>() { "Carrier", "Recipe", "Lot", "ProcessJob", "ControlJob" };
        public HistoryViewModel() : base("HistoryViewModel")
        {
            ProcessData = new ObservableCollection<HistoryProcessData>();
            WaferData = new ObservableCollection<HistoryWaferData>();
            MovementData = new ObservableCollection<HistoryMoveData>();
            CarrierData = new ObservableCollection<HistoryCarrierData>();
            OcrCodeData = new ObservableCollection<HistoryOCRData>();


            _VisibilityLotId = Visibility.Hidden;
            _VisibilityId = Visibility.Hidden;
            _VisibilityPC = Visibility.Hidden;
            _VisibilityRfid = Visibility.Hidden;
        }

        public void UpdateProcessData(DateTime begin, DateTime end)
        {
            try
            {
                string sql = string.Format(
                    "SELECT * FROM \"process_data\" where \"process_begin_time\" >= '{0}' and \"process_begin_time\" <= '{1}'  order by \"process_begin_time\" ASC;",
                    begin.ToString("yyyy/MM/dd HH:mm:ss.fff"), end.ToString("yyyy/MM/dd HH:mm:ss.fff"));

                List<HistoryProcessData> result = QueryDataClient.Instance.Service.QueryDBProcess(sql);

                Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                {
                    ProcessData = new ObservableCollection<HistoryProcessData>();

                    if (result != null)
                    {
                        foreach (HistoryProcessData ev in result)
                        {
                            ProcessData.Add(ev);
                        }

                    }

                    WaferData = new ObservableCollection<HistoryWaferData>();
                    MovementData = new ObservableCollection<HistoryMoveData>();
                    InvokePropertyChanged("ProcessData");
                    InvokePropertyChanged("WaferData");
                    InvokePropertyChanged("MovementData");
                }));
            }
            catch (Exception e)
            {
                LOG.Write(e);
            }
        }
        public void UpdateProcessData(DateTime begin, DateTime end,string Selected)
        {
            try
            {
                ProcessData.Clear();
                Checklist = Selected + " List";
                if (Selected == "Recipe")
                {

                    string sql = string.Format(
                        "SELECT * FROM \"process_data\" where \"process_begin_time\" >= '{0}' and \"process_begin_time\" <= '{1}'  order by \"process_begin_time\" ASC;",
                        begin.ToString("yyyy/MM/dd HH:mm:ss.fff"), end.ToString("yyyy/MM/dd HH:mm:ss.fff"));

                    List<HistoryProcessData> result = QueryDataClient.Instance.Service.QueryDBProcess(sql);
                    CheckCount = result.Count.ToString();
                    Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                    {
                        ProcessData = new ObservableCollection<HistoryProcessData>();

                        if (result != null)
                        {
                            foreach (HistoryProcessData ev in result)
                            {
                                ProcessData.Add(ev);
                            }

                        }

                        WaferData = new ObservableCollection<HistoryWaferData>();
                        MovementData = new ObservableCollection<HistoryMoveData>();
                        OcrCodeData = new ObservableCollection<HistoryOCRData>();

                        VisibilityId = Visibility.Visible;
                        VisibilityRfid = Visibility.Hidden;
                        VisibilityLotId = Visibility.Hidden;
                        VisibilityPC = Visibility.Hidden;

                        InvokePropertyChanged("ProcessData");
                        InvokePropertyChanged("WaferData");
                        InvokePropertyChanged("MovementData");
                        InvokePropertyChanged("OcrCodeData");                        
                        InvokePropertyChanged("CheckCount");
                        InvokePropertyChanged("VisibilityLotId");
                        InvokePropertyChanged("VisibilityId");
                        InvokePropertyChanged("VisibilityRfid");
                        InvokePropertyChanged("VisibilityPC");
                    }));                  
                }
                if (Selected == "Carrier")
                {                  
                    string sql = string.Format(
                        "SELECT * FROM \"carrier_data\" where \"load_time\" >= '{0}' and \"load_time\" <= '{1}'  order by \"load_time\" ASC;",
                        begin.ToString("yyyy/MM/dd HH:mm:ss.fff"), end.ToString("yyyy/MM/dd HH:mm:ss.fff"));

                    List<HistoryProcessData> result = QueryDataClient.Instance.Service.QueryDBProcessCarrier(sql);
                    CheckCount = result.Count.ToString();
                    Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                    {
                        ProcessData = new ObservableCollection<HistoryProcessData>();

                        if (result != null)
                        {
                            foreach (HistoryProcessData ev in result)
                            {
                                ProcessData.Add(ev);
                            }

                        }

                        WaferData = new ObservableCollection<HistoryWaferData>();
                        MovementData = new ObservableCollection<HistoryMoveData>();
                        OcrCodeData = new ObservableCollection<HistoryOCRData>();

                        VisibilityId = Visibility.Hidden;
                        VisibilityLotId = Visibility.Hidden;
                        VisibilityPC = Visibility.Hidden;
                        VisibilityRfid = Visibility.Visible;
                        InvokePropertyChanged("ProcessData");
                        InvokePropertyChanged("WaferData");
                        InvokePropertyChanged("MovementData");
                        InvokePropertyChanged("OcrCodeData");
                        InvokePropertyChanged("CheckCount");
                        InvokePropertyChanged("VisibilityLotId");
                        InvokePropertyChanged("VisibilityId");
                        InvokePropertyChanged("VisibilityRfid");
                        InvokePropertyChanged("VisibilityPC");
                    }));
                }
                if (Selected == "Lot")
                {                   
                    string sql = string.Format(
                        "SELECT * FROM \"lot_data\" where \"start_time\" >= '{0}' and \"start_time\" <= '{1}'  order by \"start_time\" ASC;",
                        begin.ToString("yyyy/MM/dd HH:mm:ss.fff"), end.ToString("yyyy/MM/dd HH:mm:ss.fff"));

                    List<HistoryProcessData> result = QueryDataClient.Instance.Service.QueryDBProcessLot(sql);
                    CheckCount = result.Count.ToString();
                    Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                    {
                        ProcessData = new ObservableCollection<HistoryProcessData>();

                        if (result != null)
                        {
                            foreach (HistoryProcessData ev in result)
                            {
                                ProcessData.Add(ev);
                            }

                        }

                        WaferData = new ObservableCollection<HistoryWaferData>();
                        MovementData = new ObservableCollection<HistoryMoveData>();
                        OcrCodeData = new ObservableCollection<HistoryOCRData>();

                        VisibilityId = Visibility.Hidden;
                        VisibilityLotId = Visibility.Visible;
                        VisibilityPC = Visibility.Hidden;
                        VisibilityRfid = Visibility.Hidden;
                        InvokePropertyChanged("ProcessData");
                        InvokePropertyChanged("WaferData");
                        InvokePropertyChanged("MovementData");
                        InvokePropertyChanged("OcrCodeData");
                        InvokePropertyChanged("CheckCount");
                        InvokePropertyChanged("VisibilityLotId");
                        InvokePropertyChanged("VisibilityId");
                        InvokePropertyChanged("VisibilityRfid");
                        InvokePropertyChanged("VisibilityPC");
                    }));                  
                }
                if (Selected == "ProcessJob")
                {

                    string sql = string.Format(
                        "SELECT * FROM \"carrier_data\" where \"load_time\" >= '{0}' and \"load_time\" <= '{1}'  order by \"load_time\" ASC;",
                        begin.ToString("yyyy/MM/dd HH:mm:ss.fff"), end.ToString("yyyy/MM/dd HH:mm:ss.fff"));

                    List<HistoryProcessData> result = QueryDataClient.Instance.Service.QueryDBProcessCarrier(sql);
                    CheckCount = result.Count.ToString();
                    Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                    {
                        ProcessData = new ObservableCollection<HistoryProcessData>();

                        if (result != null)
                        {
                            foreach (HistoryProcessData ev in result)
                            {
                                ProcessData.Add(ev);
                            }

                        }

                        VisibilityId = Visibility.Collapsed;
                        VisibilityLotId = Visibility.Collapsed;
                        VisibilityPC = Visibility.Collapsed;
                        VisibilityRfid = Visibility.Hidden;

                        WaferData = new ObservableCollection<HistoryWaferData>();
                        MovementData = new ObservableCollection<HistoryMoveData>();
                        OcrCodeData = new ObservableCollection<HistoryOCRData>();
                        InvokePropertyChanged("ProcessData");
                        InvokePropertyChanged("WaferData");
                        InvokePropertyChanged("MovementData");
                        InvokePropertyChanged("OcrCodeData");                        
                        InvokePropertyChanged("CheckCount");
                        InvokePropertyChanged("VisibilityLotId");
                        InvokePropertyChanged("VisibilityId");
                        InvokePropertyChanged("VisibilityRfid");
                        InvokePropertyChanged("VisibilityPC");
                    }));
                    
                }
                if (Selected == "ControlJob")
                {                   
                    string sql = string.Format(
                        "SELECT * FROM \"carrier_data\" where \"load_time\" >= '{0}' and \"load_time\" <= '{1}'  order by \"load_time\" ASC;",
                        begin.ToString("yyyy/MM/dd HH:mm:ss.fff"), end.ToString("yyyy/MM/dd HH:mm:ss.fff"));

                    List<HistoryProcessData> result = QueryDataClient.Instance.Service.QueryDBProcessCarrier(sql);
                    CheckCount = result.Count.ToString();
                    Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                    {
                        ProcessData = new ObservableCollection<HistoryProcessData>();

                        if (result != null)
                        {
                            foreach (HistoryProcessData ev in result)
                            {
                                ProcessData.Add(ev);
                            }

                        }

                        WaferData = new ObservableCollection<HistoryWaferData>();
                        MovementData = new ObservableCollection<HistoryMoveData>();
                        OcrCodeData = new ObservableCollection<HistoryOCRData>();
                        VisibilityId = Visibility.Hidden;
                        VisibilityLotId = Visibility.Hidden;
                        VisibilityPC = Visibility.Hidden;
                        VisibilityRfid = Visibility.Hidden;
                        InvokePropertyChanged("ProcessData");
                        InvokePropertyChanged("WaferData");
                        InvokePropertyChanged("MovementData");
                        InvokePropertyChanged("OcrCodeData");
                        InvokePropertyChanged("CheckCount");
                        InvokePropertyChanged("VisibilityLotId");
                        InvokePropertyChanged("VisibilityId");
                        InvokePropertyChanged("VisibilityRfid");
                        InvokePropertyChanged("VisibilityPC");
                    }));                   
                }
            }
            catch (Exception e)
            {
                LOG.Write(e);
            }
        }
        public void UpdateCarrierData(DateTime begin, DateTime end)
        {
            try
            {
                string sql = string.Format(
                    "SELECT * FROM \"carrier_data\" where \"load_time\" >= '{0}' and \"load_time\" <= '{1}'  order by \"load_time\" ASC;",
                    begin.ToString("yyyy/MM/dd HH:mm:ss.fff"), end.ToString("yyyy/MM/dd HH:mm:ss.fff"));

                List<HistoryCarrierData> result = QueryDataClient.Instance.Service.QueryDBCarrier(sql);

                Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                {
                    CarrierData = new ObservableCollection<HistoryCarrierData>();

                    if (result != null)
                    {
                        foreach (HistoryCarrierData ev in result)
                        {
                            CarrierData.Add(ev);
                        }

                    }

                    WaferData = new ObservableCollection<HistoryWaferData>();
                    MovementData = new ObservableCollection<HistoryMoveData>();
                    OcrCodeData = new ObservableCollection<HistoryOCRData>();
                    InvokePropertyChanged("CarrierData");
                    InvokePropertyChanged("WaferData");
                    InvokePropertyChanged("MovementData");
                    InvokePropertyChanged("OcrCodeData");
                }));
            }
            catch (Exception e)
            {
                LOG.Write(e);
            }
        }

        public void UpdateWaferDataByProcess(string processGuid)
        {
            try
            {
                string sql = string.Format(
                    "SELECT * FROM \"wafer_data\" where \"process_data_guid\" = '{0}'  order by \"create_slot\" ASC;",
                    processGuid);

                List<HistoryWaferData> result = QueryDataClient.Instance.Service.QueryDBWafer(sql);

                Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                {
                    WaferData = new ObservableCollection<HistoryWaferData>();

                    if (result != null)
                        foreach (HistoryWaferData ev in result)
                        {
                            string sql3 = string.Format("SELECT * FROM \"carrier_data\" where \"carrier_data\".\"guid\"='{0}';", ev.CarrierGuid);

                            List<HistoryCarrierData> dbData3 = QueryDataClient.Instance.Service.QueryDBCarrier(sql3);
                            if (dbData3 != null)
                            {
                                for (int i = 0; i < dbData3.Count; i++)
                                {
                                    ev.SourceCarrier = dbData3[i].Rfid.ToString();
                                }
                            }

                            string sql1 = string.Format("SELECT * FROM \"wafer_move_history\" where \"wafer_data_guid\" = '{0}'  order by \"arrive_time\" ASC;", ev.Guid);

                            List<HistoryMoveData> dbData = QueryDataClient.Instance.Service.QueryDBMovement(sql1);
                            if (dbData == null) continue;
                            for (int i = 0; i < dbData.Count; i++)
                            {
                                var station = dbData[i].Station.ToString();

                                if (station.Contains("LP"))
                                {
                                    ev.DestinationPort = station;
                                    var datetime = dbData[i].ArriveTime;
                                    string sql2 = string.Format("SELECT * FROM \"carrier_data\" where \"load_time\" < '{0}' and \"station\" = '{1}' order by \"load_time\" ASC ", datetime, station);
                                    List<HistoryCarrierData> dbData2 = QueryDataClient.Instance.Service.QueryDBCarrier(sql2);
                                    if (dbData2 == null) continue;
                                    for (int j = 0; j < dbData2.Count; j++)
                                    {
                                        if (j != dbData2.Count - 1) continue;
                                        ev.DestinationCarrier = dbData2[j].Rfid.ToString();
                                    }
                                }
                            }
                            WaferData.Add(ev);
                        }

                    MovementData = new ObservableCollection<HistoryMoveData>();
                    InvokePropertyChanged("WaferData");
                    InvokePropertyChanged("MovementData");
                }));
            }
            catch (Exception e)
            {
                LOG.Write(e);
            }
        }

        public void UpdateWaferDataByCarrier(string carrierGuid)
        {
            try
            {
                string sql = string.Format(
                    "SELECT * FROM \"wafer_data\" where \"carrier_data_guid\" = '{0}' order by \"create_slot\" ASC;",
                    carrierGuid);

                List<HistoryWaferData> result = QueryDataClient.Instance.Service.QueryDBWafer(sql);

                Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                {
                    WaferData = new ObservableCollection<HistoryWaferData>();

                    if (result != null)
                        foreach (HistoryWaferData ev in result)
                        {
                            string sql3 = string.Format("SELECT * FROM \"carrier_data\" where \"carrier_data\".\"guid\"='{0}';", ev.CarrierGuid);

                            List<HistoryCarrierData> dbData3 = QueryDataClient.Instance.Service.QueryDBCarrier(sql3);
                            if (dbData3 != null)
                            {
                                for (int i = 0; i < dbData3.Count; i++)
                                {
                                    ev.SourceCarrier = dbData3[i].Rfid.ToString();
                                }
                            }

                            string sql1 = string.Format("SELECT * FROM \"wafer_move_history\" where \"wafer_data_guid\" = '{0}'  order by \"arrive_time\" ASC;", ev.Guid);

                            List<HistoryMoveData> dbData = QueryDataClient.Instance.Service.QueryDBMovement(sql1);
                            if (dbData == null) continue;
                            for (int i = 0; i < dbData.Count; i++)
                            {
                                var station = dbData[i].Station.ToString();

                                if (station.Contains("LP"))
                                {
                                    ev.DestinationPort = station;
                                    var datetime = dbData[i].ArriveTime;
                                    string sql2 = string.Format("SELECT * FROM \"carrier_data\" where \"load_time\" < '{0}' and \"station\" = '{1}' order by \"load_time\" ASC ", datetime, station);
                                    List<HistoryCarrierData> dbData2 = QueryDataClient.Instance.Service.QueryDBCarrier(sql2);
                                    if (dbData2 == null) continue;
                                    for (int j = 0; j < dbData2.Count; j++)
                                    {
                                        if (j != dbData2.Count - 1) continue;
                                        
                                        ev.DestinationCarrier = dbData2[j].Rfid.ToString();
                                    }
                                }
                            }
                            WaferData.Add(ev);
                        }

                    MovementData = new ObservableCollection<HistoryMoveData>();
                    InvokePropertyChanged("WaferData");
                    InvokePropertyChanged("MovementData");
                }));
            }
            catch (Exception e)
            {
                LOG.Write(e);
            }
        }
        public void UpdateOcrData(string waferGuid)
        {
            try
            {
                string sql = string.Format(
                    "SELECT * FROM \"ocr_data\" where \"wafer_id\" = '{0}'  order by \"read_time\" ASC;",
                    waferGuid);

                List<HistoryOCRData> result = QueryDataClient.Instance.Service.QueryDBOCRHistory(sql);

                Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                {
                    OcrCodeData = new ObservableCollection<HistoryOCRData>();

                    if (result != null)
                        foreach (HistoryOCRData ev in result)
                        {
                            OcrCodeData.Add(ev);
                        }

                    InvokePropertyChanged("OcrCodeData");
                }));
            }
            catch (Exception e)
            {
                LOG.Write(e);
            }
        }
        public void UpdateMovementData(string waferGuid)
        {
            try
            {
                string sql = string.Format(
                    "SELECT * FROM \"wafer_move_history\" where \"wafer_data_guid\" = '{0}'  order by \"arrive_time\" ASC;",
                    waferGuid);

                List<HistoryMoveData> result = QueryDataClient.Instance.Service.QueryDBMovement(sql);

                Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                {
                    MovementData = new ObservableCollection<HistoryMoveData>();

                    if (result != null)
                        foreach (HistoryMoveData ev in result)
                        {
                            MovementData.Add(ev);
                        }

                    InvokePropertyChanged("MovementData");
                }));
            }
            catch (Exception e)
            {
                LOG.Write(e);
            }
        }
        public void UpdateWaferDataByLot(string lotGuid)
        {
            try
            {
                string sql = string.Format(
                    "SELECT * FROM \"wafer_data\" where \"lot_id\" = '{0}'  order by \"create_slot\" ASC;",
                    lotGuid);

                List<HistoryWaferData> result = QueryDataClient.Instance.Service.QueryDBWafer(sql);

                Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                {
                    WaferData = new ObservableCollection<HistoryWaferData>();

                    if (result != null)
                        foreach (HistoryWaferData ev in result)
                        {
                            string sql3 = string.Format("SELECT * FROM \"carrier_data\" where \"carrier_data\".\"guid\"='{0}';", ev.CarrierGuid);

                            List<HistoryCarrierData> dbData3 = QueryDataClient.Instance.Service.QueryDBCarrier(sql3);
                            if (dbData3 != null)
                            {
                                for (int i = 0; i < dbData3.Count; i++)
                                {
                                    ev.SourceCarrier = dbData3[i].Rfid.ToString();
                                }
                            }

                            string sql1 = string.Format("SELECT * FROM \"wafer_move_history\" where \"wafer_data_guid\" = '{0}'  order by \"arrive_time\" ASC;", ev.Guid);

                            List<HistoryMoveData> dbData = QueryDataClient.Instance.Service.QueryDBMovement(sql1);
                            if (dbData == null) continue;
                            for (int i = 0; i < dbData.Count; i++)
                            {
                                var station = dbData[i].Station.ToString();

                                if (station.Contains("LP"))
                                {
                                    ev.DestinationPort = station;
                                    var datetime = dbData[i].ArriveTime;
                                    string sql2 = string.Format("SELECT * FROM \"carrier_data\" where \"load_time\" < '{0}' and \"station\" = '{1}' order by \"load_time\" ASC ", datetime, station);
                                    List<HistoryCarrierData> dbData2 = QueryDataClient.Instance.Service.QueryDBCarrier(sql2);
                                    if (dbData2 == null) continue;
                                    for (int j = 0; j < dbData2.Count; j++)
                                    {
                                        if (j != dbData2.Count - 1) continue;
                                        ev.DestinationCarrier = dbData2[j].Rfid.ToString();
                                    }
                                }
                            }
                            WaferData.Add(ev);
                        }

                    MovementData = new ObservableCollection<HistoryMoveData>();
                    InvokePropertyChanged("WaferData");
                    InvokePropertyChanged("MovementData");
                }));
            }
            catch (Exception e)
            {
                LOG.Write(e);
            }
        }
        public void UpdateWaferDataByPj(string PjGuid)
        {
            try
            {
                string sql = string.Format(
                    "SELECT * FROM \"wafer_data\" where \"pj_data_guid\" = '{0}'  order by \"create_slot\" ASC;",
                    PjGuid);

                List<HistoryWaferData> result = QueryDataClient.Instance.Service.QueryDBWafer(sql);

                Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                {
                    WaferData = new ObservableCollection<HistoryWaferData>();

                    if (result != null)
                        foreach (HistoryWaferData ev in result)
                        { 
                            string sql3 = string.Format("SELECT * FROM \"carrier_data\" where \"carrier_data\".\"guid\"='{0}';", ev.CarrierGuid);

                            List<HistoryCarrierData> dbData3 = QueryDataClient.Instance.Service.QueryDBCarrier(sql3);
                            if (dbData3 != null)
                            {
                                for (int i = 0; i < dbData3.Count; i++)
                                {
                                    ev.SourceCarrier = dbData3[i].Rfid.ToString();
                                }
                            }

                            string sql1 = string.Format("SELECT * FROM \"wafer_move_history\" where \"wafer_data_guid\" = '{0}'  order by \"arrive_time\" ASC;", ev.Guid);

                            List<HistoryMoveData> dbData = QueryDataClient.Instance.Service.QueryDBMovement(sql1);
                            if (dbData == null) continue;
                            for (int i = 0; i < dbData.Count; i++)
                            {
                                var station = dbData[i].Station.ToString();

                                if (station.Contains("LP"))
                                {
                                    ev.DestinationPort = station;
                                    var datetime = dbData[i].ArriveTime;
                                    string sql2 = string.Format("SELECT * FROM \"carrier_data\" where \"load_time\" < '{0}' and \"station\" = '{1}' order by \"load_time\" ASC ", datetime, station);
                                    List<HistoryCarrierData> dbData2 = QueryDataClient.Instance.Service.QueryDBCarrier(sql2);
                                    if (dbData2 == null) continue;
                                    for (int j = 0; j < dbData2.Count; j++)
                                    {
                                        if (j != dbData2.Count - 1) continue;
                                        ev.DestinationCarrier = dbData2[j].Rfid.ToString();
                                    }
                                }
                            }
                            WaferData.Add(ev);
                        }

                    MovementData = new ObservableCollection<HistoryMoveData>();
                    InvokePropertyChanged("WaferData");
                    InvokePropertyChanged("MovementData");
                }));
            }
            catch (Exception e)
            {
                LOG.Write(e);
            }
        }
        public void UpdateWaferDataByCj(string CjGuid)
        {
            try
            {
                string sql = string.Format(
                    "SELECT * FROM \"wafer_data\" where \"pj_data_guid\" = '{0}'  order by \"create_slot\" ASC;",
                    CjGuid);

                List<HistoryWaferData> result = QueryDataClient.Instance.Service.QueryDBWafer(sql);

                Application.Current.Dispatcher.BeginInvoke(new Action(() =>
                {
                    WaferData = new ObservableCollection<HistoryWaferData>();

                    if (result != null)
                        foreach (HistoryWaferData ev in result)
                        {
                            string sql3 = string.Format("SELECT * FROM \"carrier_data\" where \"carrier_data\".\"guid\"='{0}';", ev.CarrierGuid);

                            List<HistoryCarrierData> dbData3 = QueryDataClient.Instance.Service.QueryDBCarrier(sql3);
                            if (dbData3 != null)
                            {
                                for (int i = 0; i < dbData3.Count; i++)
                                {
                                    ev.SourceCarrier = dbData3[i].Rfid.ToString();
                                }
                            }

                            string sql1 = string.Format("SELECT * FROM \"wafer_move_history\" where \"wafer_data_guid\" = '{0}'  order by \"arrive_time\" ASC;", ev.Guid);

                            List<HistoryMoveData> dbData = QueryDataClient.Instance.Service.QueryDBMovement(sql1);
                            if (dbData == null) continue;
                            for (int i = 0; i < dbData.Count; i++)
                            {
                                var station = dbData[i].Station.ToString();

                                if (station.Contains("LP"))
                                {
                                    ev.DestinationPort = station;
                                    var datetime = dbData[i].ArriveTime;
                                    string sql2 = string.Format("SELECT * FROM \"carrier_data\" where \"load_time\" < '{0}' and \"station\" = '{1}' order by \"load_time\" ASC ", datetime, station);
                                    List<HistoryCarrierData> dbData2 = QueryDataClient.Instance.Service.QueryDBCarrier(sql2);
                                    if (dbData2 == null) continue;
                                    for (int j = 0; j < dbData2.Count; j++)
                                    {
                                        if (j != dbData2.Count - 1) continue;
                                        ev.DestinationCarrier = dbData2[j].Rfid.ToString();
                                    }
                                }
                            }
                            WaferData.Add(ev);
                        }

                    MovementData = new ObservableCollection<HistoryMoveData>();
                    InvokePropertyChanged("WaferData");
                    InvokePropertyChanged("MovementData");
                }));
            }
            catch (Exception e)
            {
                LOG.Write(e);
            }
        }

        public void ExportMovement(DateTime beginTime)
        {
            try
            {
                List<HistoryMoveData> DataLogList = MovementData.ToList();
                if (DataLogList == null || DataLogList.Count == 0)
                {
                    return;
                }
                Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
                dlg.DefaultExt = ".xlsx"; // Default file extension 
                dlg.Filter = "Excel数据表格文件(*.xls)|*.xls|Excel数据表格文件(*.xlsx)|*.xlsx|所有文件|*.*"; // Filter files by extension 
                Nullable<bool> result = dlg.ShowDialog();// Show open file dialog box
                if (result == true) // Process open file dialog box results
                {
                    System.Data.DataSet ds = new System.Data.DataSet();
                    ds.Tables.Add(new System.Data.DataTable("系统运行日志"));
                    ds.Tables[0].Columns.Add("Time In");
                    ds.Tables[0].Columns.Add("Station");
                    ds.Tables[0].Columns.Add("Slot");
                    foreach (var item in DataLogList)
                    {
                        var row = ds.Tables[0].NewRow();
                        row[0] = item.ArriveTime;
                        row[1] = item.Station;
                        row[2] = item.Slot;
                        ds.Tables[0].Rows.Add(row);
                    }

                    if (!ExcelHelper1.ExportToExcel(dlg.FileName, ds, out string reason))
                    {
                        MessageBox.Show($"Export failed, {reason}", "Export", MessageBoxButton.OK, MessageBoxImage.Warning);
                        return;
                    }

                    MessageBox.Show($"Export succeed, file save as {dlg.FileName}", "Export", MessageBoxButton.OK, MessageBoxImage.Information);
                }
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
                MessageBox.Show("导出系统日志发生错误", "导出失败", MessageBoxButton.OK, MessageBoxImage.Warning);
            }

            //ArrayList excelContentList = new ArrayList();
            //ArrayList head = new ArrayList();
            //ArrayList[] data = new ArrayList[DataLogList.Count];
            //head.Add("Time In");
            //head.Add("Station");
            //head.Add("Slot");

            //excelContentList.Add(head);
            //for (int i = 0; i < DataLogList.Count; i++)
            //{
            //    if (data[i] == null)
            //        data[i] = new ArrayList();
            //    data[i].Add(DataLogList[i].ArriveTime ?? "");
            //    data[i].Add(DataLogList[i].Station ?? "");
            //    data[i].Add(DataLogList[i].Slot);

            //}
            //for (int i = 0; i < data.Length; i++)
            //    excelContentList.Add(data[i]);

            //ExcelExporter.exportToExcel(excelContentList);
        }
        public void ExportProcessData(DateTime beginTime)
        {
            try
            {
                List<HistoryOCRData> DataLogList = OcrCodeData.ToList();
                if (DataLogList == null || DataLogList.Count == 0)
                {
                    return;
                }
                Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
                dlg.DefaultExt = ".xlsx"; // Default file extension 
                dlg.Filter = "Excel数据表格文件(*.xls)|*.xls|Excel数据表格文件(*.xlsx)|*.xlsx|所有文件|*.*"; // Filter files by extension 
                Nullable<bool> result = dlg.ShowDialog();// Show open file dialog box
                if (result == true) // Process open file dialog box results
                {
                    System.Data.DataSet ds = new System.Data.DataSet();
                    ds.Tables.Add(new System.Data.DataTable("系统运行日志"));
                    ds.Tables[0].Columns.Add("WaferId");
                    ds.Tables[0].Columns.Add("ReadTime");
                    ds.Tables[0].Columns.Add("SourceCarrier");
                    ds.Tables[0].Columns.Add("SourceLp");
                    ds.Tables[0].Columns.Add("SourceSlot");
                    ds.Tables[0].Columns.Add("OcrNo");
                    ds.Tables[0].Columns.Add("OcrJob");
                    ds.Tables[0].Columns.Add("OcrScore");
                    ds.Tables[0].Columns.Add("Result");
                    ds.Tables[0].Columns.Add("TCode");
                    foreach (var item in DataLogList)
                    {
                        var row = ds.Tables[0].NewRow();
                        row[0] = item.wafer_id;
                        row[1] = item.read_time;
                        row[2] = item.source_carrier;
                        row[3] = item.source_lp;
                        row[4] = item.source_slot;
                        row[5] = item.ocr_no;
                        row[6] = item.ocr_job;
                        row[7] = item.ocr_score;
                        row[8] = item.read_result;
                        row[9] = item.lasermark;
                        ds.Tables[0].Rows.Add(row);
                    }

                    if (!ExcelHelper1.ExportToExcel(dlg.FileName, ds, out string reason))
                    {
                        MessageBox.Show($"Export failed, {reason}", "Export", MessageBoxButton.OK, MessageBoxImage.Warning);
                        return;
                    }

                    MessageBox.Show($"Export succeed, file save as {dlg.FileName}", "Export", MessageBoxButton.OK, MessageBoxImage.Information);
                }
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
                MessageBox.Show("导出系统日志发生错误", "导出失败", MessageBoxButton.OK, MessageBoxImage.Warning);
            }
            //List<HistoryOCRData> DataLogList = OcrCodeData.ToList();
            //if (DataLogList == null || DataLogList.Count == 0)
            //{
            //    return;
            //}
            //ArrayList excelContentList = new ArrayList();
            //ArrayList head = new ArrayList();
            //ArrayList[] data = new ArrayList[DataLogList.Count];
            //head.Add("WaferId");
            //head.Add("ReadTime");
            //head.Add("SourceCarrier");
            //head.Add("SourceLp");
            //head.Add("SourceSlot");
            //head.Add("OcrNo");
            //head.Add("OcrJob");
            //head.Add("OcrScore");
            //head.Add("Result");
            //head.Add("TCode");

            //excelContentList.Add(head);
            //for (int i = 0; i < DataLogList.Count; i++)
            //{
            //    if (data[i] == null)
            //        data[i] = new ArrayList();
            //    data[i].Add(DataLogList[i].wafer_id ?? "");
            //    data[i].Add(DataLogList[i].read_time ?? "");
            //    data[i].Add(DataLogList[i].source_carrier ?? "");
            //    data[i].Add(DataLogList[i].source_lp ?? "");
            //    data[i].Add(DataLogList[i].source_slot ?? "");
            //    data[i].Add(DataLogList[i].ocr_no ?? "");
            //    data[i].Add(DataLogList[i].ocr_job ?? "");
            //    data[i].Add(DataLogList[i].ocr_score ?? "");
            //    data[i].Add(DataLogList[i].read_result ?? "");
            //    data[i].Add(DataLogList[i].lasermark ?? "");
            //}
            //for (int i = 0; i < data.Length; i++)
            //    excelContentList.Add(data[i]);

            //ExcelExporter.exportToExcel(excelContentList);
        }
        public void ExportCarrier(DateTime beginTime)
        {
            //List<HistoryCarrierData> DataLogList = CarrierData.ToList();
            //if (DataLogList == null || DataLogList.Count == 0)
            //{
            //    return;
            //}

            //Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
            //dlg.DefaultExt = ".xls"; // Default file extension 
            //dlg.Filter = "Data Excel File|*.xls"; // Filter files by extension 
            //dlg.FileName = string.Format("Carrier{0}", beginTime.ToString("yyyyMMdd"));
            //Nullable<bool> result = dlg.ShowDialog();// Show open file dialog box
            //if (result != true) // Process open file dialog box results
            //    return;


            //try
            //{
            //    if (File.Exists(dlg.FileName))
            //    {
            //        File.Delete(dlg.FileName);
            //    }
            //}
            //catch (Exception)
            //{
            //    MessageBox.Show("File is occupied by other application, delete the file first to save.", "Error",
            //        MessageBoxButton.OK);
            //    return;
            //}


            //ArrayList excelContentList = new ArrayList();
            //ArrayList head = new ArrayList();
            //ArrayList[] data = new ArrayList[DataLogList.Count];

            //head.Add("LoadTime");
            //head.Add("Carrier ID");
            //head.Add("Station");
            //head.Add("UnloadTime");
            //head.Add("Lot ID");
            //head.Add("Product Category");
            //excelContentList.Add(head);

            //for (int i = 0; i < DataLogList.Count; i++)
            //{
            //    if (data[i] == null)
            //        data[i] = new ArrayList();
            //    data[i].Add(DataLogList[i].LoadTime ?? "");
            //    data[i].Add(DataLogList[i].Rfid ?? "");
            //    data[i].Add(DataLogList[i].Station ?? "");
            //    data[i].Add(DataLogList[i].UnloadTime ?? "");
            //    data[i].Add(DataLogList[i].LotId ?? "");
            //    data[i].Add(DataLogList[i].ProductCategory ?? "");

            //}
            //for (int i = 0; i < data.Length; i++)
            //    excelContentList.Add(data[i]);

            //ExcelExporter.exportToExcel(excelContentList);
            try
            {
                List<HistoryCarrierData> DataLogList = CarrierData.ToList();
                if (DataLogList == null || DataLogList.Count == 0)
                {
                    return;
                }

                Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
                dlg.DefaultExt = ".xlsx"; // Default file extension 
                dlg.Filter = "Excel数据表格文件(*.xls)|*.xls|Excel数据表格文件(*.xlsx)|*.xlsx|所有文件|*.*"; // Filter files by extension 
                Nullable<bool> result = dlg.ShowDialog();// Show open file dialog box
                if (result == true) // Process open file dialog box results
                {
                    System.Data.DataSet ds = new System.Data.DataSet();
                    ds.Tables.Add(new System.Data.DataTable("系统运行日志"));
                    ds.Tables[0].Columns.Add("LoadTime");
                    ds.Tables[0].Columns.Add("Carrier ID");
                    ds.Tables[0].Columns.Add("Station");
                    ds.Tables[0].Columns.Add("UnloadTime");
                    ds.Tables[0].Columns.Add("Lot ID");
                    ds.Tables[0].Columns.Add("Product Category");
                    foreach (var item in DataLogList)
                    {
                        var row = ds.Tables[0].NewRow();
                        row[0] = item.LoadTime;
                        row[1] = item.Rfid;
                        row[2] = item.Station;
                        row[3] = item.UnloadTime;
                        row[4] = item.LotId;
                        row[5] = item.ProductCategory;
                        ds.Tables[0].Rows.Add(row);
                    }

                    if (!ExcelHelper1.ExportToExcel(dlg.FileName, ds, out string reason))
                    {
                        MessageBox.Show($"Export failed, {reason}", "Export", MessageBoxButton.OK, MessageBoxImage.Warning);
                        return;
                    }

                    MessageBox.Show($"Export succeed, file save as {dlg.FileName}", "Export", MessageBoxButton.OK, MessageBoxImage.Information);
                }
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
                MessageBox.Show("导出系统日志发生错误", "导出失败", MessageBoxButton.OK, MessageBoxImage.Warning);
            }
        }

        public void ExportProcess(DateTime beginTime)
        {
            //List<HistoryProcessData> DataLogList = ProcessData.ToList();
            //if (DataLogList == null || DataLogList.Count == 0)
            //{
            //    return;
            //}

            //Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
            //dlg.DefaultExt = ".xls"; // Default file extension 
            //dlg.Filter = "Data Excel File|*.xls"; // Filter files by extension 
            //dlg.FileName = string.Format("Process{0}", beginTime.ToString("yyyyMMdd"));
            //Nullable<bool> result = dlg.ShowDialog();// Show open file dialog box
            //if (result != true) // Process open file dialog box results
            //    return;

            //try
            //{
            //    if (File.Exists(dlg.FileName))
            //    {
            //        File.Delete(dlg.FileName);
            //    }
            //}
            //catch (Exception)
            //{
            //    MessageBox.Show("File is occupied by other application, delete the file first to save.", "Error",
            //        MessageBoxButton.OK);
            //    return;
            //}

            //ArrayList excelContentList = new ArrayList();
            //ArrayList head = new ArrayList();
            //ArrayList[] data = new ArrayList[DataLogList.Count];

            //head.Add("Recipe");
            //head.Add("Start Time");
            //head.Add("End Time");
            //head.Add("Result");
            //excelContentList.Add(head);

            //for (int i = 0; i < DataLogList.Count; i++)
            //{
            //    if (data[i] == null)
            //        data[i] = new ArrayList();
            //    data[i].Add(DataLogList[i].RecipeName ?? "");
            //    data[i].Add(DataLogList[i].StartTime ?? "");
            //    data[i].Add(DataLogList[i].EndTime ?? "");
            //    data[i].Add(DataLogList[i].Result ?? "");

            //}
            //for (int i = 0; i < data.Length; i++)
            //    excelContentList.Add(data[i]);

            //ExcelExporter.exportToExcel(excelContentList);
            try
            {
                List<HistoryProcessData> DataLogList = ProcessData.ToList();
                if (DataLogList == null || DataLogList.Count == 0)
                {
                    return;
                }

                Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
                dlg.DefaultExt = ".xlsx"; // Default file extension 
                dlg.Filter = "Excel数据表格文件(*.xls)|*.xls|Excel数据表格文件(*.xlsx)|*.xlsx|所有文件|*.*"; // Filter files by extension 
                Nullable<bool> result = dlg.ShowDialog();// Show open file dialog box
                if (result == true) // Process open file dialog box results
                {
                    System.Data.DataSet ds = new System.Data.DataSet();
                    ds.Tables.Add(new System.Data.DataTable("系统运行日志"));
                    ds.Tables[0].Columns.Add("Recipe");
                    ds.Tables[0].Columns.Add("Start Time");
                    ds.Tables[0].Columns.Add("End Time");
                    ds.Tables[0].Columns.Add("Result");
                    foreach (var item in DataLogList)
                    {
                        var row = ds.Tables[0].NewRow();
                        row[0] = item.RecipeName;
                        row[1] = item.StartTime;
                        row[2] = item.EndTime;
                        row[3] = item.Result;
                        ds.Tables[0].Rows.Add(row);
                    }

                    if (!ExcelHelper1.ExportToExcel(dlg.FileName, ds, out string reason))
                    {
                        MessageBox.Show($"Export failed, {reason}", "Export", MessageBoxButton.OK, MessageBoxImage.Warning);
                        return;
                    }

                    MessageBox.Show($"Export succeed, file save as {dlg.FileName}", "Export", MessageBoxButton.OK, MessageBoxImage.Information);
                }
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
                MessageBox.Show("导出系统日志发生错误", "导出失败", MessageBoxButton.OK, MessageBoxImage.Warning);
            }
        }

        public void ExportWafer(DateTime beginTime)
        {
            //List<HistoryWaferData> DataLogList = WaferData.ToList();
            //if (DataLogList == null || DataLogList.Count == 0)
            //{
            //    return;
            //}

            //Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
            //dlg.DefaultExt = ".xls"; // Default file extension 
            //dlg.Filter = "Data Excel File|*.xls"; // Filter files by extension 
            //dlg.FileName = string.Format("Wafer{0}", beginTime.ToString("yyyyMMdd"));
            //Nullable<bool> result = dlg.ShowDialog();// Show open file dialog box
            //if (result != true) // Process open file dialog box results
            //    return;

            //try
            //{
            //    if (File.Exists(dlg.FileName))
            //    {
            //        File.Delete(dlg.FileName);
            //    }
            //}
            //catch (Exception)
            //{
            //    MessageBox.Show("File is occupied by other application, delete the file first to save.", "Error",
            //        MessageBoxButton.OK);
            //    return;
            //}


            //ArrayList excelContentList = new ArrayList();
            //ArrayList head = new ArrayList();
            //ArrayList[] data = new ArrayList[DataLogList.Count];

            //head.Add("Station");
            //head.Add("Slot");
            //head.Add("Wafer ID");
            //head.Add("Create Time");
            //head.Add("Delete Time");
            //head.Add("LaserMarker1(LaserMarker)");
            //head.Add("LaserMarker2(T7Code)");
            //excelContentList.Add(head);

            //for (int i = 0; i < DataLogList.Count; i++)
            //{
            //    if (data[i] == null)
            //        data[i] = new ArrayList();
            //    data[i].Add(DataLogList[i].Station ?? "");
            //    data[i].Add(DataLogList[i].Slot ?? "");
            //    data[i].Add(DataLogList[i].WaferId ?? "");
            //    data[i].Add(DataLogList[i].CreateTime ?? "");
            //    data[i].Add(DataLogList[i].DeleteTime ?? "");
            //    data[i].Add(DataLogList[i].LaserMarker ?? "");
            //    data[i].Add(DataLogList[i].T7Code);

            //}
            //for (int i = 0; i < data.Length; i++)
            //    excelContentList.Add(data[i]);

            //ExcelExporter.exportToExcel(excelContentList);
            try
            {
                List<HistoryWaferData> DataLogList = WaferData.ToList();
                if (DataLogList == null || DataLogList.Count == 0)
                {
                    return;
                }

                Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
                dlg.DefaultExt = ".xlsx"; // Default file extension 
                dlg.Filter = "Excel数据表格文件(*.xls)|*.xls|Excel数据表格文件(*.xlsx)|*.xlsx|所有文件|*.*"; // Filter files by extension 
                Nullable<bool> result = dlg.ShowDialog();// Show open file dialog box
                if (result == true) // Process open file dialog box results
                {
                    System.Data.DataSet ds = new System.Data.DataSet();
                    ds.Tables.Add(new System.Data.DataTable("系统运行日志"));
                    ds.Tables[0].Columns.Add("Station");
                    ds.Tables[0].Columns.Add("Slot");
                    ds.Tables[0].Columns.Add("Lot");
                    ds.Tables[0].Columns.Add("Wafer ID");
                    ds.Tables[0].Columns.Add("Create Time");
                    ds.Tables[0].Columns.Add("Delete Time");
                    ds.Tables[0].Columns.Add("LaserMarker1(LaserMarker)");
                    ds.Tables[0].Columns.Add("LaserMarker2(T7Code)");
                    foreach (var item in DataLogList)
                    {
                        var row = ds.Tables[0].NewRow();
                        row[0] = item.Station;
                        row[1] = item.Slot;
                        row[2] = item.LotId;
                        row[3] = item.WaferId;
                        row[4] = item.CreateTime;
                        row[5] = item.DeleteTime;
                        row[6] = item.LaserMarker;
                        row[7] = item.T7Code;
                        ds.Tables[0].Rows.Add(row);
                    }

                    if (!ExcelHelper1.ExportToExcel(dlg.FileName, ds, out string reason))
                    {
                        MessageBox.Show($"Export failed, {reason}", "Export", MessageBoxButton.OK, MessageBoxImage.Warning);
                        return;
                    }

                    MessageBox.Show($"Export succeed, file save as {dlg.FileName}", "Export", MessageBoxButton.OK, MessageBoxImage.Information);
                }
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
                MessageBox.Show("导出系统日志发生错误", "导出失败", MessageBoxButton.OK, MessageBoxImage.Warning);
            }
        }


    }


   public class ExcelExporter
    {
        private const int OLDOFFICEVESION = -4143;
        private const int NEWOFFICEVESION = 56;

        public static void exportToExcel(ArrayList dataArray)
        {
            try
            {
                ////保存excel文件的格式
                //int FormatNum;
                ////excel版本号
                //string Version;
                ////启动应用
                //Excel.Application xlApp = new Excel.Application();

                ////System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                //Excel.Workbooks workbooks = xlApp.Workbooks;
                ////创建文件
                //Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                ////创建sheet
                //Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
                ////获取你使用的excel 的版本号
                //Version = xlApp.Version;
                ////使用Excel 97-2003
                //if (Convert.ToDouble(Version) < 12)
                //{
                //    FormatNum = OLDOFFICEVESION;
                //}
                ////使用 excel 2007或更新
                //else
                //{
                //    FormatNum = NEWOFFICEVESION;
                //}
                ////添加输出excel表格的表头信息信息
                ////注意这里的excel对应的单元格第一个位置为[1,1]，而不是我们平时定义[0,0]
                //ArrayList head = (ArrayList)dataArray[0];
                //for (int i = 0; i < head.Count; i++)
                //{
                //    worksheet.Cells[1, i + 1] = head[i].ToString();
                //}
                ////添加输出excel表格的内容信息
                //for (int rowIndex = 1; rowIndex < dataArray.Count; rowIndex++)
                //{
                //    ArrayList rowArr = (ArrayList)dataArray[rowIndex];
                //    for (int cellIndex = 0; cellIndex < rowArr.Count; cellIndex++)
                //    {
                //        Excel.Range vsto_cell = worksheet.Cells[rowIndex + 1, cellIndex + 1];
                //        vsto_cell.NumberFormat = "@";

                //        worksheet.Cells[rowIndex + 1, cellIndex + 1] = rowArr[cellIndex].ToString();




                //    }
                //}

                //int r = dataArray.Count > 0 ? dataArray.Count : 1;

                //Excel.Range range = worksheet.get_Range("A1", $"G{r}");
                //range.EntireColumn.AutoFit();
                //range.AutoFormat();
                ////range.Formula = "@";

                ////删除已存在的excel文件，否则会无法保存创建的excel文件
                //if (File.Exists(filePath))
                //{
                //    try
                //    {
                //        File.Delete(filePath);
                //    }
                //    catch (IOException e)
                //    {
                //        Console.WriteLine(e.Message);
                //    }
                //}
                ////保存，这里必须指定FormatNum文件的格式，否则无法打开创建的excel文件
                //workbook.SaveAs(filePath, FormatNum);
                ////显示创建的excel文件
                ////xlApp.Visible = true;

                //xlApp.Workbooks.Close();
                //xlApp.Quit();
                //xlApp = null;
                Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
                dlg.DefaultExt = ".xls"; // Default file extension 
                dlg.Filter = "Excel数据表格文件(*.xls)|*.xls|Excel数据表格文件(*.xlsx)|*.xlsx|所有文件|*.*"; // Filter files by extension 
                Nullable<bool> result = dlg.ShowDialog();// Show open file dialog box
                if (result == true) // Process open file dialog box results
                {
                    System.Data.DataSet ds = new System.Data.DataSet();
                    ds.Tables.Add(new System.Data.DataTable("系统运行日志"));

                    ArrayList head = (ArrayList)dataArray[0];
                    for (int i = 0; i < head.Count; i++)
                    {
                        ds.Tables[0].Columns.Add(head[i].ToString());
                    }
                    //添加输出excel表格的内容信息
                    for (int rowIndex = 1; rowIndex < dataArray.Count; rowIndex++)
                    {
                        var row = ds.Tables[0].NewRow();
                        ArrayList rowArr = (ArrayList)dataArray[rowIndex];
                        for (int cellIndex = 0; cellIndex < rowArr.Count; cellIndex++)
                        {
                            row[cellIndex] = rowArr[cellIndex].ToString();
                            

                        }
                        ds.Tables[0].Rows.Add(row);
                    }

                    ds.WriteXml(dlg.FileName);
                }
            }
            catch (Exception e)
            {
                LOG.Write(e);
            }



        }
        public void Export()
        {
            try
            {
                //List<StatsStatisticsData> DataLogList = HistoryStatsStatisticsData.ToList();
                //if (DataLogList == null || DataLogList.Count == 0)
                //{
                //    return;
                //}
                //Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
                //dlg.DefaultExt = ".xlsx"; // Default file extension 
                //dlg.Filter = "Excel数据表格文件(*.xlsx)|*.xlsx"; // Filter files by extension 
                //Nullable<bool> result = dlg.ShowDialog();// Show open file dialog box
                //if (result == true) // Process open file dialog box results
                //{
                //    System.Data.DataSet ds = new System.Data.DataSet();
                //    ds.Tables.Add(new System.Data.DataTable("系统运行日志"));
                //    ds.Tables[0].Columns.Add("Time In");
                //    ds.Tables[0].Columns.Add("Executing");
                //    ds.Tables[0].Columns.Add("Idle");
                //    ds.Tables[0].Columns.Add("Pause");
                //    ds.Tables[0].Columns.Add("Ready");
                //    ds.Tables[0].Columns.Add("Setup");
                //    ds.Tables[0].Columns.Add("Unknown");
                //    foreach (var item in DataLogList)
                //    {
                //        var row = ds.Tables[0].NewRow();
                //        row[0] = item.Date;
                //        row[1] = item.Executing;
                //        row[2] = item.Idle;
                //        row[3] = item.Pause;
                //        row[4] = item.Ready;
                //        row[5] = item.Setup;
                //        row[6] = item.Unknown;
                //        ds.Tables[0].Rows.Add(row);
                //    }

                //    if (!ExcelHelper.ExportToExcel(dlg.FileName, ds, out string reason))
                //    {
                //        MessageBox.Show($"Export failed, {reason}", "Export", MessageBoxButton.OK, MessageBoxImage.Warning);
                //        return;
                //    }

                //    MessageBox.Show($"Export succeed, file save as {dlg.FileName}", "Export", MessageBoxButton.OK, MessageBoxImage.Information);
                //}
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
                MessageBox.Show("导出系统日志发生错误", "导出失败", MessageBoxButton.OK, MessageBoxImage.Warning);
            }
        }
        //测试函数
        public void test()
        {
            ArrayList perant = new ArrayList();
            ArrayList head = new ArrayList();
            head.Add("头部");
            perant.Add(head);
            ArrayList data = new ArrayList();
            data.Add("data");
            perant.Add(data);

            //exportToExcel(perant, @"D:\testExcell.xlsx");
        }
    }

    public class ExcelHelper1
    {
        public static bool ExportToExcel(string filepath, DataSet ds, out string reason, bool createNewFile = true)
        {
            reason = string.Empty;

            try
            {
                SpreadsheetDocument spreadsheetDocument;
                WorkbookPart workbookpart;
                WorksheetPart worksheetPart;
                Sheets sheets;
                if (createNewFile)
                {
                    spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
                    workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                    sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                }
                else
                {
                    spreadsheetDocument = SpreadsheetDocument.Open(filepath, true);
                    workbookpart = spreadsheetDocument.WorkbookPart;
                    sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
                }
                worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new SheetData());

                uint sheetID = 1;
                if (sheets.Descendants<Sheet>().Count() > 0)
                {
                    sheetID = sheets.Descendants<Sheet>().Select(S => S.SheetId.Value).Max() + 1;
                }

                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    Sheet sheet = new Sheet()
                    {
                        Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                        SheetId = sheetID,
                        Name = ds.Tables[i].TableName,
                    };

                    sheets.Append(sheet);

                    var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                    DocumentFormat.OpenXml.Spreadsheet.Row rowCaption = new DocumentFormat.OpenXml.Spreadsheet.Row();
                    for (int col = 0; col < ds.Tables[i].Columns.Count; col++)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Cell titleCell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        titleCell.CellValue = new CellValue(ds.Tables[i].Columns[col].Caption);
                        titleCell.DataType = new EnumValue<CellValues>(CellValues.String);
                        rowCaption.Append(titleCell);
                    }
                    sheetData.Append(rowCaption);

                    for (int row = 0; row < ds.Tables[i].Rows.Count; row++)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Row rowData = new DocumentFormat.OpenXml.Spreadsheet.Row();
                        for (int col = 0; col < ds.Tables[i].Columns.Count; col++)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Cell dataCell = new DocumentFormat.OpenXml.Spreadsheet.Cell();

                            var data = ds.Tables[i].Rows[row][col];

                            if (data is DateTime time)
                            {
                                dataCell.CellValue = new CellValue(time);
                                dataCell.DataType = new EnumValue<CellValues>(CellValues.String);
                            }
                            else if (data is double value)
                            {
                                dataCell.CellValue = new CellValue(data.ToString());
                                dataCell.DataType = new EnumValue<CellValues>(CellValues.Number);
                            }
                            else
                            {
                                dataCell.CellValue = new CellValue(data.ToString());
                                dataCell.DataType = new EnumValue<CellValues>(CellValues.String);
                            }

                            rowData.Append(dataCell);
                        }
                        sheetData.Append(rowData);
                    }
                }
                workbookpart.Workbook.Save();
                spreadsheetDocument.Close();
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
                reason = ex.Message;
                return false;
            }

            return true;

        }

    }
}