Posts
  • CarlosAg Blog

    See you at PDC

    • 6 Comments

    Well, I’m really excited to be heading to PDC’05, it sure promise to be an interesting one where we can finally show off so many new technologies we have been working so hard during this past few months to create.

    I will be there along with many of my teammates from IIS trying to show off the really cool new features that we have built into the next version of IIS which I'm sure will rock your mind if you are a Web lover like me.

    So I really hope to meet a lot of you guys out there and try to answer any questions you might have.

     

  • CarlosAg Blog

    Generating Excel Workooks without Excel

    • 53 Comments

    Why I wrote Excel Xml Writer
    One day I found myself having to build a Web Application that one of the requirements involved generating a nice Excel Workbook that users could then play with. This required quite some styling and several other features that you can only do with Excel (like setting printer options and document properties).
     
    Well, coming from Consulting, this requirement was no strange at all to me, and I had to dealt with this problem many times years ago. However then, it was a different story, Managed code and Xml were not even created, and COM was the only option I had. The only solution was using Excel Automation to build Workbooks that supported all the features I required. Yes, I know HTML could do the trick to just generate a table and set the content type to an Excel Application but this certainly leaves you lacking of control in several Excel features like document properties (Author, custom properties, etc), printer settings and more.
     
    Excel Automation
    If you ever worked with Excel Automation you know that it is an extremely powerful (and complicated) object model. However, this power does not come for free, every time you create an Excel.Application, you are essentially running a new Excel.exe instance which is nothing but cheap, and for that reason (and many more) you certainly do not want to do that in a Web Application where thousands of users might run the nice “Export to Excel” link and you end up with thousand of processes being created and destroyed.
     
    Just to illustrate my point, I created the following sample.
     
    C#
    using System;
    using 
    Excel Microsoft.Office.Interop.Excel;
    using 
    System.Runtime.InteropServices;
    using 
    Missing System.Reflection.Missing;

    static class 
    Program {

        
    static void Main() {
            
    int tick Environment.TickCount;
            
    // Create the Excel Application
            
    Excel.Application excel = new Excel.Application();

            try 
    {
                
    // make it visible for demostration purposes
                
    excel.Visible = true;

                
    // Add a Workbook
                
    Excel.Workbook workbook excel.Workbooks.Add(Missing.Value);

                
    // Set the author
                
    workbook.Author "CarlosAg";

                
    // Create a Style
                
    Excel.Style style workbook.Styles.Add("Style1", Missing.Value);
                
    style.Font.Bold = true;

                
    // Add a new Worksheet
                
    Excel.Worksheet sheet =
                    
    (Excel.Worksheet)workbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                
    // Set some text to a cell
                
    Excel.Range range ((Excel.Range)sheet.Cells[11]);
                
    range.Style style;
                
    range.Value2 "Hello World";

                
    workbook.SaveAs(@"c:\test.xls", Missing.Value, Missing.Value, Missing.Value,
                                Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
                                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)
    ;

                
    // Finally close the Workbook and save it
                
    workbook.Close(false, Missing.Value, Missing.Value);

                
    // Close Excel
                
    excel.Quit();

            
    }
            
    finally {
                
    // Make sure we release the reference to the underlying COM object
                
    Marshal.ReleaseComObject(excel);
            
    }

            Console.WriteLine(
    "Time: {0}", Environment.TickCount - tick);
        
    }
    }

    Colorized by: CarlosAg.CodeColorizer Note: If you want to run the application you need to add a Reference to Microsoft Excel COM library.
     
    Well, I ran this really simple application cold in my machine that has Office 2003, and running it took almost 3000 milliseconds. Of course if you run it again it runs in about 1 second. But this solution will just not scale in a Web Application.
     
    Another big problem with this approach is the code itself, if you take a close look I had to type almost 20 references to Missing.Value.
     
    Solution
    Luckily ever since Office XP, Excel supports a new file format called Xml Workbook (or something like that), this allow you to create an Xml document that follows a certain schema and Excel will work as if it were the binary XLS format (though not all features at supported like Charts).
     
    Now I have new options, I could just generate the Xml using an XmlDocument or even better using an XmlWriter; but doing so it is quite cumbersome, since you need to understand a lot of Xml, Schemas, and Namespaces and it is quite probably that you will mess up something like closing an element incorrectly or adding the wrong namespace, or prefix, etc.
     
    For that reason I thought to build a lightweight fast wrapper to the Excel Xml Workbook schema. This way my application manipulates an object model that looks similar to Excel Automation OM but it is lightweight, 100% managed, and that in the end serialize itself into an Xml using an XmlWriter.
     
    This is exactly what Excel Xml Writer is, just a simple object model that generates Xml following the Excel Xml Workbook schema. After almost done with it, I thought I could add the ability to load the Xml as well, so I added that feature. This turned out to be extremely useful when loading Excel worksheets from the Office Web Components, and really cool usage, so that you can embed workbooks in your page, and then use AJAX like technology to post the XMLData property back and load it in the server side to do the actual processing in your Database, etc.
     
    You can download it for free at http://www.carlosag.net/Tools/ExcelXmlWriter/Default.aspx.
     
    Now, the code to generate the same workbook we just did using my library the code looks like:
     
     
    using System;
    using 
    CarlosAg.ExcelXmlWriter;

    class 
    Program {
        
    static void Main(string[] args) {
            
    int ticks Environment.TickCount;

            
    // Create the workbook
            
    Workbook book = new Workbook();
            
    // Set the author
            
    book.Properties.Author "CarlosAg";

            
    // Add some style
            
    WorksheetStyle style book.Styles.Add("style1");
            
    style.Font.Bold = true;

            
    Worksheet sheet book.Worksheets.Add("SampleSheet");

            
    WorksheetRow Row0 sheet.Table.Rows.Add();
            
    // Add a cell
            
    Row0.Cells.Add("Hello World", DataType.String, "style1");

            
    // Save it
            
    book.Save(@"c:\test.xls");

            
    Console.WriteLine("Time:{0}", Environment.TickCount - ticks);
        
    }
    }

    Colorized by: CarlosAg.CodeColorizer

    Several differences:
    1)      You don’t actually need Excel installed in your server to run this program since it does not uses Excel at all, just Xml.
    2)      Working set of your application is way smaller than using the Interop libraries
    3)      This is more than 100 times faster to run.
    4)      Code looks much more simpler.
    5)      Since it generates Xml, you can actually stream it directly in an ASP.NET application to the Response.OutputStream without ever saving it into the file system.
    6)      This solution will scale to thousands of users since it does not require any creation of processes.
     
    Now, even better I decided to write a code generator tool so that you don’t need to write all the styling code and superficial stuff and just focus on the actual data. This tool allows you to open an Excel Xml Workbook that you have created in Excel, and it will generate the C# or VB.NET code that you can use to generate it. This means that you can create the skeleton with all the formatting options in Excel and then just generate the code.
     
     
    Conclusion
    Don’t get me wrong Excel team did an awesome job with the Automation support for all Office products, however this has been around for several years, but it definitely lacks of support for Web based applications (asside from Office Web Components). Luckily they are addressing this in the next release of Office where they will have some awesome support for server side applications and many exciting stuff.
     
    In the mean time you might find really exciting working with Xml in Office since they have great support and it will only get better with time.
  • CarlosAg Blog

    My First Post

    • 2 Comments

    Well, today is my very first post ever.

    Soon I hope to have time to start writing something that hopefully is interesting at all. Some of the first ideas I intend to dump here are about the tools that I posted in my site so that people trying to use them understand a little bit more about the problem I was trying to solve, the rationale behind them and the design issues I ran into and other stuff. Some of those tools include a Chart Control for ASP.NET, a library to generate Excel Xml Workbooks, C# and VB.NET Code translation, Code colorizer, and other random tools.

     

    For now, this is just my meaningless boring post to say ‘hello' and thanks for the people who kept 'bugging' [:)] me to finally do this.

     

Page 10 of 10 (93 items) «678910