In my previous posts I have discussed some of the following building blocks of using Excel object model using .Net.

• How to open excel file using .Net with COM Interop
• How to access rows and columns in Excel file using .Net

How to set color of a specified cell or row in Excel

Now that we have seen how to access the rows and cells programatically, lets see how we can format the cells in Excel sheet. One of the most common tasks we all perform is to set a color or change the font. Following code snippets show this can be done.

Microsoft.Office.Interop.Excel.Range dataRow = allRows[i];
Microsoft.Office.Interop.Excel.Range dateCell = dataRow.Cells[1];
Microsoft.Office.Interop.Excel.Range priceCell = dataRow.Cells[2];
priceCell.NumberFormat = "$0.00";
priceCell.Font.Bold = false;
priceCell.Interior.Color = System.Drawing.Color.BlanchedAlmond;

This code uses Color property on Interior object of a cell range. The documentation does not explain much on what this Color object is. After digging through some old VBA documentation, I found that you have to create Color .Net object to assign to this property. But there is some issue that you need to be aware of. Although you can set any color for the cell(s) but older versions of Excel do not support all colors. Older versions are limited to only 56 colors that you could specify using ColorIndex property. So if you specify any color that does not fall in that range, you will end up with following Microsoft Excel - Compatibility dialog box warning you about it. But if your users are not going to use older versions of Excel then you are all set to use any color you want.

In my previous post, I described How to open excel file using .Net with COM Interop. Now that you have opened the workbook, next you want to access data in rows and columns. If you are working with some tabular form of data then sometime you want to figure out how many rows and columns are there in the worksheet and how you can iterate over each of those. There are properties of Worksheet object that you can use to access row and columns. But there are little confusing.

There are properties named Rows and Cells on Worksheet object. But these properties represent all the rows and cells used and unused. What this means is that the value returned by these properties represent the maximum rows and cells for worksheet. What we are interested in are rows and cells that have data. There is a property UsedRange that returns all the rows and cells that have been used. This property returns Range object that you can query to get count of Rows and then on each row you can query Cells.

Following code snippet shows how to access rows and cells.

Worksheet currentPriceSheet = workbook.Worksheets["12-13-2010"];
if (null != currentPriceSheet)
{
  Console.WriteLine("Number of rows: {0}", currentPriceSheet.Rows.Count);
  Microsoft.Office.Interop.Excel.Range usedRange = currentPriceSheet.UsedRange;
  if (null != usedRange)
  {
    Microsoft.Office.Interop.Excel.Range rows = usedRange.Rows;
    Console.WriteLine("Row Count: {0}", rows.Count);
    for (var i = 1; i <= rows.Count; i++)
    {
      Microsoft.Office.Interop.Excel.Range row = rows[i, Type.Missing];
      Microsoft.Office.Interop.Excel.Range cells = row.Cells;
      Console.WriteLine("Column Count: {0}", cells.Count);
    }
  }
}

While working on creating a console application to manipulate an Excel file using COM interop, I ran into an issue where console application would not close even after I called Quit on the Application object. There was no exception or any error throw but the console application was still waiting for close.

The problem was that I had made a change in the worksheet but I did not save the workbook after it. Excel application sees that a workbook is in Dirty state so it wants to make sure that user of the application, which in my case is a console application, saves the unsaved changes before closing the workbook. There are few options you have here.

How to check if there are unsaved changes in Excel Workbook?

Workbook object exposes a property Saved that can be used to check if there are any unsaved changes. If value of this property is True that means you have unsaved changes.

if (myWorkbook.Saved)
{
 Console.Writeline("There are no unsaved changes to this workbook");
}

How to close Workbook without saving changes?

There may be cases where you have made some changes to some cells. That means Workbook is dirty now. But you really do not want to save the changes. If you do not take care of this, then your application will not close because Excel application instance is waiting for Workbook to be saved. You will use the same Saved property to deal with this. You can set this property to True to indicate that Workbook should be treated as saved and not prompted for saving.

if (!myWorkbook.Saved && !needToSave)
{
 myWorkbook.Saved = true;
}

This is all to how you can deal with saving or not saving of a dirty Excel Workbook.

Last week Microsoft released Office 2010. I have been using Beta and Release Candidate for quite some time and have been waiting for RTM version. So I downloaded installer from MSDN and launched it. Right off the bat, I get the following error dialog box.

---------------------------
Setup Errors
---------------------------
Setup is unable to proceed due to the following error(s):
Microsoft Office 2010 does not support upgrading from a prerelease 
version of Microsoft Office 2010. You must first uninstall any
prerelease versions of Microsoft Office 2010 products and associated 
technologies.
Correct the issue(s) listed above and re-run setup.
---------------------------
OK   
---------------------------

I was hoping there will be straight forward migration from Beta to RTM. But fair enough that Microsoft wants machine clean of all Beta and Release Candidate installations. So I went to Control Panel and opened up dialog box to uninstall programs. Picked up the Beta installation of Office 2010 and uninstalled it.

I launched Office 2010 installer again, this time I got the same message again that I need to uninstall previous versions of Office 2010. After banging my head for some time, I decided to look at list of all installed programs and find anything that says beta. Bingo, there I found an installation of Outlook Hotmail Connector (Beta) as shown below in screen shot.

After uninstalling Outlook Hotmail Connector, my installation of Office 2010 went very smoothly. If you are running into same issue, make sure that any component related to Office 2010 that you installed during Beta or Release candidate, you uninstall all of those. Some of those components may not say Beta in their name. But if they are related to Office 2010, they need to be uninstalled.