form1.cs

来自「C# EXECl的读取方法,SHEET名的读取,SHEET名选择,一览数据的变更」· CS 代码 · 共 78 行

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;   
using System.Data.OleDb;


namespace xml
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();              
        }
        string strPath = "";
        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog openfile = new OpenFileDialog();
            openfile.Filter = "岺嶌敄(*.xls)|*.xls|強桳(*.*)|*.*";
            if (openfile.FilterIndex == 1 && openfile.ShowDialog() == DialogResult.OK)
                ExcelToDS(openfile.FileName);
            strPath = openfile.FileName;
        }

        public DataTable ExcelToDS(string path)
        {

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + @path   + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();

            DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return null;
            }
            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;

            // Add the sheet name to the string array.
            foreach (DataRow row in dt.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString().Replace("$","");
                comboBox1.DisplayMember = excelSheets[i];
                comboBox1.ValueMember = row["TABLE_NAME"].ToString();
                i++;
            }
            comboBox1.DataSource = excelSheets;          

            return dt;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + @strPath + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();

            string sheetName = comboBox1.ValueMember.ToString();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select * from [" + sheetName + "]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            System.Data.DataTable table2 = new System.Data.DataTable();
            ds = new DataSet();
            myCommand.Fill(table2);
            dataGridView1.DataSource = table2;

        }

    }
}