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. }
  8. public void WriteExcelWithNPOI(String extension, DataTable dt)
  9. {
  10. // dll refered NPOI.dll and NPOI.OOXML
  12. IWorkbook workbook;
  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. }
  27. ISheet sheet1 = workbook.CreateSheet("Sheet 1");
  29. //make a header row
  30. IRow row1 = sheet1.CreateRow(0);
  32. for (int j = 0; j < dt.Columns.Count; j++)
  33. {
  35. ICell cell = row1.CreateCell(j);
  37. String columnName = dt.Columns[j].ToString();
  38. cell.SetCellValue(columnName);
  39. }
  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. {
  48. ICell cell = row.CreateCell(j);
  49. String columnName = dt.Columns[j].ToString();
  50. cell.SetCellValue(dt.Rows[i][columnName].ToString());
  51. }
  52. }
  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.