Partager via

Customizing EWA in the XLViewer.aspx Page

  • Article

As you may have noticed if you have started to play around with Excel Services, the XLViewer.aspx page is the leanest way to open up a workbook on the server.  There is very little Sharepoint UI added to this page and you are basically given a full screen Excel Web Access web part.

The biggest drawbacks of the XLViewer.aspx page are the lack of support for adding other web parts and connecting filter web parts, and the lack of customizability of the EWA properties.

This post will teach you how to get around the second drawback, and enable you to customize the EWA web part in XLViewer.aspx simply by sending it the query string parameters to be used.

How:

The coding here is actually pretty simple, and will enable you to do things like loading XLViewer.aspx in an IFRAME and specifying how many columns or rows to display, or whether or not to display the navigation toolbar.  On a later date I will even show you how to create a Live.com gadget that will embed this page on https://www.live.com/.

The first thing you need to do is create a copy of XLViewer.aspx which is located in C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS (your mileage may vary).  I named my copy MyXLViewer.aspx.  You can now access this by going to https://SERVERNAME/_layouts/MyXLViewer.aspx, but obviously it won't be doing anything special just yet.

The next step is to open MyXLViewer.aspx in Notepad or any other text editor.  Our goal here is to modify the Page_Load() event such that when the page loads we will set the properties passed in through the query string directly into the EWA web part object.

To accomplish this we first need to enable events on this page.  You can do this by changing the 4th line on the page from reading:

<%@ Page language="C#" Codebehind="XlViewer.aspx.cs" AutoEventWireup="false"...
To read:
<%@ Page language="C#" Codebehind="XlViewer.aspx.cs" AutoEventWireup="true"...

Congratulations!  You have now enabled us to handle events in the page.  The next thing we need is a little bit of code to parse the query string for the properties we want, and stuff them into the EWA web part object so that it does what we tell it to do.

Here's the code you want to place just before the </HEAD> tag on the page:

<script runat="server">

private void Page_Load(object sender, System.EventArgs e)

{

            if (Request.QueryString["RowsToDisplay"] != null)

                        m_excelWebRenderer.RowsToDisplay =

                                    Int32.Parse(Request.QueryString["RowsToDisplay"]);

 

            if (Request.QueryString["ColumnsToDisplay"] != null)

                        m_excelWebRenderer.ColumnsToDisplay =

                                    Int32.Parse(Request.QueryString["ColumnsToDisplay"]);

               

            if (Request.QueryString["ToolbarVisibilityStyle"] != null)

                        if (Request.QueryString["ToolbarVisibilityStyle"] == "1")

                                    m_excelWebRenderer.ToolbarStyle =

ToolbarVisibilityStyle.FullToolbar;

                        else

                                    m_excelWebRenderer.ToolbarStyle =

                                                           ToolbarVisibilityStyle.None;

}

</script>

The code above will add support for 3 query string parameters, namely: RowsToDisplay, ColumnsToDisplay, and ToolbarVisibilityStyle.  You use these parameters simply by appending them to the query string, so if you want to load https://SERVERNAME/Documents/Book1.xlsx  showing 5 columns, 10 rows, but no Toolbar you would browse to https://SERVERNAME/_layouts/MyXLViewer.aspx? id=https://SERVERNAME/Documents/Book1.xlsx &RowsToDisplay=10&ColumnsToDisplay=5&ToolbarVisibilityStyle=0

That's all!  I told you it would be simple.  Using this same method you can customize any of the Excel Web Access web part properties just by adding them to the Page_Load event as I have shown above.  In order to figure out all of the variable names and types for the properties we provide you can open up an existing EWA web part page, click on Modify Web Part drop down and choose Export.  You can save the *.webpart file anywhere and open it on Notepad.  Here's what I'm seeing on our current builds:

<properties>
<property name="Height" type="string" />
<property name="HelpMode" type="helpmode">Modeless</property>
<property name="CatalogIconImageUrl" type="string">/_layouts/images/ewr023.gif</property>
<property name="AutomaticPeriodicDataRefresh" Type="Microsoft.Office.Excel.WebUI.AutomaticPeriodicDataRefreshMode, Microsoft.Office.Excel.WebUI, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c">Disabled</property>
<property name="ColumnsToDisplay" type="int">20</property>
<property name="Description" type="string"></property>
<property name="ShowVisibleItemButton" type="bool">True</property>
<property name="Direction" type="direction">NotSet</property>
<property name="MissingAssembly" type="string"></property>
<property name="Width" type="string" />
<property name="AllowConnect" type="bool">True</property>
<property name="AllowSorting" type="bool">True</property>
<property name="AllowHyperlinks" type="bool">True</property>
<property name="TitleUrl" type="string" />
<property name="AllowEdit" type="bool">True</property>
<property name="RowsToDisplay" type="int">75</property>
<property name="ShowWorkbookParameters" type="bool">True</property>
<property name="CloseWorkbookSessions" type="bool">False</property>
<property name="WorkbookUri" type="string" />
<property name="ChromeType" type="chrometype">TitleOnly</property>
<property name="ChromeState" type="chromestate">Normal</property>
<property name="AllowPivotSpecificOperations" type="bool">True</property>
<property name="AutoGenerateTitle" type="bool">True</property>
<property name="AllowFiltering" type="bool">True</property>
<property name="VisibleItem" type="string" />
<property name="AllowInExcelOperations" type="bool">True</property>
<property name="AllowNavigation" type="bool">True</property>
<property name="ExportMode" type="exportmode">All</property>
<property name="ToolbarStyle" type="Microsoft.Office.Excel.WebUI.ToolbarVisibilityStyle, Microsoft.Office.Excel.WebUI, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c">FullToolbar</property>
<property name="AllowInteractivity" type="bool">True</property>
<property name="HelpUrl" type="string" />
<property name="AllowPeriodicDataRefresh" type="bool">True</property>
<property name="AllowClose" type="bool">True</property>
<property name="Hidden" type="bool">False</property>
<property name="TitleIconImageUrl" type="string">/_layouts/images/ewr023.gif</property>
<property name="AllowManualDataRefresh" type="bool">True</property>
<property name="AllowHide" type="bool">True</property>
<property name="AllowZoneChange" type="bool">True</property>
<property name="AutoGenerateDetailLink" type="bool">True</property>
<property name="AllowRecalculation" type="bool">True</property>
<property name="Title" type="string">Excel Web Access</property>
<property name="AllowParameterModification" type="bool">True</property>
<property name="AllowMinimize" type="bool">True</property>
</properties>

NOTE: The list above is only there for you to be able to get the "property name" for everything we support in the Excel Web Renderer object, and the "type" it maps to.  For example, if you wanted to set this particular property from the list above:

<property name="AllowMinimize" type="bool">True</property>

 You would simply add code to the Page_Load() handler to do this:

m_excelWebRenderer.AllowMinimize = true;   // or false if you want since type is bool

 

SEE ATTACHED MYXLVIEWER.ASPX PAGE!

MyXLViewer.aspx

Comments

  • Anonymous
    October 19, 2006
    Luis is one of our prolific testers. He keeps thinking up new applications for Excel Services (or implementing

  • Anonymous
    October 19, 2006
    The comment has been removed

  • Anonymous
    November 15, 2006
    Hi All, I have tryed this code putting into xlViewer.aspx page.when i want to see tghe excel in the browser iam unable to see the added details. please help me on this.. Thanks&Regards, Amar...

  • Anonymous
    November 27, 2006
    Amar, Can you email me with what you did and what is not working?  Did you use the attached ASPX page or try to copy/paste the code from the blog?  Try with the attached page just in case. You can mail me internally if you're at MS, or use the Email link at the top of the blog. Thanks!

  • Luis PS: Sorry for the delay, was taking a break for Thanksgiving :o)