
using Microsoft.AspNetCore.Http;
using NPOI.HSSF.UserModel;
using NPOI.OpenXmlFormats.Spreadsheet;
using NPOI.POIFS.NIO;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.Util.Collections;
using NPOI.XSSF.UserModel;
using SqlSugar;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tiobon.Core.Common;
using Tiobon.Core.Common.Extensions;
using Tiobon.Core.Common.Helper;
using Tiobon.Core.Model.Models;

namespace Tiobon.Core.DataAccess;

/// <summary>
/// 报表辅助类
/// </summary>
public static class ReportHelper
{

    #region 文件下载
    /// <summary>
    /// 生成文件，并通知用户下载(IQueryable)
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="list"></param>
    /// <param name="modelName">导出模块名称(JobSetting.xxx)</param>
    /// <param name="exportFields"></param>
    /// <param name="exportFieldsWidth"></param>
    /// <param name="user"></param>
    /// <param name="sort"></param>
    /// <param name="headText"></param>
    /// <param name="totalText"></param>
    /// <param name="isNeedItemNo"></param>
    public static async Task<string> SendFile<T>(IQueryable<T> list, string modelName, List<string> exportFields, Dictionary<string, string> exportDicFields = null, List<int> exportFieldsWidth = null, string sort = null, string headText = "", string totalText = "", bool isNeedItemNo = false) where T : class
    {

        if (list == null)
            throw new Exception("生成文件失败");
        //生成文件至文件服务器
        //var fid = SnowFlakeSingle.Instance.NextId();
        var path = $"{$"{Environment.CurrentDirectory}{Path.DirectorySeparatorChar}wwwroot{Path.DirectorySeparatorChar}files{Path.DirectorySeparatorChar}export{Path.DirectorySeparatorChar}"}";
        if (!Directory.Exists(path))
            Directory.CreateDirectory(path);
        var fname = $"{modelName}.xlsx";

        //list = string.IsNullOrEmpty(sort) ? list : list.OrderBy(sort);
        list.IntoFileFromLinqExcel(path + fname, ",", exportFields, exportDicFields, exportFieldsWidth, headText, totalText, isNeedItemNo);

        using var _context = ContextFactory.CreateContext();

        //Ghre_Attachment fileAttachment = new Ghre_Attachment();
        //fileAttachment.Id = fid;
        //fileAttachment.AttachFileName = fname;
        //fileAttachment.CreateBy = App.User.ID;
        //fileAttachment.CreateTime = DateTime.Now;
        //fileAttachment.AttachmentName = fname;
        //fileAttachment.AttachFileExtension = "xlsx";
        //fileAttachment.AttachFileSize = 0;
        //fileAttachment.PhysicsPath = $"/files/export/{fid}/" + fname;
        //fileAttachment.AttachmentType = "xlsx";

        //await _context.Ghre_Attachment.AddAsync(fileAttachment);
        //await _context.SaveChangesAsync();
        return $"{$"/files/export/"}" + fname;
    }

    public static async Task<long> SendFile(DataTable list, string modelName, Dictionary<string, string> fieldDescs, List<int> exportFieldsWidth, string sort = null, string headText = "", string totalText = "", bool isNeedItemNo = false)
    {

        if (list == null)
            throw new Exception("生成文件失败");
        //生成文件至文件服务器
        var fid = SnowFlakeSingle.Instance.NextId();
        var path = $"{$"{Environment.CurrentDirectory}{Path.DirectorySeparatorChar}wwwroot{Path.DirectorySeparatorChar}files{Path.DirectorySeparatorChar}export{Path.DirectorySeparatorChar}{fid}{Path.DirectorySeparatorChar}"}";
        if (!Directory.Exists(path))
            Directory.CreateDirectory(path);
        var fname = $"{modelName}.xlsx";

        list.IntoFileFromLinqExcel(path + fname, ",", fieldDescs, exportFieldsWidth, headText, totalText, isNeedItemNo);

        using var _context = ContextFactory.CreateContext();

        Ghre_Attachment fileAttachment = new Ghre_Attachment();
        fileAttachment.Id = fid;
        fileAttachment.AttachFileName = fname;
        fileAttachment.CreateBy = App.User.ID;
        fileAttachment.CreateTime = DateTime.Now;
        fileAttachment.AttachmentName = fname;
        fileAttachment.AttachFileExtension = "xlsx";
        fileAttachment.AttachFileSize = 0;
        fileAttachment.PhysicsPath = $"/files/export/{fid}/" + fname;
        fileAttachment.AttachmentType = "xlsx";

        await _context.Ghre_Attachment.AddAsync(fileAttachment);
        await _context.SaveChangesAsync();
        return fid;
    }
    #endregion

    #region 生成导入模板

