C# 导出excel的压缩包到浏览器页面
需求背景：TCX_1710项目产品质量导出功能，客户希望每个总成导出到一个Excel表中
实现分析：客户选择时间段，点击导出按钮，默认导出开始时间当天的数据，每个总成一个Excel，将各个Excel打包压缩，返回压缩包到浏览器页面供客户下载
控制器类：
/// <summary> /// 导出产品质量信息（每个总成一个Excel） /// </summary> /// <param name="keyword"></param> /// <returns></returns> public void exportExcel(string keyword) { string SNum = Request.QueryString["SNum"]; string PSCode = Request.QueryString["PSCode"]; string StartTime = Request.QueryString["StartTime"]; string EndTime = Request.QueryString["EndTime"]; //dictionary根据以上查询条件查询的数据字典 Dictionary<string, Dictionary<string, List<PR_Product>>> dictionary = new Dictionary<string, Dictionary<string, List<PR_Product>>>(); if (keyword == "TimeAndSnum") { dictionary = this.CreateService<IProductInfoAppServices>().GetDictionary("TimeAndSnum", SNum, PSCode, StartTime, EndTime); } else { dictionary = this.CreateService<IProductInfoAppServices>().GetDictionary("NoTimeNoSnum", SNum, PSCode, StartTime, EndTime); } //初始化需要导出字段 List<ExportFieldInfo> fieldInfo10 = new List<ExportFieldInfo>(); var tableInfo10 = this.CreateService<IProductInfoAppServices>().getFiledByTable(); for (var i = 0; i < tableInfo10.Count; i++) { fieldInfo10.Add(new ExportFieldInfo() { FieldName = tableInfo10[i].filedName, DisplayName = tableInfo10[i].filedExplain, DataType = DataTypeEnum.String }); } string filePath = ConfigurationManager.AppSettings["filePath"];//配置写在E:\MyProject\1710\02_代码\DoMes.Web\Configs\system.config //string filePath = "F:\\exporfolder"; FileStream stream = ExcelHelper<PR_Product>.ToExcel(filePath, dictionary, fieldInfo10, Response); Response.Flush(); Response.End();//关闭响应 stream.Close();//关闭zip流，否则无法删除zip文件 //获取指定路径下所有文件夹 //string[] folderPaths = Directory.GetDirectories(filePath); //获取指定路径下所有文件 var filePaths = Directory.GetFiles(filePath); foreach (string filePath_2 in filePaths) { //删除所有文件 System.IO.File.Delete(filePath_2); } //foreach (string folderPath in folderPaths) //{ // Directory.Delete(folderPath, true); //} //删除最外面的文件夹 Directory.Delete(filePath, true); }
获取数据的方法：
/// <summary> /// 根据查询条件获取数据，并将数据转换为数据字典 /// </summary> /// <param name="type">查询类型(暂时不用)</param> /// <param name="sNum">产品总成</param> /// <param name="pSCode">工位</param> /// <param name="startTime">开始时间</param> /// <param name="endTime">结束时间</param> /// <returns></returns> public Dictionary<string, Dictionary<string, List<PR_Product>>> GetDictionary(string type, string sNum, string pSCode, string startTime, string endTime) { //第一层key为日期(年/月/日)，value为该日所有总成的数据字典集合 //第二层key为总成，value为该总成所有的质量数据 Dictionary<string, Dictionary<string, List<PR_Product>>> dictionary = new Dictionary<string, Dictionary<string, List<PR_Product>>>(); Expression<Func<PR_Product, bool>> exp = (a => 1 == 1); if (!string.IsNullOrEmpty(pSCode)) { exp = exp.And(a => a.PSCode == pSCode); } if (!string.IsNullOrEmpty(startTime)) { DateTime ctStart = Convert.ToDateTime(startTime); exp = exp.And(a => a.CreationTime >= ctStart); } if (!string.IsNullOrEmpty(endTime)) { DateTime ctEnd = Convert.ToDateTime(endTime); exp = exp.And(a => a.CreationTime < ctEnd.AddDays(1)); } if (!string.IsNullOrEmpty(sNum)) { exp = exp.And(a => a.SNum.Contains(sNum)); } //根据查询条件获得的产品质量数据 List<PR_Product> product = this.DbContext.Query<PR_Product>().Where(exp).OrderBy(a => a.SNum).ThenBy(it => it.PSCode).ToList(); //产品总成不为空则只有一个总成 if (!string.IsNullOrEmpty(sNum)) { Dictionary<string, List<PR_Product>> dictionary2 = new Dictionary<string, List<PR_Product>>(); dictionary2.Add(sNum, product); dictionary.Add(startTime, dictionary2); } else//产品总成为空 { Dictionary<string, List<PR_Product>> dictionary2 = new Dictionary<string, List<PR_Product>>(); //从查询的数据集合中查询出所有的总成 List<PR_Product> snumDistinct = product.Where((x, firstId) => product.FindIndex(z => z.SNum == x.SNum) == firstId).ToList(); foreach(PR_Product item in snumDistinct) { //从大数据集合中查找该总成的数据集合 List<PR_Product> snumList = product.Where(x => x.SNum == item.SNum).ToList(); dictionary2.Add(item.SNum, snumList); } dictionary.Add(startTime, dictionary2); } return dictionary; }
Excel文件帮助类方法：
/// <summary> /// 导出产品质量数据，每个总成一个Excel /// </summary> /// <param name="filePath">导出文件路径</param> /// <param name="dictionary">导出数据字典</param> /// <param name="fieldInfies">导出数据表头</param> /// <param name="Response">页面响应</param> /// <returns></returns> public static FileStream ToExcel(String filePath, Dictionary<string, Dictionary<string, List<T>>> dictionary, List<ExportFieldInfo> fieldInfies, HttpResponseBase Response) { //导出文件路径（这里也可以写成固定路径"F:\\exporfolder"） //String filePath = "F:\\exporfolder"; //创建此路径（配置文件中的地址一定要保证磁盘存在） Directory.CreateDirectory(filePath); //导出压缩文件的全路径(zipFilePath) DateTime dateTimeZip = DateTime.Now; string zipFilePath = filePath + Path.DirectorySeparatorChar + "QM_" + dateTimeZip.ToString("yyyyMMdd") + "_" + dateTimeZip.ToString("HHmmss") + "_" + dateTimeZip.ToString("fff") + ".zip"; //导出Excel文件路径 string fullFilePath = ""; //保存Excel文件名 string fileName = ""; //用于存放生成的Excel文件名称集合 List<string> fileNames = new List<string>(); //excel文件流 FileStream excel = null; foreach (Dictionary<string, List<T>> items in dictionary.Values) { foreach (var item in items) { DateTime dateTimeExcel = DateTime.Now; //Excel文件名 fileName = item.Key + "_" + dateTimeExcel.ToString("yyyyMMdd") + "_" + dateTimeExcel.ToString("HHmmss") + "_" + dateTimeExcel.ToString("fff") + ".xlsx"; //Excel文件路径 fullFilePath = filePath + Path.DirectorySeparatorChar + fileName; //存放到Excel文件名称集合 fileNames.Add(fullFilePath); excel = File.Create(fullFilePath); HSSFWorkbook book = createColumnHSSF(item.Key, item.Value, fieldInfies); // 写文件 book.Write(excel); excel.Close(); } } FileStream stream = ZipFiles(fileNames, zipFilePath, Response); return stream; }
/// <summary> /// 压缩多个文件 /// </summary> /// <param name="filesToZip">要进行压缩的文件名集合</param> /// <param name="zipedFile">压缩后生成的压缩文件名</param> public static FileStream ZipFiles(List<string> filesToZip, string zipedFile, HttpResponseBase Response) { Response.AddHeader("content-disposition", "attachment;filename=" + zipedFile.Substring(zipedFile.LastIndexOf("\\", StringComparison.Ordinal) + 1) ); //Zip文件流 FileStream zipFile = File.Create(zipedFile); //将zipStream写到响应输出流中 ZipOutputStream zipStream = new ZipOutputStream(Response.OutputStream); //遍历所有的Excel文件 foreach (string fileToZip in filesToZip) { if (string.IsNullOrEmpty(fileToZip)) { throw new ArgumentException(fileToZip); } if (string.IsNullOrEmpty(zipedFile)) { throw new ArgumentException(zipedFile); } if (!File.Exists(fileToZip)) { throw new FileNotFoundException("指定要压缩的文件: " + fileToZip + " 不存在!"); } try { //读取Excel文件到文件流中 using (var fs = File.OpenRead(fileToZip)) { var buffer = new byte[fs.Length]; fs.Read(buffer, 0, buffer.Length); fs.Close(); //从Excel文件路径中读取Excel文件名 var fileName = fileToZip.Substring(fileToZip.LastIndexOf("\\", StringComparison.Ordinal) + 1); //根据文件名创建ZipEntry var zipEntry = new ZipEntry(fileName); //将ZipEntry放入zipStream流中 zipStream.PutNextEntry(zipEntry); zipStream.SetLevel(5); zipStream.Write(buffer, 0, buffer.Length); } } catch (IOException ioex) { throw new IOException(ioex.Message); } catch (Exception ex) { throw new Exception(ex.Message); } } //zipStream完成后返回 zipStream.Finish(); return zipFile; }
