Export to Excel Using NPOI DLL Library

Introduction

In this article is the code for exporting to Excel from any data source. Attached is code and the required DLL in the attachment section. It is one of the ways to write with Excel and at the end of the article are links for the other ways to work with Excel.

Source Code

Aspx code

<asp:Button ID="btnexport_grid" runat="server" Text="Exporttogrid" OnClick="btnexport_grid_Click" />

**
Code behind code**

protected void  btnexport_grid_Click(object sender, EventArgs e)  
     {  
         DataSet ds = ObjOSM.getboqitems();  
         DataTable dt = ds.Tables[0];  
           WriteExcelWithNPOI("xlsx", dt);  
     }  
   
   public void  WriteExcelWithNPOI(String extension, DataTable dt)  
   {  
       // dll referred NPOI.dll and NPOI.OOXML  
   
       IWorkbook workbook;  
   
       if (extension == "xlsx")  
       {  
           workbook = new  XSSFWorkbook();  
       }  
       else if  (extension == "xls")  
       {  
           workbook = new  HSSFWorkbook();  
       }  
       else 
       {  
           throw new  Exception("This format is not supported");  
       }  
   
       ISheet sheet1 = workbook.CreateSheet("Sheet 1");  
   
       //make a header row  
       IRow row1 = sheet1.CreateRow(0);  
   
       for (int j = 0; j < dt.Columns.Count; j++)  
       {  
   
           ICell cell = row1.CreateCell(j);  
   
           String columnName = dt.Columns[j].ToString();  
           cell.SetCellValue(columnName);  
       }  
   
       //loops through data  
       for (int i = 0; i < dt.Rows.Count; i++)  
       {  
           IRow row = sheet1.CreateRow(i + 1);  
           for (int j = 0; j < dt.Columns.Count; j++)  
           {  
   
               ICell cell = row.CreateCell(j);  
               String columnName = dt.Columns[j].ToString();  
               cell.SetCellValue(dt.Rows[i][columnName].ToString());  
           }  
       }  
   
       using (var exportData = new MemoryStream())  
       {  
           Response.Clear();  
           workbook.Write(exportData);  
           if (extension == "xlsx") //xlsx file format  
           {  
               Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
               Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "tpms_Dict.xlsx"));  
               Response.BinaryWrite(exportData.ToArray());  
           }  
           else if  (extension == "xls")  //xls file format  
           {  
               Response.ContentType = "application/vnd.ms-excel";  
               Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "tpms_dict.xls"));  
               Response.BinaryWrite(exportData.GetBuffer());  
           }  
           Response.End();  
       }  
   }

Other ways to read and write Excel

• Read Excel Files Using Open XML SDK In ASP.NET C#
• Read Excel Files Using Microsoft Office Interop Assemblies in ASP.NET C#
• Import and Export Excel Files into Varbinary (Max) in SQL Server

Conclusion

Hope the above information was useful. Kindly let us know your valuable feedback or thoughts.