Excel asp.net

导入EXCEL

SELECT * INTO db_news.dbo.test FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:/工作/test.xls', [Sheet1$])

 

Excel 转Dataset

 

public DataSet ExcelToDS(string fileName) { string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + "Extended Properties=Excel 8.0;"; string sql_F = "Select * FROM [{0}]"; OleDbConnection conn = null; OleDbDataAdapter da = null; DataTable tblSchema = null; ArrayList tblNames = new ArrayList(); // 初始化连接，并打开 conn = new OleDbConnection(connStr); conn.Open(); // 获取数据源的表定义元数据 tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); foreach (DataRow row in tblSchema.Rows) { tblNames.Add((string)row["TABLE_NAME"]); // 读取表名 } // 初始化适配器 da = new OleDbDataAdapter(); // 准备数据，导入DataSet DataSet ds = new DataSet(); foreach (string tblName in tblNames) { da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblName), conn); try { da.Fill(ds, tblName); } catch { // 关闭连接 if (conn.State == ConnectionState.Open) { conn.Close(); } throw; } } // 关闭连接 if (conn.State == ConnectionState.Open) { conn.Close(); } return ds; }