.NET - C# - Interop - Excel - Utility/Wrapper Class


/ Published in: C#
Save to your folder(s)

Utility class that abstracts the creation of a Excel spreadsheet through Interop, and helps on adding and format its contents.


Copy this code and paste it in your HTML
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5.  
  6. using System.Drawing;
  7. using System.Globalization;
  8. using Microsoft.Office.Interop.Excel;
  9.  
  10. namespace ExcelWrapper
  11. {
  12. public class Spreadsheet
  13. {
  14. private Application excelApplication;
  15. private Workbook workbook;
  16. private Worksheet worksheet;
  17. private Window mainWindow;
  18.  
  19. private Dictionary<string, XlPattern> namePatternMap;
  20. private Dictionary<string, XlLineStyle> nameLineStyleMap;
  21. private Dictionary<string, XlBorderWeight> nameBorderWeightMap;
  22. private Dictionary<string, XlHAlign> nameHAlignMap;
  23. private Dictionary<string, XlVAlign> nameVAlignMap;
  24.  
  25. public Spreadsheet(bool displayGridLines)
  26. {
  27. Initialize();
  28.  
  29. excelApplication = new Application();
  30.  
  31. // passing xlWBATWorksheet from XlWBATemplate enumeration as parameter
  32. // based on Workbooks.Add Method (http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.add%28v=office.11%29.aspx)
  33. workbook = excelApplication.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
  34.  
  35. mainWindow = workbook.Windows[1];
  36. mainWindow.DisplayGridlines = displayGridLines;
  37.  
  38. // index starts on 1
  39. worksheet = (Worksheet)workbook.Worksheets[1];
  40. }
  41.  
  42. private void Initialize()
  43. {
  44. namePatternMap = new Dictionary<string, XlPattern>();
  45. namePatternMap.Add("25% Gray", XlPattern.xlPatternGray25);
  46.  
  47. nameLineStyleMap = new Dictionary<string, XlLineStyle>();
  48. nameLineStyleMap.Add("Continuous", XlLineStyle.xlContinuous);
  49. nameLineStyleMap.Add("Dot", XlLineStyle.xlDot);
  50.  
  51. nameBorderWeightMap = new Dictionary<string, XlBorderWeight>();
  52. nameBorderWeightMap.Add("Thin", XlBorderWeight.xlThin);
  53. nameBorderWeightMap.Add("HairLine", XlBorderWeight.xlHairline);
  54.  
  55. nameHAlignMap = new Dictionary<string, XlHAlign>();
  56. nameHAlignMap.Add("Center", XlHAlign.xlHAlignCenter);
  57.  
  58. nameVAlignMap = new Dictionary<string, XlVAlign>();
  59. nameVAlignMap.Add("Center", XlVAlign.xlVAlignCenter);
  60. }
  61.  
  62. public void Show()
  63. {
  64. excelApplication.Visible = true;
  65. }
  66.  
  67. public void Save(string path, string entity, DateTime date)
  68. {
  69. workbook.Saved = true;
  70. workbook.SaveCopyAs(path);
  71. }
  72.  
  73. public void Close()
  74. {
  75. workbook.Close(true, Type.Missing, Type.Missing);
  76. workbook = null;
  77. excelApplication.Quit();
  78. excelApplication = null;
  79. }
  80.  
  81. public void SetZoom(int zoomPercentage)
  82. {
  83. mainWindow.Zoom = zoomPercentage;
  84. }
  85.  
  86. public void FreezePanes(int splitRow, int scrollRow, int splitColumn, int scrollColumn)
  87. {
  88. mainWindow.SplitRow = splitRow;
  89. mainWindow.ScrollRow = scrollRow;
  90. mainWindow.SplitColumn = splitColumn;
  91. mainWindow.ScrollColumn = scrollColumn;
  92. mainWindow.FreezePanes = true;
  93. }
  94.  
  95. public void SetFont(string startCell, string endCell, string family, int size)
  96. {
  97. Range range = worksheet.get_Range(startCell, endCell);
  98. range.Font.Name = family;
  99. range.Font.Size = size;
  100. }
  101.  
  102. public void SetFontStyle(string startCell, string endCell, bool bold, bool underline, string color)
  103. {
  104. Range range = worksheet.get_Range(startCell, endCell);
  105. range.Font.Bold = bold;
  106. range.Font.Underline = underline;
  107. range.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName(color));
  108. }
  109.  
  110. public void SetBackgroundColor(string startCell, string endCell, string color)
  111. {
  112. Range range = worksheet.get_Range(startCell, endCell);
  113. range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName(color));
  114. }
  115.  
  116. public void SetNumberFormat(string startCell, string endCell, string numberFormat)
  117. {
  118. Range range = worksheet.get_Range(startCell, endCell);
  119. range.NumberFormat = numberFormat;
  120. }
  121.  
  122. public void SetPattern(string startCell, string endCell, string pattern)
  123. {
  124. Range range = worksheet.get_Range(startCell, endCell);
  125.  
  126. try
  127. {
  128. XlPattern xlPattern = namePatternMap[pattern];
  129. range.Interior.Pattern = xlPattern;
  130. }
  131. catch
  132. {
  133. range.Interior.Pattern = XlPattern.xlPatternSolid;
  134. }
  135. }
  136.  
  137. public void InsertPicture(string path, float left, float top, float width, float height)
  138. {
  139. worksheet.Shapes.AddPicture(path, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue,
  140. left, top, width, height);
  141. }
  142.  
  143. public void SetText(string cell, string text)
  144. {
  145. Range range = worksheet.get_Range(cell, cell);
  146. range.Cells[1,1] = text;
  147. }
  148.  
  149. public void SetFormula(string cell, string formula)
  150. {
  151. Range range = worksheet.get_Range(cell, cell);
  152. range.Formula = formula;
  153. }
  154.  
  155. public void SetBorderAround(string startCell, string endCell, string lineStyle, string borderWeight, string borderColorName)
  156. {
  157. Range range = worksheet.get_Range(startCell, endCell);
  158.  
  159. try
  160. {
  161. object borderColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName(borderColorName));
  162. range.BorderAround(nameLineStyleMap[lineStyle], nameBorderWeightMap[borderWeight], XlColorIndex.xlColorIndexAutomatic, borderColor);
  163. }
  164. catch
  165. {
  166. object borderColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName("Black"));
  167. range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, borderColor);
  168. }
  169. }
  170.  
  171. public void SetBorderInternal(string startCell, string endCell, string lineStyle, string borderWeight, string borderColorName)
  172. {
  173. Range range = worksheet.get_Range(startCell, endCell);
  174.  
  175. try
  176. {
  177. object borderColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName(borderColorName));
  178. range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = nameLineStyleMap[lineStyle];
  179. range.Borders[XlBordersIndex.xlInsideHorizontal].Weight = nameBorderWeightMap[borderWeight];
  180. range.Borders[XlBordersIndex.xlInsideHorizontal].Color = borderColor;
  181. range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = nameLineStyleMap[lineStyle];
  182. range.Borders[XlBordersIndex.xlInsideVertical].Weight = nameBorderWeightMap[borderWeight];
  183. range.Borders[XlBordersIndex.xlInsideVertical].Color = borderColor;
  184. }
  185. catch
  186. {
  187. object borderColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName("Black"));
  188. range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, borderColor);
  189. range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDot;
  190. range.Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlHairline;
  191. range.Borders[XlBordersIndex.xlInsideHorizontal].Color = borderColor;
  192. range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlDot;
  193. range.Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlHairline;
  194. range.Borders[XlBordersIndex.xlInsideVertical].Color = borderColor;
  195. }
  196. }
  197.  
  198. public void SetHorizontalAlignment(string startCell, string endCell, string hAlign)
  199. {
  200. Range range = worksheet.get_Range(startCell, endCell);
  201.  
  202. try
  203. {
  204. range.HorizontalAlignment = nameHAlignMap[hAlign];
  205. }
  206. catch
  207. {
  208. range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
  209. }
  210. }
  211.  
  212. public void SetVerticalAlignment(string startCell, string endCell, string vAlign)
  213. {
  214. Range range = worksheet.get_Range(startCell, endCell);
  215.  
  216. try
  217. {
  218. range.VerticalAlignment = nameVAlignMap[vAlign];
  219. }
  220. catch
  221. {
  222. range.VerticalAlignment = XlVAlign.xlVAlignCenter;
  223. }
  224. }
  225.  
  226. public void AutoFitRow(string cell)
  227. {
  228. Range range = worksheet.get_Range(cell, cell);
  229. range.EntireRow.AutoFit();
  230. }
  231.  
  232. public void AutoFitColumn(string cell)
  233. {
  234. Range range = worksheet.get_Range(cell, cell);
  235. range.EntireColumn.AutoFit();
  236. }
  237.  
  238. public void SetRowHeight(string cell, float height)
  239. {
  240. Range range = worksheet.get_Range(cell, cell);
  241. range.RowHeight = height;
  242. }
  243.  
  244. public void SetColumnWidth(string cell, float width)
  245. {
  246. Range range = worksheet.get_Range(cell, cell);
  247. range.ColumnWidth = width;
  248. }
  249. }
  250. }

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.