C#如何从Excel中读取数据到DataTable中？(听说标题长，回复率高……-__-)

Question

如题,偶自己的代码:private void button1_Click(object sender, EventArgs e)
        {
            BindingSource DataBinding = new BindingSource(this.SampleDataTable,null);
            this.dataGridView1.DataSource = DataBinding;
        }
 
        private int GetExcelCon()
        {
            string connection = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = C:\\Documents and Settings\\yangjh\\桌面\\sample.xls;Extended Properties=Excel 8.0";
            OleDbConnection thisconnection = new OleDbConnection(connection);
            string command = "select * from [sheet1$]";
            thisconnection.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(command, thisconnection);
            DataSet ds = new DataSet();
            adapter.Fill(ds, "[sheet1$]");
            thisconnection.Close();
            foreach (DataRow Row in ds.Tables[0].Rows)
            {
                /*疑难所在：我只是想将这个DataTable的0列数据依次传递到存储过程"t_StoreAndSale"中的参数
                            @FFullNumber中，根据返回的行，构造成一个N行的DataTable,然后BindingSource到
                            DataGridView中,下面这段语句不知道该如何写了，请高手指点，或代码演示*/
                //int RowCount=ds.Tables[0].Rows.Count;
            }
            //?return ds.Tables[0].Columns[0]; 
        }
      
        private DataTable SampleDataTable
        {
            get
            {
                string connection = "Server=192.100.3.3;Datasource=AIS20090330165131;uid=sa;pwd=;";
                SqlConnection thisconntion = new SqlConnection(connection);
                SqlCommand cmd = thisconntion.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "t_StoreAndSale";
                cmd.CommandTimeout = 0;
                SqlParameter parameter = new SqlParameter("@FFullNumber", SqlDbType.VarChar, 40);
                parameter.Direction = ParameterDirection.ReturnValue;
                parameter.Value = GetExcelCon();
                cmd.Parameters.Add(parameter);
                SqlDataAdapter Adapter = new SqlDataAdapter();
                Adapter.SelectCommand = cmd;
                DataSet ds = new DataSet();
                Adapter.Fill(ds);
                return ds.Tables[0];
            }
        }

Answer 1

        //读Excel到DataGridView
        private void RedExcel()
        {
            string sPath = "F:\\excel名称.xls";
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";             string strSQL = "SELECT * FROM [sheet1$]";
            OleDbConnection excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open();             OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);            DataTable dTable = new DataTable();            dataAdapter.Fill(dTable);            dataGridView1.DataSource = dTable;            dTable.Dispose();
            dataAdapter.Dispose();
            dbCommand.Dispose();
            excelConnection.Close();
            excelConnection.Dispose();
        }
上面代码就可以读取了

Answer 2

 /// 数据从Excel到DataSet
        /// </summary>
        /// <param name="ds">DataSet</param>
        public clsExcelReport(DataSet ds)
        {
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Title = "导入 Microsoft Excel Document";
            dlg.Filter = "Microsoft Excel|*.xls";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    fileName = dlg.FileName;
                    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + dlg.FileName + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
                    using (OleDbConnection conn = new OleDbConnection(strConn))
                    {
                        conn.Open();
                        System.Data.DataTable tables = conn.GetSchema("Tables");
                        string ss = "select * from [" + tables.Rows[0]["Table_Name"].ToString() + "]";
                        using (OleDbDataAdapter myCommand = new OleDbDataAdapter(ss, conn))
                        {
                            myCommand.Fill(ds);
                        }
                    }
                }
                catch (Exception ex)
                {
                    //MessageBox.Show(ex.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    CDataCenter.runInfo = ex.ToString();//TODO:李明20091021
                    
                }
            }
        }

Answer 3

是可以读取了，但是传递相关的数据到存储过程“t_StoreAndSale”中偶代码也可以修改成2楼那样，可能2楼还未仔细看明白需求

Answer 4

