锐客网

  1. 首页 > 睿知 > it技术 > >

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

IT知识utilitywrapperInterop

【.NET-C#-Interop-Excel-Utility/Wrapper类】志不强者智不达，言不信者行不果。这篇文章主要讲述.NET-C#-Interop-Excel-Utility/Wrapper类相关的知识，希望能为你提供帮助。
Utility class that abstracts the creation of a Excel spreadsheet through Interop, and helps on adding and format its contents.

  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. }


    推荐阅读


    上一篇：Appindicator正面

    下一篇：Android和Iphone-现代css媒体查询