整理文档，搜刮出一个C# 通过 oledb 操作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
|
public string GetConnectionString() { Dictionary<string, string> props = new Dictionary<string, string>(); // XLSX - Excel 2007, 2010, 2012, 2013 props["Provider"] = "Microsoft.ACE.OLEDB.12.0;"; props["Extended Properties"] = "Excel 12.0 XML"; props["Data Source"] = @"C:\tools\MyExcel.xlsx"; // XLS - Excel 2003 and Older //props["Provider"] = "Microsoft.Jet.OLEDB.4.0"; //props["Extended Properties"] = "Excel 8.0"; //props["Data Source"] = "C:\\MyExcel.xls"; var sb = new StringBuilder(); foreach (KeyValuePair<string, string> prop in props) { sb.Append(prop.Key); sb.Append('='); sb.Append(prop.Value); sb.Append(';'); } return sb.ToString(); } public void WriteExcelFile() { string connectionString = GetConnectionString(); using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01');"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(2, 'BBBB','2014-01-03');"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(3, 'CCCC','2014-01-03');"; cmd.ExecuteNonQuery(); cmd.CommandText = "UPDATE [table1] SET name = 'DDDD' WHERE id = 3;"; cmd.ExecuteNonQuery(); conn.Close(); } } public DataSet ReadExcelFile() { DataSet ds = new DataSet(); string connectionString = GetConnectionString(); using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; // Get all Sheets in Excel File DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); // Loop through all Sheets to get data foreach (DataRow dr in dtSheet.Rows) { string sheetName = dr["TABLE_NAME"].ToString(); if (!sheetName.EndsWith("$")) continue; // Get all rows from the Sheet cmd.CommandText = "SELECT * FROM [" + sheetName + "]"; DataTable dt = new DataTable(); dt.TableName = sheetName; OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(dt); ds.Tables.Add(dt); } cmd = null; conn.Close(); } return ds; } |
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：http://www.cnblogs.com/lizikang2013/p/6930833.html?utm_source=tuicool&utm_medium=referral
