菜鸟求助c#操作excel问题。
我用c#去读取excel时,那个excel有多个表。我怎么能够,指定要读取的表,比方说我要读第一个,或者是第二个。我是这么读的private static void ReadExcel()
{
DataTable ExcelTable;
DataSet ds = new DataSet();
//Excel的连接
OleDbConnection objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\2000火灾报警系统离线编程范本.xls;Extended Properties=Excel 8.0;");
objConn.Open();
DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[0][2].ToString().Trim();//获取 Excel 的表名,默认值是sheet1
string strSql = "select * from [" + tableName + "]";
OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
myData.Fill(ds, tableName);//填充数据
objConn.Close();
这样读到的是第二个表,我尝试了改schemaTable.Rows[0][2].ToString().Trim()中的2,但是不行啊,望高手指点下。谢谢了 --------------------编程问答--------------------
在Excel 中每一个表对应一个sheet会比较好处理。
--------------------编程问答-------------------- Excel中的表名默认情况下是Sheet1,Sheet2,Sheet3......,但如果不是这些又该如何读取呢,很简单,方法如下:
DataTable dtExcel = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string SpreadSheetName = "[" + dtExcel.Rows[0]["TABLE_NAME"].ToString() + "]";
其中objConn是Excel连接对象。 --------------------编程问答-------------------- OleDbConnection myOleConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False; data source=" + strFileName + "; Extended Properties='Excel 8.0;PersistSecurityInfo=False;HDR=YES;IMEX=no'");
myOleConn.Open();
string sql = "select * from [表名]";
OleDbDataAdapter MyCommand = new System.Data.OleDb.OleDbDataAdapter(sql, myOleConn);
DataSet ds = new System.Data.DataSet();
MyCommand.Fill(ds);
myOleConn.Close();
return ds.Tables[0]; --------------------编程问答--------------------
/// <summary>
/// 获得Excel文件所有sheet的名称
/// </summary>
/// <param name="Conn">Excel的OleDb连接</param>
/// <returns>sheet名称数组</returns>
private ArrayList GetExcelSheetName(OleDbConnection Conn) // 获得Excel文件所有sheet的名称
{
ArrayList result = new ArrayList();
DataTable sheetNames = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow dr in sheetNames.Rows)
{
result.Add(dr[2]);
}
return result;
}
/// <summary>
/// 读取Excel文件
/// </summary>
/// <param name="route">Excel文件的路径</param>
/// <returns>Excel文件中的数据</returns>
private DataSet ReadExcel(string route) // 读取Excel文件
{
string strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';", route);
OleDbConnection Conn = new OleDbConnection(strConn);
Conn.Open();
OleDbDataAdapter adp;
DataSet ds = new DataSet();
ArrayList sheetNames = GetExcelSheetName(Conn);
for (int i = 0; i < sheetNames.Count; i++)
{
adp = new OleDbDataAdapter(string.Format("Select * from [{0}]", sheetNames[i]), Conn);
adp.Fill(ds, sheetNames[i].ToString().Substring(0, sheetNames[i].ToString().Length - 1));
}
Conn.Close();
return ds;
}
补充:.NET技术 , C#