Export to Excel Using NPOI DLL Library

Introduction

In this article, I have posted the code for exporting to Excel from any data source. I have attached code and required dll in the attachment section. It is one of the ways to write with Excel and at the end of the article I posted links for the other ways to work with Excel.

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 refered 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

I hope the above information was useful. Kindly let me know your valuable feedback or thoughts.