More in this section
Blogs RSS feed

Excel DataSource

by User Not Found
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 
        // 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); 
        // 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.

1 comment

Leave a comment
  1. tegobay May 10, 2010
    The code above leaves an open connection and is not functioning properly.

    Leave a comment