top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

    Get Free Article Updates

Facebook Login
Site Registration

Export An Excel Using Closed XML

0 votes


In today's tech world, most of the applications being developed under Logistics, Inventory, Internal Transaction and other domains require day-to-day data in excel files and prefer excel file operations in their applications.

I will be sharing one of the Nuget Package tools which, with very minimal lines of code, will export an excel file for us.

The Tool is Closed XML.

Just write a few lines of code and done! It is developer friendly. If we have used the Open XML, there are a lot of lines of code which are required to export an excel from data. We can create an excel file of 2007/2010 configuration without an Excel application.

To add the closed XML package, we add it directly through the user interface from the Nuget Gallery and also, we can use the Package Manager console to add the package using the below command

PM> Install-Package ClosedXML


  1. DataTable dt = new DataTable();  
  2. dt.Columns.AddRange(new DataColumn[3]  
  3. {  
  4.  new DataColumn("Id", typeof(int)), new DataColumn("Name", typeof(string)), new DataColumn("Country", typeof(string))  
  5. });  
  6. dt.Rows.Add(1, "C Sharp corner", "United States");  
  7. dt.Rows.Add(2, "Suraj", "India");  
  8. dt.Rows.Add(3, "Test User", "France");  
  9. dt.Rows.Add(4, "Developer", "Russia"); //Exporting to Excel               
  10. string folderPath = "C:\\Excel\\";              
  11. if (!Directory.Exists(folderPath))              
  12. {                   
  13.     Directory.CreateDirectory(folderPath);            
  14. }     
  15. //Codes for the Closed XML             
  16.     using (XLWorkbook wb = new XLWorkbook())              
  17.     {                
  18.         wb.Worksheets.Add(dt, "Customers");                  
  19.         //wb.SaveAs(folderPath + "DataGridViewExport.xlsx");                
  20.         string myName = Server.UrlEncode("Test" + "_" + DateTime.Now.ToShortDateString() + ".xlsx");        
  21.         MemoryStream stream = GetStream(wb);  
  22.         // The method is defined below             
  23.         Response.Clear();                  
  24.         Response.Buffer = true;              
  25.         Response.AddHeader("content-disposition", "attachment; filename=" + myName);         
  26.         Response.ContentType = "application/";           
  27.         Response.BinaryWrite(stream.ToArray());                
  28.         Response.End();  
  29.     }  

The above code instantiates a data table, with few data initializations.

  1. public MemoryStream GetStream(XLWorkbook excelWorkbook)   
  2. {  
  3.     MemoryStream fs = new MemoryStream();  
  4.     excelWorkbook.SaveAs(fs);  
  5.     fs.Position = 0;  
  6.     return fs;  
  7. }

We are using this method, so as to return a stream in order to download the file in response to using the stream. The save as method of the Closed XML helps create the stream.

Downloaded file looks like below,


posted Jan 16 by Shivaranjini

  Promote This Article
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button

Contact Us
+91 9880187415
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Karnataka INDIA.