1 class ExportToExcel
2 {
3
4 public void SaveExcel(DataTable dt, string Filter, string FileName, string SheetName)
5 {
6
7 if (FileName == "")
8 {
9 SaveFileDialog a = new SaveFileDialog();
10 a.Filter = "Excel 工作簿 (*.xls)|*.xls";
11 if (a.ShowDialog() == DialogResult.OK)
12 {
13 FileName = a.FileName;
14 }
15 else
16 {
17 return;
18 }
19 }
20
21 try
22 {
23 System.IO.File.Delete(FileName);
24 }
25 catch (Exception)
26 {
27 MessageBox.Show("该文件已经存在,删除文件时出错!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
28 return;
29 }
30
31 string ConnStr;
32 ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + FileName + "\";Extended Properties=\"Excel 8.0;HDR=YES\"";
33
34 OleDbConnection conn_excel = new OleDbConnection();
35 conn_excel.ConnectionString = ConnStr;
36
37 OleDbCommand cmd_excel = new OleDbCommand();
38
39 string sql;
40 sql = SqlCreate(dt, SheetName);
41
42 conn_excel.Open();
43 cmd_excel.Connection = conn_excel;
44 cmd_excel.CommandText = sql;
45 cmd_excel.ExecuteNonQuery();
46
47 conn_excel.Close();
48
49 OleDbDataAdapter da_excel = new OleDbDataAdapter("Select * From [" + SheetName + "$]", conn_excel);
50 DataTable dt_excel = new DataTable();
51 da_excel.Fill(dt_excel);
52
53 da_excel.InsertCommand = SqlInsert(SheetName, dt, conn_excel);
54
55 DataRow dr_excel;
56 string ColumnName;
57
58 foreach (DataRow dr in dt.Select(Filter))
59 {
60 dr_excel = dt_excel.NewRow();
61
62 foreach (DataColumn dc in dt.Columns)
63 {
64 ColumnName = dc.ColumnName;
65 dr_excel[ColumnName] = dr[ColumnName];
66
67 }
68 dt_excel.Rows.Add(dr_excel);
69
70 }
71
72 da_excel.Update(dt_excel);
73 conn_excel.Close();
74
75 if (MessageBox.Show("数据成功导出到『" + FileName + "』,是否现在打开?", "导出",
76 MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
77 {
78 System.Diagnostics.Process.Start(FileName);
79 }
80 }
81
82 private void CheckColumn(DataTable dt, DataTable dt_v)
83 {
84 foreach (DataRow dr in dt_v.Select())
85 {
86 if (!dt.Columns.Contains(dr["列名"].ToString()))
87 {
88 dr.Delete();
89 }
90 }
91 dt_v.AcceptChanges();
92 }
93
94 private string GetDataType(Type i)
95 {
96 string s;
97
98 switch (i.Name)
99 {
100 case "String":
101 s = "Char";
102 break;
103 case "Int32":
104 s = "Int";
105 break;
106 case "Int64":
107 s = "Int";
108 break;
109 case "Int16":
110 s = "Int";
111 break;
112 case "Double":
113 s = "Double";
114 break;
115 case "Decimal":
116 s = "Double";
117 break;
118 default:
119 s = "Char";
120 break;
121
122 }
123 return s;
124 }
125
126 private OleDbType StringToOleDbType(Type i)
127 {
128 OleDbType s;
129
130 switch (i.Name)
131 {
132 case "String":
133 s = OleDbType.Char;
134 break;
135 case "Int32":
136 s = OleDbType.Integer;
137 break;
138 case "Int64":
139 s = OleDbType.Integer;
140 break;
141 case "Int16":
142 s = OleDbType.Integer;
143 break;
144 case "Double":
145 s = OleDbType.Double;
146 break;
147 case "Decimal":
148 s = OleDbType.Decimal;
149 break;
150 default:
151 s = OleDbType.Char;
152 break;
153
154 }
155 return s;
156
157 }
158
159
160 private string SqlCreate(DataTable dt, string SheetName)
161 {
162 string sql;
163
164 sql = "CREATE TABLE " + SheetName + " (";
165
166 foreach (DataColumn dc in dt.Columns)
167 {
168 sql += "[" + dc.ColumnName + "] " + GetDataType(dc.DataType) + " ,";
169 }
170
171 //sql = "CREATE TABLE [" + SheetName + "] (";
172
173 //foreach (C1.Win.C1TrueDBGrid.C1DataColumn dc in grid.Columns)
174 //{
175 // sql += "[" + dc.Caption + "] " + GetDataType(dc.DataType) + ",";
176 //}
177 //sql = sql.Substring(0, sql.Length - 1);
178 //sql += ")";
179
180 sql = sql.Substring(0, sql.Length - 1);
181 sql += ")";
182
183 return sql;
184 }
185
186
187 // 生成 InsertCommand 并设置参数
188 private OleDbCommand SqlInsert(string SheetName, DataTable dt, OleDbConnection conn_excel)
189 {
190 OleDbCommand i;
191 string sql;
192
193 sql = "INSERT INTO [" + SheetName + "$] (";
194 foreach (DataColumn dc in dt.Columns)
195 {
196 sql += "[" + dc.ColumnName + "] ";
197 sql += ",";
198 }
199 sql = sql.Substring(0, sql.Length - 1);
200 sql += ") VALUES (";
201 foreach (DataColumn dc in dt.Columns)
202 {
203 sql += "?,";
204 }
205 sql = sql.Substring(0, sql.Length - 1);
206 sql += ")";
207
208 i = new OleDbCommand(sql, conn_excel);
209
210 foreach (DataColumn dc in dt.Columns)
211 {
212 i.Parameters.Add("@" + dc.Caption, StringToOleDbType(dc.DataType), 0, dc.Caption);
213 }
214
215 return i;
216 }
217
218 }
2 {
3
4 public void SaveExcel(DataTable dt, string Filter, string FileName, string SheetName)
5 {
6
7 if (FileName == "")
8 {
9 SaveFileDialog a = new SaveFileDialog();
10 a.Filter = "Excel 工作簿 (*.xls)|*.xls";
11 if (a.ShowDialog() == DialogResult.OK)
12 {
13 FileName = a.FileName;
14 }
15 else
16 {
17 return;
18 }
19 }
20
21 try
22 {
23 System.IO.File.Delete(FileName);
24 }
25 catch (Exception)
26 {
27 MessageBox.Show("该文件已经存在,删除文件时出错!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
28 return;
29 }
30
31 string ConnStr;
32 ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + FileName + "\";Extended Properties=\"Excel 8.0;HDR=YES\"";
33
34 OleDbConnection conn_excel = new OleDbConnection();
35 conn_excel.ConnectionString = ConnStr;
36
37 OleDbCommand cmd_excel = new OleDbCommand();
38
39 string sql;
40 sql = SqlCreate(dt, SheetName);
41
42 conn_excel.Open();
43 cmd_excel.Connection = conn_excel;
44 cmd_excel.CommandText = sql;
45 cmd_excel.ExecuteNonQuery();
46
47 conn_excel.Close();
48
49 OleDbDataAdapter da_excel = new OleDbDataAdapter("Select * From [" + SheetName + "$]", conn_excel);
50 DataTable dt_excel = new DataTable();
51 da_excel.Fill(dt_excel);
52
53 da_excel.InsertCommand = SqlInsert(SheetName, dt, conn_excel);
54
55 DataRow dr_excel;
56 string ColumnName;
57
58 foreach (DataRow dr in dt.Select(Filter))
59 {
60 dr_excel = dt_excel.NewRow();
61
62 foreach (DataColumn dc in dt.Columns)
63 {
64 ColumnName = dc.ColumnName;
65 dr_excel[ColumnName] = dr[ColumnName];
66
67 }
68 dt_excel.Rows.Add(dr_excel);
69
70 }
71
72 da_excel.Update(dt_excel);
73 conn_excel.Close();
74
75 if (MessageBox.Show("数据成功导出到『" + FileName + "』,是否现在打开?", "导出",
76 MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
77 {
78 System.Diagnostics.Process.Start(FileName);
79 }
80 }
81
82 private void CheckColumn(DataTable dt, DataTable dt_v)
83 {
84 foreach (DataRow dr in dt_v.Select())
85 {
86 if (!dt.Columns.Contains(dr["列名"].ToString()))
87 {
88 dr.Delete();
89 }
90 }
91 dt_v.AcceptChanges();
92 }
93
94 private string GetDataType(Type i)
95 {
96 string s;
97
98 switch (i.Name)
99 {
100 case "String":
101 s = "Char";
102 break;
103 case "Int32":
104 s = "Int";
105 break;
106 case "Int64":
107 s = "Int";
108 break;
109 case "Int16":
110 s = "Int";
111 break;
112 case "Double":
113 s = "Double";
114 break;
115 case "Decimal":
116 s = "Double";
117 break;
118 default:
119 s = "Char";
120 break;
121
122 }
123 return s;
124 }
125
126 private OleDbType StringToOleDbType(Type i)
127 {
128 OleDbType s;
129
130 switch (i.Name)
131 {
132 case "String":
133 s = OleDbType.Char;
134 break;
135 case "Int32":
136 s = OleDbType.Integer;
137 break;
138 case "Int64":
139 s = OleDbType.Integer;
140 break;
141 case "Int16":
142 s = OleDbType.Integer;
143 break;
144 case "Double":
145 s = OleDbType.Double;
146 break;
147 case "Decimal":
148 s = OleDbType.Decimal;
149 break;
150 default:
151 s = OleDbType.Char;
152 break;
153
154 }
155 return s;
156
157 }
158
159
160 private string SqlCreate(DataTable dt, string SheetName)
161 {
162 string sql;
163
164 sql = "CREATE TABLE " + SheetName + " (";
165
166 foreach (DataColumn dc in dt.Columns)
167 {
168 sql += "[" + dc.ColumnName + "] " + GetDataType(dc.DataType) + " ,";
169 }
170
171 //sql = "CREATE TABLE [" + SheetName + "] (";
172
173 //foreach (C1.Win.C1TrueDBGrid.C1DataColumn dc in grid.Columns)
174 //{
175 // sql += "[" + dc.Caption + "] " + GetDataType(dc.DataType) + ",";
176 //}
177 //sql = sql.Substring(0, sql.Length - 1);
178 //sql += ")";
179
180 sql = sql.Substring(0, sql.Length - 1);
181 sql += ")";
182
183 return sql;
184 }
185
186
187 // 生成 InsertCommand 并设置参数
188 private OleDbCommand SqlInsert(string SheetName, DataTable dt, OleDbConnection conn_excel)
189 {
190 OleDbCommand i;
191 string sql;
192
193 sql = "INSERT INTO [" + SheetName + "$] (";
194 foreach (DataColumn dc in dt.Columns)
195 {
196 sql += "[" + dc.ColumnName + "] ";
197 sql += ",";
198 }
199 sql = sql.Substring(0, sql.Length - 1);
200 sql += ") VALUES (";
201 foreach (DataColumn dc in dt.Columns)
202 {
203 sql += "?,";
204 }
205 sql = sql.Substring(0, sql.Length - 1);
206 sql += ")";
207
208 i = new OleDbCommand(sql, conn_excel);
209
210 foreach (DataColumn dc in dt.Columns)
211 {
212 i.Parameters.Add("@" + dc.Caption, StringToOleDbType(dc.DataType), 0, dc.Caption);
213 }
214
215 return i;
216 }
217
218 }