·
If you have data stored in a list in SharePoint Server you
can easily export it to Microsoft Office Excel
·
By exporting your SharePoint list to Excel, you can take
advantage of the rich set of list features and formatting options in Excel
·
After exporting the list to Excel, you can choose whether to
keep it linked to the SharePoint list
Solution:
·
I have created a custom web part that will take list name and
column names separated by comma through web part custom properties rather than
hard coding the list name and column names in the code
·
Basically, I would like the user to click on export to excel
button and have the SharePoint list exported to Excel from within the code, and
not the Export to Excel button on the ribbon
·
To add list name through custom properties in the web part, please follow the
below code:
public partial class ExportSPListToExcel : WebPart
{
[WebBrowsable(true),
WebDisplayName("List Name"),
WebDescription("This accepts List
name"),
Personalizable(PersonalizationScope.Shared),
Category("Custom
Properties")]
public string ListName
{ get; set; }
}
·
To add column names through custom properties in the web part, please follow the
below code:
[WebBrowsable(true),
WebDisplayName("Column Names"),
WebDescription("This accepts Comma
seperated Column names"),
Personalizable(PersonalizationScope.Shared),
Category("Custom
Properties")]
public string
ColumnNames { get; set; }
·
The above code will allow you to give the list name and
columns names separated by comma in the web part custom properties as shown in
the below screenshot:
·
I have used the below code to export the SP List data to
Excel:
public
void ProcessRequest(HttpContext context)
{
string filename = ListName;
context.Response.Clear();
context.Response.AddHeader("content-disposition",
"attachment;filename=" + filename + ".xls");
context.Response.ContentType =
"application/ms-excel";
context.Response.Charset =
"utf-8";
StringBuilder exceltable = new
StringBuilder();
exceltable.Append("<table border=\"1\" ><tr
style=\"text-align:left\">");
String[] columns =
ColumnNames.Split(',');
foreach (string column in
columns)
{
exceltable.Append("<th>" + column.ToString() +
"</th>");
}
exceltable.Append("<th>Created
Date</th><th>Modified Date</th></tr>");
SPSecurity.RunWithElevatedPrivileges(delegate()
{
SPList lst =
SPContext.Current.Web.Lists[ListName.ToString()];
SPListItemCollection itmColl = lst.Items;
foreach (SPListItem
itm in itmColl)
{
exceltable.Append("<tr>");
/*get column
names and iterate throuh each */
foreach (string
column in columns)
{
String
value = string.Empty;
String val
= Convert.ToString(itm[column]);
if
(String.IsNullOrEmpty(val)) { }
else
{
String
newVal = String.Empty;
value =
val.ToLower().Contains(";#") ? String.Join(newVal, val.Replace('#', '
').Split(';').Where(((s, i) => i % 2 == 1))) : val;
}
exceltable.Append("<td>" + value +
"</td>");
}
/*get column
names and iterate throuh each */
exceltable.Append("<td
style=\"text-align:center\">" +
Convert.ToDateTime(itm["Created"]).ToString() +
"</td>");
exceltable.Append("<td
style=\"text-align:center\">" +
Convert.ToDateTime(itm["Modified"]).ToString() +
"</td>");
exceltable.Append("</tr>");
}
exceltable.Append("</table>");
}
}
});
context.Response.Write(exceltable);
context.Response.End();
}
·
I have added a Button in .ascx and
on click of that Button, called the above function:
protected void Button1_Click(object sender, EventArgs
e)
{
ProcessRequest(HttpContext.Current);
}
·
The below screen shot shows the
end result where the user can click on export to excel button in the page and
the SP list name and column names given in the web part custom properties will
be downloaded in excel format:
·
The below screen shot shows the
end result where the user successfully downloads the SP List to excel sheet
