Export XML to Excel in SharePoint 2010

Description:

I have an xml file Inputs.xml which has the following values


I need to display the values in the SharePoint 2010 Visual Web Part. And there will be a button in the Visual web part "Export to Excel" by clicking on that I should be able to save the values in the excel.



Create SharePoint 2010 Visual Web Part using Visual Studio 2010:

1. Open Visual Studio 2010.
   
   
2. Go to File => New => Project.
   
   
3. Select Visual Web Part template from the installed templates.
   
   
   
   
4. Enter the Name for the project as VisualWebPart and then click on Ok.
   
   
5. Select the local site that will be used for debugging.
   
   
   
   
6. Click on Finish.
   
   
7. In the solution explorer, right click on VisualWebPart1 and then click on Delete.
   
   
   
   
8. Right click on the solution, click on Add and then click on New Item.
   
   
9. Select Visual Web Part from the installed templates and enter the Name as VisualWebPart, then click on Ok.
   
   
10. In VisualWebPartUserControl.aspx add the GridView to display the values from the xml.
    
    
11. Add a button, on button click event the values should be exported to Excel.

VisualWebPartUserControl.ascx:

1. Replace VisualWebPartUserControl.aspx with the following

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>
<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral,
PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral,
PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
%>
<%@ Import Namespace="Microsoft.SharePoint" %>
<%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral,
PublicKeyToken=71e9bce111e9429c" %>
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="VisualWebPartUserControl.ascx.cs"
Inherits="VisualWebPart.VisualWebPart.VisualWebPartUserControl" %>
<asp:GridView ID="gvXMLValues" runat="server" EnableModelValidation="True"
    CellPadding="4" ForeColor="#333333" GridLines="None">
    <AlternatingRowStyle BackColor="White" />
    <EditRowStyle BackColor="#7C6F57" />
    <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#E3EAEB" />
    <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
</asp:GridView>
<p>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Button ID="btnExporttoExcel" runat="server" onclick=" btnExporttoExcel _Click"
        Text="Export to Excel" Width="176px" />
</p>

VisualWebPartUserControl.ascx.cs:

1. Replace VisualWebPartUserControl.aspx.cs with the following code

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data;
using System.Web;
using System.IO;
 
namespace VisualWebPart.VisualWebPart
{
    public partial class VisualWebPartUserControl : UserControl
    {
        DataSet ds;
        string fileName = "Excel.xls";
        protected void Page_Load(object sender, EventArgs e)
        {
            string xmlFilePath = @"D:\VisualWebPart\VisualWebPart\Inputs.xml";
            ds = new System.Data.DataSet();
            ds.ReadXml(xmlFilePath);
            DataView dv = new DataView(ds.Tables[0]);
            gvXMLValues.DataSource = dv;
            gvXMLValues.DataBind();
            gvXMLValues.Visible = true;
        }
 
        protected void btnExporttoExcel_Click(object sender, EventArgs e)
        {
            HttpResponse response = HttpContext.Current.Response;
            response.Clear();
            response.Charset = "";
            response.ContentType = "application/vnd.ms-excel";
            response.AddHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");
            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    DataGrid dg = new DataGrid();
                    dg.DataSource = ds.Tables[0];
                    dg.DataBind();
                    dg.RenderControl(htw);
                    response.Write(sw.ToString());
                    response.End();
                }
            }
        }
    }
}

Deploy the solution:

1. Build the solution
   
   
2. Hit F5.
   

Create SharePoint 2010 Visual Web Part in the SharePoint site:

1. Open the SharePoint Site.
   
   
2. Go to Site Actions => Edit the page.
   
   
3. Go to Editing Tools in the ribbon interface => Insert => Web Part.
   
   
4. In the Categories section, Select Custom and then select Visual Web Part.
   
   
   
   
5. Click on Add.
   
   
6. Visual Web Part looks like the following
   
   
   

Export to Excel:

1. Click on "Export to Excel" button.
   
   
2. File Download dialog will pop up as shown in the following.
   
   
   
   
3. Open the Excel file and we could see the xml values
   
   
   
   
4. Excel file can be saved to the local system.