How to set the first item in a list as the default value in an Excel?

24 Jun 20265 minutes to read

You can programmatically make the first item in a data-validation list the default selection in an Excel file by using Syncfusion XlsIO. The following examples in C# (cross-platform and Windows-specific) and VB.NET demonstrate the process.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];

    worksheet["A1"].Value = "ListItem1";
    worksheet["A2"].Value = "ListItem2";
    worksheet["A3"].Value = "ListItem3";
    worksheet["A4"].Value = "ListItem4";
    
    worksheet.Range["C1"].Text = "Data Validation List in C3";
    worksheet.Range["C1"].AutofitColumns();

    //Data validation for the list
    IDataValidation listValidation = worksheet.Range["C3"].DataValidation;        
    listValidation.DataRange = worksheet.Range["A1:A4"];

    //Set the first item in the list as default value
    worksheet.Range["C3"].Value = worksheet.Range["C3"].DataValidation.DataRange.Cells[0].Value;
   

    #region Save
    //Saving the workbook
    workbook.SaveAs("../../../Output/ListValidation.xlsx");
    #endregion
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];

    worksheet["A1"].Value = "ListItem1";
    worksheet["A2"].Value = "ListItem2";
    worksheet["A3"].Value = "ListItem3";
    worksheet["A4"].Value = "ListItem4";

    worksheet.Range["C1"].Text = "Data Validation List in C3";
    worksheet.Range["C1"].AutofitColumns();

    //Data validation for the list
    IDataValidation listValidation = worksheet.Range["C3"].DataValidation;
    listValidation.DataRange = worksheet.Range["A1:A4"];

    //Set the first item in the list as default value
    worksheet.Range["C3"].Value = worksheet.Range["C3"].DataValidation.DataRange.Cells[0].Value;


    #region Save
    //Saving the workbook
    workbook.SaveAs("../../Output/ListValidation.xlsx");
    #endregion
}
Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx

    'Create a workbook and access the first worksheet
    Dim workbook As IWorkbook = application.Workbooks.Create(1)
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    'Populate list items
    worksheet("A1").Value = "ListItem1"
    worksheet("A2").Value = "ListItem2"
    worksheet("A3").Value = "ListItem3"
    worksheet("A4").Value = "ListItem4"

    'Header for the validation cell
    worksheet.Range("C1").Text = "Data Validation List in C3"
    worksheet.Range("C1").AutofitColumns()

    'Create list‐type data validation for cell C3
    Dim listValidation As IDataValidation = worksheet.Range("C3").DataValidation
    listValidation.DataRange = worksheet.Range("A1:A4")

    'Set the first list item as the default value
    worksheet.Range("C3").Value = listValidation.DataRange.Cells(0).Value

    'Save the workbook
    workbook.SaveAs("../../Output/ListValidation.xlsx")
End Using

A complete working example to set the first item in a list as the default selected value in an Excel File is available on this GitHub page.