using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;

namespace AutoTurnOver.Utility
{
    public class EPPlusHelper
    {

        /// <summary>
        /// 导入数据到Excel中
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="ds"></param>
        public static bool ImportExcel(Stream file, DataSet ds)
        {
            if (ds == null || ds.Tables.Count == 0)
            {
                return false;
            }
            //在using语句里面我们可以创建多个worksheet，ExcelPackage后面可以传入路径参数
            //命名空间是using OfficeOpenXml
            using (ExcelPackage package = new ExcelPackage(file))
            {
                foreach (DataTable dt in ds.Tables)
                {
                    //创建工作表worksheet
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(dt.TableName);
                    //给单元格赋值有两种方式
                    //worksheet.Cells[1, 1].Value = "单元格的值";直接指定行列数进行赋值
                    //worksheet.Cells["A1"].Value = "单元格的值";直接指定单元格进行赋值
                    worksheet.Cells.Style.Font.Name = "微软雅黑";
                    worksheet.Cells.Style.Font.Size = 12;
                    worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            worksheet.Cells[i + 1, j + 1].Value = dt.Rows[i][j].ToString();
                        }
                    }
                    using (var cell = worksheet.Cells[1, 1, 1, dt.Columns.Count])
                    {
                        //设置样式:首行居中加粗背景色
                        cell.Style.Font.Bold = true; //加粗
                        cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中
                        cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;     //垂直居中
                        cell.Style.Font.Size = 14;
                        cell.Style.Fill.PatternType = ExcelFillStyle.Solid;  //背景颜色
                        cell.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));//设置单元格背景色
                    }
                }
                //保存
                package.Save();
            }
            return true;
        }

        /// <summary>
        /// 读取Excel数据
        /// </summary>
        /// <param name="fileName"></param>
        public static DataSet ReadExcel(Stream file)
        {
            DataSet data_set = new DataSet();
            try
            {
                using (ExcelPackage package = new ExcelPackage(file))
                {
                    var count = package.Workbook.Worksheets.Count;
                    for (int k = 1; k <= count; k++)  //worksheet是从1开始的
                    {
                        DataTable table = new DataTable();
                        var workSheet = package.Workbook.Worksheets[k];
                        table.TableName = workSheet.Name;
                        if (workSheet.Dimension == null) continue;
                        int row = workSheet.Dimension.Rows;
                        int col = workSheet.Dimension.Columns;
                        for (int j = 1; j <= col; j++)
                        {
                            table.Columns.Add(workSheet.Cells[1, j].Value.ToString());
                        }
                        for (int i = 2; i <= row; i++)
                        {
                            DataRow data_row = table.NewRow();
                            for (int j = 1; j <= col; j++)
                            {
                                try
                                {
                                    data_row[workSheet.Cells[1, j].Value.ToString()] = workSheet.Cells[i, j].Value == null ? "" : workSheet.Cells[i, j].Value.ToString();
                                }
                                catch (Exception ex)
                                {

                                    throw;
                                }

                            }
                            table.Rows.Add(data_row);
                        }
                        data_set.Tables.Add(table);
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return data_set;
        }

        public static void ExportExcel(DataTable dt, string fileName, string sheetName = null)
        {
            FileInfo newFile = new FileInfo(fileName);
            if (!newFile.Directory.Exists)
            {
                newFile.Directory.Create();
            }
            if (newFile.Exists)
            {
                newFile.Delete();
                newFile = new FileInfo(fileName);
            }
            using (ExcelPackage pck = new ExcelPackage(newFile))
            {
                ExcelWorksheet worksheet = pck.Workbook.Worksheets.Add("Sheet1");

                worksheet.Cells["A1"].LoadFromDataTable(dt, true);
                worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
                worksheet.Cells.AutoFitColumns();
                pck.Save();
            }
        }
    }
}
