在网上找资料 在根据需求写的一个execl导出帮助类 需要用到NPOI.dll 与ICSharpCode.SharpZipLib.dll
1 /// <summary> 2 /// 导出数据到EXCEL 多个表的 3 /// </summary> 4 /// <param name="ds">数据集</param> 5 /// <param name="AbosultedFilePath">导出的 EXCEL 路径</param> 6 /// <param name="name">EXCEL 工作簿的名字</param> 7 /// <param name="title">表头</param> 8 /// <returns>返回文件路径</returns> 9 public static string ExportToExcels(System.Data.DataSet ds, string AbosultedFilePath, string[] name, string title) 10 { 11 try 12 { 13 14 string path = ConfigHelper.GetValue("execlFile"); 15 //判断路径是否存在 16 if (Directory.Exists(path)) 17 { 18 //删除文件夹及文件 19 foreach (string d in Directory.GetFileSystemEntries(path)) 20 { 21 if (File.Exists(d)) 22 { File.Delete(d); } 23 } 24 Directory.Delete(path, true); 25 } 26 int PageIndex = 0; 27 if (ds.Tables.Count <= 0) 28 return string.Empty; 29 for (int t = 0; t < ds.Tables.Count; t++) 30 { 31 System.Data.DataTable dt = ds.Tables[t]; 32 int count = dt.Rows.Count;//获取datatable内数据量 33 int pagecount = 5000; //每页的数据 34 PageIndex = Pagount(count, pagecount); //获取分页数 35 string filename = t.ToString() == "0" ? "Area_Statistics" : "IP_statistics"; 36 //存在分页时 创建新目录保存新execl文件 37 if (!Directory.Exists(path)) 38 { 39 Directory.CreateDirectory(path); 40 } 41 for (int i = 1; i <= PageIndex; i++) 42 { 43 //将模板文件复制到新目录下 44 string fileName = path + "/" + filename + i + ".xls"; 45 //根据页码获取DataTable内的数据 46 System.Data.DataTable execlDT = GetPagedTable(dt, i, pagecount); 47 //将DataTable内的数据写入execl 48 RenderDataTableToExcel(execlDT, fileName); 49 } 50 } 51 //完成写入后 压缩文件 52 ZipDir(path, path, 2, title); 53 return path + title + ".zip"; 54 } 55 catch (Exception ex) 56 { 57 Logger.Error("DataTable转execl失败" + ex.Message); 58 return string.Empty; 59 } 60 } 61 #region 压缩文件 62 /// <summary> 63 /// 压缩文件夹 64 /// </summary> 65 /// <param name="DirToZip">文件夹路径</param> 66 /// <param name="ZipedFile">输出文件路径</param> 67 /// <param name="CompressionLevel">设置缓存大小</param> 68 ///<param name="fileName">压缩后的文件名称</param> 69 public static void ZipDir(string DirToZip, string ZipedFile, int CompressionLevel, string fileName) 70 { 71 try 72 { 73 //压缩文件为空时默认与压缩文件夹同一级目录 74 if (ZipedFile == string.Empty) 75 { 76 ZipedFile = DirToZip.Substring(DirToZip.LastIndexOf("\") + 1); 77 ZipedFile = DirToZip.Substring(0, DirToZip.LastIndexOf("\")) + "\" + ZipedFile + ".zip"; 78 } 79 if (System.IO.Path.GetExtension(ZipedFile) != ".zip") 80 { 81 ZipedFile = ZipedFile + fileName + ".zip"; 82 } 83 using (ZipOutputStream zipoutputstream = new ZipOutputStream(System.IO.File.Create(ZipedFile))) 84 { 85 zipoutputstream.SetLevel(CompressionLevel); 86 Crc32 crc = new Crc32(); 87 System.IO.DirectoryInfo myDir = new DirectoryInfo(DirToZip); 88 List<DictionaryEntry> fileList = GetAllFiles(DirToZip); 89 foreach (DictionaryEntry item in fileList) 90 { 91 //可能存在文件夹无法访问情况 需捕捉异常，根据实际情况返回 92 try 93 { 94 System.IO.FileStream fs = System.IO.File.OpenRead(item.Key.ToString()); 95 byte[] buffer = new byte[fs.Length]; 96 fs.Read(buffer, 0, buffer.Length); 97 ZipEntry entry = new ZipEntry(item.Key.ToString().Substring(DirToZip.Length + 1)); 98 entry.DateTime = (DateTime)item.Value; 99 entry.Size = fs.Length; 100 fs.Flush(); 101 fs.Close(); 102 crc.Reset(); 103 crc.Update(buffer); 104 entry.Crc = crc.Value; 105 zipoutputstream.PutNextEntry(entry); 106 zipoutputstream.Write(buffer, 0, buffer.Length); 107 } 108 catch (Exception ex) 109 { 110 Logger.Error("压缩文件夹：" + ex.Message); 111 } 112 } 113 } 114 } 115 catch (Exception ex) 116 { 117 Logger.Error("压缩execl文件夹：" + ex.Message); 118 } 119 } 120 121 /// <summary> 122 /// 获取所有文件 123 /// </summary> 124 /// <returns></returns> 125 private static List<DictionaryEntry> GetAllFiles(string dir) 126 { 127 try 128 { 129 List<DictionaryEntry> dictonary = new List<DictionaryEntry>(); 130 if (!System.IO.Directory.Exists(dir)) 131 { 132 return dictonary; 133 } 134 else 135 { 136 System.IO.DirectoryInfo root = new System.IO.DirectoryInfo(dir); 137 System.IO.FileSystemInfo[] arrary = root.GetFileSystemInfos(); 138 for (int i = 0; i < arrary.Length; i++) 139 { 140 dictonary.Add(new DictionaryEntry(arrary[i].FullName, arrary[i].LastWriteTime)); 141 } 142 } 143 return dictonary; 144 } 145 catch (Exception ex) 146 { 147 Logger.Error("获取文件夹下的所有文件" + ex.Message); 148 return null; 149 } 150 } 151 #endregion 152 153 #region DataTable分页 154 /// <summary> 155 /// DataTable分页 156 /// </summary> 157 /// <param name="dt">DataTable</param> 158 /// <param name="PageIndex">页索引,注意：从1开始</param> 159 /// <param name="PageSize">每页大小</param> 160 /// <returns>分好页的DataTable数据</returns> 第1页 每页10条 161 public static System.Data.DataTable GetPagedTable(System.Data.DataTable dt, int PageIndex, int PageSize) 162 { 163 if (PageIndex == 0) { return dt; } 164 System.Data.DataTable newdt = dt.Copy(); 165 newdt.Clear(); 166 int rowbegin = (PageIndex - 1) * PageSize; 167 int rowend = PageIndex * PageSize; 168 169 if (rowbegin >= dt.Rows.Count) 170 { return newdt; } 171 172 if (rowend > dt.Rows.Count) 173 { rowend = dt.Rows.Count; } 174 for (int i = rowbegin; i <= rowend - 1; i++) 175 { 176 DataRow newdr = newdt.NewRow(); 177 DataRow dr = dt.Rows[i]; 178 foreach (DataColumn column in dt.Columns) 179 { 180 newdr[column.ColumnName] = dr[column.ColumnName]; 181 } 182 newdt.Rows.Add(newdr); 183 } 184 return newdt; 185 } 186 187 /// <summary> 188 /// 返回分页的页数 189 /// </summary> 190 /// <param name="count">总条数</param> 191 /// <param name="pageye">每页显示多少条</param> 192 /// <returns>如果 结尾为0：则返回1</returns> 193 public static int Pagount(int count, int pageye) 194 { 195 int page = 0; 196 int sesepage = pageye; 197 if (count % sesepage == 0) { page = count / sesepage; } 198 else { page = (count / sesepage) + 1; } 199 if (page == 0) { page += 1; } 200 return page; 201 } 202 #endregion 203 204 #region Datatable转Execl 205 /// <summary> 206 /// 把Datatable中的数据保存成指定的Excel文件 207 /// </summary> 208 /// <param name="SourceTable">需要转成execl的DateTable</param> 209 /// <param name="FileName">详细的文件路径带文件名与格式</param> 210 public static void RenderDataTableToExcel(System.Data.DataTable SourceTable, string FileName) 211 { 212 Logger.Info("进入方法RenderDataTableToExcel 文件名：" + FileName); 213 HSSFWorkbook workbook = new HSSFWorkbook(); 214 MemoryStream _ms = new MemoryStream(); 215 // 创建Excel文件的Sheet 216 Sheet sheet = workbook.CreateSheet("Sheet1"); 217 sheet.SetColumnWidth(0, 30 * 256); //设置单元格的宽度 218 sheet.SetColumnWidth(1, 20 * 256);//设置单元格的宽度 219 sheet.SetColumnWidth(2, 20 * 256);//设置单元格的宽度 220 // 创建行 221 Row headerRow = sheet.CreateRow(0); 222 // 把Datatable中的列名添加Sheet中第一列中作为表头 223 foreach (DataColumn column in SourceTable.Columns) 224 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); 225 int rowIndex = 1; 226 // 循环Datatable中的行和列添加数据到Excel中 227 foreach (DataRow row in SourceTable.Rows) 228 { 229 Row dataRow = sheet.CreateRow(rowIndex); 230 foreach (DataColumn column in SourceTable.Columns) 231 { 232 dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); 233 } 234 rowIndex++; 235 } 236 try 237 { 238 MemoryStream ms = _ms as MemoryStream; 239 workbook.Write(ms); 240 _ms.Flush(); 241 _ms.Position = 0; 242 FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.ReadWrite); 243 byte[] data = ms.ToArray(); 244 fs.Write(data, 0, data.Length); 245 fs.Flush(); 246 fs.Close(); 247 ms.Flush(); 248 ms.Close(); 249 data = null; 250 ms = null; 251 fs = null; 252 } 253 catch (Exception ex) 254 { 255 Logger.Error("把Datatable中的数据保存成指定的Excel文件:" + ex.Message); 256 } 257 } 258 #endregion
然后是页面调用
1 string filepath = ExcelHelper.ExportToExcels(ds, ExcelBankPath, names, proName); 2 //判断返回的路径是否为空 3 if (!string.IsNullOrEmpty(filepath)) 4 { 5 System.IO.FileInfo file = new System.IO.FileInfo(filepath); 6 Response.Clear(); 7 Response.Charset = "GB2312"; 8 Response.ContentEncoding = System.Text.Encoding.UTF8; 9 string fileName = "IPStatistics_" + DateTime.Now.ToString("yyMMdd") + new Random().Next(1000, 9999) + ExcelVersion; 10 //下载文件默认文件名 11 Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName + ".zip")); 12 //添加头信息，指定文件大小，让浏览器能显示下载进度 13 Response.AddHeader("Content-Length", file.Length.ToString()); 14 Response.ContentType = "application/rar"; 15 //把文件发送该客户段 16 Response.WriteFile(file.FullName); 17 }