在网上找资料 在根据需求写的一个execl导出帮助类 需要用到NPOI.dll 与ICSharpCode.SharpZipLib.dll
/// <summary> /// 导出数据到EXCEL 多个表的 /// </summary> /// <param name="ds">数据集</param> /// <param name="AbosultedFilePath">导出的 EXCEL 路径</param> /// <param name="name">EXCEL 工作簿的名字</param> /// <param name="title">表头</param> /// <returns>返回文件路径</returns> public static string ExportToExcels(System.Data.DataSet ds, 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); } } Directory.Delete(path, true); } int PageIndex = 0; if (ds.Tables.Count <= 0) return string.Empty; for (int t = 0; t < ds.Tables.Count; t++) { System.Data.DataTable dt = 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.DataTable execlDT = GetPagedTable(dt, i, pagecount);
//将DataTable内的数据写入execl RenderDataTableToExcel(execlDT, fileName); } } //完成写入后 压缩文件 ZipDir(path, path, 2, title); return path + title + ".zip"; } catch (Exception ex) { Logger.Error("DataTable转execl失败" + ex.Message); return string.Empty; } } #region 压缩文件 /// <summary> /// 压缩文件夹 /// </summary> /// <param name="DirToZip">文件夹路径</param> /// <param name="ZipedFile">输出文件路径</param> /// <param name="CompressionLevel">设置缓存大小</param> ///<param name="fileName">压缩后的文件名称</param> 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 (ZipOutputStream zipoutputstream = new ZipOutputStream(System.IO.File.Create(ZipedFile))) { zipoutputstream.SetLevel(CompressionLevel); Crc32 crc = new Crc32(); System.IO.DirectoryInfo myDir = new DirectoryInfo(DirToZip); List<DictionaryEntry> fileList = GetAllFiles(DirToZip); foreach (DictionaryEntry item in fileList) { //可能存在文件夹无法访问情况 需捕捉异常，根据实际情况返回 try { System.IO.FileStream fs = System.IO.File.OpenRead(item.Key.ToString()); byte[] buffer = new byte[fs.Length]; fs.Read(buffer, 0, buffer.Length); ZipEntry entry = new ZipEntry(item.Key.ToString().Substring(DirToZip.Length + 1)); entry.DateTime = (DateTime)item.Value; entry.Size = fs.Length; fs.Flush(); fs.Close(); crc.Reset(); crc.Update(buffer); entry.Crc = crc.Value; zipoutputstream.PutNextEntry(entry); zipoutputstream.Write(buffer, 0, buffer.Length); } catch (Exception ex) { Logger.Error("压缩文件夹：" + ex.Message); } } } } catch (Exception ex) { Logger.Error("压缩execl文件夹：" + ex.Message); } } /// <summary> /// 获取所有文件 /// </summary> /// <returns></returns> private static List<DictionaryEntry> GetAllFiles(string dir) { try { List<DictionaryEntry> dictonary = new List<DictionaryEntry>(); if (!System.IO.Directory.Exists(dir)) { return dictonary; } else { System.IO.DirectoryInfo root = 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 (Exception ex) { Logger.Error("获取文件夹下的所有文件" + ex.Message); return null; } } #endregion #region DataTable分页 /// <summary> /// DataTable分页 /// </summary> /// <param name="dt">DataTable</param> /// <param name="PageIndex">页索引,注意：从1开始</param> /// <param name="PageSize">每页大小</param> /// <returns>分好页的DataTable数据</returns> 第1页 每页10条 public static System.Data.DataTable GetPagedTable(System.Data.DataTable dt, int PageIndex, int PageSize) { if (PageIndex == 0) { return dt; } System.Data.DataTable newdt = 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++) { DataRow newdr = newdt.NewRow(); DataRow dr = dt.Rows[i]; foreach (DataColumn column in dt.Columns) { newdr[column.ColumnName] = dr[column.ColumnName]; } newdt.Rows.Add(newdr); } return newdt; } /// <summary> /// 返回分页的页数 /// </summary> /// <param name="count">总条数</param> /// <param name="pageye">每页显示多少条</param> /// <returns>如果 结尾为0：则返回1</returns> 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 /// <summary> /// 把Datatable中的数据保存成指定的Excel文件 /// </summary> /// <param name="SourceTable">需要转成execl的DateTable</param> /// <param name="FileName">详细的文件路径带文件名与格式</param> public static void RenderDataTableToExcel(System.Data.DataTable SourceTable, string FileName) { Logger.Info("进入方法RenderDataTableToExcel 文件名：" + FileName); HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream _ms = new MemoryStream(); // 创建Excel文件的Sheet Sheet sheet = workbook.CreateSheet("Sheet1"); sheet.SetColumnWidth(0, 30 * 256); //设置单元格的宽度 sheet.SetColumnWidth(1, 20 * 256);//设置单元格的宽度 sheet.SetColumnWidth(2, 20 * 256);//设置单元格的宽度 // 创建行 Row headerRow = sheet.CreateRow(0); // 把Datatable中的列名添加Sheet中第一列中作为表头 foreach (DataColumn column in SourceTable.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); int rowIndex = 1; // 循环Datatable中的行和列添加数据到Excel中 foreach (DataRow row in SourceTable.Rows) { Row dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in SourceTable.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } try { MemoryStream ms = _ms as MemoryStream; workbook.Write(ms); _ms.Flush(); _ms.Position = 0; FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.ReadWrite); byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); fs.Close(); ms.Flush(); ms.Close(); data = null; ms = null; fs = null; } catch (Exception ex) { Logger.Error("把Datatable中的数据保存成指定的Excel文件:" + ex.Message); } } #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 }View Code
相关文章
