Excel DataSource

Excel DataSource

December 19, 2008 0 Comments

The content you're reading is getting on in years
This post is on the older side and its content may be out of date.
Be sure to visit our blogs homepage for our latest news, updates and information.

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.
progress-logo

The Progress Team

View all posts from The Progress Team on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.

Comments
Comments are disabled in preview mode.
Topics
 
 
Latest Stories in
Your Inbox
Subscribe
More From Progress
New_Mobile_Dev_Ebook_Progress_Website_Thumbail
The New Mobile Development Landscape
Download Whitepaper
 
IDC Spotlight Sitefinity Thumbnail
Choosing the Right Digital Experience Platform to Improve Business Outcomes
Download Whitepaper
 
TheFastestWayToBuildMobileAppsArtboard-2
The Fastest Way to Build Mobile Apps With Cloud Data
Watch Webinar