3楼的代码也可以这样从Excel中绑定到DataSet了，但偶只想要这个Excel中的某一列，作为参数传递到存储过程"t_StoreAndSale"，根据存储过程所返回的行，构造一个复杂的N行的DataTable,再将此DataTable传递到DataGridView控件中

Answer 5

是参数传递的问题，不清楚
 string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;";   
            strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;";   
            strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";   
            OleDbConnection objConnection = new OleDbConnection(strConnection);   
            objConnection.Open();   
            OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection);   
            DataSet ds = new DataSet();   
            myCommandd.Fill(ds, "[Sheet1$]");   
            System.Data.DataTable dt = ds.Tables["[Sheet1$]"];   
            Console.WriteLine(dt.Columns[0].ToString());   
            Console.WriteLine(dt.Columns[1].ToString());   
            DataRow drDisplay = dt.Rows[0];   
            int[] num = new int[dt.Columns.Count];   
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/LCL_data/archive/2009/05/06/4154784.aspx

Answer 6

说错，修正是1楼和2楼的代码只是将整个Excel表中的数据读取到Excel中，至于下一步操作，是将该DataSet所绑定的DataTable中的第0列数据依次传入存储过程“t_StoreAndSale”中，根据该存储过程中所返回的值，再次绑定另一DataTable中，将该表传递到DataGridView中

Answer 7

存储过程好像不能用datatable来作参数吧

Answer 8

你是问存储过程怎么弄的问题吧        public static DataSet ExecPrc_库存明细_布料_按订单(string date, string dd, string no, string color, string sehao, string guige, string chengfen, string kezhong, string bufeng)
        {
            SqlDataAdapter adp = new SqlDataAdapter();
            adp.SelectCommand = new SqlCommand("库存明细_布料_按订单", clsSql.cn);
            adp.SelectCommand.CommandType = CommandType.StoredProcedure;            SqlParameter 日期 = adp.SelectCommand.Parameters.Add("@日期", SqlDbType.VarChar, 50);
            日期.Direction = ParameterDirection.Input;            SqlParameter 订单编号 = adp.SelectCommand.Parameters.Add("@订单编号", SqlDbType.VarChar, 50);
            订单编号.Direction = ParameterDirection.Input;            SqlParameter 物料编号 = adp.SelectCommand.Parameters.Add("@物料编号", SqlDbType.VarChar, 50);
            物料编号.Direction = ParameterDirection.Input;            SqlParameter 颜色 = adp.SelectCommand.Parameters.Add("@颜色", SqlDbType.VarChar, 50);
            颜色.Direction = ParameterDirection.Input;            SqlParameter 色号 = adp.SelectCommand.Parameters.Add("@色号", SqlDbType.VarChar, 50);
            色号.Direction = ParameterDirection.Input;            SqlParameter 规格 = adp.SelectCommand.Parameters.Add("@规格", SqlDbType.VarChar, 50);
            规格.Direction = ParameterDirection.Input;            SqlParameter 成份 = adp.SelectCommand.Parameters.Add("@成份", SqlDbType.VarChar, 50);
            成份.Direction = ParameterDirection.Input;            SqlParameter 克重 = adp.SelectCommand.Parameters.Add("@克重", SqlDbType.VarChar, 50);
            克重.Direction = ParameterDirection.Input;            SqlParameter 布封 = adp.SelectCommand.Parameters.Add("@布封", SqlDbType.VarChar, 50);
            布封.Direction = ParameterDirection.Input;            日期.Value = date;
            订单编号.Value = dd;
            物料编号.Value = no;
            颜色.Value = color;
            色号.Value = sehao;
            规格.Value = guige;
            成份.Value = chengfen;
            克重.Value = kezhong;
            布封.Value = bufeng;            DataSet ds = new DataSet();            adp.Fill(ds);            return ds;
        }
你参考

Answer 9

parameter.Value = GetExcelCon();Value是一个object对象

Answer 10

