Excellib代码

Excellib代码 
/********************************************************  
 * 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)  
        {  
            // 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);  
        }  
 
        /// <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;  
 
            Range range = (Range)this._ws.Cells[row, col];  
 
            return range.Value2;  
        }  
 
        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  
    }  

posted @ 2011-05-06 17:51  *天道酬勤*  阅读(375)  评论(0编辑  收藏  举报