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了
[解决办法]
/// <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; }