DataTable 导出Excel 带图片 性能改进
去掉下载循环.
经过测试 WebClient 性能已经达到了.
/// <summary> /// 导出Excel可带图片 /// </summary> /// <param name="tmpDataTable">需要导出的Table</param> /// <param name="strFileName">Excel保存的路径带文件名</param> /// <param name="imgColumnName">图片列名[没图片为""即可]</param> /// <param name="imgWidth">图片宽</param> /// <param name="imgHeight">图片高</param> /// <param name="isByte">是否是二进字符串</param> public void ImportExcel(System.Data.DataTable tmpDataTable, string strFileName, string imgColumnName, int imgWidth, int imgHeight, bool isByte) { if (tmpDataTable == null) { return; } Microsoft.Office.Interop.Excel.Application m_xlApp = null; Microsoft.Office.Interop.Excel.Workbooks workbooks = null; Microsoft.Office.Interop.Excel.Workbook workbook = null; Microsoft.Office.Interop.Excel.Worksheet worksheet = null; Microsoft.Office.Interop.Excel.Range m_range = null; string sPath = System.Windows.Forms.Application.StartupPath; DirectoryInfo dicFile = System.IO.Directory.CreateDirectory(sPath + "\\temp"); try { long rowNum = tmpDataTable.Rows.Count;//行数 int columnNum = tmpDataTable.Columns.Count;//列数 m_xlApp = new Microsoft.Office.Interop.Excel.Application(); m_xlApp.DisplayAlerts = false;//不显示更改提示 m_xlApp.Visible = false; if (m_xlApp.Version == "11.0") { strFileName = strFileName.Substring(0, strFileName.Length - 1); //如果是2003版.则把后缀名修改一下.xlsx 修改为 xls } workbooks = m_xlApp.Workbooks; workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 for (int i = 0; i < columnNum; i++) //写入字段 { Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)worksheet.Rows[1]; range.Columns[i + 1] = tmpDataTable.Columns[i].Caption; } int r = 0; for (r = 0; r < rowNum; r++) { for (int i = 0; i < columnNum; i++) { //行的共同属性 m_range = (Microsoft.Office.Interop.Excel.Range)worksheet.Rows[r + 2]; m_range.RowHeight = imgHeight + 14; //设置行高 +14 避免图片紧贴着单元格边线 if (tmpDataTable.Columns[i].ColumnName == imgColumnName) { #region 图片列的共同设置 string strTemp = ""; for (int j = 0; j < (imgWidth / 7) + 2; j++) { strTemp += " "; } m_range.Columns[i + 1] = strTemp; //添加空格,用于自动适应格式.免得图片位置不正确. Microsoft.Office.Interop.Excel.Range mCol = (Microsoft.Office.Interop.Excel.Range)m_range.Columns[i + 1]; float topCount = Convert.ToSingle((int)mCol.Top + 7); float leftCount = Convert.ToSingle((int)mCol.Left + 7); #endregion if (isByte) { #region 保存图片并添加到excel---图片二进制字符串[请使用Convert.FromBase64String转换] Byte[] imgbyte = Convert.FromBase64String(tmpDataTable.Rows[r][i].ToString()); MemoryStream my = new MemoryStream(imgbyte); Image img = Image.FromStream(my); try { string hzm = "jpg"; //默认jpg #region 获取后缀名 //获取后缀名 if (img.RawFormat.Guid == ImageFormat.Gif.Guid) { hzm = ImageFormat.Gif.ToString(); } if (img.RawFormat.Guid == ImageFormat.Png.Guid) { hzm = ImageFormat.Png.ToString(); } if (img.RawFormat.Guid == ImageFormat.Bmp.Guid) { hzm = ImageFormat.Bmp.ToString(); } #endregion string fileNameTemp = dicFile.FullName + "\\" + Guid.NewGuid().ToString() + "." + hzm;//图片暂时路径. img.Save(fileNameTemp); worksheet.Shapes.AddPicture(fileNameTemp, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, Convert.ToSingle(leftCount), Convert.ToSingle(topCount), imgWidth, imgHeight); } catch (Exception) { //throw e; // 单个图片未添加成功.不作处理 } #endregion } else { #region 本地图片和网络图片 string tmpPath = tmpDataTable.Rows[r][i].ToString(); if (tmpPath.Length > 1) //判断图片地址有没有 { #region 添加本地图片和网络图片到EXCEL单元格 try { bool isPath = true; //图片路径是否存在 if (tmpDataTable.Rows[r][i].ToString().IndexOf("http://") >= 0) { string url = tmpDataTable.Rows[r][i].ToString(); string hzm = url.Substring(url.LastIndexOf('.') + 1, 3); WebClient web = new WebClient(); tmpPath = dicFile.FullName + "\\" + Guid.NewGuid().ToString() + "." + hzm; try { web.DownloadFile(url, tmpPath); } catch (Exception e) { isPath = false; //没下载成功.图片路径不存在 } } else { isPath = System.IO.File.Exists(tmpPath); //检查一下本地文件是否存在. } if (isPath) worksheet.Shapes.AddPicture(tmpPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, Convert.ToSingle(leftCount), Convert.ToSingle(topCount), imgWidth, imgHeight); else m_range.Columns[i + 1] = ""; } catch (Exception) { // 单个图片未添加成功.不作处理 } #endregion } else { m_range.Columns[i + 1] = ""; } #endregion } } else { object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()]; m_range.RowHeight = imgHeight + 14; // +14 避免图片紧贴着单元格边线 m_range.Columns[i + 1] = obj == null ? "" : obj.ToString().Trim(); } } } worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。 m_xlApp.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized; workbook.Saved = true; workbook.SaveCopyAs(strFileName); } catch (Exception e) { throw e; } finally { #region 保存完成,释放资源. dicFile.Delete(true); //删除暂时目录 ReleaseObj(worksheet); ReleaseObj(workbook); ReleaseObj(workbooks); ReleaseObj(m_xlApp); System.GC.Collect(); System.GC.WaitForPendingFinalizers(); #endregion } }
/// <summary> /// 释放对象，内部调用 /// </summary> /// <param name="o"></param> private void ReleaseObj(object o) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(o); } catch { } finally { o = null; } }
