Excel Export using XML Spreadsheet

This blog helps you to export data to excel using XML spreadsheet.

Table:



Script for creating table:

CREATE TABLE [dbo].[Users](
    [UserId] [int] NOT NULL,
    [Firstname] [varchar](50) NOT NULL,
    [Surname] [varchar](50) NOT NULL,
    [DepartmentId] [nchar](10) NOT NULL,
    [Email] [nvarchar](50) NOT NULL,
    [Password] [nvarchar](50) NOT NULL,
    [RoleId] [int] NOT NULL
) ON [PRIMARY]

Stored Procedure to get the data:

CREATE PROCEDURE p_GetUsersData

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT Firstname, Surname,Email
    FROM Users
END
GO

Now create ASP.NET Web Project and add a page then add Grid inside page and then bind the data to Griview.



Below is the template that we will use to export the data.

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Vinay</Author>
  <LastAuthor>Vinay</LastAuthor>
  <Created>2015-09-26T06:14:39Z</Created>
  <LastSaved>2015-09-26T11:36:29Z</LastSaved>
  <Version>12.00</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>4680</WindowHeight>
  <WindowWidth>7635</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>75</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s16">
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="17" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
    <Column ss:Index="1" ss:Width="50"></Column>
    <Column  ss:Width="50"></Column>
    <Column  ss:Width="150"></Column>
   <!--Insert Data Here-->
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>20</ActiveRow>
     <ActiveCol>5</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet3">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

Method to Export the data:

protected void btnExcel_Click(object sender, EventArgs e)
{
    string templateString = string.Empty;
    StringBuilder sb = new StringBuilder();
    try
    {
        templateString = GetTemplateString();

        if (!string.IsNullOrEmpty(templateString))
        {
            if (Session["Data"] != null)
            {
                DataSet ds = Session["Data"] as DataSet;

                if (ds != null && ds.Tables.Count > 0)
                {
                    sb.Append("<Row>");
                    sb.Append("<Cell><Data ss:Type=\"String\">Firstname</Data></Cell>");
                    sb.Append("<Cell><Data ss:Type=\"String\">Surname</Data></Cell>");
                    sb.Append("<Cell><Data ss:Type=\"String\">Email</Data></Cell>");
                    sb.Append("</Row>");

                    foreach (DataRow r in ds.Tables[0].Rows)
                    {
                        sb.Append("<Row>");
                        sb.Append("<Cell><Data ss:Type=\"String\">" + r["Firstname"].ToString() + "</Data></Cell>");
                        sb.Append("<Cell><Data ss:Type=\"String\">" + r["Surname"].ToString() + "</Data></Cell>");
                        sb.Append("<Cell><Data ss:Type=\"String\">" + r["Email"].ToString() + "</Data></Cell>");
                        sb.Append("</Row>");
                    }

                  templateString =  templateString.Replace("<!--Insert Data Here-->", sb.ToString());

                }

                byte[] temp = Encoding.UTF8.GetBytes(templateString);

                if (temp != null)
                {


                   Response.ClearContent();
                   Response.AppendHeader("content-disposition", "attachment; filename=ListeRue.xls");
                   Response.ContentType = "application/excel";

                   Response.Charset = "";

                   Response.BinaryWrite(temp);
                   Response.Flush();
                   Response.End();

                }

            }
        }

    }
    catch (Exception ex)
    {
    }

}