Automatic spell checking in EXCEL

Good time of the day.

Just the other day, "experienced users" Excel addressed me and my colleagues. According to the specifics of work, “experienced users” often use an office suite for documentation. Oddly enough, Excel spreadsheets are also common. As on Habré, it is not permissible to issue documents with spelling errors. There is no standard Excel feature for spelling errors. In this regard, I developed Add-ins for Excel that would implement such functionality.

Checking for errors is not so difficult, there is a standard SpellCheck, which I used. It is supposed to implement 2 parts:
1) check of already filled cells
2) check in real time at input.
The logic of work is simple. In the first case, we check all the cells, if there is an error, select the tokens and check each token separately, for erroneous tokens, create a note and highlight the erroneous token in red. In the second case, we simply subscribe to the cell change event and do everything as in 1 case with only a smaller range.

Let's
start : Run VisualStudio, create an extension for Excel 2007.
When registering the plugin, we subscribe to the Cell-Changed event and call our function (the function will be described a bit later).
private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            //Подпишемся на событие изменения ячейки
            Globals.ThisAddIn.Application.Cells.Worksheet.Change += new Excel.DocEvents_ChangeEventHandler(Worksheet_Change);
        }
void Worksheet_Change(Excel.Range Target)
        {
            SpellCheck.SpellChecker(Target);
        }


We won’t do anything else during registration. Verification of the entire book will be left to the user on call.
Create your own tab in Ribbon. Add a new Item Ribbon (Visual Designer) /
Create a new tab, do not touch the first one, because she is reserved.
Also, add 2 check buttons: check the entire book, and check the current sheet.
Let us write the following code in button events
for the entire book
private void OrfoCheckAll_Click(object sender, RibbonControlEventArgs e)
        {
            var excel = Globals.ThisAddIn.Application;
            var wss = excel.Worksheets;
            var app = excel.Application;
            foreach (var ws in wss)
            {
                var sheet = ws as Excel.Worksheet;
                if (sheet != null)
                {
                    var range = sheet.UsedRange;
                    foreach (var cll in range)
                    {
                        var cell = cll as Excel.Range;
                        SpellCheck.SpellChecker(cell);
                    }
                }
            }
        }

For sheet

private void OrfoCheckCurrentSheet_Click(object sender, RibbonControlEventArgs e)
        {
            var excel = Globals.ThisAddIn.Application;
            var app = excel.Application;
            var sheet = app.ActiveSheet as Excel.Worksheet;
            if (sheet != null)
            {
                var range = sheet.UsedRange;
                foreach (var cll in range)
                {
                    var cell = cll as Excel.Range;
                    SpellCheck.SpellChecker(cell);
                }
            }
        }

Well and most importantly, we will create a static class SpellCheck in which we will describe our verification function
public static class SpellCheck
    {
        public static void SpellChecker(Excel.Range Target)
        {
            var app = Globals.ThisAddIn.Application.Application;
            string str = Target.Text.ToString();
            if (app.CheckSpelling(str, Type.Missing, true) == false)
            {
                foreach (string tmp in ((string)str).Split(' '))
                {
                    if (app.CheckSpelling(tmp, Type.Missing, Type.Missing) == false)
                    {
                        if (Target.Comment == null)
                        {
                            Target.AddComment("Ошибка в слове " + tmp);
                            Target.Characters[str.IndexOf(tmp) + 1, tmp.Length].Font.ColorIndex = 3;
                        }
                        else
                        {
                            Excel.Characters c = Target.Comment.Shape.TextFrame.Characters(Type.Missing, Type.Missing);
                            if (!c.Caption.Contains(tmp))
                            {
                                c.Caption = c.Caption + " " + tmp;
                            }
                            Target.Characters[str.IndexOf(tmp) + 1, tmp.Length].Font.ColorIndex = 3;
                        }
                    }
                    else
                    {
                        Target.Characters[str.IndexOf(tmp) + 1, tmp.Length].Font.ColorIndex = 0;
                    }
                }
            }
            else
            {
                if (Target.Comment != null)
                {
                    if (Target.Comment.Shape.AlternativeText.Contains("Ошибка в слове "))
                    {
                        Target.Characters[str.IndexOf(str), str.Length].Font.ColorIndex = 0;
                        Target.Comment.Delete();
                    }
                }
            }
        }

After compiling and downloading the plugin, we check the work.
image
This is all that Excel lacked for automatic verification.
As always, if you have more interesting implementation methods, share your experience

UPD: At the request of the habrozhitel, I posted the finished plugin here

Also popular now: