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

namespace VueWebCoreApi.Tools
{
    public static class DownLoad
    {
        ///<summary>
        ///    下载
        ///</summary>
        ///<typeparam name="T"></typeparam>
        ///<param name="data"></param>
        ///<param name="map">所需要的列</param>
        ///<param name="filenName">文件名字</param>
        ///<returns></returns>
        public static string Download<T>(this IList<T> data, Dictionary<string, string> map, string filenName)
        {
            var filename = filenName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            string fileip = AppSetting.GetAppSetting("FileIP");
            var filepath = CoreHttpContext.MapPath("/File/newxls/");
            var path = filepath + filename;

            if (!Directory.Exists(filepath))
            {
                Directory.CreateDirectory(filepath);
            }
            var properties = TypeDescriptor.GetProperties(typeof(T));
            var dt = new DataTable();
            var list = map.Values;
            //显示表结构
            foreach (var row in list)
            {
                dt.Columns.Add(row);
            }
            //取值
            foreach (var item in data)
            {
                var i = 0;
                var values = new object[dt.Columns.Count];
                foreach (var content in map)
                {
                    var property = properties[content.Key];
                    if (property != null)
                    {
                        var value = property.GetValue(item);
                        if (property.PropertyType == typeof(decimal))
                        {
                            if ((decimal)value == 0)
                            {
                                value = 0;
                            }
                            else
                            {
                                value = ((decimal)value).ToString("0.00");
                            }
                        }
                        values[i] = value;
                        if (Convert.ToString(content).Contains("率"))
                        {
                            values[i] = value + "%";
                        }

                    }
                    else
                    {
                        values[i] = DBNull.Value;
                    }
                    i++;
                }
                dt.Rows.Add(values);
            }
            var b = DALSkillCondition.ResultExcel(path, dt);
            if (b)
            {
                var url = fileip + "/apis/File/newxls/" + filename;
                return url;
            }
            var messge = filename + "下载失败";
            return messge;
        }

        /// <summary>
        /// Datas the set to excel.
        /// </summary>
        /// <param name="ds">The ds.</param>
        /// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
        public static string DataSetToExcel(DataSet ds, string filenName)
        {
            try
            {
                string fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
                string fileip = AppSetting.GetAppSetting("FileIP");
                string filePath = CoreHttpContext.MapPath("/File/newxls/");

                Directory.CreateDirectory(filePath);
                string Path = filePath + fileName;

                FileStream fs = null;
                XSSFWorkbook workbook = new XSSFWorkbook();
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(ds.Tables[i].TableName);
                    XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
                    XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
                    dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

                    int rowIndex = 0;

                    #region 新建表，填充表头，填充列头，样式

                    if (rowIndex == 0)
                    {
                        #region 列头及样式

                        XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
                        XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;

                        XSSFFont font = (XSSFFont)workbook.CreateFont();
                        font.FontName = "微软雅黑";
                        font.FontHeightInPoints = 12; //字体大小
                        font.Boldweight = 700;//字体加粗
                        headStyle.SetFont(font);
                        //自定义表头
                        for (var j = 0; j < ds.Tables[i].Columns.Count; j++)
                        {
                            sheet.SetColumnWidth(j, 30 * 256);//
                            //sheet.AutoSizeColumn(j); //自适应宽度
                            headerRow.CreateCell(j).SetCellValue(ds.Tables[i].Columns[j].ColumnName);
                            headerRow.GetCell(j).CellStyle = headStyle;
                        }

                        #endregion

                        rowIndex = 1;
                    }

                    #endregion
                    ICellStyle cellstyle = workbook.CreateCellStyle();
                    cellstyle.VerticalAlignment = VerticalAlignment.Center;
                    cellstyle.Alignment = HorizontalAlignment.Center;
                    foreach (DataRow row in ds.Tables[i].Rows)
                    {
                        XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);

                        #region 填充内容

                        foreach (DataColumn column in ds.Tables[i].Columns)
                        {
                            XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
                            string type = row[column].GetType().FullName.ToString();
                            newCell.SetCellValue(GetValue(row[column].ToString(), type));
                            newCell.CellStyle = cellstyle;
                        }

                        #endregion

                        rowIndex++;
                    }
                }