9楼的代码是通过某一输入值来传递参数的吧，比如说textBox传递的值但是偶现在要的这个传递的值是Excel中某一列的数据，一列中有N个物料号，通过这些物料号不断的调用存储过程，这样就构造一个复杂的N行数据了，绑定这N行到一个DataTable表，用BindingSource到DataGridView中，再次导出，这样就是一个全新的Excel表了```

Answer 11

那你也可以先写一个如同我贴的那样的方法,然后循环你读EXCEL得到的dataset,将要传递的参数传到存储过程循环执行啊

Answer 12

我顺便写下存储过程方法public static DataTable ExecPrc(string 传的参数)//这里参数可以是多个,看你的需求了
{
//这里我直接拷贝你的代码了
string connection = "Server=192.100.3.3;Datasource=AIS20090330165131;uid=sa;pwd=;";
                SqlConnection thisconntion = new SqlConnection(connection);
                SqlCommand cmd = thisconntion.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "t_StoreAndSale";
                cmd.CommandTimeout = 0;                //...将你要传进存储过程的参数安装我9楼的办法传进去                cmd.Parameters.Add(parameter);
                SqlDataAdapter Adapter = new SqlDataAdapter();
                Adapter.SelectCommand = cmd;
                DataSet ds = new DataSet();
                Adapter.Fill(ds);
                return ds.Tables[0];
}
循环//读excel得到dataset后
for(int i=0;i<dataset.Table[0].Rows.Count;i++)
{
     //ExecPrc();这里调用那个存储过程方法,将要传进去的参数传进去
      //处理你得到的datatable也在这里
}

Answer 13

 List<string> parms = new List<string>();
            DataSet dt = new DataSet();
            foreach (DataRow item in dt.Tables[0].Rows)
            {
                parms.Add(item[0].ToString());
            }

Answer 14

     public String[] GetTableNames(string fileName)
        {
            string strconn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Extended Properties=excel 8.0;";//HDR=Yes;IMEX=1
            OleDbConnection conn = null;
            try
            {
                conn = new OleDbConnection(strconn);
                conn.Open();
                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                List<String> names = new List<string>();
                foreach (DataRow dr in schemaTable.Rows)
                {
                    names.Add(dr["TABLE_NAME"].ToString().Replace("''","'"));
                }
                return names.ToArray();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }        }
        /// <summary>
        /// 从一个Excel中读取一个Table
        /// Table必须是标准的
        /// </summary>
        /// <param name="file"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public DataTable TableRead(string fileName, string tableName)
        {
            // TODO:  Add ImpExcel.ReadExcel implementation
            string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";//修改读取数丢失的BUG
            OleDbConnection conn = null;
            try
            {
                conn = new OleDbConnection(strconn);
                conn.Open();
                if (tableName.IndexOf("$")==-1)
                {
                    tableName = tableName + "$";
                }
                string sql = "select * from [" + tableName + "]";
                OleDbDataAdapter aper = new OleDbDataAdapter(sql, conn);
                DataSet myset = new DataSet();
                aper.Fill(myset, tableName);
                return myset.Tables[0];
            }
            catch (Exception ex)
            {
                if (ex.Message.Contains("is not a valid name"))
                    throw new DBException(Resources.FileReadWriteResource.MSGIsnotfind + tableName, ex);
                else
                    throw new DBException(Resources.FileReadWriteResource.MSGReadexcelexception, ex);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
        }        public string FilterIParameterBadWord(string str)
        {
            return str.Replace("(", "_").Replace(")", "_");
        }        public DbType GetDbType(Type _type)
        {
            try
            {
                DbType dtype = (DbType)Enum.Parse(typeof(DbType), _type.Name);
                return dtype;
            }
            catch
            {
                return DbType.String;
            }
        }
        /// <summary>
        /// 把一个DataTable写入到一个Excel Sheet中。
        /// Sheet中必须包含对应的Column.
        /// </summary>
        /// <param name="file"></param>
        /// <param name="desTable"></param>
        /// <param name="sheetName"></param>
        public void WriteExcel(string file, DataTable desTable, string sheetName)
        {
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + "; Extended Properties=excel 8.0";
            OleDbConnection objConn = null;
            try
            {
                objConn = new OleDbConnection(connString);
                objConn.Open();
                OleDbCommand objCmd = new OleDbCommand();
                objCmd.Connection = objConn;                objCmd.Parameters.Clear();
                string sqlString = "INSERT INTO [" + sheetName + "$]({0}) values ({1})";                string fieldString = "";
                string valueString = "";
                for (int i = 0; i < desTable.Columns.Count; i++)
                {
                    if (fieldString != "")
                    {
                        fieldString += ",";
                        valueString += ",";
                    }
                    fieldString += "[" + desTable.Columns[i].ColumnName + "]";
                    valueString += "@" + FilterIParameterBadWord(desTable.Columns[i].ColumnName) + "";
                    OleDbParameter op = new OleDbParameter();
                    op.ParameterName = "@" + FilterIParameterBadWord(desTable.Columns[i].ColumnName) + "";
                    op.DbType = GetDbType(desTable.Columns[i].DataType);
                    objCmd.Parameters.Add(op);
                }                sqlString = string.Format(sqlString, fieldString, valueString);
                objCmd.CommandText = sqlString;                //遍历DataSet将数据插入新建的Excel文件中
                foreach (DataRow row in desTable.Rows)
                {
                    for (int i = 0; i < desTable.Columns.Count; i++)
                    {
                        objCmd.Parameters["@" + FilterIParameterBadWord(desTable.Columns[i].ColumnName) + ""].Value = row[desTable.Columns[i].ColumnName];
                    }
                    objCmd.ExecuteNonQuery();
                }
                objCmd.Connection.Close();
            }
            catch (Exception ex)
            {
                throw new DBException(Resources.FileReadWriteResource.MSGWritetoexcelexception, ex);
            }
            finally
            {
                if (objConn != null)
                    objConn.Close();
            }
        }

Answer 15

参考http://blog.csdn.net/xuenzhen123/archive/2009/10/22/4713019.aspx
一个很好用的Excel操作类~

Answer 16

测试了N久，楼上的几种方法大概都是一种思路，只是将sheet表的数据做成DataTable，返回这个DataTable就完事了，而没有去做到把这个DataTable表的某一列作为参数传递到存储过程，调用存储过程做成另一个复杂的Table表……

Answer 17

//读excel得到dataset后
for(int i=0;i<dataset.Table[0].Rows.Count;i++)
{
     ExecPrc(dataset.Table[0].Rows[i].Cells[0].ToString());
     //这里调用那个存储过程方法,将要传进去的参数传进去
      //处理你得到的datatable也在这里
}
晕了,我上面代码不是提示你了么,你在取得excel的datatable后,你再循环那个datatable然后将你需要的某一列的值传进去!

Answer 18

一个封装好的EXCEL导入导出类，可以直接调用
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using Excel;namespace ImportExportToExcel
{
    public class ImportExportToExcel
    {
        private string strConn ;
        
        private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
        private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();      
        
        public ImportExportToExcel()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            this.openFileDlg.DefaultExt = "xls";
            this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";            this.saveFileDlg.DefaultExt="xls";
            this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";        }        
        #region 从Excel文件导入到DataSet
        //        /// <summary>
        //        /// 从Excel导入文件
        //        /// </summary>
        //        /// <param name="strExcelFileName">Excel文件名</param>
        //        /// <returns>返回DataSet</returns>
        //        public DataSet ImportFromExcel(string strExcelFileName)
        //        {
        //            return doImport(strExcelFileName);
        //        }
        /**//// <summary>
        /// 从选择的Excel文件导入
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ImportFromExcel()
        {
            DataSet ds=new DataSet();
            if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                ds=doImport(openFileDlg.FileName);
            return ds;
        }
        /**//// <summary>
        /// 从指定的Excel文件导入
        /// </summary>
        /// <param name="strFileName">Excel文件名</param>
        /// <returns></returns>
        public DataSet ImportFromExcel(string strFileName)
        {
            DataSet ds=new DataSet();
            ds=doImport(strFileName);
            return ds;
        }
        /**//// <summary>
        /// 执行导入
        /// </summary>
        /// <param name="strFileName">文件名</param>
        /// <returns>DataSet</returns>
        private DataSet doImport(string strFileName)
        {
            if (strFileName=="") return null;
              
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + strFileName + ";" +
                "Extended Properties=Excel 8.0;";
            OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);            DataSet ExcelDs = new DataSet();
            try
            {
                ExcelDA.Fill(ExcelDs, "ExcelInfo");
                
            }
            catch(Exception err)
            {
                System.Console.WriteLine( err.ToString() );
            }
            return ExcelDs;
            
            
        
        }
        #endregion        #region 从DataSet到出到Excel
        /**//// <summary>
        /// 导出指定的Excel文件
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的Excel文件名</param>
        public void ExportToExcel(DataSet ds,string strExcelFileName)
        {
            if (ds.Tables.Count==0 || strExcelFileName=="") return;
            doExport(ds,strExcelFileName);
           }
        /**//// <summary>
        /// 导出用户选择的Excel文件
        /// </summary>
        /// <param name="ds">DataSet</param>
        public void ExportToExcel(DataSet ds)
        {
            if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                doExport(ds,saveFileDlg.FileName);
            
        }
        /**//// <summary>
        /// 执行导出
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的文件名</param>
        private void doExport(DataSet ds,string strExcelFileName)
        {
            
            Excel.Application excel= new Excel.Application();
            
            //            Excel.Workbook obj=new Excel.WorkbookClass();
            //            obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);            int rowIndex=1;
            int colIndex=0;            excel.Application.Workbooks.Add(true);
            
    
            System.Data.DataTable table=ds.Tables[0] ;
            foreach(DataColumn col in table.Columns)
            {
                colIndex++;    
                excel.Cells[1,colIndex]=col.ColumnName;                
            }            foreach(DataRow row in table.Rows)
            {
                rowIndex++;
                colIndex=0;
                foreach(DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
                }
            }
            excel.Visible=false;
            
            excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS",
                Excel.XlFileFormat.xlExcel9795, null, null, false,
                false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null,null);
            
            
            //wkbNew.SaveAs strBookName
            //excel.Save(strExcelFileName);
            excel.Quit();
            excel=null;
            
            GC.Collect();//垃圾回收
        }
        #endregion        #region 从XML导入到Dataset        /**//// <summary>
        /// 从选择的XML文件导入
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ImportFromXML()
        {
            DataSet ds=new DataSet();
            System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
            openFileDlg.DefaultExt="xml";
            openFileDlg.Filter= "xml文件 (*.xml)|*.xml";
            if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                try{ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);}
                catch{}
            return ds;
        }
        /**//// <summary>
        /// 从指定的XML文件导入
        /// </summary>
        /// <param name="strFileName">XML文件名</param>
        /// <returns></returns>
        public DataSet ImportFromXML(string strFileName)
        {
            if (strFileName=="")
                return null;
            DataSet ds=new DataSet();
            try{ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);}
            catch{}
            return ds;
        }
        
        #endregion        #region 从DataSet导出到XML
        /**//// <summary>
        /// 导出指定的XML文件
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strXMLFileName">要导出的XML文件名</param>
        public void ExportToXML(DataSet ds,string strXMLFileName)
        {
            if (ds.Tables.Count==0 || strXMLFileName=="") return;
            doExportXML(ds,strXMLFileName);
        }
        /**//// <summary>
        /// 导出用户选择的XML文件
        /// </summary>
        /// <param name="ds">DataSet</param>
        public void ExportToXML(DataSet ds)
        {
            System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog(); 
            saveFileDlg.DefaultExt="xml";
            saveFileDlg.Filter= "xml文件 (*.xml)|*.xml";
            if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                doExportXML(ds,saveFileDlg.FileName);
        }        /**//// <summary>
        /// 执行导出
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的XML文件名</param>
        private void doExportXML(DataSet ds,string strXMLFileName)
        {
            try
            {ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );}
            catch(Exception ex)
            {System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;}    
        }        #endregion 
    }}

Answer 19

up!! 还没有弄过这样的 O(∩_∩)O~ 学习！！！！！

Answer 20

 如果你是要传递一列数据的,那这让你失望了.存储过程是不能传递数组row或Column或table等.你可将excel导入到数据库的中,然后再用select行成table.如果你的excel文件与数据库同在一台计算机上,还可用OPENQUERY或OPENDATASOURCE来完成.

Answer 21

感谢各位这么积极的回帖，总的来说如六月的思路，偶自己也努力测试，用MyNewTable.Merge(),将Table不断的合并就可以达到预期的效果，代码如下：using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
 
namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            
            using (OpenFileDialog dlg = new OpenFileDialog())
            {
                dlg.Filter = "Excel files (*.xls)|*.xls";
                dlg.Multiselect = false;
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    string FilePath = dlg.FileName;
                    BindingSource DataBinding = new BindingSource(this.fSampleDataTable(FilePath), null);
                    this.dataGridView1.DataSource = DataBinding;
                }
            }
        }
 
        private DataTable fSampleDataTable(string FilePath)
        {                              
            string connection = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+FilePath+";Extended Properties=Excel 8.0";
            OleDbConnection thisconnection = new OleDbConnection(connection);                     
            thisconnection.Open();
            DataTable schemaTable = thisconnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string table = schemaTable.Rows[0][2].ToString().Trim();//获取Excel的表名,默认值是sheet1
            string command = "select * from ["+table+"]";
            OleDbDataAdapter adapter = new OleDbDataAdapter(command, thisconnection);
            DataSet ds = new DataSet();
            adapter.Fill(ds, table);
            thisconnection.Close();
            DataTable MyNewTable = new DataTable();
            foreach (DataRow Row in ds.Tables[table].Rows)
            {
                if (Row[0].ToString().Trim().StartsWith("7"))
                {
                    MyNewTable.Merge(MyGetTableMethod("t_StoreAndSale", Row[0].ToString().Trim()));
                }
                /*else
                {
                    MyNewTable.Merge(MyGetTableMethod("t_AI", Row[0].ToString().Trim()));
                }*/
            }
            return MyNewTable;              
        }
         private DataTable MyGetTableMethod(string ProcName,string ProcParameterValue)
        {          
                string connection = "Server=192.100.3.3;Database=AIS20090330165131;uid=sa;pwd=;";
                SqlConnection thisconntion = new SqlConnection(connection);
                SqlCommand cmd = thisconntion.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = ProcName;
                cmd.CommandTimeout = 0;
                SqlParameter parameter = new SqlParameter("@FFullNumber", SqlDbType.VarChar, 40);
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = ProcParameterValue;
                cmd.Parameters.Add(parameter);
                SqlDataAdapter Adapter = new SqlDataAdapter();
                Adapter.SelectCommand = cmd;
                DataSet ds = new DataSet();
                Adapter.Fill(ds);
                return ds.Tables[0];
          
        }
        
    }
}

Answer 22

但是还是有点问题，就是选择导入的Excel，只能传递sheet1的第一列的值，而不能传递sheet2,sheet3,甚至更多的sheet,再者另一问题：就是导入的sheet1中的第1行0列的值不能通过存储过程调用出来……有心人帮忙测试一下

Answer 23

Excel似乎可以直接作为数据库，来处理

Answer 24

    但这是应用在客户端的，不可能客户端都要把这个Excel读入数据库吧，这样太费事了