Supporters of End User
Web

JQuery for Everyone: Dynamically Sizing Excel Web Parts

 
Item is currently unrated. Press SHIFT+ENTER to rate this item.1 star selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+ESCAPE to leave rating submit mode.2 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.3 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.4 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.5 stars selected. Press SHIFT+ENTER to submit. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.
Categories: 
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 1280x1024 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:
  1. Checks the page for web parts with iframe content to load.
  2. Binds an event to each iframe web part.
  3. Triggers the bound event to check for a completed postback.
  4. Waits for the inner document to render.
  5. Returns the height and width of the iframe's content.
  6. 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>
Paul Grenier

Comments

JoeD

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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.

Posted 05-Jan-2009 by JoeD
tom.vangaever

JQuery for Everyone: Dynamically Sizing Excel Web Parts

and again...

very nice done!

Posted 05-Jan-2009 by tom.vangaever
AutoSponge

JQuery for Everyone: Dynamically Sizing Excel Web Parts

@JoeD

Browsers prevent access to inner documents from another host. But I have a solution! I'll post it today.

Posted 05-Jan-2009 by AutoSponge
JQuery for Everyone: Manually Resizing Web Parts : End User SharePoint

JQuery for Everyone: Dynamically Sizing Excel Web Parts

[...] I would love to have all iframes dynamically resize to fit their contents. However, as JoeD pointed out from my previous article, the browser’s security restricts us to only dynamically resizing iframes from our [...]

Posted 05-Jan-2009 by JQuery for Everyone: Manually Resizing Web Parts : End User SharePoint
Michael Gannotti

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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!

Posted 05-Jan-2009 by Michael Gannotti
Gus

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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

Posted 06-Jan-2009 by Gus
AutoSponge

JQuery for Everyone: Dynamically Sizing Excel Web Parts

@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

Posted 06-Jan-2009 by AutoSponge
Gus

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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

Posted 06-Jan-2009 by Gus
Yautja_cetanu

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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?

Posted 08-Jan-2009 by Yautja_cetanu
AutoSponge

JQuery for Everyone: Dynamically Sizing Excel Web Parts

@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.

Posted 08-Jan-2009 by AutoSponge
Kim

JQuery for Everyone: Dynamically Sizing Excel Web Parts

Thanks, it works!
Excellent tool for Excel WebPart

Posted 08-Jan-2009 by Kim
Duncan

JQuery for Everyone: Dynamically Sizing Excel Web Parts

Hi Paul

I was wondering if you have a similar solution for the Office Spreadsheet webpart in WSS3?

Many thanks

Duncan

Posted 14-Jan-2009 by Duncan
OneOfSix

JQuery for Everyone: Dynamically Sizing Excel Web Parts

Add Links to SharePoint Wiki Toolbar using jQuery... ...

Posted 22-Jan-2009 by OneOfSix
Quin Dennis

JQuery for Everyone: Dynamically Sizing Excel Web Parts

Have you or would you post the connectable page viewer web part?

Posted 08-Feb-2009 by Quin Dennis
AutoSponge

JQuery for Everyone: Dynamically Sizing Excel Web Parts

@Quin,

I think the code you're looking for is here:
http://www.endusersharepoint.com/?p=1116

Posted 10-Feb-2009 by AutoSponge
Quin Dennis

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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.

Posted 10-Feb-2009 by Quin Dennis
JoeD

JQuery for Everyone: Dynamically Sizing Excel Web Parts

Quin, I will try to get something posted shortly about the web part that I did (connected page viewer), either here or on Codeplex.

Posted 10-Feb-2009 by JoeD
Quin

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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!

Posted 10-Feb-2009 by Quin
JoeD

JQuery for Everyone: Dynamically Sizing Excel Web Parts

Finally, I uploaded my Connectable Page Viewer web part to Codeplex...

http://www.codeplex.com/ConnectablePgViewer

Posted 20-Feb-2009 by JoeD
EndUserSharePoint

JQuery for Everyone: Dynamically Sizing Excel Web Parts

Joe - Thanks for the update. Will this web part have to be deployed or can it be managed through the SharePoint interface. -- Mark

Posted 20-Feb-2009 by EndUserSharePoint
JoeD

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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.

Posted 20-Feb-2009 by JoeD
EndUserSharePoint

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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

Posted 20-Feb-2009 by EndUserSharePoint
Chris

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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?

Posted 14-Apr-2009 by Chris
Ron Solinski

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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?

Posted 23-Apr-2009 by Ron Solinski
JoeD

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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.

Posted 23-Apr-2009 by JoeD
Allan Lee

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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

Posted 07-May-2009 by Allan Lee
AutoSponge

JQuery for Everyone: Dynamically Sizing Excel Web Parts

@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.

Posted 07-May-2009 by AutoSponge
Christophe

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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

Posted 18-May-2009 by Christophe
AutoSponge

JQuery for Everyone: Dynamically Sizing Excel Web Parts

@Christophe,

I only tested it with EWA.

Posted 19-May-2009 by AutoSponge
Christophe

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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.

Posted 22-May-2009 by Christophe
Christophe

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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.

Posted 22-May-2009 by Christophe
Christophe

JQuery for Everyone: Dynamically Sizing Excel Web Parts

Actually this seems enough:
find(”div.ewr-positionedelementanchor:last”)

Christophe

Posted 22-May-2009 by Christophe
CBauer

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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?

Posted 01-Jul-2009 by CBauer
Maggie

JQuery for Everyone: Dynamically Sizing Excel Web Parts

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?

Posted 12-Oct-2009 by Maggie
Maggie

JQuery for Everyone: Dynamically Sizing Excel Web Parts

Nevermind! I was ignoring the fact that this was made for an excel web part. (doh!)

Posted 12-Oct-2009 by Maggie
Natmee

JQuery for Everyone: Dynamically Sizing Excel Web Parts

Hi,

could you please post the code ?

thanks and regards
Nat

Posted 10-Dec-2009 by Natmee
EndUserSharePoint

JQuery for Everyone: Dynamically Sizing Excel Web Parts

Natmee - Done. -- Mark

Posted 10-Dec-2009 by EndUserSharePoint
Jim

JQuery for Everyone: Dynamically Sizing Excel Web Parts

So I have a quick question regarding dynamic sizing with Excel webparts.  I have been able to upload the excel spreadsheet that I want to show and have mapped it to the webpart.  Too big.  I then took the code that Paul provided and opened another webpart, adding the code to the source editor area.  Unfortunately, I can't figure out how the have this work together with the excel spreadsheet.  What am I doing wrong?

Posted 06-Apr-2011 by Jim
peter

SharePoint 2010 / 365

This is a nice article, but anyone tested it on 2010/365? Nothing seems to happen... What could be the problem?

Posted 30-Jan-2012 by peter
Doug Steckel

Auto Scaling of content

Hi All,
 
I'm new to sharepoint and excel services. I implemented Paul's dynamic sizing code and it worked like a charm. My problem now is the actuial rendering and scaling. So a spreasheet with charts, a dashbord we have that fits nicely on the screen in excel is sclaed up almost double. Thanks to Pauls code I no longer get the inner web part scrollbars but still have to scrole in the browser. Why would the excel services rendering scale up like this. Is there any setting that would do the equivelent of "scale to fit" the web part?
 
Thanks,
Doug

Posted 25-Jan-2013 by Doug Steckel
desapri

nakalama

thank for info

Posted 31-Mar-2014 by desapri

Notify me of comments to this article

E-mail:
   

Add Comment

Title:

 
Comment:
Email:

   


Name:

 
Url: