C#基础：使用反射和ClosedXML库快速写入实体列表到Excel

我是苏苏

已于 2024-11-01 22:52:27 修改

阅读量3.3k

点赞数 29

分类专栏： C#基础文章标签： c# 开发语言

于 2024-01-23 11:18:50 首次发布

本文链接：https://blog.csdn.net/m0_67412019/article/details/135767198

版权

C#基础专栏收录该内容

22 篇文章

订阅专栏

本文介绍了如何使用C#的ClosedXML库在控制台操作Excel，包括生成数据、填充列表（如学生姓名、班级和成绩）、设置表格样式，以及添加图片和筛选器。代码示例展示了如何在工作表中添加数据和应用筛选功能以方便数据分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、基础Demo（无反射，直接遍历）

直接在控制台输出，确保安装了该第三方库，安装第一个即可：

using ClosedXML.Excel;

class DataSource
{
    public int id {  get; set; }
    public string name { get; set; } = "";
    public string classes { get; set; } = "";
    public int score { get; set; }
}
class Test
{
    public static void SetBorder(IXLRange range)
    {
        // 设置表格框线
        range.Style.Border.TopBorder = XLBorderStyleValues.Thin;
        range.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
        range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
        range.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
        range.Style.Border.RightBorder = XLBorderStyleValues.Thin;
        range.Style.Border.BottomBorder = XLBorderStyleValues.Thin;

        // 设置表格框线颜色和粗细
        var borderColor = XLColor.Black;
        range.Style.Border.OutsideBorderColor = borderColor;
        range.Style.Border.LeftBorderColor = borderColor;
        range.Style.Border.RightBorderColor = borderColor;
        range.Style.Border.TopBorderColor = borderColor;
        range.Style.Border.BottomBorderColor = borderColor;

        range.Style.Border.TopBorder = XLBorderStyleValues.Thin;
        range.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
        range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
        range.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
        range.Style.Border.RightBorder = XLBorderStyleValues.Thin;
        range.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
        //range.Style.Border.SetBorderWidth(borderWidth);
    }
    static int Main(string[] args)
    {
        //一、生成数据
        List<DataSource> data = GetDataSource();

        //二、填充进Excel(ClosedXML.Excel)
        //（1）填充列表（列名：学生姓名，学生班级，学生成绩）
        //（2）添加一张图片
        using (var workbook = new XLWorkbook())
        {
            #region（1）填充列表
            //1.增加工作表
            var worksheet = workbook.Worksheets.Add("Sheet1");

            //1-2.设计样式
            // 调整第二列的列宽为20
            worksheet.Column(2).Width = 20;
            // 调整第四行的行高为15
            worksheet.Row(4).Height = 15;
            // 设置A1-C1的背景颜色为淡绿色
            worksheet.Range("A1:C1").Style.Fill.BackgroundColor = XLColor.LightGreen;
            // 设置A1-C11增加表格框
            SetBorder(worksheet.Range("A1:C11"));
            // 设置第一行垂直居中对齐和水平居中对齐
            worksheet.Row(1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            worksheet.Row(1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

            //2.填充列表（列名：学生姓名，学生班级，学生成绩）
            worksheet.Cell("A1").Value = "学生姓名";
            worksheet.Cell("B1").Value = "学生班级";
            worksheet.Cell("C1").Value = "学生成绩";

            //3.填充数据
            int rowIndex = 2;
            foreach (var item in data)
            {
                worksheet.Cell($"A{rowIndex}").Value = item.name;
                worksheet.Cell($"B{rowIndex}").Value = item.classes;
                worksheet.Cell($"C{rowIndex}").Value = item.score;
                rowIndex++;
            }
            #endregion

            #region （2）添加一张图片
            worksheet.AddPicture("F:\\C_program\\test2\\files\\dog.png").MoveTo(worksheet.Cell("A20"));
            #endregion

            //保存excel文件
            workbook.SaveAs("F:\\C_program\\test2\\files\\data.xlsx");//存储路径
        }
        return 0;
    }

    private static List<DataSource> GetDataSource()
    {
        List<DataSource> data = new List<DataSource>();
        Random random = new Random();
        for (int i = 0; i < 10; i++)
        {
            DataSource item = new DataSource();
            item.id = i + 1;
            item.name = $"Student{i + 1}";
            int classNumber = random.Next(201, 204);
            item.classes = $"计算机{classNumber}";
            item.score = random.Next(50, 101);

            data.Add(item);
        }
        return data;
    }
}

实现效果：

二、高度封装的方法（反射实现导出数据）

1.输出单列表

【封装方法】

using ClosedXML.Excel;
using System.Reflection;

namespace ConsoleApp1
{
    public static class ClosedXMLExtensions
    {
        /// <summary>
        /// 写入列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="filepath"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static bool ClosedXMLWriteList<T>(List<T> list,List<string> titlelist, string filepath) where T : class
        {
            try
            {
                //填充进Excel(ClosedXML.Excel)
                using (var workbook = new XLWorkbook())
                {
                    #region
                    //1.增加工作表
                    var worksheet = workbook.Worksheets.Add("Sheet1");

                    //2.获取列表的范围，并且设置相关样式，例如A1:C1
                    List<string> columns = new List<string> { { "A" }, { "B" }, { "C" }, { "D" }, { "E" }, { "F" }, { "G" }, { "H" }, { "I" }, { "J" }, { "K" }, { "L" }, { "M" }, { "N" }, { "O" }, { "P" }, { "Q" }, { "R" }, { "S" }, { "T" }, { "U" }, { "V" }, { "W" }, { "X" }, { "Y" }, { "Z" } };
                    var count = list.Count;
                    var filedcount = GetEntity<T>().Length;//字段的长度
                    // 设置标题行背景颜色为淡绿色
                    worksheet.Range($"A1:{columns[filedcount-1]}1").Style.Fill.BackgroundColor = XLColor.LightGreen;
                    // 启用筛选器
                    worksheet.Range($"A1:{columns[filedcount-1]}1").SetAutoFilter();
                    // 设置所有内容表格框
                    SetBorder(worksheet.Range($"A1:{columns[filedcount-1]}{count+1}"));

                    // 3. 设置所有单元格的居中对齐
                    worksheet.Cells().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    worksheet.Cells().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
 
                    // 4. 设置所有单元格的字体为宋体
                    worksheet.Cells().Style.Font.FontName = "SimSun";
                    


                    //5.填充列表（列名：学生姓名，学生班级，学生成绩）
                    if (filedcount != titlelist.Count)
                    {
                        throw new Exception("标题数量和字段数量不一致，请核对！");
                    }
                    var index = 0;
                    foreach (var item in GetEntity<T>())
                    {                        
                        worksheet.Cell($"{columns[index]}1").Value = titlelist[index];
                        index++;
                    }

                    //6.填充数据
                    int rowIndex = 2;
                    foreach (var item in list)
                    {
                        int columnIndex = 0;
                        foreach (var property in GetEntity<T>())
                        {
                            worksheet.Cell(rowIndex, columnIndex + 1).Value = property.GetValue(item)?.ToString();
                            columnIndex++;
                        }
                        worksheet.Column(columnIndex).Style.Alignment.WrapText = true;// 设置第x列自动换行
                        rowIndex++;
                    }

                    //7.自动调整列宽
                    worksheet.Columns().AdjustToContents();
                    #endregion



                    //保存excel文件
                    workbook.SaveAs(filepath);//存储路径

                    return true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

        }

        /// <summary>
        /// 给表格画边框线
        /// </summary>
        /// <param name="range"></param>
        public static void SetBorder(IXLRange range)
        {
            // 设置表格框线
            range.Style.Border.TopBorder = XLBorderStyleValues.Thin;
            range.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
            range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
            range.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
            range.Style.Border.RightBorder = XLBorderStyleValues.Thin;
            range.Style.Border.BottomBorder = XLBorderStyleValues.Thin;

            // 设置表格框线颜色和粗细
            var borderColor = XLColor.Black;
            range.Style.Border.OutsideBorderColor = borderColor;
            range.Style.Border.LeftBorderColor = borderColor;
            range.Style.Border.RightBorderColor = borderColor;
            range.Style.Border.TopBorderColor = borderColor;
            range.Style.Border.BottomBorderColor = borderColor;

            range.Style.Border.TopBorder = XLBorderStyleValues.Thin;
            range.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
            range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
            range.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
            range.Style.Border.RightBorder = XLBorderStyleValues.Thin;
            range.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
            //range.Style.Border.SetBorderWidth(borderWidth);

        }


        /// <summary>
        /// 反射获取实体的所有属性
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        static PropertyInfo[] GetEntity<T>() where T : class
        {
            return typeof(T).GetProperties();

        }


    }
}

【调用示例】

        var list = new UserInfo().GetList();//从数据库中获取数据表
        List<string> titlelist = new List<string> { { "用户id" }, { "用户名" }, { "性别" }, { "职位ID" }, { "身份标识索引" }, { "部门ID" }, { "邮箱" }, { "密码" }, { "手机" }, { "入职时间" }, { "离职时间" }, { "备注" } };
        string filepath = "C:\\Users\\ASUS\\Desktop\\temp\\data.xlsx";
        bool b = ClosedXMLExtensions.ClosedXMLWriteList(list, titlelist,filepath);
        Console.WriteLine($"输出状态{b}");

【效果图】

2.输出多sheet列表

using ClosedXML;
using ClosedXML.Excel;
using System.Reflection;

namespace WinFormsApp1
{
    public static class ClosedXMLExtensions
    {
        public class ExcelOutPutDetails<T>
        {

            public List<MulData<T>> DataDetails { get; set; } =   new List<MulData<T>>();

            public string Filepath { get; set; }
        }

        public class MulData<T>
        {
            public List<T> Data { get; set; } = new List<T>();

            public List<string> Titlelist { get; set; } = new List<string>();

            public string WorkSheetName { get; set; }
        }

        /// <summary>
        /// 写入列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="filepath"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static bool ClosedXMLWriteList<T>(ExcelOutPutDetails<T> excel) where T : class
        {
            try
            {
                //填充进Excel(ClosedXML.Excel)
                using (var workbook = new XLWorkbook())
                {
                    foreach (var jtem in excel.DataDetails)
                    {
                        #region
                        //1.增加工作表
                        var worksheet = workbook.Worksheets.Add(jtem.WorkSheetName);

                        //2.获取列表的范围，并且设置相关样式，例如A1:C1
                        List<string> columns = new List<string> { { "A" }, { "B" }, { "C" }, { "D" }, { "E" }, { "F" }, { "G" }, { "H" }, { "I" }, { "J" }, { "K" }, { "L" }, { "M" }, { "N" }, { "O" }, { "P" }, { "Q" }, { "R" }, { "S" }, { "T" }, { "U" }, { "V" }, { "W" }, { "X" }, { "Y" }, { "Z" } };
                        var count = jtem.Data.Count;
                        var filedcount = GetEntity<T>().Length;//字段的长度
                        // 设置标题行背景颜色为蓝色
                        worksheet.Range($"A1:{columns[filedcount-1]}1").Style.Fill.BackgroundColor = XLColor.CornflowerBlue;
                        // 设置标题行字体为加粗
                        worksheet.Range($"A1:{columns[filedcount - 1]}1").Style.Font.Bold = true;
                        // 启用筛选器
                        worksheet.Range($"A1:{columns[filedcount-1]}1").SetAutoFilter();
                        // 设置所有内容表格框
                        SetBorder(worksheet.Range($"A1:{columns[filedcount-1]}{count + 1}"));

                        // 3. 设置所有单元格的居中对齐
                        worksheet.Cells().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        worksheet.Cells().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;

                        // 4. 设置所有单元格的字体为宋体
                        worksheet.Cells().Style.Font.FontName = "SimSun";



                        //5.填充列表（列名：学生姓名，学生班级，学生成绩）
                        if (filedcount != jtem.Titlelist.Count)
                        {
                            throw new Exception("标题数量和字段数量不一致，请核对！");
                        }
                        var index = 0;
                        foreach (var item in GetEntity<T>())
                        {
                            worksheet.Cell($"{columns[index]}1").Value = jtem.Titlelist[index];
                            index++;
                        }

                        //6.填充数据
                        int rowIndex = 2;
                        foreach (var item in jtem.Data)
                        {
                            int columnIndex = 0;
                            foreach (var property in GetEntity<T>())
                            {
                                worksheet.Cell(rowIndex, columnIndex + 1).Value = property.GetValue(item)?.ToString();
                                columnIndex++;
                            }    
                            worksheet.Column(columnIndex).Style.Alignment.WrapText = true;// 设置第x列自动换行
                            rowIndex++;
                        }

                        //7.自动调整列宽
                        worksheet.Columns().AdjustToContents();
                        #endregion
                    }


                    //保存excel文件
                    workbook.SaveAs(excel.Filepath);//存储路径

                    return true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

        }

        /// <summary>
        /// 给表格画边框线
        /// </summary>
        /// <param name="range"></param>
        public static void SetBorder(IXLRange range)
        {
            // 设置表格框线
            range.Style.Border.TopBorder = XLBorderStyleValues.Thin;
            range.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
            range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
            range.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
            range.Style.Border.RightBorder = XLBorderStyleValues.Thin;
            range.Style.Border.BottomBorder = XLBorderStyleValues.Thin;

            // 设置表格框线颜色和粗细
            var borderColor = XLColor.Black;
            range.Style.Border.OutsideBorderColor = borderColor;
            range.Style.Border.LeftBorderColor = borderColor;
            range.Style.Border.RightBorderColor = borderColor;
            range.Style.Border.TopBorderColor = borderColor;
            range.Style.Border.BottomBorderColor = borderColor;

            range.Style.Border.TopBorder = XLBorderStyleValues.Thin;
            range.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
            range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
            range.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
            range.Style.Border.RightBorder = XLBorderStyleValues.Thin;
            range.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
            //range.Style.Border.SetBorderWidth(borderWidth);

        }


        /// <summary>
        /// 反射获取实体的所有属性
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        static PropertyInfo[] GetEntity<T>() where T : class
        {
            return typeof(T).GetProperties();

        }


    }
}

三、其余说明

【路径+文件名+时间戳】命名法

string folderPath = textBox1.Text.Trim(); // 获取文件夹路径
string fileName = "data"; // 文件名
string timeStamp = DateTime.Now.ToString("yyyyMMddHHmmss"); // 时间戳，格式为年月日时分秒
string filePathWithTimeStamp = Path.Combine(folderPath, $"{fileName}_{timeStamp}.xlsx"); // 拼接路径、文件名和时间戳
workbook.SaveAs(filePathWithTimeStamp); // 保存文件

【添加筛选器】需为每一个sheet表添加

worksheet.Cell("A1").Value = "员工姓名";
worksheet.Cell("B1").Value = "发放薪资";
worksheet.Cell("C1").Value = "发放日期";
worksheet.Cell("D1").Value = "岗位";
worksheet.Cell("E1").Value = "联系方式";
worksheet.RangeUsed().SetAutoFilter();//添加筛选器

效果如下所示：