asp.net大数据导出execl实现分开压缩并下载

/// 
/// 导出数据到EXCEL 多个表的/// 
/// 数据集/// 导出的 EXCEL 路径/// EXCEL 工作簿的名字/// 表头/// 
     
      返回文件路径
     public static string ExportToExcels(System.Data.DataSetds, string AbosultedFilePath, string[]name, string title){try{string path= ConfigHelper.GetValue("execlFile");//判断路径是否存在if (Directory.Exists(path)){//删除文件夹及文件foreach (string d in Directory.GetFileSystemEntries(path)){if (File.Exists(d)){File.Delete(d); }}ectory.Delete(path, true);Dir}int PageIndex= 0;if (ds.Tables.Count<= 0)return string.Empty;for (int t= 0; t < ds.Tables.Count; t++){System.Data.DataTabledt = ds.Tables[t];int count = dt.Rows.Count;//获取datatable内数据量int pagecount = 5000; //每页的数据PageIndex = Pagount(count, pagecount); //获取分页数string filename= t.ToString()== "0" ? "Area_Statistics" : "IP_statistics";//存在分页时 创建新目录保存新execl文件if (!Directory.Exists(path)){Directory.CreateDirectory(path);}for (int i= 1; i <= PageIndex; i++){//将模板文件复制到新目录下string fileName= path + "/" + filename + i + ".xls";//根据页码获取DataTable内的数据System.Data.DataTableexeclDT = GetPagedTable(dt, i, pagecount);//将DataTable内的数据写入execlRenderDataTableToExcel(execlDT,fileName);} }//完成写入后 压缩文件ZipDir(path,path, 2, title);return path+ title + ".zip";}catch (Exceptionex){Logger.Error("DataTable转execl失败" + ex.Message);return string.Empty;}}#region 压缩文件/// 
/// 压缩文件夹/// 
/// 文件夹路径/// 输出文件路径/// 设置缓存大小///压缩后的文件名称public static void ZipDir(string DirToZip, string ZipedFile, int CompressionLevel, string fileName){try{//压缩文件为空时默认与压缩文件夹同一级目录if (ZipedFile== string.Empty){ZipedFile =DirToZip.Substring(DirToZip.LastIndexOf("\\") + 1);ZipedFile =DirToZip.Substring(0, DirToZip.LastIndexOf("\\"))+ "\\" + ZipedFile + ".zip";}if (System.IO.Path.GetExtension(ZipedFile)!= ".zip"){ZipedFile =ZipedFile + fileName + ".zip";}using (ZipOutputStreamzipoutputstream= new ZipOutputStream(System.IO.File.Create(ZipedFile))){zipoutputstream.SetLevel(CompressionLevel);Crc32crc = new Crc32();System.IO.DirectoryInfomyDir = new DirectoryInfo(DirToZip);List
     
      fileList = GetAllFiles(DirToZip);foreach (DictionaryEntryitem in fileList){//可能存在文件夹无法访问情况 需捕捉异常，根据实际情况返回try{System.IO.FileStreamfs = System.IO.File.OpenRead(item.Key.ToString());byte[]buffer = new byte[fs.Length];fs.Read(buffer, 0,buffer.Length);ZipEntryentry = new ZipEntry(item.Key.ToString().Substring(DirToZip.Length+ 1));entry.DateTime = (DateTime)item.Value;entry.Size = fs.Length;fs.Flush();fs.Close();crc.Update(crc.Reset();buffer);entry.Crc = crc.Value;zipoutputstream.PutNextEntry(entry);zipoutputstream.Write(buffer, 0,buffer.Length);}catch (Exceptionex){Logger.Error("压缩文件夹：" + ex.Message);}}}}catch (Exceptionex){Logger.Error("压缩execl文件夹：" + ex.Message);}}/// 
      
/// 获取所有文件/// 
/// 
      private static List
      
       GetAllFiles(string dir){try{List
       
        dictonary = new List
        
         ();if (!System.IO.Directory.Exists(dir)){return dictonary;}else{System.IO.DirectoryInforoot = new System.IO.DirectoryInfo(dir);System.IO.FileSystemInfo[]arrary = root.GetFileSystemInfos();for (int i= 0; i < arrary.Length; i++){dictonary.Add(new DictionaryEntry(arrary[i].FullName,arrary[i].LastWriteTime));} }return dictonary;}catch (Exceptionex){Logger.Error("获取文件夹下的所有文件" + ex.Message);return null;}}#endregion #region DataTable分页/// 
         
/// DataTable分页/// 
/// 
         
          DataTable/// 
          页索引,注意：从1开始/// 
          每页大小/// 
          
           分好页的DataTable数据
           第1页 每页10条public static System.Data.DataTableGetPagedTable(System.Data.DataTabledt, int PageIndex, int PageSize){if (PageIndex== 0) { return dt; }System.Data.DataTablenewdt = dt.Copy();newdt.Clear();int rowbegin= (PageIndex - 1) * PageSize;int rowend= PageIndex * PageSize;if (rowbegin>= dt.Rows.Count){ return newdt;}if (rowend> dt.Rows.Count){rowend = dt.Rows.Count; }for (int i= rowbegin; i <= rowend - 1; i++){DataRownewdr = newdt.NewRow();DataRowdr = dt.Rows[i];foreach (DataColumncolumn in dt.Columns){newdr[column.ColumnName] = dr[column.ColumnName];}wdt.Rows.Add(newdr);}ne return newdt;}/// 
          
/// 返回分页的页数/// 
/// 
          总条数/// 
          每页显示多少条/// 
          
           如果 结尾为0：则返回1
          public static int Pagount(int count, int pageye){int page= 0;int sesepage= pageye;if (count% sesepage == 0) { page = count / sesepage; }else {page = (count / sesepage) + 1; }if (page== 0) { page += 1; }return page;}#endregion#region Datatable转Execl/// 
          
/// 把Datatable中的数据保存成指定的Excel文件/// 
/// 
          需要转成execl的DateTable/// 
          详细的文件路径带文件名与格式public static void RenderDataTableToExcel(System.Data.DataTableSourceTable, string FileName){Logger.Info("进入方法RenderDataTableToExcel 文件名：" + FileName);HSSFWorkbookworkbook = new HSSFWorkbook();MemoryStream_ms = new MemoryStream();// 创建Excel文件的SheetSheetsheet = workbook.CreateSheet("Sheet1");sheet.SetColumnWidth(0, 30 * 256); //设置单元格的宽度sheet.SetColumnWidth(1, 20 * 256);//设置单元格的宽度sheet.SetColumnWidth(2, 20 * 256);//设置单元格的宽度// 创建行RowheaderRow = sheet.CreateRow(0);// 把Datatable中的列名添加Sheet中第一列中作为表头foreach (DataColumncolumn in SourceTable.Columns)headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);int rowIndex= 1;// 循环Datatable中的行和列添加数据到Excel中foreach (DataRowrow in SourceTable.Rows){RowdataRow = sheet.CreateRow(rowIndex);foreach (DataColumncolumn in SourceTable.Columns){dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());}rowIndex++;}try{MemoryStreamms = _ms as MemoryStream;workbook.Write(ms);_ms.Flush();= 0;_ms.PositionFileStreamfs = new FileStream(FileName, FileMode.Create,FileAccess.ReadWrite);byte[]data = ms.ToArray();fs.Write(data, 0,data.Length);fs.Flush();fs.Close();ms.Close();ms.Flush();data = null;ms = null;fs = null;}catch (Exceptionex){Logger.Error("把Datatable中的数据保存成指定的Excel文件:" + ex.Message);}}#endregion
         
        
       
      
     

然后是页面调用 

string filepath = ExcelHelper.ExportToExcels(ds, ExcelBankPath, names, proName);//判断返回的路径是否为空if (!string.IsNullOrEmpty(filepath)){System.IO.FileInfo file = new System.IO.FileInfo(filepath);Response.Clear();Response.Charset = "GB2312";Response.ContentEncoding = System.Text.Encoding.UTF8;string fileName = "IPStatistics_" + DateTime.Now.ToString("yyMMdd") + new Random().Next(1000, 9999) + ExcelVersion; //下载文件默认文件名 Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName + ".zip")); //添加头信息，指定文件大小，让浏览器能显示下载进度 Response.AddHeader("Content-Length", file.Length.ToString()); Response.ContentType = "application/rar"; //把文件发送该客户段 Response.WriteFile(file.FullName); }