C# 操作Excel ——Excel获取数据、时间、图片

     最近开发项目中，涉及到通过c#，向Excel中读取数据、插入图片、读取时间等操作。经过查找网上资料，终于完成相关操作，现将代码粘贴出来：

  1 public class ExcelControl:ConvertObject 
  2     {
  3         //Fields
  4         private _Workbook _objBook=null ;
  5         private Application _objExcel=null ;
  6         private object _objOpt=Missing.Value ;
  7         private Range _objRange=null ;
  8         private _Worksheet _objWorkSheet=null ;
  9         private Sheets _objSheets=null ;
 10         private string _savePath;//文件保存路径
 11         private int _sheetIndex=1;//工作表的索引
 12         private string _templatePath;//文件模板路径
 13 
 14         
 15 
 16 //Properties
 17         public string SavePath
 18         {
 19             get { return _savePath; }
 20             set { _savePath = value; }
 21         }
 22 
 23         public int SheetIndex
 24         {
 25             get { return _sheetIndex; }
 26             set { _sheetIndex = value; }
 27         }
 28         public string TemplatePath
 29         {
 30             get { return _templatePath; }
 31             set { _templatePath = value; }
 32         }
 33 
 34         //Methods
 35 
 36         public ExcelControl()
 37         {
 38             this._objExcel = new ApplicationClass();
 39         }
 40 
 41         public void CellsAlignment(int startRow, int startColumn, int endRow, int endColumn, XlHAlign hAlign, XlVAlign vAlign)
 42         {
 43             Range range = this._objWorkSheet.get_Range(this._objWorkSheet.Cells[startRow, startColumn], this._objWorkSheet.Cells[endRow, endColumn]);
 44             range.HorizontalAlignment = hAlign;
 45             range.VerticalAlignment = vAlign;
 46         }
 47 
 48         public void CellsUnite(int startRow, int startColumn, int endRow, int endColumn)
 49         {
 50             this._objWorkSheet.get_Range(this._objWorkSheet.Cells[startRow, startColumn], this._objWorkSheet.Cells[endRow, endColumn]).MergeCells = true;
 51         }
 52 
 53         /// <summary>
 54 /// 关闭excel
 55 /// </summary>
 56         public void Close()
 57         {
 58             try
 59             {
 60                 this._objBook.Close(_objOpt, _objOpt, _objOpt);
 61                 this._objExcel.Workbooks.Close();
 62                 this._objExcel.Quit();
 63                 Marshal.ReleaseComObject(_objBook);
 64                 Marshal.ReleaseComObject(_objExcel);
 65                 this._objBook = null;
 66                 this._objExcel = null;
 67                 GC.Collect();
 68             }
 69             catch(Exception ex)
 70             {
 71                 ErrMessage = ex.Message;
 72             }
 73         }
 74 
 75         /// <summary>
 76 /// 获取excel中单元格数据
 77 /// </summary>
 78 /// <param name="range"></param>
 79 /// <returns></returns>
 80         public object GetRange(object range)
 81         {
 82             try
 83             {
 84                 this._objRange = this._objWorkSheet.get_Range(range, this._objOpt);
 85                 return this._objRange.Value2;
 86             }
 87             catch (Exception ex)
 88             {
 89                 this.ErrMessage = ex.Message;
 90                 return null;
 91             }
 92         }
 93 
 94         /// <summary>
 95 /// 向excel中插入图片
 96 /// </summary>
 97 /// <param name="pictureName">图片名称</param>
 98 /// <param name="left">左边宽度</param>
 99 /// <param name="top">顶部宽度</param>
100 /// <param name="width">宽度</param>
101 /// <param name="height">高度</param>
102         public void InsertPictures(string pictureName, int left, int top, int width, int height)
103         {
104             this._objWorkSheet.Shapes.AddPicture(pictureName, MsoTriState.msoFalse, MsoTriState.msoTrue, (float)left, (float)top, (float)width, (float)height);
105         }
106 
107         public bool InsertRow(object rowID, object colunmID)
108         {
109             try
110             {
111                 Range range = (Range)this._objWorkSheet.Cells[rowID, colunmID];
112                 range.Select();
113                 range.EntireRow.Insert(this._objOpt, this._objOpt);
114                 return true;
115             }
116             catch (Exception)
117             {
118                 return false;
119             }
120         }
121 
122         /// <summary>
123 /// 打开excel
124 /// </summary>
125 /// <returns></returns>
126         public bool Open()
127         {
128             try
129             {
130                 this._objBook = this._objExcel.Workbooks.Open(this._templatePath, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt);
131                 this._objSheets = this._objBook.Worksheets;
132                 return true;
133             }
134             catch(Exception e)
135             {
136                 this.ErrMessage = e.Message;
137                 return false;
138             }
139         }
140 
141         /// <summary>
142 /// 打开模板
143 /// </summary>
144 /// <param name="filePath"></param>
145 /// <returns></returns>
146         public bool Open(string filePath)
147         {
148             this._templatePath = filePath;
149             try
150             {
151                 this._objBook = this._objExcel.Workbooks.Open(this._templatePath, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt);
152                 this._objSheets = this._objBook.Worksheets;
153                 return true;
154             }
155             catch (Exception e)
156             {
157                 this.ErrMessage = e.Message;
158                 return false;
159             }
160         }
161 
162         /// <summary>
163 /// 行自动适合宽度
164 /// </summary>
165 /// <param name="rowNum"></param>
166         public void RowAutoFit(int rowNum)
167         {
168             Range range = (Range)this._objWorkSheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), Type.Missing];
169             range.EntireColumn.AutoFit();
170         }
171 
172         /// <summary>
173 /// 保存
174 /// </summary>
175 /// <returns></returns>
176         public bool Save()
177         {
178             try
179             {
180                 this._objBook.Save();
181                 return true;
182             }
183             catch (Exception ex)
184             {
185                 this.ErrMessage = ex.Message;
186                 return false;
187             }
188         }
189 
190         /// <summary>
191 /// 另存为
192 /// </summary>
193 /// <returns></returns>
194         public bool SaveAs()
195         {
196             try
197             {
198                 this._objBook.SaveAs(this._savePath, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, XlSaveAsAccessMode.xlNoChange, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt);
199                 return true;
200             }
201             catch (Exception ex)
202             {
203                 this.ErrMessage = ex.Message;
204                 return false;
205             }
206         }
207         public bool SaveAs(string newPath)
208         {
209             this._savePath = newPath;
210             try
211             {
212                 this._objBook.SaveAs(this._savePath, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt, XlSaveAsAccessMode.xlNoChange, this._objOpt, this._objOpt, this._objOpt, this._objOpt, this._objOpt);
213                 return true;
214             }
215             catch (Exception ex)
216             {
217                 this.ErrMessage = ex.Message;
218                 return false;
219             }
220         }
221 
222         /// <summary>
223 /// 工作表激活状态
224 /// </summary>
225 /// <returns></returns>
226         public bool SetActiveSheer()
227         {
228             try
229             {
230                 this._objWorkSheet = (_Worksheet)this._objSheets.get_Item(this._sheetIndex);
231                 return true;
232             }
233             catch (Exception ex)
234             {
235                 this.ErrMessage = ex.Message;
236                 return false;
237             }
238         }
239 
240         /// <summary>
241 /// 
242 /// </summary>
243 /// <param name="activeIndex"></param>
244 /// <returns></returns>
245         public bool SetActiveSheer(int activeIndex)
246         {
247             this._sheetIndex = activeIndex;
248             try
249             {
250                 this._objWorkSheet = (_Worksheet)this._objSheets.get_Item(this._sheetIndex);
251                 return true;
252             }
253             catch (Exception ex)
254             {
255                 this.ErrMessage = ex.Message;
256                 return false;
257             }
258         }
259 
260         /// <summary>
261 /// 设置显示警告
262 /// </summary>
263 /// <param name="b"></param>
264         public void SetDisplayAlerts(bool b)
265         {
266             this._objExcel.DisplayAlerts = b;
267         }
268 
269         /// <summary>
270 /// 向excel中插入值
271 /// </summary>
272 /// <param name="range">插入位置</param>
273 /// <param name="newValue">插入值</param>
274         public void SetRange(object range, object newValue)
275         {
276             try
277             {
278                 this._objRange = this._objWorkSheet.get_Range(range, this._objOpt);
279                 this._objRange.Value2 = newValue;
280             }
281             catch (Exception ex)
282             {
283                 this.ErrMessage = ex.Message;
284             }
285         }
286 
287         /// <summary>
288 /// 获取excel中时间格式
289 /// </summary>
290 /// <param name="strDate"></param>
291 /// <returns></returns>
292         public DateTime GetConvertDate(string strDate)
293         {
294             return DateTime.FromOADate(Convert.ToDouble(strDate));
295         }
296     }

     本人能力有限，如有错误，望大家指正。

转载于:https://www.cnblogs.com/Peter-Luo/archive/2011/12/23/C_Excel.html