In my previous posts I have discussed some of the following building blocks of using Excel object model using .Net.
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; Microsoft.Office.Interop.Excel.Range priceCell = dataRow.Cells; 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.