C# 使用ClosedXML 导出数据（工具类）

使用ClosedXML导出数据工具类代码：

using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Chine.MVCClibrary.Meet.Tools
{
    public class ExcelHelper
    {
        /// <summary>
        /// 导出全量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        public static void ExportToExcel<T>(List<T> data, string filePath, string sheetName) where T : new()
        {
            using (var workbook = new XLWorkbook())
            {
                var worksheet = workbook.Worksheets.Add(sheetName);

                // 获取所有属性作为列头
                var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

                //添加表头
                for (int i = 0; i < properties.Length; i++)
                {
                    worksheet.Cell(1, i + 1).Value = properties[i].Name;
                }

                //填充数据
                for (int row = 0; row < data.Count; row++)
                {
                    for (int col = 0; col < properties.Length; col++)
                    {
                        var value = properties[col].GetValue(data[row], null);
                        worksheet.Cell(row + 2, col + 1).Value = value ?? "";
                    }
                }

                //保存Excel到指定路径
                workbook.SaveAs(filePath);
            }
        }

        /// <summary>
        /// 导出特定列
        /// </summary>
        /// <typeparam name="T">数据实体</typeparam>
        /// <param name="data">需要导出的数据</param>
        /// <param name="columnMappings">属性英文，中文对应关系(定义的就是需要导出的字段)</param>
        /// <param name="filePath">导出路径</param>
        /// <param name="sheetName"></param>
        public static void ExportToExcel<T>(List<T> data, Dictionary<string, string> columnMappings, string filePath, string sheetName) where T : new()
        {
            List<string> columnsToExport = columnMappings.Keys.ToList();
            using (var workbook = new XLWorkbook())
            {
                //写入sheetName
                var worksheet = workbook.Worksheets.Add(sheetName);

                // 写入标题行
                int columnIndex = 1;
                foreach (var column in columnsToExport)
                {
                    if (columnMappings.ContainsKey(column))
                    {
                        worksheet.Cell(1, columnIndex).Value = columnMappings[column]; // 使用中文标题
                        columnIndex++;
                    }
                }

                //设置标题行样式
                IXLStyle style = worksheet.Range(1, 1, 1, columnIndex - 1).Style;
                style.Font.Bold = true;
                style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                style.Fill.BackgroundColor = XLColor.LightGreen;
                style.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
                style.Border.SetInsideBorder(XLBorderStyleValues.Thin);

                // 写入数据行
                for (int i = 0; i < data.Count; i++)
                {
                    columnIndex = 1;
                    foreach (var column in columnsToExport)
                    {
                        var property = typeof(T).GetProperty(column);
                        if (property != null)
                        {
                            var value = property.GetValue(data[i]);

                            worksheet.Cell(i + 2, columnIndex).DataType = XLDataType.Text; // 设置单元格数据类型为文本
                            worksheet.Cell(i + 2, columnIndex).Value = IsNumeric(value.ToString()) ? $"'{value?.ToString() ?? ""}" : value?.ToString() ?? "";
                            columnIndex++;
                        }
                    }
                }

                // 设置列宽自适应内容宽度
                worksheet.Columns().AdjustToContents();

                // 保存文件
                workbook.SaveAs(filePath);
            }
        }

        private static bool IsNumeric(string value)
        {
            if (string.IsNullOrEmpty(value) || value.Length < 15) return false;

            return Regex.IsMatch(value, @"^\d+(\.\d+)?$");
        }

    }
}

 

 

调用方示例：

public ActionResult TicketPurchaserExport()
{
    var list = Factory.M_Meeting_Purchaser.GetTicketPurchaserPageList("", "", -1, 100000, 1, out int count);
    if (list == null || list.Count == 0)
    {
        return JResult(false, "没有数据导出");
    }

    string sheetName = "购票者信息管理";
    string filePath = Server.MapPath("~\\UploadFile\\" + sheetName + ".xlsx");

    Dictionary<string, string> columnMappings = new Dictionary<string, string>()
    {
        { "PurchaserGlobalId", "购票人GlobalId" },
        { "PurchaserMelaId", "购票人会员编号" },
        { "PurchasedTicketCount", "购票资格数量" },
        { "StatusDes", "状态" },
        { "AttendeeCount", "已确认出席人数" },
        { "PurchaserName", "购票人姓名" },
        { "PurchaserPhoneNumber", "购票人手机号" },
        { "PurchaserIDNumber", "购票人身份证号" },
        { "IsPurchaserAttendanceDes", "购票人是否出席" },
        { "SpouseName", "配偶姓名" },
        { "SpousePhoneNumber", "配偶手机号" },
        { "SpouseIDNumber", "配偶身份证号" },
        { "IsSpouseAttendanceDes", "配偶是否出席" }
    };

    ExcelHelper.ExportToExcel(list, columnMappings, filePath, sheetName);

    return File(filePath, "application/vnd.ms-excel", $"{sheetName}.xlsx");
}