Compartilhar via

AJAX Library for Excel Services

  • Artigo

Edit: I moved the download to its own page. 

I have had this on my table for a while already and have been meaning to make it available for everyone to use/modify. The idea behind this example is to wrap the Excel Web Services API so that it is available for call inside Internet Explorer. The idea is that people will be able to leverage models inside workbooks just like C# code.

The attached file (ExcelServicesAjax.js) needs to be placed inside the _layouts directory of your SharePoint server. In yout HTML files, you then need to reference the javascript file. That will give you access to the functionality.

Note: I am not a javascript guy. That means I am not really that well versed with the "right way" of doing thing. I built an object library using the weird JS way of doing objects and I am pretty sure it could be done in a better way. So if someone has a "correct way of doing it" modification, let me know and I will post about it.

Like with AJAX, each method call that goes to the server also takes a callback argument that will be called when the request returns from the server. In the following HTML, I will show how to use some of the functionality in the library. In essence, there are two classes that will be used most often - the first one wraps the Excel Web Services API and is aptly called ExcelServices. The second is called ExcelServicesSession and it wraps a session id returned from Excel services, allowing the callers to make calls that are somewhat easier to issue. There is also a class called ExcelServicesMethodCall which represents a single SOAP call to the Excel Web Services interface - for most callbacks, this method is passed back in - the reason is maintaining state across async calls and may be discussed in a future post (depending on the amount of interest this will or will not generate).

Starting

The first piece of HTML will show how to set up the HTML page so that it can use the AJAX wrappers for Excel Services. The most important thing to understand here is that the AJAX library AND the page making the calls both have to be in the same domain. If they are not, you will run into XSS (cross site scripting) limitations that will prevent you from using it. That said, here's what the HTML looks like when it's done running:

Each of the texts at the right hand side of the table gets generated sequentially, as the previous one is done. This is what the HTML of the page looks like for the first part (which will also issue the first request to the server to open the workbook).

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="https://www.w3.org/1999/xhtml" >

<head>

    <title>Excel Services Ajax Test Page</title>

    <script src="ExcelServicesAjax.js" type="text/javascript"></script>

</head>

<body onload="javascript:Initialize();">

<table>

       <tr>

              <td>Startup</td>

              <td id="starting">Inactive</td>

       </tr>

       <tr>

              <td>OpenWorkbook Result:</td>

              <td id="openWorkbookResult">Inactive</td>

       </tr>

       <tr>

              <td>First GetCell result:</td>

              <td id="firstGetCell">Inactive</td>

       </tr>

       <tr>

              <td>Running SetCell:</td>

              <td id="setCellResult">Inactive</td>

       </tr>

       <tr>

              <td>GetCell result after SetCell:</td>

              <td id="secondGetCell">Inactive</td>

       </tr>

       <tr>

              <td>Close workbook:</td>

              <td id="closeWorkbook">Inactive</td>

       </tr>

</table>

<script type="text/javascript">

//<!--

var es = new ExcelServices();

var session = null;

function Initialize()

{

       SetProgress("starting", "Started...");

       es.openSession("https://bluemonster/Test/Shared Documents/Test.xlsx", OnWorkbookOpened);

       SetProgress("openWorkbookResult", "Waiting for response...");

}

function SetProgress(spanid, text)

{

       var el = document.getElementById(spanid);

       el.innerHTML = text;

}

//-->

</script>

</body>

</html>

 

Improtant parts:

* The table at the top of the HTML is the one displayed in the screen shot. It contains an item for each of the operations that will take place.

* The Initialize() method is called when the page is done loading. It will "kick off" the Excel Services call sequence.

* The SetProgress() method is a helper method that changes text inside the table as the sequence progresses.

The Initialize() function calls the openSession() method on the Excel Services instance inside the global variable "es". It passes as a parameter the name of the workbook and the callback that needs to be called when it's done. Note that it changes the status text in the table.

Working with Sessions

Once the session is opened on the server, the OnWorkbookOpened() function gets called:

function OnWorkbookOpened(result)

