本文实例讲述了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 Object
namespace 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#程序设计有所帮助。