C#使用模板导出Excel

冷不防 2021-12-01 10:06 385阅读 0赞

**前言：此随笔仅供自己学习，如有不足请指出**

**在某些情况导出的时候，会有很复杂的表头或者样式，如果靠代码去调整的话回很麻烦，需要写很多代码，这个时候可以预先设置好表格，这样就方便灵活很多。**

/// <summary>
            /// 使用模板导出Excel
            /// </summary>
            /// <param name="listEntity">数据集</param>
            /// <param name="path">路径</param>
            /// <param name="index">从第几行插入</param>
            /// <returns></returns>
            public static byte[] TemplateOutput(Array listEntity, string path, int index)
            {
                if ((listEntity?.Length ?? 0) == 0)
                {
                    return new byte[] { };
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    Type entityType = listEntity.GetValue(0).GetType(); ;
                    var entityProperties = entityType.GetProperties();
                    string extension = Path.GetExtension(path);
                    FileStream fs = File.OpenRead(path);
                    IWorkbook workbook = null;
                    if (extension.Equals(".xls"))
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    else
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                    fs.Close();
                    ISheet sheet = workbook.GetSheetAt(0);
                    IRow rows = null;
                    int i = 0;
                    foreach (var dbitem in listEntity)
                    {
                        rows = sheet.CreateRow(i++ + index);
                        for (int j = 0; j < entityProperties.Length; j++)
                        {
                            var prop = entityProperties[j];
                            var value = prop.GetValue(dbitem)?.ToString();
                            rows.CreateCell(j).SetCellValue(value ?? "");
                        }
                    }
                    workbook.Write(ms);
                    return ms.ToArray();
                }
            }

转载于:https://www.cnblogs.com/haixiaocan/p/11152678.html