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:

  • Open Visual Studio 2010.

  • Go to File => New => Project.

  • Select Visual Web Part template from the installed templates.


  • Enter the Name for the project as VisualWebPart and then click on Ok.

  • Select the local site that will be used for debugging.


  • Click on Finish.

  • In the solution explorer, right click on VisualWebPart1 and then click on Delete.


  • Right click on the solution, click on Add and then click on New Item.

  • Select Visual Web Part from the installed templates and enter the Name as VisualWebPart, then click on Ok.

  • In VisualWebPartUserControl.aspx add the GridView to display the values from the xml.

  • Add a button, on button click event the values should be exported to Excel.


VisualWebPartUserControl.ascx:

1. Replace VisualWebPartUserControl.aspx with the following

  1. <%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>

  2. <%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

  3. <%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls"Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

  4. <%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities"Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

  5. <%@ Register Tagprefix="asp" Namespace="System.Web.UI"Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>

  6. <%@ Import Namespace="Microsoft.SharePoint" %>

  7. <%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages"Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

  8. <%@ Control Language="C#" AutoEventWireup="true"CodeBehind="VisualWebPartUserControl.ascx.cs" Inherits="VisualWebPart.VisualWebPart.VisualWebPartUserControl" %>

  9. <asp:GridView ID="gvXMLValues" runat="server" EnableModelValidation="True" CellPadding="4" ForeColor="#333333" GridLines="None">

  10. <AlternatingRowStyle BackColor="White" />

  11. <EditRowStyle BackColor="#7C6F57" />

  12. <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />

  13. <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />

  14. <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />

  15. <RowStyle BackColor="#E3EAEB" />

  16. <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />

  17. </asp:GridView>

  18. <p> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

  19. <asp:Button ID="btnExporttoExcel" runat="server" onclick=" btnExporttoExcel _Click" Text="Export to Excel" Width="176px" />

  20. </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:

  • Build the solution

  • Hit F5.

Create SharePoint 2010 Visual Web Part in the SharePoint site:

  • Open the SharePoint Site.

  • Go to Site Actions => Edit the page.

  • Go to Editing Tools in the ribbon interface => Insert => Web Part.

  • In the Categories section, Select Custom and then select Visual Web Part.


  • Click on Add.

  • Visual Web Part looks like the following


Export to Excel:

  • Click on "Export to Excel" button.

  • File Download dialog will pop up as shown in the following.


  • Open the Excel file and we could see the xml values


  • Excel file can be saved to the local system.

Recent Posts

See All