using System;
using System.Collections.Generic;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Data;
using System.Web;

namespace Eson.Utils.ExcelHelper
{
    /// <summary>
    /// 导出Excel帮助类(文件模板形式，特殊样式时，可自己设置模板)
    /// </summary>
    public class ExcelFileExport
    {

        private static ExcelFileExport _instance;
        /// <summary>
        /// 实例化导出Excel帮助类
        /// </summary>
        /// <returns></returns>
        public static ExcelFileExport GetInstance()
        {
            if (_instance == null)
                _instance = new ExcelFileExport();
            return _instance;
        }

        public IWorkbook workBook;
        public ISheet workSheet;

        #region Excel文件基本操作
        /// <summary>
        /// 初始化工作簿(Book)
        /// </summary>
        /// <param name="strSaveName">文件名称，也作为工作簿名称</param>
        /// <param name="is07Excel">是否导出2007以上版本的Excel</param>
        public void InitializeWorkbook(string strSaveName, bool is07Excel)
        {
            FileStream file = new FileStream(strSaveName, FileMode.Open, FileAccess.Read);
            workBook = is07Excel ? (new XSSFWorkbook(file) as IWorkbook) : (new HSSFWorkbook(file) as IWorkbook);
            GetSheetByIndex(null);
        }

        /// <summary>
        /// 按序号打开工作表(Sheet)
        /// </summary>
        /// <param name="intSheetIndex">工作表序号，为空时默认第一个工作表</param>
        public void GetSheetByIndex(int? intSheetIndex)
        {
            if (intSheetIndex == null)
                workSheet = workBook.GetSheetAt(0);
            else
                workSheet = workBook.GetSheetAt(Convert.ToInt32(intSheetIndex));
        }

        /// <summary>
        /// 按名称打开工作表(Sheet)
        /// </summary>
        /// <param name="strSheetName">工作表名称</param>
        public void GetSheetByName(string strSheetName)
        {
            workSheet = workBook.GetSheet(strSheetName);
        }

        /// <summary>
        /// 初始化工作簿，自动打开第intSheetIndex个Sheet（intSheetIndex默认为0）
        /// </summary>
        /// <param name="strExcelFilePath">原Excel文件路径</param>
        /// <param name="intSheetIndex">第几个Sheet，默认为0</param>
        /// <param name="is07Excel">是否导出2007以上版本的Excel</param>
        public void GetSheetFromExcel(string strExcelFilePath, int? intSheetIndex, bool is07Excel)
        {
            InitializeWorkbook(strExcelFilePath, is07Excel);
            GetSheetByIndex(intSheetIndex);
        }

        /// <summary>
        /// 将工作簿写入文件
        /// 如果目标文件已经存在，先删除
        /// </summary>
        /// <param name="strSaveFilePath">要保存的文件地址</param>
        public void WriteToFile(string strSaveFilePath)
        {
            FileInfo fi = new FileInfo(strSaveFilePath);
            if (fi.Exists)
                fi.Delete();
            FileStream fs = new FileStream(strSaveFilePath, FileMode.Create);
            workBook.Write(fs);
            fs.Close();
        }

        ///// <summary>
        ///// 导出Excel文件
        ///// </summary>
        ///// <param name="strSaveFilePath">文件路径</param>
        ///// <param name="strSaveName">保存名称</param>
        ///// <param name="is07Excel">是否导出2007以上版本的Excel</param>
        //public void SaveExcel(string strSaveFilePath, string strSaveName, bool is07Excel)
        //{
        //    WriteToFile(strSaveFilePath);
        //    Assistant.ToExcel(strSaveFilePath, strSaveName, is07Excel);
        //}

        #endregion


        #region 写数据到excel文件
        /// <summary>
        /// 导出Excel，传入泛型数据
        /// </summary>
        /// <typeparam name="T">T类型</typeparam>
        /// <param name="lst">泛型</param>
        /// <param name="dicProperties">想要转换的属性名称列表[key值对应Model属性,value值对应Excel显示的列名]</param>
        /// <param name="strSaveName">保存名称</param>
        /// <param name="is07Excel">是否导出2007以上版本的Excel，默认是</param>
        /// <param name="deamoPath">模板路径（默认不传入时使用默认模板，需要特殊样式时使用自己设置好样式的模板，模板统一放在EXCELFILE下，可在其下另建文件夹，注意模板名称去掉后缀再传入）</param>
        //public void WriteListDate<T>(List<T> lst, Dictionary<string, string> dicProperties, string strSaveName, bool is07Excel = true, string deamoPath = null)
        //    where T : class
        //{
        //    #region 初始化工作簿
        //    string strSaveFilePath = System.Web.HttpContext.Current.Server.MapPath("~/EXCELFILE/" + Guid.NewGuid().ToString() + (is07Excel ? ".xlsx" : ".xls"));
        //    File.Copy(System.Web.HttpContext.Current.Server.MapPath(string.Format("~/EXCELFILE/{0}{1}", string.IsNullOrEmpty(deamoPath) ? "demo" : deamoPath, is07Excel ? ".xlsx" : ".xls")), strSaveFilePath);
        //    InitializeWorkbook(strSaveFilePath, is07Excel);

