C#根据自定义特性标记成员变量 并将标记的成员变量保存在Excel中

提供一种便于维护和扩展Excel保存数据的方法

前言

2021-3-16，刚刚工作半年的萌新，本文作为自身学习和工作的记录，若有更好的方式或者代码有Bug，欢迎各位大佬评论区指正。

设计需求

从事于工控行业的上位机软件，主要包括数据采集和上传。客户经常会要求增加保存到本地表格的数据，如将某一数据添加到新的一列数据。我们修改的时候，由于各个软件的不同、各个工程师实现保存数据方式的不同，修改数据往往要寻找保存数据的代码位置，并从上传的Json字符串中寻找数据，匹配数据并修改保存方式，这样的阅读效率并不高效，修改数据不方便。

本文提出一种新的保存方式，就是采用特性标记要保存的成员变量（字段或属性）并将特性中的信息作为表头和表头顺序，该变量的值作为保存的数据。

实现方式

在.NET Framework 4.5以上的版本中，反射的方法里多了反射特性的方法。

在此我们添加一个自定义特性，该类继承系统的Attribute就能作为特性挂在成员变量上。

自定义特性类

    /// <summary>
    /// 用于数据保存于Excel的特性类  带有该特性的字段或属性会被记录在Excel
    /// </summary>
    public class ExcelSaveAttribute : Attribute
    {
        #region 字段或属性
        /// <summary>
        /// 会保存的表头名称
        /// </summary>
        public string HeadName { get; set; }

        /// <summary>
        /// 优先级  数字越小 在表格的位置就越前
        /// </summary>
        public int Priority { get; set; }

        /// <summary>
        /// 将带有该特性的字段或属性的值保存为表头
        /// </summary>
        public bool ValueToHeadName { get; set; }

        /// <summary>
        /// 检索该对象中的某个字段名或属性名   通过该字段或该属性的名称作为表头 填充自身的数据
        /// </summary>
        public string MemberNameToSave { get; set; }
        #endregion

        #region 方法
        public ExcelSaveAttribute(string _headname, int _priority)
        {
            this.HeadName = _headname;
            this.Priority = _priority;

            this.ValueToHeadName = false;
            this.MemberNameToSave = "";
        }

        /// <summary>
        /// 带有该特性标记的成员  自身的值作为表头，检索对象中名称为"_MemberName"的成员，将该成员的值作为保存到表格的值
        /// </summary>
        /// <param name="_ValueToHeadName"></param>
        /// <param name="_MemberName"></param>
        /// <param name="_Priority"></param>
        public ExcelSaveAttribute(bool _ValueToHeadName, string _MemberName, int _Priority)
        {
            this.ValueToHeadName = _ValueToHeadName;
            this.MemberNameToSave = _MemberName;
            this.Priority = _Priority;

            this.HeadName = "";
        }
        #endregion 
    }

跟据特性获得表头和保存数据的值

该特性提供了两种保存方法
一：该成员变量特性的信息作为表头，值保存在Excel中
二：该成员变量的值作为表头，同一类的另一个对象的值保存在Excel中。

        /// <summary>
        /// 获得字段或属性的Excel保存特性,若该属性没有被标记 则返回空值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Member">字段或属性 </param>
        /// <param name="SourceItem">源对象</param>
        private ExcelSaveData GetExcelData<T>(T Member, object SourceItem)
            where T : MemberInfo
        {
            ExcelSaveAttribute excelAtt = Member.GetCustomAttribute<ExcelSaveAttribute>();
            if (excelAtt == null)
            {
                //没有Excel保存特性  则退出
                return null;
            }
            string _headname = Member.GetCustomAttribute<ExcelSaveAttribute>().HeadName;
            int _priority = Member.GetCustomAttribute<ExcelSaveAttribute>().Priority;
            bool _ValueToHeadName = Member.GetCustomAttribute<ExcelSaveAttribute>().ValueToHeadName;
            string _MemberNameValue = Member.GetCustomAttribute<ExcelSaveAttribute>().MemberNameToSave;
            object value;
            if (_ValueToHeadName)
            {
                string HeadNameV ="";
                string Value ="";
                Type t = SourceItem.GetType();
                MemberInfo[] allinfo = t.GetMembers();
                if (Member is FieldInfo)
                {
                    FieldInfo FieldMember = Member as FieldInfo;
                    HeadNameV =   FieldMember.GetValue(SourceItem).ToString();
                }
                if (Member is PropertyInfo)
                {
                    PropertyInfo ProMember = Member as PropertyInfo;
                    HeadNameV = ProMember.GetValue(SourceItem).ToString();
                }
                foreach (var item in allinfo)
                {
                    if (item.Name == _MemberNameValue)
                    {
                        if (item is FieldInfo)
                        {
                            FieldInfo FieldMember = item as FieldInfo;
                            Value = FieldMember.GetValue(SourceItem).ToString();
                        }
                        if (item is PropertyInfo)
                        {
                            PropertyInfo ProMember = item as PropertyInfo;
                            Value = ProMember.GetValue(SourceItem).ToString();
                        }
                    }
                }
                return new ExcelSaveData() { HeadName = HeadNameV, Priority = _priority ,Value = Value};
            }
            if (Member is FieldInfo)
            {
                FieldInfo info = Member as FieldInfo;

                value = info.GetValue(SourceItem);
                if (value == null)
                {
                    value = "";
                }
                return new ExcelSaveData() { HeadName = _headname, Priority = _priority, Value = value.ToString() };
            }
            else if (Member is PropertyInfo)
            {
                PropertyInfo info = Member as PropertyInfo;
                value = info.GetValue(SourceItem);
                if (value == null)
                {
                    value = "";
                }
                return new ExcelSaveData() { HeadName = _headname, Priority = _priority, Value = value.ToString() };
            }
            return null;
        }

