Host and compile C# in Excel Services

Who says you can’t write C# Code in Excel?

Probably not what you were expecting…

I wrote a quick console application while testing Excel Services which uses the code provided by Robbe Morris to compile a .NET application on the fly, using source code stored as text format in an Excel spreadsheet and published to Excel Services.

So now you can write C# code in Excel and compile without Visual Studio, and host in a central location. 

Please comment if you can think of a good (or bad) application for this.  I can think of a few.

• How about allowing the business to write custom code snippets? 
• Storing business logic & data tiers in a spreadsheet? 
• Complex calculations that require the power of C# or external libraries to run?

Or maybe just something to add to the list of stupid Excel tricks.

Here is the code to go with this solution.  (Limited to under 50 lines stored in the text).

using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Text; 
using System.Xml; 
using System.Web.Services.Protocols; 
using System.Security.Principal; 
using SourceCodeManager; 
using System.Reflection; 
using System.Diagnostics; 

namespace HelloWorldConsoleApp 
{ 
    class Program 
    { 
        private const string Workbook = @"http://bi-vpc/Reports/ReportsLibrary/CodeSnippet.xlsx"; 
        static void Main(string[] args) 
        { 
            es.ExcelService s = new es.ExcelService(); 
            s.SoapVersion = SoapProtocolVersion.Soap12; 
            string sessionId = null; 

            try 
            { 
                s.Credentials = System.Net.CredentialCache.DefaultCredentials; 
                es.Status[] status; 
                Console.WriteLine("Opening"); 
                sessionId = s.OpenWorkbook(Workbook, String.Empty, String.Empty, out status); 
                Console.WriteLine("Session ID: {0}", sessionId); 
                Debug.WriteLine(WindowsIdentity.GetCurrent().Name.ToString()); 
                Debug.WriteLine(Workbook); 
                Debug.WriteLine(sessionId); 
                object cellVal; 
                StringBuilder sb = new StringBuilder(); 

                for (int i = 1; i < 50; i++) 
                { 
                    cellVal = s.GetCellA1(sessionId, "Sheet1", "A" + i.ToString(), true, out status); 
                    Debug.WriteLine(cellVal); 
                    sb.Append(cellVal); 
                } 

                Debug.Write(sb.ToString()); 
                Console.Write(sb.ToString()); 
                string returnMsg = CompileCode(sb.ToString()); // The magic happens here
                Debug.WriteLine(returnMsg); 
                Console.WriteLine(returnMsg); 

            } 
            catch (SoapException ex) 
            { 
                Debug.WriteLine("Error: {0} ", ex.SubCode.Code.Name); 
                Console.WriteLine("Error: {0} ", ex.SubCode.Code.Name); 

            } 
            catch (Exception ex) 
            { 
                Debug.WriteLine("Unknown Error: " + ex.ToString()); 
                Console.WriteLine("Unknown Error: {0}", ex); 
            } 
            finally 
            { 
                if (!String.IsNullOrEmpty(sessionId)) 
                    s.CloseWorkbook(sessionId); 
                s = null; 
            } 

        } 
        protected static string CompileCode(string sourceText) 
        { 
            SourceCodeManager.Runtime runtime = new SourceCodeManager.Runtime(); 
            SourceCodeManager.CodeContainer codeContainer = null; 
            string methodName = "Validate"; 
            object[] parameters = null; 

            try 
            { 

                // Simulate pulling code from disk or from database. 

                codeContainer = new SourceCodeManager.CodeContainer(); 
                codeContainer.ClassName = "MyClassName"; 
                codeContainer.NameSpace = "MyNameSpace"; 
                codeContainer.UniqueKey = "robbe"; 
                codeContainer.SourceCode = sourceText; 

                // Compare this code with that which is set in 
                // our static List<CodeContainer> by the UniqueKey. 
                // If not found, compile and add. 
                // If found but source code is different, delete, compile, and add. 
                // If found and source code is the same, grab reference to 
                // previously compiled assembly. 

                SourceCodeManager.Registration.Update(codeContainer); 

                // Let's create some sample parameters to pass into 
                // our dynamically compiled class/method. 

                parameters = new object[2]; 

                List<int> parameter1 = new List<int>(); 

                parameter1.Add(5); 
                parameter1.Add(10); 

                List<int> parameter2 = new List<int>(); 

                parameter2.Add(50); 
                parameter2.Add(100); 

                parameters[0] = parameter1; 
                parameters[1] = parameter2; 

                // Execute the desired method and pass in our parameters. 
                // Our Execute method will always return an object.  So, 
                // we need to know its actual desired return type ahead 
                // of time if we want to convert it to the property Type. 

                bool returnValue = (bool)runtime.Execute(codeContainer, 
                                                         methodName, 
                                                         parameters); 

                return "Method result: " + returnValue.ToString(); 

            } 
            catch (Exception err) 
            { 

                return err.ToString(); 
            } 
            finally { 

                runtime = null; 

            } 
        } 
    } 
} 

 USE AT YOUR OWN RISK!

ASP.NET 2.0 - Safely Compile And Execute Source Code Dynamically By Robbe Morris