ThisAddIn.cs 8.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219
  1. /***
  2. * Project: Helpful Highlighter Excel Add-In
  3. * Component: Core Add-In class
  4. * Author: Todd Boyd
  5. * Date: 2010/10/26
  6. * Description:
  7. *
  8. * This Excel Add-In highlights the current row and column (but not the currently selected area) to assist
  9. * users with cognitive and/or visual impairments in maintaining focus through the use of visual cues. The
  10. * background color of the cells is actually changed on-the-fly in order to facilitate this functionality.
  11. *
  12. * The highlighting will be removed prior to printing or saving the worksheet in order to prevent the Add-In
  13. * interfering with the spreadsheets it is highlighting for the user.
  14. ***/
  15. using System;
  16. using System.Collections.Generic;
  17. using System.Linq;
  18. using System.Text;
  19. using System.Xml.Linq;
  20. using Excel = Microsoft.Office.Interop.Excel;
  21. using Office = Microsoft.Office.Core;
  22. using Microsoft.Office.Tools.Excel;
  23. using Microsoft.Office.Tools.Excel.Extensions;
  24. using Microsoft.Office.Interop.Excel;
  25. using System.Collections;
  26. namespace HelpfulHighlighter
  27. {
  28. public partial class ThisAddIn
  29. {
  30. private Range hlRange = null; // row and column ranges for highlighting
  31. private HelpfulHighlighterRibbon ribbon = null; // ribbon object (options)
  32. private List<object[]> old = null;
  33. private Microsoft.Office.Interop.Excel.Worksheet oldSheet = null;
  34. // startup process
  35. private void ThisAddIn_Startup(object sender, System.EventArgs e)
  36. {
  37. // intialize
  38. {
  39. // cache for color and colorindex properties
  40. this.old = new List<object[]>();
  41. }
  42. // bind events
  43. {
  44. // selection changed
  45. this.Application.SheetSelectionChange += new AppEvents_SheetSelectionChangeEventHandler(this.Sheet_Selection_Changed);
  46. // before save
  47. this.Application.WorkbookBeforeSave += new AppEvents_WorkbookBeforeSaveEventHandler(this.Workbook_Before_Save);
  48. // before print
  49. this.Application.WorkbookBeforePrint += new AppEvents_WorkbookBeforePrintEventHandler(this.Workbook_Before_Print);
  50. }
  51. }
  52. // shutdown process
  53. private void ThisAddIn_Shutdown(object sender, System.EventArgs e) { /* nothing to do */ }
  54. // remove highlighting
  55. public void CleanUp(params object[] p)
  56. {
  57. // pull parameter
  58. bool closeBuffer = true;
  59. if(p.Count() > 0)
  60. closeBuffer = (bool)p[0];
  61. // nothing to do?
  62. if(this.hlRange == null)
  63. return;
  64. // buffer on
  65. this.Application.ScreenUpdating = false;
  66. XlCalculation calc = this.Application.Calculation;
  67. this.Application.Calculation = XlCalculation.xlCalculationManual;
  68. // only restore colors if preserve is set and there are colors to restore in the first place
  69. if(this.old.Count > 0)
  70. {
  71. // cycle through "remembered" colors
  72. IEnumerator enu = this.old.GetEnumerator();
  73. while(enu.MoveNext())
  74. {
  75. object[] curr = (object[])enu.Current;
  76. Interior intr;
  77. // get old sheet's interior
  78. if (this.oldSheet != null)
  79. intr = ((Range)oldSheet.Cells[curr[0], curr[1]]).Interior;
  80. // get current sheet's interior
  81. else
  82. intr = ((Range)this.Application.Cells[curr[0], curr[1]]).Interior;
  83. // restore colors
  84. intr.Color = curr[2];
  85. intr.ColorIndex = curr[3];
  86. }
  87. this.old.Clear();
  88. }
  89. // set colors to "nothing" -- preserve is disabled
  90. else if(this.hlRange != null)
  91. this.hlRange.Interior.ColorIndex = XlColorIndex.xlColorIndexNone;
  92. // buffer off
  93. if(closeBuffer)
  94. this.Application.ScreenUpdating = true;
  95. this.Application.Calculation = calc;
  96. }
  97. // highlight selection's entire row/column (excluding selected area)
  98. private void HighLight()
  99. {
  100. // nothing to do?
  101. if(this.hlRange == null)
  102. return;
  103. // buffer on
  104. this.Application.ScreenUpdating = false;
  105. XlCalculation calc = this.Application.Calculation;
  106. this.Application.Calculation = XlCalculation.xlCalculationManual;
  107. Range sel = (Range)this.Application.Selection;
  108. // preserve colors?
  109. if(HelpfulHighlighter.Properties.Settings.Default.preserve)
  110. {
  111. // iterate through highlight range and save color/colorindex to cache
  112. IEnumerator enu = this.hlRange.GetEnumerator();
  113. while(enu.MoveNext())
  114. {
  115. Range curr = (Range)enu.Current;
  116. this.old.Add(new object[] { curr.Row, curr.Column, curr.Interior.Color, curr.Interior.ColorIndex });
  117. }
  118. }
  119. // highlight row and column ranges
  120. this.hlRange.Interior.Color = this.ribbon.GetColor();
  121. // buffer off
  122. this.Application.ScreenUpdating = true;
  123. this.Application.Calculation = calc;
  124. // remember last sheet highlighted
  125. this.oldSheet = (Microsoft.Office.Interop.Excel.Worksheet)this.Application.ActiveSheet;
  126. }
  127. // selection changed; re-highlight
  128. private void Sheet_Selection_Changed(object sh, Range target)
  129. {
  130. // do nothing if addin disabled
  131. if (! HelpfulHighlighter.Properties.Settings.Default.enabled)
  132. return;
  133. // clean up old highlighting, but keep buffer on
  134. if(this.hlRange != null)
  135. this.CleanUp(new object[] { false });
  136. Range sel = (Range)this.Application.Selection;
  137. // if we have an entire row, and entire column, or just a ton of cells selected, don't highlight
  138. if(sel.Rows.Cells.Count > this.Application.ActiveWindow.VisibleRange.Rows.Cells.Count
  139. || sel.Columns.Cells.Count > this.Application.ActiveWindow.VisibleRange.Columns.Cells.Count)
  140. {
  141. this.Application.ScreenUpdating = true;
  142. return;
  143. }
  144. // get cell ranges for areas to highlight
  145. Range colsAhead = this.Application.get_Range(this.Application.Cells[target.Row, sel.Column + sel.Columns.Count], this.Application.Cells[target.Row, target.Application.ActiveWindow.VisibleRange.Column + target.Application.ActiveWindow.VisibleRange.Columns.Count]);
  146. // substitute first cell to the right of selection if on column 1
  147. Range colsBehind = (
  148. sel.Column <= 1
  149. ? (Range)this.Application.Cells[sel.Row, sel.Column + sel.Columns.Count]
  150. : this.Application.get_Range(this.Application.Cells[target.Row, 1], this.Application.Cells[target.Row, sel.Column - 1]));
  151. Range rowsBelow = this.Application.get_Range(this.Application.Cells[sel.Row + sel.Rows.Count, target.Column], this.Application.Cells[target.Application.ActiveWindow.VisibleRange.Row + target.Application.ActiveWindow.VisibleRange.Rows.Count, target.Column]);
  152. // substitute first cell above selection if on row 1
  153. Range rowsAbove = (
  154. sel.Row <= 1
  155. ? (Range)this.Application.Cells[sel.Row + sel.Rows.Count, sel.Column]
  156. : this.Application.get_Range(this.Application.Cells[1, target.Column], this.Application.Cells[sel.Row - 1, target.Column]));
  157. this.hlRange = this.Application.Union(rowsAbove, rowsBelow, colsBehind, colsAhead,
  158. missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
  159. // highlight row/column
  160. this.HighLight();
  161. }
  162. // clear highlighting before save
  163. private void Workbook_Before_Save(Microsoft.Office.Interop.Excel.Workbook wb, bool SaveAsUI, ref bool Cancel)
  164. {
  165. this.CleanUp();
  166. }
  167. // clear highlighting before print
  168. private void Workbook_Before_Print(Microsoft.Office.Interop.Excel.Workbook wb, ref bool Cancel)
  169. {
  170. this.CleanUp();
  171. }
  172. // load ribbon
  173. protected override Microsoft.Office.Tools.Ribbon.OfficeRibbon[] CreateRibbonObjects()
  174. {
  175. if (this.ribbon == null)
  176. {
  177. this.ribbon = new HelpfulHighlighterRibbon();
  178. this.ribbon.addin = this;
  179. }
  180. return new Microsoft.Office.Tools.Ribbon.OfficeRibbon[] { this.ribbon };
  181. }
  182. #region VSTO generated code
  183. /// <summary>
  184. /// Required method for Designer support - do not modify
  185. /// the contents of this method with the code editor.
  186. /// </summary>
  187. private void InternalStartup()
  188. {
  189. this.Startup += new System.EventHandler(ThisAddIn_Startup);
  190. this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
  191. }
  192. #endregion
  193. }
  194. }