There was a similar question but all of the answers did not seem to answer the question. I am using sharepoint 2010 and I have created a Custome Page that includes a webpart of a list from the sharepoint site. Instead of directing the user to the instructions for exporting a list to excel, I want to add a button by the list, on the page, that will export the list to excel. Is that possible?
2 Answers
As another option, you can also just put a link on a wiki page or wherever your webpart is, if you want to do it in the browser and not in code:
The link would be {siteurl}/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={GUID of list}&View={GUID of view}&CacheControl=1
Just fill in your siteurl and the 2 GUIDs. Use the encoded GUID format %7BE4066548%2DFA49%2D4884%2DA666%2DE85A45AFCACF%7D, for example
I am not sure about button but you can add a custom action to the list that will do the job for you nicely,
Here's an example code,
public void ListToExcelConvertor()
{
using (SPSite site = new SPSite("SharePointSiteURL"))
{
using (SPWeb web = site.OpenWeb())
{
DirectoryInfo dir = new DirectoryInfo(@"C:\List Data");
dir.Create();
FileInfo file = new FileInfo(@"C:\List Data\ExcelFileName.xls");
StreamWriter streamWriter = file.CreateText();
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
Table tblData = GetListTableControl("SharePointListURL", "SharePointListName");
tblData.RenderControl(htmlTextWriter);
streamWriter.Write(stringWriter.ToString());
htmlTextWriter.Close();
streamWriter.Close();
stringWriter.Close();
}
}
}
private static Table GetListTableControl(string strListURL, string strListName)
{
Table tblListView = new Table();
tblListView.ID = "_tblListView";
tblListView.BorderStyle = BorderStyle.Solid;
tblListView.BorderWidth = Unit.Pixel(1);
tblListView.BorderColor = Color.Silver;
using (SPSite site = new SPSite(strListURL.Trim()))
{
using (SPWeb web = site.OpenWeb())
{
SPList list = web.Lists[strListName.Trim()];
SPView wpView = list.Views["All Items"];
wpView.RowLimit = 2147483647;
SPQuery query = new SPQuery(wpView);
SPListItemCollection items = list.GetItems(query);
if (items != null && items.Count > 0)
{
DataTable tbListViewData = items.GetDataTable();
DataView dvListViewData = tbListViewData.DefaultView;
if (dvListViewData != null && dvListViewData.Count > 0)
{
tblListView.Rows.Add(new TableRow());
tblListView.Rows[0].BackColor = Color.Gainsboro;
tblListView.Rows[0].Font.Bold = true;
for (int i = 0; i < wpView.ViewFields.Count; i++)
{
tblListView.Rows[0].Cells.Add(new TableCell());
tblListView.Rows[0].Cells[i].Text = list.Fields.GetFieldByInternalName(wpView.ViewFields[i].ToString()).Title;
}
for (int i = 0; i < dvListViewData.Count; i++)
{
tblListView.Rows.Add(new TableRow());
for (int j = 0; j < wpView.ViewFields.Count; j++)
{
tblListView.Rows[i + 1].Cells.Add(new TableCell());
if (tbListViewData.Columns.Contains(wpView.ViewFields[j].ToString()))
{
tblListView.Rows[i + 1].Cells[j].Text = dvListViewData[i][wpView.ViewFields[j].ToString()].ToString();
}
}
}
}
}
}
}
}
Source & Export SharePoint List to Excel Spreadsheet Programmatically using C#
Creating Custom Actions Menu for Specific Lists in SharePoint