如果指定列中有值，请突出显示行

本文关键字：显示 如果 | 更新日期: 2023-09-27 18:34:52

我已经问过这个问题并在VBA宏中回答了它。它工作正常，但我现在真正想做的是我在 c# 中自动化它，而不是制作宏，并且必须填写坏列 + 值的特定范围。

目前我得到了这段代码：

public void HightlightErrors()
{
     Worksheet worksheet = (Worksheet)workbook.Sheets[1];
     Range last = worksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
     Range myRange = worksheet.get_Range("A1", last);
     int lastUsedRow = last.Row;
     int lastUsedColumn = last.Column;
     for (int i = 1; i < lastUsedColumn; i++) //Check each column to see if it is less than 5% filled.
     {
          Range currentColumn = worksheet.Columns[i];
          double a = application.WorksheetFunction.CountA(currentColumn);
          if ((a / lastUsedRow * 100) < 5)
          {
                //This range contains less than 5% 
                //Here i need to find the cell that has a value and highlight the Row.
          }
      }
      Marshal.ReleaseComObject(last);
      Marshal.ReleaseComObject(myRange);
}

但我似乎找不到与使用 Range.FindNext 语句查找值相关的任何内容。所有教程和解释都使用特定的数字或单词。我不可能离得那么远，谁能给我指出正确的方向？

如果指定列中有值，请突出显示行

我解决了问题，这是最终结果

public void HightlightErrors()
{
     Worksheet worksheet = (Worksheet)workbook.Sheets[1];
     Range last = worksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
     Range myRange = worksheet.get_Range("A1", last);
     int lastUsedRow = last.Row;
     int lastUsedColumn = last.Column;
     for (int i = 1; i < lastUsedColumn; i++) //Check each column to see if it is less than 5% filled.
     {
         Range currentColumn = worksheet.Columns[i];
         double a = application.WorksheetFunction.CountA(currentColumn);
         if ((a / lastUsedRow * 100) < 5)
         {
              string columnChar = GetExcelColumnName(i);
              string redCondition = "=COUNTA($"+ columnChar + "2)>0";
              dynamic format = myRange.FormatConditions.Add(XlFormatConditionType.xlExpression, Formula1: redCondition);
              format.Interior.Color = 0x0000FF;
         }
      }
      Marshal.ReleaseComObject(last);
      Marshal.ReleaseComObject(myRange);
}

获取 ExcelColumnName 是此方法：

    private string GetExcelColumnName(int columnNumber)
    {
        int dividend = columnNumber;
        string columnName = String.Empty;
        int modulo;
        while (dividend > 0)
        {
            modulo = (dividend - 1) % 26;
            columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
            dividend = (int)((dividend - modulo) / 26);
        } 
        return columnName;
    }
相关文章：