C# data validation input box in excel file

By: Rajesh P.S.

The following C# program demonstrates the process of programmatically embedding Data Validation Input Messages in an Excel file. By utilizing the Microsoft Excel 12.0 Object Library, the included source code showcases the necessary steps to create and incorporate Data Validation Input Messages into an Excel file using C#. As discussed in the previous section, we covered the procedure for importing the Microsoft Excel 12.0 Object Library into the C# project, thereby facilitating the integration of Excel-related functionalities. By following the outlined C# source code, developers can effectively implement Data Validation Input Messages within an Excel file, enhancing data integrity and providing informative prompts for users.

Create Excel file from CSharp

Data Validation Input Messages

Data Validation Input Messages are employed to embed informative messages within cells in an Excel spreadsheet. When a user selects a cell with a Data Validation Input Message, the designated message is displayed, providing guidance and instructions during data entry. This feature proves highly valuable for enforcing data entry rules and enabling users to input data accurately. Additionally, Data Validation Input Messages allow for the inclusion of notes or additional details pertaining to the data within specific cells, enhancing the overall comprehensibility and usability of the spreadsheet.

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

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(); } } } }

Next > Read and Import Excel File into DataSet or DataTable

Related Topics

More Related Topics.....