C#封装Excel常用操作类

 

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Text;
  4 
  5 using Excel = Microsoft.Office.Interop.Excel;
  6 using System.Diagnostics;
  7 using System.Windows.Forms;
  8 
  9 namespace OfficeManager
 10 {
 11     public class ExcelClass : IDisposable
 12     {
 13         private Excel._Application m_ExcelApplication = null;
 14         private Excel._Workbook m_Workbook = null;
 15         public Excel._Worksheet m_Worksheet = null;
 16         private object missing = System.Reflection.Missing.Value;
 17 
 18         public ExcelClass()
 19         {
 20             if (m_ExcelApplication == null)
 21             {
 22                 m_ExcelApplication = new Excel.ApplicationClass();
 23             }
 24         }
 25 
 26         ~ExcelClass()
 27         {
 28             try
 29             {
 30                 if (m_ExcelApplication != null)
 31                     m_ExcelApplication.Quit();
 32             }
 33             catch (Exception ex)
 34             {
 35                 Debug.Write(ex.ToString());
 36             }
 37         }
 38         /// <summary>
 39         /// 获取或设置当前工作表
 40         /// </summary>
 41         public int CurrentWorksheetIndex
 42         {
 43             set
 44             {
 45                 if (value <= 0 || value > m_Workbook.Worksheets.Count)
 46                     throw new Exception("索引超出范围");
 47                 else
 48                 {
 49                     object index = value;
 50                     m_Worksheet = m_Workbook.Worksheets[index] as Excel._Worksheet;
 51                 }
 52             }
 53         }
 54         /// <summary>
 55         /// 打开一个Excel工作薄
 56         /// </summary>
 57         /// <param name="fileName"></param>
 58         public void OpenWorkbook(string fileName)
 59         {
 60             m_Workbook = m_ExcelApplication.Workbooks.Open(fileName, missing, missing, missing, missing, missing,
 61                 missing, missing, missing, missing, missing, missing, missing, missing, missing);
 62 
 63             if (m_Workbook.Worksheets.Count > 0)
 64             {
 65                 object index = 1;
 66                 m_Worksheet = m_Workbook.Worksheets[index] as Excel._Worksheet;
 67 
 68             }
 69         }
 70         /// <summary>
 71         /// 保存数据
 72         /// </summary>
 73         public void Save()
 74         {
 75             if (m_Workbook != null)
 76             {
 77                 m_Workbook.Save();
 78             }
 79         }
 80         /// <summary>
 81         /// 关闭文档
 82         /// </summary>
 83         /// <param name="isSave"></param>
 84         public void Close(bool isSave)
 85         {
 86             this.ClearClipboard();
 87 
 88             object obj_Save = isSave;
 89             if (m_Workbook != null)
 90                 m_Workbook.Close(obj_Save, missing, missing);
 91         }
 92         /// <summary>
 93         /// 设置当前工作表中某单元格的值
 94         /// </summary>
 95         /// <param name="cellIndex"></param>
 96         /// <param name="value"></param>
 97         public Excel.Range SetCellValue(string cellIndex, object value)
 98         {
 99             if (m_Worksheet != null)
100             {
101                 object cell1 = cellIndex;
102                 Excel.Range range = m_Worksheet.get_Range(cell1, missing);
103                 if (range != null)
104                 {
105                     range.Value2 = value;
106                 }
107 
108                 return range;
109             }
110 
111             return null;
112         }
113         /// <summary>
114         /// 合并单元格
115         /// </summary>
116         /// <param name="cellIndex1"></param>
117         /// <param name="cellIndex2"></param>
118         public Excel.Range Merge(string cellIndex1, string cellIndex2)
119         {
120             if (m_Worksheet != null)
121             {
122                 object cell1 = cellIndex1;
123                 object cell2 = cellIndex2;
124                 Excel.Range range = m_Worksheet.get_Range(cell1, cell2);
125                 range.MergeCells = true;
126 
127                 return range;
128             }
129 
130             return null;
131         }
132         /// <summary>
133         /// 将当前工作表中的表格数据复制到剪切板
134         /// </summary>
135         public void Copy()
136         {
137             if (m_Worksheet != null)
138             {
139                 try
140                 {
141                     m_Worksheet.UsedRange.Select();
142                 }
143                 catch { }
144                 m_Worksheet.UsedRange.Copy(missing);
145             }
146         }
147         /// <summary>
148         /// 清空剪切板
149         /// </summary>
150         public void ClearClipboard()
151         {
152             Clipboard.Clear();
153         }
154 
155         #region IDisposable 成员
156 
157         public void Dispose()
158         {
159             try
160             {
161                 if (m_ExcelApplication != null)
162                 {
163                     this.Close(false);
164                     m_ExcelApplication.Quit();
165                     m_ExcelApplication = null;
166                 }
167             }
168             catch (Exception ex)
169             {
170                 Debug.Write(ex.ToString());
171             }
172         }
173 
174         #endregion
175     }
176 }