反射+属性标签 通用Excel导入导_.NET_编程开发_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
热搜:
更多>>
 
您所在的位置: 程序员俱乐部 > 编程开发 > .NET > 反射+属性标签 通用Excel导入导

反射+属性标签 通用Excel导入导

 2014/10/20 14:03:55  li_jr  程序员俱乐部  我要评论(0)
  • 摘要:在做通用导入导出的时候,最关键的应该就是实体导出导入的顺序了,但是编译器在编译的时候又无法自定义属性编译的顺序,所以需要一个自定义的特性标签来指定实体类导出的顺序,然后通过自定义的比较器将属性排序因为wcf中无法对实体类的自定义特性进行描述,所以获取不到我们自定义的OrderAttribute,虽然DataMemberAttribute中的Order属性是描述属性序列化的顺序,但是因为没有对序列化排序没有特殊的要求,于是就用它代替了,起初发射之后总是倒数两个Order属性的值是正常的
  • 标签:excel 反射

 在做通用导入导出的时候,最关键的应该就是实体导出导入的顺序了,但是编译器在编译的时候又无法自定义属性编译的顺序,所以需要一个自定义的特性标签来指定实体类导出的顺序,然后通过自定义的比较器将属性排序

  因为wcf中无法对实体类的自定义特性进行描述,所以获取不到我们自定义的OrderAttribute,虽然DataMemberAttribute中的Order属性是描述属性序列化的顺序,但是因为没有对序列化排序没有特殊的要求,于是就用它代替了,起初发射之后总是倒数两个Order属性的值是正常的,其他的都为-1,后来发现生成的顺序也是按Order生成的,于是就没有深究了(如果有深入研究的朋友 希望指点一下)。

class="brush:csharp;gutter:true;">class Program
    {
        static void Main(string[] args)
        {
            //过滤掉没有打排序标签的属性
            List<PropertyInfo> pis = typeof(People).GetProperties().Where(p => p.GetCustomAttributes(typeof(OrderAttribute), false).Any()).ToList();
            //自定义比较器排序
            pis.Sort(new OrderComparator());
            Console.ReadKey();
        }
    }
    //自定义排序特性
    public class OrderAttribute : Attribute
    {
        public OrderAttribute(int order)
        {
            this.PropertyOrder = order;
        }
        public int PropertyOrder { get; set; }
    }
    //实体类
    public class People
    {
        public int ID { get; set; }
        [Order(1)]
        public string Name { get; set; }
        [Order(3)]
        public int Age { get; set; }
        [Order(2)]
        public bool Gender { get; set; }
        [Order(5)]
        public double Height { get; set; }
        [Order(4)]
        public double Weight { get; set; }
    }
    //自定义属性比较器
    public class OrderComparator : IComparer<PropertyInfo>
    {
        public int Compare(PropertyInfo x, PropertyInfo y)
        {
            OrderAttribute xOrderAttribute = x.GetCustomAttributes(typeof(OrderAttribute), false).FirstOrDefault() as OrderAttribute;
            OrderAttribute yOrderAttribute = y.GetCustomAttributes(typeof(OrderAttribute), false).FirstOrDefault() as OrderAttribute;
            return xOrderAttribute.PropertyOrder - yOrderAttribute.PropertyOrder;
        }
    }

  目前使用过操作excel的方式有NPOI和,微软提供的Microsoft.Office.Interop.Excel性能太牛X了,所以就不敢贴出来了

一、使用NPOI

NPOI导出:

/// <summary>
        /// 保存到硬盘
        /// </summary>
        /// <param name="path">保存路径@"c:\book1.xls"</param>
        /// <param name="data">数据源</param>
        /// <param name="columnsName">excel列名</param>
        public void SaveToFile<T>(List<T> data, string path, List<string> excelColumnsTitle)
        {
            if (Path.GetExtension(path).Equals(".xls"))
            {
                //excel2003
                SaveToFile2003<T>(data, path, excelColumnsTitle);
            }
            else if (Path.GetExtension(path).Equals(".xlsx"))
            {
                //excel2007
                SaveToFile2007<T>(data, path, excelColumnsTitle);
            }
            else
            {
                throw new Exception("请传入正确的excel路径");
            }

        }

  SaveToFile2003

 /// <summary>
        /// excel2003导出
        /// </summary>
        private void SaveToFile2003<T>(List<T> data, string path, List<string> excelColumnsTitle)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet"); //添加一个sheet

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0);
            for (int i = 0; i < excelColumnsTitle.Count; i++)
            {
                row1.CreateCell(i).SetCellValue(excelColumnsTitle[i]);
            }

            //过滤属性
            List<PropertyInfo> pis = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(OrderAttribute), false).Any()).ToList();
            pis.Sort(new OrderComparator());
