Aspose.Cells.dll下载，ICSharpCode.SharpZipLib.dll下载
1 public void ExportExcel() { 2 var url = ""; 3 var FileName = ""; 4 var uploadFiles = Whir.ezEIP.Web.SysManagePageBase.Insten.UploadFilePath; 5 var _repo = DbHelper.CurrentDb; 6 var SchoolList = _repo.Query("select * from Whir_U_Content where CategoryId=2 and TypeId=4 and IsDel=0").Tables[0]; 7 //根据学校创建多个Excel 8 if (SchoolList.Rows.Count > 0) 9 { 10 for (int s = 0; s < SchoolList.Rows.Count; s++) 11 { 12 //规格 13 var AttrList = _repo.Query("select * from Whir_Shop_AttrValue where IsDel=0").Tables[0]; 14 if (AttrList.Rows.Count > 0) 15 { 16 Workbook book = new Workbook(); //创建工作簿 17 book.Worksheets.Clear();//清除默认创建的sheet 18 for (int a = 0; a < AttrList.Rows.Count; a++) 19 { 20 string radDate = DateTime.Now.ToString("yyyyMMddHhmmss"); 21 book.FileName = SchoolList.Rows[s]["Title"].ToString() + AttrList.Rows[a]["AttrValueName"].ToString() + radDate; 22 //年级 23 var GradeList = _repo.Query("select * from Whir_U_Content where CategoryId=3 and TypeId=4 and IsDel=0").Tables[0]; 24 //班级 25 var ClassList = _repo.Query("select * from Whir_U_Content where CategoryId=4 and TypeId=4 and IsDel=0").Tables[0]; 26 var TotalGradeClass = GradeList.Rows.Count * ClassList.Rows.Count; 27 int sheetNums = 0; 28 if (TotalGradeClass > 0) 29 { 30 if (GradeList.Rows.Count > 0) 31 { 32 for (int g = 0; g < GradeList.Rows.Count; g++) 33 { 34 if (ClassList.Rows.Count > 0) 35 { 36 for (int c = 0; c < ClassList.Rows.Count; c++) 37 { 38 book.Worksheets.Add(GradeList.Rows[g]["Title"].ToString() + ClassList.Rows[c]["Title"].ToString() + AttrList.Rows[a]["AttrValueName"].ToString()); 39 Worksheet ws = book.Worksheets[sheetNums]; 40 sheetNums++; 41 TotalGradeClass--; 42 //标题 43 Cells cells = ws.Cells;//获取行数 44 cells.Merge(0, 0, 1, 5); 45 cells[0, 0].PutValue(DateTime.Now.Year.ToString() + "年" + AttrList.Rows[a]["AttrValueName"].ToString() + "少年儿童出版社电子版汇总表"); 46 Style style = new Style();//新建单元格样式实例 47 style.HorizontalAlignment = TextAlignmentType.Center;//字体居中对齐 48 cells[0, 0].SetStyle(style); 49 //学校 50 cells[1, 0].PutValue("学校："); 51 cells[1, 0].SetStyle(style); 52 cells[1, 1].PutValue(SchoolList.Rows[s]["Title"].ToString()); 53 cells[1, 1].SetStyle(style); 54 //班级 55 cells[1, 3].PutValue("班级："); 56 cells[1, 3].SetStyle(style); 57 cells[1, 4].PutValue(GradeList.Rows[g]["Title"].ToString() + ClassList.Rows[c]["Title"].ToString()); 58 cells[1, 4].SetStyle(style); 59 //表单标题 60 cells[2, 0].PutValue("刊名"); 61 cells[2, 0].SetStyle(style); 62 cells[2, 1].PutValue("半年价"); 63 cells[2, 1].SetStyle(style); 64 cells[2, 2].PutValue("订阅量"); 65 cells[2, 2].SetStyle(style); 66 cells[2, 3].PutValue("金额"); 67 cells[2, 3].SetStyle(style); 68 cells[2, 4].PutValue("订阅人"); 69 cells[2, 4].SetStyle(style); 70 //订阅内容循环赋值 71 int row = 3; 72 var ShopProList = _repo.Query<ShopProInfo>("select * from Whir_Shop_ProInfo where IsDel=0").ToList(); 73 decimal TotalAll = 0; 74 foreach (var item in ShopProList) 75 { 76 //半年价 77 var attrInfo = _repo.Query<ShopAttrPro>("select * from Whir_Shop_AttrPro where AttrValueIDs=@0 and ProID=@1 and IsDel=0", AttrList.Rows[a]["AttrValueID"].ToString(), item.ProID).FirstOrDefault(); 78 var OrderInfo = _repo.Query<int>("select OrderID from Whir_Shop_OrderInfo where School=@0 and Grade=@1 and Class=@2 and Status=0", SchoolList.Rows[s]["Title"].ToString(), GradeList.Rows[g]["Title"].ToString(), ClassList.Rows[c]["Title"].ToString()).ToList(); 79 //订阅量 80 var OrderProduct = _repo.Query<ShopOrderProduct>("select * from Whir_Shop_OrderProduct where AttrProID=@0 and ProID=@1", AttrList.Rows[a]["AttrValueID"].ToString(), item.ProID).ToList(); 81 if (OrderProduct.Count > 0) 82 { 83 OrderProduct = OrderProduct.Where(o => OrderInfo.Contains(o.OrderID)).ToList(); 84 } 85 int Subscription = OrderProduct.Count(); 86 cells[row, 0].PutValue(item.ProName); 87 cells[row, 0].SetStyle(style); 88 cells[row, 1].PutValue(attrInfo.CostAmount); 89 cells[row, 1].SetStyle(style); 90 cells[row, 2].PutValue(Subscription); 91 cells[row, 2].SetStyle(style); 92 var Total = Subscription * attrInfo.CostAmount; 93 TotalAll += Total; 94 cells[row, 3].PutValue(Total); 95 cells[row, 3].SetStyle(style); 96 string Name = ""; 97 foreach (var Orderitem in OrderProduct) 98 { 99 Name += _repo.Query<ShopOrderInfo>("select * from Whir_Shop_OrderInfo where OrderID=@0", Orderitem.OrderID).FirstOrDefault().TakeName; 100 Name += ","; 101 } 102 Name = Name.TrimEnd(','); 103 cells[row, 4].PutValue(Name); 104 cells[row, 4].SetStyle(style); 105 row++; 106 } 107 cells[row, 0].PutValue("总金额"); 108 cells[row, 0].SetStyle(style); 109 cells[row, 3].PutValue(TotalAll); 110 ws.AutoFitColumns(); //自适应宽 111 } 112 } 113 else 114 { 115 break; 116 } 117 } 118 } 119 else 120 { 121 break; 122 } 123 } 124 else 125 { 126 break; 127 } 128 //或者使用下面的方法，输出到浏览器下载。 129 //byte[] bytes = book.SaveToStream().ToArray(); 130 //OutputClient(bytes, book.FileName); 131 //保存文件到指定地址 AppDomain.CurrentDomain.BaseDirectory 132 string Path = AppDomain.CurrentDomain.BaseDirectory; 133 book.Save(HttpContext.Current.Server.MapPath(uploadFiles) + "Excel /" + book.FileName + ".xls"); //保存 134 //LogHelper.Log("BaseDirectory" + AppDomain.CurrentDomain.BaseDirectory); 135 //LogHelper.Log("uploadFiles" + uploadFiles); 136 GC.Collect(); 137 url += HttpContext.Current.Server.MapPath(uploadFiles + "Excel/" + book.FileName + ".xls,"); 138 FileName += book.FileName+ ".xls,"; 139 book.Worksheets.Clear();//清除前面创建的sheet(防止出现生成上个表单创建的sheet) 140 } 141 } 142 else 143 { 144 break; 145 } 146 } 147 } 148 url = url.TrimEnd(','); 149 FileName = FileName.TrimEnd(','); 150 string time = DateTime.Now.Ticks.ToString(); 151 ZipFileMain(url.Split(','), FileName.Split(','), HttpContext.Current.Server.MapPath(uploadFiles + "Excel/" + time + ".zip"), 9);//压缩文件 152 DownloadFile("商城订单"+time+".zip", HttpContext.Current.Server.MapPath(uploadFiles + "Excel/" + time + ".zip"));//下载文件 153 }
1 /// <summary> 2 /// 文件下载 3 /// </summary> 4 /// <param name="fileName">下载文件名</param> 5 /// <param name="filePath">下载文件路径</param> 6 private void DownloadFile(string fileName, string filePath) 7 { 8 FileInfo fileInfo = new FileInfo(filePath); 9 HttpContext.Current.Response.Clear(); 10 HttpContext.Current.Response.ClearContent(); 11 HttpContext.Current.Response.ClearHeaders(); 12 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName); 13 HttpContext.Current.Response.AddHeader("Content-Length", fileInfo.Length.ToString()); 14 HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary"); 15 HttpContext.Current.Response.ContentType = "application/octet-stream"; 16 HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); 17 HttpContext.Current.Response.WriteFile(fileInfo.FullName); 18 HttpContext.Current.Response.Flush(); 19 File.Delete(filePath);//删除已下载文件 20 HttpContext.Current.Response.End(); 21 }
1 /// <summary> 2 /// 压缩文件 3 /// </summary> 4 /// <param name="fileName">要压缩的所有文件（完全路径)</param> 5 /// <param name="fileName">文件名称</param> 6 /// <param name="name">压缩后文件路径</param> 7 /// <param name="Level">压缩级别</param> 8 public void ZipFileMain(string[] filenames, string[] fileName, string name, int Level) 9 { 10 ZipOutputStream s = new ZipOutputStream(File.Create(name)); 11 Crc32 crc = new Crc32(); 12 //压缩级别 13 s.SetLevel(Level); // 0 - store only to 9 - means best compression 14 try 15 { 16 int m = 0; 17 foreach (string file in filenames) 18 { 19 //打开压缩文件 20 FileStream fs = File.OpenRead(file);//文件地址 21 byte[] buffer = new byte[fs.Length]; 22 fs.Read(buffer, 0, buffer.Length); 23 //建立压缩实体 24 ZipEntry entry = new ZipEntry(fileName[m].ToString());//原文件名 25 //时间 26 entry.DateTime = DateTime.Now; 27 //空间大小 28 entry.Size = fs.Length; 29 fs.Close(); 30 crc.Reset(); 31 crc.Update(buffer); 32 entry.Crc = crc.Value; 33 s.PutNextEntry(entry); 34 s.Write(buffer, 0, buffer.Length); 35 m++; 36 } 37 } 38 catch 39 { 40 throw; 41 } 42 finally 43 { 44 s.Finish(); 45 s.Close(); 46 } 47 }