VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > c#编程 >

  • C#教程之WinForm项目开发中Excel用法实例解析

在实际项目的开发过程中，所涉及的EXCEL往往会比较复杂，并且列中还会带有一些计算公式，这就给读取带来了很大的困难，曾经尝试过一些免费的第三方dll，譬如Myxls，NPOI，IExcelDataReader都会出现一些问题，最后采用OLEDB形式读取，再x64操作系统上有点问题，不过采用小技巧即可解决，可以参考链接地址：http://ellisweb.net/2010/01/connecting-to-excel-and-access-files-using-net-on-a-64-bit-server/

封装代码如下：

?
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
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
namespace DBUtilHelpV2
{
public class OLEDBExcelToolV2
{
static readonly string xls = ".xls";
static readonly string xlsx = ".xlsx";
string _ExcelExtension = string.Empty;//后缀
string _ExcelPath = string.Empty;//路径
string _ExcelConnectString = string.Empty;//链接字符串
static bool _X64Version = false;//是否强制使用x64链接字符串，即xlsx形式
public OLEDBExcelToolV2(string excelPath, bool x64Version)
{
  if (string.IsNullOrEmpty(excelPath))
 throw new ArgumentNullException("excelPath");
  if (!File.Exists(excelPath))
 throw new ArgumentException("excelPath");
  string _excelExtension = Path.GetExtension(excelPath);
  _ExcelExtension = _excelExtension.ToLower();
  _ExcelPath = excelPath;
  _X64Version = x64Version;
  _ExcelConnectString = BuilderConnectionString();
}
/// <summary>
/// 创建链接字符串
/// </summary>
/// <returns></returns>
private string BuilderConnectionString()
{
  Dictionary<string, string> _connectionParameter = new Dictionary<string, string>();
  if (!_ExcelExtension.Equals(xlsx) && !_ExcelExtension.Equals(xls))
  {
 throw new ArgumentException("excelPath");
  }
 
  if (!_X64Version)
  {
 if (_ExcelExtension.Equals(xlsx))
 {
   // XLSX - Excel 2007, 2010, 2012, 2013
   _connectionParameter["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
   _connectionParameter["Extended Properties"] = "'Excel 12.0 XML;IMEX=1'";
 }
 else if (_ExcelExtension.Equals(xls))
 {
   // XLS - Excel 2003 and Older
   _connectionParameter["Provider"] = "Microsoft.Jet.OLEDB.4.0";
   _connectionParameter["Extended Properties"] = "'Excel 8.0;IMEX=1'";
 }
  }
  else
  {
 _connectionParameter["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
 _connectionParameter["Extended Properties"] = "'Excel 12.0 XML;IMEX=1'";
  }
 
  _connectionParameter["Data Source"] = _ExcelPath;
  StringBuilder _connectionString = new StringBuilder();
 
  foreach (KeyValuePair<string, string> parameter in _connectionParameter)
  {
 _connectionString.Append(parameter.Key);
 _connectionString.Append('=');
 _connectionString.Append(parameter.Value);
 _connectionString.Append(';');
  }
  return _connectionString.ToString();
}
/// <summary>
/// Excel操作
/// DELETE不支持
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sql)
{
  int _affectedRows = -1;
  using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))
  {
 try
 {
   sqlcon.Open();
   using (OleDbCommand sqlcmd = new OleDbCommand(sql, sqlcon))
   {
 _affectedRows = sqlcmd.ExecuteNonQuery();
   }
 }
 catch (Exception)
 {
   return -1;
 }
  }
  return _affectedRows;
}
/// <summary>
/// Excel操作
///获取EXCEL内sheet集合
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public string[] GetExcelSheetNames()
{
  DataTable _schemaTable = null;
  using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))
  {
 try
 {
   sqlcon.Open();
   _schemaTable = sqlcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
   String[] _excelSheets = new String[_schemaTable.Rows.Count];
   int i = 0;
   foreach (DataRow row in _schemaTable.Rows)
   {
 _excelSheets[i] = row["TABLE_NAME"].ToString().Trim();
 i++;
   }
   return _excelSheets;
 }
 catch (Exception)
 {
   return null;
 }
 finally
 {
   if (_schemaTable != null)
   {
 _schemaTable.Dispose();
   }
 }
  }
}
/// <summary>
/// 读取sheet
/// eg:select * from [Sheet1$]
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql)
{
  using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))
  {
 try
 {
   using (OleDbCommand sqlcmd = new OleDbCommand(sql, sqlcon))
   {
 using (OleDbDataAdapter sqldap = new OleDbDataAdapter(sqlcmd))
 {
   DataTable _dtResult = new DataTable();
   sqldap.Fill(_dtResult);
   return _dtResult;
 }
   }
 }
 catch (Exception)
 {
   return null;
 }
  }
 
}
/// <summary>
/// 获取excel所有sheet数据
/// </summary>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet()
{
  DataSet _excelDb = null;
  using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))
  {
 try
 {
   sqlcon.Open();
   DataTable _schemaTable = sqlcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
   if (_schemaTable != null)
   {
 int i = 0;
 _excelDb = new DataSet();
 foreach (DataRow row in _schemaTable.Rows)
 {
   string _sheetName = row["TABLE_NAME"].ToString().Trim();
   string _sql = string.Format("select * from [{0}]", _sheetName);
   using (OleDbCommand sqlcmd = new OleDbCommand(_sql, sqlcon))
   {
  using (OleDbDataAdapter sqldap = new OleDbDataAdapter(sqlcmd))
  {
    DataTable _dtResult = new DataTable();
    _dtResult.TableName = _sheetName;
    sqldap.Fill(_dtResult);
    _excelDb.Tables.Add(_dtResult);
  }
   }
   i++;
 }
   }
 }
 catch (Exception)
 {
   return null;
 }
  }
  return _excelDb;
}
}
}

代码使用方法如下：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
/// <summary>
/// 合并EXCEL数据
/// </summary>
/// <param name="_excelPath">excel路径</param>
private void HandleMergeExcel(string _excelPath)
{
  if (!string.IsNullOrEmpty(_excelPath))
  {
 OLEDBExcelToolV2 _excelHelper = new OLEDBExcelToolV2(_excelPath, true);
 DataSet _excelSource = _excelHelper.ExecuteDataSet();
 HandleExcelSource(_excelSource);
  }
}

若在x64操作系统，将第二个参数设置true，并且按照AccessDatabaseEngine_X64.exe即可正常读取

代码运行效果如下图所示：

 



相关教程