本文實例講述了C#基于COM方式讀取Excel表格的方法。分享給大家供大家參考，具體如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
|
using System;using System.Collections.Generic;using System.Collections.ObjectModel;using System.Data;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows;using System.Collections;//TestEnviroment:VS2013Update4 Excel2007//Read by COM Objectnamespace SmartStore.LocalModel{ public class ExcelTable { private string _path; public ExcelTable() { _path = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; _path += "條碼對照表.xls"; } public void ReadEPC2BarCode(out ArrayList arrayPI) { DataTable dt = ReadSheet(2); arrayPI = new ArrayList(); foreach (DataRow dr in dt.Rows) { EPC2BarCode eb = new EPC2BarCode(); eb.EPC = (string)dr["epcID"]; eb.Barcode = (string)dr["條形碼"]; eb.EPC = eb.EPC.Trim(); eb.Barcode = eb.Barcode.Trim(); if (eb.EPC == null || eb.EPC.Length <= 0) break; arrayPI.Add(eb); } } public void ReadProductInfo(out ArrayList arrayPI) { DataTable dt = ReadSheet(1); arrayPI = new ArrayList(); foreach (DataRow dr in dt.Rows) { ProductInfo pi = new ProductInfo(); pi.Name = (string)dr["商品名稱"]; pi.SN = (string)dr["商品編號"]; pi.BarCode = (string)dr["商品條碼"]; pi.Brand = (string)dr["品牌"]; pi.Color = (string)dr["顏色"]; pi.Size = (string)dr["尺碼"]; pi.Name = pi.Name.Trim(); pi.SN = pi.SN.Trim(); pi.BarCode = pi.BarCode.Trim(); pi.Brand = pi.Brand.Trim(); pi.Color = pi.Color.Trim(); pi.Size = pi.Size.Trim(); if (pi.Name == null || pi.Name.Length <= 0) break; arrayPI.Add(pi); } } private DataTable ReadSheet(int indexSheet) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Sheets sheets; Microsoft.Office.Interop.Excel.Workbook workbook = null; object oMissiong = System.Reflection.Missing.Value; System.Data.DataTable dt = new System.Data.DataTable(); try { workbook = app.Workbooks.Open(_path, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong); //將數據讀入到DataTable中——Start sheets = workbook.Worksheets; //輸入1, 讀取第一張表 Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(indexSheet); if (worksheet == null) return null; string cellContent; int iRowCount = worksheet.UsedRange.Rows.Count; int iColCount = worksheet.UsedRange.Columns.Count; Microsoft.Office.Interop.Excel.Range range; //負責列頭Start DataColumn dc; int ColumnID = 1; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]; while (range.Text.ToString().Trim() != "") { dc = new DataColumn(); dc.DataType = System.Type.GetType("System.String"); dc.ColumnName = range.Text.ToString().Trim(); dt.Columns.Add(dc); range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, ++ColumnID]; } //End for (int iRow = 2; iRow <= iRowCount; iRow++) { DataRow dr = dt.NewRow(); for (int iCol = 1; iCol <= iColCount; iCol++) { range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol]; cellContent = (range.Value2 == null) ? "" : range.Text.ToString(); //if (iRow == 1) //{ // dt.Columns.Add(cellContent); //} //else //{ dr[iCol - 1] = cellContent; //} } //if (iRow != 1) dt.Rows.Add(dr); } //將數據讀入到DataTable中——End return dt; } catch { return null; } finally { workbook.Close(false, oMissiong, oMissiong); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; app.Workbooks.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; GC.Collect(); GC.WaitForPendingFinalizers(); } } }} |
希望本文所述對大家C#程序設計有所幫助。
