Excel编程篇之多个excel表导入到Dataset

public class ExcelToDataset { /// <summary> /// 导入Excel /// </summary> /// <param name="filePath"></param> /// <param name="dsSet"></param> /// <returns></returns> public static DataSet Excelreader(string filePath) { DataSet ds = new DataSet(); string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + filePath; OleDbConnection myConn = new OleDbConnection(strCon); string strCom = GetExcelFirstTableName(filePath); myConn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); myCommand.Fill(ds); myConn.Close(); return ds; } /// <summary> /// 返回Excel数据源 /// </summary> /// <param name="filePath">文件路径</param> /// <param name="dsSet">数据集</param> /// <returns>DataSet</returns> public static DataSet InSertAnotherExcelToDataset(string filePath, DataSet dsSet) { DataSet ds=dsSet; string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + filePath; OleDbConnection myConn = new OleDbConnection(strCon); string strCom = GetExcelFirstTableName(filePath); myConn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); myCommand.Fill(ds); myConn.Close(); return ds; } /// <summary> /// 动态取Excel表名 /// </summary> /// <param name="fullPath">文件路径</param> /// <returns></returns> public static string GetExcelFirstTableName(string fullPath) { string tableName = null; if (File.Exists(fullPath)) { using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet." + "OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + fullPath)) { conn.Open(); var oleDbSchemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (oleDbSchemaTable != null) tableName = oleDbSchemaTable.Rows[0][2].ToString().Trim(); } } tableName="SELECT * FROM [" + tableName + "]"; return tableName; } }

注意分析逻辑，遇到问题再仔细分析，切忌浮躁~相信前辈的Blog~