本文实例讲述了C#实现的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
|
using System;using System.Data;using System.Data.OleDb;using System.Text;using System.IO;namespace Hxh.API{ /// <summary> /// ExcelOpration 的摘要说明。 /// </summary> public class ExcelOpration { OleDbConnection Conn; string connString ; string FileName; public ExcelOpration() { // // TODO: 在此处添加构造函数逻辑 // } public ExcelOpration(string _FileName) { // // TODO: 在此处添加构造函数逻辑 // FileName = _FileName; } /// <summary> /// 打开连接 /// </summary> private void Open() { if(Conn==null) { //在此处设置访问的数据库文件 connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName +";Extended Properties=Excel 8.0;"; Conn=new OleDbConnection(connString); Conn.Open(); } else if(Conn.State==System.Data.ConnectionState.Closed) Conn.Open(); } /// <summary> /// 关闭连接 /// </summary> public void Close() { if (Conn != null) Conn.Close(); } /// <summary> /// 导入数据到Excel /// </summary> /// <param name="OutTable"></param> public void ImportData(DataTable OutTable) { CreateExcel(OutTable); InsertData(OutTable); } /// <summary> /// 创建Excel文件和表头 /// </summary> private void CreateExcel(DataTable OutTable) { StringBuilder sb = new StringBuilder(); if(File.Exists(FileName)) { File.Delete(FileName); } sb.Append("create table Sheet1("); foreach(DataColumn col in OutTable.Columns) { sb.Append(col.ColumnName+" varchar,"); } sb.Remove(sb.Length-1,1); sb.Append(")"); Open(); OleDbCommand OleCmd = new OleDbCommand(); OleCmd.Connection = Conn; OleCmd.CommandText = sb.ToString(); OleCmd.ExecuteNonQuery(); Close(); } /// <summary> /// 插入数据 /// </summary> private void InsertData(DataTable OutTable) { OleDbCommand OleCmd = new OleDbCommand(); OleDbParameter[] parm=new OleDbParameter[OutTable.Columns.Count]; StringBuilder sb = new StringBuilder(); sb.Append("insert into Sheet1 values("); for(int i=0;i<OutTable.Columns.Count;i++) { parm=new OleDbParameter("@P"+OutTable.Columns.ColumnName,OleDbType.VarChar); sb.Append("@P"+OutTable.Columns.ColumnName+","); OleCmd.Parameters.Add(parm); } sb.Remove(sb.Length-1,1); sb.Append(")"); Open(); OleCmd.Connection = Conn; OleCmd.CommandText = sb.ToString(); foreach(DataRow row in OutTable.Rows) { for(int i=0;i<OutTable.Columns.Count;i++) { parm.Value = row[OutTable.Columns.ColumnName].ToString().Trim(); } OleCmd.ExecuteNonQuery(); } Close(); } /// <summary> /// 从Excel输出数据到数据集 /// </summary> /// <returns></returns> public DataSet OutPortData() { DataSet ds = new DataSet(); Open(); OleDbDataAdapter myAdapter = new OleDbDataAdapter("select * from [Sheet1$]", Conn); myAdapter.Fill(ds,"Input"); Close(); return ds; } }} |
希望本文所述对大家C#程序设计有所帮助。
