C#操作Excel类

/********************************************************  
 * Code Base of C#  
 * Excel report develop tools  
 * Create by huang pinghua 2008/12/28, all rights reserved.  
 * *****************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using System.IO;

namespace ExcelX.ExcelLib
{
    /// <summary>  
    /// Excel 操作代理  
    /// </summary>  
    public class ExcelAgent
    {
        private ApplicationClass _app = null;
        private _Workbook _wb = null;
        private _Worksheet _ws = null;
        private string _filePath = "";
        private int _shIndex = 0; // 1 based index  

        public event EventHandler ExcelExceptionOccured;

        /// <summary>  
        /// 当前Sheet  
        /// </summary>  
        public int SheetIndex { get { return this._shIndex; } }

        /// <summary>  
        /// 当前文件名  
        /// </summary>  
        public string FileName { get { return this._filePath; } }

        #region private operations
        /// <summary>  
        /// 打开App  
        /// </summary>  
        private void OpenApp()
        {
            this._app = new ApplicationClass();
            this._app.Visible = false;
        }

        /// <summary>  
        /// 释放资源  
        /// </summary>  
        /// <param name="o"></param>  
        private void ReleaseCom(object o)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);//强制释放一个对象  
            }
            catch { }
            finally
            {
                o = null;
            }
        }

        /// <summary>  
        /// 检查App  
        /// </summary>  
        private bool CheckApp()
        {
            if (this._app == null)
            {
                if (this.ExcelExceptionOccured != null)
                {
                    this.ExcelExceptionOccured(this, new ErrorEventArgs(new Exception("Application对象未初始化")));
                }
                return false;
            }

            return true;
        }

        /// <summary>  
        /// 检查Book  
        /// </summary>  
        private bool CheckWorkBook()
        {
            if (this._wb == null)
            {
                if (this.ExcelExceptionOccured != null)
                {
                    this.ExcelExceptionOccured(this, new ErrorEventArgs(new Exception("Workbook对象未初始化")));
                }

                return false;
            }

            return true;
        }

        /// <summary>  
        /// 检查Sheet  
        /// </summary>  
        private bool CheckSheet()
        {
            if (this._ws == null)
            {
                if (this.ExcelExceptionOccured != null)
                {
                    this.ExcelExceptionOccured(this, new ErrorEventArgs(new Exception("Worksheet对象未初始化")));
                }

                return false;
            }

            return true;
        }
        #endregion

        #region basic operation
        /// <summary>  
        /// 打开文件  
        /// </summary>  
        /// <param name="filePath"></param>  
        public void Open(string filePath)
        {
            if (!File.Exists(filePath))
            {
                //打开工作簿 
                Workbook mybook = (Workbook)this._app.Workbooks.Add(1);   //   1表示只建一个表 
                //保存 
                mybook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange,
                        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            }
            // Check Application   
            if (!this.CheckApp()) return;

            // Open workbook  
            this._filePath = filePath;
            this._wb = this._app.Workbooks._Open(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value
                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            // set default sheet  
            this.SetCurrentSheet(1);
        }

        public void Creat(string filePath)
        {
            if(!File.Exists(filePath) )
            {
                ApplicationClass   my=new   ApplicationClass(); 
                                  
                my.Visible=false; 
                object   objMissing=System.Reflection.Missing.Value;



                //打开工作簿 
                Workbook mybook = (Workbook)my.Workbooks.Add(1);   //   1表示只建一个表 


                //保存 
                mybook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange,
                        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            }
        }





        /// <summary>  
        /// 自动打开Excel对象  
        /// </summary>  
        public ExcelAgent()
        {
            this.OpenApp();
        }

        /// <summary>  
        /// 打开excel文件  
        /// </summary>  
        /// <param name="filePath"></param>  
        public ExcelAgent(string filePath)
        {
            this.OpenApp();
            this.Open(filePath);
        }

        /// <summary>  
        /// 保存当前文档  
        /// </summary>  
        public void Save()
        {
            // check workbook   
            if (!this.CheckWorkBook()) return;

            // save the book  
            this._wb.Save();

        }

        /// <summary>  
        /// 另存当前文档  
        /// </summary>  
        /// <param name="filePath"></param>  
        public void Save(string filePath)
        {
            // check workbook   
            if (!this.CheckWorkBook()) return;

            // save work book  
            this._filePath = filePath;
            bool b = this._app.DisplayAlerts;
            this._app.DisplayAlerts = false;

            // save work book              
            this._wb.SaveAs(this._filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            this._app.DisplayAlerts = b;
        }

        /// <summary>  
        /// 关闭当前操作  
        /// </summary>  
        public void Close()
        {
            if (this._app == null) return;
            if (this._wb != null)
            {
                this._wb.Close(false, Missing.Value, Missing.Value);
                ReleaseCom(this._wb);
                this._wb = null;
            }
            this._app.Quit();
            ReleaseCom(this._app);
            this._app = null;

        }

        /// <summary>  
        /// 设置当前工作Sheet（序号：从1记起）  
        /// </summary>  
        /// <param name="sheetIndex"></param>  
        public void SetCurrentSheet(int sheetIndex)
        {
            // check workbook   
            if (!this.CheckWorkBook()) return;

            // set sheet object  
            this._shIndex = sheetIndex;
            this._ws = (_Worksheet)this._wb.Worksheets[sheetIndex];
        }

        /// <summary>  
        /// 设置当前工作Sheet（序号：从1记起）  
        /// </summary>  
        /// <param name="sheetIndex"></param>  
        public void SetCurrentSheet(string SheetName)
        {
            // check workbook   
            if (!this.CheckWorkBook()) return;

            // set sheet object  
            this._ws = (_Worksheet)this._wb.Worksheets[SheetName];
            this._shIndex = this._ws.Index;
        }

        /// <summary>  
        /// 删除一个工作表  
        /// </summary>  
        /// <param name="SheetName"></param>  
        public void DeleteSheet()
        {
            // check workbook   
            if (!this.CheckSheet()) return;

            this._ws.Delete();
        }

        /// <summary>  
        /// 改名  
        /// </summary>  
        /// <param name="newName"></param>  
        public void RenameSheet(string newName)
        {
            // check workbook   
            if (!this.CheckSheet()) return;

            this._ws.Name = newName;
        }

        /// <summary>  
        /// 创建Sheet  
        /// </summary>  
        /// <param name="newName"></param>  
        public void CreateSheet(string newName)
        {
            // check workbook   
            if (!this.CheckWorkBook()) return;

            this._wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        }

        /// <summary>  
        /// 获取数量  
        /// </summary>  
        /// <returns></returns>  
        public int GetSheetCount()
        {
            // check workbook   
            if (!this.CheckWorkBook()) return -1;

            return this._wb.Worksheets.Count;
        }
        #endregion

        #region sheet operation
        /// <summary>  
        /// 设置单元值  
        /// </summary>  
        /// <param name="x"></param>  
        /// <param name="y"></param>  
        /// <param name="value"></param>  
        public void SetCellValue(int x, int y, object value)
        {
            if (!this.CheckSheet()) return;
            this._ws.Cells[x, y] = value;
        }

        /// <summary>  
        /// 合并单元格  
        /// </summary>  
        /// <param name="x1"></param>  
        /// <param name="y1"></param>  
        /// <param name="x2"></param>  
        /// <param name="y2"></param>  
        public void UniteCells(int x1, int y1, int x2, int y2)
        {
            if (!this.CheckSheet()) return;
            this._ws.get_Range(this._ws.Cells[x1, y1], this._ws.Cells[x2, y2]).Merge(Type.Missing);
        }

        /// <summary>  
        /// 将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一  
        /// </summary>  
        /// <param name="dt"></param>  
        /// <param name="startX"></param>  
        /// <param name="startY"></param>  
        public void InsertTable(System.Data.DataTable dt, int startX, int startY)
        {
            if (!this.CheckSheet()) return;

            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    this._ws.Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();

                }

            }

        }

        /// <summary>  
        /// 获取单元格值  
        /// </summary>  
        /// <param name="cellName"></param>  
        /// <returns></returns>  
        public object GetCellValue(string cellName)
        {
            if (!this.CheckSheet()) return null;

            Range range = this._ws.get_Range(cellName, Type.Missing);

            return range.Value2;
        }

        /// <summary>  
        /// 获取单元格值  
        /// </summary>  
        /// <param name="row"></param>  
        /// <param name="col"></param>  
        /// <returns></returns>  
        public object GetCellValue(int row, int col)
        {
            if (!this.CheckSheet()) return null;
            try
            {
                Range range = (Range)this._ws.Cells[row, col];
                return range.Value2;
            }
            catch
            {
                return "";
            }

            
        }

        public string GetStringValue(string cellName)
        {
            object val = this.GetCellValue(cellName);
            string result = "";

            if (val != null) result = val.ToString();

            return result;
        }

        public string GetStringValue(int row, int col)
        {
            object val = this.GetCellValue(row, col);
            string result = "";

            if (val != null) result = val.ToString();

            return result;
        }

        public double GetDoubleValue(string cellName)
        {
            object val = this.GetCellValue(cellName);
            string result = "";

            if (val != null) result = val.ToString();

            double number = 0d;
            if (double.TryParse(result, out number))
            {
                number = double.Parse(result);
            }
            else
            {
                number = 0d;
            }

            return number;
        }

        public double GetDoubleValue(int row, int col)
        {
            object val = this.GetCellValue(row, col);
            string result = "";

            if (val != null) result = val.ToString();

            double number = 0d;
            if (double.TryParse(result, out number))
            {
                number = double.Parse(result);
            }
            else
            {
                number = 0d;
            }

            return number;
        }

        #endregion
    }
}

 实例