将Excel数据加载到GridView中

大家好，

我在这里发布一段代码，它将读取 Excel 文档。这段代码将遍历 Excel 电子表格的所有工作表，无论它们叫什么名字。

它使用 OLEDB 连接来读取 Excel 工作表。

using System.Data.OleDb;

protected void Page_Load(object sender, EventArgs e)
{
	GetExcelSheetNames("Path");
}

private void GetExcelSheetNames(string excelFile)
{
	OleDbConnection objConn = null;
	System.Data.DataTable dt = null;
	try
	{
		DataSet ds = new DataSet();
		// Connection String. 
		String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
		// Create connection. 
		objConn = new OleDbConnection(connString);
		// Opens connection with the database. 
		objConn.Open();
		// Get the data table containing the schema guid, and also sheet names. 
		dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
		if (dt == null)
		{
			return;
		}
		String[] excelSheets = new String[dt.Rows.Count];
		int i = 0;
		// Add the sheet name to the string array. 
		// And respective data will be put into dataset table 
		foreach (DataRow row in dt.Rows)
		{
			excelSheets[i] = row["TABLE_NAME"].ToString();
			OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + excelSheets[i] + "]", objConn);
			OleDbDataAdapter oleda = new OleDbDataAdapter();
			oleda.SelectCommand = cmd;
			oleda.Fill(ds, "TABLE");
			i++;
		}
		// Bind the data to the GridView 
		GridView1.DataSource = ds.Tables[0].DefaultView;
		GridView1.DataBind();
		Session["Table"] = ds.Tables[0];
	}
	catch (Exception ex)
	{
		Response.Write(ex.Message);
		
	}
	finally
	{
		// Clean up. 
		if (objConn != null)
		{
			objConn.Close();
			objConn.Dispose();
		}
		if (dt != null)
		{
			dt.Dispose();
		}
	}
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
	GridView1.PageIndex = e.NewPageIndex;
	GridView1.DataSource = ((DataTable)Session["Table"]).DefaultView;
	GridView1.DataBind();
}

 

在 .aspx 文件中

<asp:GridView ID="GridView1" 
              runat="server" 
              AllowPaging="true" 
              PagerSettings-Mode="Numeric" 
              PagerSettings-Position="Bottom" 
              PagerStyle-Font-Size="Medium" 
              PageSize = "10" 
              OnPageIndexChanging="GridView1_PageIndexChanging" >
</asp:GridView>

 

这将把 Excel 文档中的数据加载到带有分页功能的 GridView 中。