Exporting Excel With Multiple Sheets To Data set

In this blog, we will see how to export an Excel file having multiple sheets to a data set; i.e., we will get each sheet in Excel as a separate table inside the data set.

Suppose, we have an Excel sheet having employees' personal and official details, as shown below.

Now, we have to export these details to a data set and show them inside two separate GridViews in our Webpage

We are using two DLL files for it, which are.

Excel.dll
ICSharpCode.SharpZipLib.dll

Now, we will design our file upload control, buttons and two GridViews, which are as follows.

<div>
<asp:FileUpload ID="filupld" runat="server" />
<asp:Button ID="btnexport" runat="server" Text="Export" OnClick="btnexportclick" />
<br />
<br />
<b>Employee Personal</b>
<asp:GridView ID="grdemployeepersonal" runat="server" OnRowDataBound="grdemppersonal" AutoGenerateColumns="true" CellPadding="10">
</asp:GridView>
<br />
<br />
<b>Employee Official</b>
<asp:GridView ID="grdemployeeofficial" runat="server" OnRowDataBound="grdempofficial" AutoGenerateColumns="true" CellPadding="10">
</asp:GridView>
</div>

Now, we can write our button. Click export function and GridView bind function.

protected void btnexportclick(object sender, EventArgs e)
{
try
{
//Getting the filename and mapping it to our folder
string FilePath = Server.MapPath(filupld.FileName);
//Checking if file exists. If exists, we delete it and upload new file.
if (File.Exists(FilePath))
{
File.Delete(FilePath);
}
//Saving excel file to folder
filupld.PostedFile.SaveAs(FilePath);
DataSet Ds = new DataSet();
string connString = string.Empty;
FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read);
//1. Reading from Excel file (2003 format; *.xls)
if (Path.GetExtension(FilePath) == ".xls" || Path.GetExtension(FilePath) == ".XLS")
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
Ds = excelReader.AsDataSet();
excelReader.Close();
}
//2. Reading from Excel file (2007 format; *.xlsx)
if (Path.GetExtension(FilePath) == ".xlsx" || Path.GetExtension(FilePath) == ".XLSX")
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
Ds = excelReader.AsDataSet();
excelReader.Close();
}
grdemployeepersonal.DataSource = Ds.Tables["Employee Personal"];
grdemployeepersonal.DataBind();
grdemployeeofficial.DataSource = Ds.Tables["Employee Official"];
grdemployeeofficial.DataBind();
}
catch (Exception ex)
{ }
}

After selecting our Excel file and clicking on export button, we will get the corresponding data in GridViews, as shown below.

Conclusion

In this blog, we found out how to export an Excel file with multiple sheets to a data set and bind it in a GridView. You can use the code, mentioned above to export Excel with n sheets to a data set.

2 Comments

Join the conversation! Your thoughts help the community grow.

Midhun Tp10y ago
Hi, ExcelDataReader is dependent on this ICSharpCode.SharpZipLib.dll. Without that dll, it will throw error.
Former member10y ago
Why you use this dll ICSharpCode.SharpZipLib.dll ? let us know the reason. thanks