displaydatafromexcel.aspx.cs

在asp.net中进行Excel读写. 读入Excel数据到asp.net页面中

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data .OleDb ;

namespace ExcelOperate
{
	/// <summary>
	/// DisplayDataFromExcel 的摘要说明。
	/// </summary>
	public class DisplayDataFromExcel : System.Web.UI.Page
	{
		protected System.Web.UI.WebControls.DataGrid grdDisplayExcelData;
	
		private void Page_Load(object sender, System.EventArgs e)
		{
			// 在此处放置用户代码以初始化页面
			// Create connction string variable. Modify the "Data Source"
			// parameter as appropriate for your environment.

			string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+
				Server.MapPath ("File") + "\\" + "ExcelData.xls"+";"+
				"Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
			/*
			 * "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""" 
			 "HDR=Yes;" indicates that the first row contains columnnames, not data
			 "IMEX=1;" tells the driver to always read "intermixed" data columns as text.
					   Note that this option might affect excel sheet write access negative.
			 TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
			 TIP! Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
				  located registry REG_DWORD "TypeGuessRows". 
				  That's the key to not letting Excel use only the first 8 rows to guess the columns data type.
				   Set this value to 0 to scan all rows. This might hurt performance.
			 Important note! The two double quota ("") in the string are escaped quotas (VB syntax), 
				   you may have to change this to your language s
				   pecific escape syntax (ex. \") or maybe single quota (').
			 */

			// Create connection object by using the preceding connction string.
			OleDbConnection objConn = new OleDbConnection ( strCon );
			
			// Open conneciton with the database.
			objConn.Open ();

			//The code to follow uses a SQL SELECT command to display the data from the worksheet.

			// Create new OldDbCommand to return data from worksheet.
			OleDbCommand objCmdSelect = new OleDbCommand ( "select * from [Sheet1$]", objConn );

			//OleDbCommand objCmdSelect = new OleDbCommand ( "select * from [myRange1]", objConn );

			// Create new OleDbDataAdapter that is used to build a DataSet
			// based on the preceding SQL SELECT statement.
			OleDbDataAdapter da = new OleDbDataAdapter ();

			// Pass the Select command to the adapter.
			da.SelectCommand = objCmdSelect;

			// Create new DataSet to hold information from the worksheet.
			DataSet ds = new DataSet ();

			// Fill the DataSet with the information from the worksheet.
			da.Fill ( ds,"XLData" );

			// Bind data to DataGrid control.
			this.grdDisplayExcelData .DataSource = ds.Tables [0].DefaultView ;
			this.grdDisplayExcelData .DataBind ();

			// Clean up objects;
			objConn.Close ();
		}

		#region Web 窗体设计器生成的代码
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		/// 设计器支持所需的方法 - 不要使用代码编辑器修改
		/// 此方法的内容。
		/// </summary>
		private void InitializeComponent()
		{    
			this.Load += new System.EventHandler(this.Page_Load);

		}
		#endregion
	}
}