using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;

namespace TProduct.WinFrmUI.TPump 
{
    internal class BatImportFeatTestPointHelper
    {
        public static void NewTemplateFile(List<TProduct.Model.WorkBenchMonitorPoint> allMonitorList)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Title = "导入模版";
            dlg.FileName = "测试数据模版.xls";
            dlg.Filter = "excel文件 (*.xls)|*.xls";
            if (dlg.ShowDialog() != DialogResult.OK)
                return;
            var strXlsFile = dlg.FileName;


            #region 初始化表格
            HSSFWorkbook theBook = InitializeBook(strXlsFile);
            if (theBook == null)
            {
                return;
            }

            NPOI.SS.UserModel.ISheet theSheet1 = theBook.GetSheet("Sheet1");
            if (theSheet1 == null)
                return;
            #endregion



            //标题
            NPOI.SS.UserModel.IRow rowTitle = theSheet1.CreateRow(0);
            rowTitle.CreateCell(0).SetCellValue("序号");

            int col = 1;
            foreach (var item in allMonitorList)
            {
                rowTitle.CreateCell(col).SetCellValue(item.Name);
                col++;
            }
            //Force excel to recalculate all the formula while open
            //theSheet1.ForceFormulaRecalculation = true;

            //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
            using (FileStream fs = File.OpenWrite(strXlsFile))
            {
                theBook.Write(fs);   //向打开的这个xls文件中写入mySheet表并保存。
                                     // 
            }
            XtraMessageBox.Show("导出成功！");
        }

        public static void ImportFile(
            List<TProduct.Model.WorkBenchMonitorPoint> allMonitorList,
            Func<List<TProduct.Model.MonitorPointValuePure>,bool> OnImport)
        {
            if (OnImport == null)
                return;
            if (allMonitorList == null)
                return;
            OpenFileDialog selFileDlg = new OpenFileDialog();
            selFileDlg.AddExtension = true;
            selFileDlg.CheckFileExists = true;
            selFileDlg.CheckPathExists = true;
            selFileDlg.Multiselect = false;
            selFileDlg.Filter = "EXCEL文件(*.xls)|*.xls";
            if (selFileDlg.ShowDialog() != DialogResult.OK)
                return;
            var strXlsFile = selFileDlg.FileName;

 



 
            HSSFWorkbook theBookHandler = null;
            try
            {
                using (FileStream file = new FileStream(strXlsFile, FileMode.Open, FileAccess.Read))
                {
                    theBookHandler = new HSSFWorkbook(file);
                }

                if (theBookHandler == null)
                    return;
            }
            catch (Exception ex)
            {
                if (ex.Message.Contains("Initialisation of record"))
                {
                    XtraMessageBox.Show(string.Format("{0}  : {1}",  "请将EXCEL打开后" ,  ex.Message) );
                    return;
                }
      
                XtraMessageBox.Show(  "请勿打开将要导入的EXCEL文件" );
                return;
            }

            NPOI.SS.UserModel.ISheet workSheet = theBookHandler.GetSheet("Sheet1");
            if (workSheet == null)
            {
                for (var i = 0; i < 3; i++)
                {
                    workSheet = theBookHandler.GetSheetAt(i);//从第一个excek SHEET开始读,不一就修改一下

                    if (workSheet != null)
                    {
                        if (!theBookHandler.IsSheetHidden(i))
                        {
                            break;
                        }
                        workSheet = null;
                    }
                }
                if (workSheet == null)
                {
                    MessageBox.Show( "请将数据放在sheet1里" );
                    return;
                }
            }
 

 
            try
            {
                for (int currentLine = 1; currentLine < 100; currentLine++)
                {
                    NPOI.SS.UserModel.IRow currentExcel = workSheet.GetRow(currentLine);
                    if (currentExcel == null)
                    {
                        break;
                    }
                    int col = 1;
                    List<TProduct.Model.MonitorPointValuePure> valueList = new List<Model.MonitorPointValuePure>();
                    foreach (var item in allMonitorList)
                    {
                        var cell_value =  GetCellValue(currentExcel.GetCell(col) );
                        col++;
                        double? value = 0;
                        if (!string.IsNullOrEmpty(cell_value))
                        {
                            value = Convert.ToDouble(cell_value); 
                        }
                        valueList.Add(new Model.MonitorPointValuePure(item.ID, value, DateTime.Now));
                    }
                   if( OnImport.Invoke(valueList) == false)
                   {
                       return;
                   }    
                }
            


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message,  "行号"  + "读取出错" );
                return;
            }
            finally
            {
 
            }
        }
        public static string GetCellValue(NPOI.SS.UserModel.ICell cellRead)
        {
            if (cellRead == null)
                return "";

            switch (cellRead.CellType)
            {
                case NPOI.SS.UserModel.CellType.Numeric:
                    return cellRead.NumericCellValue.ToString();
                case NPOI.SS.UserModel.CellType.String :
                    return cellRead.StringCellValue.Trim();
 
                case NPOI.SS.UserModel.CellType.Formula:
                    return cellRead.NumericCellValue.ToString();
                default:
                    return "";
            }
        }
        //初始化表格:复制Blank.xls
        private static HSSFWorkbook InitializeBook(string strFullName, string TemplateFileName = "Blank")
        {
            string strTempalteFile = string.Format("{0}\\template\\{1}.xls", System.Windows.Forms.Application.StartupPath, TemplateFileName);
            if (!File.Exists(strTempalteFile))
            {
                return null;
            }
            File.Copy(strTempalteFile, strFullName, true);

            HSSFWorkbook theBook = null;
            using (FileStream file = new FileStream(strFullName, FileMode.Open, FileAccess.Read))
            {
                theBook = new HSSFWorkbook(file);
            }

            //create application entry of DocumentSummaryInformation
            NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "EvenTech";
            theBook.DocumentSummaryInformation = dsi;

            //create application entry of SummaryInformation
            NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
            si.Subject = "yiwei";
            theBook.SummaryInformation = si;

            return theBook;
        }

    }
}