SimulationServer/SimulationServer/Utils/DataTableExtensions.cs

using System.Data;
using KYFramework;
using MongoDB.Bson;
using NPOI.HSSF.UserModel;
using NPOIHelper;

namespace SimulationServer.Utils;

public static class DataTableExtensions
{
    public static T ToEntity<T>(this DataTable table) where T : new()
    {
        T entity = new T();
        for (int i = 0; i < table.Rows.Count; i++)
        {
            var properties = entity.GetType().GetProperties();
            for (int j = 0; j < properties.Length; j++)
            {
                Type newType = properties[j].PropertyType;
                //判断type类型是否为泛型，因为nullable是泛型类,
                if (newType.IsGenericType
                    && newType.GetGenericTypeDefinition() == typeof(Nullable<>)) //判断convertsionType是否为nullable泛型类
                {
                    //如果type为nullable类，声明一个NullableConverter类，该类提供从Nullable类到基础基元类型的转换
                    System.ComponentModel.NullableConverter nullableConverter =
                        new System.ComponentModel.NullableConverter(newType);
                    //将type转换为nullable对的基础基元类型
                    newType = nullableConverter.UnderlyingType;
                }

                properties[j].SetValue(entity, Convert.ChangeType(table.Rows[i][j], newType), null);
            }
        }

        return entity;
    }

    public static List<T> ToEntities<T>(this DataTable table) where T : new()
    {
        List<T> entities = new List<T>();
        if (table == null)
            return null;

        for (int i = 0; i < table.Rows.Count; i++)
        {
            T entity = new T();
            var properties = entity.GetType().GetProperties();
            for (int j = 0; j < properties.Length; j++)
            {
                Type newType = properties[j].PropertyType;
                //判断type类型是否为泛型，因为nullable是泛型类,
                if (newType.IsGenericType
                    && newType.GetGenericTypeDefinition() == typeof(Nullable<>)) //判断convertsionType是否为nullable泛型类
                {
                    //如果type为nullable类，声明一个NullableConverter类，该类提供从Nullable类到基础基元类型的转换
                    System.ComponentModel.NullableConverter nullableConverter =
                        new System.ComponentModel.NullableConverter(newType);
                    //将type转换为nullable对的基础基元类型
                    newType = nullableConverter.UnderlyingType;
                }

                properties[j].SetValue(entity, Convert.ChangeType(table.Rows[i][j], newType), null);
            }

            entities.Add(entity);
        }

        return entities;
    }

    public static void SaveToExcel(string path , Dictionary<string, Dictionary<string,List<string>>> report,bool withA = false)
    {
        if(!File.Exists(path)) File.Create(path).Close();
        // 创建一个新的Excel文件
        var workbook = new HSSFWorkbook();

        foreach (KeyValuePair<string,Dictionary<string,List<string>>> kv in report)
        {
            // 创建一个新的工作表
            var sheet = workbook.CreateSheet(kv.Key);
            
            // 创建标题行
            var headerRow = sheet.CreateRow(0);
            
            var headerCell = headerRow.CreateCell(0);
            headerCell.SetCellValue("指标名");
            
            // 遍历字典，将数据写入Excel
            int rowIndex = 1;
            foreach (KeyValuePair<string,List<string>> kv1 in kv.Value)
            {
                var row = sheet.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue(kv1.Key);
                if (withA)
                {
                    for (int i = 0; i < kv1.Value.Count - 1; i++)
                    {
                        var headerCell1 = headerRow.CreateCell(i + 1);
                        headerCell1.SetCellValue($"数值{i+1}");
                        row.CreateCell(i + 1).SetCellValue(kv1.Value[i]);
                    }
                    var headerCell1End = headerRow.CreateCell(kv1.Value.Count);
                    headerCell1End.SetCellValue($"平均值");
                    row.CreateCell(kv1.Value.Count).SetCellValue(kv1.Value[kv1.Value.Count - 1]);
                }
                else
                {
                    for (int i = 0; i < kv1.Value.Count; i++)
                    {
                        var headerCell1 = headerRow.CreateCell(i + 1);
                        headerCell1.SetCellValue($"数值{i+1}");
                        row.CreateCell(i + 1).SetCellValue(kv1.Value[i]);
                    }
                }
               
                rowIndex++;
            }
        }

        // 保存Excel文件
        using (var fs = new FileStream(path, FileMode.Create, FileAccess.Write))
        {
            workbook.Write(fs);
        }
    }
    public static void SaveToExcel(string path , Dictionary<string, Dictionary<string,string>> report)
    {
        
        // 创建一个新的Excel文件
        var workbook = new HSSFWorkbook();

        foreach (KeyValuePair<string,Dictionary<string,string>> kv in report)
        {
            // 创建一个新的工作表
            var sheet = workbook.CreateSheet(kv.Key);
            
            // 创建标题行
            var headerRow = sheet.CreateRow(0);
            var headerCell = headerRow.CreateCell(0);
            
            var headerRow1 = sheet.CreateRow(1);
            var headerCell1 = headerRow1.CreateCell(1);
            
            headerCell.SetCellValue("指标名");
            headerCell1.SetCellValue("数值");
            
            // 遍历字典，将数据写入Excel
            int rowIndex = 1;
            foreach (KeyValuePair<string,string> kv1 in kv.Value)
            {
                var row = sheet.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue(kv1.Key);
                row.CreateCell(1).SetCellValue(kv1.Value);
                rowIndex++;
            }
        }

        // 保存Excel文件
        using (var fs = new FileStream(path, FileMode.Create, FileAccess.Write))
        {
            workbook.Write(fs);
        }
        
        
        
    }
}