I have a custom application page that pulls data from one specific list and exports to excel. Below is the code that performs the work:
<script runat="server">
protected void Page_PreInit(object sender, EventArgs e)
{
// Override master page
this.MasterPageFile = "~/_catalogs/masterpage/minimal.master";
}
protected override void OnLoad(EventArgs e)
{
if (!Page.IsPostBack)
{
ListToExcelConvertor();
}
}
public void ListToExcelConvertor()
{
using (SPSite cursite = new SPSite("http://portal.contoso.com/"))
{
using (SPWeb curweb = cursite.OpenWeb())
{
try
{
DirectoryInfo dir = new DirectoryInfo(@"D:\SPListData");
dir.Create();
string excelFileName = string.Format(@"D:\SPListData\ExporttoExcel_{0}.xls", DateTime.Now.Ticks.ToString());
FileInfo file = new FileInfo(excelFileName);
StreamWriter streamWriter = file.CreateText();
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
Table tblData = GetListTableControl("http://portal.contoso.com/Lists/TodaysRecipes/", "TodaysRecipes");
tblData.RenderControl(htmlTextWriter);
streamWriter.Write(stringWriter.ToString());
htmlTextWriter.Close();
streamWriter.Close();
stringWriter.Close();
}
catch
{
lblMsage.Text = "SPListData directory not available";
}
}
}
}
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();
}
}
}
}
}
}
}
return tblListView;
}
What I need it to do is:
- Output specific columns that I need. For this I need List Name, Item Name, Version, Created By, Created Date, Modified By, and Modified Date.
- Pull all items from all lists in a web scope
Please see shot of desired output:
For test doc1.docx I need this to output most current to oldest versions of the file(ie Version 3, 2, 1) if possible. I know this sounds a lot but I've scoured the net for information on how to do each step and came out with nothing. Your expertise would be greatly appreciated.