How to check if any cell without a title in the Excel file is highlighted in bold or italics

Question

We use an Excel worksheet that contains some business logic (and therefore it is often edited by non-IT). Some C ++ code is generated in VBA from the same sheet - I know, stupid. I plan to write a bunch of unit tests on this sheet, providing a strict format for everyone. For example, things should be sorted alphabetically. I didn’t do this before; not even sure which library to use. To keep my feet wet, I would like to find all cells that have a font other than "auto color, simple style, Arial, size 10" - for example. red or bold or size 11 or ComicSans font. Then I want to check if these cells are "non-headers". Header cells are those that are part of well-known named ranges. For instance,if such a cell belongs to the named range "XYZheaders", then this is OK. If not, then I want to report the coordinates of the cell (ideally, as something human-readable, like "D25" for each cell that has a problem, and also indicate whether the problem is with color, font type, style or size.

EDIT: I just put generosity on this question because I'm looking for a complete C # sample. Please ask questions if you think my question is ambiguous.

Answer 1

Here is my solution. I tested it with some Excel 2007 files (.xlsx). The program can be built using VS 2010 (oriented to .NET 4) with the following four links: Microsoft.CSharp, Microsoft.Office.Interop.Excel, System and System.Core.

Using .NET 4 simplifies working with Excel.

Anyway, here is the code:

using System;
using System.Collections.Generic;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelTest
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application excelapplication = null;
            Excel.Workbook workbook = null;

            try
            {
                excelapplication = new Excel.Application();
                workbook = excelapplication.Workbooks.Open(args[0]);
                var errors = new Dictionary<string, List<string>>();
                foreach (Excel.Worksheet sheet in workbook.Sheets)
                {
                    int rowCount = sheet.UsedRange.Cells.Rows.Count;
                    int colCount = sheet.UsedRange.Cells.Columns.Count;
                    var usedCells = sheet.UsedRange.Cells;

                    for (int i = 1; i <= rowCount; i++)
                    {
                        for (int j = 1; j <= colCount; j++)
                        {
                            Excel.Range range = usedCells[i, j];
                            List<string> cellErrors;
                            if (HasNonDefaultFont(range, out cellErrors))
                            {
                                if (!IsHeaderCell(workbook, range))
                                {
                                    string cellDisplayTitle = String.Format("{0}!{1}", sheet.Name, range.Address);
                                    errors[cellDisplayTitle] = cellErrors;
                                }
                            }
                        }
                    }
                }
                ReportErrors(errors);
            }
            finally
            {
                if (workbook != null)
                    workbook.Close();
                if (excelapplication != null)
                    excelapplication.Quit();
            }
        }

        static bool HasNonDefaultFont(Excel.Range range, out List<string> differences)
        {
            differences = new List<string>();

            if (range.Font.Color != 0.0)
                differences.Add("Has font-color");

            if (range.Font.Bold)
                differences.Add("Is bold");

            if (range.Font.Italic)
                differences.Add("Is italic");

            if (range.Font.Underline != (int)Microsoft.Office.Interop.Excel.XlUnderlineStyle.xlUnderlineStyleNone)
                differences.Add("Is underline");

            if (range.Font.Strikethrough)
                differences.Add("Is strikethrough");

            if (range.Font.Name != "Arial")
                differences.Add(String.Format("Font is {0}", range.Font.Name));

            if (range.Font.Size != 10)
                differences.Add(String.Format("Font size is {0}", range.Font.Size));

            return differences.Count != 0;
        }

        static bool IsHeaderCell(Excel.Workbook workbook, Excel.Range range)
        {
            // Look through workbook names:
            foreach (Excel.Name namedRange in workbook.Names)
            {
                if (range.Parent == namedRange.RefersToRange.Parent && range.Application.Intersect(range, namedRange.RefersToRange) != null)
                    return true;
            }

            // Look through worksheet-names.
            foreach (Excel.Name namedRange in range.Worksheet.Names)
            {
                if (range.Parent == namedRange.RefersToRange.Parent && range.Worksheet.Application.Intersect(range, namedRange.RefersToRange) != null)
                    return true;
            }
            return false;
        }

        static void ReportErrors(Dictionary<string, List<string>> errors)
        {
            if (errors.Count > 0)
            {
                Console.WriteLine("Found the following errors:");
                Console.WriteLine("---------------------------------");
                Console.WriteLine("{0,-15} | Error", "Cell");
                Console.WriteLine("---------------------------------");
            }

            foreach (KeyValuePair<string, List<string>> kv in errors)
                Console.WriteLine("{0,-15} | {1}", kv.Key, kv.Value.Aggregate((e, s) => e + ", " + s));
        }
    }
}

excel . , . " " , , , .

, ​​ , , , .

Answer 2

, . , , ( F9 ).

Option Explicit

Public Function IsDataCellBoldOrItalic() As Boolean
    Dim rngName As Name
    Dim intersectRange As Name

    For Each rngName In ActiveWorkbook.Names
        If Not Intersect(rngName.RefersToRange, Application.ThisCell) Is Nothing Then
            IsDataCellBoldOrItalic = False
            Exit Function
        End If
    Next

    ''# Now we know we are not in a "header" cell
    IsDataCellBoldOrItalic = Application.ThisCell.Font.Bold Or Application.ThisCell.Font.Italic

End Function