C#、VSTO讀取Excel類

C#、VSTO讀取Excel類

 

之前寫的類存在Excel進程不能結束的Bug，重寫ExcelReader類，類實例清理時Excel進程自動結束。

  1     class ExcelReader
  2     {
  3         // Excel Object
  4         public Application app;
  5         public Workbooks wbs;
  6         public Workbook wb;
  7         public Worksheet ws;
  8         public Range rng;
  9 
 10         private bool disposed = false;
 11 
 12         public ExcelReader()
 13         {
 14             // New Excel Application
 15             app = new Application
 16             {
 17                 Visible = false,
 18                 DisplayAlerts = false
 19             };
 20         }
 21 
 22         /// <summary>
 23         /// 關閉對象
 24         /// </summary>
 25         public void Close()
 26         {
 27             Dispose();
 28         }
 29 
 30         /// <summary>
 31         /// 清理對象
 32         /// </summary>
 33         public void Dispose()
 34         {
 35             Dispose(true);
 36             GC.SuppressFinalize(this);
 37         }
 38 
 39         /// <summary> 
 40         /// 清理所有正在使用的资源。
 41         /// </summary>
 42         /// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param>
 43         protected virtual void Dispose(bool disposing)
 44         {
 45             if (disposed)
 46             {
 47                 return;
 48             }
 49             if (disposing)
 50             {
 51                 // Excel Application Quit
 52                 app.Quit();
 53 
 54                 // Finally, Release app.
 55                 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(app);
 56                 app = null;
 57 
 58                 disposed = true;
 59             }
 60         }
 61 
 62         #region Worksheet Operation
 63         /// <summary>
 64         /// 從工作表讀取數據到 DataTable
 65         /// </summary>
 66         /// <param name="fileName">文件</param>
 67         /// <param name="sheet">工作表名</param>
 68         /// <returns>DataTable</returns>
 69         public DataTable GetDataTableFromSheet(string fileName, string sheet, int rows, int cols)
 70         {
 71             // Create Table
 72             DataTable dt = new DataTable();
 73 
 74             // Get Excel's WorkBooks. Attention: Don't use .Net cascade, ex.: app.Workbooks.Add()
 75             // Every variable Must set to reference, then Release it one by one.
 76             // If not, Can't quit Excel Process.
 77             wbs = app.Workbooks;
 78 
 79             // Get WorkBook
 80             wb = wbs.Open(fileName);
 81 
 82             // Get WorkSheet
 83             ws = wb.Sheets[sheet];
 84 
 85             // Columns & Rows Count
 86             //int colCount = ws.UsedRange.CurrentRegion.Columns.Count;
 87             //int rowCount = ws.UsedRange.CurrentRegion.Rows.Count;
 88 
 89             // Get worksheet's used range
 90             //rng = ws.UsedRange;
 91             rng = ws.Range[ws.Range["A1"], ws.Range[GetColumnName(cols)+rows]];
 92             dt = GetDataTableFromRange(rng, rows, cols);
 93 
 94             // Relase Range, Set to null. (variable reference that COM Object's Count is 0)
 95             System.Runtime.InteropServices.Marshal.FinalReleaseComObject(rng);
 96             rng = null;
 97 
 98             // Release WorkSheet
 99             System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ws);
100             ws = null;
101 
102             // Release WorkBook
103             System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wb);
104             wb = null;
105 
106             // Release WorkBooks
107             System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wbs);
108             wbs = null;
109 
110             return dt;
111         }
112         #endregion
113 
114         #region Range Operation
115         /// <summary>
116         /// 從單元格範圍讀取數據到 DataTable
117         /// </summary>
118         /// <param name="range">單元格範圍</param>
119         /// <returns>DataTable</returns>
120         private DataTable GetDataTableFromRange(Range range, int rows, int cols)
121         {
122             DataTable dataTable = new DataTable();
123 
124             // First Row Range
125             Range titleRange = range.Rows[1];
126 
127             // Columns Count
128             //int colCount = titleRange.CurrentRegion.Columns.Count;
129 
130             // Title Row has Empty Cell Or Replication, Use Excel Column Header.
131             if (titleRange.Cells.Cast<Range>().Any(s => s.Value2 == null)
132                 || titleRange.Cells.Cast<Range>().GroupBy(s => s.Value2).Count() != titleRange.Cells.Count
133                 || titleRange.CurrentRegion.Columns.Count != cols)
134             {
135                 for (int i = 1; i <= cols; i++)
136                 {
137                     dataTable.Columns.Add(GetColumnName(i), typeof(string));
138                 }
139             }
140             else
141                 dataTable = GetTableStructureFromTitleRange(range);
142 
143             // Release Range Object
144             System.Runtime.InteropServices.Marshal.FinalReleaseComObject(titleRange);
145             titleRange = null;
146 
147             // Insert Data To DataTable Wtih Range Value
148             object[,] arr = range.Value;
149             for (int i = 0; i < arr.GetLength(0); i++)
150             {
151                 DataRow dr = dataTable.NewRow();
152                 for (int j = 0; j < arr.GetLength(1); j++)
153                 {
154                     if (arr[i + 1, j + 1] != null)
155                         dr[j] = arr[i + 1, j + 1];
156                     else
157                         dr[j] = "";
158                 }
159                     
160                 dataTable.Rows.Add(dr);
161             }
162 
163             return dataTable;
164         }
165 
166         /// <summary>
167         /// 生成表頭。如果首行表頭規範，則採用首行表頭，否則用Excel表頭代替
168         /// </summary>
169         /// <param name="titleRow">首行範圍</param>
170         /// <param name="isAllString">全部採用字符串格式</param>
171         /// <returns>DataTable</returns>
172         private DataTable GetTableStructureFromTitleRange(Range titleRow, bool isAllString = true)
173         {
174             DataTable dataTable = new System.Data.DataTable();
175             foreach (Range cell in titleRow.Cells)
176             {
177                 if (isAllString)
178                     dataTable.Columns.Add(cell.Value2, typeof(string));
179                 else
180                     dataTable.Columns.Add(cell.Value2, typeof(object));
181             }
182             return dataTable;
183         }
184         #endregion
185 
186         #region Column Operation
187         /// <summary>
188         /// 使用 Excel 標頭的方式生成字母列頭
189         /// </summary>
190         /// <param name="index">索引號</param>
191         /// <returns>字母列頭</returns>
192         public string GetColumnName(int index)
193         {
194             var dividend = index;
195             var columnName = string.Empty;
196 
197             while (dividend > 0)
198             {
199                 var modulo = (dividend - 1) % 26;
200                 columnName = Convert.ToChar(65 + modulo) + columnName;
201                 dividend = (dividend - modulo) / 26;
202             }
203 
204             return columnName;
205         }
206         #endregion
207     }