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