using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Diagnostics;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
/// <summary>
/// 功能: 建立讀取 Excel 之背景程序物件。
/// 維護: 
/// [108/04/24][09:46] > 完成基礎代碼撰寫，未測試。
/// [108/04/24][12:04] > 處理 row 為 null 的例外。
/// [108/04/24][12:06] > 取消對 IWorkBook 物件的物件釋放。
/// [108/04/24][12:17] > 新增 Elapsed 方法轉換耗費的時間。
/// [108/04/24][14:35] > 修改讀取完成的回應字串。
/// 建立於: 108/04/24
/// </summary>
namespace ETC_App1
{
    public partial class ETCBackgroundWorker1 : BackgroundWorker
    {
        public string[] AllowExtension { get; set; } = { ".xls", ".xlsx" };

        public string Filename { get; set; }

        public bool HasHead { get; set; } = true;

        public ETCBackgroundWorker1()
        {
            InitializeComponent();
        }

        private double Elapsed(long milliseconds, out string message)
        {
            double elapsed = Convert.ToDouble(milliseconds);

            message = "共耗時";

            if (elapsed >= 1000)
            {
                elapsed = elapsed / 1000;

                if (elapsed >= 60)
                {
                    elapsed = elapsed / 60;

                    if (elapsed >= 60)
                    {
                        elapsed = elapsed / 60;

                        if (elapsed >= 24)
                        {
                            elapsed = elapsed / 24;

                            message = message + "約 " + elapsed + " 天。";

                            return elapsed;
                        }

                        message = message + "約 " + elapsed + " 小時。";

                        return elapsed;
                    }

                    message = message + "約 " + elapsed + " 分鐘。";

                    return elapsed;
                }

                message = message + "約 " + elapsed + " 秒。";

                return elapsed;
            }

            message = message + " " + elapsed + " 毫秒。";

            return elapsed;
        }

        protected override void OnDoWork(DoWorkEventArgs e)
        {
            Dictionary<string, List<Dictionary<string, string>>> data = new Dictionary<string, List<Dictionary<string, string>>>();

            List<string> allowExtension = new List<string>(AllowExtension);

            string filename = (Filename = e.Argument as string);

            string extension = Path.GetExtension(filename);

            if (allowExtension.Contains(extension))
            {
                if (File.Exists(filename))
                {
                    Stopwatch stopwatch = Stopwatch.StartNew();

                    string message = "";

                    ReportProgress(0, "檔案讀取中...");

                    using (FileStream stream = new FileStream(filename, FileMode.Open))
                    {
                        IWorkbook book = null;

                        if (extension == ".xls")
                            book = new HSSFWorkbook(stream);

                        if (extension == ".xlsx")
                            book = new XSSFWorkbook(stream);

                        ReportProgress(0, "建立結構化物件...");

                        for (int i = 0; i < book.NumberOfSheets; i++)
                        {
                            List<Dictionary<string, string>> value = new List<Dictionary<string, string>>();

                            ISheet sheet = book.GetSheetAt(i);

                            for (int j = HasHead ? sheet.FirstRowNum + 1 : sheet.FirstRowNum; j <= sheet.LastRowNum; j++)
                            {
                                Dictionary<string, string> item = new Dictionary<string, string>();

                                IRow row = sheet.GetRow(j);

                                IRow head = HasHead ? sheet.GetRow(sheet.FirstRowNum) : row;

                                for (int m = head.FirstCellNum; m <= head.LastCellNum; m++)
                                {
                                    ICell kcell = head.GetCell(m), vcell = row != null ? row.GetCell(m) : null;

                                    string k = kcell != null && HasHead ? (kcell.CellType == CellType.Numeric ? kcell.NumericCellValue.ToString() : kcell.StringCellValue) : "C" + m;

                                    string v = vcell != null ? (vcell.CellType == CellType.Numeric ? vcell.NumericCellValue.ToString() : vcell.CellType == CellType.String ? vcell.StringCellValue : "") : "";

                                    if (!item.ContainsKey(k))
                                        item.Add(k, v);
                                    else
                                        item[k] = v;
                                }

                                value.Add(item);
                            }

                            data.Add(sheet.SheetName, value);
                        }

                        ReportProgress(0, "建立結構化物建完成，清除暫存記憶體。");

                        stream.Close();

                        stopwatch.Stop();
                    }

                    ReportProgress(Convert.ToInt32(Elapsed(stopwatch.ElapsedMilliseconds, out message) * 100), "檔案讀取，" + message);
                }
                else
                    ReportProgress(2, "檔案不存在於指定位置。");
            }
            else
                ReportProgress(1, "不支援此檔案格式。");

            e.Result = data;
        }
    }
}
