How to Output Specific Columns to Excel

Question

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.

Answer 1

You can use CAML query and define ViewFields.

Example

SPQuery query = new SPQuery();
qry.Query = "<Where><Geq><FieldRef Name='ID' /><Value Type='Text'>1</Value></Geq></Where>";
qry.ViewFields = <FieldRef Name='Title'/><FieldRef Name='ID'/>
items = list.GetItems(qry);