//金额格式 NPOI.SS.UserModel.ICellStyle cellStyleDecimal = book.CreateCellStyle(); NPOI.SS.UserModel.IDataFormat formatDecimal = book.CreateDataFormat(); cellStyleDecimal.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0.00"); //单元格格式为“0.00”来表示,"¥#,##0"美元显示,"0.00%"百分比显示 //日期格式 NPOI.SS.UserModel.ICellStyle cellStyleDateTime = book.CreateCellStyle(); NPOI.SS.UserModel.IDataFormat formatDateTime = book.CreateDataFormat(); cellStyleDateTime.DataFormat = formatDateTime.GetFormat("yyyy-m"); //将数据逐步写入sheet1各个行 for (int i = 0; i < data.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet.CreateRow(i + 1); for (int j = 0; j < pis.Count; j++) { NPOI.SS.UserModel.ICell cell = rowtemp.CreateCell(j); if (pis[j].PropertyType.IsAssignableFrom(typeof(string))) { cell.SetCellValue(pis[j].GetValue(data[i], null).ToString()); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(int))) { cell.SetCellValue(Convert.ToInt32(pis[j].GetValue(data[i], null))); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(decimal))) { cell.CellStyle = cellStyleDecimal; cell.SetCellValue(Convert.ToDouble(pis[j].GetValue(data[i], null))); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(DateTime))) { cell.CellStyle = cellStyleDateTime; cell.SetCellValue(Convert.ToDateTime(pis[j].GetValue(data[i], null))); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(double))) { cell.CellStyle = cellStyleDecimal; cell.SetCellValue(Convert.ToDouble(pis[j].GetValue(data[i], null))); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(bool))) { cell.SetCellValue(Convert.ToBoolean(pis[j].GetValue(data[i], null))); } } } // 写入到客户端 using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write)) { book.Write(fs); } }

  SaveToFile2007

 /// <summary>
        /// excel2007导出
        /// </summary>
        private void SaveToFile2007<T>(List<T> data, string path, List<string> excelColumnsTitle)
        {
            NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet"); //添加一个sheet

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0);
            for (int i = 0; i < excelColumnsTitle.Count; i++)
            {
                row1.CreateCell(i).SetCellValue(excelColumnsTitle[i]);
            }

            //过滤属性
            List<PropertyInfo> pis = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(OrderAttribute), false).Any()).ToList();
pis.Sort(new OrderComparator()); //金额格式 NPOI.SS.UserModel.ICellStyle cellStyleDecimal = book.CreateCellStyle(); NPOI.SS.UserModel.IDataFormat formatDecimal = book.CreateDataFormat(); cellStyleDecimal.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0.00"); //单元格格式为“0.00”来表示,"¥#,##0"美元显示,"0.00%"百分比显示 //日期格式 NPOI.SS.UserModel.ICellStyle cellStyleDateTime = book.CreateCellStyle(); NPOI.SS.UserModel.IDataFormat formatDateTime = book.CreateDataFormat(); cellStyleDateTime.DataFormat = formatDateTime.GetFormat("yyyy-m"); //将数据逐步写入sheet1各个行 for (int i = 0; i < data.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet.CreateRow(i + 1); for (int j = 0; j < pis.Count; j++) { NPOI.SS.UserModel.ICell cell = rowtemp.CreateCell(j); if (pis[j].PropertyType.IsAssignableFrom(typeof(string))) { cell.SetCellValue(pis[j].GetValue(data[i], null).ToString()); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(int))) { cell.SetCellValue(Convert.ToInt32(pis[j].GetValue(data[i], null))); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(decimal))) { cell.CellStyle = cellStyleDecimal; cell.SetCellValue(Convert.ToDouble(pis[j].GetValue(data[i], null))); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(DateTime))) { cell.CellStyle = cellStyleDateTime; cell.SetCellValue(Convert.ToDateTime(pis[j].GetValue(data[i], null))); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(double))) { cell.CellStyle = cellStyleDecimal; cell.SetCellValue(Convert.ToDouble(pis[j].GetValue(data[i], null))); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(bool))) { cell.SetCellValue(Convert.ToBoolean(pis[j].GetValue(data[i], null))); } } } // 写入到客户端 using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write)) { book.Write(fs); } }

