Excel Automation

I wanted to redesign couple of REST API’s which have data returned in Atom format. First thing I wanted to know what are the elements returned in response. I wanted to have an excel sheet, with URL heading on top row and underneath all other attributes returned from the API. Something like this

Manually this seems a lot of work, so here a small code which will do exactly that:

namespace ExcellAutomation
{
using System;
using System.Xml;
using System.Reflection;
using System.Net;
using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;



    class Program
    {
        const string NEWLINE = "\n\r" ;
        static void Main(string[] args)
        {
            string[] URL = new string[] {
                "Movie", @"http://rafatsarosh.com:80/v3.2/en-us/movie",
                "MovieDetail", @"http://rafatsarosh.com:80/v3.2/en-us/movie/99f96de9-9a4c-42de-84be-0a0c9d959657",
                "Tracks", @"http://rafatsarosh.com:80/v3.2/en-us/music/track",
                "TrackDetails", @"http://rafatsarosh.com:80/v3.2/en-us/music/track/1b120f00-0100-11db-89ca-0019b92a3933",
            };
            string sline = "";
            string sTab = "";
            Dictionary d = new Dictionary();
            MyExcel.StartExcel();
            int row = 1; int col = 0;
            foreach (string url in URL)
            {
                if (!url.StartsWith("http") )
                {
                    row = 1;
                    col++;
                    MyExcel.WriteValue(col, row, url);
                    row++;
                    continue;
                }
                sline = url + "\n\r";
                WebClient wc = new WebClient();
                d.Clear();
                XmlReader reader = XmlReader.Create(wc.OpenRead(url));
                while (reader.Read())
                {
                    if (reader.NodeType == XmlNodeType.Element)
                    {
                        if (reader.Name != "a:link")
                            sTab += "  ";

                        if (!d.ContainsKey(reader.Name))
                        {
                            d.Add(reader.Name, reader.Name);
                            sline += sTab + reader.Name + "\n\r";
                            MyExcel.WriteValue(col, row++, reader.Name);
                        }
                    }
                    if (reader.NodeType == XmlNodeType.EndElement)
                        sTab = sTab.Substring(0, sTab.Length - 2);
                }
                Console.WriteLine(sline);
            }
            Console.Read();
            MyExcel.Save();
        }
    }

static public class MyExcel
    {
        static Excel.Application oXL;
        static Excel._Workbook oWB;
        static Excel._Worksheet oSheet;

        public static void StartExcel () 
        {
            try
	     {
	        //Start Excel and get Application object.
	        oXL = new Excel.Application();
	        oXL.Visible = true;
              //Get a new workbook.
	        oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
		 oSheet = (Excel._Worksheet)oWB.ActiveSheet;
            } catch  ( Exception ex )
            {
                Console.WriteLine ( ex.Message );
            }
        }

        public static void WriteValue(int col, int row, string value)
        {
	     oSheet.Cells[row, col] = value;
            //Come customization
            if (value == "a:entry")
            {
                Excel.Range r = oSheet.Cells[row, col];
                r.Font.Color = ConsoleColor.Blue;
                r.Font.Bold = true;
            }

            if (value.EndsWith("s"))
            {
                Excel.Range r = oSheet.Cells[row, col];
                r.Font.Bold = true;
            }
		}

        public static void Save()
        {
            oXL.Quit();
        }
    }
  }

Disclaimer: This is not a production code, a throw away code, just to show you the Excel automation.