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


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