Post
Ask Question

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.
  1. <div>  
  2.     <asp:FileUpload ID="filupld" runat="server" />  
  3.     <asp:Button ID="btnexport" runat="server" Text="Export" OnClick="btnexportclick" />  
  4.     <br />  
  5.     <br />  
  6.     <b>Employee Personal</b>  
  7.     <asp:GridView ID="grdemployeepersonal" runat="server" OnRowDataBound="grdemppersonal" AutoGenerateColumns="true" CellPadding="10">  
  8.     </asp:GridView>  
  9.     <br />  
  10.     <br />  
  11.     <b>Employee Official</b>  
  12.     <asp:GridView ID="grdemployeeofficial" runat="server" OnRowDataBound="grdempofficial" AutoGenerateColumns="true" CellPadding="10">  
  13.     </asp:GridView>  
  14. </div>   
Now, we can write our button. Click export function and GridView bind function.
  1. protected void btnexportclick(object sender, EventArgs e)  
  2. {         
  3.     try  
  4.     {  
  5.         //Getting the filename and mapping it to our folder  
  6.         string FilePath = Server.MapPath(filupld.FileName);  
  7.         //Checking if file exists. If exists, we delete it and upload new file.  
  8.         if (File.Exists(FilePath))  
  9.         {  
  10.             File.Delete(FilePath);  
  11.         }  
  12.         //Saving excel file to folder  
  13.         filupld.PostedFile.SaveAs(FilePath);  
  14.         DataSet Ds = new DataSet();  
  15.         string connString = string.Empty;  
  16.         FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read);  
  17.         //1. Reading from Excel file (2003 format; *.xls)  
  18.         if (Path.GetExtension(FilePath) == ".xls" || Path.GetExtension(FilePath) == ".XLS")  
  19.         {  
  20.             IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);  
  21.             excelReader.IsFirstRowAsColumnNames = true;  
  22.             Ds = excelReader.AsDataSet();  
  23.             excelReader.Close();  
  24.         }  
  25.         //2. Reading from Excel file (2007 format; *.xlsx)  
  26.         if (Path.GetExtension(FilePath) == ".xlsx" || Path.GetExtension(FilePath) == ".XLSX")  
  27.         {  
  28.             IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);  
  29.             excelReader.IsFirstRowAsColumnNames = true;  
  30.             Ds = excelReader.AsDataSet();  
  31.             excelReader.Close();  
  32.         }  
  33.         grdemployeepersonal.DataSource = Ds.Tables["Employee Personal"];  
  34.         grdemployeepersonal.DataBind();  
  35.         grdemployeeofficial.DataSource = Ds.Tables["Employee Official"];  
  36.         grdemployeeofficial.DataBind();  
  37.     }  
  38.     catch (Exception ex)  
  39.     { }  
  40. }   
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.