C# 读取excel到datagridview

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;//解决问题：找不着类型或命名空间名称“Excel”
//要在引用中添加microsoft excel 11.0(或其他版本)object library,项目-添加引用-com。
//窗体中添加的控件：菜单控件，datagridview控件，openfiledialog，
//XP,VS2008
//借鉴《突破C#编程实例五十讲》 刘杨
namespace ExcelBrower1._0
{
    public partial class Form1 : Form
    {
        private Excel.Application ExcelObj = null;
        public Form1()
        {
            InitializeComponent();
            ExcelObj = new Excel.Application();
            //看一下对象是否成功生成
            if (ExcelObj == null)
            {
                MessageBox.Show("error!");
                System.Windows.Forms.Application.Exit();
            }
            ExcelObj.Visible = true;//使程序可见。？？什么意思？
        }

        //method
        public void OpenExcel(string pFilename)
        {
            object pobject = System.Reflection.Missing.Value;
            //可用这个来补足不知道的参数，以凑齐参数的个数
            //如下面的open最后的两个参数，不知道的话可以用pobject补足

 

            Excel.Workbook theworkbook = ExcelObj.Workbooks.Open(pFilename, pobject, pobject,
                pobject, pobject, pobject, pobject, pobject, pobject, pobject, pobject,
                pobject, pobject, pobject, pobject);
                
            /*
             * excel.workbooks.open参数说明：
             * filename
             * updatelinks:文件中链接的更新方式。0代表不更新任何引用
             * readonly：
             * format：如果打开的是文本文件，设置分隔字符。5代表没有分隔符
             * password
             * writerespassword：写入权密码
             * ignorereadonlyRecommended：是否忽略建议只读
             * origin：如果该文件为文本文件，则该参数用于指示该文件的来源于何种操作系统
             * delimiter：（若是文本文件且format=6）用于指定作用分隔符的类型
             * editable
             * notify
             * converter
             * addToMRU是否将工作薄添加到最近使用文件列表
             * LOCAL
             * corruptload
             */

            Excel.Sheets psheets = theworkbook.Worksheets ;
           
                      
                //get the sheet of the index=1
            Excel._Worksheet mySheet = (Excel._Worksheet)psheets.get_Item(1);
          
               
                //获取有记录单元格的范围
                int rowNum = mySheet.UsedRange.Cells.Rows.Count;
                int colummNum = mySheet.UsedRange.Cells.Columns.Count;
                string cellStr = null;
                char ch = 'A';
                for (int j = 0; j < colummNum; j++)
                {
                    dataGridView1.Columns.Add(j.ToString(), ch .ToString());
                    dataGridView1.Rows.Add(rowNum);
                   
                    for (int k = 0; k < rowNum; k++)
                    {
                       
                        cellStr = ch.ToString() + (k+1).ToString();
                        //MessageBox.Show (cellStr);

                        dataGridView1[j, k].Value = mySheet.Cells.get_Range(cellStr, pobject).Text.ToString();
                       
                    }
                    ch++;
                }

           

        }
                private void openExcelFileToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.openFileDialog1.FileName = "*.xls";
            string strFilename;
            if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                strFilename = this.openFileDialog1.FileName;
                OpenExcel(strFilename);
            }
        }
    }
}