In Sitefinity, you can bind data to a variety of data sources and one of those options is MS Excel. Although there is no data source control, you can bind a RadGrid to MS Excel programmatically using ADO.NET. The process involves connecting to the Excel sheet, select data, loading it into a dataset and binding the RadGrid. In this scenario, you want to use the NeedDataSource event. Here is the sample C# code:
| using System; |
| using System.Data.OleDb; |
| using System.Data; |
| public partial class Files_RadGridExcelDataSource : System.Web.UI.UserControl |
| { |
| protected void Page_Load(object sender, EventArgs e) |
| { |
| } |
| protected void RadGrid1_NeedDataSource(object source, Telerik.Web.UI.GridNeedDataSourceEventArgs e) |
| { |
| // Create a new Adapter |
| OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(); |
| // retrieve the Select command for the Spreadsheet |
| objDataAdapter.SelectCommand = ExcelConnection(); |
| // Create a DataSet |
| DataSet objDataSet = new DataSet(); |
| // Populate the DataSet with the spreadsheet worksheet data |
| objDataAdapter.Fill(objDataSet); |
| // Bind the data to the RadGrid |
| RadGrid1.DataSource = objDataSet.Tables[0].DefaultView; |
| } |
| protected OleDbCommand ExcelConnection() |
| { |
| // Connect to the Excel Spreadsheet |
| string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + |
| "Data Source=" + Server.MapPath(@"~\Files\Test.xls") + ";" + |
| "Extended Properties=Excel 8.0;"; |
| // create your excel connection object using the connection string |
| OleDbConnection conn = new OleDbConnection(xConnStr); |
| conn.Open(); |
| // use a SQL Select command to retrieve the data from the Excel Spreadsheet |
| // the "table name" is the name of the worksheet within the spreadsheet |
| // in this case, the worksheet name is "Customers" and is coded as: [Customers$] |
| OleDbCommand objCommand = new OleDbCommand("SELECT * FROM [Customers$]", conn); |
| return objCommand; |
| } |
| } |
Special thanks to Nannette Thacker and her detailed code samples. A VB.NET sample is also available on her site. For more info on uploading user controls, please read this article.