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 .
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.
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
- How to create Excel file in C#
- How to open an Excel file in C#
- How to read an Excel file using C#
- How to programmatically Add New Worksheets
- How to delete worksheet from an excel file
- How to format an Excel file using C#
- How to insert a picture in excel from C# App
- How to insert a background picture in excel
- How to create Excel Chart from C#
- How to export excel chart from C#
- How to excel chart in C# picturebox
- Read and Import Excel File into DataSet or DataTable
- How to insert data to Excel file using OLEDB
- How to update data in Excel file using OLEDB
- How to export databse to excel file
- How to export DataGridView to excel file