C#操作Excel

Excel操作类使用方法

 1         protected void Button1_Click(object sender, EventArgs e)
 2         {
 3             OperateExcel excel = new OperateExcel();
 4             //打开Excel
 5             excel.Open("d:\abc.xlsx");
 6             //获取工作表
 7             var  weet = excel.GetSheet("Sheet2");
 8             //写入Excel
 9             excel.SetCellValue(weet, 1, 2, "1011");
10             ////另存为Excel
11             //excel.SaveAs("d:\bcd.xlsx");
12             ////注销Excel进程
13             //excel.Close();
14             excel.SaveAsHtml(HttpContext.Current.Server.MapPath("aa.html"));
15 
16             //杀掉Excel进程
17             excel.KillSpecialExcel();
18         }

Excel操作类

需引用：

  1 using System;
  2 using System.Data;
  3 using System.Configuration;
  4 using System.Web;
  5 using System.Web.Security;
  6 using System.Web.UI;
  7 using System.Web.UI.WebControls;
  8 using System.Web.UI.WebControls.WebParts;
  9 using System.Web.UI.HtmlControls;
 10 using Microsoft.Office.Core;
 11 using System.Runtime.InteropServices;
 12 using System.IO;
 13 
 14 ///   <summary >       
 15 ///   Excel操作类       
 16 ///   </summary >       
 17 public class OperateExcel
 18 {
 19     public string mFilename;
 20     public Microsoft.Office.Interop.Excel.Application app;
 21     public Microsoft.Office.Interop.Excel.Workbooks wbs;
 22     public Microsoft.Office.Interop.Excel.Workbook wb;
 23     public Microsoft.Office.Interop.Excel.Worksheets wss;
 24     public Microsoft.Office.Interop.Excel.Worksheet ws;
 25     public OperateExcel()
 26     {
 27         //       
 28         //   TODO:   在此处添加构造函数逻辑       
 29         //       
 30     }
 31 
 32     /// <summary>
 33     /// 创建一个Excel对象
 34     /// </summary>
 35     public void Create()
 36     {
 37         app = new Microsoft.Office.Interop.Excel.Application();
 38         wbs = app.Workbooks;
 39         wb = wbs.Add(true);
 40     }
 41 
 42     /// <summary>
 43     /// 打开一个Excel文件
 44     /// </summary>
 45     /// <param name="FileName">Excel文件路径及名称</param>
 46     public void Open(string FileName)
 47     {
 48         object missing = System.Reflection.Missing.Value;
 49         app = new Microsoft.Office.Interop.Excel.Application();
 50         app.Visible = true;
 51         wbs = app.Workbooks;
 52         wb = wbs.Open(FileName, missing, false, missing, missing, missing,missing, missing, missing, true, missing, missing, missing, missing, missing);
 53         //wb = wbs.Add(FileName);
 54         mFilename = FileName;
 55     }
 56 
 57     /// <summary>
 58     /// 复制并打开模板文件
 59     /// </summary>
 60     /// <param name="Path">模板文件位置及文件名</param>
 61     /// <param name="sufix">复制后文件所加后缀</param>
 62     /// <param name="IsVisible">是否可见</param>
 63     /// <returns>复制后文件位置</returns>
 64     public string CopyAndOpenTemplate(string excelName)
 65     {
 66         try
 67         {
 68             string templetFilePath = HttpContext.Current.Server.MapPath("~\XlsTemplate\SampleZhongshuExcelTemplate.xlsx");
 69             string currentFolder = HttpContext.Current.Server.MapPath("~\UploadFile\")+DateTime.Now.ToString("yyyy-MM-dd");
 70             string toPath = currentFolder + "\" + excelName;
 71             //string tempFolderName = DateTime.Now.ToString("yyyy-MM-dd").Replace("-", "").Replace("/", "").Replace("\", "");
 72             //string targetFolder = HttpContext.Current.Server.MapPath("~\UploadFile\" + tempFolderName);
 73             //如果不存在则创建
 74             if (!Directory.Exists(currentFolder))
 75             {
 76                 Directory.CreateDirectory(currentFolder);
 77             }
 78 
 79             File.Copy(templetFilePath, toPath, true);
 80 
 81             //File.SetAttributes(toPath, FileAttributes.Normal);
 82             Open(toPath);
 83             return toPath;
 84         }
 85         catch (Exception e)
 86         {
 87             //
 88             KillSpecialExcel();
 89             throw e;
 90 
 91         }
 92     }
 93 
 94     /// <summary>
 95     /// 获取一个工作表
 96     /// </summary>
 97     /// <param name="SheetName">工作表名称</param>
 98     /// <returns>Excel工作表</returns>
 99     public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string SheetName)
100     {
101         Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName];
102         return s;
103     }
104 
105     /// <summary>
106     /// 添加一个工作表
107     /// </summary>
108     /// <param name="SheetName">工作表名称</param>
109     /// <returns>Excel工作表</returns>
110     public Microsoft.Office.Interop.Excel.Worksheet AddSheet(string SheetName)
111     {
112         Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
113         s.Name = SheetName;
114         return s;
115     }
116 
117     /// <summary>
118     /// 删除一个工作表
119     /// </summary>
120     /// <param name="SheetName">工作表名称</param>
121     public void DelSheet(string SheetName)
122     {
123         ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName]).Delete();
124     }
125 
126     /// <summary>
127     /// 重命名一个工作表
128     /// </summary>
129     /// <param name="OldSheetName">要改名的工作表</param>
130     /// <param name="NewSheetName">工作表新名称</param>
131     /// <returns>工作表</returns>
132     public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)
133     {
134         Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[OldSheetName];
135         s.Name = NewSheetName;
136         return s;
137     }
138 
139     /// <summary>
140     /// 重命名一个工作表
141     /// </summary>
142     /// <param name="Sheet">Excel工作表实例</param>
143     /// <param name="NewSheetName">新命名的工作表</param>
144     /// <returns>Excel工作表</returns>
145     public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(Microsoft.Office.Interop.Excel.Worksheet Sheet, string NewSheetName)
146     {
147         Sheet.Name = NewSheetName;
148         return Sheet;
149     }
150 
151     /// <summary>
152     /// 设置工作表的值1
153     /// </summary>
154     /// <param name="ws">要设值的工作表</param>
155     /// <param name="x">行</param>
156     /// <param name="y">列</param>
157     /// <param name="value">要设置的值</param>
158     public void SetCellValue(Microsoft.Office.Interop.Excel.Worksheet ws, int x, int y, object value)
159     {
160         ws.Cells[x, y] = value;
161     }
162 
163     /// <summary>
164     /// 设置工作表的值2
165     /// </summary>
166     /// <param name="ws">工作表的名称</param>
167     /// <param name="x">行</param>
168     /// <param name="y">列</param>
169     /// <param name="value">要设置的值</param>
170     public void SetCellValue(string ws, int x, int y, object value)
171     {
172         GetSheet(ws).Cells[x, y] = value;
173     }
174 
175     /// <summary>
176     /// 设置工作表属性
177     /// </summary>
178     /// <param name="ws">工作表</param>
179     /// <param name="Startx">开始的行</param>
180     /// <param name="Starty">开始的列</param>
181     /// <param name="Endx">结束的行</param>
182     /// <param name="Endy">结束的列</param>
183     /// <param name="size">大小</param>
184     /// <param name="name">字体名称</param>
185     /// <param name="color">颜色</param>
186     /// <param name="HorizontalAlignment">对齐方式</param>
187     public void SetCellProperty(Microsoft.Office.Interop.Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Microsoft.Office.Interop.Excel.Constants color, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)
188     {
189         //name = "宋体 ";
190         //size = 12;
191         //color = Microsoft.Office.Interop.Excel.Constants.xlAutomatic;
192         //HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight;
193         ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
194         ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
195         ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;
196         ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
197     }
198 
199     /// <summary>
200     /// 设置工作表的值
201     /// </summary>
202     /// <param name="ws">工作表的名称</param>
203     /// <param name="Startx">开始的行</param>
204     /// <param name="Starty">开始的列</param>
205     /// <param name="Endx">结束的行</param>
206     /// <param name="Endy">结束的列</param>
207     /// <param name="size">大小</param>
208     /// <param name="name">字体名称</param>
209     /// <param name="color">颜色</param>
210     /// <param name="HorizontalAlignment">对齐方式</param>
211     public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Microsoft.Office.Interop.Excel.Constants color, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)
212     {
213         //name   =   "宋体 ";       
214         //size   =   12;       
215         //color   =   Microsoft.Office.Interop.Excel.Constants.xlAutomatic;       
216         //HorizontalAlignment   =   Microsoft.Office.Interop.Excel.Constants.xlRight;       
217         Microsoft.Office.Interop.Excel.Worksheet ws = GetSheet(wsn);
218         ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
219         ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
220         ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;
221         ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
222     }
223 
224 
225 
226 
227     /// <summary>
228     /// 合并单元格
229     /// </summary>
230     /// <param name="ws">工作表</param>
231     /// <param name="x1">开始的行</param>
232     /// <param name="y1">开始的列</param>
233     /// <param name="x2">结束的行</param>
234     /// <param name="y2">结束的列</param>
235     public void UniteCells(Microsoft.Office.Interop.Excel.Worksheet ws, int x1, int y1, int x2, int y2)
236     {
237         ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
238     }
239 
240     /// <summary>
241     /// 合并单元格
242     /// </summary>
243     /// <param name="ws">工作表名称</param>
244     /// <param name="x1">开始的行</param>
245     /// <param name="y1">开始的列</param>
246     /// <param name="x2">结束的行</param>
247     /// <param name="y2">结束的列</param>
248     public void UniteCells(string ws, int x1, int y1, int x2, int y2)
249     {
250         GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing);
251     }
252 
253     /// <summary>
254     /// 将表格插入到Excel的指定工作表指定位置
255     /// </summary>
256     /// <param name="dt">DataTable</param>
257     /// <param name="ws">工作表名称</param>
258     /// <param name="startX">开始行</param>
259     /// <param name="startY">开始列</param>
260     public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY)
261     {
262         for (int i = 0; i <= dt.Rows.Count - 1; i++)
263         {
264             for (int j = 0; j <= dt.Columns.Count - 1; j++)
265             {
266                 GetSheet(ws).Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();
267             }
268         }
269     }
270 
271 
272 
273     /// <summary>
274     /// DataTable表格添加到Excel指定工作表的指定位置
275     /// </summary>
276     /// <param name="dt">DataTable</param>
277     /// <param name="ws">工作表名称</param>
278     /// <param name="startX">开始行</param>
279     /// <param name="startY">开始列</param>
280     public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY)
281     {
282         for (int i = 0; i <= dt.Rows.Count - 1; i++)
283         {
284             for (int j = 0; j <= dt.Columns.Count - 1; j++)
285             {
286                 GetSheet(ws).Cells[i + startX, j + startY] = dt.Rows[i][j];
287             }
288         }
289     }
290 
291     /// <summary>
292     /// DataTable表格添加到Excel指定工作表的指定位置
293     /// </summary>
294     /// <param name="dt">DataTable</param>
295     /// <param name="ws">工作表</param>
296     /// <param name="startX">开始行</param>
297     /// <param name="startY">开始列</param>
298     public void AddTable(System.Data.DataTable dt, Microsoft.Office.Interop.Excel.Worksheet ws, int startX, int startY)
299     {
300         for (int i = 0; i <= dt.Rows.Count - 1; i++)
301         {
302             for (int j = 0; j <= dt.Columns.Count - 1; j++)
303             {
304                 ws.Cells[i + startX, j + startY] = dt.Rows[i][j];
305             }
306         }
307     }
308 
309     /// <summary>
310     /// 将图片插入到工作表中
311     /// </summary>
312     /// <param name="Filename">图片</param>
313     /// <param name="ws">工作表</param>
314     public void InsertPictures(string Filename, string ws)
315     {
316         GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);//后面的数字表示位置       
317     }
318 
319     public void InsertActiveChart(Microsoft.Office.Interop.Excel.XlChartType ChartType, string ws, int DataSourcesX1, int DataSourcesY1, int DataSourcesX2, int DataSourcesY2, Microsoft.Office.Interop.Excel.XlRowCol ChartDataType)//插入图表操作       
320     {
321         ChartDataType = Microsoft.Office.Interop.Excel.XlRowCol.xlColumns;
322         wb.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
323         {
324             wb.ActiveChart.ChartType = ChartType;
325             wb.ActiveChart.SetSourceData(GetSheet(ws).get_Range(GetSheet(ws).Cells[DataSourcesX1, DataSourcesY1], GetSheet(ws).Cells[DataSourcesX2, DataSourcesY2]), ChartDataType);
326             wb.ActiveChart.Location(Microsoft.Office.Interop.Excel.XlChartLocation.xlLocationAsObject, ws);
327         }
328     }
329 
330     /// <summary>
331     /// 保存文档 
332     /// </summary>
333     /// <returns>是否保存成功</returns>
334     public bool Save()
335     {
336         if (string.IsNullOrEmpty(mFilename))
337         {
338             return false;
339         }
340         else
341         {
342             try
343             {
344                 wb.Save();
345                 return true;
346             }
347             catch (Exception ex)
348             {
349                 return false;
350             }
351         }
352     }
353 
354     /// <summary>
355     /// 文档的另存为
356     /// </summary>
357     /// <param name="FileName">另存为名称</param>
358     /// <returns>是否保存成功</returns>
359     public bool SaveAs(object FileName)//文档另存为       
360     {
361         try
362         {
363             wb.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
364             return true;
365         }
366         catch (Exception ex)
367         {
368             return false;
369         }
370     }
371 
372     /// <summary>
373     /// 将文档另存为Html页
374     /// </summary>
375     /// <param name="HtmlName">Html页面名称</param>
376     /// <returns>是否保存成功</returns>
377     public bool SaveAsHtml(object HtmlName)//文档另存为       
378     {
379         try
380         {
381             wb.SaveAs(HtmlName, Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
382             return true;
383         }
384         catch (Exception ex)
385         {
386             return false;
387         }
388     }
389 
390     /// <summary>
391     /// 关闭一个Excel对象，销毁对象
392     /// </summary>
393     public void Close()
394     {
395 
396         wb.Close(Type.Missing, Type.Missing, Type.Missing);
397         wbs.Close();
398         app.Quit();
399         wb = null;
400         wbs = null;
401         app = null;
402         GC.Collect();
403     }
404 
405 
406     #region Kill Special Excel Process
407     [DllImport("user32.dll", SetLastError = true)]
408     static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);
409 
410     //推荐这个方法，找了很久，不容易啊 
411     public void KillSpecialExcel()
412     {
413         try
414         {
415             if (app != null)
416             {
417                 int lpdwProcessId;
418                 GetWindowThreadProcessId(new IntPtr(app.Hwnd), out lpdwProcessId);
419 
420                 System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
421             }
422         }
423         catch (Exception ex)
424         {
425             Console.WriteLine("Delete Excel Process Error:" + ex.Message);
426         }
427     }
428     #endregion
429 
430 }

View Code

参考：http://blog.sina.com.cn/s/blog_4adf31ea0100ifi3.html

　　　http://blog.csdn.net/zhanglei5415/article/details/5396311

　　　http://www.cnblogs.com/wang_yb/articles/1750419.html

　　　http://www.open-open.com/code/view/1420029490093