using ExcelExport.Base;
using OfficeOpenXml;
using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Style;
using System;
using System.Data;
using System.Drawing;
using System.IO;

namespace ExcelExport.Export
{
    public class EPPlusExport : IExport
    {
        static EPPlusExport()
        {
            //EPPlus 5.0 以后的版本需要指定 商业证书 或者非商业证书。你需要在代码里指定证书或者降低EPPlus版本
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        }
        public bool Export(DataTable dt, string filenPath)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(dt.Copy());
            return Export(ds, filenPath);
        }

        public bool Export(DataSet ds, string filePath)
        {
            using ExcelPackage package = new ExcelPackage();
            foreach (DataTable item in ds.Tables)
            {
                if (!Export(item, package))
                {
                    return false;
                }
            }
            byte[] bytes = package.GetAsByteArray();
            FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
            fs.Write(bytes, 0, bytes.Length);
            fs.Flush();
            fs.Close();
            return true;
        }

        private bool Export(DataTable dt, ExcelPackage package)
        {
            if (package is null)
                return false;
            ExcelWorksheet sheet = package.Workbook.Worksheets.Add(dt.TableName);

            //设置标题样式
            var caption = sheet.Cells[1, 1];
            caption.Value = dt.TableName;
            caption.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            caption.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            caption.Style.Font.Size = 16;
            caption.Style.Font.Bold = true;
            sheet.Cells[1, 1, 1, dt.Columns.Count].Merge = true;//合并单元格
            sheet.Cells[1, 1, 1, dt.Columns.Count].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);

            //设置列头
            sheet.Row(2).Height = 30;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                var head = sheet.Cells[2, i + 1];
                head.Value = dt.Columns[i];
                head.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
                head.Style.Fill.PatternType = ExcelFillStyle.Solid;
                head.Style.Fill.BackgroundColor.SetColor(Color.Gold);
                head.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                head.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                sheet.Column(i + 1).Width = 20;//设置列宽
            }

            //填充数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sheet.Row(i + 3).Height = 30;//设置行高
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = sheet.Cells[i + 3, j + 1];
                    if (j == 3)
                        cell.Value = Convert.ToDouble(dt.Rows[i][j]);
                    else
                        cell.Value = dt.Rows[i][j];
                    cell.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
                    cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }
            }

            ExcelChart chart = sheet.Drawings.AddChart("chart", eChartType.BarStacked);
            ExcelChartSerie serie = chart.Series.Add(sheet.Cells[3, 4, 9, 4], sheet.Cells[3, 2, 9, 3]);
            serie.Header = "2017年";
            chart.SetPosition(100, 600);//设置位置
            chart.SetSize(800, 400);//设置大小
            chart.Title.Text = "国际数据主要国家年度数据";//设置图表的标题
            //chart.Title.Font.Color = Color.FromArgb(89, 89, 89);//设置标题的颜色
            chart.Title.Font.Size = 15;//标题的大小
            chart.Title.Font.Bold = true;//标题的粗体
            chart.Style = eChartStyle.Style15;//设置图表的样式
            chart.Legend.Border.LineStyle = OfficeOpenXml.Drawing.eLineStyle.Solid;
            chart.Legend.Border.Fill.Color = Color.FromArgb(217, 217, 217);//设置图例的样式
            package.Save();
            //sheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小
            //sheet.Row(1).CustomHeight = true;//自动调整行高
            //sheet.Column(1).Width = 30;//设置列宽
            return true;
        }
    }
}