OLSBackend_Common/JiaZhiQuan.Common/Utils/ExcelUtils.cs

using Senparc.Weixin.MP.AdvancedAPIs.Card;
using System.Collections.Generic;
using System.IO;
using System;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.Reflection;
using System.Data;

namespace JiaZhiQuan.Common.Utils
{
    public class ExcelUtils
    {
        public static byte[] ExportToExcel(List<Dictionary<string, object>> data, List<string> columnNames)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            ICreationHelper creationHelper = workbook.GetCreationHelper();
            ICellStyle dateCellStyle = CreateDateCellStyle(workbook);
            ICellStyle numberCellStyle = CreateNumberCellStyle(workbook);
            ICellStyle doubleCellStyle = CreateDoubleCellStyle(workbook);

            // Create header row
            IRow headerRow = sheet.CreateRow(0);

            if (columnNames != null && columnNames.Count > 0)
            {
                for (int colIndex = 0; colIndex < columnNames.Count; colIndex++)
                {
                    headerRow.CreateCell(colIndex).SetCellValue(columnNames[colIndex]);
                }
            }
            else
            {
                // If columnNames is not provided, use keys from the first dictionary as column names
                if (data.Count > 0)
                {
                    var firstRow = data[0];
                    int colIndex = 0;

                    foreach (var columnName in firstRow.Keys)
                    {
                        headerRow.CreateCell(colIndex).SetCellValue(columnName);
                        colIndex++;
                    }
                }
            }

            // Create data rows
            int rowIndex = 1;
            foreach (var rowData in data)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);

                int colIndex = 0;
                foreach (var value in rowData.Values)
                {
                    ICell cell = dataRow.CreateCell(colIndex);

                    if (value != null)
                    {
                        Type valueType = value.GetType();

                        if (valueType == typeof(DateTime))
                        {
                            cell.SetCellValue((DateTime)value);
                            cell.CellStyle = dateCellStyle;
                        }
                        else if (valueType.IsEnum)
                        {
                            cell.SetCellValue(Enum.GetName(valueType, value));
                        }
                        else if (valueType == typeof(bool))
                        {
                            cell.SetCellValue((bool)value);
                        }
                        else if (IsNumericType(valueType))
                        {
                            cell.SetCellValue(Convert.ToInt64(value));
                            cell.CellStyle = numberCellStyle;
                        }
                        else if (IsDoubleType(valueType))
                        {
                            cell.SetCellValue(Convert.ToDouble(value));
                            cell.CellStyle = doubleCellStyle;
                        }
                        else
                        {
                            cell.SetCellValue(creationHelper.CreateRichTextString(value.ToString()));
                        }
                    }

                    colIndex++;
                }

                rowIndex++;
            }

            for (int i = 0; i < headerRow.Cells.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }

            using MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            return stream.ToArray();
        }


        public static byte[] ExportToExcel<T>(List<T> data)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            ICreationHelper creationHelper = workbook.GetCreationHelper();
            ICellStyle dateCellStyle = CreateDateCellStyle(workbook);
            ICellStyle numberCellStyle = CreateNumberCellStyle(workbook);
            ICellStyle doubleCellStyle = CreateDoubleCellStyle(workbook);

            // Create header row
            IRow headerRow = sheet.CreateRow(0);
            var properties = typeof(T).GetProperties();
            for (int i = 0; i < properties.Length; i++)
            {
                var property = properties[i];
                var attr = GetAttr(property);
                string columnName = attr?.ColumnName ?? property.Name;
                headerRow.CreateCell(i).SetCellValue(columnName);
            }

            // Create data rows
            for (int rowIndex = 0; rowIndex < data.Count; rowIndex++)
            {
                IRow dataRow = sheet.CreateRow(rowIndex + 1);
                for (int colIndex = 0; colIndex < properties.Length; colIndex++)
                {
                    var value = properties[colIndex].GetValue(data[rowIndex]);
                    ICell cell = dataRow.CreateCell(colIndex);

                    if (value != null)
                    {
                        Type valueType = value.GetType();

                        if (valueType == typeof(DateTime))
                        {
                            cell.SetCellValue((DateTime)value);
                            cell.CellStyle = dateCellStyle;
                        }
                        else if (valueType.IsEnum)
                        {
                            cell.SetCellValue(Enum.GetName(valueType, value));
                        }
                        else if (valueType == typeof(bool))
                        {
                            cell.SetCellValue((bool)value);
                        }
                        else if (IsNumericType(valueType))
                        {
                            cell.SetCellValue(Convert.ToInt64(value));
                            cell.CellStyle = numberCellStyle;
                        }
                        else if (IsDoubleType(valueType))
                        {
                            cell.SetCellValue(Convert.ToDouble(value));
                            cell.CellStyle = doubleCellStyle;
                        }
                        else
                        {
                            cell.SetCellValue(creationHelper.CreateRichTextString(value.ToString()));
                        }
                    }
                }
            }

            for (int i = 0; i < properties.Length; i++)
            {
                sheet.AutoSizeColumn(i);
            }

            using MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            return stream.ToArray();
        }

        public static ExcelConfigAttribute GetAttr(PropertyInfo property)
        {
            return (ExcelConfigAttribute)Attribute.GetCustomAttribute(property, typeof(ExcelConfigAttribute));
        }

        public static ICellStyle CreateDateCellStyle(IWorkbook workbook)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            IDataFormat dateFormat = workbook.CreateDataFormat();
            cellStyle.DataFormat = dateFormat.GetFormat("yyyy-mm-dd hh:mm:ss");
            return cellStyle;
        }

        public static ICellStyle CreateNumberCellStyle(IWorkbook workbook)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
            return cellStyle;
        }

        public static ICellStyle CreateDoubleCellStyle(IWorkbook workbook)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
            return cellStyle;
        }

        public static bool IsNumericType(Type valueType)
        {
            return valueType.IsPrimitive && valueType != typeof(bool) ||
                   valueType == typeof(int) ||
                   valueType == typeof(long) ||
                   valueType == typeof(short) ||
                   valueType == typeof(byte);
        }

        public static bool IsDoubleType(Type valueType)
        {
            return valueType.IsPrimitive && valueType != typeof(bool) ||
                   valueType == typeof(decimal) ||
                   valueType == typeof(double) ||
                   valueType == typeof(float);
        }
    }

    public class ExcelColumnDTO
    {
        /// <summary>
        /// 数据的字段名称
        /// </summary>
        public string Prop { get; set; }

        /// <summary>
        /// Excel的列名
        /// </summary>
        public string Label { get; set; }
    }

    [AttributeUsage(AttributeTargets.Class | AttributeTargets.Property, AllowMultiple = false)]
    public class ExcelConfigAttribute : Attribute
    {
        public string ColumnName { get; }

        public ExcelConfigAttribute(string columnName)
        {
            ColumnName = columnName;
        }
    }
}