【c#】写入EXCEL文件中的多个表

结果图：

1、插入单表

2、插入多表

二、代码

```csharp

```csharp
sharp``
public static partial class ExcelHelper
    {
        public class PropertyAndColumn
        {
            public PropertyAndColumn(PropertyInfo p, ExcelColumnAttribute e)
            {
                P = p;
                E = e;
            }

            public PropertyInfo P { get; set; }

            public ExcelColumnAttribute E { get; set; }

           
        }
/// <summary>
        /// 将实体类集合写入excel文件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        /// <param name="dateFormat">时间格式</param>
        public static void WriteToExcel(IEnumerable<SheetInfo> sheetInfos, string filePath)
        {
            string dateFormat = "yyyy-MM-dd HH:mm:ss";
            var workbook = GetWorkbook(sheetInfos, dateFormat);
            WriteToFile(filePath, workbook);
        }
		 /// <summary>
        /// 将实体类集合写入多个表的excel文件3
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        /// <param name="dateFormat">时间格式</param>
        public static void WriteManySheetsToExcel(IEnumerable<SheetInfo> sheetInfos, string filePath, string dateFormat = "yyyy-MM-dd HH:mm:ss")
        {
            //判断是否有重名表
            var temp = from a in sheetInfos
                       group a by a.SheetName into g
                       where g.Count() > 1
                       select g;
            if (temp.Any())
            {
                throw new Exception($"以下页签名称重复: {string.Join(",", temp.Select(g => g.Key))}");
            }

            var workbook = GetWorkbook(sheetInfos, dateFormat);
            WriteToFile(filePath, workbook);

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="workbook"></param>
        public static void WriteToFile(string filePath, IWorkbook workbook)
        {
            using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                //写入数据流
                workbook.Write(fs);
                if (fs.CanRead)
                {
                    fs.Flush();
                }
            }
        }

        /// <summary>
        /// 重载GetWook方法，实现创建多页表
        /// </summary>
        /// <param name="sheetInfos"></param>
        /// <param name="dateFormat"></param>
        /// <returns></returns>
        public static IWorkbook GetWorkbook(IEnumerable<SheetInfo> sheetInfos, string dateFormat = "yyyy-MM-dd HH:mm:ss")
        {
            //对excel版本进行判断，用哪个插件
            //xls文件对应的类是：HSSFWorkBook
            //xlsx文件对应的类是：XSSFWorkBook 这两个类都继承接口：IWorkBook。

            IWorkbook workbook = new XSSFWorkbook();
            foreach (var sheetInfo in sheetInfos)
            {
                ISheet sheet = workbook.CreateSheet(sheetInfo.SheetName);
                IRow row = sheet.CreateRow(0);
                ICellStyle dateCellStyle = workbook.CreateCellStyle();
                IDataFormat format = workbook.CreateDataFormat();
                dateCellStyle.DataFormat = format.GetFormat(dateFormat);


                ///将每列的值与实例对象的属性建立连接
                List<PropertyAndColumn> propertyAndColumns = new List<PropertyAndColumn>();

                ///通过反射获取对象的值
                foreach (var property in sheetInfo.RowType.GetProperties())
                {
                    if (property.GetCustomAttributes(typeof(ExcelColumnAttribute), true).SingleOrDefault() is ExcelColumnAttribute e)
                    {
                        if (e.WriteToExcel)
                        {
                            propertyAndColumns.Add(new PropertyAndColumn(property, e));
                        }
                    }
                }

                propertyAndColumns = propertyAndColumns.OrderBy(a => a.E.WriteOrder).ToList();

                for (int i = 0; i < propertyAndColumns.Count; i++)
                {
                    var a = propertyAndColumns[i];
                    row.CreateCell(i).SetCellValue(a.E.HeaderText);
                    row.Height = 350;
                    sheet.AutoSizeColumn(i);
                }

                var rows = sheetInfo.Rows.ToList();
                //处理数据内容
                for (int i = 0; i < rows.Count; i++)
                {
                    var currentRowData = rows[i];
                    row = sheet.CreateRow(1 + i);
                    row.Height = 250;
                    for (int j = 0; j < propertyAndColumns.Count; j++)
                    {
                        var a = propertyAndColumns[j];
                        object value = a.P.GetValue(currentRowData);
                        if (value != null)
                        {
                            var c = row.CreateCell(j);

                            if (value is DateTime d)
                            {
                                c.SetCellValue(d);
                                c.CellStyle = dateCellStyle;
                            }
                            else
                            {
                                c.SetCellValue(value.ToString());
                            }
                        }
                        sheet.SetColumnWidth(j, 256 * 15);
                    }
                }
            }

            return workbook;

        }
        
    }
}