using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using Siger.Middlelayer.Common.Extensions;

namespace Siger.Middlelayer.Utility.Helpers
{
    public class CommonExcelHelper
    {
        private ExcelWorksheet Sheet { get; }
        public CommonExcelHelper()
        {

        }
        public CommonExcelHelper(string filePath)
        {
            if (File.Exists(filePath))
            {
                var file = new FileInfo(filePath);
                var excelPackage = new ExcelPackage(file);
                if (excelPackage.Workbook != null && excelPackage.Workbook.Worksheets.Count > 0)
                {
                    Sheet = excelPackage.Workbook.Worksheets.First();
                }
                else
                {
                    throw new Exception("read excel failed.");
                }
            }
        }
        public static void Generate(List<List<string>> res, string filePath)
        {
            var file = new FileInfo(filePath);
            var package = new ExcelPackage(file);
            var worksheet = package.Workbook.Worksheets.Add("sheet1");

            try
            {
                worksheet.Column(1).Width = 10;
                worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                worksheet.Cells.Style.ShrinkToFit = true;

                int i = 0;
                //add content
                foreach (var item in res)
                {
                    int j = 0;
                    foreach (var tmp in item)
                    {
                        worksheet.Cells[i + 1, j + 1].Value = tmp;
                        worksheet.Cells[i + 1, j + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        j++;
                    }
                    i++;
                }

                package.Save();
            }
            finally
            {
                package.Dispose();
            }
        }
        public static List<List<string>> GetValue(string filePath)
        {
            List<List<string>> result = new List<List<string>>();

            if (!File.Exists(filePath))
            {
                throw new Exception("excel file not found.");
            }
            var file = new FileInfo(filePath);
            var excelPackage = new ExcelPackage(file);
            if (excelPackage.Workbook == null || excelPackage.Workbook.Worksheets.Count <= 0)
            {
                throw new Exception("read excel failed.");
            }
            var messages = new List<string>();

            var sheet = excelPackage.Workbook.Worksheets.First();
            var columnCount = sheet.Dimension.End.Column;

            var endRow = sheet.Dimension.Rows;
            for (var row = 1; row <= sheet.Dimension.Rows; row++)
            {
                var tmp = new List<string>();
                for (int j = 1; j <= columnCount; j++)
                {
                    tmp.Add(sheet.Cells[row, j].Value.ToStr());
                }
                result.Add(tmp);
            }
            return result;
        }
    }
}
