Troubleshooting Excel Web Service and Visual Studio 2008

After some hours developing with Excel Web Service and Visual Studio 2008 I have discovered that an only single consideration can save you a lot of time.

When you add the Excel Web Service in Visual Studio 2008 operations will have a different signature compared to MSDN samples, and if you try for example to set a range you may experience some problems with an object of type ArrayOfAnyType.

However all these issues and differences can be avoided with one simple configuration change:

1. Add the Excel Web Service.

2. Click the Visual Studio button to “Show All Files”, in Solution Explorer.

3. The Web Service will appear as a folder. You can click on it and some files appear.

4. You will see a file called Reference.svcmap. Open it.

5. In element: ReferenceGroup

a. In ClientOptions

i. In Serializer: Change Auto to XmlSerializer:
<Serializer>XmlSerializer</Serializer>

And everything will work fine!

In the link below there is a comment that explains what we have done. By default Visual Studio uses a DataContractSerializer, but an XmlSerializer should be used instead.

http://social.msdn.microsoft.com/Forums/en-US/sharepointexcel/thread/2fd36e6b-5fa7-47a4-9d79-b11493d18107/