C# 可重复追加Excel

做的一个小工具,需要定时向Excel中添加数据,也就是多次追加写入. 网上开源工具也基本提供,因此自己简单实现了下. 依赖NPOI.

所有需要导出属性都需要加上[ExcelHeader]属性. 有更完善方式,可以评论留言.

  1 using System;
  2 using System.Collections.Generic;
  3 using System.IO;
  4 using System.Reflection;
  5 using NPOI.SS.UserModel;
  6 using NPOI.XSSF.UserModel;
  7 
  8 namespace BatteryMonitor.Excel
  9 {
 10     public class ExcelUtil
 11     {
 12         /// <summary>
 13         ///      可以重复导入
 14         ///     导出Excel实时数据
 15         /// </summary>
 16         /// <param name="excelPath"></param>
 17         /// <param name="models"></param>
 18         /// <param name="header"></param>
 19         public static void AppendExcelData<T>(string excelPath, List<T> models) where T : class, new()
 20         {
 21             FileStream fout = null;
 22             ISheet sheet = null;
 23             IWorkbook workbook = null;
 24             FileStream fin = null;
 25             var ms = new NpoiMemoryStream {AllowClose = false};
 26 
 27             FileInfo fileInfo = new FileInfo(excelPath);
 28             if (!fileInfo.Exists)
 29             {
 30                 fout = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite,
 31                     FileShare.None);
 32                 workbook = new XSSFWorkbook();
 33                 sheet = workbook.CreateSheet("Sheet1");
 34             }
 35             else
 36             {
 37                 // 从此处读数据.方便添加, 不要考虑共用一个fout. 因为传递到构造函数时,fout会被关闭.从而导致最终的workbook.Write报错.
 38                 fin = new FileStream(excelPath, FileMode.Open, FileAccess.Read,
 39                     FileShare.Read);
 40                 workbook = new XSSFWorkbook(fin);
 41                 fout = new FileStream(excelPath, FileMode.Open, FileAccess.ReadWrite,
 42                     FileShare.None);
 43                 sheet = workbook.GetSheetAt(0); //获取工作表
 44             }
 45 
 46             var type = models[0].GetType();
 47             var propertyInfos = type.GetProperties();
 48             Array.Sort(propertyInfos, (info, propertyInfo) =>
 49             {
 50                 var lhs = info.GetCustomAttribute<ExcelHeaderAttribute>();
 51                 var rhs = propertyInfo.GetCustomAttribute<ExcelHeaderAttribute>();
 52 
 53                 if (lhs != null && rhs != null)
 54                 {
 55                     return lhs.Index > rhs.Index ? 1 : -1;
 56                 }
 57 
 58                 return 0;
 59             });
 60             
 61             // 设置头信息
 62             if (!fileInfo.Exists || fileInfo.Length == 0)
 63             {
 64                 var style = workbook.CreateCellStyle();
 65                 var font = workbook.CreateFont();
 66                 font.IsBold = true;
 67                 style.SetFont(font);
 68                 style.Alignment = HorizontalAlignment.Center;
 69                 var row = sheet.CreateRow(sheet.LastRowNum);
 70                 for (var i = 0; i < propertyInfos.Length; i++)
 71                 {
 72                     var c = row.CreateCell(i);
 73                     c.CellStyle = style;
 74                     c.SetCellValue(propertyInfos[i].GetCustomAttribute<ExcelHeaderAttribute>().Name);
 75                 }
 76             }
 77 
 78             var cellStyle = workbook.CreateCellStyle();
 79             cellStyle.Alignment = HorizontalAlignment.Center;
 80             foreach (var model in models)
 81             {
 82                 var row = sheet.CreateRow(sheet.LastRowNum + 1);
 83 
 84                 for (int i = 0;i < propertyInfos.Length;i++)
 85                 {
 86                     var c = row.CreateCell(i);
 87                     c.CellStyle = cellStyle;
 88                     var value = propertyInfos[i].GetValue(model);
 89                     c.SetCellValue(value.ToString());
 90                 }
 91             }
 92 
 93             if (!fileInfo.Exists)
 94             {
 95                 workbook.Write(ms);
 96                 ms.Flush();
 97                 ms.Position = 0;
 98                 ms.AllowClose = true;
 99                 ms.WriteTo(fout);
100                 fout.Flush();
101                 fout.Close();
102                 ms.Close();
103             }
104             else
105             {
106                 workbook.Write(fout);
107                 fout.Flush();
108                 fout.Close();
109                 fin.Close();
110                 workbook.Close();
111             }
112         }
113     }
114 }

 

 1 using System.IO;
 2 
 3 namespace BatteryMonitor.Excel
 4 {
 5         public class NpoiMemoryStream : MemoryStream
 6         {
 7             public NpoiMemoryStream()
 8             {
 9                 AllowClose = true;
10             }
11 
12             public bool AllowClose { get; set; }
13 
14             public override void Close()
15             {
16                 if (AllowClose)
17                     base.Close();
18             }
19         }
20         
21 }
using System;

namespace BatteryMonitor.Excel
{
    /// <summary>
    /// 设置Excel名称
    /// </summary>
    public class ExcelHeaderAttribute : Attribute
    {
        public string Name { get; set; }
        public int Index { get; set; }

        public ExcelHeaderAttribute(string name , int index = 0)
        {
            this.Name = name;
            this.Index = index;
        }
    }
}

 

posted @ 2021-10-19 23:06  一粒粟  阅读(570)  评论(0)  编辑  收藏  举报