using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace BoYuan.Framework.Uitility
{
  public static partial class NPOIHelper
    {
        /// <summary>
        /// excel 类型
        /// </summary>
        public enum ExcelType
        {
            xls = 65535,    //HSSFWorkbook （后缀是.xls）  03版excel 最大 256 列 × 65,536 行 - 65535
            xlsx = 1048576, //XSSFWorkbook （后缀是.xlsx） 07版excel 最大 16,384 列 × 1,048,576 行 - 1048576
        }

        #region 直接下载

        /// <summary>
        /// 下载Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="type"></param>
        public static void DownloadExcel(DataTable dt, ExcelType type = ExcelType.xlsx, string fileName = "")
        {
            DownloadExcelStream(RenderToExcel(dt, type), type, fileName);
        }

        /// <summary>
        /// 直接以流的形式下载Excel。简单以字符串类型导出数据
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="type"></param>
        public static void DownloadExcel(IDataReader reader, ExcelType type = ExcelType.xlsx)
        {
            DownloadExcelStream(RenderToExcel(reader, type), type);
        }

        #endregion

        #region 转成数据流

        /// <summary>
        /// DataTable转换成Excel文档流(超出最大行数，自动分页)，默认xlsx格式
        /// </summary>
        /// <param name="sourceTable"></param>
        /// <param name="type">生成excel类型</param>
        /// <returns></returns>
        public static MemoryStream RenderToExcel(DataTable sourceTable, ExcelType type = ExcelType.xlsx)
        {
            int maxRowNum = (int)type;

            IWorkbook workbook;
            if (type == ExcelType.xls)
            {
                workbook = new HSSFWorkbook();
            }
            else //xlsx
            {
                workbook = new XSSFWorkbook();
            }

            MemoryStream ms = new MemoryStream();

            workbook.AddSheet("sheet1", sourceTable, type);
           
            workbook.Write(ms);
            ms.Flush();
            if (type == ExcelType.xls)
                ms.Position = 0;
            //sheet = null;
            // headerRow = null;
            workbook = null;
            return ms;
        }

        /// <summary>
        /// DataReader转换成Excel文档流(超出最大行数，自动分页)。简单以字符串类型导出数据
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="type">生成excel类型</param>
        /// <returns></returns>
        public static MemoryStream RenderToExcel(IDataReader reader, ExcelType type = ExcelType.xlsx)
        {
            MemoryStream ms = new MemoryStream();

            // handling value.
            int sheetNum = 1;
            int rowIndex = 1;
            int tempIndex = 1; //标识

            using (reader)
            {

                int maxRowNum = (int)type;

                IWorkbook workbook = new XSSFWorkbook();
                if (type == ExcelType.xls)
                {
                    workbook = new HSSFWorkbook();
                }

                ISheet sheet = workbook.CreateSheet("sheet" + sheetNum);//

                int cellCount = reader.FieldCount;
                CreateRow(sheet, reader);

                while (reader.Read())
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    for (int i = 0; i < cellCount; i++)
                    {
                        dataRow.CreateCell(i).SetCellValue(reader[i].ToString());
                    }

                    if (rowIndex == maxRowNum)
                    {
                        sheetNum++;
                        sheet = workbook.CreateSheet("sheet" + sheetNum);//
                        CreateRow(sheet, reader);
                        tempIndex = 0;
                    }

                    rowIndex++;
                    tempIndex++;
                }

                workbook.Write(ms);
                ms.Flush();

                reader.Close();

                if (type == ExcelType.xls)
                    ms.Position = 0;

                workbook = null;
                sheet = null;
                //headerRow = null;
            }
            return ms;
        }

        /// <summary>
        /// 将DataTable转成html Table字符串. 可用于直接输出.
        /// </summary>
        /// <param name="dt">传入的DataTable数据, 必须提供标题!</param>
        /// <returns></returns>
        public static string DataTableToHtml(DataTable dt)
        {
            StringBuilder newLine = new StringBuilder();
            newLine.Append("<table cellspacing=\"1\" border=\"1\">");
            //newLine.Append("<tr><td colspan=\String.Empty + dt.Columns.Count + "\" align=\"center\">" + dt.TableName + "</td></tr>");
            newLine.Append("<tr>");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                newLine.AppendFormat("<td>{0}</td>", dt.Columns[i].Caption);
            }
            newLine.Append("</tr>");

            for (int j = 0; j < dt.Rows.Count; j++)
            {
                newLine.Append("<tr>");

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    newLine.AppendFormat("<td>{0}</td>", dt.Rows[j][i]);
                }
                newLine.Append("</tr>");
            }
            newLine.Append("</table>");
            return newLine.ToString();
        }

        /// <summary>
        /// 获取excel数据转成datatable
        /// </summary>
        /// <param name="filePath">excel地址</param>
        /// <param name="sheetName">要上传数据excel表</param>
        /// <param name="errorInfo">错误信息，如果为空则无错误</param>
        /// <param name="headColumnNum">显示列名称行号，如果没有列名行请设置值为0(从1开始)</param>
        /// <param name="dataRowColumnNum">数据行号(从0开始)</param>
        /// <param name="firstCellNum">开始列数(从0开始)</param>
        /// <param name="dataTableNames">列名集合(注意集合数量要一致，否则异常)</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string filePath, string sheetName, out string errorInfo, int headColumnNum = 1, int dataRowColumnNum = 1, int firstCellNum = 0, string[] dataTableNames = null)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();

            try
            {
                IWorkbook workbook;
                var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                if (filePath.IndexOf(".xlsx") > 0)
                    workbook = new XSSFWorkbook(fs);
                else
                {
                    if (filePath.IndexOf(".xls") <= 0)
                    {
                        errorInfo = "非excel格式";
                        return null;
                    }
                    workbook = new HSSFWorkbook(fs);
                }

                if (!string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                }
                if (sheet == null)//默认第一个表
                {
                    sheet = workbook.GetSheetAt(0);
                }

                if (sheet == null)
                {
                    errorInfo = "找不到sheet";
                    return null;
                }

                IRow firstRow = sheet.GetRow(0);
                int cellCount = firstRow.LastCellNum;

                if (headColumnNum > 0)//有列名称列
                {
                    for (int i = firstCellNum; i < cellCount; ++i)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue = cell.StringCellValue;
                            if (cellValue != null)
                            {
                                DataColumn column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }
                }
                else
                {
                    if (dataTableNames == null || dataTableNames.Length == 0)//默认列名
                    {
                        for (int i = firstCellNum; i < cellCount; ++i)
                        {
                            string cellValue = "F" + (i + 1);

                            DataColumn column = new DataColumn(cellValue);
                            data.Columns.Add(column);
                        }
                    }
                    else //自定义列名
                    {
                        int tempInt = 0;
                        for (int i = firstCellNum; i < cellCount; ++i)
                        {
                            string cellValue = dataTableNames[tempInt++];

                            DataColumn column = new DataColumn(cellValue);
                            data.Columns.Add(column);
                        }
                    }
                }

                int rowCount = sheet.LastRowNum;
                for (int i = dataRowColumnNum; i <= rowCount; ++i)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue;

                    DataRow dataRow = data.NewRow();
                    for (int j = firstCellNum; j < cellCount; ++j)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell != null)
                        {
                            switch (cell.CellType)
                            {
                                case CellType.Numeric://NPOI中的数字和日期都是Numeric类型的，这里判断是否为日期类型
                                    if (HSSFDateUtil.IsCellDateFormatted(cell)) //日期类型
                                    {
                                        dataRow[j] = cell.DateCellValue;
                                    }
                                    else//其他是数字类型
                                    {
                                        dataRow[j] = cell.NumericCellValue;
                                    }
                                    break;
                                case CellType.Blank://空数据类型
                                    dataRow[j] = string.Empty;
                                    break;
                                case CellType.Formula://公式类型
                                    HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
                                    dataRow[j] = eva.Evaluate(cell).StringValue;
                                    break;
                                default://其他都按字符串类型处理
                                    dataRow[j] = cell.StringCellValue;
                                    break;
                            }
                        }
                    }
                    data.Rows.Add(dataRow);
                }

                errorInfo = string.Empty;
                return data;
            }
            catch (Exception ex)
            {
                errorInfo = ex.Message;
                return null;
            }
        }
        #endregion

        #region 公有方法

        /// <summary>
        /// 添加sheet
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheetName"></param>
        /// <param name="sourceTable"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static IWorkbook AddSheet(this IWorkbook workbook, string sheetName, DataTable sourceTable, ExcelType type = ExcelType.xlsx)
        {
            int maxRowNum = (int)type;
            int dtRowsCount = sourceTable.Rows.Count;

            int sheetNum = 1;
            int rowIndex = 1;
            int tempIndex = 1; //标识
            ISheet sheet = workbook.CreateSheet("sheet" + sheetNum);

            CreateRow(sheet, sourceTable);
            IRow dataRow;
            ICell newCell;
            string drValue;
            DateTime dateV;
            bool boolV = false;
            int intV = 0;
            double doubV = 0;

            IDataFormat format = workbook.CreateDataFormat();

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            //ICellStyle stringStyle = workbook.CreateCellStyle();
            //stringStyle.DataFormat = format.GetFormat("@");
            //stringStyle.DataFormat = format.GetFormat("'"); //单引号

            for (int i = 0; i < dtRowsCount; i++)
            {
                dataRow = sheet.CreateRow(tempIndex);

                foreach (DataColumn column in sourceTable.Columns)
                {
                    //dataRow.CreateCell(column.Ordinal).SetCellValue(sourceTable.Rows[i][column].ToString());

                    newCell = dataRow.CreateCell(column.Ordinal);

                    drValue = sourceTable.Rows[i][column].ToString();
                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            newCell.SetCellValue(drValue);
                            //newCell.CellStyle = stringStyle;//格式化显示,禁止用科学计数法
                            break;
                        case "System.DateTime"://日期类型
                            if (DateTime.TryParse(drValue, out dateV))
                            {
                                newCell.SetCellValue(dateV);
                            }
                            else
                            {
                                newCell.SetCellValue(drValue);
                            }

                            newCell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            if (bool.TryParse(drValue, out boolV))
                            {
                                newCell.SetCellValue(boolV);
                            }
                            else
                            {
                                newCell.SetCellValue(drValue);
                            }
                            break;
                        case "System.Int16"://break;//整型
                        case "System.Int32"://break
                        case "System.Int64"://break
                        case "System.Byte"://break
                            if (int.TryParse(drValue, out intV))
                            {
                                newCell.SetCellValue(intV);
                            }
                            else
                            {
                                newCell.SetCellValue(drValue);
                            }
                            break;
                        case "System.Decimal"://break; //浮点型
                        case "System.Double":
                            if (double.TryParse(drValue, out doubV))
                            {
                                newCell.SetCellValue(doubV);
                            }
                            else
                            {
                                newCell.SetCellValue(drValue);
                            }
                            break;
                        default://空值等处理
                            newCell.SetCellValue(String.Empty);
                            break;
                    }

                }
                if (tempIndex == maxRowNum)
                {
                    sheetNum++;
                    sheet = workbook.CreateSheet("sheet" + sheetNum);
                    CreateRow(sheet, sourceTable);
                    tempIndex = 0;
                }
                rowIndex++;
                tempIndex++;
                //AutoSizeColumns(sheet);
            }

            return workbook;
        }

        
        /// <summary>
        /// 动态添加sheet(调用完成后,需要手动 关闭工作簿 workbook.Close();
        /// </summary>
        /// <typeparam name="T">数据实体泛型</typeparam>
        /// <param name="workbook"></param>
        /// <param name="sheetName">sheet名称(注意不要重复)</param>
        /// <param name="headerList">表头名称</param>
        /// <param name="dataList">数据集合</param>
        /// <param name="filePath">excel存放位置</param>
        /// <param name="selector">要导出的列表达式</param>
        public static void ExportListToExcel<T>(this IWorkbook workbook, string sheetName, IList<string> headerList, List<T> dataList, string filePath, Func<T, object[]> selector)
        {
            // 创建一个新的工作簿
            //IWorkbook workbook = new XSSFWorkbook();

            // 创建一个工作表
            ISheet sheet = workbook.CreateSheet(sheetName);

            int rowIndex = 1;
            //标题行
            if (headerList != null && headerList.Count > 0)
            {
                var row = sheet.CreateRow(0);
                for (var i = 0; i < headerList.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(headerList[i]);
                }
            }
            else
            {
                rowIndex = 0;//如果没有标题行 从第一行开始开始填充数据
            }

            // 填充数据
            foreach (var item in dataList)
            {
                var row = sheet.CreateRow(rowIndex++);
                var cellValues = selector(item);
                for (int i = 0; i < cellValues.Length; i++)
                {
                    row.CreateCell(i).SetCellValue(cellValues[i].ToString());
                }
            }
            
            //sheet.AutoSizeColumn(i);//慎用，会有性能问题
            
            // 将数据写入文件
            using (FileStream file = new FileStream(filePath, FileMode.Create))
            {
                workbook.Write(file);
            }

            // 关闭工作簿
            //workbook.Close();
        }


        #region 给单元格赋值
        /// <summary>
        /// 给单元格赋值
        /// </summary>
        /// <param name="sheet">表对象(sheet)</param>
        /// <param name="rownum">第几行(从0开始)</param>
        /// <param name="cellnum">第几个单元格(从0开始)</param>
        /// <param name="value">值</param>
        public static void SetCellValue(ISheet sheet, int rownum, int cellnum, string value)
        {
            IRow row = sheet.GetRow(rownum); ICell cell = row.GetCell(cellnum);
            cell.SetCellValue(value);
        }
        /// <summary>
        /// 给单元格赋值
        /// </summary>
        /// <param name="sheet">表对象(sheet)</param>
        /// <param name="rownum">第几行(从0开始)</param>
        /// <param name="cellnum">第几个单元格(从0开始)</param>
        /// <param name="value">值</param>
        public static void SetCellValue(ISheet sheet, int rownum, int cellnum, double value)
        {
            IRow row = sheet.GetRow(rownum); ICell cell = row.GetCell(cellnum);
            cell.SetCellValue(value);
        }
        /// <summary>
        /// 给单元格赋值
        /// </summary>
        /// <param name="sheet">表对象(sheet)</param>
        /// <param name="rownum">第几行(从0开始)</param>
        /// <param name="cellnum">第几个单元格(从0开始)</param>
        /// <param name="value">值</param>
        public static void SetCellValue(ISheet sheet, int rownum, int cellnum, bool value)
        {
            IRow row = sheet.GetRow(rownum); ICell cell = row.GetCell(cellnum);
            cell.SetCellValue(value);
        }
        /// <summary>
        /// 给单元格赋值
        /// </summary>
        /// <param name="sheet">表对象(sheet)</param>
        /// <param name="rownum">第几行(从0开始)</param>
        /// <param name="cellnum">第几个单元格(从0开始)</param>
        /// <param name="value">值</param>
        public static void SetCellValue(ISheet sheet, int rownum, int cellnum, DateTime value)
        {
            IRow row = sheet.GetRow(rownum); ICell cell = row.GetCell(cellnum);
            cell.SetCellValue(value);
        }
        /// <summary>
        /// 给单元格赋值
        /// </summary>
        /// <param name="sheet">表对象(sheet)</param>
        /// <param name="rownum">第几行(从0开始)</param>
        /// <param name="cellnum">第几个单元格(从0开始)</param>
        /// <param name="value">值</param>
        public static void SetCellValue(ISheet sheet, int rownum, int cellnum, IRichTextString value)
        {
            IRow row = sheet.GetRow(rownum); ICell cell = row.GetCell(cellnum);
            cell.SetCellValue(value);
        }
        #endregion

        /// <summary>
        /// 创建列
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="reader"></param>
        public static void CreateRow(ISheet sheet, IDataReader reader)
        {
            IRow headerRow = sheet.CreateRow(0);
            int cellCount = reader.FieldCount;

            // handling header.

            for (int i = 0; i < cellCount; i++)
            {
                headerRow.CreateCell(i).SetCellValue(reader.GetName(i));
            }
        }

        /// <summary>
        /// 以流的形式下载
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="type"></param>
        /// <param name="fileName">文件名称(不带后缀名称，例如 my.xls 只填写my即可)</param>
        public static void DownloadExcelStream(MemoryStream stream, ExcelType type, string fileName = "")
        {
            string tempName = String.Format("{0}.{1}",
                string.IsNullOrEmpty(fileName) ? DateTime.Now.ToString("yyyyMMdd") : fileName, type.ToString());

            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + tempName); //导出xlsx，要引用5个dll
            HttpContext.Current.Response.ContentType = "application/excel";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            HttpContext.Current.Response.BinaryWrite(stream.ToArray());
            stream.Dispose();
            HttpContext.Current.Response.End();
        }

        public static void DownloadExcelFile(string filePath, string fileName = "")
        {
            FileInfo fileInfo = new FileInfo(filePath);

            if (string.IsNullOrEmpty(fileName))
                fileName = String.Format("{0:yyyyMMdd}.{1}", DateTime.Now, GetFileExtension(fileInfo.FullName));

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ClearHeaders();
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
            HttpContext.Current.Response.AddHeader("Content-Length", fileInfo.Length.ToString());
            HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");
            HttpContext.Current.Response.ContentType = "application/excel";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
            HttpContext.Current.Response.WriteFile(fileInfo.FullName);
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
        }

        public static void DownloadExcelFile(string filePath, ExcelType type, string fileName = "")
        {
            DownloadExcelFile(filePath, fileName);
        }
        #endregion

        #region 私有方法
        /// <summary>
        /// 创建列
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="sourceTable"></param>
        private static void CreateRow(ISheet sheet, DataTable sourceTable)
        {
            IRow headerRow = sheet.CreateRow(0);
            foreach (DataColumn column in sourceTable.Columns) //创建列
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
        }

        /// <summary>
        /// 获取文件后缀名(例如:txt形式,)
        /// </summary>
        /// <param name="fileName">文件完整名称</param>
        /// <returns></returns>
        private static string GetFileExtension(string fileName)
        {
            return System.IO.Path.GetExtension(fileName)?.Substring(1).ToLower();
        }
        #endregion

    }
}

