0

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: enter image description here

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.

0

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);
  • Sorry for this question but I'm assuming from what that query does is that it retreives Title and ID? Looking at tutorials now and just trying to get a grasp at your code. Hope to hear from you soon. – Aspire27 Oct 1 '15 at 1:44
  • That's just an example. You can change and add yours as required. – Amal Hashim Oct 1 '15 at 1:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.