C# data validation input box in excel file

The following C# program shows how to embedded a Data Validation Input Messages in an Excel file programmatically.

The following C# source code using Microsoft Excel 12.0 Object Library .In the previous section we saw how to import Microsoft Excel 12.0 Object Library in the C# project .

Create Excel file from CSharp

Data Validation Input Messages is using to embed a message in cells in an Excel Spread Sheet. When we select that cell the message will display . It is very useful for data entry purpose for define the rules for entering data and also we can put a notes about the data in the cell.

The following image shows how a Data Validation Input Message after embedded in an Excel cell.

csharp-excel-validation Full Source C#
using System; using System.Drawing; using System.Windows.Forms; using Excel = Microsoft.Office.Interop.Excel; namespace WindowsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { Excel.Application xlApp ; Excel.Workbook xlWorkBook ; Excel.Worksheet xlWorkSheet ; Excel.Range chartRange ; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); chartRange = xlWorkSheet.get_Range ("b2", "e9"); xlWorkSheet.get_Range("B5", "D5").Validation.Add(Excel.XlDVType.xlValidateInputOnly, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, misValue, misValue); xlWorkSheet.get_Range("B5", "D5").Validation.IgnoreBlank = true; xlWorkSheet.get_Range("B5", "B5").FormulaR1C1 = "Click Here to see Notes"; xlWorkSheet.get_Range("B5", "D5").Validation.InputTitle = "csharp.net-informations.com"; xlWorkSheet.get_Range("B5", "D5").Validation.ErrorTitle = "Error in Title"; xlWorkSheet.get_Range("B5", "D5").Validation.InputMessage = "Here is the notes embeded - you can enter 255 characters maximum in notes "; xlWorkSheet.get_Range("B5", "D5").Validation.ErrorMessage = "Error in Notes"; xlWorkSheet.get_Range("B5", "D5").Validation.ShowInput = true; xlWorkSheet.get_Range("B5", "D5").Validation.ShowError = true; xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Excel file created , you can find the file c:\\csharp-Excel.xls"); } private void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { obj = null; MessageBox.Show("Exception Occured while releasing object " + ex.ToString()); } finally { GC.Collect(); } } } }