#region 调用例子
/*
 //自定义下载
        private void DownloadExcel(string sql, SqlParameter[] par)
        {
            string sql = string.Format("select * from Account");
            IDataReader reader = DbHelperSQL.ExecuteReader(sql);

            MemoryStream ms = NPOIHelper.RenderToExcel(reader);
            reader.Close();

            Response.ClearContent();
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx"); //导出xlsx，要引用5个dll
            //Response.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMdd") + ".xls");//注意导出的文件格式
            Response.ContentType = "application/excel";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.BinaryWrite(ms.ToArray());

            ms.Dispose();

            Response.End();
        }
*/
/*//直接下载
     protected void btn1_OnClick(object sender, EventArgs e)
        {
            string sql = string.Format("select * from Account");
            IDataReader reader = DbHelperSQL.ExecuteReader(sql);

           NPOIHelper.DownloadExcel(reader,NPOIHelper.ExcelType.xls);
        }

            DataTable dt = queryable.ToDataTable();//通过sql排序字段
            dt.Columns["Warehouse"].ColumnName = "仓库";
            dt.Columns["GoodTypeName"].ColumnName = "物品类型";
            dt.Columns["Goods"].ColumnName = "物品名称";
            dt.Columns["Code"].ColumnName = "物品编码";
            dt.Columns["Size"].ColumnName = "规格";
            dt.Columns["Color"].ColumnName = "颜色";
            dt.Columns["Price"].ColumnName = "参考单价";
            dt.Columns["TotalNum"].ColumnName = "总量";
            dt.Columns["TotalPrice"].ColumnName = "总价格";

            NPOIHelper.DownloadExcel(dt,fileName: "总库存数据");
 */
