using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Reflection;
using System.Windows.Forms;

namespace ExcelBLL
{
    public class ExcelHandle
    {        
        #region 变量区
        object oMissing = System.Reflection.Missing.Value;
        private Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
        private Excel.Workbooks oBooks = null;
        private Excel.Workbook wBooks = null;
        private Excel._Workbook oBook = null;
        private Excel.Worksheet xSt = null;
        private Excel.Range range = null;
        private string FileName = "";
        private VBIDE.VBComponent module = null;
        #endregion

        #region 方法区
        #region Add Macro
        /// <summary>
        /// 添加宏
        /// </summary>
        /// <param name="list">添加多个宏</param>
        public void AddMacro(ArrayList list)
        {
            try
            {
                module = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
                foreach (object l in list)
                {
                    module.CodeModule.AddFromString(l.ToString());
                }
            }
            catch (Exception ex)
            {
                throw new Exception("添加失败:" + ex.Message);
            }
        }

        /// <summary>
        /// 添加宏
        /// </summary>
        /// <param name="macro">宏</param>
        public void AddMacro(string macro)
        {
            try
            {
                module = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
                module.CodeModule.AddFromString(macro);
            }
            catch (Exception ex)
            {
                throw new Exception("添加失败:" + ex.Message);
            }
        }
        #endregion

        #region Delete Macro
        /// <summary>
        /// 根据指定宏的名称 删除宏
        /// </summary>
        /// <param name="macroName">宏的名称</param>
        public void DelMacro(string macroName)
        {
            try
            {
                module = ((VBIDE.VBComponentsClass)(oBook.VBProject.VBComponents)).Item(macroName);
                module.CodeModule.DeleteLines(1, module.CodeModule.CountOfLines);
            }
            catch (Exception ex)
            {
                throw new Exception("删除失败:" + ex.Message);
            }
        }
        #endregion

        #region Clear Macro
        /// <summary>
        /// 删除所有宏
        /// </summary>
        public void ClearMacro()
        {
            try
            {
                foreach (VBIDE.VBComponent module1 in (VBIDE.VBComponentsClass)(oBook.VBProject.VBComponents))
                {
                    if (module1.Type == VBIDE.vbext_ComponentType.vbext_ct_StdModule)
                    {
                        module1.CodeModule.DeleteLines(1, module.CodeModule.CountOfLines);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("删除失败:" + ex.Message);
            }

        }
        #endregion

        #region Run Macro
        /// <summary>
        /// 运行宏
        /// </summary>
        /// <param name="list">要运行的宏</param>
        public void RunMacro(ArrayList list)
        {
            try
            {
                foreach (object l in list)
                {
                    oBook.Application.Run(l.ToString(), Missing.Value, Missing.Value, Missing.Value,
                                       Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                       Missing.Value,