首页 > 代码库 > NPOI从数据库中导出数据到Excel
NPOI从数据库中导出数据到Excel
首先要添加NPOI.dll程序集 https://yunpan.cn/cMeSTELJSXmJJ 访问密码 8d83
把里面的程序集都添加到引用里
下面的代码是从数据库导出到Excel
1 public void DaoChuExcel(string path) 2 { //path是导出的excel要保存的路径 3 using (Stream fs = new FileStream(path, FileMode.Create, FileAccess.Write)) 4 { //创建一个表 5 XSSFWorkbook work = new XSSFWorkbook(); 6 //创建页 7 ISheet sheet = work.CreateSheet("zx"); 8 List<MemberInfo> list = memDal.GetAllMemberByDelFlag(0); 9 //创建行10 for (int i = 0; i < list.Count; i++)11 {12 IRow row = sheet.CreateRow(i); //定义excel中的一行13 14 17 //下面的Icell代表行中的单元格18 ICell cell2 = row.CreateCell(0, CellType.String); 19 cell2.SetCellValue(list[i].MemName);20 21 ICell cell3 = row.CreateCell(1, CellType.String);22 cell3.SetCellValue(list[i].MemMobilePhone);23 24 ICell cell4 = row.CreateCell(2, CellType.String);25 cell4.SetCellValue(list[i].MemAddress);26 27 ICell cell5 = row.CreateCell(3, CellType.Numeric);28 cell5.SetCellValue(list[i].MemType);29 30 ICell cell6 = row.CreateCell(4, CellType.String);31 cell6.SetCellValue(list[i].MemNum);32 33 ICell cell7 = row.CreateCell(5, CellType.String);34 cell7.SetCellValue(list[i].MemGender);35 36 ICell cell8 = row.CreateCell(6, CellType.Numeric);37 cell8.SetCellValue(list[i].MemDiscount);38 39 ICell cell9 = row.CreateCell(7, CellType.Numeric);40 cell9.SetCellValue(list[i].MemMoney);41 42 ICell cell10 = row.CreateCell(8, CellType.Numeric);43 cell10.SetCellValue(list[i].DelFlag);44 45 ICell cell11 = row.CreateCell(9, CellType.String);46 cell11.SetCellValue(list[i].SubTime.ToShortDateString());47 48 ICell cell12 = row.CreateCell(10, CellType.Numeric);49 cell12.SetCellValue(list[i].MemIntegral);50 51 ICell cell13 = row.CreateCell(11, CellType.String);52 cell13.SetCellValue(list[i].MemEndTime.ToShortDateString());53 54 ICell cell14 = row.CreateCell(12, CellType.String);55 cell14.SetCellValue(list[i].MemBirthday.ToShortDateString());56 }57 work.Write(fs); //最后把表写入IO流中58 59 }60 61 }
下面是从excel中导入数据库
1 public void ReadExcel(string path) 2 { 3 using (Stream fs = new FileStream(path,FileMode.Open,FileAccess.Read)) //从指定路径读入excel表 4 { 5 List<MemberInfo> list = new List<MemberInfo>(); 6 //工作表 7 IWorkbook workbook = WorkbookFactory.Create(fs); 8 //获取页 9 ISheet sheet = workbook.GetSheetAt(0);10 //遍历行11 for (int i = 0; i <=sheet.LastRowNum; i++)12 {13 MemberInfo mem = new MemberInfo();14 //获取行15 IRow row = sheet.GetRow(i);16 17 mem.MemName = row.GetCell(0).StringCellValue;18 mem.MemMobilePhone = row.GetCell(1).StringCellValue;19 mem.MemAddress = row.GetCell(2).StringCellValue;20 mem.MemType = Convert.ToInt32(row.GetCell(3).NumericCellValue);21 mem.MemNum = row.GetCell(4).StringCellValue;22 mem.MemGender = row.GetCell(5).StringCellValue;23 mem.MemDiscount = Convert.ToDouble(row.GetCell(6).NumericCellValue);24 mem.MemMoney = row.GetCell(7).NumericCellValue;25 mem.DelFlag = Convert.ToInt32(row.GetCell(8).NumericCellValue);26 27 mem.SubTime =Convert.ToDateTime(row.GetCell(9).StringCellValue);28 mem.MemIntegral = Convert.ToInt32(row.GetCell(10).NumericCellValue);29 mem.MemEndTime = Convert.ToDateTime(row.GetCell(11).StringCellValue);30 mem.MemBirthday = Convert.ToDateTime(row.GetCell(12).StringCellValue);31 list.Add(mem);32 }33 memDal.AddMemberInfo(list); //把数据体添加到数据库34 }35 36 37 }
NPOI从数据库中导出数据到Excel
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。