PageRenderTime 60ms CodeModel.GetById 29ms RepoModel.GetById 0ms app.codeStats 0ms

/MContracts/MCDomain/Importer/ExcelReader.cs

#
C# | 318 lines | 213 code | 65 blank | 40 comment | 48 complexity | 622c4d4f64d74c4680dc92086145e73a MD5 | raw file
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using Microsoft.Office.Interop.Excel;
  6. using System.Diagnostics.Contracts;
  7. using System.IO;
  8. using MCDomain.Common;
  9. using CommonBase;
  10. namespace MCDomain.Importer
  11. {
  12. public class ExcelReader: BaseReader
  13. {
  14. class SheetNotFoundException : Exception
  15. {
  16. public SheetNotFoundException(string message) : base(message) { }
  17. }
  18. class ExcelDataSourceNotSetException : Exception
  19. {
  20. public ExcelDataSourceNotSetException(string message) : base(message) { }
  21. }
  22. public ExcelReader()
  23. {
  24. Clear();
  25. AcceptingExtensions.Add(".xls");
  26. AcceptingExtensions.Add(".xlsx");
  27. }
  28. ~ExcelReader()
  29. {
  30. //Clear();
  31. }
  32. /// <summary>
  33. /// приложение Excel
  34. /// </summary>
  35. private Microsoft.Office.Interop.Excel.Application _app;
  36. /// <summary>
  37. /// книга, открытая в Excel
  38. /// </summary>
  39. private Microsoft.Office.Interop.Excel.Workbook _wb;
  40. public override void Clear()
  41. {
  42. _currentRange = null;
  43. _sh = null;
  44. if (_wb != null)
  45. {
  46. _wb.Close(false);
  47. _wb = null;
  48. }
  49. base.Clear();
  50. if (_app != null) _app.Quit();
  51. _app = null;
  52. }
  53. private Microsoft.Office.Interop.Excel.Workbook InitializeApp(ref Microsoft.Office.Interop.Excel.Application app)
  54. {
  55. Contract.Ensures(app != null);
  56. if (app != null)
  57. {
  58. app.Quit();
  59. app = null;
  60. }
  61. app = new Microsoft.Office.Interop.Excel.Application();
  62. app.Visible = false;
  63. app.DisplayAlerts = false;
  64. if (!File.Exists(FileName))
  65. {
  66. Clear();
  67. throw new FileNotFoundException("Файл не найден");
  68. }
  69. return app.Workbooks.Open(FileName, false, false);
  70. }
  71. /// <summary>
  72. /// открыть книгу
  73. /// </summary>
  74. public override void Open()
  75. {
  76. Contract.Ensures(_app != null);
  77. Contract.Ensures(_wb != null);
  78. ClearFileElements();
  79. _wb = InitializeApp(ref _app);
  80. }
  81. /// <summary>
  82. /// закрыть книгу и открытое приложение
  83. /// </summary>
  84. public override void Close() {
  85. if (_wb != null) ActiveWorkBook.Close();
  86. Clear();
  87. }
  88. /// <summary>
  89. /// открытая сейчас книга
  90. /// </summary>
  91. public Workbook ActiveWorkBook {
  92. get { return _wb; }
  93. }
  94. private Worksheet _sh;
  95. public Worksheet ActiveWorkSheet
  96. {
  97. get {
  98. Contract.Assert(ActiveWorkBook != null);
  99. if (_sh == null) _sh = ActiveWorkBook.ActiveSheet;
  100. return _sh;
  101. }
  102. }
  103. /// <summary>
  104. /// все листы книги Excel
  105. /// </summary>
  106. public Sheets Worksheets
  107. {
  108. get
  109. {
  110. if (ActiveWorkBook != null)
  111. return ActiveWorkBook.Worksheets;
  112. else
  113. return null;
  114. }
  115. }
  116. private List<FileElement> _fileelements;
  117. protected override IEnumerable<INamed> GetFileElements()
  118. {
  119. if (_fileelements == null)
  120. {
  121. _fileelements = new List<FileElement>();
  122. if (Worksheets != null)
  123. {
  124. foreach (Worksheet sh in Worksheets)
  125. {
  126. _fileelements.Add(new FileElement(sh.Name));
  127. }
  128. }
  129. }
  130. return _fileelements;
  131. }
  132. public override void ClearFileElements()
  133. {
  134. if (_fileelements != null)
  135. _fileelements = null;
  136. }
  137. /// <summary>
  138. /// позволяет автоматически определить конечную строку
  139. /// </summary>
  140. /// <param name="finishCol"></param>
  141. /// <param name="finishRow"></param>
  142. protected override void DefineFinishCell(ref int? finishCol, ref int? finishRow)
  143. {
  144. Contract.Ensures(finishCol >= StartColumn);
  145. Contract.Ensures(finishRow >= StartRow);
  146. if (_currentRange == null)
  147. ReadCurrentRegion();
  148. finishCol = CurrentRange.Column + CurrentRange.Columns.Count - 1;
  149. finishRow = CurrentRange.Row + CurrentRange.Rows.Count - 1;
  150. }
  151. private void ReadCurrentRegion()
  152. {
  153. Contract.Requires(StartColumn > 0);
  154. Contract.Requires(StartRow > 0);
  155. Contract.Ensures(_currentRange != null);
  156. Contract.Ensures(_currentRange.Column == StartColumn);
  157. Contract.Ensures(_currentRange.Row == StartRow);
  158. _currentRange = ActiveWorkSheet.Cells[StartRow, StartColumn].CurrentRegion;
  159. int iFinRow = 0; int iFinCol = 0;
  160. // дальше проверяем - нам не нужен текущий регион выше указанного нами левого верхнего угла
  161. if ((!FinishRow.HasValue) || (FinishRow.Value < StartRow.Value))
  162. iFinRow = _currentRange.Row + _currentRange.Rows.Count - 1;
  163. else
  164. iFinRow = FinishRow.HasValue?FinishRow.Value:_currentRange.Row + _currentRange.Rows.Count - 1;
  165. if ((!FinishColumn.HasValue) || (FinishColumn.Value < StartColumn.Value))
  166. iFinCol = _currentRange.Column + _currentRange.Columns.Count - 1;
  167. else
  168. iFinCol = FinishColumn.HasValue ? FinishColumn.Value : _currentRange.Column + _currentRange.Columns.Count - 1;
  169. if ((iFinRow >= StartRow) && (iFinCol >= StartColumn))
  170. {
  171. _currentRange = ActiveWorkSheet.Range[ActiveWorkSheet.Cells[StartRow, StartColumn],
  172. ActiveWorkSheet.Cells[iFinRow, iFinCol]];
  173. }
  174. }
  175. private Microsoft.Office.Interop.Excel.Range _currentRange;
  176. public Microsoft.Office.Interop.Excel.Range CurrentRange
  177. {
  178. get {
  179. if (_currentRange == null) ReadCurrentRegion();
  180. return _currentRange; }
  181. }
  182. protected override void InternalRead(int rowcount, int colcount)
  183. {
  184. RunInitSteps(2*rowcount);
  185. base.InternalRead(rowcount, colcount);
  186. // читаем эксель файл
  187. int i;
  188. int j;
  189. for (i = 1; i <= rowcount; i++)
  190. {
  191. Cells[i - 1].RowHeight = (int)CurrentRange.Rows[i].RowHeight;
  192. for (j = 1; j <= colcount; j++)
  193. {
  194. if (CurrentRange.Cells[i, j].Value != null)
  195. {
  196. Cells[i - 1][j - 1].Value = CurrentRange.Cells[i, j].Value.ToString();
  197. Cells[i - 1][j - 1].ColumnWidth = (int)CurrentRange.Cells[i, j].ColumnWidth;
  198. if (Cells[i - 1][j - 1].ColumnWidth <= 0)
  199. Cells[i - 1][j - 1].ColumnWidth = 10;
  200. }
  201. else
  202. {
  203. Cells[i - 1][j - 1].Value = "";
  204. Cells[i - 1][j - 1].ColumnWidth = (int)CurrentRange.Cells[i, j].ColumnWidth;
  205. if (Cells[i - 1][j - 1].ColumnWidth <= 0)
  206. Cells[i - 1][j - 1].ColumnWidth = 10;
  207. }
  208. }
  209. RunNextStep();
  210. }
  211. }
  212. /// <summary>
  213. /// читаем из файла - на выходе таблица Cells
  214. /// </summary>
  215. public override void Read ()
  216. {
  217. InternalRead(CurrentRange.Rows.Count, CurrentRange.Columns.Count);
  218. }
  219. /// <summary>
  220. /// задает параметры для конвертора
  221. /// </summary>
  222. /// <param name="parameters">объект с параметрами</param>
  223. public override void AcceptParameters(IReaderCommonParameters parameters)
  224. {
  225. Contract.Ensures(StartRow == parameters.StartRow);
  226. Contract.Ensures(StartColumn == parameters.StartColumn);
  227. if (ActiveWorkBook == null) throw new ExcelDataSourceNotSetException("Источник данных (книга Microsoft Excel) не задан");
  228. if (parameters.ActiveFileElementIndex > _wb.Sheets.Count) throw new SheetNotFoundException("Лист с указанным индексом " + parameters.ActiveFileElementIndex.ToString() + " не найден");
  229. GetFileElements();
  230. base.AcceptParameters(parameters);
  231. _sh = _wb.Sheets[parameters.ActiveFileElementIndex];
  232. ReadCurrentRegion();
  233. }
  234. /// <summary>
  235. /// сохраняет параметры конвертора в объект
  236. /// </summary>
  237. /// <param name="parameters">объект с параметрами</param>
  238. public override void SaveParameters(IReaderCommonParameters parameters)
  239. {
  240. base.SaveParameters(parameters);
  241. }
  242. /// <summary>
  243. /// показать приложение
  244. /// </summary>
  245. public override void ShowApp()
  246. {
  247. base.ShowApp();
  248. Microsoft.Office.Interop.Excel.Application __app = null;
  249. InitializeApp(ref __app);
  250. __app.Visible = true;
  251. }
  252. }
  253. }