Forum Discussion

Vishal_96707's avatar
Vishal_96707
Icon for Nimbostratus rankNimbostratus
Feb 11, 2009

sharepoint "Export to Excel" not working

We have deployed sharepoint 2007 behind F5 LTM. Sharepoint uses a feature in which export to excel which is not working for us in HTTPS environment. Here is the info which i got export to excel feature.

 

********

 

9. Leverage Excel for data management

 

Exporting data to Excel is well-supported in SharePoint and makes graphing and printing convenient (via the Print with Excel and Chart with Excel options). But it's also possible (and may often be desirable) to export data to Excel just for the sake of manageability. The Excel Export function creates an Excel Web query linking to the original data. In this way, you can create spreadsheets that will accept data, and then push that data to SharePoint.

 

This can be done by generating an Excel spreadsheet, then linking the spreadsheet to SharePoint (by using Export and Link to Excel from a Datasheet Task Pane). Once this is done, data can be entered into the spreadsheet and pushed from the spreadsheet to Excel with the Synchronize List option.

 

*********

 

Everything works fine when the VS is configured on HTTP mode. The servers are listening on port 80. The problem is with HTTPS connection only. In standard setup we are redirecting all HTTP requests to HTTPS.

 

POST /it/sites001/_vti_bin/lists.asmx HTTP/1.1

 

Content-Type: text/xml; charset=utf-8

 

SOAPAction: http://schemas.microsoft.com/sharepoint/soap/GetListAndView

 

X-Office-Version: 12.0.6020

 

User-Agent: Microsoft Office/12.0 (Windows NT 5.1; Microsoft Office Excel 12.0.6020; Pro)

 

Host: mysite.mydomain.com

 

Content-Length: 340

 

Connection: Keep-Alive

 

Cache-Control: no-cache

 

Cookie: MSOWebPartPage_AnonymousAccessCookie=80

 

 

 

 

 

{C5B5DCE4-BF3E-4715-9BD8-1A8AA36A5064}

 

{38C6017E-7CE6-4AB3-B3F6-1280E26CD806}

 

 

 

 

HTTP/1.0 302 Found

 

Location: https://mysite.mydomain.com/it/sites001/_vti_bin/lists.asmx

 

Server: BigIP

 

Connection: Keep-Alive

 

Content-Length: 0

 

If i user the uri part above and connect to HTTPS then it is not working.

 

This is the .iqy file which gets generated when you do "export to excel"

 

WEB

 

1

 

http://mysite.mydomain.com/it/sites001/_vti_bin/owssvr.dll?XMLDATA=1&List={C5B5DCE4-BF3E-4715-9BD8-1A8AA36A5064}&View={38C6017E-7CE6-4AB3-B3F6-1280E26CD806}&RowLimit=0&RootFolder=%2fit%2fsites001%2fLists%2fSite%20Provisioning%20List

 

Selection={C5B5DCE4-BF3E-4715-9BD8-1A8AA36A5064}-{38C6017E-7CE6-4AB3-B3F6-1280E26CD806}

 

EditWebPage=

 

Formatting=None

 

PreFormattedTextToColumns=True

 

ConsecutiveDelimitersAsOne=True

 

SingleBlockTextImport=False

 

DisableDateRecognition=False

 

DisableRedirections=False

 

SharePointApplication=http://intranetpp.bmc.com/it/sites001/_vti_bin

 

SharePointListView={38C6017E-7CE6-4AB3-B3F6-1280E26CD806}

 

SharePointListName={C5B5DCE4-BF3E-4715-9BD8-1A8AA36A5064}

 