                using (fs = File.OpenWrite(Path))
                {
                    workbook.Write(fs);
                    var url = fileip + "/File/newxls/" + fileName;
                    return url;
                }
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }

        /// <summary>
        /// Datas the set to excel.
        /// </summary>
        /// <param name="ds">The ds.</param>
        /// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
        public static string DataTableToExcel(DataTable ds, string filenName)
        {
            try
            {
                string fileName = filenName + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
                string fileip = AppSetting.GetAppSetting("FileIP");
                string filePath = CoreHttpContext.MapPath("/File/newxls/");
                Directory.CreateDirectory(filePath);
                string Path = filePath + fileName;

                FileStream fs = null;
                XSSFWorkbook workbook = new XSSFWorkbook();

                XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(ds.TableName);
                XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
                XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

                int rowIndex = 0;

                #region 新建表，填充表头，填充列头，样式

                if (rowIndex == 0)
                {
                    #region 列头及样式

                    XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
                    XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;

                    XSSFFont font = (XSSFFont)workbook.CreateFont();
                    font.FontName = "微软雅黑";
                    font.FontHeightInPoints = 12; //字体大小
                    font.Boldweight = 700;//字体加粗
                    headStyle.SetFont(font);
                    //自定义表头
                    for (var j = 0; j < ds.Columns.Count; j++)
                    {
                        sheet.SetColumnWidth(j, 30 * 256);//
                                                          //sheet.AutoSizeColumn(j); //自适应宽度
                        headerRow.CreateCell(j).SetCellValue(ds.Columns[j].ColumnName);
                        headerRow.GetCell(j).CellStyle = headStyle;
                    }

                    #endregion

                    rowIndex = 1;
                }

                #endregion
                ICellStyle cellstyle = workbook.CreateCellStyle();
                cellstyle.VerticalAlignment = VerticalAlignment.Center;
                cellstyle.Alignment = HorizontalAlignment.Center;
                foreach (DataRow row in ds.Rows)
                {
                    XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);

                    #region 填充内容

                    foreach (DataColumn column in ds.Columns)
                    {
                        XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
                        string type = row[column].GetType().FullName.ToString();
                        newCell.SetCellValue(GetValue(row[column].ToString(), type));
                        newCell.CellStyle = cellstyle;
                    }

                    #endregion

                    rowIndex++;
                }


                using (fs = File.OpenWrite(Path))
                {
                    workbook.Write(fs);
                    var url = fileip + "/File/newxls/" + fileName;
                    return url;
                }
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }

        /// <summary>
        /// Gets the value.
        /// </summary>
        /// <param name="cellValue">The cell value.</param>
        /// <param name="type">The type.</param>
        /// <returns>System.String.</returns>
        private static string GetValue(string cellValue, string type)
        {
            object value = string.Empty;
            switch (type)
            {
                case "System.String"://字符串类型
                    value = cellValue;
                    break;
                case "System.DateTime"://日期类型
                    System.DateTime dateV;
                    System.DateTime.TryParse(cellValue, out dateV);
                    value = dateV;
                    break;
                case "System.Boolean"://布尔型
                    bool boolV = false;
                    bool.TryParse(cellValue, out boolV);
                    value = boolV;
                    break;
                case "System.Int16"://整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    int intV = 0;
                    int.TryParse(cellValue, out intV);
                    value = intV;
                    break;
                case "System.Decimal"://浮点型
                case "System.Double":
                    double doubV = 0;
                    double.TryParse(cellValue, out doubV);
                    value = doubV;
                    break;
                case "System.DBNull"://空值处理
                    value = string.Empty;
                    break;
                default:
                    value = string.Empty;
                    break;
            }
            return value.ToString();
        }


        /// <summary>
        /// Datatable生成Excel表格并返回路径
        /// </summary>
        /// <param name="m_DataTable">Datatable</param>
        /// <param name="s_FileName">文件名</param>
        /// <returns></returns>
        public static string DataToExcel(System.Data.DataTable m_DataTable, string filenName)
        {
            var filename = filenName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            string fileip = AppSetting.GetAppSetting("FileIP");
            string filepath = CoreHttpContext.MapPath("/File/newxls/");
            var path = filepath + filename;

            //string FileName = AppDomain.CurrentDomain.BaseDirectory + ("/Upload/Excel/") + filenName + ".xls";  //文件存放路径
            if (System.IO.File.Exists(path))                                //存在则删除
            {
                System.IO.File.Delete(path);
            }
            System.IO.FileStream objFileStream;
            System.IO.StreamWriter objStreamWriter;
            string strLine = "";
            objFileStream = new System.IO.FileStream(path, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write);
            objStreamWriter = new System.IO.StreamWriter(objFileStream, Encoding.Unicode);
            for (int i = 0; i < m_DataTable.Columns.Count; i++)
            {

                strLine = strLine + m_DataTable.Columns[i].Caption.ToString() + Convert.ToChar(9);      //写列标题
            }
            objStreamWriter.WriteLine(strLine);
            strLine = "";
            for (int i = 0; i < m_DataTable.Rows.Count; i++)
            {
                for (int j = 0; j < m_DataTable.Columns.Count; j++)
                {
                    if (m_DataTable.Rows[i].ItemArray[j] == null)
                        strLine = strLine + " " + Convert.ToChar(9);                                    //写内容
                    else
                    {
                        string rowstr = "";
                        rowstr = m_DataTable.Rows[i].ItemArray[j].ToString();
                        if (rowstr.IndexOf("\r\n") > 0)
                            rowstr = rowstr.Replace("\r\n", " ");
                        if (rowstr.IndexOf("\t") > 0)
                            rowstr = rowstr.Replace("\t", " ");
                        strLine = strLine + rowstr + Convert.ToChar(9);
                    }
                }
                objStreamWriter.WriteLine(strLine);
                strLine = "";
            }
            objStreamWriter.Close();
            objFileStream.Close();
            var url = fileip + "/File/newxls/" + filename;
            return url;
        }