NPOI导入:

 /// <summary>
        /// 返回List数据
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="path">excel文件路径</param>
        /// <returns></returns>
        public List<T> ImportExcelToList<T>(string path)
        {
            if (Path.GetExtension(path).Equals(".xls"))
            {
                //excel2003
                return ImportExcelToList2003<T>(path);
            }
            else if (Path.GetExtension(path).Equals(".xlsx"))
            {
                //excel2007
                return ImportExcelToList2007<T>(path);
            }
            else
            {
                throw new Exception("请传入正确的excel路径");
            }
        }

  ImportExcelToList2003

/// <summary>
        /// excel2003导入
        /// </summary>
        private List<T> ImportExcelToList2003<T>(string path)
        {
            List<T> list = new List<T>();
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(new FileStream(path, FileMode.Open, FileAccess.Read));
            NPOI.HSSF.UserModel.HSSFSheet sheet = book.GetSheet("Sheet") as NPOI.HSSF.UserModel.HSSFSheet;
            if (sheet != null)
            {
                List<PropertyInfo> pis = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(OrderAttribute), false).Any()).ToList();
pis.Sort(new OrderComparator()); //导入数据 for (int i = 1; i <= sheet.LastRowNum; i++) //获得所有行数 { T model = Activator.CreateInstance<T>(); NPOI.SS.UserModel.IRow row = sheet.GetRow(i); //读取当前行数据 if (row != null) { try { for (int j = 0; j < row.Cells.Count; j++) { if (pis[j].PropertyType.IsAssignableFrom(typeof(string))) { pis[j].SetValue(model, row.GetCell(j).StringCellValue, null); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(int))) { pis[j].SetValue(model, (int)row.GetCell(j).NumericCellValue, null); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(decimal))) { pis[j].SetValue(model, (decimal)row.GetCell(j).NumericCellValue, null); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(DateTime))) { pis[j].SetValue(model, row.GetCell(j).DateCellValue, null); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(double))) { pis[j].SetValue(model, row.GetCell(j).NumericCellValue, null); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(bool))) { pis[j].SetValue(model, row.GetCell(j).BooleanCellValue, null); } } list.Add(model); } catch { } } } } return list; }

  ImportExcelToList2007

 /// <summary>
        /// excel2007导入
        /// </summary>
        private List<T> ImportExcelToList2007<T>(string path)
        {
            List<T> list = new List<T>();
            NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook(new FileStream(path, FileMode.Open, FileAccess.Read));
            NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(0);  
            if (sheet != null)
            {
                List<PropertyInfo> pis = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(OrderAttribute), false).Any()).ToList();
pis.Sort(new OrderComparator()); //导入数据 for (int i = 1; i <= sheet.LastRowNum; i++) //获得所有行数 { T model = Activator.CreateInstance<T>(); NPOI.SS.UserModel.IRow row = sheet.GetRow(i); //读取当前行数据 if (row != null) { try { for (int j = 0; j < row.Cells.Count; j++) { if (pis[j].PropertyType.IsAssignableFrom(typeof(string))) { pis[j].SetValue(model, row.GetCell(j).StringCellValue, null); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(int))) { pis[j].SetValue(model, (int)row.GetCell(j).NumericCellValue, null); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(decimal))) { pis[j].SetValue(model, (decimal)row.GetCell(j).NumericCellValue, null); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(DateTime))) { pis[j].SetValue(model, row.GetCell(j).DateCellValue, null); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(double))) { pis[j].SetValue(model, row.GetCell(j).NumericCellValue, null); } else if (pis[j].PropertyType.IsAssignableFrom(typeof(bool))) { pis[j].SetValue(model, row.GetCell(j).BooleanCellValue, null); } } list.Add(model); } catch { } } } } return list; }

  

