第一次写博文。。。
.NET中一般使用NPOI进行Excel文件的导入导出,为了方便使用,写了一个简单的封装。
运用到的相关知识:
1)使用NPOI创建Excel文件并写入数据
2)泛型
3)特性和反射
从最主要的一个函数说起:
public static void ExportDataToExcelFile<T>(string strFilePath, List<T> data)
where T : class
1)函数的功能:将一个泛型List集合中的数据导出到一个Excel文件中去
2)第一个参数指定了Excel文件的路径,文件不存在则创建该文件,存在则覆盖
3)data是一个List集合,其中类型参数T要求是一个类(不知道能不能约束它必须拥有某些特性)
4)这里也可以不使用特性,只是反射出属性来,但是为了获得更多的信息(工作表名称、列的名称)我还是加上了特性
程序中用到的两个自定义特性类:
ExcelSheetAttribute特性类:
1 [AttributeUsage(AttributeTargets.Class)] 2 public sealed class ExcelSheetAttribute : Attribute 3 { 4 public ExcelSheetAttribute(string sheetName) 5 { 6 SheetName = sheetName; 7 } 8 9 private string _SheetName; 10 public string SheetName 11 { 12 get { return _SheetName; } 13 set { _SheetName = value; } 14 } 15 }
说明:该特性只能使用在class上面,如果为某个类使用该特性必须指定一个位置参数sheetName,该参数最终将指示工作表的名称,并不是要求函数ExportDataToExcelFile中的类型参数T必须运用该特性,运用了则使用提供的名称,没有使用该特性默认名称为“sheet1”。
ExcelColumn特性类:
1 [AttributeUsage(AttributeTargets.Property)] 2 public sealed class ExcelColumn : Attribute 3 { 4 public ExcelColumn(CellType type=CellType.STRING) //默认是字符串类型 5 { 6 ColumnType = type; 7 } 8 9 private CellType _ColumnType; 10 private string _ColumnName; 11 12 public CellType ColumnType 13 { 14 get { return _ColumnType; } 15 set { _ColumnType = value; } 16 } 17 18 public string ColumnName 19 { 20 get { return _ColumnName; } 21 set { _ColumnName = value; } 22 } 23 }
说明:该特性只能使用在属性上,其中包含两个属性ColumnType和ColumnName,其中ColumnType的类型是NPOI中提供表示Excel表格中单元格数据类型的一个枚举,在写入Excel文件时会使用该特性属性,而ColumnName则表示的是最终在Excel文件的头部显示的信息,表头名称。
一个使用了以上两个自定义特性的类示例:
1 [ExcelSheet("学生信息")] 2 public class StudentSheet 3 { 4 [ExcelColumn(CellType.NUMERIC,ColumnName="编号")] 5 public int ID { get; set; } 6 7 [ExcelColumn(CellType.STRING,ColumnName="姓名")] 8 public string Name { get; set; } 9 10 [ExcelColumn(CellType.STRING,ColumnName="性别")] 11 public string Sex { get; set; } 12 13 [ExcelColumn(ColumnName = "生日")] 14 public string Birthdate { get; set; } 15 16 [ExcelColumn(ColumnName = "学习日期")] 17 public string Studydate { get; set; } 18 19 [ExcelColumn(CellType.STRING,ColumnName="地址")] 20 public string Address { get; set; } 21 22 [ExcelColumn(CellType.STRING, ColumnName = "电子邮件")] 23 public string Email { get; set; } 24 25 [ExcelColumn(CellType.STRING, ColumnName = "电话")] 26 public string Telephone { get; set; } 27 }
说明:在具体使用的时候我们只需要定义这样的一个类,在类和属性上面添加适当的特性信息,再调用上面提到过的函数,就能拿到我们想要的数据,这里属性定义的顺序将决定最后Excel文件中各个列的顺序。
ExportDataToExcelFile函数:
1 public static void ExportDataToExcelFile<T>(string strFilePath, List<T> data) 2 where T : class 3 { 4 Type type = typeof(T); 5 //获取工作表的名称 6 string strSheetName = "sheet1"; 7 if (type.IsDefined(typeof(ExcelSheetAttribute), false)) 8 { 9 ExcelSheetAttribute obj=(ExcelSheetAttribute)type.GetCustomAttributes(typeof(ExcelSheetAttribute),false)[0]; 10 strSheetName = obj.SheetName; 11 } 12 13 PropertyInfo[] props = type.GetProperties(); 14 //获取每个属性上的列类型特性 15 List<CellType> celltypes = new List<CellType>();//表示列的类型信息的list 16 List<string> columnnames = new List<string>();//表示列的表头信息的list 17 foreach (var prop in props) 18 { 19 object[] attrs = prop.GetCustomAttributes(false); 20 foreach (var attr in attrs) 21 { 22 ExcelColumn ec = attr as ExcelColumn; 23 if (ec != null) 24 { 25 celltypes.Add(ec.ColumnType); 26 columnnames.Add(ec.ColumnName); 27 } 28 } 29 } 30 31 using (Workbook work = new HSSFWorkbook()) //创建工作簿 32 { 33 using (Sheet sheet = work.CreateSheet(strSheetName)) //创建表格 34 { 35 Row header = sheet.CreateRow(0); 36 for (int i = 0; i < columnnames.Count; i++) 37 { 38 header.CreateCell(i).SetCellValue(columnnames[i]); 39 } 40 for (int i = 1; i <= data.Count; i++) 41 { 42 Row row = sheet.CreateRow(i); 43 for (int j = 0; j < celltypes.Count; j++) //列数由拥有特性的属性个数决定,而不是属性个数,即类中没有标志ExcelColumn特性的属性不会写到文件中 44 { 45 Cell cell = row.CreateCell(j); 46 cell.SetCellType(celltypes[j]); 47 object value = props[j].GetValue(data[i-1], null); 48 if (value ==null) 49 { 50 continue; 51 } 52 cell.SetCellValue(value.ToString()); 53 } 54 } 55 56 FileStream fs = File.OpenWrite(strFilePath); 57 work.Write(fs); 58 fs.Close(); 59 } 60 } 61 }
说明:
引用了NPOI程序集后需要添加的两个命名空间:
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
整个函数可以大致分为两个部分。
第一部分是反射类型参数T的类型信息、属性信息,所有获得工作表的名称、表中每一列的数据类型和表头名称,其中两个List(celltypes、columnnames )是一一对应的关系,在后面for循环里面创建Cell对象设置值和类型的时候用下标j获得的是相对应的值;
第二部分是根据传递进来的data创建Excel文件并写入数据。首先,创建一个HSSFWorkbook对象,它代表着一个工作簿,即一个Excel文件;紧接着使用该对象创建一个HSSFSheet 对象,它代表了一个工作表(Sheet),这里的HSSFWorkbook和HSSFSheet分别是实现了Workbook和Sheet接口两个对象;其次,创建表头信息,将显示在表格的第一行;外部的for循环负责创建表格数据行,下标由data.count决定;内循环负责创建行中的每一个单元格并为之设定类型和值,循环下标由celltypes.Count决定,表示类中没有标志ExcelColumn特性的属性不会写到文件中;最后,根据文件路径打开文件流,写入数据,关闭文件流;
客户端代码:
1 string sql = @"SELECT TOP 1000 [stuId] 2 ,[stuName] 3 ,[stuSex] 4 ,[stuBirthdate] 5 ,[stuStudydate] 6 ,[stuAddress] 7 ,[stuEmail] 8 ,[stuPhone] 9 FROM [TestDataBase].[dbo].[Student]"; 10 List<StudentSheet> students=new List<StudentSheet>(); 11 using (SqlDataReader reader = Common.SQLHelper.ExecuteDataReader(sql)) 12 { 13 while (reader.Read()) 14 { 15 StudentSheet student = new StudentSheet(); 16 student.ID = Convert.ToInt32(reader[0]); 17 student.Name = reader[1].ToString(); 18 student.Sex = reader[2].ToString(); 19 student.Birthdate = reader[3].ToString(); 20 student.Studydate = reader[4].ToString(); 21 student.Address = reader[5].ToString(); 22 student.Email = reader[6].ToString(); 23 student.Telephone = reader[7].ToString(); 24 students.Add(student); 25 } 26 } 27 Common.ExcelHelper.ExportDataToExcelFile<StudentSheet>("C:\1.xls", students); 28 MessageBox.Show("ok");
最后结果:
缺点:
对于大数据量的文件处理会非常蛮,测试了一个6万行数据的表格,一共也就十几m用了居然十几秒的时候,所以可以考虑使用多线程,其次大数据量对内存的消耗很大,这时候就需要使用DataReader来一条一条的读取和写入了!