Post
Ask Question

Excel Export using XML Spreadsheet

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   
  2.           
  3. AS  
  4. BEGIN  
  5.     -- SET NOCOUNT ON added to prevent extra result sets from  
  6.     -- interfering with SELECT statements.  
  7.     SET NOCOUNT ON;  
  8.   
  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();  
  8.   
  9.         if (!string.IsNullOrEmpty(templateString))  
  10.         {  
  11.             if (Session["Data"] != null)  
  12.             {  
  13.                 DataSet ds = Session["Data"] as DataSet;  
  14.   
  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>");  
  22.   
  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.                     }  
  31.   
  32.                   templateString =  templateString.Replace("<!--Insert Data Here-->", sb.ToString());  
  33.                           
  34.                 }  
  35.   
  36.                 byte[] temp = Encoding.UTF8.GetBytes(templateString);  
  37.   
  38.                 if (temp != null)  
  39.                 {  
  40.                     
  41.   
  42.                    Response.ClearContent();  
  43.                    Response.AppendHeader("content-disposition", "attachment; filename=ListeRue.xls");  
  44.                    Response.ContentType = "application/excel";  
  45.   
  46.                    Response.Charset = "";  
  47.   
  48.                    Response.BinaryWrite(temp);  
  49.                    Response.Flush();  
  50.                    Response.End();  
  51.   
  52.                 }  
  53.   
  54.             }  
  55.         }  
  56.   
  57.     }  
  58.     catch (Exception ex)  
  59.     {  
  60.     }  
  61.   
  62. }  

 
Next Recommended Reading How To Convert JSON Into XML Or XML Into JSON