导出Excel解决方案之一NOPI
一、概要
导出Excel这个功能相信很多人都做过，但是实现这个功能解决方案有好几种，今天我未大家介绍一种比较新的，其实也不新了- -！它叫NPOI，可以完美操作EXCEl的导入和导出操作，让我们一起看下代码吧(都是园子里大神写的，我借鉴一下，望海涵)
二、导入DLL
实现NOPI需要使用第三方DLL，官方下载地址是：http://npoi.codeplex.com/
三、关于大数据量的导入问题
大家都知道Excel2003每个sheet最大的行数是65536，所以大于65535的数据需要写入另一个sheet里，这里是需要注意的。
四、代码
1 public void ProcessRequest(HttpContext context) 2 { 3 context.Response.ContentType = "application/x-excel"; 4 string filename = HttpUtility.UrlEncode("人员档案.xls");//文件名进行url编码，防止乱码 5 context.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename); 6 7 var list = bArchive.GetAllArchivesBase(); 8 9 HSSFWorkbook workBook = new HSSFWorkbook(); 10 ISheet sheet1 = workBook.CreateSheet("表单一"); 11 //sheet列表,防止记录条数大于65535 12 List<NPOI.SS.UserModel.ISheet> sheetList = new List<NPOI.SS.UserModel.ISheet>(); 13 sheetList.Add(sheet1); 14 //给sheet1添加数据 15 SheetFirst(sheet1, workBook, list); 16 //给其他sheet添加数据 从1开始：去掉第一个sheet +1是因为有一个表头 17 int rows = list.Count + 1; 18 int p = rows % 65535 == 0 ? rows / 65535 : (rows / 65535) + 1; 19 for (int i = 1; i < p; i++) 20 { 21 ISheet sheet = workBook.CreateSheet("sheet" + (i + 1).ToString()); 22 //为sheet添加数据 23 SheetElse(sheet, (i - 1) * 65535 + 65535, list); 24 } 25 // 写入到客户端 26 System.IO.MemoryStream ms = new System.IO.MemoryStream(); 27 workBook.Write(ms); 28 context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff"))); 29 context.Response.BinaryWrite(ms.ToArray()); 30 workBook = null; 31 ms.Close(); 32 ms.Dispose(); 33 } 34 35 //第一个Sheet，Excel最大行数是65536行 36 protected void SheetFirst(NPOI.SS.UserModel.ISheet sheet1, NPOI.HSSF.UserModel.HSSFWorkbook book, List<ArchivesBase> datalist) 37 { 38 //标题 39 NPOI.SS.UserModel.ICell cellTitle = sheet1.CreateRow(0).CreateCell(0); 40 //cellTitle.SetCellValue("水位月报表--" + drpCategory.SelectedItem.Text); 41 cellTitle.SetCellValue("人事档案表"); 42 //设置标题行样式 43 NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); 44 style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; 45 NPOI.SS.UserModel.IFont font = book.CreateFont(); 46 font.FontHeight = 20 * 20; 47 style.SetFont(font); 48 cellTitle.CellStyle = style; 49 //合并标题行 50 sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9)); 51 52 //给sheet1添加第一行的头部标题 53 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(1); 54 row1.CreateCell(0).SetCellValue("档案号"); 55 row1.CreateCell(1).SetCellValue("姓名"); 56 row1.CreateCell(2).SetCellValue("性别"); 57 row1.CreateCell(3).SetCellValue("档案类型"); 58 row1.CreateCell(4).SetCellValue("毕业时间"); 59 row1.CreateCell(5).SetCellValue("毕业学校"); 60 row1.CreateCell(6).SetCellValue("身份证"); 61 //将数据逐步写入sheet1各个行 62 for (int i = 0; i < 65535; i++) 63 { 64 NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); 65 rowtemp.CreateCell(0).SetCellValue(datalist[i].ArchivesNO == null ? "" : datalist[i].ArchivesNO.ToString()); 66 rowtemp.CreateCell(1).SetCellValue(datalist[i].PeopleName == null ? "" : datalist[i].PeopleName.ToString()); 67 rowtemp.CreateCell(2).SetCellValue(datalist[i].PeopleSex == null ? "" : datalist[i].PeopleSex.ToString()); 68 rowtemp.CreateCell(3).SetCellValue(datalist[i].ArchivesClass == null ? "" : datalist[i].ArchivesClass.ToString()); 69 rowtemp.CreateCell(4).SetCellValue(datalist[i].GraduateTime == null ? "" : datalist[i].GraduateTime.ToString()); 70 rowtemp.CreateCell(5).SetCellValue(datalist[i].ArchivesSchool == null ? "" : datalist[i].ArchivesSchool.ToString()); 71 rowtemp.CreateCell(6).SetCellValue(datalist[i].PeopleCardNO == null ? "" : datalist[i].PeopleCardNO.ToString()); 72 } 73 } 74 75 //其他sheet 76 protected void SheetElse(NPOI.SS.UserModel.ISheet sheet, int j, List<ArchivesBase> datalist) 77 { 78 //将数据逐步写入sheet1各个行 79 for (int i = 0; j + i < datalist.Count; i++)//65535 80 { 81 NPOI.SS.UserModel.IRow rowtemp = sheet.CreateRow(i); 82 rowtemp.CreateCell(0).SetCellValue(datalist[j + i].ArchivesNO == null ? "" : datalist[j + i].ArchivesNO.ToString()); 83 rowtemp.CreateCell(1).SetCellValue(datalist[j + i].PeopleName == null ? "" : datalist[j + i].PeopleName.ToString()); 84 rowtemp.CreateCell(2).SetCellValue(datalist[j + i].PeopleSex == null ? "" : datalist[j + i].PeopleSex.ToString()); 85 rowtemp.CreateCell(3).SetCellValue(datalist[j + i].ArchivesClass == null ? "" : datalist[j + i].ArchivesClass.ToString()); 86 rowtemp.CreateCell(4).SetCellValue(datalist[j + i].GraduateTime == null ? "" : datalist[j + i].GraduateTime.ToString()); 87 rowtemp.CreateCell(5).SetCellValue(datalist[j + i].ArchivesSchool == null ? "" : datalist[j + i].ArchivesSchool.ToString()); 88 rowtemp.CreateCell(6).SetCellValue(datalist[j + i].PeopleCardNO == null ? "" : datalist[j + i].PeopleCardNO.ToString()); 89 } 90 }
以上代码园子里有很多，o(∩_∩)o 我写下来防止以后忘记，也顺便学习下，话说这个NPOI真的很好用哦~~
