Body:

Published: 28/04/2011 4:22 p.m.

Problem: I can’t export a list to a excel spreadsheet file from a SharePoint 2007/WSS3.0 site is using SSL.  SP2010 exports https lists correctly. 
Initial Hypothesis: File is being exported however, when opening the *.iqy file, the error “Excel cannot connect to the SharePoint list.” pops up. 

Resolution: This is a bug with 2 workarounds,

• Option1: From excel connect to the list. or
• Option 2: Save the *.iqy file, open it using notepad, chnage the url to use https instead of http (it occurs twice in the iqy file.

Body:

Preliminary steps

​

 

Excel VSTO application

1. Create a VSTO workbook project using Visual Studio 2010:

 

2.  Create a helper class called SPHelper in a project folder called Helpers

 

3. Add a type in your project corresponding to the Customer structure in a class called CommonTypes.cd in a Common directory:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

namespace DuetExcelWorkbook.Common

{

    public struct CustomerType

    {

        public string FirstLineName;

        public string CountryCode;

        public string AddressregionCode;

        public string AddresscityName;

 

    }

}

 

 

4.  Add the Client OM assembly:

-      C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\

-          Add  the two following assembly references to your project:

o   Microsoft.SharePoint.Client.dll

o   Microsoft.SharePoint.Client.Runtime.dll

 -          Create a class called SPHelpers.cs in Common:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.SharePoint.Client;

using System.Linq.Expressions;

using System.Xml;

 

namespace DuetExcelWorkbook.Helpers

{

    public static class SPHelper

    {

        public static List<CustomerType> GetCustomerList(string TargetSiteUrl, string TargetListName)

        {

            List<CustomerType> CustomerList = new List<CustomerType>();

 

            try

            {

                ClientContext clientContext = new ClientContext(TargetSiteUrl);

 

                List externalList = clientContext.Web.Lists.GetByTitle(

                    TargetListName);

 

                // To properly construct the CamlQuery and

                // ClientContext.LoadQuery,

                // we need some View data of the Virtual List.

                // In particular, the View will give us the CamlQuery

                // Method and Fields.

                clientContext.Load(

                    externalList.Views,

                    viewCollection => viewCollection.Include(

                        view => view.ViewFields,

                        view => view.HtmlSchemaXml));

 

                // This tells us how many list items we can retrieve.

                clientContext.Load(clientContext.Site,

                    s => s.MaxItemsPerThrottledOperation);

 

                clientContext.ExecuteQuery();

 

                // Let’s just pick the first View.

                View targetView = externalList.Views[0];

                string method = ReadMethodFromViewXml(

                    targetView.HtmlSchemaXml);

                ViewFieldCollection viewFields = targetView.ViewFields;

 

                CamlQuery vlQuery = CreateCamlQuery(

                    clientContext.Site.MaxItemsPerThrottledOperation,

                    method,

                    viewFields);

 

                Expression<Func<ListItem, object>>[] listItemExpressions =

                    CreateListItemLoadExpressions(viewFields);

 

                ListItemCollection listItemCollection =

                    externalList.GetItems(vlQuery);

 

                // Note: Due to limitation, you currently cannot use

                // ClientContext.Load.

                //       (you’ll get InvalidQueryExpressionException)

                IEnumerable<ListItem> resultData = clientContext.LoadQuery(

                    listItemCollection.Include(listItemExpressions));

 

                clientContext.ExecuteQuery();

 

                foreach (ListItem li in resultData)

                {

                    // Now you can use the ListItem data!

                    CustomerType customer = new CustomerType();

 

                    customer.FirstLineName = li["FirstLineName"].ToString();

                    //customer.AddresscityName = li["AddresscityName"].ToString();

                    //customer.AddressregionCode = li["AddressregionCode"].ToString();

                    customer.CountryCode = li["CountryCode"].ToString();

 

                    CustomerList.Add(customer);

                    Console.WriteLine("First Name: {0} Country : {1} \n", li["FirstLineName"].ToString(), li["CountryCode"].ToString());

                    // Note: In the CamlQuery, we specified RowLimit of

                    // MaxItemsPerThrottledOperation.

                    // You may want to check whether there are other rows

                    // not yet retrieved.               

                }

            }

            catch (Exception ex)

            {

 

                throw ex;

            }

 

            return CustomerList;

        }

 

        /// <summary>

        /// Parses the viewXml and returns the Method value.

        /// </summary>       

        private static string ReadMethodFromViewXml(string viewXml)

        {

            XmlReaderSettings readerSettings = new XmlReaderSettings();

            readerSettings.ConformanceLevel = ConformanceLevel.Fragment;

 

            XmlReader xmlReader = XmlReader.Create(

                new StringReader(viewXml), readerSettings);

            while (xmlReader.Read())

            {

                switch (xmlReader.NodeType)

                {

                    case XmlNodeType.Element:

                        if (xmlReader.Name == "Method")

                        {

                            while (xmlReader.MoveToNextAttribute())

                            {

                                if (xmlReader.Name == "Name")

                                {

                                    return xmlReader.Value;

                                }

                            }

                        }

                        break;

                }

            }

 

            throw new Exception("Unable to find Method in View XML");

        }

 

        /// <summary>

        /// Creates a CamlQuery based on the inputs.

        /// </summary>       

        private static CamlQuery CreateCamlQuery(

            uint rowLimit, string method, ViewFieldCollection viewFields)

        {

            CamlQuery query = new CamlQuery();

 

            XmlWriterSettings xmlSettings = new XmlWriterSettings();

            xmlSettings.OmitXmlDeclaration = true;

 

            StringBuilder stringBuilder = new StringBuilder();

            XmlWriter writer = XmlWriter.Create(

                stringBuilder, xmlSettings);

 

            writer.WriteStartElement("View");

 

            // Specifies we want all items, regardless of folder level.

            writer.WriteAttributeString("Scope", "RecursiveAll");

 

            writer.WriteStartElement("Method");

            writer.WriteAttributeString("Name", method);

            writer.WriteEndElement();  // Method

 

            if (viewFields.Count > 0)

            {

                writer.WriteStartElement("ViewFields");

                foreach (string viewField in viewFields)

                {

                    if (!string.IsNullOrEmpty(viewField))

                    {

                        writer.WriteStartElement("FieldRef");

                        writer.WriteAttributeString("Name", viewField);

                        writer.WriteEndElement();  // FieldRef

                    }

                }

                writer.WriteEndElement();  // ViewFields

            }

 

            writer.WriteElementString(

                "RowLimit", rowLimit.ToString(CultureInfo.InvariantCulture));

 

            writer.WriteEndElement();  // View

 

            writer.Close();

 

            query.ViewXml = stringBuilder.ToString();

 

            return query;

        }

 

        /// <summary>

        /// Returns an array of Expression used in

        /// ClientContext.LoadQuery to retrieve

        /// the specified field data from a ListItem.       

        /// </summary>       

        private static Expression<Func<ListItem, object>>[]

            CreateListItemLoadExpressions(

            ViewFieldCollection viewFields)

        {

            List<Expression<Func<ListItem, object>>> expressions =

                new List<Expression<Func<ListItem, object>>>();

 

            foreach (string viewFieldEntry in viewFields)

            {

                // Note: While this may look unimportant,

                // and something we can skip, in actuality,

                //       we need this step.  The expression should

                // be built with local variable.               

                string fieldInternalName = viewFieldEntry;

 

                Expression<Func<ListItem, object>>

                    retrieveFieldDataExpression =

                    listItem => listItem[fieldInternalName];

 

                expressions.Add(retrieveFieldDataExpression);

            }

 

            return expressions.ToArray();

        }

    }

5. Now we will code the user interface, the part of the code that will call the Client OM class.

Add a Ribbon on you project:

 

 

Modify the following properties for the Ribbon:

Label: Duet Enterprise
Name: tabDuet

 Add a button on the ribbon and modifies the following properties: 

ControlSize: RibbonControlSizeLarge
Label: Customers
OfficeImageId: SlideMasterChartPlacehoderInsert

 6.  Add the following code in the buttonCustomers_Click() function:

private void buttonCustomers_Click(object sender, RibbonControlEventArgs e)

        {

            List<CustomerType> result = new List<CustomerType>();

 

            try

            {

                result = SPHelper.GetCustomerList("http://litware", "SAPCustomers");

 

                //string[] Names = new string[];

                //string[] Countries;

                List<string> Names = new List<string>();

                List<string> Countries = new List<string>();

                int counter = 0;

 

                // Fill the collections

 

                foreach (var item in result)

                {

                    // Add the customers in the ListView

 

                    Names.Add(item.FirstLineName);

                    Countries.Add(item.CountryCode);

                         

                    counter++;

 

                }

 

                // Create a data table with two columns.

                System.Data.DataTable table = new DataTable();

                DataColumn column1 = new DataColumn("Name", typeof(string));

                DataColumn column2 = new DataColumn("Country", typeof(string));

                table.Columns.Add(column1);

                table.Columns.Add(column2);

 

                // Add the four rows of data to the table.

                DataRow row;

                for (int i = 0; i < counter; i++)

                {

                    row = table.NewRow();

                    row["Name"] = Names[i];

                    row["Country"] = Countries[i];

                    table.Rows.Add(row);

                }

 

                Microsoft.Office.Tools.Excel.ListObject list1 =

                    Globals.Sheet1.Controls.AddListObject(Globals.Sheet1.Range["A1", "B4"], "list1");

 

                // Bind the list object to the table.

                list1.SetDataBinding(table);

 

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

                throw;

            }

 

        }

 

7. Run Visual Studio

Note: make sure you run Visual Studio using the correct user identity. The user must be able to have appropriate read permission on the Customers list.

Click on the Duet Enterprise Ribbon and click on the Customers button. You will see the customers list added to Microsoft Excel:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Category: Duet; Excel Services

Published: 2/3/2011 9:00 AM