RootFolder=/it/sites001/Lists/Site Provisioning List
application delivery
dev
devops
iRules
  • Dave_Kelly_2025's avatar
    Dave_Kelly_2025
    Icon for Nimbostratus rankNimbostratus
    Feb 19, 2009
    We are having the exact same issue. If we save the IQY file and edit it to have "https" for the URLs, it works correctly when opened in Excel. I'm not sure what the answer is to fix it at this point, but I would love to hear any suggestions.
  • hoolio's avatar
    hoolio
    Icon for Cirrostratus rankCirrostratus
    Feb 19, 2009
    If you can identify the request which generates the response with the errant reference to http://mysite.mydomain.com/it/sites001/_vti_bin/owssvr.dll?XMLDATA..., you could use a stream profile and STREAM::expression basd iRule to rewrite the references to https://... You can check the STREAM::expression wiki page for examples.

     

     

    I would also think that there is a method for instructing the application that it's being proxied by an SSL offloading device and to use https:// references even though it's answering on http://. The method for doing this in Exchange is to insert an HTTP header "Front-End-Https: On".

     

     

    Aaron
  • Dave_Kelly_2025's avatar
    Dave_Kelly_2025
    Icon for Nimbostratus rankNimbostratus
    Feb 19, 2009
    OK, I have fixed this with the following iRule. I am not sure why we need to de-chunk the HTTP_REQUEST. Can anyone tell me? I tried it with that part commented out, and it worked fine as well, so I'm guessing it's a request size issue...I suspect that the .IQY files being sent by Sharepoint wouldn't be affected by it, but who knows. At any rate, it's in there, but you might be able to comment it out for this case. 

     
 when HTTP_REQUEST { 
    Don't allow data to be chunked 
   if { [HTTP::version] eq "1.1" } { 
     if { [HTTP::header is_keepalive] } { 
         HTTP::header replace "Connection" "Keep-Alive" 
     } 
     HTTP::version "1.0" 
   } 
 } 
  
 when HTTP_RESPONSE { 
    Only check responses that are a text/x-ms-iqy content type  
   if { [HTTP::header "Content-Type"] starts_with "text/x-ms-iqy" } { 
      Get the content length so we can request the data to be 
      processed in the HTTP_RESPONSE_DATA event. 
     if { [HTTP::header exists "Content-Length"] } { 
       set content_length [HTTP::header "Content-Length"] 
     } else { 
       set content_length 1000000000 
     } 
     if { $content_length > 0 } { 
        HTTP::collect $content_length 
     } 
   } 
 } 
  
 when HTTP_RESPONSE_DATA { 
    Here you define what you want to find in the payload 
   set find "SharePointApplication=http://"  
  
    And here's what you'll be replacing it with 
   set replace "SharePointApplication=https://" 
  
   set payload [HTTP::payload] 
  
    Run the regsub to make all the replacements (add -nocase for case insensitivity) 
   if {[regsub -all $find $payload $replace new_response] > 0} { 
     HTTP::payload replace 0 [HTTP::payload length] $new_response 
   } 
 }
  • hoolio's avatar
    hoolio
    Icon for Cirrostratus rankCirrostratus
    Feb 19, 2009
    The idea is that you prevent response chunking (which sets no explicit content length) by making the request to the server HTTP 1.0.

     

     

    It would be more efficient to use a stream profile and iRule to rewrite the response versus the payload collection though.

     

     

    The stream expression would be:

     

     

    STREAM::expression "@SharePointApplication=http://@SharePointApplication=https://@"

     

     

    Aaron
  • Dave_Kelly_2025's avatar
    Dave_Kelly_2025
    Icon for Nimbostratus rankNimbostratus
    Feb 19, 2009

    Posted By hoolio on 02/19/2009 9:16 AM

     

    It would be more efficient to use a stream profile and iRule to rewrite the response versus the payload collection though.

     

     

     

     

    Admittedly, I know very little about stream profiles, but doesn't a stream profile examine everything, regardless of content type? The thing I like about the iRule is that I can restrict to just .IQY files, without causing problems with other file types. If I'm using a stream profile, and someone visits and HTML documentation page I've created that has the phrase in it, won't it modify that HTML content as well?

     

     

    The other side of the coin is that the .IQY files are not very heavily used on our system, but they do need the modification, so I don't know if that has any bearing on efficiency or not.
  • hoolio's avatar
    hoolio
    Icon for Cirrostratus rankCirrostratus
    Feb 19, 2009
    You're correct about the stream profile. If you add a stream profile to the VIP all request and response payloads will be checked. I've opened a case with F5 and asked them to support more options so you can specify in the stream profile configuration whether you want to check requests and/or responses. It would also be ideal if you could specify response content types to check.

    As it is, it's important to use an iRule to specifically enable the stream filter only when you want it. You can add a blank stream profile to the VIP and then use a rule like this to configure it: 

     
  when HTTP_RESPONSE {  
  
     Only check responses that are a text/x-ms-iqy content type 
    if { [HTTP::header "Content-Type"] starts_with "text/x-ms-iqy" } { 
  
        Configure the find/replace strings 
       STREAM::expression "@SharePointApplication=http://@SharePointApplication=https://@" 
  
        Enable the stream filter 
       STREAM::enable 
  
    } else { 
  
        Response isn't a type we want to check so disable the stream filter 
       STREAM::disable 
    } 
 }

    Aaron
  • Vishal_96707's avatar
    Vishal_96707
    Icon for Nimbostratus rankNimbostratus
    Feb 24, 2009
    Thanks for all you response. I am a newbie on F5 and very less knowledge on sharepoint (i am taking care of Network stuff and the app team is different). Can someone please explain it in layman's term? Apologies for silly request.
  • hoolio's avatar
    hoolio
    Icon for Cirrostratus rankCirrostratus
    Mar 11, 2009
    I think Sharepoint's method for configuring external host, port and/or URI aliases is to use Alternate Access Mappings:

     

     

    What every SharePoint administrator needs to know about Alternate Access Mappings (Part 1 of 3)

     

    http://blogs.msdn.com/sharepoint/archive/2007/03/06/what-every-sharepoint-administrator-needs-to-know-about-alternate-access-mappings-part-1.aspx (Click here)

     

     

    Plan alternate access mappings (Windows SharePoint Services)

     

    http://technet.microsoft.com/en-us/library/cc288609.aspx (Click here)

     

     

    I haven't tested this, but I'd be interested in hearing about your experiences if you try it.

     

     

    Aaron