怎么把Excel中所有Sheet中的数据查询出来再绑定到页面

Question

conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);可得到所有sheets

Answer 1

// 获取数据源的表定义元数据                        
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });详细见：
ADO.NET 如何读取 Excel （下）
http://www.cnblogs.com/Jinglecat/archive/2006/08/26/487167.html

Answer 2

conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)
string ExcelTable = dt.Rows[dtcount][2].ToString();

Answer 3

可以得到所有sheets，但怎么把所有的Sheets中数据查询出来，我试了是用循环语句，但不行，
 string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +this.FileUpload1.PostedFile.FileName+ ";Extended Properties=Excel 8.0";
        OleDbConnection conn = new OleDbConnection(ConnectionString);
        conn.Open();
        DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
        OleDbCommand comm = null;
        DataSet ds = null;
        OleDbDataReader dr = null;
 for (int i = 0; i < schemaTable.Rows.Count;i++ )
        {
              string sql = "SELECT * FROM ["+schemaTable.Rows[i]["TABLE_NAME"].ToString()+"]";
            comm = new OleDbCommand(sql, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(comm);
            ds = new DataSet();
            da.Fill(ds, "cctt");
             dr = comm.ExecuteReader();
           // dr = dr.ToString() + dr.ToString();
       }
        this.DataGrid1.DataSource = dr;
        DataGrid1.DataBind();
        conn.Close();
        conn.Dispose();
用这个循环语句查询出来的是最后一个Sheet的数据，

Answer 4

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 System.Data.OleDb;
using System.IO;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;namespace ExcelDataManage
{
    public partial class ExcelShowOfAA : Form
    {
        public ExcelShowOfAA()
        {
            InitializeComponent();
        }        string fileName;
        string connString;
        DataTable table = new DataTable();        #region 打开并选择数据源的方法
        private void btnExcelOfAA_Click(object sender, EventArgs e)
        {
            //打开Excel表格选择框
            OpenFileDialog ofd = new OpenFileDialog();
            //指定打开文件的类型
            ofd.Filter = "Microsoft Excel files (*.xls)|*.xls";            if (ofd.ShowDialog() == DialogResult.OK)
            {
                fileName = ofd.FileName;
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0'";
                OleDbConnection conn = new OleDbConnection(connString);//连接到指定的Excel文件                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }                string strSQL = "select distinct * from [外观入库实数$]";
                OleDbCommand command = new OleDbCommand(strSQL, conn);
                OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                adapter.Fill(table);
                bdSource.DataSource = table;
                dgvExcelOfAA.AutoGenerateColumns = false;
                dgvExcelOfAA.DataSource = table;
                adapter.Dispose();                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
        #endregion        #region 调用根据产品等级所查询的产品方法
        private void btnSaveOfAA_Click(object sender, EventArgs e)
        {
            //当DataGridView数据为空时，无法进行数据导出
            if (dgvExcelOfAA.Rows.Count == 1)
            {
                MessageBox.Show("对不起，查询表格中目前没有任何数据可以让你导出！", "友情提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            //当DataGridView数据不为空时，则执行数据导出
            else
            {
                DataGridviewShowToExcel(dgvExcelOfAA, true);
            }
        }
        #endregion        #region 导出根据产品等级所查询的产品
        public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
        {
            //建立Excel对象
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Visible = isShowExcle;            //生成字段名称
            for (int i = 0; i < dgv.ColumnCount; i++)
            {
                excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
            }            //填充数据到Excel表格
            for (int i = 0; i < dgv.RowCount - 1; i++)
            {
                for (int j = 0; j < dgv.ColumnCount; j++)
                {
                    if (dgv[j, i].ValueType == typeof(string))
                    {
                        try
                        {
                            excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
                        }
                        catch (Exception ex)
                        {
                            throw (ex);
                        }
                    }
                    else
                    {
                        excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
                    }
                }
            }
            return true;
        }
        #endregion