用Excel范围验证使用C＃ [英] Range Validation with Excel using C#

问题描述

我一直期待在互联网上4个小时，我只是不能做到这一点。

I've been looking on the internet for 4 hours and I just can't do it.

我的目标：创建一个组合，我可以我的项目进行排序当我点击其中之一，该项目单独出现。

My objectives : create a combo where I can sort my items and when I click on one of them, the item appears alone.

在Excel中很容易做到，但我不能做在C＃。

In Excel, easy to do, but I can't do it in C#.

我发现了这样的回答：的其他话题，但我不明白的地方的this.Controls的由来。

I found this answer : Other topic, but I can't understand where the "this.Controls" comes from.

感谢您的帮助。

推荐答案

如果你想使用验证用于这一目的下面的方法是我写的添加验证当用户点击单元格中出现一个小信息框：

if you want to use a validation for that purpose the following method was written by me to add a Validation and a small Infobox that appears when the user clicks on the cell:

/// <summary>
/// Adds a small Infobox and a Validation with restriction (only these values will be selectable) to the specified cell.
/// </summary>
/// <param name="worksheet">The excel-sheet</param>
/// <param name="rowNr">1-based row index of the cell that will contain the validation</param>
/// <param name="columnNr">1-based column index of the cell that will contain the validation</param>
/// <param name="title">Title of the Infobox</param>
/// <param name="message">Message in the Infobox</param>
/// <param name="validationValues">List of available values for selection of the cell. No other value, than this list is allowed to be used.</param>
/// <exception cref="Exception">Thrown, if an error occurs, or the worksheet was null.</exception>
public static void AddDataValidation(Worksheet worksheet, int rowNr, int columnNr, string title, string message, List<string> validationValues)
{
    //If the message-string is too long (more than 255 characters, prune it)
    if (message.Length > 255)
        message = message.Substring(0, 254);

    try
    {
        //The validation requires a ';'-separated list of values, that goes as the restrictions-parameter.
        //Fold the list, so you can add it as restriction. (Result is "Value1;Value2;Value3")
        //If you use another separation-character (e.g in US) change the ; appropriately (e.g. to the ,)
        string values = string.Join(";", validationValues);
        //Select the specified cell
        Range cell = worksheet.Cells[rowNr, columnNr];
        //Delete any previous validation
        cell.Validation.Delete();
        //Add the validation, that only allowes selection of provided values.
        cell.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, values, Type.Missing);
        cell.Validation.IgnoreBlank = true;
        //Optional put a message there
        cell.Validation.InputTitle = title;
        cell.Validation.InputMessage = message;

   }
   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 cell Row:{0}, Column:{1}, Message: {2}", rowNr, columnNr, message), exception);

    }
}

如果您不需要信息框，见好就收出在哪里变量标题或消息出现的部分。

If you don't need the Infobox, just leave out the parts where the variables title or message appear.

这篇关于用Excel范围验证使用C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！