C#使用模板导出Excel

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

 

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

 

/// <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();
            }
        }