Thursday, April 7, 2011

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>

48 comments:

  1. Would like use this....however...I Created Feature solution using what is provided and it creates menu etc. like it suppose to. However, when the page is prompted....nothing ever happens for export.

    ReplyDelete
  2. Great post! I am see the programming coding and step by step execute the outputs.I am gather this coding more information. It's helpful for me my friend. Also great blog here with all of the valuable information you have.
    Sharepoint Training in Chennai

    ReplyDelete
  3. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
    Devops Training in pune

    Devops Training in Chennai

    Devops Training in Bangalore

    AWS Training in chennai

    AWS Training in bangalore

    ReplyDelete
  4. Really you have done great job,There are may person searching about that now they will find enough resources by your post

    Blueprism online training

    Blue Prism Training in Pune

    ReplyDelete
  5. Thanks for the good words! Really appreciated. Great post. I’ve been commenting a lot on a few blogs recently, but I hadn’t thought about my approach until you brought it up. 
    Data Science course in kalyan nagar | Data Science course in OMR
    Data Science course in chennai | Data science course in velachery
    Data science online course | Data science course in jaya nagar

    ReplyDelete
  6. Pleasant Tips..Thanks for Sharing….We keep up hands on approach at work and in the workplace, keeping our business pragmatic, which recommends we can help you with your tree clearing and pruning in an invaluable and fit way.


    angularjs Training in chennai
    angularjs-Training in pune

    angularjs-Training in chennai

    angularjs Training in chennai

    angularjs-Training in tambaram

    ReplyDelete
  7. I am obliged to you for sharing this piece of information here and updating us with your resourceful guidance. Hope this might benefit many learners. Keep sharing this gainful articles and continue updating us.
    Software Testing Training in Chennai
    Software Testing Courses in Chennai
    Software Training Institutes in Chennai
    Software Testing Training Institutes in Chennai
    Software Testing Course

    ReplyDelete
  8. Very nice post here thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.

    machine learning training in Velachery
    best training insitute for machine learning
    Android training in Chennai
    PMP Certification training in chennai

    ReplyDelete
  9. Amazing article. Your blog helped me to improve myself in many ways thanks for sharing this kind of wonderful informative blogs in live.
    Devops Training in Chennai | Devops Training Institute in Chennai

    ReplyDelete
  10. Pleasant Tips..Thanks for Sharing….We keep up hands on approach at work and in the workplace, keeping our business pragmatic, which recommends we can help you with your tree clearing and pruning in an invaluable and fit way.

    Microsoft Azure online training
    Selenium online training
    Java online training
    Java Script online training
    Share Point online training


    ReplyDelete
  11. Attend The Python training in bangalore From ExcelR. Practical Python training in bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Python training in bangalore.
    python training in bangalore

    ReplyDelete


  12. Very Interesting Post, It was so good to read and useful to improve my knowledge. Big thumbs up for making such wonderful blog page, keep blogging!!
    Machine Learning Course

    ReplyDelete
  13. Wow what a cool blog you have here! I am impressed. You really put a lot of time and effort into this, it was so good to read and useful to improve my knowledge as updated one, keep blogging!!
    Machine Learning Course


    ReplyDelete

  14. Thanks for sharing this informative content. I like this because it helps me a lot. Please update more content so that it can help me a lot in further studies!!
    Artificial Intelligence Course

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. Thanks for your information's...IT sector has an Very god opinion about Java...looking towards to explore more about JAVA visit here and Grasp it...
    Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

    ReplyDelete
  17. Resources like the one you mentioned here will be very useful to me ! I will post a link to this page on my blog. I am sure my visitors will find that very useful
    AWS training in chennai | AWS training in annanagar | AWS training in omr | AWS training in porur | AWS training in tambaram | AWS training in velachery

    ReplyDelete
  18. The post is really great to explore various content. Thanks for sharing it across. It has helped me in learning a various trend and aspects happening around
    Selenium Training in Chennai

    Selenium Training in Velachery

    Selenium Training in Tambaram

    Selenium Training in Porur

    Selenium Training in Omr

    Selenium Training in Annanagar

    ReplyDelete
  19. I am regular reader of your blog from long time,What a Beautiful post! This is so chock full of useful information I can’t wait to dig and start using my time on blogging and I am looking for and I love to post a comment that "The content of your post is awesome" Great work!
    Java Training in Chennai

    Java Training in Velachery

    Java Training in Tambaram

    Java Training in Porur

    Java Training in OMR

    Java Training in Annanagar


    ReplyDelete

  20. I appreciate this piece of useful information. Kshemkari Export Import academy one of the best leading Trade and Training Institute for import and export business, provides the best service in India with expert TeamFor more information visit our site: Export Import Certificate Online Training

    ReplyDelete
  21. Thanks for sharing this great article. It made me understand few things about this concept which I never knew before. Keep posting such great articles so that I gain from it.
    IELTS Coaching in chennai

    German Classes in Chennai

    GRE Coaching Classes in Chennai

    TOEFL Coaching in Chennai

    Spoken english classes in chennai | Communication training

    ReplyDelete
  22. Very Informative blog thank you for sharing. Keep sharing.

    Best software training institute in Chennai. Make your career development the best by learning software courses.

    rpa training in chennai
    Docker Training institute in Chennai
    devops training in chennai

    ReplyDelete
  23. I believe there are many more pleasurable opportunities ahead for
    individuals that looked at your site.
    unix course in chennai
    top software training institute in Chennai

    ReplyDelete
  24. Gangaur Realtech is a professionally managed organisation specializing in real estate services where integrated services are provided by professionals to its clients seeking increased value by owning, occupying or investing in real estate. data scientist course in kanpur

    ReplyDelete