程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> .NET網頁編程 >> C# >> C#入門知識 >> 創立execl導入對象類的步調

創立execl導入對象類的步調

編輯:C#入門知識

創立execl導入對象類的步調。本站提示廣大學習愛好者:(創立execl導入對象類的步調)文章只能為提供參考,不一定能成為您想要的結果。以下是創立execl導入對象類的步調正文


1、創立實體屬性標志

public class CellAttribute : Attribute
    {
        /// <summary>
        ///
        /// </summary>
        /// <param name="displayName">顯示稱號</param>
        /// <param name="hander"></param>
        public CellAttribute(string displayName, Type hander = null)
        {
            DisplayName = displayName;

            Hander = hander;
        }

        /// <summary>
        /// 顯示稱號
        /// </summary>
        public string DisplayName { get; set; }

        /// <summary>
        /// 類型
        /// </summary>
        public Type Hander { get; set; }
    }

2、創立通用途理辦法

public class XlsFileHandler<T> where T : new()
    {
        private readonly string _path;
        private readonly Dictionary<string, CellAttribute> _cellAttributes;
        readonly Dictionary<string, string> _propDictionary;

        public XlsFileHandler(string path)
        {
            _path = path;
            _cellAttributes = new Dictionary<string, CellAttribute>();
            _propDictionary = new Dictionary<string, string>();
            CreateMappers();
        }

        /// <summary>
        /// 創立映照
        /// </summary>
        private void CreateMappers()
        {
            foreach (var prop in typeof(T).GetProperties())
            {
                foreach (CellAttribute cellMapper in prop.GetCustomAttributes(false).OfType<CellAttribute>())
                {
                    _propDictionary.Add(cellMapper.DisplayName, prop.Name);
                    _cellAttributes.Add(cellMapper.DisplayName, cellMapper);
                }
            }
        }

        /// <summary>
        /// 獲得全部xls文件對應行的T對象
        /// </summary>
        /// <returns></returns>
        public List<T> ToData()
        {
            List<T> dataList = new List<T>();
            using (FileStream stream = GetStream())
            {
                IWorkbook workbook = new HSSFWorkbook(stream);
                ISheet sheet = workbook.GetSheetAt(0);
                var rows = sheet.GetRowEnumerator();
                int lastCell = 0;
                int i = 0;
                IRow headRow = null;
                while (rows.MoveNext())
                {
                    var row = sheet.GetRow(i);
                    if (i == 0)
                    {
                        headRow = sheet.GetRow(0);
                        lastCell = row.LastCellNum;
                    }
                    else
                    {
                        T t = GetData(workbook, headRow, row, lastCell);
                        dataList.Add(t);
                    }
                    i++;
                }
                stream.Close();
            }
            return dataList;
        }

        /// <summary>
        /// 獲得T對象
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="headRow"></param>
        /// <param name="currentRow"></param>
        /// <param name="lastCell"></param>
        /// <returns></returns>
        private T GetData(IWorkbook workbook, IRow headRow, IRow currentRow, int lastCell)
        {
            T t = new T();
            for (int j = 0; j < lastCell; j++)
            {
                var displayName = headRow.Cells[j].StringCellValue;
                if (!_cellAttributes.ContainsKey(displayName) || !_propDictionary.ContainsKey(displayName))
                {
                    continue;
                }
                var currentAttr = _cellAttributes[displayName];
                var propName = _propDictionary[displayName];

                ICell currentCell = currentRow.GetCell(j);
                string value = currentCell != null ? GetCellValue(workbook, currentCell) : "";
                if (currentAttr.Hander != null)
                {
                    SetValue(ref t, propName, InvokeHandler(currentAttr.Hander, value));
                }
                else
                {
                    SetValue(ref t, propName, value);
                }
            }
            return t;
        }

        /// <summary>
        /// 靜態履行處置辦法
        /// </summary>
        /// <param name="type"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private static object InvokeHandler(Type type, object value)
        {
            System.Reflection.ConstructorInfo constructor = type.GetConstructor(Type.EmptyTypes);
            if (constructor == null) throw new ArgumentNullException("type");
            object mgConstructor = constructor.Invoke(null);
            System.Reflection.MethodInfo method = type.GetMethod("GetResults");
            return method.Invoke(mgConstructor, new[] { value });
        }

        /// <summary>
        /// 獲得文件流
        /// </summary>
        /// <returns></returns>
        private FileStream GetStream()
        {
            if (!File.Exists(_path)) throw new FileNotFoundException("path");
            return new FileStream(_path, FileMode.Open, FileAccess.Read, FileShare.Read);
        }

        /// <summary>
        /// 獲得xls文件單位格的值
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static string GetCellValue(IWorkbook workbook, ICell cell)
        {
            string value;
            switch (cell.CellType)
            {
                case CellType.FORMULA:
                    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
                    value = evaluator.Evaluate(cell).FormatAsString();
                    break;
                default:
                    value = cell.ToString();
                    break;
            }
            return value;
        }

        /// <summary>
        /// 設置T屬性值
        /// </summary>
        /// <param name="t"></param>
        /// <param name="propName"></param>
        /// <param name="value"></param>
        private static void SetValue(ref T t, string propName, object value)
        {
            var typeName = t.GetType().GetProperty(propName).PropertyType.Name;
            var property = t.GetType().GetProperty(propName);
            switch (typeName)
            {
                case "Int32":
                    property.SetValue(t, Convert.ToInt32(value), null);
                    break;
                case "DateTime":
                    property.SetValue(t, Convert.ToDateTime(value), null);
                    break;
                case "Decimal":
                    property.SetValue(t, Convert.ToDecimal(value), null);
                    break;
                default:
                    property.SetValue(t, value, null);
                    break;
            }
        }
    }

3、創立Execl文件映照類

public class ReadMapper
    {
        [CellAttribute("測試1")]
        public decimal Code { get; set; }

        [CellAttribute("測試2")]
        public int Name { get; set; }

        [CellAttribute("測試3", typeof(ClassCellHander))]
        public string Group { get; set; }

        [CellAttribute("測試4")]
        public DateTime AddTime { get; set; }
    }

4、指定Execl文件途徑,經由過程通用途理辦法導出映照實體


[Test]
        public void Read1()
        {
            const string filePath = @"C:\Users\zk\Desktop\1.xls";
            XlsFileHandler<ReadMapper> handler = new XlsFileHandler<ReadMapper>(filePath);
            List<ReadMapper> readMappers = handler.ToData();
            Assert.AreEqual(readMappers.Count, 3);
        }

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved