excel 列名
大家好！请高手帮个忙！
现在情况是这样的:
有一个上传文件的控件，点击浏览可以选择上传的文件，点击上传可以把选择的文件上传到指定的文件夹，同时把excel的列名显示在listbox里，目前基本功能已经实现，代码如下：
上传：
if(fileup.PostedFile !=null)
{
string fileName=fileup.PostedFile.FileName ;
string sheetName="sheet1";
DataSet ds = ExcelToDS(fileName,sheetName);
foreach(DataColumn c in ds.Tables[0].Columns)
{
ListItem item = new ListItem(c.ColumnName, c.ColumnName);
ListBox1.Items.Add(item);
}
}
有个函数：
public DataSet ExcelToDS(string filePath,string sheetName)
{
string strConn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+filePath+";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
OleDbConnection myConn=new OleDbConnection(strConn);
OleDbDataAdapter myDa=new OleDbDataAdapter("select * from ["+sheetName+"$]",myConn);
DataSet ds=new DataSet();
DataTable dt=new DataTable();
myDa.Fill(dt);
myDa.Fill(ds) ;
myConn.Close();
return ds;
}
存在这样一个问题，一个excel文件可能包括很多表，表的名字也不一定都是默认的sheet1... ,现在的代码只能显示sheet1表的字段，怎么改一下可以把所有表的字段都显示出来？

[解决办法]
Dictionary<string, DataTable> dicEccelDate = new Dictionary<string,DataTable>();

OleDbConnection myConn=new OleDbConnection(strConn);
myConn.Open();
DataTable dtSheet = objMyOleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });//读Excel Sheet

if (dtSheet != null && dtSheet.Rows.Count > 0)
{
for (int i = 0; i < dtSheet.Rows.Count; i++)
{
objMyDataSet = new DataSet();
strSheetName = dtSheet.Rows[i]["TABLE_NAME"].ToString();
OleDbDataAdapter objMyDataAdapter = new OleDbDataAdapter("SELECT * FROM [" + strSheetName + "]", objMyOleDbConnection);
objMyDataAdapter.Fill(objMyDataSet, "MyData");
dicEccelDate.Add(strSheetName, objMyDataSet.Tables[0]);
}
objMyOleDbConnection.Close();
}
return dicEccelDate;

这样就可以读出全部的 Sheet了

[解决办法]

C# code

/// <summary>        /// 从Excel中获取数据.(无须指定表名，返回的DataSet包含此Excel中所有的表).        /// </summary>        /// <param name="filePath">Excel物理路径.</param>        /// <returns>DataSet:Excel中表的记录集合.</returns>        public DataSet ReadExcle(string filePath, out string msg)        {            string strSql = string.Empty;            msg = string.Empty;            DataSet ds = new DataSet();            DataTable dt = null;            OleDbDataAdapter adapter=null;            string strTableName = string.Empty;            if (filePath != string.Empty)            {                try                {                    //判断是否为Xls文件.                    if (!filePath.Substring(filePath.Length - 3, 3).Equals("xls"))                    {                        throw new Exception(ImportConst.ErrorMessage_NoExcelFile);                    }                    OleDbConnection conn = new OleDbConnection(string.Format(strConnectionString, filePath));                    conn.Open();                    //获取XML文件Schema信息.                    DataTable dtschema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);                    if (dtschema != null && dtschema.Rows.Count > 0)                    {                        //遍历Excel中所有的表                        foreach (DataRow row in dtschema.Rows)                        {                            strTableName = row[2].ToString().Trim(new char[] { '\'' });                            if (IsTable(strTableName))                            {                                dt = new DataTable(strTableName.Replace('$',' '));                                strSql = string.Format("select * from [{0}]", strTableName);                                adapter = new OleDbDataAdapter(strSql, conn);                                adapter.Fill(dt);                                ds.Tables.Add(dt);                            }                        }                    }                    conn.Close();                }                catch (Exception Ex)                {                    msg += Ex.Message + "$";                }            }            else            {                msg += ImportConst.ErrorMessage_NullPath + "$";            }            return ds;        }        /// <summary>        /// 判定是否为Excel中包含记录的表.(排除掉打印区域等表格.e.g xx$print Area)        /// </summary>        /// <param name="tableName">Excel中获取到的表名.</param>        /// <returns></returns>        protected bool IsTable(string tableName)        {            bool blIsTable = false;            if (tableName != string.Empty)            {                int nIndex = tableName.LastIndexOf("$");                if (nIndex!=-1&&tableName.Length-1==nIndex)                {                    blIsTable = true;                }            }            return blIsTable;        }