EWA Companion Part 3: Open/Download functionality


In the first part of this post series I showed the EWA companion and its various capabilities. In this part, I will delve deeper into the “Open” functionality of the EWA Companion web-part.

There are a few reasons behind adding the Open command to the EWA. While the functionality already seemingly exists in it (You can use the “Open” menu on EWA to open the active session inside Excel 2007), but some things are missing. For one, the “Open” menu only works with Excel 2007. If you have Excel 2003 or below installed with the compatibility pack, you will not be able to use that functionality. Another thing that is impossible to do today is to immediately Save As the workbook (you need to go to Excel 2007, and then choose Save As from there). Lastly, I have heard some users request printing functionality. While printing from the browser never really works that well, Microsoft does distribute a free Excel Viewer. Using this functionality, it should be possible to view and print Excel files without needing to install Excel on the client (of course, your functionality will be very diminished, but at least you should be able to print/view files outside of the browser).

To enable the Open feature, you need to go to the properties of the web-part and add them:

image

Once enabled, you will see the following menu item in the web-part:

image

Once clicked, you will get the regular IE/Firefox download dialog box:

image

This can then be used for whatever you want – clicking “Open” will run the default app (Excel 2003 for example). Clicking “Save” will give the save dialog.

The Code

There are 2 parts for the code for this portion of the companion. The first one is the ASPX page that actually allows you to get the file from the server and the second is the EWA companion code that invokes that page.

First, take a look at the ASPX page code-behind – the Load functionality:

protected override void OnLoad(EventArgs e)

{

       base.OnLoad(e);

       try

       {

              m_sessionId = Request.QueryString[SessionIdParam];

              string wt = Request.QueryString[WorkbookTypeParam];

              if (wt.Equals(WorkbookTypeFull, StringComparison.OrdinalIgnoreCase))

              {

                     m_workbookType = WorkbookType.FullWorkbook;

              }

              else if (wt.Equals(WorkbookTypeSnap, StringComparison.OrdinalIgnoreCase))

              {

                     m_workbookType = WorkbookType.FullSnapshot;

              }

              else if (wt.Equals(WorkbookTypePublished, StringComparison.OrdinalIgnoreCase))

              {

                     m_workbookType = WorkbookType.PublishedItemsSnapshot;

              }

       }

       catch (Exception ex)

       {

              m_error = ex;

       }

}

In here, the code simply takes the parameters and determines what the operation that needs to be done is. The actual code that streams the workbook back to the user is in the Render override:

protected override void Render(HtmlTextWriter writer)

{

       if (m_error != null)

       {

              writer.WriteLine(m_error.ToString());

              return;

       }

 

       try

       {

              ExcelService s = new ExcelService();

              Status[] status;

              byte[] bits = s.GetWorkbook(SessionId, WorkbookType, out status);

 

              Response.Clear();

              Response.AddHeader(“LAST-MODIFIED”, DateTime.Now.ToString(“r”));

              Response.Charset = “”;

              Response.ContentType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”;

              Response.OutputStream.Write(bits, 0, bits.Length);

              Response.OutputStream.Flush();

       }

       catch (Exception ex)

       {

              writer.WriteLine(ex.ToString());

       }

       Response.End();

}

 

The main part here is the GetWorkbook() call which takes the binary representation of the workbook. The second part is stuffing the file into the Response and making sure that it will get opened by the appropriate application on the client (that’s what the ContentType property governs).

Finally, the companion invokes this page by using client-script. Here is the piece of code that gets attached to each of the “Open” menu items:

private void AddOpenButton(string text, string type, System.Web.UI.WebControls.MenuItem sub)