        //    //转换数据
        //    DataTable dtbl = ExtensiveHelper.ConvertListToTable<T>(lst, dicProperties);

        //    //初始化行
        //    int rowIndex = 0;
        //    int intColumn = 0;
        //    IRow rowExcel = workSheet.CreateRow(rowIndex);
        //    ICell cellExcel;

        //    //初始化列
        //    foreach (KeyValuePair<string, string> property in dicProperties)
        //    {
        //        cellExcel = rowExcel.CreateCell(intColumn);
        //        cellExcel.SetCellValue(property.Value);
        //        ++intColumn;
        //    }
        //    #endregion

        //    #region 写入内容
        //    foreach (DataRow rowInfo in dtbl.Rows)
        //    {
        //        ++rowIndex;
        //        rowExcel = workSheet.CreateRow(rowIndex);
        //        int intColumnContent = 0;
        //        foreach (KeyValuePair<string, string> property in dicProperties)
        //        {
        //            cellExcel = rowExcel.CreateCell(intColumnContent);
        //            cellExcel.SetCellValue(rowInfo[property.Value].ToString());
        //            ++intColumnContent;
        //        }
        //    }
        //    #endregion

        //    //模板流导出
        //    SaveExcel(strSaveFilePath, strSaveName, is07Excel);
        //}


        /// <summary>
        /// 输出Excel文件
        /// </summary>
        /// <param name="ms">MemoryStream内存流</param>
        /// <param name="strSaveName">文件保存名称</param>
        /// <param name="is07Excel">是否导出2007以上版本的Excel</param>
        //private void ExportExcel(MemoryStream ms, string strSaveName, bool is07Excel)
        //{
        //    ms.Flush();
        //    ms.Position = 0;
        //    workBook.Write(ms);
        //    workBook = null;
        //    HttpContext current = HttpContext.Current;
        //    current.Response.ContentType = "application/ms-excel";
        //    current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + UrlOper.GetToExcelName(strSaveName) + (is07Excel ? ".xlsx" : ".xls"));
        //    current.Response.BinaryWrite(ms.ToArray());
        //    current.Response.End();
        //    ms.Close();
        //    ms = null;
        //}

        /// <summary>
        /// 导出Excel，传入DataTable内存表
        /// </summary>
        /// <param name="dtbl">DataTabke内存表</param>
        /// <param name="dicProperties">想要转换的属性名称列表[key值对应Model属性,value值对应Excel显示的列名]</param>
        /// <param name="strSaveName">保存名称</param>
        /// <param name="is07Excel">是否导出2007以上版本的Excel，默认是</param>
        /// <param name="deamoPath">模板路径（默认不传入时使用默认模板，需要特殊样式时使用自己设置好样式的模板，模板统一放在EXCELFILE下，可在其下另建文件夹，注意模板名称去掉后缀再传入）</param>
        //public void WriteListDate(DataTable dtbl, Dictionary<string, string> dicProperties, string strSaveName, bool is07Excel = true, string deamoPath = null)
        //{
        //    #region 初始化工作簿
        //    string strSaveFilePath = System.Web.HttpContext.Current.Server.MapPath("~/EXCELFILE/" + Guid.NewGuid().ToString() + (is07Excel ? ".xlsx" : ".xls"));
        //    File.Copy(System.Web.HttpContext.Current.Server.MapPath(string.Format("~/EXCELFILE/{0}{1}", string.IsNullOrEmpty(deamoPath) ? "demo" : deamoPath, is07Excel ? ".xlsx" : ".xls")), strSaveFilePath);
        //    InitializeWorkbook(strSaveFilePath, is07Excel);

        //    //初始化行
        //    int rowIndex = 0;
        //    int intColumn = 0;
        //    IRow rowExcel = workSheet.CreateRow(rowIndex);
        //    ICell cellExcel;

        //    //初始化列
        //    foreach (KeyValuePair<string, string> property in dicProperties)
        //    {
        //        cellExcel = rowExcel.CreateCell(intColumn);
        //        cellExcel.SetCellValue(property.Value);
        //        ++intColumn;
        //    }
        //    #endregion