        /// <summary>
        /// 工资报表Excel导出数据合并
        /// </summary>
        /// <param name="path"></param>
        /// <param name="table"></param>
        /// <param name="treeIndex"></param>
        public static void DataTree(DataTable table, string filenName, int treeIndex, out string pathstring)  //   /apis/File/newxls/工资产量报表20200507114638.xls
        {
            var filename = filenName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            string fileip = AppSetting.GetAppSetting("FileIP");
            string filepath = CoreHttpContext.MapPath("/File/newxls/");
            var path = filepath + filename;
            using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                IWorkbook workBook = new HSSFWorkbook();
                //现在使用的仍然是生成Excel2003的Excel文件，由于03对行数（65535）和列数（255）有限制，所以当数据超出范围后难免出错
                //ArgumentException: Invalid column index (256). Allowable column range for BIFF8 is (0..255) or ('A'..'IV') ...
                if (Path.GetExtension(path).Equals(".xlsx", System.StringComparison.OrdinalIgnoreCase))
                {
                    workBook = new XSSFWorkbook();
                }

                string sheetName = string.IsNullOrWhiteSpace(table.TableName) ? "Sheet1" : table.TableName;
                ISheet sheet = workBook.CreateSheet(sheetName);
                IRow row = null;
                int colNum = 8; //合并的列数
                //int colNum = table.Columns.Count;
                //if (treeIndex < table.Columns.Count || treeIndex > 0)
                //{
                //    colNum = treeIndex;
                //}

                ICellStyle cellCenterStyle = GetCenter(workBook);
                //IFont font = workBook.CreateFont(); //创建一个字体样式对象
                //font.FontName = "方正舒体"; //和excel里面的字体对应
                //font.IsItalic = true; //斜体
                //font.FontHeightInPoints = 16;//字体大小
                //font.Boldweight = short.MaxValue;//字体加粗
                //cellCenterStyle.SetFont(font); //将字体样式赋给样式对象

                int beginNum = 1;//排除列头，从1开始

                //处理表格列头
                row = sheet.CreateRow(beginNum - 1);
                for (int i = 0; i < table.Columns.Count; i++)
                {

                    string strVal = table.Columns[i].ColumnName;
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(strVal);
                    cell.CellStyle = cellCenterStyle;
                    row.Height = 450;

                    sheet.AutoSizeColumn(i);

                    List<int> lstColWidth = new List<int>();
                    //记录列长度
                    lstColWidth.Add(DataLength(strVal));
                    //设置列宽
                    int maxWidth = lstColWidth.Max() * 600;
                    sheet.SetColumnWidth(i, maxWidth);
                }

                //处理数据内容
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    row = sheet.CreateRow(beginNum + i);
                    row.Height = 350;
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        string strVal = table.Rows[i][j].ToString();
                        ICell currCell = row.CreateCell(j);
                        currCell.SetCellValue(strVal);
                        currCell.CellStyle = cellCenterStyle;
                        sheet.SetColumnWidth(j, 256 * 15);
                    }
                }

                for (int i = 0; i < colNum; i++)  //数据条数
                {
                    List<int> lstColWidth = new List<int>();
                    string currVal = string.Empty;
                    string nextVal = string.Empty;
                    for (int j = beginNum; j <= sheet.LastRowNum; j++)
                    {
                        currVal = sheet.GetRow(j).Cells[i].StringCellValue;

                        int mk = j;
                        if (!string.IsNullOrWhiteSpace(currVal))//排除 空值，空值不做合并处理
                        {
                            for (int k = j + 1; k <= sheet.LastRowNum; k++)
                            {
                                nextVal = sheet.GetRow(k).Cells[i].StringCellValue;

                                if (currVal != nextVal)
                                {
                                    //因为k 累加所以导致当前值与下个值 不相同，所以记录 当前行数要 减去1
                                    mk = k - 1;
                                    break;
                                }
                                else if (k == sheet.LastRowNum) //边界值，处理最后一行，则提前Break 并记录当前 k
                                {
                                    mk = k;
                                    break;
                                }
                            }
                        }

                        if (mk != j)//排除 空值外，下个值的行数不等于当前行数，则需要合并
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(j, mk, i, i));

                            //sheet.GetRow(j).GetCell(i).SetCellValue("");
                        }
                        //else
                        //{
                        //    if (mk < sheet.LastRowNum) 
                        //    {
                        //        if (sheet.GetRow(j).Cells[0].StringCellValue == sheet.GetRow(mk + 1).Cells[0].StringCellValue
                        //           && sheet.GetRow(j).Cells[1].StringCellValue == sheet.GetRow(mk + 1).Cells[1].StringCellValue
                        //           && sheet.GetRow(j).Cells[2].StringCellValue == sheet.GetRow(mk + 1).Cells[2].StringCellValue
                        //           && sheet.GetRow(j).Cells[3].StringCellValue == sheet.GetRow(mk + 1).Cells[3].StringCellValue
                        //            )
                        //        {
                        //            int mmk = mk + 1;
                        //            sheet.AddMergedRegion(new CellRangeAddress(j, mmk, i, i));
                        //        }
                        //    }
                        //}

                        //if (i == 0) //如果是第一列，则 垂直水平居中
                        {
                            sheet.GetRow(j).Cells[i].CellStyle = cellCenterStyle;
                        }
                        //跳到执行下一个不同数据的行
                        j = mk;

                        //记录列长度
                        lstColWidth.Add(DataLength(currVal));
                    }

                    //设置列宽
                    //int maxWidth = lstColWidth.Max() * 600;
                    //sheet.SetColumnWidth(i, maxWidth);
                }
                //固定列、行 滚动时不变
                //sheet.CreateFreezePane(3, 1, 3, 1);

                //写入数据流
                workBook.Write(fs);
                var url = fileip + "/apis/File/newxls/" + filename;
                pathstring = url;
            }
        }


        private static ICellStyle GetCenter(IWorkbook workBook, short fontSize = 10)
        {
            ICellStyle cellStyle = workBook.CreateCellStyle();
            IFont font = workBook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = fontSize;
            cellStyle.SetFont(font);
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Center;
            return cellStyle;
        }


        /// <summary>
        /// 获取字符串长度（中文按2个字节长度）
        /// </summary>
        /// <param name="stringWithEnglishAndChinese"></param>
        /// <returns></returns>
        private static int DataLength(string stringWithEnglishAndChinese)
        {
            int lng = 0;
            for (int i = 0; i < stringWithEnglishAndChinese.Length; i++)
            {
                byte[] b = System.Text.Encoding.Default.GetBytes(stringWithEnglishAndChinese.Substring(i, 1));
                if (b.Length > 1)
                    lng += 2;
                else
                    lng += 1;
            }
            return lng;
        }

    }
}