{

       System.Web.UI.WebControls.MenuItem item = new System.Web.UI.WebControls.MenuItem(text);

       sub.ChildItems.Add(item);

       item.NavigateUrl = String.Format(

                     “javascript:EcOpenExcelWorkbook(‘{0}’,'{1}’);”,

                     ConnectedEwa.ClientID,

                     type);

}

All this does is call the JavaScript function EcOpenExcelWorkbook(). That’s defined inside the EwaCompanionScripts.js file:

function EcOpenExcelWorkbook(wpid, type)

{

       var sessionId = EwaGetSessionId(wpid);

       if (sessionId == null)

       {

              return 0;

       }

      

      

       window.location.assign(“/_layouts/EcDownloadWorkbook.aspx?sid=” + encodeURIComponent(sessionId) + “&type=” + type);

}

 

All THIS function does, is get the session id inside the EWA and passes it to the EcDownloadWorkbook.aspx page for processing.

The next part of this series will discuss the last feature in the companion (at this time) which is the periodic refresh.

Comments (16)

  1. jachan says:

    Hi, I installed and tried the EWA Companion, everything runs quite well except the Open function.

    When I select Open Full Workbook, Open Snapshot or Open Published Items, they all give me the same error message:

    The XML page cannot be displayed

    Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.

    ——————————————————————————–

    An invalid character was found in text content. Error processing resource ‘http://srv2007/_layouts/EcDownloadWorkbook.aspx?…

    Would you give me some suggestion what I should check for this error? Thank you very much.

  2. Shahar Prish says:

    Is this workbook something you can send me? I would like to try it myself so I can debug it. That will help me figure out what’s going on in there..

    Use the "Contact" link at the top of the page to send me an email. I will reply to you and then you will be able to send me back the attached file (or, if you have access to a public download site, put it there and post the link)

  3. jachan says:

    I just used the SampleWorkbook.xlsx, which is the sample included in the Reports Library after creating a report center on SharePoint. The server only has Excel 2003 installed, hope this is not the reason causing the error.

    Thank you very much.

  4. jachan says:

    Hi, to clarify my last post, "the server" is also the client. I’m using a standalone server and do testing on it. I have also tried opening other xlsx files, the same error message is resulted. I think the error message stated part of the error, but I am not able to post it here because the it is some strange characters. You can download the screen capture here: http://download.yousendit.com/922D5B5B15C75318

    Thank you very much.

  5. Shahar Prish says:

    Can you see if when you save the file and open it it will work and post your findings?

    Also, please send the file as well – it would help me (I couldnt find it on my machine).

  6. jachan says:

    I saved the file and open it by Excel 2003 + Office 2007 compatibility pack, which was failed and there was an error saying something Excel Beta version. So, I found someone to create a simple xlsx file for me, which contains only a few characters. You can download it here: http://download.yousendit.com/E34ED7547734DCB3

    By using this file, it still gave me the same error message.

    For your reference, the sample file on the Record Center is here: http://download.yousendit.com/551EB7EC58B1D488

    Thank you very much.

  7. Shahar Prish says:

    If you try to open this new file in Excel 2007, does it work or not? And does this change based on whether you open the file directly or from the companion?

    Is it possible that you are running a beta-version of the server/client?

  8. jachan says:

    The server is using the Evaluation copy of Windows Server 2003 Enterprise Edition, SP1.

    The file can be opened in Excel 2007. And this time I have tried the functions with a client having Excel 2007 installed. It did have a pop up asking to open or save the file. And then I can open it with Excel 2007.

    When I tried the Open function on a client that have no Excel 2007, it will go to a page with the following URL, and the error message I captured before is shown.

    http://srv2007/_layouts/EcDownloadWorkbook.aspx?sid=64.8b4c12e2-7ddf-4e46-b568-3e0ab85c9436w8tEg1qpUnGDCpZFRnYhvm8gKuQ%3D118.22.46ccOEk9QGRpezi7%2BaEFtK90.5.en-US5.en-US73.-0480%230000-00-00-00T00%3A00%3A00%3A0000%23%2B0000%230000-00-00-00T00%3A00%3A00%3A0000%23-0060&type=full

  9. Shahar Prish says:

    Jachan,

    Please use the Contact link at the top of the page to email me directly – I am feeling like I am missing something, and i want to make sure I understand everything before I go on with helping you.

  10. alex says:

    Hi Shahar,

    I’m using an EWA webpart with your companion in a web site in order to use an xlsx file, however my client computer uses Office 2003 (with the compatibility pack) and the excel files is not visible, I’ve got an error.

    Moreover, when I tried to open the file with the companion it redirect me to a page that print a 0

    is there something that I’ve missed to use it with Excel 2003 ?

    thanks for your answer

  11. Shahar Prish says:

    What do you mean by "excel files are not visible"?

    Also, do you not get a dialog box for saving when you click on the "Download" link?

  12. Cassandra says:

    I have a multiple client based SharePoint site that houses several excel web parts.  I have some users on 2003 and some on 2007… It sounds to me that the 2003 guys need to be updated to 2007 in order to open the files.  But as for the ones already on 2007 it seems to be hit or miss… some are experiencing no problems opening the excel worksheets on their PC and others go to open and internet explorer 7.0 kicks a fatal error and closes… do you know why this is happening?

  13. manu says:

    Hello

    When i try to do a snapshot … i have this error message :

    Microsoft.Office.Excel.Server.WebServices.ExcelServerApiException: Excel Web Services could not determine the Windows SharePoint Services site context of the calling process. at Microsoft.Office.Excel.Server.WebServices.ApiShared.VerifySiteContext() at Microsoft.Office.Excel.Server.WebServices.ExcelService..ctor() at

    Prish.WebParts.EcDownloadWorkbook.Render(HtmlTextWriter writer)

    Have you an idea for this error ?

    Thanks

    manu

  14. Shahar Prish says:

    This basically means you are not running the thing as a SharePoint web-part, but rather as an ASP.NET part. Is that possible? The component needs to run in the context of a SP front end.

  15. Manu says:

    Hello

    thanks for your answer..

    I think it’s not possible. I have install the webpart like  in the readme.txt file.

    The problem occure only with Open snapshot option… the save and save as work perfectly? maybe a security problem?

    thanks for your help.

    manu

  16. Michael says:

    This is a little off topic; so I will apologies upfront.

    Has anyone come across the ability to convert the excel snapshot to pdf?

    To explain a bit more: What I would like is that the user has a "button" (save as pdf) the converts the EWA session to a pdf file on the fly.