Embed excel into winform （转）

原链接：http://www.cnblogs.com/jeet/archive/2004/07/07/22161.html

 

把Excel嵌入winform中，其实在思路的博客与MSDN都有相关的介绍，但所有的文章对于怎么获得载入的excel对象都没有说得太清楚，下面是我写的一个简单控件，用.net framework 1.1+office 2003测试通过。

using System;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Reflection;
using System.Windows.Forms;
using Microsoft.Win32;
using Microsoft.Office.Interop.Excel;
using ExcelApplication=Microsoft.Office.Interop.Excel.ApplicationClass;


namespace ExcelTest
{
    /**//// <summary>
    /// ExcelControl 的摘要说明。
    /// </summary>
    public class ExcelControl : System.Windows.Forms.UserControl
    {

        私有变量、方法和构造函数#region 私有变量、方法和构造函数

        private AxSHDocVw.AxWebBrowser axWebBrowser1;

        //奇怪，必须初始化一个ExcelApplication方能正常工作
        private ExcelApplication excel=new ExcelApplication();
        
        private object missing=Missing.Value;

        /**//// <summary> 
        /// 必需的设计器变量。
        /// </summary>
        private System.ComponentModel.Container components = null;

        public ExcelControl()
        {
            // 该调用是 Windows.Forms 窗体设计器所必需的。
            InitializeComponent();

            // TODO: 在 InitializeComponent 调用后添加任何初始化

        }

        private void axWebBrowser1_NavigateComplete2(object sender, AxSHDocVw.DWebBrowserEvents2_NavigateComplete2Event e)
        {
            object o=e.pDisp;
            object oDocument = o.GetType().InvokeMember("Document",BindingFlags.GetProperty,null,o,null);
            excel=(ExcelApplication)o.GetType().InvokeMember("Application",BindingFlags.GetProperty,null,oDocument,null);                   
            //MessageBox.Show(excel.ActiveSheet.ToString());
        }

        /**//// <summary>
        /// 释放EXCEL进程
        /// </summary>
        private void releaseExcel()
        {
            excel.Quit();
            excel=null;
            axWebBrowser1.Dispose();
            GC.Collect();
        }    
        
        /**//// <summary>
        /// 判断一个工作表中是否有内容
        /// </summary>
        /// <param name="worksheet"></param>
        /// <returns></returns>
        private bool hasContent(Worksheet worksheet)
        {
            Range range=worksheet.get_Range("A1","G15");
            object[,] vals=(object[,])range.get_Value(missing);
            string content=string.Empty;
            foreach(object val in vals)
            {
                if(val!=null)
                    content+=val.ToString();
            }
            if(content.Length>0)
                return true;
            else
                return false;
        }

        /**//// <summary>
        /// 加入新工作簿
        /// </summary>
        /// <param name="fileName"></param>
        private void addWorksheet(string fileName)
        {
            ExcelApplication myExcel=new ExcelApplication();
            myExcel.Workbooks.Open(fileName,missing,missing,missing,missing,missing,missing,missing,missing,
                    missing,missing,missing,missing,missing,missing);
            int count=myExcel.Worksheets.Count+1;
            for(int i=1;i<count;i++)
            {
                Worksheet sheet=(Worksheet)myExcel.Worksheets.get_Item(i);
                if(hasContent(sheet))
                {
                    Range range=sheet.get_Range("A1","Z200");
                    object[,] vals=(object[,])range.get_Value(missing);
                    string sheetName=(string)sheet.Name;
                    
                    int sheetNo=sheetCount();
                    excel.Sheets.Add(missing,excel.Worksheets.get_Item(sheetNo),missing,missing);
                                    
                    Worksheet newSheet=(Worksheet)excel.Worksheets.get_Item(sheetNo+1);
                    if(hasDupliacteName(sheetName))
                        sheetName=sheetName+"-1";
                    newSheet.Name=sheetName;
                    range=newSheet.get_Range("A1","Z200");
                    range.Value2=vals;
                }
            }
            myExcel.Quit();
        }

        
        /**//// <summary>
        /// 检查现有工作簿中是否有重名工作表存在
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        private bool hasDupliacteName(string sheetName)
        {
            int count=excel.Worksheets.Count+1;
            for(int i=1;i<count;i++)
            {
                Worksheet sheet=(Worksheet)excel.Worksheets.get_Item(i);
                if(sheet.Name==sheetName)
                    return true;
            }
            return false;
        }

