Creating webpages with Excel Web App mashups

Getting input from web form controls into your Excel file

Wouldn't it be nice to have a web form which users can enter data into and have your Excel Web App updated with the content of the web form. That is what I describe here.

Put up a form with some controls

To get data from a form textbox into your Excel file, you need to place a form on your web page. This piece of html does that:

<form name="ChangeInput">
    Phase difference (degrees) 
    <input name="Phase" size="18" style="width: 112px" />
    Frequency ratio 
    <input name="Freq" size="18" style="width: 112px" />
    <input onclick="JavaScript:SetValuesInSheet();" type="button" value="Update Chart" />
</form>

As you can see, the button's click event is set to call a Javascript function called SetValuesInSheet.

Writing values in a cell

The SetValuesInSheet function is relatively short:

function SetValuesInSheet()
    {
    // Get a reference to where we need to write
    ewaCht.getActiveWorkbook().getRangeA1Async('b1:b2', getRangeA1Callback, null);
    }

As you can see, we first need to ask the Ewa to give us a reference to range B1:B2 in the active sheet. This is done asynchronously, so a call-back function is needed: getRangeA1Callback. Listed below:

function getRangeA1Callback(asyncResult)
        {
        // getRangeA1Async returns a range object through the AsyncResult object.
        var range = asyncResult.getReturnValue();
        var values = new Array(2);
        values[0] = new Array(1);
        values[1] = new Array(1);
        var control = document.ChangeInput.Phase;
        values[0][0] = control.value;
        control = document.ChangeInput.Freq;
        values[1][0] = control.value;
        // Now send the array to the range.
        range.setValuesAsync(values,Dummy,null);
}

If you want to push data into cells, you always need to use an array, like the code above shows.

Because the setValuesAsync is -again- asynchronous, it requires a callback, for which I included an empty function called Dummy (not shown here). NB: Dummy can be replaced with "null", but I left it in because the code works as it is.

So does this all work? Have a look at the next page!

Comments

All comments about this page:

Comment by: Jim Maiolo (MSTF) (12/22/2011 4:04:33 PM)

As another alternative to using getRangeA1Async, you can write code like this:

var range = ewaCht.getActiveWorkbook().getActiveSheet().getRange(1, 0, 1, 1);
...
range.setValuesAsync(values, null, null);

This eliminates one of the asynchronous steps by using index-based range selection (which is 0-based):
"B1:B2" -> first row = 1, first column = 0, last row = 1, last column = 1

Also note that it is valid to pass in null for the callback.

Comment by: Jan Karel Pieterse (12/23/2011 6:37:52 AM)

Hi Jim,

Nice to see someone from the MSFT team respond here!

Thanks for the tip. I did see the synchronous getRange method later on when I browsed the MSDN content on the Ewa object. I decided to leave it as is in the article.

Comment by: James Dansey (12/29/2011 4:38:20 AM)

Hi Jan/Jim,

I am struggling to troubleshoot a little, perhaps you could help me?

If I wish to pass values to a cell named "SiteControl" in a sheet named "Brain", would it be correct to define the reference as follows?:

ewaCht.getActiveWorkbook().getRangeA1Async('Brain!SiteControl', getRangeA1Callback, null);

Everything is working perfectly so far, I just seem unable to get values to write!

Great work though. Very impressive tutorial!

James.

Comment by: Jan Karel Pieterse (12/29/2011 7:07:19 AM)

Hi James,

Have a look at this page which shows how to get a specific worksheet:

http://msdn.microsoft.com/en-us/library/ee660110.aspx

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.