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.    public void WriteExcelWithNPOI(String extension, DataTable dt)
  8.    {
  9.        // dll refered NPOI.dll and NPOI.OOXML
  10.        IWorkbook workbook;
  11.        if (extension == “xlsx”)
  12.        {
  13.            workbook = new XSSFWorkbook();
  14.        }
  15.        else if (extension == “xls”)
  16.        {
  17.            workbook = new HSSFWorkbook();
  18.        }
  19.        else
  20.        {
  21.            throw new Exception(“This format is not supported”);
  22.        }
  23.        ISheet sheet1 = workbook.CreateSheet(“Sheet 1”);
  24.        //make a header row
  25.        IRow row1 = sheet1.CreateRow(0);
  26.        for (int j = 0; j < dt.Columns.Count; j++)
  27.        {
  28.            ICell cell = row1.CreateCell(j);
  29.            String columnName = dt.Columns[j].ToString();
  30.            cell.SetCellValue(columnName);
  31.        }
  32.        //loops through data
  33.        for (int i = 0; i < dt.Rows.Count; i++)
  34.        {
  35.            IRow row = sheet1.CreateRow(i + 1);
  36.            for (int j = 0; j < dt.Columns.Count; j++)
  37.            {
  38.                ICell cell = row.CreateCell(j);
  39.                String columnName = dt.Columns[j].ToString();
  40.                cell.SetCellValue(dt.Rows[i][columnName].ToString());
  41.            }
  42.        }
  43.        using (var exportData = new MemoryStream())
  44.        {
  45.            Response.Clear();
  46.            workbook.Write(exportData);
  47.            if (extension == “xlsx”//xlsx file format
  48.            {
  49.                Response.ContentType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”;
  50.                Response.AddHeader(“Content-Disposition”string.Format(“attachment;filename={0}”“tpms_Dict.xlsx”));
  51.                Response.BinaryWrite(exportData.ToArray());
  52.            }
  53.            else if (extension == “xls”)  //xls file format
  54.            {
  55.                Response.ContentType = “application/vnd.ms-excel”;
  56.                Response.AddHeader(“Content-Disposition”string.Format(“attachment;filename={0}”“tpms_dict.xls”));
  57.                Response.BinaryWrite(exportData.GetBuffer());
  58.            }
  59.            Response.End();
  60.        }
  61.    }

Other ways to read and write excel

Conclusion

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

Leave a Reply

Your email address will not be published. Required fields are marked *