Formatting Excel cell with Microsoft Interop

Question

Hello,

I generate some Excel file with Microsoft Interop, no problem, I can create files, sheet, file, password protect. But I'd like :

• for a specific range allow only numbers
• for an another specific range allow only numbers but only 0 or 1

Do you have an idea how to do this ?

Thanks,

Answer 1

If you want to validate an entry into the Cell, look at the Validation.Add method.

MSDN Example

Your second one is something like:

aRange.Validation.Add(XlDVType.xlValidateWholeNumber, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, 0, 1);

Answer 2

Hi, took a while but I think I got it. I am assuming you're using Excel 2007. I am also assuming you have a reference to a range already. Here is a quick example.

Excel.Worksheet sheet = this.Application.ActiveSheet as Excel.Worksheet;
Excel.Range range = sheet.get_Range("A1", "A5") as Excel.Range;

 //delete previous validation rules 
 range.Validation.Delete();
 range.Validation.Add(Excel.XlDVType.xlValidateWholeNumber,
                                 Excel.XlDVAlertStyle.xlValidAlertStop,
                                 Excel.XlFormatConditionOperator.xlBetween,
                                 0, 1);

This will add a validation of number between 0 and 1 for a specific range in this case between A1 and A5.

You can also play with the Validation object further to create custom Error Messages etc.

Hope this helps.