DataTable 导出 Excel 带图片_.NET_编程开发_程序员俱乐部

class="code_img_closed" src="/Upload/Images/2013082618/0015B68B3C38AA5B.gif" alt="" />logs_code_hide('21e4601b-104c-4d5a-91b9-1322401a3a09',event)" src="/Upload/Images/2013082618/2B1B950FA3DF188F.gif" alt="" />

  1    #region Excel导出带图片
  2 
  3 
  4         /// <summary>
  5         ///  导出Excel可带自定义显示列名
  6         /// </summary>
  7         /// <param name="tmpDataTable">需要导出的Table</param>
  8         /// <param name="strFileName">Excel保存的路径带文件名</param>
  9         /// <param name="tabDispayColumnName">Excel列名</param>
 10         /// <param name="imgColumnName">图片列名[没图片为""即可]</param>
 11         /// <param name="imgWidth">图片宽</param>
 12         /// <param name="imgHeight">图片高</param>
 13         /// <param name="isByte">是否是二进字符串</param>
 14         public void ImportExcel(System.Data.DataTable tmpDataTable, string strFileName, string[] tabDispayColumnName, string imgColumnName, int imgWidth, int imgHeight, bool isByte)
 15         {
 16             if (tabDispayColumnName.Length != tmpDataTable.Columns.Count)
 17             {
 18                 return;
 19             }
 20 
 21             for (int i = 0; i < tabDispayColumnName.Length; i++)
 22             {
 23                 tmpDataTable.Columns[i].Caption = tabDispayColumnName[i];
 24             }
 25             try
 26             {
 27                 ImportExcel(tmpDataTable, strFileName, imgColumnName, imgWidth, imgHeight, isByte);
 28             }
 29             catch (Exception e)
 30             {
 31                 throw e;
 32             }
 33         }
 34 
 35         /// <summary>
 36         ///  导出Excel可带图片
 37         /// </summary>
 38         /// <param name="tmpDataTable">需要导出的Table</param>
 39         /// <param name="strFileName">Excel保存的路径带文件名</param>
 40         /// <param name="imgColumnName">图片列名[没图片为""即可]</param>
 41         /// <param name="imgWidth">图片宽</param>
 42         /// <param name="imgHeight">图片高</param>
 43         /// <param name="isByte">是否是二进字符串</param>
 44         public void ImportExcel(System.Data.DataTable tmpDataTable, string strFileName, string imgColumnName, int imgWidth, int imgHeight, bool isByte)
 45         {
 46             if (tmpDataTable == null)
 47             {
 48                 return;
 49             }
 50             Microsoft.Office.Interop.Excel.Application m_xlApp = null;
 51             Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
 52             Microsoft.Office.Interop.Excel.Workbook workbook = null;
 53             Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
 54             Microsoft.Office.Interop.Excel.Range m_range = null;
 55             try
 56             {
 57                 long rowNum = tmpDataTable.Rows.Count;//行数  
 58                 int columnNum = tmpDataTable.Columns.Count;//列数  
 59                 m_xlApp = new Microsoft.Office.Interop.Excel.Application();
 60                 m_xlApp.DisplayAlerts = false;//不显示更改提示  
 61                 m_xlApp.Visible = false;
 62                 if (m_xlApp.Version == "11.0")
 63                 {
 64                     strFileName = strFileName.Substring(0, strFileName.Length - 1); //如果是2003版.则把后缀名修改一下.xlsx 修改为 xls
 65                 }
 66 
 67                 workbooks = m_xlApp.Workbooks;
 68                 workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
 69                 worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 
 70 
 71 
 72                 for (int i = 0; i < columnNum; i++) //写入字段  
 73                 {
 74                     Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)worksheet.Rows[1];
 75                     range.Columns[i + 1] = tmpDataTable.Columns[i].Caption;
 76                 }
 77                 int r = 0;
 78                 for (r = 0; r < rowNum; r++)
 79                 {
 80                     for (int i = 0; i < columnNum; i++)
 81                     {
 82                         //行的共同属性
 83                         m_range = (Microsoft.Office.Interop.Excel.Range)worksheet.Rows[r + 2];
 84                         m_range.RowHeight = imgHeight + 14; //设置行高  +14 避免图片紧贴着单元格边线
 85                         if (tmpDataTable.Columns[i].ColumnName == imgColumnName)
 86                         {
 87                             #region 图片列的共同设置
 88                             string strTemp = "";
 89                             for (int j = 0; j < (imgWidth / 7) + 2; j++)
 90                             {
 91                                 strTemp += " ";
 92                             }
 93                             m_range.Columns[i + 1] = strTemp; //添加空格,用于自动适应格式.免得图片位置不正确.
 94                             Microsoft.Office.Interop.Excel.Range mCol = (Microsoft.Office.Interop.Excel.Range)m_range.Columns[i + 1];
 95                             float topCount = Convert.ToSingle((int)mCol.Top + 7);
 96                             float leftCount = Convert.ToSingle((int)mCol.Left + 7);
 97                             #endregion
 98                             if (isByte)
 99                             {
100                                 #region 保存图片并添加到excel---图片二进制字符串[请使用Convert.FromBase64String转换]
101                                 Byte[] imgbyte = Convert.FromBase64String(tmpDataTable.Rows[r][i].ToString());
102                                 MemoryStream my = new MemoryStream(imgbyte);
103                                 Image img = Image.FromStream(my);
104                                 string fileNameTemp = ""; //图片暂时路径.
105                                 try
106                                 {
107                                     string parentPath = System.Windows.Forms.Application.StartupPath;
108                                     string hzm = "jpg"; //默认jpg
109                                     #region 获取后缀名
110                                     //获取后缀名
111                                     if (img.RawFormat.Guid == ImageFormat.Gif.Guid)
112                                     {
113                                         hzm = ImageFormat.Gif.ToString();
114                                     }
115                                     if (img.RawFormat.Guid == ImageFormat.Png.Guid)
116                                     {
117                                         hzm = ImageFormat.Png.ToString();
118                                     }
119                                     if (img.RawFormat.Guid == ImageFormat.Bmp.Guid)
120                                     {
121                                         hzm = ImageFormat.Bmp.ToString();
122                                     }
123                                     #endregion
124                                     fileNameTemp = parentPath + "\\" + Guid.NewGuid().ToString() + "." + hzm;
125                                     img.Save(fileNameTemp);
126                                     worksheet.Shapes.AddPicture(fileNameTemp, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, Convert.ToSingle(leftCount), Convert.ToSingle(topCount), imgWidth, imgHeight);
127                                 }
128                                 catch (Exception e)
129                                 {
130                                     //throw e;
131                                     // 单个图片未添加成功.不作处理
132 
133                                 }
134                                 finally
135                                 {
136                                     File.Delete(fileNameTemp); //有没有成功都删除临时图片.
137                                 }
138                                 #endregion
139                             }
140                             else
141                             {
142                                 #region 本地图片和网络图片
143                                 string tmpPath = tmpDataTable.Rows[r][i].ToString();
144                                 bool IsHttp = false;
145                                 try
146                                 {
147 
148                                     if (tmpDataTable.Rows[r][i].ToString().IndexOf("http://") >= 0)
149                                     {
150                                         string url = tmpDataTable.Rows[r][i].ToString();
151                                         string parentPath = System.Windows.Forms.Application.StartupPath;
152                                         string hzm = url.Substring(url.LastIndexOf('.') + 1, 3);
153 
154                                         WebClient web = new WebClient();
155                                         tmpPath = parentPath + "\\" + Guid.NewGuid().ToString() + "." + hzm;
156 
157                                         #region 如果下载失败.循环3次下载图片
158                                         bool isp = true;
159                                         int a = 0;
160                                         while (isp && a < 3)
161                                         {
162                                             try
163                                             {
164                                                 web.DownloadFile(url, tmpPath);
165                                                 IsHttp = true;
166                                                 isp = false;
167                                             }
168                                             catch (Exception e)
169                                             {
170                                                 isp = true;
171                                                 a++;
172                                                 if (a > 3)
173                                                 {
174                                                     throw e;
175                                                 }
176                                             }
177                                             System.Threading.Thread.Sleep(1000);
178                                         }
179 
180                                         #endregion
181                                     }
182                                     worksheet.Shapes.AddPicture(tmpPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, Convert.ToSingle(leftCount), Convert.ToSingle(topCount), imgWidth, imgHeight);
183                                 }
184                                 catch (Exception e)
185                                 {
186                                     // 单个图片未添加成功.不作处理
187                                 }
188                                 finally
189                                 {
190                                     if (IsHttp)
191                                     {
192                                         File.Delete(tmpPath);
193                                     }
194                                 }
195                                 #endregion
196                             }
197                         }
198                         else
199                         {
200                             object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
201                             m_range.RowHeight = imgHeight + 14; // +14 避免图片紧贴着单元格边线
202                             m_range.Columns[i + 1] = obj == null ? "" : "'" + obj.ToString().Trim();
203                         }
204                     }
205                 }
206                 worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。  
207                 m_xlApp.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
208                 workbook.Saved = true;
209                 workbook.SaveCopyAs(strFileName);
210             }
211             catch (Exception e)
212             {
213                 throw e;
214             }
215             finally
216             {
217                 #region 保存完成,释放资源.
218                 ReleaseObj(worksheet);
219                 ReleaseObj(workbook);
220                 ReleaseObj(workbooks);
221                 ReleaseObj(m_xlApp);
222                 System.GC.Collect();
223                 System.GC.WaitForPendingFinalizers();
224                 #endregion
225             }
226         }
227         /// <summary>
228         /// 释放对象，内部调用
229         /// </summary>
230         /// <param name="o"></param>
231         private void ReleaseObj(object o)
232         {
233             try
234             {
235                 System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
236             }
237             catch { }
238             finally { o = null; }
239         }
240         #endregion

View Code