list data validation automation excel

Question

I am automating excel from c# and want to put list data validation on a column I have so far:

//Creat Excel WorkBook with WorkSheet

Excel.Application objExcel = new Excel.Application();

Excel.Workbook objWorkBook = objExcel.Workbooks.Add(System.Reflection.Missing.Value);

Excel.Worksheet objSheet = (Excel.Worksheet)objWorkBook.Worksheets.get_Item(1);

//put column heading in row 1

objSheet.get_Range("a1", System.Reflection.Missing.Value).Value2 = "rs_id";

objSheet.get_Range("b1", System.Reflection.Missing.Value).Value2 = "manufacturer";

objSheet.get_Range("c1", System.Reflection.Missing.Value).Value2 = "title";

objSheet.get_Range("d1", System.Reflection.Missing.Value).Value2 = "description";

objSheet.get_Range("e1", System.Reflection.Missing.Value).Value2 = "quantity";

objSheet.get_Range("f1", System.Reflection.Missing.Value).Value2 = "discrepancy";

objSheet.get_Range("g1", System.Reflection.Missing.Value).Value2 = "cost_price";

objSheet.get_Range("h1", System.Reflection.Missing.Value).Value2 = "BIN_price";

objSheet.get_Range("i1", System.Reflection.Missing.Value).Value2 = "quantity_per_sale";

objSheet.get_Range("j1", System.Reflection.Missing.Value).Value2 = "category";

//find out how many need to be preped befor the can be listed

SqlDataAdapter DaFindNumberToPrep = new SqlDataAdapter();

DataSet DsToPrep = new DataSet();

string SelectToPrep = "SELECT * FROM test_db.dbo.deliveries WHERE ready_to_list = 'no'";

int NumberToPrep = 0;

using (SqlConnection test_db_conn = new SqlConnection(test_db_ConnectionStrin))

{

DaFindNumberToPrep = new SqlDataAdapter(SelectToPrep, test_db_conn);

DaFindNumberToPrep.Fill(DsToPrep);

NumberToPrep = (int)DsToPrep.Tables[0].Rows[0][0];

}

//loop to populate sheet

int row_index = 2;

foreach (DataRow row in DsToPrep.Tables[0].Rows)

{

objSheet.get_Range("a" + row_index.ToString(), System.Reflection.Missing.Value).Value2 = row["rs_id"].ToString();

objSheet.get_Range("b" + row_index.ToString(), System.Reflection.Missing.Value).Value2 = row["manufacturer"].ToString();

objSheet.get_Range("c" + row_index.ToString(), System.Reflection.Missing.Value).Value2 = row["title"].ToString();

objSheet.get_Range("d" + row_index.ToString(), System.Reflection.Missing.Value).Value2 = row["description"].ToString();

objSheet.get_Range("e" + row_index.ToString(), System.Reflection.Missing.Value).Value2 = (int)row["quantity"];

objSheet.get_Range("f" + row_index.ToString(), System.Reflection.Missing.Value).Value2 = row["discrepancy"].ToString();

objSheet.get_Range("g" + row_index.ToString(), System.Reflection.Missing.Value).Value2 = (float)row["cost_price"];

row_index++;

}

//Display results

objExcel.Visible = true;

but once the loop is done I want to set "j2":"j"+row_index.ToString() to have list data validations ie it can only take the values in the range "k1":"k6" so that the user can just choose from the drop down list

Luca Bottaro · Answer

Hi you might want to take a look at this code /// /// This method creates a Excel filtered list. /// Only the cell content matching the filter criteria will be selected /// /// Worksheet to use /// First cell of List (top left eg A2) /// Last cell of List (bottom right eg J22) private void Excel_createFilteredList(Worksheet ws string excelList_firstCell string excelList_lastCell) { ws.get_Range(excelList_firstCell excelList_lastCell).AutoFilter(1 System.Reflection.Missing.Value XlAutoFilterOperator.xlAnd System.Reflection.Missing.Value true); ws.get_Range(excelList_firstCell excelList_lastCell).Borders.LineStyle = XlLineStyle.xlContinuous; } It's taken as example from my snippet at snip2code.com .

alec · Answer

I am going to try this objSheet.get_Range(A1 System.Reflection.Missing.Value).Validation.Add(Excel.XlDVType.xlValidateList System.Reflection.Missing.Value CategoryListRange System.Reflection.Missing.Value); if it works I will mark this as correct answer FYI CategoryListRange is a range of excel cells that I set earlier on in the code

Sebastian · Answer

have a look at the following sample: workSheet.get_Range( A1 ).Validation.Add( XlDVType .xlValidateList XlDVAlertStyle .xlValidAlertStop XlFormatConditionOperator .xlBetween =$A2:$E2  Missing .Value); workSheet.get_Range( A1 ).Validation.IgnoreBlank = true ; workSheet.get_Range( A1 ).Validation.InCellDropdown = true ; workSheet.get_Range( A1 ).Validation.ShowInput = true ; workSheet.get_Range( A1 ).Validation.ShowError = true ; // displayed as tooltiptext workSheet.get_Range( A1 ).Validation.InputTitle =  ; workSheet.get_Range( A1 ).Validation.InputMessage =  ; // message box in casse of input dont match with list entrys workSheet.get_Range( A1 ).Validation.ErrorTitle =  ; workSheet.get_Range( A1 ).Validation.ErrorMessage =  ;