        /**//// <summary>
        /// 返回现有工作簿中有内容的工作表数量
        /// </summary>
        /// <returns></returns>
        private int sheetCount()
        {
            int count=excel.Worksheets.Count+1;
            int retval=0;
            for(int i=1;i<count;i++)
            {
                if(hasContent((Worksheet)excel.Worksheets.get_Item(i)))
                    retval++;
            }
            return retval;
        }

    #endregion

        /**//// <summary> 
        /// 清理所有正在使用的资源。
        /// </summary>
        protected override void Dispose( bool disposing )
        {
            if( disposing )
            {
                if(components != null)
                {
                    releaseExcel();
                    components.Dispose();
                }
            }
            base.Dispose( disposing );
        }

    

        组件设计器生成的代码#region 组件设计器生成的代码
        /**//// <summary> 
        /// 设计器支持所需的方法 - 不要使用代码编辑器 
        /// 修改此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {
            System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(ExcelControl));
            this.axWebBrowser1 = new AxSHDocVw.AxWebBrowser();
            ((System.ComponentModel.ISupportInitialize)(this.axWebBrowser1)).BeginInit();
            this.SuspendLayout();
            // 
            // axWebBrowser1
            // 
            this.axWebBrowser1.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom) 
                | System.Windows.Forms.AnchorStyles.Left) 
                | System.Windows.Forms.AnchorStyles.Right)));
            this.axWebBrowser1.Enabled = true;
            this.axWebBrowser1.Location = new System.Drawing.Point(0, 0);
            this.axWebBrowser1.OcxState = ((System.Windows.Forms.AxHost.State)(resources.GetObject("axWebBrowser1.OcxState")));
            this.axWebBrowser1.Size = new System.Drawing.Size(400, 300);
            this.axWebBrowser1.TabIndex = 0;
            this.axWebBrowser1.NavigateComplete2 += new AxSHDocVw.DWebBrowserEvents2_NavigateComplete2EventHandler(this.axWebBrowser1_NavigateComplete2);
            // 
            // ExcelControl
            // 
            this.Controls.Add(this.axWebBrowser1);
            this.Name = "ExcelControl";
            this.Size = new System.Drawing.Size(400, 300);
            ((System.ComponentModel.ISupportInitialize)(this.axWebBrowser1)).EndInit();
            this.ResumeLayout(false);

        }
        #endregion

        公有方法#region 公有方法

        public void Open(string fileName)
        {
            try
            {
                axWebBrowser1.Navigate(fileName,ref missing,ref missing,ref missing,ref missing);
                //axWebBrowser1.ExecWB(SHDocVw.OLECMDID.OLECMDID_HIDETOOLBARS,SHDocVw.OLECMDEXECOPT.OLECMDEXECOPT_DONTPROMPTUSER,ref missing,ref missing);
            }
            catch
            {
                throw new ApplicationException("文件不存在");
            }

        }
    
        
        public void Save(string fileName)
        {
            Worksheet worksheet=(Worksheet)excel.ActiveSheet;
            worksheet.SaveAs(fileName,missing,missing,missing,missing,missing,missing,missing,missing,missing);
        }
 
        /**//// <summary>
        /// 把已有文件的内容插入到工作表中
        /// </summary>
        /// <param name="fileName"></param>
        public void Insert(string fileName)
        {
            try
            {
                addWorksheet(fileName);  
//                Range range=((Worksheet)excel.ActiveSheet).get_Range("A1",missing);
//                range.Select();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
       
        #endregion
    }
}