二、使用Acey.ExcelX,以前的时候性能是忧于NPOI的,而且支持excel03和07,相较NPOI有时候还能省去cell类型的判断,只是是第三方收费的,所以导出后会有广告

Acey.ExcelX导出

 /// <summary>
        /// 保存到硬盘
        /// </summary>
        /// <param name="path">保存路径@"c:\book1.xls"</param>
        /// <param name="data">数据源</param>
        /// <param name="columnsName">excel列名</param>
        public void SaveToFile<T>(List<T> data, string path, List<string> excelColumnsTitle)
        {
            IWorkbook workbook = ExcelxApplication.CreateWorkbook();
            IWorksheet worksheet = workbook.Worksheets[0];
            for (int i = 0; i < excelColumnsTitle.Count; i++)
            {
                worksheet.Cells[0, i].Value = excelColumnsTitle[i];
            }
            List<PropertyInfo> pis = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(OrderAttribute), false).Any()).ToList();
            pis.Sort(new OrderComparator());
            for (int i = 0; i < data.Count; i++)
            {
                for (int j = 0; j < pis.Count; j++)
                {
                    worksheet.Cells[i + 1, j].Value = pis[j].GetValue(data[i], null);
                }
            }
            workbook.SaveAs(path);//保存到硬盘
        }

Acey.ExcelX导入

/// <summary>
        /// 返回List数据
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="path">excel文件路径</param>
        /// <returns></returns>
        public List<T> ImportExcelToList<T>(string path)
        {
            List<T> list = new List<T>();
            Type type = typeof(T);
            //创建Workbook对象通过打开指定的Excel文档。
            IWorkbook workbook = ExcelxApplication.Open(path);
            //获取以0为基数的Worksheet对象。
            IWorksheet worksheet = workbook.Worksheets[0];
            //获取工作表中最大数据行。
            int maxRow = worksheet.MaxDataRow;
            //获取工作表中最大数据列。
            int maxCol = worksheet.MaxDataColumn;
            //创建指定区域的对象。
            IRange range = worksheet.Cells.CreateRange(0, 0, maxRow, maxCol);
            //将该区域对象的数据导出到DataTable对象中。
            DataTable table = range.ExportDataTable();
            //返回该DataTable对象。
            for (int row = 1; row <= maxRow; row++)
            {
                T model = Activator.CreateInstance<T>();
                List<PropertyInfo> pis = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(OrderAttribute), false).Any()).ToList();
                pis.Sort(new OrderComparator());
                for (int col = 0; col <= maxCol; col++)
                {
                    ICell cell = worksheet.Cells[row, col];
                    if (pis[col].PropertyType.IsAssignableFrom(typeof(string)))
                    {
                        pis[col].SetValue(model, Convert.ToString(cell.Value), null);
                    }
                    else if (pis[col].PropertyType.IsAssignableFrom(typeof(int)))
                    {
                        pis[col].SetValue(model, Convert.ToInt32(cell.Value), null);
                    }
                    else if (pis[col].PropertyType.IsAssignableFrom(typeof(decimal)))
                    {
                        pis[col].SetValue(model, Convert.ToDecimal(cell.Value), null);
                    }
                    else if (pis[col].PropertyType.IsAssignableFrom(typeof(DateTime)))
                    {
                        pis[col].SetValue(model, Convert.ToDateTime(cell.Value), null);
                    }
                    else if (pis[col].PropertyType.IsAssignableFrom(typeof(double)))
                    {
                        pis[col].SetValue(model, Convert.ToDouble(cell.Value), null);
                    }
                    else if (pis[col].PropertyType.IsAssignableFrom(typeof(bool)))
                    {
                        pis[col].SetValue(model, Convert.ToBoolean(cell.Value), null);
                    }
                }
                list.Add(model);
            }
            return list;
        }

Acey.ExcelX输出到web

 /// <summary>
        ///输出到网页
        ///</summary>
        ///<param name="context">HttpContext</param>
        ///<param name="fileName">"book1.xls"</param>
        ///<param name="data">数据源</param>
        public static void RenderToBrowser(DataTable data, HttpContext context, string fileName)
        {
            IWorkbook workbook = ProcessWorkBook(data);
            workbook.SaveToHttpResponse(context.Response, fileName, false);//输出到网页
        }

 

  

发表评论
用户名: 匿名