外部调用的方法

        public void SaveExcelData(List<object> InputData, string FilePath)
        {
            if (FilePath == null)
                FilePath = OperatorPath;

            List<ExcelSaveData> _ListSaveData = new List<ExcelSaveData>();
            foreach (object item in InputData)
            {
                FieldInfo[] _fieldinfos = item.GetType().GetFields(BindingFlags.Public | BindingFlags.Instance);
                foreach (FieldInfo fieldInfo in _fieldinfos)//遍歷字段
                {
                    ExcelSaveData tempdata = GetExcelData(fieldInfo, item);
                    if (tempdata != null) _ListSaveData.Add(tempdata);
                }
                PropertyInfo[] _propertyinfos = item.GetType().GetProperties();
                foreach (PropertyInfo propertyInfo in _propertyinfos)//遍历属性
                {
                    ExcelSaveData tempdata = GetExcelData(propertyInfo, item);
                    if (tempdata != null) _ListSaveData.Add(tempdata);
                }
            }
            //按照优先级排序
            for (int i = 0; i < _ListSaveData.Count - 1; i++)
            {
                for (int j = 0; j < _ListSaveData.Count - i - 1; j++)
                {
                    if (_ListSaveData[j].Priority > _ListSaveData[j + 1].Priority)
                    {
                        ExcelSaveData temp = _ListSaveData[j];
                        _ListSaveData[j] = _ListSaveData[j + 1];
                        _ListSaveData[j + 1] = temp;
                    }
                }
            }

            if (!File.Exists(FilePath))
            {
                List<string> Head = new List<string>();
                List<string> Value = new List<string>();
                foreach (var item in _ListSaveData)
                {
                    Head.Add(item.HeadName);
                    Value.Add(item.Value);
                }
                MergeCell(null,new Point(0,0),new Point(0,Head.Count),"测试信息");
                SaveHeadName(Head, FilePath);
                SaveData(Value, FilePath);
            }
            else
            {
                HeadIndex = 1;
                List<string> HeadName = ReadData(FilePath, HeadIndex.ToString());
                List<string> Value = new List<string>();
                
                foreach (string headname in HeadName)
                {
                    bool _ismatch = false;
                    foreach (ExcelSaveData item in _ListSaveData)
                    {
                        if (item.HeadName == headname)
                        {
                            Value.Add(item.Value);
                            _ismatch = true;
                        }
                    }
                    if (!_ismatch) Value.Add("");//该表头没有匹配的数据，要添加一个空的字符串
                }
                SaveData(Value, FilePath);
            }

        }

        /// <summary>
        /// 保存數據在表格的頭一行
        /// </summary>
        /// <param name="headname"></param>
        /// <param name="path"></param>
        private void SaveHeadName(List<string> headname, string path)
        {
            string OperationPath = path;
            if (!Directory.Exists(DefaultPath)) Directory.CreateDirectory(DefaultPath);
            IWorkbook ExcelBook = null;
            try
            {
                if (!File.Exists(OperationPath)) File.Copy(ModelPath, OperationPath);
                string AppExtensionName = Path.GetExtension(OperationPath);
                using (FileStream fs = File.Open(OperationPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    if (AppExtensionName == ".xlsx") ExcelBook = new XSSFWorkbook(fs);
                    else if (AppExtensionName == ".xls") ExcelBook = new HSSFWorkbook(fs);
                    ISheet Sheet = ExcelBook.GetSheetAt(0);
                    IRow NewRow = Sheet.CreateRow(HeadIndex);
                    for (int i = 0; i < headname.Count; i++)
                    {
                        ICell cell = NewRow.CreateCell(i);
                        cell.SetCellValue(headname[i]);
                    }
                }
                using (FileStream fs = File.OpenWrite(OperationPath))
                {
                    ExcelBook.Write(fs);
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine("Error!!" + ex);
            }
        }

        /// <summary>
        /// 合并单元格 方法内没有提供保存文件的功能，请在外部保存文件
        /// </summary>
        /// <param name="Sheet"></param>
        /// <param name="StartPoint"></param>
        /// <param name="EndPoint"></param>
        /// <param name="Value"></param>
        private void MergeCell(string path, Point StartPoint, Point EndPoint,string Value)
        {
            if ((StartPoint.X - EndPoint.X > 0) || (StartPoint.Y - EndPoint.Y > 0)) return;
            string OperationPath;
            if (path == null) OperationPath = OperatorPath;
            else OperationPath = path;
            if (!Directory.Exists(DefaultPath)) Directory.CreateDirectory(DefaultPath);
            IWorkbook ExcelBook = null;
            try
            {
                if (!File.Exists(OperationPath)) File.Copy(ModelPath, OperationPath);
                string AppExtensionName = Path.GetExtension(OperationPath);
                using (FileStream fs = File.Open(OperationPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    if (AppExtensionName == ".xlsx") ExcelBook = new XSSFWorkbook(fs);
                    else if (AppExtensionName == ".xls") ExcelBook = new HSSFWorkbook(fs);
                    ISheet Sheet = ExcelBook.GetSheetAt(0);
                    Sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(StartPoint.X, EndPoint.X, StartPoint.Y, EndPoint.Y));
                    HeadIndex = HeadIndex +(EndPoint.X - StartPoint.X) + 1;
                    ICell cell = Sheet.CreateRow(StartPoint.X).CreateCell(StartPoint.Y);
                    ICellStyle _NewStyle = ExcelBook.CreateCellStyle();
                    _NewStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CenterSelection;
                    cell.CellStyle = _NewStyle;
                    cell.SetCellValue(Value);
                    Console.WriteLine(HeadIndex);
                }
                using (FileStream fs = File.OpenWrite(OperationPath))
                {
                    ExcelBook.Write(fs);
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine("Error!!" + ex);

            }


        }

        public List<string> ReadData(string path, string row)
        {
            int rownum;
            if (path == null)
                path = OperatorPath;
            if (int.TryParse(row, out rownum) || (rownum > 0))
                Console.WriteLine("OK");
            else
                return null;
            if (File.Exists(path))
            {
                IWorkbook ExcelBook;
                try
                {
                    using (FileStream fs = new FileStream(path, FileMode.Open))
                    {
                        ExcelBook = new HSSFWorkbook(fs);
                        ISheet Sheet = ExcelBook.GetSheetAt(0);
                        IRow ReadRow = Sheet.GetRow(rownum);
                        if (ReadRow == null) return null;
                        List<string> Output = new List<string>();
                        foreach (ICell cell in ReadRow.Cells)
                        {
                            Output.Add(cell.StringCellValue);
                        }
                        return Output;
                    }
                }
                catch (Exception)
                {
                }
            }
            return null;
        }

实现效果

 private void button1_Click(object sender, EventArgs e)
        {
            List<object> UploadData = new List<object>();
            TestClass1 t1 = new TestClass1() { RightHand = "OKRH",RightLeg = "OKRL",LeftHand = "OKLH",LeftLeg = "OKLL"};
            TestClass3 t31 = new TestClass3() { ValueToName = "Test11111",Result = "OK"};
            TestClass3 t32 = new TestClass3() { ValueToName = "Test22222" ,Result = "NG"};
            UploadData.Add(t1);
            UploadData.Add(t31);
            UploadData.Add(t32);
            ExcelHelper.Instance().SaveExcelData(UploadData,null);

        }