Post
Ask Question

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

  1. <asp:Button ID="btnexport_grid" runat="server" Text="Exporttogrid" OnClick="btnexport_grid_Click" />  
Code behind code
  1. protected void btnexport_grid_Click(object sender, EventArgs e)  
  2.      {  
  3.          DataSet ds = ObjOSM.getboqitems();  
  4.          DataTable dt = ds.Tables[0];  
  5.            WriteExcelWithNPOI("xlsx", dt);  
  6.      }  
  7.   
  8.    public void WriteExcelWithNPOI(String extension, DataTable dt)  
  9.    {  
  10.        // dll refered NPOI.dll and NPOI.OOXML  
  11.   
  12.        IWorkbook workbook;  
  13.   
  14.        if (extension == "xlsx")  
  15.        {  
  16.            workbook = new XSSFWorkbook();  
  17.        }  
  18.        else if (extension == "xls")  
  19.        {  
  20.            workbook = new HSSFWorkbook();  
  21.        }  
  22.        else  
  23.        {  
  24.            throw new Exception("This format is not supported");  
  25.        }  
  26.   
  27.        ISheet sheet1 = workbook.CreateSheet("Sheet 1");  
  28.   
  29.        //make a header row  
  30.        IRow row1 = sheet1.CreateRow(0);  
  31.   
  32.        for (int j = 0; j < dt.Columns.Count; j++)  
  33.        {  
  34.   
  35.            ICell cell = row1.CreateCell(j);  
  36.   
  37.            String columnName = dt.Columns[j].ToString();  
  38.            cell.SetCellValue(columnName);  
  39.        }  
  40.   
  41.        //loops through data  
  42.        for (int i = 0; i < dt.Rows.Count; i++)  
  43.        {  
  44.            IRow row = sheet1.CreateRow(i + 1);  
  45.            for (int j = 0; j < dt.Columns.Count; j++)  
  46.            {  
  47.   
  48.                ICell cell = row.CreateCell(j);  
  49.                String columnName = dt.Columns[j].ToString();  
  50.                cell.SetCellValue(dt.Rows[i][columnName].ToString());  
  51.            }  
  52.        }  
  53.   
  54.        using (var exportData = new MemoryStream())  
  55.        {  
  56.            Response.Clear();  
  57.            workbook.Write(exportData);  
  58.            if (extension == "xlsx") //xlsx file format  
  59.            {  
  60.                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
  61.                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "tpms_Dict.xlsx"));  
  62.                Response.BinaryWrite(exportData.ToArray());  
  63.            }  
  64.            else if (extension == "xls")  //xls file format  
  65.            {  
  66.                Response.ContentType = "application/vnd.ms-excel";  
  67.                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "tpms_dict.xls"));  
  68.                Response.BinaryWrite(exportData.GetBuffer());  
  69.            }  
  70.            Response.End();  
  71.        }  
  72.    }  
Other ways to read and write excel

Conclusion

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