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
	}
}