?1using?System;
??2
??3namespace?ExcelHandle
??4{
??5????/**////?<summary>
??6????///?ExcelHandle?的摘要說明。
??7????///?</summary>
??8????public?class?ExcelHandle
??9????{
?10
?11????????/**////?<summary>
?12????????///?Excel
?13????????///?</summary>
?14????????public?Excel.Application?CurExcel?=?null;
?15
?16????????/**////?<summary>
?17????????///?工作簿
?18????????///?</summary>
?19????????public?Excel._Workbook?CurBook?=?null;
?20
?21????????/**////?<summary>
?22????????///?工作表
?23????????///?</summary>
?24????????public?Excel._Worksheet?CurSheet?=?null;
?25
?26????????private?object?mValue?=?System.Reflection.Missing.Value;
?27
?28????????/**////?<summary>
?29????????///?構造函數
?30????????///?</summary>
?31????????public?ExcelHandle()
?32????????{
?33????????????//
?34????????????//?TODO:?在此處添加構造函數邏輯
?35????????????//????
?36
?37????????????this.dtBefore?=?System.DateTime.Now;
?38????????????
?39????????????CurExcel?=?new?Excel.Application();
?40
?41????????????this.dtAfter?=?System.DateTime.Now;
?42
?43????????????this.timestamp?=?System.DateTime.Now.ToShortDateString().Replace("-",?"")?+?System.DateTime.Now.ToShortTimeString().Replace(":",?"")?+?System.DateTime.Now.Second.ToString()?+?System.DateTime.Now.Millisecond.ToString();
?44
?45????????}
?46
?47????????/**////?<summary>
?48????????///?構造函數
?49????????///?</summary>
?50????????///?<param?name="strFilePath">加載的Excel文件名</param>
?51????????public?ExcelHandle(string?strFilePath)
?52????????{
?53
?54????????????this.dtBefore?=?System.DateTime.Now;
?55
?56????????????CurExcel?=?new?Excel.Application();
?57
?58????????????this.dtAfter?=?System.DateTime.Now;
?59
?60????????????CurBook?=?(Excel._Workbook)CurExcel.Workbooks.Open(strFilePath,?mValue,?false,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue);
?61
?62????????????this.timestamp?=?System.DateTime.Now.ToShortDateString().Replace("-",?"")?+?System.DateTime.Now.ToShortTimeString().Replace(":",?"")?+?System.DateTime.Now.Second.ToString()?+?System.DateTime.Now.Millisecond.ToString();
?63
?64????????}
?65
?66????????/**////?<summary>
?67????????///?釋放內存空間
?68????????///?</summary>
?69????????public?void?Dispose()
?70????????{
?71????????????try
?72????????????{
?73????????????????System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
?74????????????????CurSheet?=?null;
?75
?76????????????????CurBook.Close(false,?mValue,?mValue);
?77????????????????System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
?78????????????????CurBook?=?null;
?79
?80????????????????CurExcel.Quit();
?81????????????????System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
?82????????????????CurExcel?=?null;
?83????????????
?84????????????????GC.Collect();
?85????????????????GC.WaitForPendingFinalizers();
?86????????????
?87????????????}
?88????????????catch(System.Exception?ex)
?89????????????{
?90????????????????this.MessageWarning("在釋放Excel內存空間時發生了一個錯誤：",?ex);
?91????????????}
?92????????????finally
?93????????????{
?94????????????????foreach(System.Diagnostics.Process?pro?in?System.Diagnostics.Process.GetProcessesByName("Excel"))????????????????????
?95????????????????????if?(pro.StartTime?>?this.dtBefore?&&?pro.StartTime?<?this.dtAfter)
?96????????????????????????pro.Kill();
?97????????????}
?98????????????System.GC.SuppressFinalize(this);
?99????????}
100
101
102????????private?string?filepath;
103????????private?string?timestamp;
104????????private?System.DateTime?dtBefore;
105????????private?System.DateTime?dtAfter;
106
107
108????????/**////?<summary>
109????????///?Excel文件名
110????????///?</summary>
111????????public?string?FilePath
112????????{
113????????????get
114????????????{
115????????????????return?this.filepath;
116????????????}
117????????????set
118????????????{
119????????????????this.filepath?=?value;
120????????????}
121????????}
122
123????????/**////?<summary>
124????????///?是否打開Excel界面
125????????///?</summary>
126????????public?bool?Visible
127????????{
128????????????set
129????????????{
130????????????????CurExcel.Visible?=?value;
131????????????}
132????????}
133
134????????/**////?<summary>
135????????///?以時間字符串作為保存文件的名稱
136????????///?</summary>
137????????public?string?TimeStamp
138????????{
139????????????get
140????????????{
141????????????????return?this.timestamp;
142????????????}
143????????????set
144????????????{
145????????????????this.timestamp?=?value;
146????????????}
147????????}
148
149
150????????/**////?<summary>
151????????///?加載Excel文件
152????????///?</summary>
153????????public?void?Load()
154????????{
155????????????if?(CurBook?==?null?&&?this.filepath?!=?null)
156????????????????CurBook?=?(Excel._Workbook)CurExcel.Workbooks.Open(this.filepath,?mValue,?false,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue);
157????????}
158
159????????/**////?<summary>
160????????///?加載Excel文件
161????????///?</summary>
162????????///?<param?name="strFilePath">Excel文件名</param>
163????????public?void?Load(string?strFilePath)
164????????{
165????????????if?(CurBook?==?null)
166????????????????CurBook?=?(Excel._Workbook)CurExcel.Workbooks.Open(strFilePath,?mValue,?false,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue,?mValue);
167????????}
168
169????????/**////?<summary>
170????????///?新建工作表
171????????///?</summary>
172????????///?<param?name="strWorkSheetName">工作表名稱</param>
173????????public?void?NewWorkSheet(string?strWorkSheetName)
174????????{
175????????????CurSheet?=?(Excel._Worksheet)CurBook.Sheets.Add(CurBook.Sheets[1],?mValue,?mValue,?mValue);
176????????????CurSheet.Name?=?strWorkSheetName;
177????????}
178
179????????/**////?<summary>
180????????///?在指定單元格插入指定的值
181????????///?</summary>
182????????///?<param?name="strCell">單元格，如“A4”</param>
183????????///?<param?name="objValue">文本、數字等值</param>
184????????public?void?WriteCell(string?strCell,?object?objValue)
185????????{
186????????????CurSheet.get_Range(strCell,?mValue).Value2?=?objValue;
187????????}
188
189????????/**////?<summary>
190????????///?在指定Range中插入指定的值
191????????///?</summary>
192????????///?<param?name="strStartCell">Range的開始單元格</param>
193????????///?<param?name="strEndCell">Range的結束單元格</param>
194????????///?<param?name="objValue">文本、數字等值</param>
195????????public?void?WriteRange(string?strStartCell,?string?strEndCell,?object?objValue)
196????????{
197????????????CurSheet.get_Range(strStartCell,?strEndCell).Value2?=?objValue;
198????????}
199
200
201????????/**////?<summary>
202????????///?合并單元格，并在合并后的單元格中插入指定的值
203????????///?</summary>
204????????///?<param?name="strStartCell"></param>
205????????///?<param?name="strEndCell"></param>
206????????///?<param?name="objValue"></param>
207????????public?void?WriteAfterMerge(string?strStartCell,?string?strEndCell,?object?objValue)
208????????{
209????????????CurSheet.get_Range(strStartCell,?strEndCell).Merge(mValue);
210????????????CurSheet.get_Range(strStartCell,?mValue).Value2?=?objValue;
211????????}
212
213????????/**////?<summary>
214????????///?在連續單元格中插入一個DataTable中的值
215????????///?</summary>
216????????///?<param?name="strStartCell">開始的單元格</param>
217????????///?<param?name="dtData">存儲數據的DataTable</param>
218????????public?void?WriteTable(string?strStartCell,?System.Data.DataTable?dtData)
219????????{
220????????????object[,]?arrData?=?new?object[dtData.Rows.Count,?dtData.Columns.Count];
221
222????????????for?(int?i?=?0;?i?<?dtData.Rows.Count;?i?++)
223????????????????for?(int?j?=?0;?j?<?dtData.Columns.Count;?j?++)
224????????????????????arrData[i,?j]?=?dtData.Rows[i][j];
225
226????????????CurSheet.get_Range(strStartCell,?this.GetEndCell(strStartCell,?dtData.Rows.Count?-?1,?dtData.Columns.Count?-?1)).Value2?=?arrData;
227
228????????????arrData?=?null;
229????????}
230
231????????/**////?<summary>
232????????///?在連續單元格中插入一個DataTable并作超級鏈接
233????????///?</summary>
234????????///?<param?name="strStartCell">起始單元格標識符</param>
235????????///?<param?name="dtData">存儲數據的DataTable</param>
236????????///?<param?name="strLinkField">鏈接的地址字段</param>
237????????///?<param?name="strTextField">鏈接的文本字段</param>
238????????public?void?WriteTableAndLink(string?strStartCell,?System.Data.DataTable?dtData,?string?strLinkField,?string?strTextField)
239????????{
240????????????object[,]?arrData?=?new?object[dtData.Rows.Count,?dtData.Columns.Count?-?1];
241
242????????????for?(int?i?=?0;?i?<?dtData.Rows.Count;?i?++)
243????????????{
244????????????????for?(int?j?=?0;?j?<?dtData.Columns.Count;?j?++)
245????????????????{
246????????????????????if?(j?>?dtData.Columns.IndexOf(strLinkField))
247????????????????????????arrData[i,?j?-?1]?=?dtData.Rows[i][j];
248????????????????????else?if?(j?<?dtData.Columns.IndexOf(strLinkField))
249????????????????????????arrData[i,?j]?=?dtData.Rows[i][j];
250????????????????}
251????????????}
252
253????????????CurSheet.get_Range(strStartCell,?this.GetEndCell(strStartCell,?dtData.Rows.Count?-?1,?dtData.Columns.Count?-?2)).Value2?=?arrData;
254
255????????????for?(int?i?=?0;?i?<?dtData.Rows.Count;?i?++)
256????????????????this.AddHyperLink(this.NtoL(this.LtoN(this.GetCellLetter(strStartCell))?+?dtData.Columns.IndexOf(strTextField))?+?System.Convert.ToString(this.GetCellNumber(strStartCell)?+?i),?dtData.Rows[i][strLinkField].ToString()?+?".htm",?"點擊查看詳細",?dtData.Rows[i][strTextField].ToString());
257
258????????????arrData?=?null;
259????????}
260
261????????/**////?<summary>
262????????///?為單元格設置公式
263????????///?</summary>
264????????///?<param?name="strCell">單元格標識符</param>
265????????///?<param?name="strFormula">公式</param>
266????????public?void?SetFormula(string?strCell,?string?strFormula)
267????????{
268????????????CurSheet.get_Range(strCell,?mValue).Formula?=?strFormula;
269????????}
270
271????????/**////?<summary>
272????????///?設置單元格或連續區域的字體為黑體
273????????///?</summary>
274????????///?<param?name="strCell">單元格標識符</param>
275????????public?void?SetBold(string?strCell)
276????????{
277????????????CurSheet.get_Range(strCell,?mValue).Font.Bold?=?true;
278????????}
279
280????????/**////?<summary>
281????????///?設置連續區域的字體為黑體
282????????///?</summary>
283????????///?<param?name="strStartCell">開始單元格標識符</param>
284????????///?<param?name="strEndCell">結束單元格標識符</param>
285????????public?void?SetBold(string?strStartCell,?string?strEndCell)
286????????{
287????????????CurSheet.get_Range(strStartCell,?strEndCell).Font.Bold?=?true;
288????????}
289
290????????/**////?<summary>
291????????///?設置單元格或連續區域的字體顏色
292????????///?</summary>
293????????///?<param?name="strCell">單元格標識符</param>
294????????///?<param?name="clrColor">顏色</param>
295????????public?void?SetColor(string?strCell,?System.Drawing.Color?clrColor)
296????????{
297????????????CurSheet.get_Range(strCell,?mValue).Font.Color?=?System.Drawing.ColorTranslator.ToOle(clrColor);
298????????}
299
300????????/**////?<summary>
301????????///?設置連續區域的字體顏色
302????????///?</summary>
303????????///?<param?name="strStartCell">開始單元格標識符</param>
304????????///?<param?name="strEndCell">結束單元格標識符</param>
305????????///?<param?name="clrColor">顏色</param>
306????????public?void?SetColor(string?strStartCell,?string?strEndCell,?System.Drawing.Color?clrColor)
307????????{
308????????????CurSheet.get_Range(strStartCell,?strEndCell).Font.Color?=?System.Drawing.ColorTranslator.ToOle(clrColor);
309????????}
310
311????????/**////?<summary>
312????????///?設置單元格或連續區域的邊框：上下左右都為黑色連續邊框
313????????///?</summary>
314????????///?<param?name="strCell">單元格標識符</param>
315????????public?void?SetBorderAll(string?strCell)
316????????{
317????????????CurSheet.get_Range(strCell,?mValue).Borders[Excel.XlBordersIndex.xlEdgeTop].Color?=?System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
318????????????CurSheet.get_Range(strCell,?mValue).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle?=?Excel.XlLineStyle.xlContinuous;
319
320????????????CurSheet.get_Range(strCell,?mValue).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color?=?System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
321????????????CurSheet.get_Range(strCell,?mValue).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle?=?Excel.XlLineStyle.xlContinuous;
322
323????????????CurSheet.get_Range(strCell,?mValue).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color?=?System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
324????????????CurSheet.get_Range(strCell,?mValue).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle?=?Excel.XlLineStyle.xlContinuous;
325
326????????????CurSheet.get_Range(strCell,?mValue).Borders[Excel.XlBordersIndex.xlEdgeRight].Color?=?System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
327????????????CurSheet.get_Range(strCell,?mValue).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle?=?Excel.XlLineStyle.xlContinuous;
328
329????????????CurSheet.get_Range(strCell,?mValue).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color?=?System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
330????????????CurSheet.get_Range(strCell,?mValue).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle?=?Excel.XlLineStyle.xlContinuous;
331
332????????????CurSheet.get_Range(strCell,?mValue).Borders[Excel.XlBordersIndex.xlInsideVertical].Color?=?System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
333????????????CurSheet.get_Range(strCell,?mValue).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle?=?Excel.XlLineStyle.xlContinuous;
334
335
336????????}
337
338????????/**////?<summary>
339????????///?設置連續區域的邊框：上下左右都為黑色連續邊框
340????????///?</summary>
341????????///?<param?name="strStartCell">開始單元格標識符</param>
342????????///?<param?name="strEndCell">結束單元格標識符</param>
343????????public?void?SetBorderAll(string?strStartCell,?string?strEndCell)
344????????{
345????????????CurSheet.get_Range(strStartCell,?strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color?=?System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
346????????????CurSheet.get_Range(strStartCell,?strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle?=?Excel.XlLineStyle.xlContinuous;
347
348????????????CurSheet.get_Range(strStartCell,?strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color?=?System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
349????????????CurSheet.get_Range(strStartCell,?strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle?=?Excel.XlLineStyle.xlContinuous;
350
351????????????CurSheet.get_Range(strStartCell,?strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color?=?System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
352????????????CurSheet.get_Range(strStartCell,?strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle?=?Excel.XlLineStyle.xlContinuous;
353
354????????????CurSheet.get_Range(strStartCell,?strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color?=?System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
355????????????CurSheet.get_Range(strStartCell,?strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle?=?Excel.XlLineStyle.xlContinuous;
356
357????????????CurSheet.get_Range(strStartCell,?strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color?=?System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
358????????????CurSheet.get_Range(strStartCell,?strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle?=?Excel.XlLineStyle.xlContinuous;
359
360????????????CurSheet.get_Range(strStartCell,?strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color?=?System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
361????????????CurSheet.get_Range(strStartCell,?strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle?=?Excel.XlLineStyle.xlContinuous;
362????????}
363
364????????/**////?<summary>
365????????///?設置單元格或連續區域水平居左
366????????///?</summary>
367????????///?<param?name="strCell">單元格標識符</param>
368????????public?void?SetHAlignLeft(string?strCell)
369????????{
370????????????CurSheet.get_Range(strCell,?mValue).HorizontalAlignment?=?Excel.XlHAlign.xlHAlignLeft;
371????????}
372
373????????/**////?<summary>
374????????///?設置連續區域水平居左
375????????///?</summary>
376????????///?<param?name="strStartCell">開始單元格標識符</param>
377????????///?<param?name="strEndCell">結束單元格標識符</param>
378????????public?void?SetHAlignLeft(string?strStartCell,?string?strEndCell)
379????????{
380????????????CurSheet.get_Range(strStartCell,?strEndCell).HorizontalAlignment?=?Excel.XlHAlign.xlHAlignLeft;
381????????}
382
383????????/**////?<summary>
384????????///?設置單元格或連續區域水平居左
385????????///?</summary>
386????????///?<param?name="strCell">單元格標識符</param>
387????????public?void?SetHAlignCenter(string?strCell)
388????????{
389????????????CurSheet.get_Range(strCell,?mValue).HorizontalAlignment?=?Excel.XlHAlign.xlHAlignCenter;
390????????}
391
392????????/**////?<summary>
393????????///?設置連續區域水平居中
394????????///?</summary>
395????????///?<param?name="strStartCell">開始單元格標識符</param>
396????????///?<param?name="strEndCell">結束單元格標識符</param>
397????????public?void?SetHAlignCenter(string?strStartCell,?string?strEndCell)
398????????{
399????????????CurSheet.get_Range(strStartCell,?strEndCell).HorizontalAlignment?=?Excel.XlHAlign.xlHAlignCenter;
400????????}
401
402????????/**////?<summary>
403????????///?設置單元格或連續區域水平居右
404????????///?</summary>
405????????///?<param?name="strCell">單元格標識符</param>
406????????public?void?SetHAlignRight(string?strCell)
407????????{
408????????????CurSheet.get_Range(strCell,?mValue).HorizontalAlignment?=?Excel.XlHAlign.xlHAlignRight;
409????????}
410
411????????/**////?<summary>
412????????///?設置連續區域水平居右
413????????///?</summary>
414????????///?<param?name="strStartCell">開始單元格標識符</param>
415????????///?<param?name="strEndCell">結束單元格標識符</param>
416????????public?void?SetHAlignRight(string?strStartCell,?string?strEndCell)
417????????{
418????????????CurSheet.get_Range(strStartCell,?strEndCell).HorizontalAlignment?=?Excel.XlHAlign.xlHAlignRight;
419????????}
420
421????????/**////?<summary>
422????????///?設置單元格或連續區域的顯示格式
423????????///?</summary>
424????????///?<param?name="strCell">單元格標識符</param>
425????????///?<param?name="strNF">如"#,##0.00"的顯示格式</param>
426????????public?void?SetNumberFormat(string?strCell,?string?strNF)
427????????{
428????????????CurSheet.get_Range(strCell,?mValue).NumberFormat?=?strNF;
429????????}
430
431????????/**////?<summary>
432????????///?設置連續區域的顯示格式
433????????///?</summary>
434????????///?<param?name="strStartCell">開始單元格標識符</param>
435????????///?<param?name="strEndCell">結束單元格標識符</param>
436????????///?<param?name="strNF">如"#,##0.00"的顯示格式</param>
437????????public?void?SetNumberFormat(string?strStartCell,?string?strEndCell,?string?strNF)
438????????{
439????????????CurSheet.get_Range(strStartCell,?strEndCell).NumberFormat?=?strNF;
440????????}
441
442????????/**////?<summary>
443????????///?設置單元格或連續區域的字體大小
444????????///?</summary>
445????????///?<param?name="strCell">單元格或連續區域標識符</param>
446????????///?<param?name="intFontSize"></param>
447????????public?void?SetFontSize(string?strCell,?int?intFontSize)
448????????{
449????????????CurSheet.get_Range(strCell,?mValue).Font.Size?=?intFontSize.ToString();
450????????}
451
452????????/**////?<summary>
453????????///?設置連續區域的字體大小
454????????///?</summary>
455????????///?<param?name="strStartCell">開始單元格標識符</param>
456????????///?<param?name="strEndCell">結束單元格標識符</param>
457????????///?<param?name="intFontSize">字體大小</param>
458????????public?void?SetFontSize(string?strStartCell,?string?strEndCell,?int?intFontSize)
459????????{
460????????????CurSheet.get_Range(strStartCell,?strEndCell).Font.Size?=?intFontSize.ToString();
461????????}
462
463????????/**////?<summary>
464????????///?設置列寬
465????????///?</summary>
466????????///?<param?name="strColID">列標識，如A代表第一列</param>
467????????///?<param?name="decWidth">寬度</param>
468????????public?void?SetColumnWidth(string?strColID,?double?dblWidth)
469????????{
470????????????((Excel.Range)CurSheet.Columns.GetType().InvokeMember("Item",?System.Reflection.BindingFlags.GetProperty,?null,?CurSheet.Columns,?new?object[]{(strColID?+?":"?+?strColID).ToString()})).ColumnWidth?=?dblWidth;
471????????}
472
473????????/**////?<summary>
474????????///?為單元格添加超級鏈接
475????????///?</summary>
476????????///?<param?name="strCell">單元格標識符</param>
477????????///?<param?name="strAddress">鏈接地址</param>
478????????///?<param?name="strTip">屏幕提示</param>
479????????///?<param?name="strText">鏈接文本</param>
480????????public?void?AddHyperLink(string?strCell,?string?strAddress,?string?strTip,?string?strText)
481????????{
482????????????CurSheet.Hyperlinks.Add(CurSheet.get_Range(strCell,?mValue),?strAddress,?mValue,?strTip,?strText);
483????????}
484
485????????/**////?<summary>
486????????///?已知開始的單元格標識，求intR行、intColumn列后的單元格標識
487????????///?</summary>
488????????///?<param?name="strStartCell">開始單元格標識</param>
489????????///?<param?name="intR">行數</param>
490????????///?<param?name="intC">列數</param>
491????????///?<returns>單元格標識符結果</returns>
492????????public?string?GetEndCell(string?strStartCell,?int?intR,?int?intC)
493????????{
494
495????????????System.Text.RegularExpressions.Regex?regex?=?new?System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\d+)");
496
497????????????return?this.NtoL(this.LtoN(regex.Match(strStartCell).Result("${vLetter}"))?+?intC)?+?System.Convert.ToString((System.Convert.ToInt32(regex.Match(strStartCell).Result("${vNumber}"))?+?intR));
498
499????????}
500
501????????/**////?<summary>
502????????///?獲取單元格標識符中的字母
503????????///?</summary>
504????????///?<param?name="strCell">單元格標識符</param>
505????????///?<returns>單元格標識符對應的字母</returns>
506????????public?string?GetCellLetter(string?strCell)
507????????{
508????????????System.Text.RegularExpressions.Regex?regex?=?new?System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\d+)");
509????????????return?regex.Match(strCell).Result("${vLetter}");
510????????}
511
512????????/**////?<summary>
513????????///?獲取單元格標識符中的數字
514????????///?</summary>
515????????///?<param?name="strCell">單元格標識符</param>
516????????public?int?GetCellNumber(string?strCell)
517????????{
518????????????System.Text.RegularExpressions.Regex?regex?=?new?System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\d+)");
519????????????return?System.Convert.ToInt32(regex.Match(strCell).Result("${vNumber}"));
520????????}
521
522
523????????/**////?<summary>
524????????///?另存為xls文件
525????????///?</summary>
526????????///?<param?name="strFilePath">文件路徑</param>
527????????public?void?Save(string?strFilePath)
528????????{
529????????????CurBook.SaveCopyAs(strFilePath);
530????????}
531
532????????/**////?<summary>
533????????///?另存為html文件
534????????///?</summary>
535????????///?<param?name="strFilePath">文件路徑</param>
536????????public?void?SaveHtml(string?strFilePath)
537????????{
538????????????CurBook.SaveAs(strFilePath,?Excel.XlFileFormat.xlHtml,?mValue,?mValue,?mValue,?mValue,?Excel.XlSaveAsAccessMode.xlNoChange,?mValue,?mValue,?mValue,?mValue,?mValue);
539????????}
540
541????????public?void?CreateHtmlFile()
542????????{
543????????????
544????????}
545
546
547???????????
548????????輔助函數#region?輔助函數
549
550????????/**////?<summary>
551????????///?調用MessageBox顯示警告信息
552????????///?</summary>
553????????///?<param?name="text">警告信息</param>
554????????private?void?MessageWarning(string?text)
555????????{
556????????????System.Windows.Forms.MessageBox.Show(text,?"Excel操作組件",?System.Windows.Forms.MessageBoxButtons.OK,?System.Windows.Forms.MessageBoxIcon.Warning);
557????????}
558
559????????/**////?<summary>
560????????///?調用MessageBox顯示警告信息
561????????///?</summary>
562????????///?<param?name="text">警告信息</param>
563????????///?<param?name="ex">產生警告的異常</param>
564????????private?void?MessageWarning(string?text,?System.Exception?ex)
565????????{
566????????????System.Windows.Forms.MessageBox.Show(text?+?"\n\n錯誤信息：\n"?+?ex.Message?+?"\n堆棧跟蹤："?+?ex.StackTrace?+?"\n錯誤來源："?+?ex.Source,?"Excel操作組件",?System.Windows.Forms.MessageBoxButtons.OK,?System.Windows.Forms.MessageBoxIcon.Warning);
567????????}
568
569????????/**////?<summary>
570????????///?字母轉換為數字，Excel列頭，如A-1;AA-27
571????????///?</summary>
572????????///?<param?name="strLetter">字母</param>
573????????///?<returns>字母對應的數字</returns>
574????????private?int?LtoN(string?strLetter)
575????????{
576????????????int?intRtn?=?0;
577
578????????????string?strLetters?=?"ABCDEFGHIJKLMNOPQRSTUVWXYZ";
579
580????????????if?(strLetter.Length?==?2)
581????????????????intRtn?+=?(strLetters.IndexOf(strLetter.Substring(0,?1))?+?1)?*?26;
582
583????????????intRtn?+=?strLetters.IndexOf(strLetter.Substring(strLetter.Length?-?1,?1))?+?1;
584
585????????????return?intRtn;
586
587????????}
588
589????????/**////?<summary>
590????????///?數字轉換為字母，Excel列頭，如1-A;27-AA
591????????///?</summary>
592????????///?<param?name="intNumber">數字</param>
593????????///?<returns>數字對應的字母</returns>
594????????private?string?NtoL(int?intNumber)
595????????{
596????????????if?(intNumber?>?702)
597????????????????return?String.Empty;
598
599????????????if?(intNumber?==?702)
600????????????????return?"ZZ";
601
602????????????string?strRtn?=?String.Empty;
603
604????????????string?strLetters?=?"ABCDEFGHIJKLMNOPQRSTUVWXYZ";
605
606????????????if?(intNumber?>?26)?
607????????????????strRtn?=?strLetters.Substring(intNumber?/?26?-?1,?1);
608????????????
609????????????strRtn?+=?strLetters.Substring((intNumber?%?26)?-?1,?1);
610
611????????????return?strRtn;
612????????}
613
614????????#endregion?輔助函數
615
616
617
618????}
619}

轉載于:https://www.cnblogs.com/gzsxy8013/archive/2005/11/09/272248.html

總結

以上是生活随笔為你收集整理的经常需要在开发中使用Excel COM，为简化使用写了这个类，感觉还是不太方便的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。