/*
//多表导出excel
IWorkbook workbook = new XSSFWorkbook();//xlsx
workbook.AddSheet("型材", opList.ToDataTable());
workbook.AddSheet("玻璃",  glassList.ToDataTable());
workbook.AddSheet("配件", partList.ToDataTable());
 MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush();
            workbook = null;
            NPOIHelper.DownloadExcelStream(ms,NPOIHelper.ExcelType.xlsx, "仓库报表"+ ViewState["planno"].ToString());
    
 */
 /*
 //多list 导出示例
 var uidList = newList.GroupBy(p => p.PatientUID).Select(p => p.Key).ToList();
   
   IWorkbook workbook = new XSSFWorkbook();
   string excelPath = "myexcel.xlsx";//保存位置
   var headerList = "姓名 床号 性别 年龄 日期 有效点 早餐后两小时".Split(" ");
   int tempNum = 1;
   foreach (var uid in uidList)
   {  
   //姓名 床号 性别 年龄 日期 有效点 早餐后两小时
   List<ExcelDTO> tempList = newList.Where(p=>p.PatientUID==uid)
   .OrderBy(p=>p.DataTime)
   .ToList();
   
   //防止重名 sheet前面加个序号
   NpoiHelper.ExportListToExcel(workbook, tempNum + "_"+ tempList[0].PatientName , headerList, tempList, excelPath,
   p => new object[] { p.PatientName,p.BedNum,p.Sex,p.Age,p.DataTime,p.ValidPonit,p.GlucoseMgData});
   tempNum++;
   }
   workbook.Close();// 关闭工作簿
 */
#endregion