title: C# Add validation list to excel error
body: <p>I'm writing a program to add a set of user data to the excel file.
  Some of the cells need to have a drop down list, which is a validation list. The list of data is provided by user.</p>
  
  <p>While exporting the data to excel using C# code, sometimes the data will cause programs but sometimes not.</p>
  
  <p>There's no error from program side when I add data</p>
  
  <pre><code>validationValues.Add("Interface Phase 1 Cycle3");
  validationValues.Add("Performance Testing");
  validationValues.Add("Pre SIT (HLB)");
  validationValues.Add("Pre-SIT FE (EtoE) Phase1 C1");
  validationValues.Add("Sanity Test(HLB)");
  validationValues.Add("SIT Cycle 1 (HLB)");
  validationValues.Add("SIT Cycle 2 (HLB)");
  validationValues.Add("SIT Cycle 3 (HLB)");
  validationValues.Add("SIT FE (EtoE) Phase1 C1");
  validationValues.Add("SIT FE (EtoE) Phase1 C2");
  validationValues.Add("SIT FE (EtoE) Phase1 C3");
  validationValues.Add("SIT Sanity Check");
  validationValues.Add("SIT STB Cycle 1");
  validationValues.Add("SIT STB Cycle 2");
  validationValues.Add("SIT STB Cycle 3");
  validationValues.Add("SIT STB Sanity Check");
  validationValues.Add("SIT STB User Interface Issues");
  validationValues.Add("SIT WOG Phase1 C1");
  validationValues.Add("SIT WOG Phase1 C2");
  validationValues.Add("SIT WOG Phase1 C3");
  if (validationValues.Count != 0)
      AddDataValidation(xlWorkSheet, validationValues, "R:R");
  validationValues.Clear();
  </code></pre>
  
  <p>In AddDataValidation function:</p>
  
  <pre><code>    private static void AddDataValidation(Excel.Worksheet worksheet, List&lt;string&gt; validationValues, string col)
      {
          try
          {
  
              string values = string.Join(",", validationValues);
              Excel.Range column = worksheet.UsedRange.Columns[col, Type.Missing];
              column.Validation.Delete();
              column.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertInformation, XlFormatConditionOperator.xlBetween, values, Type.Missing);
              column.Validation.IgnoreBlank = true; 
  
          }
          catch (Exception exception)
          {
              //This part should not be reached, but is used for stability-reasons
              throw new Exception(String.Format("Error when adding a Validation with restriction to the specified Column:{0}, Message: {1}", col, exception));
  
          }
      }
  </code></pre>
  
  <p>The error is like this:
  <img src="https://i.stack.imgur.com/OwMRg.png" alt="Error 1 when open excel">
  <img src="https://i.stack.imgur.com/EQclL.png" alt="Error 2 when accept excel correction">
  <strong>Upon opening of the excel file, it removes the validation feature, no drop down list will shown to the cells.</strong></p>
  
  <p>The XML file contents are here:
  
  error025200_02.xmlErrors were detected in file 'C:\Users\wendylinlin\Desktop\Reports-Common20152241477_Defects.xlsx'Removed Feature: Data validation from /xl/worksheets/sheet1.xml part</p>
  
  <p>However, if I comment out the validations values with bracket, means don't add in those with ( or ), the validation is working fine and I can see the drop down list after open excel.</p>
  
  <p><strong>I tried to do a string manipulation, replace all "(" and ")" with a space, but it gives the same error. Some other strings with "-" and "," give the similar problem as well. But replacement of the special characters don't help at all for all cases.</strong></p>
  
  <p>Anyone have idea what's happening? How can I have the correct validation value to the excel and prevent excel to remove the validation list?</p>
  
  <p>Thank you!!</p>