Automatic spell checker in EXCEL
Good time of the day Habrozhiteli.
Just the other day, “experienced users” Excel addressed me and my colleagues. According to the specifics of the 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, there is no spelling error. In this regard, I developed Add-ins for Excel that would implement such functionality.
Check for errors is not so difficult, there is a standard SpellCheck, which I used. It is planned to implement 2 parts:
1) verification of already filled cells
2) check in real time as you type.
The very logic of the operation is simple. In the first case, we check all the cells, if there is an error, select the tokens and check each lexeme separately, for erroneous tokens, create a note and highlight the erroneous lexeme in red. In the second case, we simply subscribe to the cell change event and do everything as in 1 case only with a smaller range.
Let's start:
Launch VisualStudio, create an extension for Excel 2007.
When registering the plugin, subscribe to the Cell-Changed event and call our function (the function will be described a little later).
')
More during the registration will not do anything. Check the entire book will leave 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, because she is reserved.
Also, add 2 check buttons: check the entire book, and check the current sheet.
Let's write the following code in the button events
For the whole book
For sheet
And most importantly, we will create a static class SpellCheck in which we describe our verification function
After compiling and loading the plug-in, we check the work.
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 workers, I posted the ready Plugin here
Just the other day, “experienced users” Excel addressed me and my colleagues. According to the specifics of the 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, there is no spelling error. In this regard, I developed Add-ins for Excel that would implement such functionality.
Check for errors is not so difficult, there is a standard SpellCheck, which I used. It is planned to implement 2 parts:
1) verification of already filled cells
2) check in real time as you type.
The very logic of the operation is simple. In the first case, we check all the cells, if there is an error, select the tokens and check each lexeme separately, for erroneous tokens, create a note and highlight the erroneous lexeme in red. In the second case, we simply subscribe to the cell change event and do everything as in 1 case only with a smaller range.
Let's start:
Launch VisualStudio, create an extension for Excel 2007.
When registering the plugin, subscribe to the Cell-Changed event and call our function (the function will be described a little 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); } ')
More during the registration will not do anything. Check the entire book will leave 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, because she is reserved.
Also, add 2 check buttons: check the entire book, and check the current sheet.
Let's write the following code in the button events
For the whole 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); } } } And most importantly, we will create a static class SpellCheck in which we 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 loading the plug-in, we check the work.
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 workers, I posted the ready Plugin here
Source: https://habr.com/ru/post/136520/
All Articles