Exam 70-573 Study Guide

I found this blog usefull for Microsoft SharePoint 2010, Application Development exam:


http://blog.beckybertram.com/Lists/Exam%2070573%20Study%20Guide/AllItems.aspx 

Export to excel and lookup fields problem

When you use lookup fields or any custom fields based on lookups, and you use the "Export to Spreadsheet" in lists or documents library Action Menu, the lookup columns are exported with their seprator characters and IDs (for example Book1#;12 instead of just Book1)  which is very unpleasant for end users. For OOT lookup fields if you don't select "Allow Multiple Values" you cannot see the problem otherwise it exists. It seems that it is the way that these things works.Because of not finding any workaround or solution I added a custom "Export to Excel" menu option to export the list items to excel file without additional unwanted information. Below is the code:

elements.xml

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
    <!-- Add the action to the List Toolbar Actions Menu Dropdown -->
    <CustomAction Id="Vana.ExportListActionsToolbar"
      RegistrationType="List" 
      GroupId="ActionsMenu"
      Location="Microsoft.SharePoint.StandardMenu"
      Sequence="1000"
      ImageUrl="/_layouts/images/menuexportexcel.png"
      Title="Export List To Excel ">
    <UrlAction Url="javascript:function ExportView(){var site='{SiteUrl}';var list='{ListId}';var view=ctx.view;window.open(site + '/_layouts/ExportToExcel/ExportList.aspx?list=' + list + '&amp;view=' + view, 'Download' ,'resizable=0,width=250,height=250' );}ExportView();"/>
    </CustomAction>
</Elements>

feature.xml

<?xml version="1.0" encoding="utf-8" ?>
<Feature Id="769826dd-9dd2-11db-96ca-005056c00008"
Title="Export List To Excel"
Description="This feature adds a Export To Excel command in the Actions menu for Windows SharePoint Services lists."
Version="1.0.0.0"
Scope="Web"
xmlns="http://schemas.microsoft.com/sharepoint/">
    <ElementManifests>
        <ElementManifest Location="elements.xml" />
    </ElementManifests>
</Feature>

ExportToExcel.aspx 
 
<%@ Page Language="C#" MasterPageFile="~/_layouts/application.master" Inherits="Microsoft.SharePoint.WebControls.LayoutsPageBase"
  EnableViewState="true" EnableViewStateMac="false" %>
<%@ Assembly Name="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Assembly Name="WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>
<%@ Import Namespace="Microsoft.SharePoint" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.IO.Packaging" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Xml.Serialization" %>
<%@ Import Namespace="Microsoft.SharePoint.ApplicationPages" %>
<%@ Import Namespace="Microsoft.SharePoint.WebPartPages" %>
<%@ Import Namespace="Microsoft.SharePoint.Administration" %>
<%@ Import Namespace="System.Xml.Xsl" %>

<%@ Register TagPrefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register TagPrefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<script runat="server">
   protected override void OnLoad(EventArgs e)
     {

       // List GUID
       string listId = "";
       
         listId = Page.Request.QueryString["list"];

      // Current View GUID
       string viewID = string.Empty;
       viewID = Page.Request.QueryString["view"];

         if (listId == null)
         {
             Page.Response.Write("<p>The list ID parameter ('list') does not exist.</p>");
         }
         else
         {
             try
             {
                 using (SPWeb web = SPControl.GetContextWeb(Context))
                 {

                     SPList list = web.Lists[new Guid(listId)];
                     SPView view = list.Views[new Guid(viewID)];
                     SPViewFieldCollection viewfield = list.Views[new Guid(viewID)].ViewFields;
                     bool removeID = false;
                     bool removeCreated = false;
                       
                     SPQuery query = null;
                     if (viewID == null || string.IsNullOrEmpty(viewID))
                     {
                           query = new SPQuery();
                         query.Query = list.DefaultView.Query;
                     }
                     else
                     {
                         query = new SPQuery();
                         query.Query = list.Views[new Guid(viewID)].Query;
                         query.ViewFields = viewfield.SchemaXml;
                         string VFSchema = viewfield.SchemaXml;                     
                         if (!VFSchema.Contains("<FieldRef Name=\""+list.Fields["شناسه"].InternalName+"\" />"))
                         {
                             removeID = true;
                         }

                         if (!VFSchema.Contains("<FieldRef Name=\"" + list.Fields["ایجاد شده"].InternalName + "\" />"))
                         {
                             removeCreated = true;
                         }
                     }
                     DataGrid dgTemp = new DataGrid();
                     DataTable dtTemp = list.GetItems(query).GetDataTable();
                   
                     //Remove Unwanted Columns
                     if(removeID)
                        dtTemp.Columns.Remove("ID");
                     if(removeCreated)
                        dtTemp.Columns.Remove("Created");
                     
                     //Replace Internal Names with Normal Names
                     foreach (DataColumn col in dtTemp.Columns)
                     {
                         SPField field = list.Fields.GetFieldByInternalName(col.ColumnName);
                         col.ColumnName = field.Title;
                     }
                   
                     dgTemp.DataSource = dtTemp;
                     dgTemp.DataBind();
                     dgTemp.HeaderStyle.Font.Bold = true;
                     dgTemp.HeaderStyle.Font.Name = "Tahoma";// new System.Drawing.Font("Arial", 8.5F, System.Drawing.GraphicsUnit.Pixel);
                     dgTemp.HeaderStyle.Font.Size = FontUnit.Point(10);
                     dgTemp.HeaderStyle.BackColor = System.Drawing.ColorTranslator.FromHtml("#4F81BD");
                     dgTemp.BackColor = System.Drawing.ColorTranslator.FromHtml("#B8CCE4");
                     dgTemp.AlternatingItemStyle.BackColor = System.Drawing.ColorTranslator.FromHtml("#DBE5F1");
                     dgTemp.ItemStyle.Font.Name = "Tahoma";
                     dgTemp.ItemStyle.Font.Size = FontUnit.Point(9);
                     dgTemp.Attributes.Add("style", "dir:rtl");
                     Response.ContentType = "application/vnd.ms-excel;charset=windows-1251 name=" + "Book" + ".xls";
                     Response.AddHeader("content-disposition", "attachment;filename=" + "Book" + ".xls");
                   
                     Response.Charset = "UTF-8";
                     this.EnableViewState = false;
                     System.IO.StringWriter tw = new StringWriter();
                     System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(tw);
                     dgTemp.RenderControl(hw);
                   
                     //to Omit ;#ID from Lookup Fields 
                     string twTemp = Regex.Replace(tw.ToString(), ">[0-9]+;#", ">");
                     twTemp = Regex.Replace(twTemp, ";#[0-9]+;#", ", ");
                     Response.Write(twTemp);
                   
                     try
                     {

                         Response.End();
                     }
                     catch (Exception ex)
                     {
                     }
                 }
             }
             catch (Exception ex)
             {
                 //Response.Write("Excption : " + ex.Message + " " + ex.StackTrace);
             }
         }         
     }
 </script>
 <asp:Content ID="Main" ContentPlaceHolderID="PlaceHolderMain" runat="server">
  <table border="0" cellpadding="2" cellspacing="0" style="width: 100%; font-size: 9pt">
     <tr>
      <td valign="top" style="width: 120px" colspan="2">
        <asp:Label ID="ErrorMessage" runat="Server" ></asp:Label>
        </td>
    </tr>  
     </table>
 
</asp:Content>
<asp:Content ID="PageTitle" ContentPlaceHolderID="PlaceHolderPageTitle" runat="server">
  ارسال به Excel
</asp:Content>
<asp:Content ID="PageTitleInTitleArea" ContentPlaceHolderID="PlaceHolderPageTitleInTitleArea"
  runat="server">
    ارسال به Excel
</asp:Content>