JQuery for Everyone: Dynamically Sizing Excel Web Parts
Before the holidays, another EndUserSharePoint author, Toni Frankola, challenged me to “…create a script to resize EWA [Excel Web Access] web part automatically to match content inside.” That sounded fun and easy–it wasn’t easy.
Anyone with MOSS Enterprise (Excel Services enabled) can replicate the problem. Create a new site collection with the Collaboration Portal template. In the Reports site, open the Sample Dashboard. On anything less than 1280×1024 resolution, you will probably see a horizontal scrollbar in your Excel Web Access web part:
The same thing happens on the y-axis if you create a spreadsheet with rows beyond the normal page height (100%).
To force the web part to dynamically resize itself based on its content’s height and width, I needed to understand how the page works. The Dashboard page first loads the DOM (Document Object Model) then loads ExcelRenderer.aspx pages inside iframes. Normally, my jQuery-fu tweaks the UI on the document.ready event. In this case, the DOM can become ready before the Excel data renders–before we know the width of the content.
In my research, I found only iframes with the scriptforiframecontent attribute will load content. When the web part POST completes or a valid cookie with the same POST values is found, the attribute postedbackalready appears (with the value “SureDid”). At that point, the inner document begins loading.
To get information about the objects loaded in an iframe, jQuery must use the .contents() method. However, every time I tried to get the inner document’s width on document.ready, the results were undefined. So, I created a series of functions using setTimeout–a JavaScript method used to run on a set delay.
The final script:
- Checks the page for web parts with iframe content to load.
- Binds an event to each iframe web part.
- Triggers the bound event to check for a completed postback.
- Waits for the inner document to render.
- Returns the height and width of the iframe’s content.
- Sets the web part’s new dimensions to fit the inner content without a scrollbar.
The result may not seem impressive by these screenshots, but without scrollbars the page appears more user-friendly. The dashboard works like a dashboard should–without dragging or scrolling to see hidden data.
Thanks for the challenge, Toni.
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js" type="text/javascript"></script>
<script type="text/javascript">
function triggerMe(obj) {//trigger the bound event
$(obj).trigger("ewaLoaded");
}
function ewaSetSize(obj) {//set proper width
var e = $(obj).find("iframe:first").contents().find("table.ewrnav-invisibletable-nopadding:last");
var w = e.width();
var h = e.height();
if (w == 0 || w == undefined) { //loop if content not ready
setTimeout("ewaSetSize('"+obj+"')",1000);
}else{ //set width in three places of wp
w += 60; //add x-axis buffer space (configurable)
h += 100; //add y-axis buffer
$(obj).find("table:first> tbody:first> tr:eq(1)> td> div> div").width(w)
.find("table:first> tbody:first> tr:eq(1)> td> div").height(h);
}
}
function bindMe(obj) { //bind event to the web part
$(obj).bind("ewaLoaded",function(e) {
var b = $(e.target).find("iframe:first").attr("postedbackalready");
if (b==undefined) { //trigger this later
setTimeout("triggerMe('"+obj+"')",1000);
}else{ //try to set width now
ewaSetSize(obj);
}
}).trigger("ewaLoaded"); //trigger when we bind
}
$(function() { //find web parts with ewa content to load
$("td[id^='MSOZoneCell_WebPart']").each(function(i,e) {
var findIframe = $(e).find("iframe:first");
if (findIframe && findIframe.attr("scriptforiframecontent")) {
bindMe("#"+e.id); //bind an event we can trigger later
}
});
});
</script>
- JQuery for Everyone: Accordion Left Nav
- JQuery for Everyone: Print (Any) Web Part
- JQuery for Everyone: HTML Calculated Column
- JQuery for Everyone: Dressing-up Links Pt1
- JQuery for Everyone: Dressing-up Links Pt2
- JQuery for Everyone: Dressing-up Links Pt3
- JQuery for Everyone: Cleaning Windows Pt1
- JQuery for Everyone: Cleaning Windows Pt2
- JQuery for Everyone: Fixing the Gantt View
- JQuery for Everyone: Dynamically Sizing Excel Web Parts
- JQuery for Everyone: Manually Resizing Web Parts
- JQuery for Everyone: Total Calculated Columns
- JQuery for Everyone: Total of Time Differences
- JQuery for Everyone: Fixing Configured Web Part Height
- JQuery for Everyone: Expand/Collapse All Groups
- JQuery for Everyone: Preview Pane for Multiple Lists
- JQuery for Everyone: Preview Pane for Calendar View
- JQuery for Everyone: Degrading Dynamic Script Loader
- JQuery for Everyone: Force Checkout
- JQuery for Everyone: Replacing [Today]
- JQuery for Everyone: Whether They Want It Or Not
- JQuery for Everyone: Linking the Attachment Icon
- JQuery for Everyone: Aspect-Oriented Programming with jQuery
- JQuery for Everyone: AOP in Action - loadTip Gone Wild
- JQuery for Everyone: Wiki Outbound Links
- JQuery for Everyone: Collapse Text in List View
- JQuery for Everyone: AOP in Action - Clone List Header
- JQuery for Everyone: $.grep and calcHTML Revisited
- JQuery for Everyone: Evolution of the Preview
- JQuery for Everyone: Create a Client-Side Object Model
- JQuery for Everyone: Print (Any) Web Part(s) Plugin
- JQuery for Everyone: Minimal AOP and Elegant Modularity
- JQuery for Everyone: Cookies and Plugins
- JQuery for Everyone: Live Events vs. AOP
- JQuery for Everyone: Live Preview Pane
- JQuery for Everyone: Pre-populate Form Fields
- JQuery for Everyone: Get XML List Data with OWSSVR.DLL (RPC)
- Use Firebug in IE
- JQuery for Everyone: Extending OWS API for Calculated Columns
- JQuery for Everyone: Accordion Left-nav with Cookies Speed Test
- JQuery for Everyone: Email a List of People with OWS
- JQuery for Everyone: Faster than Document.Ready
- jQuery for Everyone: Collapse or Prepopulate Form Fields
- jQuery for Everyone: Hourly Summary Web Part
- jQuery for Everyone: "Read More..." On a Blog Site
- jQuery for Everyone: Slick Speed Test
- jQuery for Everyone: The SharePoint Game Changer
- JQuery For Everyone: Live LoadTip
Interesting. Might it be possible to do something similar with a generic Page Viewer web part, or more specifically a SQL Reporting Services web part?
I wrote my own Page Viewer web part, which is connectable so I could connect other web parts to it and substitute the values into a URL. I primarily use it to display Reporting Services reports, creating a URL with parameters to generate a report that displays in the iframe.
But it’s sometimes not very user friendly because you end up with scrollbars (in the iframe) within scrollbars (on the page) because it needs a fixed height for the iframe. I’d like to adjust the iframe to accomodate the size of the displayed report and have only the outer scrollbars on the page itself.
I tried messing with this a long time ago. I had some code that would resize an iframe to its contents, but ran into security problems due to cross site scripting.
and again…
very nice done!
@JoeD
Browsers prevent access to inner documents from another host. But I have a solution! I’ll post it today.
Read the post for page viewer part first then this. Two of the most useful posts I have seen around for a while. Great work!
Hi Paul,
Once again.. Excellent tool for Excel WebPart.
Unfortunately, I also have a Visio Viewer webPart where this and your previous JQuery does not seem to function. Perhaps, because they are inner scroll bars?
Thanks again for the great tool..
Gus
@Gus,
Visio viewer is an ActiveX control and probably requires a programming solution to resize properly.
Changing the shape of the HTML around the OBJECT will not help it expand and trying to change the OBJECT W x H values after render seems to just make it angry.
~Paul
Thanks Paul,
I thought it was a bit more complex an issue.
I found a fellow that has developed a wrapper for it in Outlook (http://bvisual.spaces.live.com/blog/cns!3350D61BC93733A9!1077.entry) that provides more functionality than the current MS solution and hopefully he can help.. If he comes back with something I will post it.
Gus
Sorry if this is a bit of a simple question but how do you use that script? Do you need Sharepoint designer? Or do you open the page in notepad?
@Yautja
1. Site Actions > Edit Page
2. Add a Web Part (Choose Content Editor).
3. Open Source Editor.
4. Paste code snippet into the field, Save.
5. Exit Edit mode.
Done.
No SPD or page access needed, just contribute rights to the shared view of the page.
Thanks, it works!
Excellent tool for Excel WebPart
Hi Paul
I was wondering if you have a similar solution for the Office Spreadsheet webpart in WSS3?
Many thanks
Duncan
Have you or would you post the connectable page viewer web part?
@Quin,
I think the code you’re looking for is here:
http://www.endusersharepoint.com/?p=1116
Nope, I don’t think so. That’s code for resizing web parts. I’m looking for a Page Viewer that can be connected to from another web part and passed a URL.
Quin, I will try to get something posted shortly about the web part that I did (connected page viewer), either here or on Codeplex.
Thanks a million JoeD. Conceptually that is such a useful web part for integrating legacy data. There was one for SS2003, but it doesn’t compile properly under MOSS. Thanks again!
Finally, I uploaded my Connectable Page Viewer web part to Codeplex…
http://www.codeplex.com/ConnectablePgViewer
Joe – Thanks for the update. Will this web part have to be deployed or can it be managed through the SharePoint interface. — Mark
Hmm… not sure. I build it into a .cab file (which contains the DLL and DWP files) and install it with stsadm. I’ve never really tried it any other way.
Yep, stsadm is the answer. Only people who have access rights to the server will be able to deploy this. Sounds like a good solution if you can convince IT to implement. — Mark
I have a question about this code. I have loaded the code and it works great, so first of all thanks for this. My questions comes in the fact that i need to be able to display the EWA web part without the title or toolbar showing and when i do this the code does not resize the web part, only when the title bar is there. Is there any way to change this so that it will resize no matter if the title is there or not?
This is cool stuff. My brain is still trying to digest it. I have a new problem to pose: My sharepoint form has 2 text filters that the EWA uses. I want to be able to set the values for the filters BEFORE the report renders for the first time. Can you help me with that one?
If you have a Text Filter that you want to set to an initial value, try specifying the filter name and value in the query string of the page.
For example, if you have a text filter named TextValue (in the web part properties), use:
http://moss2007server/site/page.aspx?TextValue=ABC
This will default the filter named TextValue to “ABC”. I am not sure if it will automatically pass it through to EWA web part however.
Thanks for the resource. Have you developed the code that will dynamically resize a page viewer web part, much like the one for excel? I tried the excel code for a page viewer web part and it didn’t work.
We have an issue where a page viewer is showing multiple forms that are of varying length as the user clicks thru them. We don’t want to show the scroll bars so we made manually set the page length to the longest form. Obviously, this creates a problem when subsequent forms are shown on the same page viewer web part. Too much blank space is shown on the subsequent shorter forms and the position stays the same on the page so the user is presented with what looks like a blank page.
Any suggestions would be helpful.
Thanks
@Allan,
The way that is typically fixed is, if you have control of the interior page, you add JavaScript to it to “report” the size of it’s DOM then modify the window to accommodate it.
If you don’t have such control, and the page is on a different server, cross-site scripting blocks will prevent you from making the modification automagically. In that case, I suggest the manual resize approach.
Lookup the jQuery UI .resizeable() method or other similar scripts.
Paul: is this supposed to work for Excel charts as well? I tried it, and only the EWA Web Parts containing tables were resized.
Christophe
@Christophe,
I only tested it with EWA.
Paul: EWA, yes, that’s what I was talking about. The script works fine with EWA displaying Excel tables, but not for EWA displaying Excel charts.
One more thing: the resizing works fine for the first table displayed, but doesn’t resize if I select another table through the view selector.
Update – I took a closer look at the code, just to confirm that it doesn’t work with charts.
I made the following change that seems to work:
replaced
find(”table.ewrnav-invisibletable-nopadding:last”)
with
find(”table.ewrnav-invisibletable-nopadding:last,div.ewr-positionedelementanchor:last”);
Still have the issue with flipping views though.
Actually this seems enough:
find(”div.ewr-positionedelementanchor:last”)
Christophe
Someone asked this before, but wondering if there is a solution. Script works great to resize the initial Excel sheet, but within the sheet I have links that bring up a different worksheet within the workbook. Can the script trigger a resize when clicking the Excel link?
For some reason this code is not working for me. I have a page with two web parts. The first is the content editor containing the code I copied from this website. The second is the page viewer web part. Am I missing something?
Nevermind! I was ignoring the fact that this was made for an excel web part. (doh!)
Hi,
could you please post the code ?
thanks and regards
Nat
Natmee – Done. — Mark