Table:

table

Script for creating table:

  1. CREATE TABLE [dbo].[Users](
  2. [UserId] [int] NOT NULL,
  3. [Firstname] [varchar](50) NOT NULL,
  4. [Surname] [varchar](50) NOT NULL,
  5. [DepartmentId] [nchar](10) NOT NULL,
  6. [Email] [nvarchar](50) NOT NULL,
  7. [Password] [nvarchar](50) NOT NULL,
  8. [RoleId] [int] NOT NULL
  9. ) ON [PRIMARY]
Stored Procedure to get the data:
  1. CREATE PROCEDURE p_GetUsersData
  3. AS
  4. BEGIN
  5. -- SET NOCOUNT ON added to prevent extra result sets from
  6. -- interfering with SELECT statements.
  7. SET NOCOUNT ON;
  9. -- Insert statements for procedure here
  10. SELECT Firstname, Surname,Email
  11. FROM Users
  12. END
  13. GO
Now create ASP.NET Web Project and add a page then add Grid inside page and then bind the data to Griview.

Run

Below is the template that we will use to export the data.
  1. <?xml version="1.0"?>
  2. <?mso-application progid="Excel.Sheet"?>
  3. <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  4. xmlns:o="urn:schemas-microsoft-com:office:office"
  5. xmlns:x="urn:schemas-microsoft-com:office:excel"
  6. xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  7. xmlns:html="http://www.w3.org/TR/REC-html40">
  8. <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  9. <Author>Vinay</Author>
  10. <LastAuthor>Vinay</LastAuthor>
  11. <Created>2015-09-26T06:14:39Z</Created>
  12. <LastSaved>2015-09-26T11:36:29Z</LastSaved>
  13. <Version>12.00</Version>
  14. </DocumentProperties>
  15. <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  16. <WindowHeight>4680</WindowHeight>
  17. <WindowWidth>7635</WindowWidth>
  18. <WindowTopX>480</WindowTopX>
  19. <WindowTopY>75</WindowTopY>
  20. <ProtectStructure>False</ProtectStructure>
  21. <ProtectWindows>False</ProtectWindows>
  22. </ExcelWorkbook>
  23. <Styles>
  24. <Style ss:ID="Default" ss:Name="Normal">
  25. <Alignment ss:Vertical="Bottom"/>
  26. <Borders/>
  27. <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
  28. <Interior/>
  29. <NumberFormat/>
  30. <Protection/>
  31. </Style>
  32. <Style ss:ID="s16">
  33. <Borders>
  34. <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
  35. <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
  36. <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
  37. <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
  38. </Borders>
  39. <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
  40. </Style>
  41. </Styles>
  42. <Worksheet ss:Name="Sheet1">
  43. <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="17" x:FullColumns="1"
  44. x:FullRows="1" ss:DefaultRowHeight="15">
  45. <Column ss:Index="1" ss:Width="50"></Column>
  46. <Column ss:Width="50"></Column>
  47. <Column ss:Width="150"></Column>
  48. <!--Insert Data Here-->
  49. </Table>
  50. <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
  51. <PageSetup>
  52. <Header x:Margin="0.3"/>
  53. <Footer x:Margin="0.3"/>
  54. <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
  55. </PageSetup>
  56. <Selected/>
  57. <Panes>
  58. <Pane>
  59. <Number>3</Number>
  60. <ActiveRow>20</ActiveRow>
  61. <ActiveCol>5</ActiveCol>
  62. </Pane>
  63. </Panes>
  64. <ProtectObjects>False</ProtectObjects>
  65. <ProtectScenarios>False</ProtectScenarios>
  66. </WorksheetOptions>
  67. </Worksheet>
  68. <Worksheet ss:Name="Sheet2">
  69. <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
  70. x:FullRows="1" ss:DefaultRowHeight="15">
  71. </Table>
  72. <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
  73. <PageSetup>
  74. <Header x:Margin="0.3"/>
  75. <Footer x:Margin="0.3"/>
  76. <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
  77. </PageSetup>
  78. <ProtectObjects>False</ProtectObjects>
  79. <ProtectScenarios>False</ProtectScenarios>
  80. </WorksheetOptions>
  81. </Worksheet>
  82. <Worksheet ss:Name="Sheet3">
  83. <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
  84. x:FullRows="1" ss:DefaultRowHeight="15">
  85. </Table>
  86. <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
  87. <PageSetup>
  88. <Header x:Margin="0.3"/>
  89. <Footer x:Margin="0.3"/>
  90. <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
  91. </PageSetup>
  92. <ProtectObjects>False</ProtectObjects>
  93. <ProtectScenarios>False</ProtectScenarios>
  94. </WorksheetOptions>
  95. </Worksheet>
  96. </Workbook>
Method to Export the data:
  1. protected void btnExcel_Click(object sender, EventArgs e)
  2. {
  3. string templateString = string.Empty;
  4. StringBuilder sb = new StringBuilder();
  5. try
  6. {
  7. templateString = GetTemplateString();
  9. if (!string.IsNullOrEmpty(templateString))
  10. {
  11. if (Session["Data"] != null)
  12. {
  13. DataSet ds = Session["Data"] as DataSet;
  15. if (ds != null && ds.Tables.Count > 0)
  16. {
  17. sb.Append("<Row>");
  18. sb.Append("<Cell><Data ss:Type=\"String\">Firstname</Data></Cell>");
  19. sb.Append("<Cell><Data ss:Type=\"String\">Surname</Data></Cell>");
  20. sb.Append("<Cell><Data ss:Type=\"String\">Email</Data></Cell>");
  21. sb.Append("</Row>");
  23. foreach (DataRow r in ds.Tables[0].Rows)
  24. {
  25. sb.Append("<Row>");
  26. sb.Append("<Cell><Data ss:Type=\"String\">" + r["Firstname"].ToString() + "</Data></Cell>");
  27. sb.Append("<Cell><Data ss:Type=\"String\">" + r["Surname"].ToString() + "</Data></Cell>");
  28. sb.Append("<Cell><Data ss:Type=\"String\">" + r["Email"].ToString() + "</Data></Cell>");
  29. sb.Append("</Row>");
  30. }
  32. templateString = templateString.Replace("<!--Insert Data Here-->", sb.ToString());
  34. }
  36. byte[] temp = Encoding.UTF8.GetBytes(templateString);
  38. if (temp != null)
  39. {
  42. Response.ClearContent();
  43. Response.AppendHeader("content-disposition", "attachment; filename=ListeRue.xls");
  44. Response.ContentType = "application/excel";
  46. Response.Charset = "";
  48. Response.BinaryWrite(temp);
  49. Response.Flush();
  50. Response.End();
  52. }
  54. }
  55. }
  57. }
  58. catch (Exception ex)
  59. {
  60. }
  62. }