<button onclick="javascript:window.open('{site Name}/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={List GUID}&View={View GUID}&CacheControl=1');"><img class="icon" src="{sitename/SiteAssets/excelicon.png" alt=""/>Export to Excel</button>
private static void ExporttoExcel(DataTable table)
{
string fileName = HttpContext.Current.Request.Params["FileName"];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
//sets font
HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
HttpContext.Current.Response.Write("<BR><BR><BR>");
//sets the table border, cell spacing, border color, font of the text, background, foreground, font height
HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
//am getting my grid's column headers
int columnscount = table.Columns.Count;
for (int j = 0; j < columnscount; j++)
{ //write in new column
HttpContext.Current.Response.Write("<Td>");
//Get column headers and make it as bold in excel columns
HttpContext.Current.Response.Write("<B>");
HttpContext.Current.Response.Write(table.Columns[j].ColumnName);
HttpContext.Current.Response.Write("</B>");
HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
foreach (DataRow row in table.Rows)
{//write in new row
HttpContext.Current.Response.Write("<TR>");
for (int i = 0; i < table.Columns.Count; i++)
{
HttpContext.Current.Response.Write("<Td>");
HttpContext.Current.Response.Write(row[i].ToString());
HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
}
HttpContext.Current.Response.Write("</Table>");
HttpContext.Current.Response.Write("</font>");
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
SPGridView
protected override void OnPreRender(EventArgs e)
{
base.OnPreRender(e);
SPSite oSiteCollection = SPContext.Current.Site;
SPWeb oWeb = SPContext.Current.Web;
SPList oList = oWeb.Lists["MyList"];
SPQuery query = new SPQuery();
query.Query = "<Where><Eq><FieldRef Name='Author' /><Value Type='User'>System Account</Value></Eq></Where>";//here you can filter your data
SPListItemCollection Mylistcollection = oList.GetItems(query);
DataTable dt = Mylistcollection.GetDataTable();
myGridView.DataSource = dt;
myGridView.DataBind();
}
public void ExportExcel(SPGridView mygridview)
{
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
HttpContext.Current.Response.ContentType = "application/excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
mygridview.RenderControl(htw);
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
CSV
private void ExportToCSV(HttpContext context, SPListItemCollection dtData)
{
context.Response.ContentType = "Application/x-msexcel";
context.Response.AddHeader("content-disposition", "attachment;filename=" + dtData.List.Title.Replace(" ", "") + ".csv");
context.Response.Write(GetCSV(dtData));
context.Response.End();
}
private string GetCSV(SPListItemCollection dataTable)
{
//create the stringbuilder that would hold the data
StringBuilder sb = new StringBuilder();
//check if there are columns in the datatable
if (dataTable.Count != 0)
{
//loop thru each of the columns for headers
foreach (SPField column in dataTable.Fields)
{
//append the column name followed by the separator
if (!column.Hidden)
{
sb.Append(column.Title.Replace(",", "") + ',');
}
}
//append a carriage return
sb.Append("\r\n");
//loop thru each row of the datatable
foreach (SPListItem row in dataTable)
{
foreach (SPField column in dataTable.Fields)
{
//append the column name followed by the separator
if (!column.Hidden)
{
sb.Append(Convert.ToString(row[column.InternalName]).Replace(",", "") + ',');
}
}
//append a carriage return
sb.Append("\r\n");
}
}
return sb.ToString();
}
#endregion
}
}