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







Related Topics
  1. How to create Excel file in C#
  2. How to open an Excel file in C#
  3. How to read an Excel file using C#
  4. How to programmatically Add New Worksheets
  5. How to delete worksheet from an excel file
  6. How to format an Excel file using C#
  7. How to insert a picture in excel from C# App
  8. How to insert a background picture in excel
  9. How to create Excel Chart from C#
  10. How to export excel chart from C#
  11. How to excel chart in C# picturebox
  12. Read and Import Excel File into DataSet or DataTable
  13. How to insert data to Excel file using OLEDB
  14. How to update data in Excel file using OLEDB
  15. How to export databse to excel file
  16. How to export DataGridView to excel file