    public static string ImportTemplate(ISqlSugarClient Db, List<QueryExportColumn> tableColumn, DataTable dt, string menuName)
    {

        //生成表格  
        try
        {
            tableColumn.Insert(0, new QueryExportColumn()
            {
                field = "SerialNumber",
                label = "序号"
            });
            //生成文件至文件服务器
            var fid = SnowFlakeSingle.Instance.NextId();
            var filepath = $"{$"{Environment.CurrentDirectory}{Path.DirectorySeparatorChar}wwwroot{Path.DirectorySeparatorChar}files{Path.DirectorySeparatorChar}ExcelTemplate{Path.DirectorySeparatorChar}{menuName}{Path.DirectorySeparatorChar}"}";
            if (!Directory.Exists(filepath))
                Directory.CreateDirectory(filepath);

            var fname = $"{menuName}_{DateTimeHelper.ConvertToSecondString1(DateTime.Now)}.xlsx";
            var sheetName = "导入数据";

            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(sheetName);
            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            ICellStyle datetimeStyle = workbook.CreateCellStyle();
            datetimeStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm");
            ICellStyle datetimesStyle = workbook.CreateCellStyle();
            datetimesStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
            int[] arrColWidth = new int[tableColumn.Count];

            for (int i = 0; i < tableColumn.Count; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding("utf-8").GetBytes(tableColumn[i].label).Length;
            }
            for (int i = 0; i < 1; i++)
            {
                for (int j = 0; j < tableColumn.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding("utf-8").GetBytes(tableColumn[j].label).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;
            int intTop = 0;
            int HeightInPoints = 40;
            int listColIndex = 0;
            //foreach (DataRow row1 in dt.Rows)
            //{

            //}
            #region 新建表、填充表头、填充列头，样式
            if (rowIndex == 655350 || rowIndex == 0)
            {
                if (rowIndex != 0)
                    sheet = workbook.CreateSheet();
                intTop = 0;

                #region  列头及样式

                IRow headerRow = sheet.CreateRow(intTop);
                headerRow.HeightInPoints = HeightInPoints;
                intTop += 1;

                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.Center; //居中
                headStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中 
                headStyle.WrapText = true;//自动换行
                                          // 边框
                headStyle.BorderBottom = BorderStyle.Thin;
                headStyle.BorderLeft = BorderStyle.Thin;
                headStyle.BorderRight = BorderStyle.Thin;
                headStyle.BorderTop = BorderStyle.Thin;


                ICellStyle headRequiredStyle = workbook.CreateCellStyle();
                headRequiredStyle.Alignment = HorizontalAlignment.Center; //居中
                headRequiredStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中 
                headRequiredStyle.WrapText = true;//自动换行
                                                  // 边框
                headRequiredStyle.BorderBottom = BorderStyle.Thin;
                headRequiredStyle.BorderLeft = BorderStyle.Thin;
                headRequiredStyle.BorderRight = BorderStyle.Thin;
                headRequiredStyle.BorderTop = BorderStyle.Thin;
                // 字体
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = (short)10;
                font.IsBold = true;
                font.FontName = "宋体";
                headStyle.SetFont(font);

                IFont font2 = workbook.CreateFont();
                font2.FontHeightInPoints = (short)10;
                font2.IsBold = true;
                font2.FontName = "宋体";
                font2.Color = IndexedColors.Red.Index;
                headRequiredStyle.SetFont(font2);
                #endregion

                for (int j = 0; j < tableColumn.Count; j++)
                {
                    string dataSource = tableColumn[j].dataSource;
                    string field = tableColumn[j].field;
                    string label = tableColumn[j].label;
                    var cel2 = headerRow.CreateCell(j);
                    cel2.SetCellValue(label);
                    if (tableColumn[j].required == "true")
                        headerRow.GetCell(j).CellStyle = headRequiredStyle;
                    else
                        headerRow.GetCell(j).CellStyle = headStyle;

                    if (tableColumn[j].Comment.IsNotEmptyOrNull())
                        cel2.CellComment = GetComment(sheet, null, j, tableColumn[j].Comment);

                    //设置列宽
                    //sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                    if (arrColWidth[j] > 255)
                    {
                        arrColWidth[j] = 254;
                    }
                    else
                    {
                        sheet.SetColumnWidth(j, (arrColWidth[j] + 1) * 256);
                    }


                    //是否下拉
                    if (!string.IsNullOrEmpty(dataSource)
                        && !dataSource.StartsWith("OrgTreeWith")
                         && !dataSource.StartsWith("StaffWith")
                        )
                    {
                        var dataSourceLists = GetDataSourceLists(Db, field, dataSource);


                        //var fields = new List<string>()
                        //{
                        //    "InOrOut",
                        //    "ResumeType",
                        //    "ImportanceDegree",
                        //    "YearHumanId",
                        //    "TitleId"
                        //};
                        try
                        {
                            if (dataSourceLists.Any())
                            {
                                //设置数据源的值
                                IDataValidationConstraint dvConstraint1 = sheet.GetDataValidationHelper().CreateExplicitListConstraint(dataSourceLists.Where(x => x.name.IsNotEmptyOrNull()).Select(x => x.name).ToArray());
                                //设置编辑的区域
                                IDataValidation dataValidation1 = sheet.GetDataValidationHelper().CreateValidation(dvConstraint1, new CellRangeAddressList(1, 65535, j, j));
                                sheet.AddValidationData(dataValidation1);
                            }
                        }
                        catch (Exception E)
                        {

                        }
                        // 多选 ，自行输入不报错
                        //if (drow.elementType.ToString().LastIndexOf("multiple") == -1)
                        //{
                        //    dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
                        //    dataValidate.ShowPromptBox = true;
                        //}
                        //else
                        //{
                        //    dataValidate.ShowErrorBox = false;
                        //}
                    }

                }

                rowIndex = intTop;
            }
            #endregion

            #region 填充内容
            //IRow dataRow = sheet.CreateRow(rowIndex);
            //dataRow.HeightInPoints = 25;

            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center; //居中
            style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 
            style.WrapText = true;//自动换行
                                  // 边框
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            // 字体
            var font1 = workbook.CreateFont();
            font1.FontHeightInPoints = (short)10;
            font1.FontName = "宋体";
            style.SetFont(font1);
            #endregion

            for (int j = 0; j < tableColumn.Count; j++)
            {
                //设置列宽
                //sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                if (arrColWidth[j] > 255)
                {
                    arrColWidth[j] = 254;
                }
                else
                {
                    sheet.SetColumnWidth(j, (arrColWidth[j] + 1) * 256);
                }
            }

            //rowIndex++;

            for (int columnNum = 0; columnNum <= tableColumn.Count; columnNum++)
            {
                sheet.AutoSizeColumn(columnNum);//先来个常规自适应

                var columnWidth = sheet.GetColumnWidth(columnNum) / 256;
                for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
                {
                    IRow currentRow;
                    //当前行未被使用过
                    if (sheet.GetRow(rowNum) == null)
                    {
                        currentRow = sheet.CreateRow(rowNum);
                    }
                    else
                    {
                        currentRow = sheet.GetRow(rowNum);
                    }
                    if (currentRow.GetCell(columnNum) != null)
                    {
                        ICell currentCell = currentRow.GetCell(columnNum);
                        int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                        if (columnWidth < length)
                        {
                            columnWidth = length;
                            if (columnWidth > 30) columnWidth = 30;
                        }
                    }
                }
                try
                {
                    sheet.SetColumnWidth(columnNum, columnWidth * 300); // 256
                }
                catch (Exception e)
                {

                }

            }
            for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
            {
                IRow currentRow = sheet.GetRow(rowNum);

                int length = 25;
                foreach (var item in currentRow.Cells)
                {
                    if (item != null)
                        length = Encoding.UTF8.GetBytes(item.ToString()).Length > length ? Encoding.UTF8.GetBytes(item.ToString()).Length : length;
                }
                currentRow.HeightInPoints = 35 * (length / 150 + 1);
            }


            //转为字节数组
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件
            using (FileStream fs = new FileStream(filepath + fname, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }

            return filepath + fname;
        }
        catch (Exception ex)
        {
        }
        return default(string);
    }

    public static async Task<string> ImportTemplate1(ISqlSugarClient Db, List<QueryExportColumn> tableColumn, DataTable dt, string menuName)
    {

        //生成表格  
        try
        {

            //生成文件至文件服务器
            var fid = SnowFlakeSingle.Instance.NextId();
            var filepath = $"{$"{Environment.CurrentDirectory}{Path.DirectorySeparatorChar}wwwroot{Path.DirectorySeparatorChar}files{Path.DirectorySeparatorChar}import{Path.DirectorySeparatorChar}{fid}{Path.DirectorySeparatorChar}"}";
            if (!Directory.Exists(filepath))
                Directory.CreateDirectory(filepath);
            var fname = $"{menuName}.xlsx";
            IWorkbook fileWorkbook;
            bool IsHSSF = true;
            if (Path.GetExtension(filepath) == ".xls")
            {
                IsHSSF = true;
                fileWorkbook = new HSSFWorkbook();
            }
            else
            {
                IsHSSF = false;
                fileWorkbook = new XSSFWorkbook();
            }
            int index = 0;

            //创建Sheet;
            ISheet sheet = fileWorkbook.CreateSheet("导入数据");
            ISheet sheet2 = fileWorkbook.CreateSheet("下拉数据");

            //隐藏下拉数据Sheet
            //fileWorkbook.SetSheetHidden(fileWorkbook.GetSheetIndex("下拉数据"), SheetState.Hidden);

            IRow headerRow = sheet.CreateRow(0); //文本行

            headerRow.HeightInPoints = 30;
            headerRow.CreateCell(0).SetCellValue("序号");

            //必填颜色
            //var CellRed = fileWorkbook.CreateCellStyle();    // 创建单元格样式
            //IFont Font = fileWorkbook.CreateFont();     // 创建字体
            //Font.Color = IndexedColors.Red.Index;   // 选择字体颜色
            //CellRed.SetFont(Font);   // 把字体赋给样式 

            ICellStyle headStyle = fileWorkbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center; //居中
            headStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中 
            headStyle.WrapText = true;//自动换行
                                      // 边框
            headStyle.BorderBottom = BorderStyle.Thin;
            headStyle.BorderLeft = BorderStyle.Thin;
            headStyle.BorderRight = BorderStyle.Thin;
            headStyle.BorderTop = BorderStyle.Thin;
            // 字体
            IFont font = fileWorkbook.CreateFont();
            font.FontHeightInPoints = (short)10;
            font.IsBold = true;
            font.FontName = "宋体";
            headStyle.SetFont(font);


            headerRow.GetCell(0).CellStyle = headStyle;

            //文本类型
            ICellStyle cellString = fileWorkbook.CreateCellStyle();
            IDataFormat cellFormat = fileWorkbook.CreateDataFormat();
            cellString.DataFormat = cellFormat.GetFormat("@");

            //下拉数据列ID
            int listColIndex = 0;
            int ColNums = 0;
            foreach (var drow in tableColumn)
            {
                int SortNum = Convert.ToInt32(drow.sortNum);
                ICell cel2 = headerRow.CreateCell(SortNum);
                string field = drow.field;
                cel2.CellStyle = headStyle;

                string label = drow.label;
                // 批注 
                string commentText = string.Empty;
                try
                {
                    commentText = drow.commentText;
                }
                catch (Exception)
                {
                }
                cel2.SetCellValue(label);

                // 设置批注
                if (!string.IsNullOrEmpty(commentText))
                {
                    cel2.CellComment = GetComment(sheet, null, ColNums, commentText);
                }

                //是否必填
                if (drow.required == "true")
                {
                    cel2.CellStyle = headStyle;
                }

                string dataSource = drow.dataSource;
                //是否下拉
                if (!string.IsNullOrEmpty(dataSource)
                    && !dataSource.StartsWith("OrgTreeWith")
                     && !dataSource.StartsWith("StaffWith")
                    )
                {
                    List<DataSourceList> dataSourceLists = GetDataSourceLists(Db, field, dataSource);
                    //下拉数据行ID
                    int listRowIndex = 1;
                    //写入下拉值区域
                    IRow rowTitle = sheet2.GetRow(0);
                    if (rowTitle == null)
                        rowTitle = sheet2.CreateRow(0);
                    rowTitle.CreateCell(listColIndex).SetCellValue(label);
                    foreach (var data in dataSourceLists)//将下拉列表中的数据循环赋给sheet页
                    {
                        IRow row = sheet2.GetRow(listRowIndex);
                        if (row == null)
                            row = sheet2.CreateRow(listRowIndex);
                        listRowIndex = listRowIndex + 1;
                        row.CreateCell(listColIndex).SetCellValue(data.name);
                    }
                    // 定义一个名称，指向刚才创建的下拉项的区域：
                    var rangeName = field + "Range";
                    IName range = fileWorkbook.CreateName();
                    string ABC = Other.Num2ABCletter((listColIndex + 1));
                    //range.RefersToFormula = "下拉数据" + "!$"+ ABC + "$2:$"+ ABC + "$" + (listRowIndex == 0 ? 2 : listRowIndex);
                    //Excel 正常引用
                    string a1 = "下拉数据" + "!$" + ABC + "$2:$" + ABC + "$" + (listRowIndex == 0 ? 2 : listRowIndex);
                    // Excel R1C1引用样式
                    string a2 = "下拉数据" + "!$R2C" + listColIndex.ToString() + ":R" + listRowIndex.ToString() + "C" + listColIndex.ToString();
                    listColIndex = listColIndex + 1;

                    range.RefersToFormula = a1;

                    range.NameName = rangeName;

                    //-----------------分割线以上是用来创建一个sheet页赋值，然后将sheet页中的内容定义成一个名称，后面用来当作数组传入方法的过程 
                    CellRangeAddressList cellRegions = new CellRangeAddressList(1, 65535, SortNum, SortNum);//  
                    DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
                    //HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
                    //IDataValidationConstraint constraint;
                    IDataValidation dataValidate;
                    if (Path.GetExtension(filepath) == ".xls")
                    {
                        constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
                        dataValidate = new HSSFDataValidation(cellRegions, constraint);
                    }
                    else
                    {
                        CT_DataValidation cdv = new CT_DataValidation();
                        cdv.allowBlank = true;
                        //constraint = CT_DataValidation
                        dataValidate = new XSSFDataValidation(cellRegions, cdv);
                    }
                    // 多选 ，自行输入不报错
                    if (drow.elementType.ToString().LastIndexOf("multiple") == -1)
                    {
                        dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
                        dataValidate.ShowPromptBox = true;
                    }
                    else
                    {
                        dataValidate.ShowErrorBox = false;
                    }
                    sheet.AddValidationData(dataValidate);
                }

                //是否文本格式
                if (drow.dataType != "int"
                    && drow.dataType != "decimal"
                    && drow.dataType != "number"
                    )
                {
                    sheet.SetDefaultColumnStyle(ColNums, cellString);
                }

                ColNums = ColNums + 1;
            }

            //erow_1.HeightInPoints = 1;

            // 如果导出模板存在默认数据
            //if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    IRow row1 = sheet.CreateRow(i + 1);
            //    ICell cell = row1.CreateCell(0);
            //    cell.SetCellValue(i + 1);
            //    for (int j = 1; j <= tableColumn.Count; j++)
            //    {
            //        cell = row1.CreateCell(j);

            //        //if (dt.Rows[i][tableColumn[j - 1].field] != null)
            //        //    cell.SetCellValue(dt.Rows[i][tableColumn[j - 1].field].ObjToString());
            //    }
            //}

            // Sheet 更改列为自适应宽度
            for (int col = 0; col <= ColNums; col++)
            {
                try
                {
                    sheet.AutoSizeColumn(col);//自适应宽度，但是其实还是比实际文本要宽
                    var columnWidth = sheet.GetColumnWidth(col) / 256;//获取当前列宽度
                    for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
                    {
                        IRow row = sheet.GetRow(rowIndex);
                        ICell cell = row.GetCell(col);
                        int contextLength = Encoding.UTF8.GetBytes(cell.ToString()).Length;//获取当前单元格的内容宽度
                        columnWidth = columnWidth < contextLength ? contextLength : columnWidth;
                    }
                    sheet.SetColumnWidth(col, columnWidth * 256);// 
                }
                catch (Exception)
                {
                }
            }

            // Sheet2 更改列为自适应宽度
            //if (listColIndex > 0)
            //{
            //    for (int col = 0; col <= listColIndex - 1; col++)
            //    {
            //        sheet2.AutoSizeColumn(col);//自适应宽度，但是其实还是比实际文本要宽
            //        int columnWidth = sheet2.GetColumnWidth(col) / 256;//获取当前列宽度
            //        for (int rowIndex = 1; rowIndex <= sheet2.LastRowNum; rowIndex++)
            //        {
            //            IRow row = sheet2.GetRow(rowIndex);
            //            ICell cell = row.GetCell(col);
            //            int contextLength = Encoding.UTF8.GetBytes(cell.ToString()).Length;//获取当前单元格的内容宽度
            //            columnWidth = columnWidth < contextLength ? contextLength : columnWidth;
            //        }
            //        sheet2.SetColumnWidth(col, columnWidth * 256);// 
            //    }
            //}
            //转为字节数组
            MemoryStream stream = new MemoryStream();
            fileWorkbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件
            using (FileStream fs = new FileStream(filepath + fname, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }

            return filepath + fname;
        }
        catch (Exception ex)
        {
        }
        return default(string);
    }
    #endregion

    #region Excel导入

    #region 获取Excel导入文件地址
    /// <summary>
    /// 获取Excel导入文件地址
    /// </summary>
    /// <param name="file">Excel文件流</param>
    /// <param name="menuName">菜单名称</param>
    /// <returns>文件地址</returns>
    public static async Task<(string, string)> GetImportFilePath(IFormFile file, string menuName)
    {
        var id = DateTimeHelper.ConvertToSecondString1(DateTime.Now);
        var path = $"{$"{Path.DirectorySeparatorChar}files{Path.DirectorySeparatorChar}import{Path.DirectorySeparatorChar}{menuName}{Path.DirectorySeparatorChar}{id}{Path.DirectorySeparatorChar}"}";
        FileHelper.CreateDirectory(path);

        var filepath = path + file.FileName;
        using (var stream = File.Create(FileHelper.GetPhysicsPath() + filepath))
        {
            await file.CopyToAsync(stream);
        }
        return (path, filepath);
    }
    #endregion

    #region 读取 excel,默认第一行为标头
    /// <summary>
    /// 读取 excel,默认第一行为标头
    /// </summary>
    /// <param name="strFileName">excel 文档路径</param>
    /// <returns></returns>
    public static DataTable ReadImportExcel(string strFileName, string sheetName = "导入数据")
    {
        DataTable dt = new();
        //HSSFWorkbook hssfworkbook;
        IWorkbook hssfworkbook;
        ISheet sheet;
        using (FileStream file = new FileStream(FileHelper.GetPhysicsPath() + strFileName, FileMode.Open, FileAccess.Read))
        {
            //hssfworkbook = new HSSFWorkbook(file);
            //hssfworkbook = new XSSFWorkbook(file);
            hssfworkbook = NPOI.SS.UserModel.WorkbookFactory.Create(file);
        }
        if (hssfworkbook == null) throw new Exception("未能加载excel");
        int sheetCount = hssfworkbook.NumberOfSheets;
        if (sheetCount == 0) throw new Exception("未能加载excel");
        if (string.IsNullOrEmpty(sheetName))
            sheet = hssfworkbook.GetSheetAt(0);
        else
        {
            int sheetIndex = hssfworkbook.GetSheetIndex(sheetName);
            if (sheetIndex >= 0)
                sheet = hssfworkbook.GetSheetAt(sheetIndex);
            else
                throw new Exception($"未能找到{sheetName}这个sheet页");
        }
        var rows = sheet.GetRowEnumerator();
        var headerRow = sheet.GetRow(0);
        int cellCount = headerRow.LastCellNum;
        for (int j = 0; j < cellCount; j++)
        {
            var cell = headerRow.GetCell(j);
            if (!string.IsNullOrWhiteSpace(cell.ToString()))
                dt.Columns.Add(cell.ToString());
        }
        for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
        {
            var row = sheet.GetRow(i);

            if (row is null)
                continue;
            var dataRow = dt.NewRow();
            for (int j = row.FirstCellNum; j < cellCount; j++)
            {
                if (j < 0)
                    continue;
                if (row.GetCell(j) != null)
                {
                    DateTime dateV = DateTime.MinValue;
                    try
                    {
                        dataRow[j] = GetCellValue(row.GetCell(j));
                        //if (row.GetCell(j).IsDate())
                        //{
                        //    dateV = row.GetCell(j).DateCellValue;
                        //    dataRow[j] = DateTimeHelper.ConvertToSecondString(dateV);
                        //}
                        //else
                        //{
                        //    dataRow[j] = row.GetCell(j).ToString();
                        //}
                    }
                    catch { }
                    //if (dateV == DateTime.MinValue)
                    //{
                    //    dataRow[j] = row.GetCell(j).ToString();
                    //}
                    //else
                    //{
                    //    dataRow[j] = DateTimeHelper.ConvertToSecondString(dateV);
                    //}

                }
            }
            dt.Rows.Add(dataRow);
        }
        if (dt.Columns["Comments"] == null)
            dt.Columns.Add("Comments", typeof(string));
        return dt;
    }
    #endregion

    #region 获取单元格类型
    /// <summary>
    /// 获取单元格类型
    /// </summary>
    /// <param name="cell"></param>
    /// <returns></returns>
    private static string GetCellValue(ICell cell)
    {
        if (cell == null)
            return null;
        switch (cell.CellType)
        {
            case CellType.Blank: //BLANK:  
                return null;
            case CellType.Boolean: //BOOLEAN:  
                return Convert.ToString(cell.BooleanCellValue);
            case CellType.Numeric: //NUMERIC:  
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    return DateTimeHelper.ConvertToSecondString(cell.DateCellValue);
                }
                else
                {
                    return Convert.ToString(cell);
                }
            case CellType.String: //STRING:  
                return cell.StringCellValue;
            case CellType.Error: //ERROR:  
                return Convert.ToString(cell.ErrorCellValue);
            case CellType.Formula: //FORMULA:  
            default:
                return "=" + cell.CellFormula;
        }
    }
    #endregion

    #region 获取excel导入初始化Dcit
    /// <summary>
    /// 获取excel导入初始化Dcit
    /// </summary>
    /// <returns></returns>
    public static Dictionary<string, object> GetDefaultDict()
    {
        var dict = new Dictionary<string, object>
        {
            { "Id",  UtilHelper.GetSysId() },
            { "CreateBy", App.User.ID },
            { "CreateTime", DateTime.Now }
        };

        return dict;
    }

    #endregion

    #region 数据验证
    /// <summary>
    /// 数据验证
    /// </summary>
    /// <param name="columns"></param>
    /// <param name="dt"></param>
    /// <returns></returns>
    public static async Task<(List<Dictionary<string, object>>, int)> ValidImportExcel(ISqlSugarClient Db, List<QueryExportColumn> columns, DataTable dt)
    {
        int ErrorCount = 0;
        var dictList = new List<Dictionary<string, object>>();
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var dict = GetDefaultDict();
            var comments = new List<string>();
            for (int j = 0; j < columns.Count; j++)
            {
                var x = columns[j];
                if (!dt.Columns.Contains(x.label))
                    comments.Add("未查询到【" + x.label + "】列！");
                else
                {
                    var value = dt.Rows[i][x.label].ToString();
                    if (x.required == "true" && value.IsNullOrEmpty())
                    {
                        comments.Add(x.label + "不能为空！");
                        dt.Rows[i]["Comments"] = string.Join(";", comments.Select(a => a));
                    }

                    if (x.dataSourceType.IsNullOrEmpty() && value.IsNotEmptyOrNull())
                    {
                        if (x.dataType == "int" || x.dataType == "decimal")
                        {
                            try
                            {
                                Convert.ToDecimal(value);
                            }
                            catch (Exception)
                            {
                                comments.Add(x.label + "无效的数字类型！");
                            }
                        }
                        else if (x.dataType == "date")
                        {
                            try
                            {
                                Convert.ToDateTime(value);
                            }
                            catch (Exception)
                            {
                                comments.Add(x.label + "无效的日期类型！");
                            }
                        }
                    }

                    if (value.IsNotEmptyOrNull())
                    {
                        if (x.elementType == "Switch")
                            value = value == "是" ? "true" : "false";
                        if (x.dataSourceId.IsNotEmptyOrNull() && x.dataSourceType.IsNotEmptyOrNull() && !x.dataSource.StartsWith("OrgTreeWith")
                         && !x.dataSource.StartsWith("StaffWith"))
                        {

                            if (x.dataSourceType == "CommonList")
                            {
                                var commonSql = await Db.Queryable<Ghrs_ListCommonSql>().Where(o => o.ListCommonSqlId == x.dataSourceId).FirstAsync();

                                if (commonSql != null)
                                {
                                    string sql = @$"SELECT [value]
                                FROM ({commonSql.SelectSql}) A
                                WHERE label = '{value}'";
                                    sql = sql.Replace("{@LangID}", "1");
                                    sql = sql.Replace("{@UserID}", App.User.ID.ObjToString());
                                    sql = sql.Replace("{@KeyWords}", null);
                                    var id2 = await Db.Ado.GetLongAsync(sql);

                                    dict.Add(x.field, id2);
                                }
                            }
                            else if (x.dataSourceType == "ParaDetailNo")
                            {
                                var sql = @$"SELECT ParaDetailNo
                                FROM Ghrs_ParaDetail
                                WHERE     ParaMasterId IN (SELECT ParaMasterId
                                                           FROM Ghrs_ParaMaster
                                                           WHERE ParaMasterId = {x.dataSourceId.ObjToInt()})
                                      AND IsEnable = 1
                                      AND ParaDetailName = '{value}'";
                                var id2 = await Db.Ado.GetStringAsync(sql);

                                dict.Add(x.field, id2);
                            }
                        }
                        else if (x.dataSource == "OrgTreeWithPriv")
                        {
                            string sql = @$"SELECT DeptID FROM Ghro_Dept WHERE DeptName = '{value}' OR DeptNo = '{value}' OR DeptEname = '{value}'";
                            var id2 = await Db.Ado.GetLongAsync(sql);
                            dict.Add(x.field, id2);
                        }
                        else if (x.dataSource == "StaffWithoutPriv")
                        {
                            string sql = @$"SELECT StaffID FROM Ghra_Staff WHERE StaffName = '{value}' OR StaffNo = '{value}' OR StaffEname = '{value}'";
                            var id2 = await Db.Ado.GetLongAsync(sql);
                            dict.Add(x.field, id2);
                        }
                        else
                            dict.Add(x.field, value);
                    }
                }
            }
            if (comments.Any())
            {
                dt.Rows[i]["Comments"] = string.Join(";", comments.Select(a => a));
                ErrorCount++;
                continue;
            }
            else
                dictList.Add(dict);
        }

        return (dictList, ErrorCount);
    }
    #endregion

    #endregion

    /// <summary>
    /// Excel字段下拉
    /// </summary>
    public class DataSourceList
    {
        public string field { get; set; }
        public string id { get; set; }
        public string no { get; set; }
        public string name { get; set; }
    } /// <summary>
      /// 设置Excel批注
      /// </summary>
      /// <param name="sheet"> sheet</param>
      /// <param name="comment"> comment </param>
      /// <param name="cellnum"> 列 </param>
      /// <param name="text">文本</param>
      /// <returns></returns>
    private static IComment GetComment(ISheet sheet, IComment comment, int cellnum, string text)
    {
        IWorkbook workbook = sheet.Workbook;
        bool exportXlsx = workbook is XSSFWorkbook;
        if (comment == null)
        {
            IDrawing draw = sheet.CreateDrawingPatriarch();
            IClientAnchor clientAnchor = null;
            if (exportXlsx)
            {
                clientAnchor = new XSSFClientAnchor(0, 0, 0, 0, cellnum, 0, cellnum + 2, 6);
            }
            else
            {
                clientAnchor = new HSSFClientAnchor(0, 0, 0, 0, cellnum, 0, cellnum + 2, 6);
            }
            comment = draw.CreateCellComment(clientAnchor);
        }
        IRichTextString richText = null;
        if (exportXlsx)
        {
            richText = new XSSFRichTextString(string.Format("{0}", text));
        }
        else
        {
            richText = new HSSFRichTextString(string.Format("{0}", text));
        }
        //richText.ApplyFont(GetDefaultFont(workbook, true));// 设置批注中文本的字体
        comment.String = richText;
        comment.Visible = false;
        comment.Author = "Tiobon";
        return comment;
    }

    /// <summary>
    /// 获取数据下拉
    /// </summary>
    /// <param name="dataSource"></param>
    /// <returns></returns>
    public static List<DataSourceList> GetDataSourceLists(ISqlSugarClient Db, string field, string dataSource)
    {
        List<DataSourceList> dataSourceList = new List<DataSourceList>();

        try
        {
            ////Dept
            //if (dataSource.StartsWith("OrgTreeWith"))
            //{
            //}
            ////Staff
            //else if (dataSource.StartsWith("StaffWith"))
            //{

            //}
            //else
            {
                string listSQL = string.Format(@"exec[dbo].[PRI_ListValue] '{0}','GetEnableList','',1,1,'',9999,1,2", dataSource);
                DataSet listds = Db.Ado.GetDataSetAll(listSQL);
                if (listds.Tables.Count > 3)
                {
                    DataTable listdt = new DataTable();
                    listdt = listds.Tables[2];

                    foreach (DataRow row in listdt.Rows)
                    {
                        DataSourceList _dataSource = new DataSourceList();
                        _dataSource.field = field;
                        try
                        {
                            _dataSource.id = row["value"].ToString();
                            _dataSource.name = row["label"].ToString();
                        }
                        catch (Exception)
                        {
                            _dataSource.id = row[0].ToString();
                            _dataSource.name = row[1].ToString();
                            try
                            {
                                _dataSource.name = _dataSource.name + " " + row[2].ToString();
                            }
                            catch (Exception)
                            {
                            }
                        }
                        var dataSourceStr = string.Join(",", dataSourceList.Select(x => x.name));
                        if (dataSourceList.Any() && _dataSource.name.IsNotEmptyOrNull())
                            dataSourceStr = dataSourceStr + "," + _dataSource.name;
                        if (dataSourceStr.Length <= 255 && _dataSource.name.IsNotEmptyOrNull())
                            dataSourceList.Add(_dataSource);

                    }
                }
            }
        }
        catch (Exception)
        {
        }

        return dataSourceList;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="sql">需要写入的查询的SQL</param>
    /// <param name="rows">开始行</param>
    /// <param name="cols">开始列</param>
    /// <returns></returns>
    public static bool InsertExcelList(ISqlSugarClient Db, ISheet sheet, string sql)
    {
        bool IsBool = false;
        DataSet ds = Db.Ado.GetDataSetAll(sql);
        //写入下拉值区域
        int cols = 0;
        foreach (DataTable dt in ds.Tables)
        {
            int col = 0;
            //表头
            IRow row = sheet.GetRow(0);
            if (row == null)
                row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i + cols);
                cell.SetCellValue(dt.Columns[i].ColumnName);
                col++;
            }
            //数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.GetRow(i + 1);
                if (row1 == null)
                    row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row1.CreateCell(j + cols);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            cols = cols + col + 1;
        }

        return IsBool;
    }

    /// <summary>
    /// 是否隐藏首行
    /// </summary>
    public static bool IsHideTopRow = false;

    /// <summary>
    /// 读取Excel写入临时表，逻辑在存储过程里写
    /// </summary>
    /// <returns></returns> 
    public static async Task<string> ExcelToDataTable(ISqlSugarClient Db, string FilePath, string menuName, int LangID, int UserID, string doType = null, string procName = null)
    {
        string result = string.Empty;
        int ErrorNum = 0;
        int SuccessNum = 0;
        DataTable mydt = GetDataTable(FilePath, 0, null);
        if (mydt.Columns["Comments"] == null)
        {
            mydt.Columns.Add("Comments", typeof(string));
        }
        mydt.Rows[0]["Comments"] = "提示信息";

        //从第二行开始读取
        int FirstI = 1;
        IsHideTopRow = true;
        //创建GUID;
        string guidN = Guid.NewGuid().ToString("N");

        DataTable edt = new DataTable();
        edt.Columns.Add("ExcelNums", typeof(int));// 
        edt.Columns.Add("GID", typeof(string));// 
        edt.Columns.Add("LangID", typeof(int));// 
        edt.Columns.Add("UserID", typeof(int));// 
        edt.Columns.Add("DoType", typeof(string));// 
        edt.Columns.Add("ProcName", typeof(string));// 
        edt.Columns.Add("MenuName", typeof(string));// 
        edt.Columns.Add("JsonColumns", typeof(string));// 

        // 获取对应的字段设定
        DataSet ds = new DataSet();
        if (menuName != "F_SchedulePeriodSetup")
            ds = await GetTableColumn(Db, LangID, UserID, menuName, null);
        DataTable tableColumn = ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable();

        List<DataSourceList> dataSourceLists = new List<DataSourceList>();
        foreach (DataRow dr in tableColumn.Rows)
        {
            // 验证导入列是否一致
            if (!mydt.Columns.Contains(dr["field"].ToString()) && doType != "Update")
            {
                throw new Exception("导入模板不匹配或模板字段已更新，请重新下载导入模板");
            }

            string dataSource = dr["dataSource"].ToString();
            string field = dr["field"].ToString();
            if (!string.IsNullOrEmpty(dataSource))
            {
                List<DataSourceList> dataSourceList = GetDataSourceLists(Db, field, dataSource);
                dataSourceLists.AddRange(dataSourceList);
            }
        }
        int AllNums = 0;
        // 判断是否为流程，流程直接返回对应信息 
        string IsFlow = "N";


        StringBuilder jsonListBuilder = new StringBuilder();
        jsonListBuilder.Append("[");

        //循环读取的Excel行
        for (int i = FirstI; i < mydt.Rows.Count; i++)
        {
            AllNums++;
            StringBuilder jsonBuilder = new StringBuilder();
            jsonBuilder.Append("{");
            string errorMsg = string.Empty;
            //// 遍历列
            foreach (DataColumn dc in mydt.Columns)
            {
                if (dc.ColumnName != "Process" && dc.ColumnName != "Comments" && dc.ColumnName != "" && dc.ColumnName != "ExcelNums")
                {
                    string value = mydt.Rows[i][dc.ColumnName].ToString();
                    string value_label = value;

                    //判断对应的字段设定
                    DataRow[] rows = ds.Tables.Count > 0 ? tableColumn.Select(" field = '" + dc.ColumnName + "' ") : null;

                    if (rows != null && rows.Length > 0)
                    {
                        DataRow fieldRow = rows[0];
                        // 必填字段为空
                        if (fieldRow["required"].ToString() == "true" && string.IsNullOrEmpty(value))
                        {
                            errorMsg = errorMsg + fieldRow["label"].ToString() + "不能为空；";
                        }
                        string dataSource = fieldRow["dataSource"].ToString();

                        //下拉数据反读值
                        //if (!string.IsNullOrEmpty(dataSource) && !string.IsNullOrEmpty(value)
                        //    // 默认值处理
                        //    && !(value == "*" && menuName.StartsWith("F_StaffChange")))
                        //{
                        //    //// Dept
                        //    if (dataSource.StartsWith("OrgTreeWith"))
                        //    {
                        //        string DeptID = string.Empty;
                        //        // 部门需要一级一级写入，不判断父级层级
                        //        if (menuName != "F_AllDept" && menuName != "F_Dept")
                        //        {
                        //            try
                        //            {
                        //                //验证部门编号填写是否错误
                        //                string DeptTest = string.Format(@"select STUFF((select ','+value
                        //                                         from dbo.Fs1_GHR30_SplitString('{0}',',') 
                        //                                         where value != '' and value not in (select DeptNo from Ghro_Dept where ISEnable=1)
                        //                                         for XML path(''),type).value('.','nvarchar(max)'),1,1,'');",
                        //                                              value);
                        //                DeptTest = DataAccess.SelectSingleValueNotKey("SqlGhr30" + "Demo", DeptTest).ToString();
                        //                if (!String.IsNullOrEmpty(DeptTest))
                        //                {
                        //                    errorMsg = errorMsg + fieldRow["label"].ToString() + "输入值(" + DeptTest + ")错误；";
                        //                }
                        //                else
                        //                {
                        //                    string deptExists = string.Format(@"select stuff((select ',' + CONVERT(nvarchar(10),DeptID) 
                        //                                         from Ghro_Dept 
                        //                                         where IsEnable=1 
                        //                                           and DeptNo in (select value from dbo.Fs1_GHR30_SplitString('{0}',',')) 
                        //                                    for XML path(''),type).value('.','nvarchar(max)'),1,1,'');",
                        //                                         value);
                        //                    DeptID = DataAccess.SelectSingleValueNotKey("SqlGhr30" + "Demo", deptExists).ToString();
                        //                    value = DeptID;
                        //                    //if (String.IsNullOrEmpty(value))
                        //                    //    errorMsg = errorMsg + fieldRow["label"].ToString() + "输入值错误；";
                        //                }
                        //            }
                        //            catch (Exception)
                        //            {
                        //                errorMsg = errorMsg + fieldRow["label"].ToString() + "输入值错误；";
                        //            }
                        //        }
                        //    }
                        //    //// Staff
                        //    else if (dataSource.StartsWith("StaffWith"))
                        //    {
                        //        string StaffID = string.Empty;
                        //        try
                        //        {
                        //            //验证工号填写是否错误
                        //            string StaffTest = string.Format(@"select STUFF((select ','+value
                        //                                         from dbo.Fs1_GHR30_SplitString('{0}',',') 
                        //                                         where value != '' and value not in (select StaffNo from Ghra_Staff where ISEnable=1)
                        //                                         for XML path(''),type).value('.','nvarchar(max)'),1,1,'');",
                        //                                          value);
                        //            StaffTest = DataAccess.SelectSingleValueNotKey("SqlGhr30" + "Demo", StaffTest).ToString();
                        //            if (!String.IsNullOrEmpty(StaffTest))
                        //            {
                        //                errorMsg = errorMsg + fieldRow["label"].ToString() + "输入值(" + StaffTest + ")错误；";
                        //            }
                        //            else
                        //            {
                        //                string staffExists = string.Format(@"select stuff((select ',' + CONVERT(nvarchar(10),StaffID) 
                        //                                         from Ghra_Staff 
                        //                                         where IsEnable=1 
                        //                                           and StaffNo in (select value from dbo.Fs1_GHR30_SplitString('{0}',',')) 
                        //                                    for XML path(''),type).value('.','nvarchar(max)'),1,1,'');",
                        //                                         value);

                        //                StaffID = DataAccess.SelectSingleValueNotKey("SqlGhr30" + "Demo", staffExists).ToString();
                        //                value = StaffID;
                        //                if (String.IsNullOrEmpty(value))
                        //                    errorMsg = errorMsg + fieldRow["label"].ToString() + "输入值错误；";
                        //            }
                        //        }
                        //        catch (Exception)
                        //        {
                        //            errorMsg = errorMsg + fieldRow["label"].ToString() + "输入值错误；";
                        //        }
                        //    }
                        //    else
                        //    {
                        //        try
                        //        {
                        //            // 多选
                        //            if (fieldRow["elementType"].ToString().LastIndexOf("multiple") == -1)
                        //            {
                        //                DataSourceList nList = dataSourceLists.First(t => t.name == value && t.field == dc.ColumnName);
                        //                if (nList != null)
                        //                {
                        //                    if (String.IsNullOrEmpty(nList.id))
                        //                        errorMsg = errorMsg + fieldRow["label"].ToString() + "输入值错误；";
                        //                    else
                        //                        value = nList.id;
                        //                }
                        //            }
                        //            else
                        //            {
                        //                string errorValue = string.Empty;
                        //                string newValue = "[";
                        //                foreach (var sp in value.Split(','))
                        //                {
                        //                    DataSourceList nList = dataSourceLists.First(t => t.name == sp && t.field == dc.ColumnName);

                        //                    if (nList != null)
                        //                    {
                        //                        if (String.IsNullOrEmpty(nList.id))
                        //                            errorValue = errorValue + fieldRow["label"].ToString() + ",";
                        //                        else
                        //                            newValue = newValue + "\"" + nList.id + "\",";
                        //                    }
                        //                    else
                        //                    {
                        //                        errorValue = errorValue + fieldRow["label"].ToString() + ",";
                        //                    }
                        //                }
                        //                if (!string.IsNullOrEmpty(errorValue))
                        //                    errorMsg = errorMsg + fieldRow["label"].ToString() + "输入值(" + errorValue + ")错误；";
                        //                else
                        //                    value = newValue.TrimEnd(',') + "]";
                        //            }
                        //        }
                        //        catch (Exception)
                        //        {
                        //            errorMsg = errorMsg + fieldRow["label"].ToString() + "输入值错误；";
                        //            value = "0";
                        //        }
                        //    }
                        //}
                        // 部门需要一级一级写入，不判断父级层级
                        if (menuName != "F_AllDept" && menuName != "F_Dept"
                            && fieldRow["field"].ToString().IndexOf("ParentDeptID") > 0
                            && !(value == "*" && menuName.StartsWith("F_StaffChange"))
                            )
                        {
                            // 是否输入整型
                            if (fieldRow["dataType"].ToString() == "int" && !string.IsNullOrEmpty(value))
                            {
                                int nums1;
                                if (!int.TryParse(value, out nums1))
                                    errorMsg = errorMsg + fieldRow["label"].ToString() + "需为整数；";
                            }
                            // 是否输入数值
                            if ((fieldRow["dataType"].ToString() == "decimal" || fieldRow["dataType"].ToString() == "number")
                                && !string.IsNullOrEmpty(value))
                            {
                                double nums2;
                                if (!double.TryParse(value, out nums2))
                                    errorMsg = errorMsg + fieldRow["label"].ToString() + "需为数值；";
                            }
                        }
                        // 是否输入日期
                        if (fieldRow["dataType"].ToString().StartsWith("date")
                            && !(value == "*" && menuName.StartsWith("F_StaffChange"))
                            )
                        {
                            if (!string.IsNullOrEmpty(value))
                            {
                                try
                                {
                                    DateTime date1;
                                    if (!DateTime.TryParse(value, out date1))
                                        errorMsg = errorMsg + fieldRow["label"].ToString() + "需为日期格式；";
                                    else if (fieldRow["dataType"].ToString() == "date")
                                    {
                                        value = date1.ToString("yyyy-MM-dd");
                                    }
                                }
                                catch (Exception)
                                {
                                    errorMsg = errorMsg + fieldRow["label"].ToString() + "(" + value + ")需为日期格式；";
                                }
                            }
                            else
                            {

                            }
                        }
                        if (fieldRow["elementType"].ToString().LastIndexOf("multiple") == -1)
                        {
                            jsonBuilder.AppendFormat("\"{0}\":\"{1}\",",
                                                    dc.ColumnName, value.Replace('"', '‘').Replace("'", "‘").Trim());
                        }
                        else
                        {
                            jsonBuilder.AppendFormat("\"{0}\":{1},",
                                                    dc.ColumnName, string.IsNullOrEmpty(value.Trim()) ? "null" : value.Trim());
                        }
                    }
                    else
                    {
                        jsonBuilder.AppendFormat("\"{0}\":\"{1}\",",
                                 dc.ColumnName, value.Replace('"', '‘').Replace("'", "‘").Trim());
                    }

                    // ESS申请 下拉返回label 值
                    if (IsFlow == "Y")
                    {
                        jsonBuilder.AppendFormat("\"{0}\":\"{1}\",",
                     dc.ColumnName + "_Label", value_label.Trim());
                    }
                }
            }

            if (jsonBuilder.Length > 1)
                jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
            jsonBuilder.Append("}");

            //判断 是否有错误
            if (!string.IsNullOrEmpty(errorMsg))
            {
                mydt.Rows[i]["Comments"] = errorMsg;
                ErrorNum++;
            }
            else
            {
                DataRow ndr = edt.NewRow();
                ndr["ExcelNums"] = mydt.Rows[i]["ExcelNums"].ToString();
                ndr["GID"] = guidN;
                ndr["LangID"] = LangID;
                ndr["UserID"] = UserID;
                ndr["DoType"] = doType;
                ndr["ProcName"] = procName;
                ndr["MenuName"] = menuName;
                ndr["JsonColumns"] = jsonBuilder.ToString();
                edt.Rows.Add(ndr);
                // 写入正确的json
                jsonListBuilder.Append(jsonBuilder);
                jsonListBuilder.Append(",");
            }
        }
        if (jsonListBuilder.Length > 1)
            jsonListBuilder.Remove(jsonListBuilder.Length - 1, 1);
        jsonListBuilder.Append("]");



        // 写入Excel待读取
        string createTempSQL = "ExcelNums int,GID nvarchar(100),LangID int,UserID int,DoType nvarchar(200),ProcName nvarchar(200),MenuName nvarchar(200),JsonColumns nvarchar(max)";
        string updateSQL = @"insert into [dbo].[Ghrs_ExcelImportWait]
                                ([GID],[LangID],[UserID],[DoType], [ProcName], [MenuName], [JsonColumns],[SortNo])
                                select[GID],[LangID],[UserID],[DoType], [ProcName],[MenuName] ,[JsonColumns],ExcelNums
                                from #CTemplateTable";

        Other.BulkUpdateData("SqlGhr30" + "Demo", edt, createTempSQL, updateSQL);
        //Db.Ado.GetDataTable("SqlGhr30" + "Demo", edt, createTempSQL, updateSQL);

        string EssResult = string.Empty;
        //调用存储过程
        // GHR 导入  
        if (IsFlow == "N")
        {
            string ReadSQL = string.Format(@"exec[dbo].[PT_GHR30_ReadExcelData] '{0}',{1},{2} ", guidN, LangID, UserID);
            await Db.Ado.ExecuteCommandAsync("SqlGhr30" + "Demo", ReadSQL);

            string resultSQL = string.Format(@"select SortNo,ErrorMsg,WarningMsg,SuccessMsg
	                                            from Ghrs_ExcelImportResult
	                                            where GID = '{0}'  
                                                  and ISNULL(ErrorMsg,'') != '' ", guidN);
            DataTable resultDt = await Db.Ado.GetDataTableAsync(resultSQL);
            //写入错误信息
            foreach (DataRow resultDR in resultDt.Rows)
            {
                string SortNo = resultDR["SortNo"].ToString();
                string ErrorMsg = "";
                ErrorMsg = resultDR["ErrorMsg"].ToString();
                mydt.Select(@"ExcelNums = '" + SortNo + "'")[0]["Comments"] = ErrorMsg;
                ErrorNum++;
            }
        }
        else
        {
            string ReadSQL = string.Format(@"exec[dbo].[PT_GHR30_EssFlowReadExcelData] '{0}',{1},{2} ", guidN, LangID, UserID);
            DataTable resultDt = await Db.Ado.GetDataTableAsync(ReadSQL);

            StringBuilder jsonBuilder = new StringBuilder();
            jsonBuilder.Append("[");

            foreach (DataRow resultDR in resultDt.Rows)
            {
                jsonBuilder.Append(resultDR["ResultJson"].ToString() + ",");
                string SortNo = resultDR["SortNo"].ToString();
                string ErrorMsg = "";
                ErrorMsg = resultDR["FlowApplyDetailInfo"].ToString();
                if (ErrorMsg != String.Empty)
                {
                    ErrorNum++;
                    mydt.Select(@"ExcelNums = '" + SortNo + "'")[0]["Comments"] = ErrorMsg;
                }
            }

            if (jsonBuilder.Length > 1)
                jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
            jsonBuilder.Append("]");

            EssResult = jsonBuilder.ToString();
        }

        SuccessNum = AllNums - ErrorNum;
        File.Delete(FilePath); //先删除，再保存。 
                               //将写入结果导出为Excel
        GetExcelByDataTable(mydt, FilePath);

        // 流程直接返回读取信息
        if (IsFlow == "Y")
        {   // 流程直接返回读取信息
            return EssResult;
        }
        try
        {
            DataTable ErrorTable = mydt;// GetNewDataTable(mydt, "Process = '' ", "");
                                        //DataRow[] foundRow;
                                        //foundRow = ErrorTable.Select("Comments != ''");
                                        //if (foundRow != null && foundRow.Count() > 0)
                                        //{
                                        //    foreach (DataRow row in foundRow)
                                        //    {
                                        //        ErrorTable.Rows.Remove(row);
                                        //    }
                                        //}
                                        //ErrorTable.AcceptChanges();//对DataTable（全部）操作完之后，一定要执行这一步，否则结果不保存

            //if (ErrorTable.Rows.Count > 0)
            //result = GHR.Common.DBCommon.ToJson(ErrorTable);
        }
        catch (Exception ex)
        {
            result = "[{\"readerror\":\"" + ex.Message + "\"}]";
        }
        return result;
    }

    /// <summary>
    /// 根据Excel和Sheet返回DataTable
    /// </summary>
    /// <param name="filePath">Excel文件地址</param>
    /// <param name="sheetIndex">Sheet索引</param>
    /// <returns>DataTable</returns>
    public static DataTable GetDataTable(string filePath, int sheetIndex, Dictionary<string, object> NameContrast)
    {
        return GetDataSet(filePath, sheetIndex, NameContrast).Tables[0];
    }

    /// <summary>
    /// 根据Excel返回DataSet
    /// </summary>
    /// <param name="filePath">Excel文件地址</param>
    /// <param name="sheetIndex">Sheet索引，可选，默认返回所有Sheet</param>
    /// <returns>DataSet</returns>
    public static DataSet GetDataSet(string filePath, int? sheetIndex, Dictionary<string, object> NameContrast)
    {
        DataSet ds = new DataSet();
        IWorkbook fileWorkbook;
        string FileExt = Path.GetExtension(filePath);
        using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
        {
            if (FileExt == ".xls")
            {
                try
                {
                    //fileWorkbook = new HSSFWorkbook(fs);
                    fileWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else if (FileExt == ".xlsx")
            {
                try
                {
                    fileWorkbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else fileWorkbook = null;
        }

        for (int i = 0; i < fileWorkbook.NumberOfSheets; i++)
        {
            if (sheetIndex != null && sheetIndex != i)
                continue;
            DataTable dt = new DataTable();
            ISheet sheet = fileWorkbook.GetSheetAt(i);

            //表头
            IRow header = sheet.GetRow(sheet.FirstRowNum);
            List<int> columns = new List<int>();
            for (int j = 0; j < header.LastCellNum; j++)
            {
                object obj = new object();
                if (FileExt == ".xls")
                {
                    obj = GetValueTypeForXLS(header.GetCell(j) as HSSFCell);
                }
                else if (FileExt == ".xlsx")
                {
                    obj = GetValueTypeForXLS(header.GetCell(j) as XSSFCell);
                }
                else { obj = null; }

                if (obj == null || obj.ToString() == string.Empty)
                {
                    dt.Columns.Add(new DataColumn("Columns" + j.ToString()));
                }
                else
                {
                    dt.Columns.Add(new DataColumn(obj.ToString()));
                    //try
                    //{
                    //    dt.Columns.Add(new DataColumn(NameContrast[obj.ToString()].ToString()));
                    //}
                    //catch (Exception)
                    //{
                    //    dt.Columns.Add(new DataColumn(obj.ToString()));
                    //} 
                }
                columns.Add(j);
            }
            //数据
            IEnumerator rows = sheet.GetEnumerator();
            int n = sheet.FirstRowNum + 1;
            while (rows.MoveNext())
            {
                DataRow dr = dt.NewRow();
                bool hasValue = false;
                foreach (int K in columns)
                {
                    try
                    {
                        dr[K] = GetValueTypeForXLS(sheet.GetRow(n).GetCell(K) as HSSFCell);
                        if (FileExt == ".xls")
                        {
                            dr[K] = GetValueTypeForXLS(sheet.GetRow(n).GetCell(K) as HSSFCell);
                        }
                        else if (FileExt == ".xlsx")
                        {
                            dr[K] = GetValueTypeForXLS(sheet.GetRow(n).GetCell(K) as XSSFCell);
                        }
                        else dr[K] = null;
                        if (dr[K] != null && dr[K].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    catch
                    {
                        hasValue = false;
                    }
                }
                if (hasValue)
                {
                    dt.Rows.Add(dr);
                }
                n++;
            }
            ds.Tables.Add(dt);
        }

        return ds;
    }

    /// <summary>
    /// 根据DataTable导出Excel
    /// </summary>
    /// <param name="dt">DataTable</param>
    /// <param name="file">保存地址</param>
    public static bool GetExcelByDataTable(DataTable dt, string file)
    {
        DataSet ds = new DataSet();
        DataTable NewDT = dt.Copy();
        ds.Tables.Add(NewDT);
        return GetExcelByDataSet(ds, file);
    }

    /// <summary>
    /// 根据DataSet导出Excel
    /// </summary>
    /// <param name="ds">DataSet</param>
    /// <param name="file">保存地址</param>
    public static bool GetExcelByDataSet(DataSet ds, string file)
    {
        try
        {
            IWorkbook fileWorkbook;
            if (Path.GetExtension(file) == ".xls")
            {
                fileWorkbook = new HSSFWorkbook();
            }
            else
            {
                fileWorkbook = new XSSFWorkbook();
            }
            int index = 0;
            foreach (DataTable dt in ds.Tables)
            {
                index++;
                ISheet sheet = fileWorkbook.CreateSheet("Sheet" + index);

                //表头
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(dt.Columns[i].ColumnName);
                }
                // 隐藏首行
                if (IsHideTopRow)
                    row.HeightInPoints = 1;

                //数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                }
            }

            //转为字节数组
            MemoryStream stream = new MemoryStream();
            fileWorkbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }
    /// <summary>
    /// 获取新增表的字段设定(导入需要)
    /// </summary>
    /// <param name="menuName"></param>
    /// <returns></returns>
    public static async Task<DataSet> GetTableColumn(ISqlSugarClient Db, int langId, int userId, string menuName, string jsonParam)
    {
        string SQL = String.Format(@"exec [PT_GHR30_GetExcelEditColumn] '{1}',0,{2},{3}, '{0}','Import'", menuName, jsonParam, langId, userId);
        // 第一张表 表头设定
        // 第二张表 写入默认值
        DataSet ds = await Db.Ado.GetDataSetAllAsync("SqlGhr30" + "Demo", SQL);

        return ds;
    }

    /// <summary>
    /// 根据单元格将内容返回为对应类型的数据
    /// </summary>
    /// <param name="cell">单元格</param>
    /// <returns>数据</returns>
    private static object GetValueTypeForXLS(HSSFCell cell)
    {
        if (cell == null)
            return null;
        switch (cell.CellType)
        {
            case CellType.Blank: //BLANK:
                return null;
            case CellType.Boolean: //BOOLEAN:
                return cell.BooleanCellValue;
            case CellType.Numeric: //NUMERIC:
                {
                    if (DateUtil.IsCellDateFormatted(cell))
                        return cell.DateCellValue.Value.ToString("yyyy-MM-dd HH:mm");
                    else
                        return cell.NumericCellValue;
                }
            case CellType.String: //STRING:
                return cell.StringCellValue;
            case CellType.Error: //ERROR:
                return cell.ErrorCellValue;
            case CellType.Formula: //FORMULA:
            default:
                return "=" + cell.CellFormula;
        }
    }
    /// <summary>
    /// 根据单元格将内容返回为对应类型的数据
    /// </summary>
    /// <param name="cell">单元格</param>
    /// <returns>数据</returns>
    private static object GetValueTypeForXLS(XSSFCell cell)
    {
        if (cell == null)
            return null;
        switch (cell.CellType)
        {
            case CellType.Blank: //BLANK:
                return null;
            case CellType.Boolean: //BOOLEAN:
                return cell.BooleanCellValue;
            case CellType.Numeric: //NUMERIC:
                {
                    if (DateUtil.IsCellDateFormatted(cell))
                        return cell.DateCellValue.Value.ToString("yyyy-MM-dd HH:mm");
                    else
                        return cell.NumericCellValue;
                }
            case CellType.String: //STRING:
                return cell.StringCellValue;
            case CellType.Error: //ERROR:
                return cell.ErrorCellValue;
            case CellType.Formula: //FORMULA:
            default:
                return "=" + cell.CellFormula;
        }
    }


    #region 其他
    public class Other
    {
        /// <summary>
        /// 数字转ADC字母
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string Num2ABCletter([Range(1, 300)] int value)
        {
            List<string> Level = new List<string>
        {
            "A","B","C","D","E","F","G","H","I","J",
            "K","L","M","N","O","P","Q","R","S","T",
            "U","V","W","X","Y","Z",
        };

            value = value - 1;
            System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding();
            int remainder = value % 26;//余数
            int front = (value - remainder) / 26;//
            if (front == 0)
            {
                return Level[remainder];
            }
            else if (front < 26)
            {
                return Level[front - 1] + Level[remainder];
            }
            else
            {
                return Num2ABCletter(front) + Level[remainder];
            }
        }

        /// <summary>
        /// SqlBulkCopy 批量更新数据
        /// </summary>
        /// <param name="dataTable">数据集</param>
        /// <param name="crateTemplateSql">临时表创建字段</param>
        /// <param name="updateSql">更新语句</param>
        public static void BulkUpdateData(string con, DataTable dataTable, string crateTemplateSql, string updateSql)
        {

            // ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
            //using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings[con].ConnectionString))
            //{
            //    using (var command = new SqlCommand("", conn))
            //    {
            //        try
            //        {
            //            conn.Open();

            //            //数据库并创建一个临时表来保存数据表的数据 
            //            Random ran = new Random();
            //            int n = ran.Next(100, 1000);
            //            command.CommandText = String.Format("  CREATE TABLE #CTemplateTable" + n.ToString() + " ({0})", crateTemplateSql);
            //            command.ExecuteNonQuery();

            //            //使用SqlBulkCopy 加载数据到临时表中
            //            using (var bulkCopy = new SqlBulkCopy(conn))
            //            {
            //                foreach (DataColumn dcPrepped in dataTable.Columns)
            //                {
            //                    bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);
            //                }

            //                bulkCopy.BulkCopyTimeout = 660;
            //                bulkCopy.DestinationTableName = "#CTemplateTable" + n.ToString();
            //                bulkCopy.WriteToServer(dataTable);
            //                bulkCopy.Close();
            //            }
            //            updateSql = updateSql.Replace("#CTemplateTable", "#CTemplateTable" + n.ToString());
            //            // 执行Command命令 使用临时表的数据去更新目标表中的数据  然后删除临时表
            //            command.CommandTimeout = 300;
            //            command.CommandText = updateSql;
            //            command.ExecuteNonQuery();
            //        }
            //        finally
            //        {
            //            conn.Close();
            //        }
            //    }
            //}
        }
    }

    #endregion
}