SimulationServerYL/SimulationServer/Utils/DataTableExtensions.cs

using System.Data;
using System.Linq;
using KYFramework;
using MongoDB.Bson;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
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, bool zt = false, string s = "")
    {
        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);
                bool t = false;
                if (kv1.Key == "医疗任务设备" || kv1.Key == "医疗药品" || kv1.Key == "单机机场使用情况")
                {
                    //row.GetCell(0).CellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                    CellUtil.SetCellStyleProperties(row.GetCell(0), new Dictionary<string, object> { { "verticalAlignment", NPOI.SS.UserModel.VerticalAlignment.Center } });
                    t = true;
                }
                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]);
                        if (t)
                        {
                            //row.GetCell(i + 1).CellStyle.WrapText = true;
                            CellUtil.SetCellStyleProperties(row.GetCell(i + 1), new Dictionary<string, object> { { "wrapText", true } });
                            CellUtil.SetCellStyleProperties(row.GetCell(i + 1), new Dictionary<string, object> { { "verticalAlignment", NPOI.SS.UserModel.VerticalAlignment.Center } });
                        }
                    }
                    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++;
            }
        }

        if (zt)
        {
            var sheet = workbook.CreateSheet("任务成功率");
            var Row0 = sheet.CreateRow(0);
            var Row0Cell0 = Row0.CreateCell(0);
            Row0Cell0.SetCellValue("指标名");
            var Row0Cell1 = Row0.CreateCell(1);
            Row0Cell1.SetCellValue("数值");
            var Row1 = sheet.CreateRow(1);
            var Row1Cell0 = Row1.CreateCell(0);
            Row1Cell0.SetCellValue("任务成功率");
            var Row1Cell1 = Row1.CreateCell(1);
            Row1Cell1.SetCellValue(s);
        }

        // 保存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);
        }



    }
}