public void LoadSheets(string fileName)
{
nameOfFile = Path.GetFileNameWithoutExtension(fileName);
excelFrame.Visible = false;
lblSheet.Text = String.Empty;
#region Creating dynamic sheets on basis of excel file
ExcelFile ef = GetExcelFile(fileName);
ExcelWorksheetCollection worksheets = ef.Worksheets;
xcel = ef;
dictControl.Clear(); // To avoid mixing of sheets of different excel file
foreach (ExcelWorksheet ew in worksheets)
{
// Creating controls dynamically
LinkButton lb = new LinkButton();
lb.ID = "lbl" + ew.Name;
lb.Text = ew.Name;
lb.Click += new EventHandler(Sheet_Click);
// Adding hashControl dynamically
phContainer.Controls.Add(lb);
phContainer.Controls.Add(new LiteralControl(" "));
// Preserving ViewState
dictControl.Add(lb.ID, lb.Text);
}
#endregion
}
protected void Sheet_Click(object sender, EventArgs e)
{
LinkButton lb = (LinkButton)sender;
ExcelWorksheetCollection worksheets = xcel.Worksheets;
OpenInIFrame(worksheets, lb.Text);
}
public void fillExcelWorksheets(ExcelWorksheetCollection worksheets, int[] startColumns)
{
int tabId = 0;
foreach (GroupTabPage tab in _groupsControl.TabPages)
{
while (tabId < worksheets.Count && worksheets[tabId].Name != tab.Text)
{
tabId++;
}
//worksheets[tabId].Cells[0, startColumns[tabId]].Value = this._container.Name;
tab.fillExcelWorksheet(worksheets[tabId], ref startColumns[tabId]);
tabId++;
}
}
protected void OpenInIFrame(ExcelWorksheetCollection worksheets, string currentSheet)
{
#region Set Active Worksheet
if (currentSheet != null)
{
worksheets.ActiveWorksheet = worksheets[currentSheet];
}
else
{
worksheets.ActiveWorksheet = worksheets[0];
}
#endregion
#region Save Html file of Excel file
WebColorConverter converter = new WebColorConverter();
lblSheet.ForeColor = (Color)converter.ConvertFromString("ThreeDDarkShadow");
lblSheet.Text = "You are watching sheet <b>" + worksheets.ActiveWorksheet.Name + "</b> of total <b>" + worksheets.Count + "</b> sheets in excel file <b>" + nameOfFile + "</b>";
string file = nameOfFile + "_" + worksheets.ActiveWorksheet.Name;
string uplPath = Server.MapPath(@"~/Documents/Excel_Html/" + file + ".html");
string virPath = HttpRuntime.AppDomainAppVirtualPath + @"/Documents/Excel_Html/" + file + ".html";
xcel.SaveHtml(uplPath, null, true);
#endregion
#region Set Iframe src path
excelFrame.Visible = true;
excelFrame.Attributes["src"] = virPath;
#endregion
#region Show error if there is no data in the sheet
CellRange range = worksheets.ActiveWorksheet.GetUsedCellRange(true);
if (range == null)
{
lblSheet.ForeColor = System.Drawing.Color.Red;
lblSheet.Text = "<b>ERROR</b> - There is no data in the selected sheet <b>" + worksheets.ActiveWorksheet.Name + "</b>";
}
#endregion
}
protected void OpenDefaultSheet()
{
ExcelWorksheetCollection worksheets = xcel.Worksheets;
OpenInIFrame(worksheets, null);
}
