everlight.eap/backend/DllEapCommon/NPOI/DataExportHelper.cs

using DllEapEntity.Dtos;
using DllEapEntity.OFILM;
using DllUfpEntity;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
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.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DllEapCommon.NPOI
{
    /// <summary>
    /// 列表导出到Excel
    /// </summary>
    public class DataExportHelper
    {
        /// <summary>
        /// 导出机台时段产量数据
        /// </summary>
        /// <param name="dto"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public static byte[] ExportRunDataToExcel(AntdComplexTableDto dto, ref string errorinfo)
        {
            var dt = dto.Datas;
            if (dt == null || dt.Rows.Count <= 0)
            {
                errorinfo = "待导出数据为空";
                return null;
            }
            var workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet("sheet1");
            var currentRow = 0;
            var currentCol = 0;
            var baseHeaderWidth = 800;
            var baseRowWidth = 800;
            var headerMargin = 900;
            var rowMargin = 500;
            var contentFontSize = 11;
            var palette = workbook.GetCustomPalette();
            palette.SetColorAtIndex((short)60, 255, 242, 204);
            palette.SetColorAtIndex((short)61, 226, 239, 218);
            palette.SetColorAtIndex((short)62, 230, 247, 255);
            palette.SetColorAtIndex((short)63, 217, 217, 217);
            // 表格内容样式
            ICellStyle style = CreateStyle(workbook, (short)9, contentFontSize);

            ICellStyle headerStyle = CreateStyle(workbook, palette.FindColor(226, 239, 218).Indexed, 12, true);

            ICellStyle coloredStyle = CreateStyle(workbook, palette.FindColor(230, 247, 255).Indexed, contentFontSize);
            // 头部
            var headerRow = sheet.CreateRow(currentRow);

            var currHeaderWidth = 0;
            foreach (var col in dto.Columns)
            {
                currHeaderWidth = baseHeaderWidth * col.ColName.Length + headerMargin;
                sheet.SetColumnWidth(currentCol, currHeaderWidth);
                ICell headerCell = headerRow.CreateCell(currentCol);
                if (col.ColName == "MacRowSpan" || col.ColName == "ModelRowSpan")
                    continue;
                DateTime tempDate;
                var cellValue = col.ColName;
                if (DateTime.TryParse(col.ColName, out tempDate))
                {
                    cellValue = tempDate.ToString("yyyy-MM-dd HH:mm");
                    sheet.SetColumnWidth(currentCol, 200 * col.ColName.Length + 700);
                }
                headerCell.SetCellValue(cellValue);
                headerCell.CellStyle = headerStyle;
                headerRow.Cells.Add(headerCell);
                currentCol++;
            }

            // Body
            currentRow = 1;
            var startMacRow = 0;
            var endMacRow = 0;
            var startModelRow = 0;
            var endModelRow = 0;
            var macName = string.Empty;
            var modelName = string.Empty;
            var regions = new List<CellRangeAddress>();
            foreach (DataRow row in dt.Rows)
            {
                IRow bodyRow = sheet.CreateRow(currentRow);
                currentCol = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    if (col.ColumnName == "MacRowSpan" || col.ColumnName == "ModelRowSpan")
                        continue;
                    ICell cell = bodyRow.CreateCell(currentCol);
                    double tempDouble;
                    var cellValue = row[col.ColumnName].ToString();
                    if (double.TryParse(cellValue, out tempDouble))
                    {
                        if (row["指标"].ToString() == "产量" || row["指标"].ToString() == "故障次数")
                        {
                            cellValue = row[col.ColumnName].ToString();
                        }
                        else if (row["指标"].ToString() == "故障时长")
                        {
                            cellValue = Math.Round(tempDouble, 2).ToString();
                        }
                        else
                        {
                            cellValue = Math.Round(tempDouble * 100, 2).ToString() + "%";
                        }
                    }
                    cell.SetCellValue(cellValue);
                    cell.CellStyle = style;

                    if (col.ColumnName != "设备ID" && col.ColumnName != "设备类型" && (row["指标"].ToString() == "产量" || row["指标"].ToString() == "稼动率"))
                    {

                        cell.CellStyle = coloredStyle;
                    }

                    if (col.ColumnName == "设备ID" || col.ColumnName == "设备类型")
                    {
                        var singleWidth = 300;
                        var width = singleWidth * (row[col.ColumnName].ToString().Length) + rowMargin;
                        if (width > currHeaderWidth)
                        {
                            sheet.SetColumnWidth(currentCol, width);
                            currHeaderWidth = width;
                        }
                    }
                    else if (col.ColumnName == "指标")
                    {
                        var singleWidth = 600;
                        var width = singleWidth * (row[col.ColumnName].ToString().Length) + rowMargin;
                        if (width > currHeaderWidth)
                        {
                            sheet.SetColumnWidth(currentCol, width);
                            currHeaderWidth = width;
                        }
                    }
                    bodyRow.Cells.Add(cell);
                    currentCol++;
                }
                if (row["设备类型"].ToString() == modelName && currentRow != dt.Rows.Count)
                {
                    endModelRow++;
                }
                else
                {
                    if (!string.IsNullOrEmpty(modelName))
                    {
                        if (currentRow == dt.Rows.Count)
                        {
                            endModelRow++;
                        }
                        regions.Add(new CellRangeAddress(startModelRow, endModelRow, 0, 0));
                    }
                    startModelRow = currentRow;
                    endModelRow = currentRow;
                    modelName = row["设备类型"].ToString();
                }
                if (row["设备ID"].ToString() == macName && currentRow != dt.Rows.Count)
                {
                    endMacRow++;
                }
                else
                {
                    if (!string.IsNullOrEmpty(macName))
                    {
                        if (currentRow == dt.Rows.Count)
                        {
                            endMacRow++;
                        }
                        regions.Add(new CellRangeAddress(startMacRow, endMacRow, 1, 1));
                    }
                    startMacRow = currentRow;
                    endMacRow = currentRow;
                    macName = row["设备ID"].ToString();
                }
                currentRow++;
            }

            if (regions != null && regions.Count > 0)
            {
                foreach (var item in regions)
                {
                    sheet.AddMergedRegion(item);
                }
            }

            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            var buffer = ms.ToArray();
            return buffer;
        }

        public static IWorkbook SULToExcel(List<DllEapEntity.SystemUpdataLogDto> dto)
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            List<string> column = new List<string> { "版本号", "更新时间", "更新环境", "更新类型", "更新内容", "业务对接部门", "对接人", "更新人","测试结果","测试人员","备注" };
            int rowIndex = 1, piIndex = 0;
            var headerCellStyle = CreateStyle(workbook, fontSize: 11, bold: true);
            var cellStyle = CreateStyle(workbook, fontSize: 11);
            var prenCellStyle = CreateStyle(workbook, fontSize: 11, type: "0");
            var headerRow = sheet.CreateRow(0);
            foreach (var item in column)
            {
                var headerCell = headerRow.CreateCell(piIndex, CellType.String);
                headerCell.SetCellValue(item);
                headerCell.CellStyle = headerCellStyle;
                sheet.SetColumnWidth(piIndex, 800 * item.Length + 700);
                piIndex++;

            }
            foreach (var item in dto)
            {
                int length = 0;
                int count = item.list.Count();
                while (length < count)
                {
                    var row = sheet.CreateRow(rowIndex);
                    piIndex = 0;
                  
                    while (piIndex<column.Count())
                    {
                        var cell = row.CreateCell(piIndex);
                        switch (piIndex)
                        {
                            case 0:
                                cell.SetCellValue(item.Version);
                                cell.CellStyle = cellStyle;
                                break;
                            case 1:
                                cell.SetCellValue(item.FDate);
                                cell.CellStyle = cellStyle;
                                break;
                            case 2:
                                if (item.Environment == 0)
                                {
                                    cell.SetCellValue("测试版");
                                }
                                else
                                {
                                    cell.SetCellValue("正式版");
                                }
                                cell.CellStyle = cellStyle;
                                break;
                            case 3:
                                if (item.list[length].UpdateType == 0)
                                {
                                    cell.SetCellValue("新增");
                                }
                                else
                                {
                                    cell.SetCellValue("修改");
                                }
                                cell.CellStyle = cellStyle;
                                break;
                            case 4:
                                cell.SetCellValue(item.list[length].Content);

                                cell.CellStyle = cellStyle;
                                break;
                            case 5:
                                cell.SetCellValue(item.list[length].Department);
                                cell.CellStyle = cellStyle;
                                break;
                            case 6:
                                cell.SetCellValue(item.list[length].Employee);

                                cell.CellStyle = cellStyle;
                                break;
                            case 7:
                                cell.SetCellValue(item.list[length].Developer);
                                cell.CellStyle = cellStyle;
                                break;
                            case 8:
                                cell.SetCellValue(item.list[length].Result);
                                cell.CellStyle = cellStyle;
                                break;
                            case 9:
                                cell.SetCellValue(item.list[length].TE);
                                cell.CellStyle = cellStyle;
                                break;
                            case 10:
                                cell.SetCellValue(item.list[length].Remark);
                                cell.CellStyle = cellStyle;
                                break;
                            default:
                                break;
                        }
                        piIndex++;
                    }
                    length++;
                    rowIndex++;


                }
                if (length>1)
                {
                    for (int i = 0; i < 3; i++)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(rowIndex - length, rowIndex - 1, i, i));
                    }
                }

            }
            return workbook;
        }

        public static IWorkbook ExportWeek(IEnumerable<CountTotal> data, ref string errorinfo)
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            List<string> column = new List<string> { "园区", "楼层", "制程代码", "设备类型", "设备ID", "指标" };
            for (int i = 0; i < 24; i++)
            {
                column.Add(i.ToString("00") + ":00:00");
            }
            //IRow headerRow = sheet.CreateRow(0);
            int rowIndex = 1, piIndex = 0;
            var headerCellStyle = CreateStyle(workbook, fontSize: 11, bold: true);
            var cellStyle = CreateStyle(workbook, fontSize: 11);
            var prenCellStyle = CreateStyle(workbook, fontSize: 11, type: "0");
            var headerRow = sheet.CreateRow(0);
            foreach (var item in column)
            {
                var headerCell = headerRow.CreateCell(piIndex,CellType.String);
                headerCell.SetCellValue(item);
                headerCell.CellStyle = headerCellStyle;
                sheet.SetColumnWidth(piIndex, 800 * item.Length + 700);
                piIndex++;
                
            }
            
            foreach (var item in data)
            {
                int length = 0;
                while (length < 6)
                {
                    
                    var row = sheet.CreateRow(rowIndex);
                    int i = 0;
                    while(i<column.Count)
                    {
                        var dataCell = row.CreateCell(i);
                        switch (i)
                        {
                            case 0:
                                
                                    dataCell.SetCellValue(item.Factory);
                                    dataCell.CellStyle=cellStyle;
                                
                                break;
                            case 1:
                                
                                    dataCell.SetCellValue(item.Floor);
                                    dataCell.CellStyle = cellStyle;
                                    
                                
                                break;
                            case 2:
                                
                                    dataCell.SetCellValue(item.PCode);
                                    dataCell.CellStyle = cellStyle;
                                    
                                
                                break;
                            case 3:
                                
                                    dataCell.SetCellValue(item.Model);
                                    dataCell.CellStyle = cellStyle;
                                    
                                
                                break;
                            case 4:
                                
                                    dataCell.SetCellValue(item.FCode);
                                    dataCell.CellStyle = cellStyle;
                                   
                                
                                break;
                            case 5:
                                dataCell.SetCellValue(item.Datas[length].Index);
                                dataCell.CellStyle = cellStyle;
                                break;
                            default:
                                switch (length)
                                {
                                    case 0:
                                    case 4:
                                        dataCell.SetCellValue(Convert.ToInt32(item.Datas[length].data[i - 6]));
                                        dataCell.CellStyle = cellStyle;
                                        break;
                                    case 1:
                                    case 2:
                                    case 3:
                                        dataCell.SetCellValue(Convert.ToDouble(item.Datas[length].data[i - 6].Replace("%", "")) /100);
                                        dataCell.CellStyle = prenCellStyle;
                                        break;
                                    case 5:
                                        dataCell.SetCellValue(Convert.ToDouble(item.Datas[length].data[i - 6]));
                                        dataCell.CellStyle = cellStyle;
                                        break;
                                    default:
                                        break;
                                }
                                break;
                        }
                        
                        i++;
                        
                    }
                    rowIndex++;
                    length++;
                }
                if (item.Factory == "汇总")
                {
                    sheet.AddMergedRegion(new CellRangeAddress(rowIndex-6, rowIndex-1, 0,4));
                   
                }
                else
                {
                    for (int i = 0; i < 5; i++)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(rowIndex-6, rowIndex-1 , i, i));
                    }
                }

            }
            return workbook;
        }

        





        /// <summary>
        /// 导出预测数据
        /// </summary>
        /// <param name="dto"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public static byte[] ExportForecastToExcel(AntdComplexTableDto dto, ref string errorinfo)
        {
            var dt = dto.Datas;
            if (dt == null || dt.Rows.Count <= 0)
            {
                errorinfo = "待导出数据为空";
                return null;
            }
            var workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet("sheet1");
            var currentRow = 0;
            var currentCol = 0;
            var baseHeaderWidth = 800;
            var baseRowWidth = 800;
            var headerMargin = 900;
            var rowMargin = 500;
            var contentFontSize = 11;
            var palette = workbook.GetCustomPalette();
            palette.SetColorAtIndex((short)60, 255, 242, 204);
            palette.SetColorAtIndex((short)61, 226, 239, 218);
            palette.SetColorAtIndex((short)62, 230, 247, 255);
            palette.SetColorAtIndex((short)63, 217, 217, 217);
            // 表格内容样式
            ICellStyle style = CreateStyle(workbook, (short)9, contentFontSize);

            ICellStyle headerStyle = CreateStyle(workbook, palette.FindColor(226, 239, 218).Indexed, 12, true);

            ICellStyle coloredStyle = CreateStyle(workbook, palette.FindColor(230, 247, 255).Indexed, contentFontSize);
            // 头部
            var headerRow = sheet.CreateRow(currentRow);

            var currHeaderWidth = 0;
            foreach (var col in dto.Columns)
            {
                currHeaderWidth = baseHeaderWidth * col.ColName.Length + headerMargin;
                sheet.SetColumnWidth(currentCol, currHeaderWidth);
                ICell headerCell = headerRow.CreateCell(currentCol);
                if (col.ColName == "ParkRowSpan" || col.ColName == "ModelRowSpan")
                    continue;
                DateTime tempDate;
                var cellValue = col.ColName;
                if (DateTime.TryParse(col.ColName, out tempDate))
                {
                    cellValue = tempDate.ToString("yyyy-MM-dd HH:mm");
                    sheet.SetColumnWidth(currentCol, 200 * col.ColName.Length + 700);
                }
                headerCell.SetCellValue(cellValue);
                headerCell.CellStyle = headerStyle;
                headerRow.Cells.Add(headerCell);
                currentCol++;
            }

            // Body
            currentRow = 1;
            var startMacRow = 0;
            var endMacRow = 0;
            var startModelRow = 0;
            var endModelRow = 0;
            var regions = new List<CellRangeAddress>();
            ICellStyle cellStylePercent = workbook.CreateCellStyle();
            cellStylePercent.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
            foreach (DataRow row in dt.Rows)
            {
                IRow bodyRow = sheet.CreateRow(currentRow);
                currentCol = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    if (col.ColumnName == "ParkRowSpan" || col.ColumnName == "ModelRowSpan")
                        continue;
                    ICell cell = bodyRow.CreateCell(currentCol);

                    double tempDouble;
                    var cellValue = row[col.ColumnName].ToString();
                    if (double.TryParse(cellValue, out tempDouble) || cellValue.Contains("%"))
                    {
                        cellValue = row[col.ColumnName].ToString();
                        if (!cellValue.Contains("%"))
                        {
                            cell.SetCellValue(tempDouble);
                        }
                        else
                        {
                            cell.SetCellValue(Convert.ToDouble(cellValue.Replace("%", "")) / 100);
                            cell.CellStyle = cellStylePercent;
                        }
                    }
                    else
                    {
                        cell.SetCellValue(cellValue);
                    }

                    //cell.CellStyle = style;

                    bodyRow.Cells.Add(cell);
                    currentCol++;
                }
                regions.Add(new CellRangeAddress(startMacRow, endMacRow, 1, 1));
                currentRow++;
            }
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            var buffer = ms.ToArray();
            return buffer;
        }

        /// <summary>
        /// 导出机台效率数据
        /// </summary>
        /// <param name="models"></param>
        /// <param name="modeltotal"></param>
        /// <param name="total"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public static byte[] ExportMachineEfficiencies(IEnumerable<MachineEfDto> models, IEnumerable<MachineEfDto> modeltotal, int total, ref string errorinfo)
        {
            try
            {
                string[] cloumnsOne = new string[] { "园区", "厂房", "楼层", "制程代码", "设备类型", "设备编号", "稼动率", "待机率", "故障率", "生产", "故障率汇总", "总时长(Min)", "运行时长(Min)", "待机时长(Min)", "故障时长(Min)", "故障时长(Min)生产", "故障时长(Min)汇总", "设备当前状态" };

                string[] cloumnsTwo = new string[] { "园区", "厂房", "楼层", "制程代码", "设备类型", "设备编号", "稼动率", "待机率", "ME", "生产", "汇总", "总时长(Min)", "运行时长(Min)", "待机时长(Min)", "ME", "生产", "汇总", "设备当前状态" };

                string[] clorow = new string[] { "FactoryName", "PlantName", "FloorName", "PCode", "ModelName", "MacCode", "RunrRate", "IdleRate", "ErrorRateTwo", "ErrorRateOne", "ErrorRate", "AllTime", "RunTime", "IdleTime", "ErrorTimeTwo", "ErrorTimeOne", "ErrorTime", "StatusName" };


                if (models == null)
                {
                    errorinfo = "待导出数据为空";
                    return null;
                }
                var workbook = new HSSFWorkbook();
                var sheet = workbook.CreateSheet("sheet1");
                var currentRow = 0;
                var currentCol = 0;
                var baseHeaderWidth = 800;
                var headerMargin = 900;
                var contentFontSize = 11;
                var palette = workbook.GetCustomPalette();
                palette.SetColorAtIndex((short)60, 255, 242, 204);
                palette.SetColorAtIndex((short)61, 226, 239, 218);
                palette.SetColorAtIndex((short)62, 230, 247, 255);
                palette.SetColorAtIndex((short)63, 217, 217, 217);
                // 表格内容样式
                ICellStyle style = CreateStyle(workbook, (short)9, contentFontSize);
                ICellStyle Pstyle= CreateStyle(workbook, (short)9, contentFontSize,type:"True");

                ICellStyle headerStyle = CreateStyle(workbook, palette.FindColor(226, 239, 218).Indexed, 12, true);

                ICellStyle coloredStyle = CreateStyle(workbook, palette.FindColor(230, 247, 255).Indexed, contentFontSize);
                // 头部
                var headerRowOne = sheet.CreateRow(0);
                var headerRowTwo = sheet.CreateRow(1);

                var currHeaderWidth = 0;
                foreach (var col in cloumnsOne)
                {
                    currHeaderWidth = baseHeaderWidth * col.Length + headerMargin;
                    sheet.SetColumnWidth(currentCol, currHeaderWidth);
                    ICell headerCell = headerRowOne.CreateCell(currentCol);

                    var cellValue = col;
                    headerCell.SetCellValue(cellValue);

                    headerCell.CellStyle = headerStyle;
                    headerRowOne.Cells.Add(headerCell);
                    currentCol++;
                }
                currentCol = 0;
                currHeaderWidth = 0;
                foreach (var col in cloumnsTwo)
                {
                    currHeaderWidth = baseHeaderWidth * col.Length + headerMargin;
                    sheet.SetColumnWidth(currentCol, currHeaderWidth);
                    ICell headerCell = headerRowTwo.CreateCell(currentCol);

                    var cellValue = col;
                    headerCell.SetCellValue(cellValue);

                    headerCell.CellStyle = headerStyle;
                    headerRowTwo.Cells.Add(headerCell);
                    currentCol++;
                }
#region 合并表头
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0));
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 1, 1));
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 2, 2));
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 3, 3));
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 4, 4));
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 5, 5));
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 6, 6));
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 7, 7));
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 8, 10));//故障率
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 11, 11));
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 12, 12));
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 13, 13));
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 14, 16));//故障时长
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 17, 17));
                #endregion
                // Body
                currentRow = 2;
                var startMacRow = 0;
                var endMacRow = 0;
                var regions = new List<CellRangeAddress>();
                /*ICellStyle cellStylePercent = workbook.CreateCellStyle();
                cellStylePercent.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");*/
                foreach (var row in modeltotal)
                {
                    IRow bodyRow = sheet.CreateRow(currentRow);
                    currentCol = 0;
                    foreach (var col in clorow)
                    {
                        ICell cell = bodyRow.CreateCell(currentCol);

                        if (currentCol == 0)
                        {
                            cell.SetCellValue("汇总");
                            bodyRow.Cells.Add(cell);
                            cell.CellStyle = style;
                            currentCol++;
                            continue;
                        }
                        else if (currentCol == 3)
                        {
                            cell.SetCellValue("设备数量：" + total);
                            bodyRow.Cells.Add(cell);
                            cell.CellStyle = style;
                            currentCol++;
                            continue;
                        }
                        var cellValue = row.GetValue(col);
                        if (cellValue.ToString().Contains("%"))
                        {


                            cell.SetCellValue(Convert.ToDouble(cellValue.ToString().Replace("%", "")) / 100);

                            cell.CellStyle = Pstyle;
                        }
                        else
                        {

                            cell.SetCellValue(cellValue);
                            cell.CellStyle = style;
                        }

                        bodyRow.Cells.Add(cell);
                        currentCol++;
                    }
                    regions.Add(new CellRangeAddress(startMacRow, endMacRow, 1, 1));
                    currentRow++;
                }
                //合并单元格
                sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 2));
                sheet.AddMergedRegion(new CellRangeAddress(2, 2, 3, 5));
                foreach (var row in models)
                {
                    IRow bodyRow = sheet.CreateRow(currentRow);
                    currentCol = 0;
                    #region 汇总行

                    #endregion
                    foreach (var col in clorow)
                    {
                        ICell cell = bodyRow.CreateCell(currentCol);

                        var value = row.GetValue(col);

                        if (value != null)
                        {
                            try
                            {
                                
                                switch (typeof(MachineEfDto).GetProperty(col).PropertyType.FullName)
                                {
                                    case "System.DateTime":
                                        var vDate = Convert.ToDateTime(value);

                                        cell.SetCellValue(vDate.ToString("yyyy-MM-dd HH:mm:ss"));
                                        cell.CellStyle = style;

                                        // dataRow.CreateCell(piIndex, CellType.Formula).SetCellValue(vInt);
                                        break;
                                    case "System.Double":
                                    case "System.Demical":
                                        var vDouble = Convert.ToDouble(value);

                                        cell.SetCellValue(vDouble);
                                        cell.CellStyle = style;
                                        break;
                                    case "System.Int32":
                                    case "System.Int64":
                                        var vInt = Convert.ToInt64(value);

                                        cell.SetCellValue(vInt);
                                        cell.CellStyle = style;
                                        break;
                                    case "System.Boolean":
                                        var vBool = Convert.ToBoolean(value);

                                        cell.SetCellValue(vBool);
                                        cell.CellStyle = style;
                                        break;
                                    default:

                                        var Value = value.ToString();
                                       
                                        if (value.ToString().Contains("%"))
                                        {


                                            cell.SetCellValue(Convert.ToDouble(value.ToString().Replace("%", "")) / 100);

                                            cell.CellStyle = Pstyle;
                                        }
                                        else
                                        {

                                            cell.SetCellValue(value.ToString());
                                            cell.CellStyle = style;
                                        }
                                        break;
                                }

                            }
                            catch
                            {

                                cell.SetCellValue("");
                                cell.CellStyle = style;
                            }
                        }
                            

                        

                        bodyRow.Cells.Add(cell);
                        currentCol++;
                    }
                    regions.Add(new CellRangeAddress(startMacRow, endMacRow, 1, 1));
                    currentRow++;
                }
                MemoryStream ms = new MemoryStream();
                workbook.Write(ms);
                var buffer = ms.ToArray();
                return buffer;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 创建单元格样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="fillColor"></param>
        /// <param name="fontSize"></param>
        /// <param name="bold"></param>
        /// <returns></returns>
        private static ICellStyle CreateStyle(IWorkbook workbook, short fillColor = HSSFColor.White.Index, int fontSize = 10, bool bold = false,string type="")
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            //设置单元格的样式：水平对齐居中
            style.Alignment = HorizontalAlignment.Center;
            //垂直居中
            style.VerticalAlignment = VerticalAlignment.Center;
            style.WrapText = true;
            style.FillForegroundColor = fillColor;
            style.FillPattern = FillPattern.SolidForeground;
            //新建一个字体样式对象
            IFont font = workbook.CreateFont();
            //设置字体加粗样式
            // font.Boldweight = short.MaxValue;
            font.FontHeightInPoints = fontSize;
            font.IsBold = bold;
            style.SetFont(font);
            if (type=="0")
            {
                style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
            }
            if (type == "1")
            {
                IDataFormat dataFormatCustom = workbook.CreateDataFormat();
                
                style.DataFormat = dataFormatCustom.GetFormat("yyyy年MM-dd HH:mm:ss");

            }
            return style;
        }
        /// <summary>
        /// 报表访问导出
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static IWorkbook ExportVisit(DataTable dt)
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            //IRow headerRow = sheet.CreateRow(0);
            int rowIndex = 1, piIndex = 0, count = dt.Columns.Count;
            var headerCellStyle = CreateStyle(workbook, fontSize: 11, bold: true);
            var cellStyle = CreateStyle(workbook, fontSize: 11);
            for (int i = 0; i < count; i++)
            {
                IRow headerRow = sheet.CreateRow(i);
                var headerCell = headerRow.CreateCell(0);
                headerCell.SetCellValue(dt.Columns[i].ColumnName);
                headerCell.CellStyle = headerCellStyle;
                var Cell = headerRow.CreateCell(1);
                var value = dt.Rows[0].ItemArray[i];
                if (i>0)
                {
                    Cell.SetCellValue(Convert.ToDouble(value));
                }
                else
                {
                    Cell.SetCellValue(value.ToString()); 
                }
                
                Cell.CellStyle = cellStyle;

            }

            return workbook;
        }
        public static IWorkbook EntityToExcel(List<Staff> staffs, List<string> list)
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            //IRow headerRow = sheet.CreateRow(0);
            int rowIndex = 1, piIndex = 0, count = list.Count;
            var headerCellStyle = CreateStyle(workbook, fontSize: 11, bold: true);
            var cellStyle = CreateStyle(workbook, fontSize: 11);
            IRow headerRow = sheet.CreateRow(0);
            foreach (var item in list)
            { 
                var headerCell = headerRow.CreateCell(piIndex);
                headerCell.SetCellValue(item);
                
                headerCell.CellStyle = headerCellStyle;
                sheet.SetColumnWidth(piIndex, 800 * item.Length + 700);
                piIndex++;
            }
            foreach (var item in staffs)
            {
                IRow rows = sheet.CreateRow(rowIndex);
                for (int i = 0; i < count; i++)
                {
                    
                    var Cell = rows.CreateCell(i);
                   
                    switch (i+1)
                    {
                        case 1:
                            Cell.SetCellValue(item.FCode);
                            break;
                        case 2:
                            Cell.SetCellValue(item.FName);
                            break;
                        case 3:
                            Cell.SetCellValue(item.RecTime);
                            break;
                        case 4:
                            Cell.SetCellValue(item.RoleNames);
                            break;
                        case 5:
                            Cell.SetCellValue(item.FStatus > 0 ? "正常" : "异常");
                               
                            break;
                        case 6:
                            Cell.SetCellValue(item.Remark);
                            break;
                        case 7:
                            break;
                        default:
                            break;
                    }

                    Cell.CellStyle = cellStyle;
                }
                rowIndex++;
            }
            return workbook;
        }
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="datas"></param>
        /// <returns></returns>
        public static IWorkbook EntityToExcel<T>(List<T> datas) where T : class, new()
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            /*ICellStyle cellStylePercent = workbook.CreateCellStyle();
            StylePercent
            
            ICellStyle cellStylePercent = workbook.CreateCellStyle();
            cellStylePercent.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
            ICellStyle cellStyleDouble = workbook.CreateCellStyle();
            cellStyleDouble.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.000");*/
            IRow headerRow = sheet.CreateRow(0);
            int rowIndex = 1, piIndex = 0;
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties();
            int pisLen = pis.Length;
            PropertyInfo pi = null;
            string displayName = string.Empty;
            var headerCellStyle = CreateStyle(workbook, fontSize: 11, bold: true);
            while (piIndex < pisLen)
            {
                
                pi = pis[piIndex];
                var pName = pi.GetCustomAttribute<DescriptionAttribute>();
                displayName = pName?.Description ?? string.Empty;
                if (!displayName.Equals(string.Empty))
                {
                    try
                    {
                        var headerCell = headerRow.CreateCell(piIndex);
                        headerCell.CellStyle = headerCellStyle;
                        headerCell.SetCellValue(displayName);
                        //  headerRow.CreateCell(piIndex).SetCellValue(displayName);
                    }
                    catch (Exception)
                    {
                        var headerCell = headerRow.CreateCell(piIndex);
                        headerCell.SetCellValue("");
                    }
                }
                
                sheet.SetColumnWidth(piIndex, 800 * displayName.Length + 700);
                piIndex++;
            }
           
            var cellStyle = CreateStyle(workbook, fontSize: 11);
            var PercentStyle = CreateStyle(workbook, fontSize: 11,type: "0");
            var DatetimeStyle = CreateStyle(workbook, fontSize: 11, type: "1");
            foreach (T data in datas)
            {
                piIndex = 0;
                IRow dataRow = sheet.CreateRow(rowIndex);
                while (piIndex < pisLen)
                {
                    pi = pis[piIndex];
                    var value = pi.GetValue(data, null);
                    ICell dataCell = null;
                    if (value != null)
                    {
                        try
                        {
                            switch (pi.PropertyType.FullName)
                            {
                                case "System.DateTime":
                              /*  case "System.Nullable":*/
                                    var vDate = Convert.ToDateTime(value);
                                    dataCell = dataRow.CreateCell(piIndex);
                                    dataCell.SetCellValue(vDate.ToString("yyyy-MM-dd HH:mm:ss"));
                                    sheet.SetColumnWidth(piIndex, 5000);
                                    // dataRow.CreateCell(piIndex, CellType.Formula).SetCellValue(vInt);
                                    dataCell.CellStyle = cellStyle;
                                    break;
                                case "System.Double":
                                case "System.Demical":
                                    var vDouble = Convert.ToDouble(value);
                                    dataCell = dataRow.CreateCell(piIndex, CellType.Numeric);
                                    dataCell.SetCellValue(vDouble);
                                   
                                    dataCell.CellStyle = cellStyle;
                                    break;
                                case "System.Int32":
                                case "System.Int64":
                                    var vInt = Convert.ToInt64(value);
                                    dataCell = dataRow.CreateCell(piIndex, CellType.Numeric);
                                    dataCell.SetCellValue(vInt);
                                    dataCell.CellStyle = cellStyle;
                                    break;
                                case "System.Boolean":
                                    var vBool = Convert.ToBoolean(value);
                                    dataCell = dataRow.CreateCell(piIndex, CellType.Boolean);
                                    dataCell.SetCellValue(vBool);
                                    dataCell.CellStyle = cellStyle;
                                    break;
                                default:
                                    
                                    if (value.ToString().Contains("%"))
                                    {
                                        dataCell = dataRow.CreateCell(piIndex,CellType.Numeric);
                                       
                                        dataCell.SetCellValue(Convert.ToDouble(value.ToString().Replace("%", "")) / 100);
                                       
                                        dataCell.CellStyle = PercentStyle;
                                    }
                                    else
                                    {
                                        dataCell = dataRow.CreateCell(piIndex, CellType.String);
                                        dataCell.SetCellValue(value.ToString());
                                        dataCell.CellStyle = cellStyle;
                                    }
                                    break;
                            }

                        }
                        catch
                        {
                            dataCell = dataRow.CreateCell(piIndex);
                            dataCell.SetCellValue("");
                            dataCell.CellStyle = cellStyle;
                        }
                    }
                    else
                    {
                        dataCell = dataRow.CreateCell(piIndex);
                        dataCell.SetCellValue("");
                        dataCell.CellStyle = cellStyle;
                    }
                    
                    piIndex++;
                }
                rowIndex++;
            }
            return workbook;
        }
        public static IWorkbook MtbaToExcel(AntdComplexTableDto roles,int plens)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            IRow headerRow = sheet.CreateRow(0);
            IRow header = sheet.CreateRow(1);
            var headerCellStyle = CreateStyle(workbook, fontSize: 11, bold: true);
            var total = roles.TotalData.Rows;
            var Columns = roles.Columns.ToList();
            for (int i = 0; i < Columns.Count; i++)
            {
                
                if (!string.IsNullOrEmpty(Columns[i].ColName))
                {
                    var name = Columns[i].ColName;
                    var headerCell = headerRow.CreateCell(i);
                    headerCell.CellStyle = headerCellStyle;
                    headerCell.SetCellValue(name);
                }
                if (i == 0)
                {
                    var headerCell = header.CreateCell(i);
                    headerCell.CellStyle = headerCellStyle;
                    headerCell.SetCellValue("汇总");
                }
                else if (i > 8)
                {
                    var headerCell = header.CreateCell(i);
                    headerCell.CellStyle = headerCellStyle;
                    headerCell.SetCellValue(Convert.ToDouble(total[0][i - 9]));
                }
                else
                {
                    var headerCell = header.CreateCell(i);
                    headerCell.CellStyle = headerCellStyle;
                    headerCell.SetCellValue("");
                }
                sheet.SetColumnWidth(i, 800 * Columns[i].ColName.Length + 700);
            }
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 8));
            var cellStyle = CreateStyle(workbook, fontSize: 11);
            int index = 0, rowIndex = 2;
            while (index < plens)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                for (int i = 0; i < Columns.Count; i++)
                {
                    ICell dataCell = null;
                    var value = roles.Datas.Rows[index][$@"{Columns[i].ColName}"];
                    if (i < 7)
                    {
                        dataCell = dataRow.CreateCell(i,CellType.String);
                        dataCell.SetCellValue(value.ToString());

                    }
                    else
                    {
                        var vDouble = Convert.ToDouble(value);
                        dataCell = dataRow.CreateCell(i,CellType.Numeric);
                        dataCell.SetCellValue(vDouble);
                    }
                    dataCell.CellStyle = cellStyle;
                   
                }
                index++;
                rowIndex++;
            }
            return workbook;
        }
        

        /// <summary>
        /// 表头合并的报表导出
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="datas">数据源</param>
        /// <param name="list">表头</param>
        /// <param name="key">需要合并位置</param>
        /// <returns></returns>

        public static IWorkbook EntityToExcel<T>(List<T> datas,List<string> list,List<int> key) where T : class, new()
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            IRow header = sheet.CreateRow(0);
            IRow headerRow= sheet.CreateRow(1);
            
            /*IDataFormat dataFormat = workbook.CreateDataFormat();
            ICellStyle cellStylePercent = workbook.CreateCellStyle();
            cellStylePercent.DataFormat = dataFormat.GetFormat("0.00%");*/
            int rowIndex = 2, piIndex = 0;
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties();
            int pisLen = pis.Length;
            PropertyInfo pi = null;
            string displayName = string.Empty;
            var headerCellStyle = CreateStyle(workbook, fontSize: 11, bold: true);
            int sum = 0;   
            while (piIndex < pisLen)
            {
                pi = pis[piIndex];
                var pName = pi.GetCustomAttribute<DescriptionAttribute>();
                displayName = pName?.Description ?? string.Empty;
                if (list.Count > piIndex && !string.IsNullOrEmpty(list[piIndex]))
                {
                    var headerCell = header.CreateCell(piIndex);
                    headerCell.CellStyle = headerCellStyle;
                    headerCell.SetCellValue(list[piIndex]);
                }
                else
                {
                    var headerCell = header.CreateCell(piIndex);
                    headerCell.CellStyle = headerCellStyle;
                    headerCell.SetCellValue("");
                }
              
                
                if (!displayName.Equals(string.Empty))
                {
                    try
                    {
                        var headerCell = headerRow.CreateCell(piIndex);
                        headerCell.CellStyle = headerCellStyle;
                        headerCell.SetCellValue(displayName);
                        //  headerRow.CreateCell(piIndex).SetCellValue(displayName);
                    }
                    catch (Exception)
                    {
                        var headerCell = headerRow.CreateCell(piIndex);
                        headerCell.CellStyle = CreateStyle(workbook);
                        headerCell.SetCellValue("");
                    }
                }
                sum = sum > displayName.Length ? sum : displayName.Length;
                sheet.SetColumnWidth(piIndex, 800 * displayName.Length + 700);
                piIndex++;
            }
            int index = 0;
            int i = 0;
            while (i < pisLen)
            {
                if (index<key.Count&&i == key[index]){
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, i, key[index + 1]));
                    i = key[index + 1];
                    index += 2;
                }
                else
                {
                    sheet.AddMergedRegion(new CellRangeAddress(0, 1, i, i));
                }
                i++;
            }
            var cellStyle = CreateStyle(workbook, fontSize: 11);
            var PercentStyle = CreateStyle(workbook, fontSize: 11,type:"0");
            var DatetimeStyle = CreateStyle(workbook, fontSize: 11, type: "1");
            foreach (T data in datas)
            {
                piIndex = 0;
                IRow dataRow = sheet.CreateRow(rowIndex);
                while (piIndex < pisLen)
                {
                    pi = pis[piIndex];
                    var value = pi.GetValue(data, null);
                    ICell dataCell = null;
                    if (value != null)
                    {
                        try
                        {
                            switch (pi.PropertyType.FullName)
                            {
                                case "System.DateTime":
                                    var vDate = Convert.ToDateTime(value);
                                    dataCell = dataRow.CreateCell(piIndex);
                                    dataCell.SetCellValue(vDate.ToString("yyyy-MM-dd HH:mm:ss"));
                                    sheet.SetColumnWidth(piIndex, 5000);
                                    // dataRow.CreateCell(piIndex）.SetCellValue(vInt);
                                    dataCell.CellStyle = DatetimeStyle;
                                    break;
                                case "System.Double":
                                case "System.Demical":
                                    var vDouble = Convert.ToDouble(value);
                                    dataCell = dataRow.CreateCell(piIndex, CellType.Numeric);
                                    dataCell.SetCellValue(vDouble);
                                    dataCell.CellStyle = cellStyle;
                                    break;
                                case "System.Int32":
                                case "System.Int64":
                                    var vInt = Convert.ToInt64(value);
                                    dataCell = dataRow.CreateCell(piIndex, CellType.Numeric);
                                    dataCell.SetCellValue(vInt);
                                    dataCell.CellStyle = cellStyle;
                                    break;
                                case "System.Boolean":
                                    var vBool = Convert.ToBoolean(value);
                                    dataCell = dataRow.CreateCell(piIndex, CellType.Boolean);
                                    dataCell.SetCellValue(vBool);
                                    dataCell.CellStyle = cellStyle;
                                    break;
                                default:
                                    /*dataCell = dataRow.CreateCell(piIndex, CellType.Boolean);
                                    dataCell.SetCellValue(value.ToString());*/
                                    var number = value.ToString();
                                    if (number.Contains("%"))
                                    {
                                        dataCell = dataRow.CreateCell(piIndex,CellType.Numeric);
                                        dataCell.SetCellValue(Convert.ToDouble(number.Replace("%", "")) / 100);
                                        /*dataCell.CellStyle = cellStylePercent;*/
                                        dataCell.CellStyle = PercentStyle;

                                    }
                                    else
                                    {
                                        dataCell = dataRow.CreateCell(piIndex, CellType.String);
                                        dataCell.SetCellValue(number);
                                        dataCell.CellStyle = cellStyle;
                                    }
/*                                    sheet.SetColumnWidth(piIndex, sheet.GetColumnWidth(piIndex) / 256> Encoding.Default.GetBytes(value.ToString()).Length + 1? sheet.GetColumnWidth(piIndex): (Encoding.Default.GetBytes(value.ToString()).Length + 1)*256);
*/
                                    break;
                            }

                        }
                        catch
                        {
                            dataCell = dataRow.CreateCell(piIndex);
                            dataCell.SetCellValue("");
                            dataCell.CellStyle = cellStyle;
                        }
                    }
                    else
                    {
                        dataCell = dataRow.CreateCell(piIndex);
                        dataCell.SetCellValue("");
                        dataCell.CellStyle = cellStyle;
                    }
                   
                   
                    piIndex++;
                }
                rowIndex++;
            }
            return workbook;
        }
        /// <summary>
        /// 导出实体集合到指定的文件路径
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filePath"></param>
        /// <param name="datas"></param>
        /// <returns></returns>
        public static Task EntityExportToFile<T>(string filePath, List<T> datas) where T : class, new()
        {
            var workbook = EntityToExcel(datas);

            var dic = System.IO.Path.GetDirectoryName(filePath);

            if (!System.IO.Directory.Exists(dic))
            {
                System.IO.Directory.CreateDirectory(dic);
            }

            if (System.IO.File.Exists(filePath))
            {
                System.IO.File.Delete(filePath);
            }

            using (FileStream ms = new FileStream(filePath, FileMode.OpenOrCreate))
            {
                workbook.Write(ms);
            }

            return Task.CompletedTask;
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="schema">导出数据结构</param>
        /// <param name="dataTable">导出数据源</param>
        /// <param name="errorinfo">错误信息</param>
        /// <returns></returns>
        public static byte[] TableToExcel2(StSchema schema, DataTable dataTable, ref string errorinfo)
        {
            if (dataTable == null || dataTable.Rows.Count <= 0)
            {
                errorinfo = "待导出数据为空";
                return null;
            }
            var workbook = new XSSFWorkbook();
            var sheet = workbook.CreateSheet("sheet1");
            var currentRow = 0;
            var currentCol = 0;
            var baseHeaderWidth = 400;
            var baseRowWidth = 500;
            var headerMargin = 900;
            var rowMargin = 400;
            ICellStyle style = workbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            //设置单元格的样式：水平对齐居中
            style.Alignment = HorizontalAlignment.Center;
            //垂直居中
            style.VerticalAlignment = VerticalAlignment.Center;
            //新建一个字体样式对象
            IFont font = workbook.CreateFont();
            //设置字体加粗样式
            // font.Boldweight = short.MaxValue;
            font.FontHeightInPoints = 12;
            //使用SetFont方法将字体样式添加到单元格样式中 
            style.SetFont(font);
            var headerRow = sheet.CreateRow(currentRow);
            foreach (var item in schema.Items)
            {
                ICell headerCell = headerRow.CreateCell(currentCol);
                headerCell.SetCellValue(item.Title);
                sheet.SetColumnWidth(currentCol, baseHeaderWidth * item.Title.Length + headerMargin);
                headerCell.CellStyle = style;
                currentCol++;
            }
            currentRow++;
            int no = 1;
            foreach (DataRow item in dataTable.Rows)
            {
                currentCol = 0;
                IRow dataRow = sheet.CreateRow(currentRow);
                foreach (var title in schema.Items)
                {
                    var currHeaderWidth = baseHeaderWidth * title.Title.Length + headerMargin;
                    ICell cell = dataRow.CreateCell(currentCol);
                    if (currentCol == 0)
                    {
                        cell.SetCellValue(no);
                        var width = baseRowWidth * no.ToString().Length + rowMargin;
                        if (width > currHeaderWidth)
                        {
                            sheet.SetColumnWidth(currentCol, width);
                        }
                    }
                    else
                    {
                        // 防止前端传回的json值datatable中没有对应的字段与之匹配
                        if (string.IsNullOrEmpty(title.Index))
                        {
                            cell.SetCellValue(string.Empty);
                        }
                        else
                        {
                            cell.SetCellValue(item[title.Index].ToString());
                            var width = baseRowWidth * (item[title.Index].ToString().Length) + rowMargin;
                            if (width > currHeaderWidth)
                            {
                                sheet.SetColumnWidth(currentCol, width);
                            }
                        }
                    }
                    cell.CellStyle = style;
                    currentCol++;
                }
                currentRow++;
                no++;
            }
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            var buffer = ms.ToArray();
            return buffer;
        }

        

        /// <summary>
        /// 导出实体
        /// </summary>
        /// <param name="schema">导出数据结构</param>
        /// <param name="dataTable">导出数据源</param>
        /// <param name="errorinfo">错误信息</param>
        /// <returns></returns>
        public static byte[] EntityToExcel<T>(StSchema schema, IEnumerable<T> entities, ref string errorinfo)
        {
            if (entities == null || entities.Count() <= 0)
            {
                errorinfo = "待导出数据为空";
                return null;
            }
            var workbook = new XSSFWorkbook();
            var startRow = 1;
            var startCol = 1;
            var sheet = workbook.CreateSheet("sheet1");
            var currentRow = startRow;
            var currentCol = startCol;
            var baseHeaderWidth = 400;
            var baseRowWidth = 500;
            var headerMargin = 900;
            var rowMargin = 400;
            ICellStyle style = workbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            //设置单元格的样式：水平对齐居中
            style.Alignment = HorizontalAlignment.Center;
            //垂直居中
            style.VerticalAlignment = VerticalAlignment.Center;
            //新建一个字体样式对象
            IFont font = workbook.CreateFont();
            //设置字体加粗样式
            // font.Boldweight = short.MaxValue;
            font.FontHeightInPoints = 12;
            //使用SetFont方法将字体样式添加到单元格样式中 
            style.SetFont(font);
            var headerRow = sheet.CreateRow(currentRow);
            foreach (var item in schema.Items)
            {
                ICell headerCell = headerRow.CreateCell(currentCol);
                headerCell.SetCellValue(item.Title);
                sheet.SetColumnWidth(currentCol, baseHeaderWidth * item.Title.Length + headerMargin);
                headerCell.CellStyle = style;
                currentCol++;
            }
            currentRow++;
            int no = 1;
            var type = typeof(T);
            var properties = type.GetProperties();

            foreach (T item in entities)
            {
                currentCol = startCol;
                IRow dataRow = sheet.CreateRow(currentRow);
                foreach (var title in schema.Items)
                {
                    var currHeaderWidth = baseHeaderWidth * title.Title.Length + headerMargin;
                    ICell cell = dataRow.CreateCell(currentCol);
                    if (currentCol == startCol)
                    {
                        cell.SetCellValue(no);

                        var width = baseRowWidth * no.ToString().Length + rowMargin;
                        if (width > currHeaderWidth)
                        {
                            sheet.SetColumnWidth(currentCol, width);
                        }
                    }
                    else
                    {
                        // 防止前端传回的json值datatable中没有对应的字段与之匹配
                        if (string.IsNullOrEmpty(title.Index))
                        {
                            cell.SetCellValue(string.Empty);
                        }
                        else
                        {
                            var property = properties.FirstOrDefault(c => c.Name.ToLower() == title.Index.ToLower());
                            var value = property.GetValue(item);
                            if (value != null)
                            {
                                var typeName = value.GetType().FullName;
                                switch (typeName)
                                {
                                    case "System.Int32": cell.SetCellValue((int)value); break;
                                    case "System.Decimal":
                                    case "System.Double":
                                        cell.SetCellValue((double)value); break;
                                    case "System.DateTime":
                                        cell.SetCellValue(((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss"));
                                        break;
                                    default:
                                        cell.SetCellValue(value.ToString()); break;
                                }
                                var width = baseRowWidth * (value.ToString().Length) + rowMargin;
                                if (width > currHeaderWidth)
                                {
                                    sheet.SetColumnWidth(currentCol, width);
                                }
                            }
                        }
                    }
                    cell.CellStyle = style;
                    currentCol++;
                }
                currentRow++;
                no++;
            }
           
            if (schema.ShowFooter)
            {
                var sumRow = schema.SumRow;
                var footRow = sheet.CreateRow(currentRow);
                var nameCell = footRow.CreateCell(startCol);
                nameCell.SetCellValue(sumRow.RowName);
                nameCell.CellStyle = style;
                var clos = sumRow.ColSpan > 2 ? sumRow.ColSpan : 3;
                for (var i = 1; i < clos; i++)
                {
                    var emptyCell = footRow.CreateCell(startCol + i);
                    emptyCell.SetCellValue("");
                    emptyCell.CellStyle = style;
                }
                sheet.AddMergedRegion(new CellRangeAddress(currentRow, currentRow, startCol, startCol +clos  - 2));
                var colIndex = clos-1;
                foreach (var item in sumRow.RowValues)
                {
                    var cell = footRow.CreateCell(colIndex + startCol);
                    cell.SetCellValue(item);
                    cell.CellStyle = style;
                    colIndex++;
                }
            }
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            var buffer = ms.ToArray();
            return buffer;
        }
       
        private void SetCellStyle(IWorkbook workbook, ICell cell)
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            //设置单元格的样式：水平对齐居中
            style.Alignment = HorizontalAlignment.Center;
            //垂直居中
            style.VerticalAlignment = VerticalAlignment.Center;
            //新建一个字体样式对象
            IFont font = workbook.CreateFont();
            //设置字体加粗样式
            // font.Boldweight = short.MaxValue;
            font.FontHeightInPoints = 12;
            //使用SetFont方法将字体样式添加到单元格样式中 
            style.SetFont(font);
            cell.CellStyle = style;
        }
    }
}