Excel Interactive Tab Selection And Display

By H K
Microsoft Excel
Articles

In this topic we will learn how to read the excel sheet with multiple tabs and allow the user to view the data & select data in each tab. This is achieved by presenting the user dynamically a set of buttons which represents the tabs in the excel sheet. The User can upload any sheet with any number of tabs in it and the Web form is capable of generating the button for each tab dynamically and displaying the data on selection of each tab.

I have made use of the following technologies javascript, asp.net & C#.  I have used VS.NET 2005 IDE and a third party free excel component.


As the buttons are generated dynamically based on the number of tabs in the input excel sheet. They need to be generated every time post back happens and the click event needs to be associated programmatically each time post back occurs.

 

The 3rd party excel component can be downloaded from the below link

http://exceldatareader.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=26041

 

I have used a fileuploader control to read the input file from the client. Add the downloaded dll i.e., Excel.dll as reference to the project. Add the name space to the codebehind file

 

Using Excel;

 

The code for reading the file from the fileuploader control is as shown below:

if (FileUploader.HasFile)
{
//Check whether the file is of the .xls format or not

string lFilePath = Path.GetTempPath() + DateTime.UtcNow.Ticks + "_" + FileUploader.FileName;                                     //Upload the file in the server.
FileUploader.SaveAs(lFilePath);

FileStream lExcelStream = null;

try
{
//Read the saved file from the web server
lExcelStream = new FileStream(lFilePath, FileMode.Open, FileAccess.Read);
lExcelReader = new ExcelDataReader(lExcelStream);

}
catch (Exception ex)
{
lblErrorMsg.Text = ex.ToString();

}
finally
{
lExcelStream.Close();
//Delete the file from the webserver
File.Delete(lFilePath);
}




To create the buttons dynamically, the code is as shown
below
//Loop through each Tab in the excel and create a button
foreach (string TabName in TabNames)
{
TabSheetName = new Button();
TabSheetName.ID = "Click" + count;
TabSheetName.Text = TabName;
TabSheetName.Click += (Event_Click);
TabSheetName.OnClientClick = "javascript:SetSelectedTab('" + count + "')";
TabSheetName.CommandArgument = count.ToString();
}




Attach a javascript method called “SetSelectedTab(count)”
which updates an hidden variable with the count of the selected Tab.


The Implementation of the Event_Click is as shown below:
protected void Event_Click(object sender, EventArgs e)
{
//Get the excel sheet data from the view state
DataSet ExcelData =  ViewState["ExcelData"] as DataSet;

//Bind it to the Gridview control based on the selected button’s
//command argument
DisplaySelectedTab.DataSource =  ExcelData.Tables[int.Parse(((Button)sender).CommandArgument)].DefaultView;  
DisplaySelectedTab.DataBind();
}




In order to  create
the button’s for the first time , in the 
open file click event, I am registering a javascript which will create a
postback as shown in the code below.


ClientScript.RegisterClientScriptBlock(this.Page.GetType(), "new", "__doPostBack('','')", true);


Complete source code with working sample availalbe in the site.

Popularity  (1338 Views)
View H K's Articles
Create New Account
Article Discussion: interactive selection and display of multiple tabs in excel
H K posted at Tuesday, May 26, 2009 3:11 PM
Original Article
reply
Question
M W replied to H K at Sunday, October 17, 2010 7:48 AM
How exactly do I implement this? Can you post a link to a sample of the working code?

I use a number of excel files with many tabs, and if I understand correctly, this creates a ´header´ sheet with buttons for all of the tabs?

Do I paste the code into the worksheet code view?

Thanks!
-Mike
reply
Download code
f h replied to H K at Sunday, October 17, 2010 7:48 AM

Has the code been posted yet? Is there a VB.Net version of the code?

Regards

reply