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)
        {
            // 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
    }
}

 

采用Excel的com组件，在部署时是挺麻烦的，现在有开源的Excel组件，也可以进行像com组件那样的操作！