        //    #region 写入内容
        //    foreach (DataRow rowInfo in dtbl.Rows)
        //    {
        //        ++rowIndex;
        //        rowExcel = workSheet.CreateRow(rowIndex);
        //        int intColumnContent = 0;
        //        foreach (KeyValuePair<string, string> property in dicProperties)
        //        {
        //            cellExcel = rowExcel.CreateCell(intColumnContent);
        //            cellExcel.SetCellValue(GetColumValue(dtbl, rowInfo, property.Key));
        //            ++intColumnContent;
        //        }
        //    }
        //    #endregion

        //    //模板流导出
        //    SaveExcel(strSaveFilePath, strSaveName, is07Excel);
        //}

        /// <summary>
        /// 通过提供的列名和具体行，返回DataTable中具体的某一列的值
        /// </summary>
        /// <param name="dtbl">内存表</param>
        /// <param name="rowInfo">具体行</param>
        /// <param name="columName">列名</param>
        /// <returns>具体某列的值</returns>
        public string GetColumValue(DataTable dtbl, DataRow rowInfo, string columName)
        {
            string clumValue = "";
            foreach (DataColumn clum in dtbl.Columns)
            {
                if (clum.ColumnName.Equals(columName))
                {
                    clumValue = rowInfo[clum].ToString();
                    break;
                }
            }
            return clumValue;
        }

        /// <summary>
        /// 将DataTable导出成Excel文件
        /// </summary>
        /// <param name="lstDtbl">DataTabke内存表泛型</param>
        /// <param name="dicProperties">字典泛型，想要转换的属性名称列表[key值对应Model属性,value值对应Excel显示的列名]</param>
        /// <param name="strSaveName">保存名称</param>
        /// <param name="intLineCount">换行数，表之间的间距,不换行可以默认输入null,也可以输入0</param>
        /// <param name="is07Excel">是否导出2007以上版本的Excel，默认是</param>
        /// <param name="deamoPath">模板路径（默认不传入时使用默认模板，需要特殊样式时使用自己设置好样式的模板，模板统一放在EXCELFILE下，可在其下另建文件夹，注意模板名称去掉后缀再传入）</param>
        //public void WriteListDate(List<DataTable> lstDtbl, List<Dictionary<string, string>> lstDicProperties, string strSaveName, int? intLineCount, bool is07Excel = true, string deamoPath = null)
        //{
        //    #region 初始化工作簿
        //    string strSaveFilePath = System.Web.HttpContext.Current.Server.MapPath("~/EXCELFILE/" + Guid.NewGuid().ToString() + (is07Excel ? ".xlsx" : ".xls"));
        //    File.Copy(System.Web.HttpContext.Current.Server.MapPath(string.Format("~/EXCELFILE/{0}.{1}", string.IsNullOrEmpty(deamoPath) ? "demo" : deamoPath, is07Excel ? ".xlsx" : ".xls")), strSaveFilePath);
        //    InitializeWorkbook(strSaveFilePath, is07Excel);

        //    //初始化行
        //    int rowIndex = 0;
        //    IRow rowExcel;
        //    ICell cellExcel;

        //    #endregion

        //    #region 写内容
        //    for (int i = 0; i < lstDicProperties.Count; i++)
        //    {
        //        //写表头
        //        rowExcel = workSheet.CreateRow(rowIndex);
        //        int intColumn = 0;
        //        foreach (KeyValuePair<string, string> property in lstDicProperties[i])
        //        {
        //            cellExcel = rowExcel.CreateCell(intColumn);
        //            cellExcel.SetCellValue(property.Value);
        //            ++intColumn;
        //        }

        //        //写入内容
        //        foreach (DataRow rowInfo in lstDtbl[i].Rows)
        //        {
        //            ++rowIndex;
        //            rowExcel = workSheet.CreateRow(rowIndex);
        //            int intColumnContent = 0;
        //            foreach (KeyValuePair<string, string> property in lstDicProperties[i])
        //            {
        //                cellExcel = rowExcel.CreateCell(intColumnContent);
        //                cellExcel.SetCellValue(GetColumValue(lstDtbl[i], rowInfo, property.Key));
        //                ++intColumnContent;
        //            }
        //        }

        //        ++rowIndex;

        //        //换行
        //        rowIndex += intLineCount == null ? 0 : Convert.ToInt32(intLineCount);
        //    }
        //    #endregion

        //    //模板流导出
        //    SaveExcel(strSaveFilePath, strSaveName, is07Excel);
        //}
        #endregion
    }
}