{

       SetProgress("openWorkbookResult", "Session ID that we got back is: " + result.sessionId);

       session = result;

       session.getCellA1("Sheet1","A1", true, FirstGetCellFinished);

       SetProgress("firstGetCell", "Waiting for response...");

}

This method again changes the text to show the session ID that was returned. On top of that, it kicks off a call to getCellA1() which will grab the value from Sheet1!A1. The callback this time is FirstGetCellFinished.

Note that the result that's passed into the callback of the openSession call is an ExcelServicesSession instance which acts as a wrapper to the Excel Web Services calls. For example, note how there was no need to pass in the session id into the getCellA1() method - it gets passed in implicitly by the wrapper class.

Once the callback gets called, the HTML sets a cell:

function FirstGetCellFinished(webMethodCall)

{

       SetProgress("firstGetCell", "Cell is equal:" + webMethodCall.result);

       session.setCellA1("Sheet1", "A2", 8, SetCellFinished);

       SetProgress("setCellResult", "Waiting for response...");

}

As you can see, to get at the result of the getCellA1 operation, the code uses the result property of the parameter that's passed into the callback. In this case, the result will contain whatever is in Sheet1!A1 of the workbook we opened.

This function also calls the setCellA1 method and sets the value 8 into cell Sheet1!A2. Once that operation will be done, the SetCellFinished() method will be called:

function SetCellFinished(webMethodCall)

{

       SetProgress("setCellResult", "Done.");

       session.getCellA1("Sheet1", "A3", true, SecondGetCellFinished);

       SetProgress("secondGetCell", "Waiting for response...");

}

function SecondGetCellFinished(webMethodCall)

{

       SetProgress("secondGetCell", "Cell is equal:" + webMethodCall.result);

       session.close(CloseWorkbookFinished);

       SetProgress("closeWorkbook", "Waiting for response...");

}

function CloseWorkbookFinished(webMethodCall)

{

       SetProgress("closeWorkbook", "Close workbook complete.");

}

Note that setCellA1 does not really return anything, and so we are not using the webMethodCall parameter at all. We are, however, going to make a second call into getCellA1 which in turn will call the SecondGetCellFinished() method when it's done. That method will get a cell that was somehow affected by changing Sheet1!A2.

Finally, the callback for the second getCellA1() call calls into the close() method which will close the session on the server.'

That, in a nut shell, is how the APIs work. If you have any questions, feel free to post them.

Notes

* Only some of the methdos in Excel Web Services are suppported. Namely, OpenWorkbook, GetCellA1, GetRangeA1, Calculate, SetCellA1 and CloseWorkbook.

* In case of error, you can check the .isError property of the result of the callback parameter. If it's true, then it means that there was an error. Inspecting the reason property will give the error description.

Enjoy!

Comments

  • Anonymous
    January 29, 2007
    Hoy en cosas interesantes: Virtual Server VS VMWare, Troyanos en los Tom Tom, Libreria de Ajax para Excel

  • Anonymous
    March 08, 2007
    Great example. I've got it working in no time. Some notes:

  • I only got it working if the web page with the js code is hosted in the same web as excel services. is this correct?
  • any chance SetRangeA1 can be implemented? O:-) -j

  • Anonymous
    March 08, 2007
    RE: Same web as Excel Services That is correct. Because of XSS limitations, it needs to be hosted there. RE: SetRangeA1 I will try getting to it. If you look at the code, it's pretty easy to add though. I will only have a chance to get to it in about 2 weeks, as an FYI.

  • Anonymous
    May 22, 2008
    Corpo: Olá pessoal, Durante a participação no MVP Summit 2008 tive a oportunidade de conversar com vários

  • Anonymous
    May 26, 2008
    En mi experiencia, he visto que este es uno de los temas poco desarrollados por el momento en los portales

  • Anonymous
    May 30, 2008
    Este post es gracias a la recopilación de ligas que Hector Insua hizo en su Blog, y que me gustaría compartir

  • Anonymous
    May 30, 2008
    Este post es gracias a la recopilación de ligas que Hector Insua hizo en su Blog, y que me gustaría compartir

  • Anonymous
    August 26, 2009
    Anybody can able to get setRangeA1 function