Customizing EWA in the XLViewer.aspx Page
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!