C# Microsoft Excel Automation - very simple Microsoft Excel AddIn to get Amazon deals

First of all here is how will it look: This is a Microsoft Excel 2003 Addin Project (under File -> New -> Project expand C# -> Office and you should see it). Here is the code for the addin connection : XmlDocument doc = new XmlDocument(); // Link to the Amazon feed. doc.Load("http://rssfeeds.s3.amazonaws.com/goldbox"); XmlNodeList xmlNodeItems = doc.DocumentElement.SelectNodes("/rss/channel/item"); Excel.Worksheet sheet = (Excel.Worksheet)Application.ActiveSheet; int i = 0; // Start reading each item in the rss feed. foreach (XmlNode

xmlNodeItem in xmlNodeItems)
{
i++;
if (i > 10) break;
// Get title, link and description, the
// description will be later used to retrieve image url.
string strTitle = xmlNodeItem.SelectSingleNode("title").InnerText;
string strLink = xmlNodeItem.SelectSingleNode("link").InnerText;
string strDescription = xmlNodeItem.SelectSingleNode("description").InnerText;

// Get the cell in which we will write the title of the product.
Excel.Range rngTitle = ((Excel.Worksheet)Application.ActiveSheet).get_Range("A" + i.ToString(), "A" + i.ToString());

// Some variables for the picture creation:

float picLeft = 50;
float picTop = float.Parse(rngTitle.Top.ToString()) + 5f;
float picWidth = 50;
float picHeight = 50;
string strImageFile = GetImageUrl(strDescription);


// Make fonts bold and write the text in the first cell ("A").
rngTitle.Font.Bold = true;
rngTitle.Value2 = strTitle;

// Add the picture
Excel.Shape pic = ((Excel.Worksheet)Application.ActiveSheet).Shapes.AddPicture(strImageFile, Microsoft.Office.Core.MsoTriState.msoCTrue, Microsoft.Office.Core.MsoTriState.msoCTrue, picLeft, picTop, picWidth, picHeight);

// Make the title centered both horizontally and vertically.

rngTitle.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rngTitle.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

// Make the picture move if neighbour cell is resized.
pic.Placement = Microsoft.Office.Interop.Excel.XlPlacement.xlMove;
}

Note : I removed the VSTO generated code region so I don't waste space. You shouldn't do this ;).

In other words if you copy the code you will need to paste it below the VSTO generated code region.

I also used a subroutine to extract the image url from the description, it is very simple, greedy regular expression:

     private string GetImageUrl(string strDescription)
     {
        Match mImage = Regex.Match(strDescription, "src=\".+?\"");
        return mImage.Value.Replace("src=", "").Replace("\"", "");
     }


Things to do:

1. First of all there is no good way to resize a cell in Excel. Your user will be presented very ugly screen. Here is how it actually looks if you don't resize the cells:




Very ugly huh?

2. The other think that bothers me is that Amazon lists about 100 products in their deals feed. So it is a bit slowly to get all the images (they are downloaded on the fly).

I will be very happy to discuss this problem with any of you.

Cheers ;).

3 ways to get true / false as string in C#

Who needs that much? Well, it's good to choose one and keep up with it. The first, oldfashioned way is to write the bool as string: string trueStr = "True"; But how would you know if the first letter should be uppercased? There are two better ways to get the True string or False string: string trueStr = true.ToString(); string trueStr = false.ToString() And the last method is to use the bool type : string trueStr = bool.TrueString; string trueStr = bool.FalseString; I think it is best to use the last one as it seems to me it was added exactly for such purposes.

>