Forum Discussion
Vishal_96707
Nimbostratus
Nimbostratussharepoint "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
8 Replies
Dave_Kelly_2025We 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
Cirrostratus
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_2025OK, 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 } } - hoolioThe 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
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. - hoolioYou'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_96707Thanks 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.- hoolioI 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
