对ｅｘｃｅｌ操作的完整代码

　1　using System;
　2using System.Collections.Generic;
　3using System.Text;
  4using Excel;
  5using System.Reflection;
  6
  7namespace CW
  8{
  9    public class myExcel
 10    {
 11        private ApplicationClass excelApp;
 12
 13        private Workbook excelBook;
 14
 15        Object Nothing = System.Reflection.Missing.Value;
 16
 17
 18        /// <summary>
 19        /// 修改 Excel 的属性
 20        /// </summary>
 21        /// <param name="ExcelPath"></param>
 22        public void ModifyMemo(string ExcelPath)
 23        {
 24            excelApp = new ApplicationClass();
 25            excelApp.Visible = false;
 26            excelApp.DisplayAlerts = false;
 27            excelApp.AlertBeforeOverwriting = false;
 28            excelApp.AskToUpdateLinks = false;
 29            //
 30            excelBook = excelApp.Workbooks.Open(ExcelPath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
 31
 32            object props = excelBook.BuiltinDocumentProperties;
 33            object _p = props.GetType().InvokeMember("", BindingFlags.GetProperty, null, props, new object[] { "Subject" });
 34            _p.GetType().InvokeMember("", BindingFlags.SetProperty, null, _p, new object[] { "修改的值" });
 35
 36            excelBook.Save();
 37
 38            if (excelBook != null)
 39            {
 40                excelBook.Close(Nothing, Nothing, Nothing);
 41                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook);
 42                excelBook = null;
 43
 44                excelApp.Application.Quit();
 45                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
 46                excelApp = null;
 47            }
 48            GC.Collect();
 49            GC.WaitForPendingFinalizers();
 50        }
/// <summary>
 53        /// 列号到列ID转换
 54        /// </summary>
 55        /// <param name="columnNum">列号数字</param>
 56        /// <returns>列ID，如 A、AB等，1返回A</returns>
 57        public static string ColumnNumToColumnID(int columnNum)
 58        {
 59            if (columnNum < 1)
 60            {
 61                return "";
 62            }
 63            int res, left;
 64            string ID = String.Empty;
 65            res = (columnNum - 1) / 26;
 66            left = columnNum - res * 26;
 67            ID = left.ToString();
 68
 69            columnNum = res;
 70            while (columnNum > 26)
 71            {
 72                res = (columnNum - 1) / 26;
 73                left = columnNum - res * 26;
 74                ID = left.ToString() + "|" + ID;
 75                columnNum = res;
 76            }
 77            if (columnNum != 0)
 78            {
 79                ID = columnNum.ToString() + "|" + ID;
 80            }
 81
 82            String[] str = ID.Split('|');
 83            ID = String.Empty;
 84            for (int i = 0; i < str.Length; i++)
 85            {
 86                ID += Convert.ToString((char)((int)('A') + Convert.ToInt16(str[i]) - 1));
 87            }
 88            return ID;
 89        }
/// <summary>
 92        /// 列 ID 到  列号的转换 
 93        /// </summary>
 94        /// <param name="cloumnID"></param>
 95        /// <returns>A返回1</returns>
 96        public static int ColumnIDToColumnNum(string columnID)
 97        {
 98            int res = 0;
 99            for (int i = 0; i < columnID.Length; i++)
100            {
101                res += ((int)columnID[i] - (int)('A') + 1) * (int)Math.Pow(26, (columnID.Length - i - 1));
102            }
103            return res;
104        }
/// <summary>
107        /// 获取单元格的值
108        /// </summary>
109        /// <param name="row"></param>
110        /// <param name="col"></param>
111        /// <param name="worksheet"></param>
112        /// <returns></returns>
113        public string getExcelCellValue(int row, int col, Excel.Worksheet worksheet)
114        {
115            string strCellID = string.Format("{0}{1}", ColumnNumToColumnID(col), row);
116
117            Excel.Range Cell = worksheet.get_Range(strCellID, Type.Missing);
118            object objContent = (Cell.GetType().InvokeMember("Value", System.Reflection.BindingFlags.GetProperty, null, Cell, null));
119            if (objContent == null || objContent.ToString().Trim() == String.Empty)
120            { ///该单元格为空值，被允许
121                return string.Empty;
122            }
123            else
124            {
125                ///如果能转换为浮点型数据
126                return objContent.ToString();
127            }
128        }
/// <summary>
132        /// 清除颜色定义
133        /// </summary>
134        /// <param name="area"></param>
135        private void clearRuleColor(ICellArea area)
136        {
137            string startCell, endCell;
138            startCell = string.Format("{0}{1}", Common.CommonFunction.ColumnNumToColumnID(area.StartCol), area.StartRow);
139            endCell = string.Format("{0}{1}", Common.CommonFunction.ColumnNumToColumnID(area.EndCol), area.EndRow);
140            Excel.Range range = ExcelWorksheet.get_Range(startCell, endCell);
141
142            if (range != null)
143            {
144                range.Interior.ColorIndex = 0;
145            }
146        }

/// <summary>
149        /// 设置区域颜色
150        /// </summary>
151        /// <param name="area"></param>
152        /// <param name="color"></param>
153        private void setRuleColor(int startRow,int startCol,int endRow,int endCol, int color)
154        {
155            区域设置背景颜色方法 ----#region 区域设置背景颜色方法 ----
156            
157            string startCell, endCell;
158            startCell = string.Format("{0}{1}", Common.CommonFunction.ColumnNumToColumnID(startCol), startRow);
159            endCell = string.Format("{0}{1}", Common.CommonFunction.ColumnNumToColumnID(endCol), endRow);
160            Excel.Range range = ExcelWorksheet.get_Range(startCell, endCell);
161
162            if (range != null)
163            {
164                range.Interior.Color = color;
165
166                ///该方法设置区域颜色为无色
167                ///range.Interior.ColorIndex = 0;
168            }
169            #endregion
170
171            
177        }
/// <summary>
195        /// 返回Excel文档所有的Sheet
196        /// </summary>
197        /// <param name="excelPath"></param>
198        /// <returns></returns>
199        public List<string> getSheetFromExcel(string excelPath)
200        {
201            List<string> Sheets = new List<string>();
202            OleDbConnection Conn = new OleDbConnection();
203            Conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + excelPath + ";Extended Properties=Excel 8.0;Persist Security Info=False";
204            Conn.Open();
205            System.Data.DataTable dt = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
206
207            int count = dt.Rows.Count;
208            if (count == 0)
209                return null;
210
211            for (int i = 0; i < count; i++)
212            {
213                Sheets.Add(dt.Rows[i]["TABLE_NAME"].ToString());
214            }
215            return Sheets;
216        }
/// <summary>
219        /// 把Excel文档转换为Xml文档
220        /// </summary>
221        /// <param name="excelPath"></param>
222        /// <param name="xmlPath"></param>
223        public void changeExcelToXml(string excelPath, string xmlPath)
224        {
225            Object Nothing = System.Reflection.Missing.Value;
226            ApplicationClass excelApp;
227            Workbook excelBook;
228            excelApp = new ApplicationClass();
229            excelApp.Visible = false;
230            excelApp.DisplayAlerts = false;
231            excelApp.AlertBeforeOverwriting = false;
232            excelApp.AskToUpdateLinks = false;
233            //
234            excelBook = excelApp.Workbooks.Open(excelPath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
235            excelBook.SaveAs(xmlPath, XlFileFormat.xlXMLSpreadsheet, Nothing, Nothing, Nothing, Nothing, XlSaveAsAccessMode.xlNoChange, Nothing, Nothing, Nothing, Nothing, Nothing);
236            //close
237            excelBook.Close(Nothing, Nothing, Nothing);
238            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook);
239            excelBook = null;
240
241            excelApp.Application.Quit();
242            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
243            excelApp = null;
244
245            GC.Collect();
246            GC.WaitForPendingFinalizers();
247        }
248
/// <summary>
251        /// 把 Xml 格式的 Excel 文件转换成 二进制格式的 Excel 文件
252        /// </summary>
253        /// <param name="xlsPath"></param>
254        public void RepairXls(string xlsPath)
255        {
256            Workbook excelBook = null;
257            ApplicationClass excelApp = null;
258            object Nothing = System.Reflection.Missing.Value;
259            excelApp = new ApplicationClass();
260            excelApp.Visible = false;
261            excelApp.DisplayAlerts = false;
262            excelApp.AlertBeforeOverwriting = false;
263            excelApp.AskToUpdateLinks = false;
264
265            excelBook = excelApp.Workbooks.Open(xlsPath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
266            excelBook.SaveAs(xlsPath, XlFileFormat.xlExcel5, Nothing, Nothing, Nothing, Nothing, XlSaveAsAccessMode.xlNoChange, Nothing, Nothing, Nothing, Nothing, Nothing);
267
268            if (excelBook != null)
269            {
270                excelBook.Close(Nothing, Nothing, Nothing);
271                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook);
272                excelBook = null;
273
274                excelApp.Application.Quit();
275                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
276                excelApp = null;
277            }
278            GC.Collect();
279            GC.WaitForPendingFinalizers();
280        }
281    }
282}