using Microsoft.SharePoint;
using System.Data;
using System.IO;
using System.Drawing;
using System.Text.RegularExpressions;
using System.Text;
//Create a Table
Table tblListView = new Table();
tblListView.ID = "_tblListView";
tblListView.BorderStyle = BorderStyle.Solid;
tblListView.BorderWidth = Unit.Pixel(1);
tblListView.BorderColor = Color.Silver;
//tblListView.GridLines = GridLines.Both;
using (SPSite site = new SPSite(SPContext.Current.Web.Url.Trim()))
{
using (SPWeb web = site.OpenWeb())
{
//Get the List
SPList list = web.Lists[ListNameToBeExported.Trim()];
//Get the Sharepoint View
SPView wpView = list.Views[ViewNameToBeExported.Trim()];
wpView.RowLimit = 2147483647;
SPQuery query = new SPQuery(wpView);
//Get the Listitems for the Sharepoint View
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].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;
tblListView.Rows[0].Cells[i].BackColor = Color.DodgerBlue;
tblListView.Rows[0].Cells[i].Style.Add(HtmlTextWriterStyle.TextAlign, "Center");
}
int k = 0; //variable to color alternate rows in table
for (int i = 0; i < dvListViewData.Count; i++)
{
tblListView.Rows.Add(new TableRow());
if (k == 0)
k = 1;
else
k = 0;
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();
string HTMLCode = dvListViewData[i][wpView.ViewFields[j].ToString()].ToString();
//To remove line breaks
HTMLCode = HTMLCode.Replace("
", " ");
HTMLCode = HTMLCode.Replace("\r\n", " ");
HTMLCode = HTMLCode.Replace("\r", " ");
HTMLCode = HTMLCode.Replace("\n", " ");
//to remove list numbers so that line breaks won't appear in th espreadsheet
HTMLCode = HTMLCode.Replace("
", " ");
HTMLCode = HTMLCode.Replace("
", " ");
HTMLCode = HTMLCode.Replace("
", " "); HTMLCode = HTMLCode.Replace("
", " "); HTMLCode = HTMLCode.Replace("
HTMLCode = HTMLCode.Replace("
", " ");
tblListView.Rows[i + 1].Cells[j].Text = HTMLCode;
if (k == 0)
tblListView.Rows[i + 1].Cells[j].BackColor = Color.LightSteelBlue;
else
tblListView.Rows[i + 1].Cells[j].BackColor = Color.AliceBlue;
}
}
}
}
}
}
}
try
{
Response.ContentType = "application/vnd.ms-excel 8.0";
Response.Charset = "";
Response.ContentEncoding = System.Text.Encoding.Default;
this.EnableViewState = false;
System.IO.StringWriter ObjWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter ObjHtmlTextWriter = new System.Web.UI.HtmlTextWriter(ObjWriter);
tblListView.RenderControl(ObjHtmlTextWriter);
Response.Write(ObjWriter.ToString());
string strhtml = ObjHtmlTextWriter.ToString();
//get File name from View name and current DateTime
string currentDtaeTime = DateTime.Now.ToString("MM-dd-yyyy-hh-mm-ss");
string FileName = ViewNameToBeExported + "_" + currentDtaeTime;
string FilenameWithExtension = FileName + ".xls";
Response.AppendHeader("content-disposition", "attachment;filename=" + FilenameWithExtension);
Response.Flush();
Response.End();
}
catch (System.Exception ex)
{
string strMsg = ex.Message;
}