![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
最近在做一个报表系统的时候,需要把DATASET中的数据导到EXCEL当中,于是在网上找了一遍,发现了好几种方法,本来以为应该差不多,但后来经过一一试用后,发现在性能上真的差别很大,现在就介绍一下,同时有不对的希望可以指正:
2
3
1. 原理:利用office组件把dataset中的数据填充到excel文件当中。
4
这里我不贴出全部代码了,只把关键部分贴出来:
5![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**////<summary>
6
///方法,导出C1TrueDBGrid中的数据到Excel文件
7
///</summary>
8
///<param name="c1grid">C1TrueDBGrid</param>
9
///<param name="FileName">Excel文件名</param>
10
public void ExportToExcel(C1.Win.C1TrueDBGrid.C1TrueDBGrid c1grid,string FileName)
11![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
12
if(FileName.Trim() == "") return; //验证strFileName是否为空或值无效
13
14
int rowscount = c1grid.Splits[0].Rows.Count; //定义表格内数据的行数
15
int colscount = c1grid.Columns.Count; //定义表格内数据的列数
16
17
//行数必须大于0
18
if (rowscount <= 0)
19![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
20
MessageBox.Show("没有数据可供保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
21
return;
22
}
23
24
//列数必须大于0
25
if (colscount <= 0)
26![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
27
MessageBox.Show("没有数据可供保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
28
return;
29
}
30
31
//行数不可以大于65536
32
if (rowscount > 65536)
33![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
34
MessageBox.Show("数据记录数太多(最多不能超过65536条),不能保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
35
return;
36
}
37
38
//列数不可以大于255
39
if (colscount > 255)
40![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
41
MessageBox.Show("数据记录行数太多,不能保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
42
return;
43
}
44
45
//将文件保存到工作路径的子目录“\Excel”下,如果路径不存在创建它
46
string n_path = Directory.GetCurrentDirectory() + "\\Excel";
47
if (Directory.Exists(n_path) == false)
48![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
49
Directory.CreateDirectory(n_path);
50
}
51
52
//验证以strFileName命名的文件是否存在,如果存在删除它
53
FileInfo fi = new FileInfo(n_path + "\\" + FileName + ".xls");
54
if(fi.Exists)
55![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
56
try
57![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
58
fi.Delete();
59
}
60
catch(Exception fie)
61![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
62
MessageBox.Show(fie.Message,"删除失败", MessageBoxButtons.OK, MessageBoxIcon.Warning);
63
return;
64
}
65
}
66
67
Excel.ApplicationClass excel = null;
68
69
try
70![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
71
//显示进度条
72
KP.PublicForm.ProgressBar pb = new PublicForm.ProgressBar("导出进度");
73
pb.Show();
74
pb.Refresh();
75
76
//新建Excel应用,新建Workbook文件
77
excel = new Excel.ApplicationClass ( ) ;
78
Excel.XlSaveAsAccessMode savemode = new Excel.XlSaveAsAccessMode();
79
excel.Application.Workbooks.Add (true) ;
80
81
//向Excel中写入表格的表头
82
int i = 1;
83
for(int c = 0;c < colscount; c++)
84![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
85
if(c1grid.Splits[0].DisplayColumns[c].Visible)
86![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
87
excel.Cells[1,i] = c1grid.Columns[c].Caption;
88
i++;
89
}
90
}
91
92
//向Excel中逐行逐列写入表格中的数据
93
for(int r = 0; r < rowscount; r++)
94![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
95
Application.DoEvents();
96
pb.SetProgressBarValue(r+1, rowscount);
97
if(pb.Cancel)
98![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
99
break;
100
}
101
102
int j = 1;
103
for(int c = 0;c < colscount; c++)
104![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
105
if(c1grid.Splits[0].DisplayColumns[c].Visible)
106![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
107
excel.Cells[r + 2,j] = c1grid.Columns[c].CellText(r);
108
j++;
109
}
110
}
111
}
112
113
//向Excel中写入表格的脚
114
if(c1grid.ColumnFooters)
115![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
116
int col = 1;
117
for(int c = 0;c < colscount; c++)
118![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
119
if(c1grid.Splits[0].DisplayColumns[c].Visible)
120![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
121
if(c1grid.Columns[c].FooterText != null && c1grid.Columns[c].FooterText.Trim() != "")
122![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
123
excel.Cells[rowscount + 2,col] = c1grid.Columns[c].FooterText;
124
}
125
col++;
126
}
127
}
128
}
129
130
//关闭进度条
131
pb.Close();
132
133
//设置Excel的默认保存路径为当前路径下的Excel子文件夹
134
excel.DefaultFilePath = n_path;
135
136
//保存文件
137
excel.ActiveWorkbook.SaveAs(FileName + ".xls",excel.ActiveWorkbook.FileFormat,"","",excel.ActiveWorkbook.ReadOnlyRecommended,excel.ActiveWorkbook.CreateBackup,savemode,excel.ActiveWorkbook.ConflictResolution,false,"","");
138
139
}
140
catch(Exception e1)
141![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
142
MessageBox.Show(e1.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
143
return;
144
}
145
finally
146![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
147
//关闭Excel应用
148
if(excel != null) excel.Quit();
149
}
150
MessageBox.Show(FileName + "导出完毕,在" + Application.StartupPath + "\\Excel文件夹下","提示", MessageBoxButtons.OK,MessageBoxIcon.Information);
151
// }
152
153
}
154
155
总结:这个方法是可以解决问题,但效率最差,3000条长点的record就用了6分钟,晕~~~~
156
157
2. 原理:利用office组件,同时把dataset的数据导到Clipboard中,然后通过粘贴到excel中。
158
Excel.XlSaveAsAccessMode savemode = new Excel.XlSaveAsAccessMode();
159
xlApp.Application.Workbooks.Add (true) ;
160
xlApp.DefaultFilePath = @"c:\";
161
xlApp.ActiveWorkbook.SaveAs("exportExcel.xls",xlApp.ActiveWorkbook.FileFormat,"","",xlApp.ActiveWorkbook.ReadOnlyRecommended,xlApp.ActiveWorkbook.CreateBackup,savemode,xlApp.ActiveWorkbook.ConflictResolution,false,"","","");
162
163
164
165
166
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing,
167
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
168
oMissing,oMissing,oMissing);
169
170
Excel.Worksheet xlWorksheet;
171
172
173
// 循环所有DataTable
174
for( int i=0; i<ds.Tables.Count; i++ )
175![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
176
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing);
177
// 以TableName作为新加的Sheet页名。
178
xlWorksheet.Name = ds.Tables[i].TableName;
179
// 取出这个DataTable中的所有值,暂存于stringBuffer中。
180
string stringBuffer = "";
181
//向Excel中写入表格的表头
182
if(node != null)
183![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
184
XmlNode nodec=node.SelectSingleNode("./Method/ShowField");
185
int ii = 1;
186
foreach(XmlNode xnode in nodec.ChildNodes )
187![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
188
xlApp.Cells[1,ii] =xnode.Attributes["displayname"].Value;
189
ii++;
190
}
191
192
193
for( int j=0; j<ds.Tables[i].Rows.Count; j++ )
194![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
195
for( int k=0; k<ds.Tables[i].Columns.Count; k++ )
196![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
197
198
stringBuffer += ds.Tables[i].Rows[j][k].ToString();
199
if( k < ds.Tables[i].Columns.Count - 1 )
200
stringBuffer += "\t";
201
}
202
stringBuffer += "\n";
203
}
204
205
}
206
else
207![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
208
int ii = 1;
209
for(int c = 0;c<ds.Tables[i].Columns.Count; c++)
210![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
211
xlApp.Cells[1,ii] = ds.Tables[i].Columns[c].Caption;
212
ii++;
213
}
214
215
for( int j=0; j<ds.Tables[i].Rows.Count; j++ )
216![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
217
for( int k=0; k<ds.Tables[i].Columns.Count; k++ )
218![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
219
220
stringBuffer += ds.Tables[i].Rows[j][k].ToString();
221
if( k < ds.Tables[i].Columns.Count - 1 )
222
stringBuffer += "\t";
223
}
224
stringBuffer += "\n";
225
}
226
}
227
228
System.Windows.Forms.Clipboard.SetDataObject("");
229
// 将stringBuffer放入剪切板。
230
System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
231
// 选中这个sheet页中的第一个单元格
232
((Excel.Range)xlWorksheet.Cells[2,1]).Select();
233
// 粘贴!
234
xlWorksheet.Paste(oMissing,oMissing);
235
// 清空系统剪切板。
236
System.Windows.Forms.Clipboard.SetDataObject("");
237
238
239
240
241
}
242
// 保存并关闭这个工作簿。
243
244
245
246
247
248
xlApp.ActiveWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
249
// xlWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
250
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
251
xlWorkbook = null;
252
MessageBox.Show(@"Excel文件:C:\exportExcel.xls 导出成功!");
253
}
254
catch(Exception ex)
255![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
256
MessageBox.Show(ex.Message);
257
}
258
finally
259![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
260
// 释放![](https://www.cnblogs.com/Images/dot.gif)
261
xlApp.Quit();
262
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
263
xlApp = null;
264
GC.Collect();
265
}
266
267
总结:这个方法比上面的方法性能好点,但还是很不好用,比原来的提高了2倍左右。
268
269
3. 原理:利用OLEDB,以excel为数据库,把dataset中的数据导入到excel文件中
270
public static void exportToExcelByDataset(string filePath, DataSet ds,XmlNode node)
271![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
272
string sqlstr;
273![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
274![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
275
if(fi.Exists)
276![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
277
fi.Delete();
278
// throw new Exception("文件删除失败");
279
}
280
else
281![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
282
fi.Create();
283
}
284
285
string sqlcon=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended ProPerties=Excel 8.0;";
286
OleDbConnection lecon = new OleDbConnection(sqlcon);
287
OleDbCommand lecmd = new OleDbCommand();
288
olecmd.Connection = olecon;
289
olecmd.CommandType = CommandType.Text;
290
291![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
292
try
293![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
294
olecon.Open();
295
296
XmlNode nodec=node.SelectSingleNode("./Method/ShowField");
297
int ii = 0;
298
sqlstr = "CREATE TABLE sheet1(";
299
foreach(XmlNode xnode in nodec.ChildNodes )
300![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
301
if(ii == nodec.ChildNodes.Count - 1)
302![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
303
if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")
304![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
305
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number)";
306
}
307
else
308![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
309
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";
310
}
311
// sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";
312
}
313
else
314![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
315
if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")
316![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
317
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number,";
318
}
319
else
320![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
321
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text,";
322
}
323
324
}
325
// sqlstr =sqlstr + xnode.Attributes["displayname"].Value + " text";
326
ii++;
327
}
328
olecmd.CommandText = sqlstr;
329
olecmd.ExecuteNonQuery();
330
for(int i=0;i<ds.Tables[0].Rows.Count;i++)
331![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
332
sqlstr = "INSERT INTO sheet1 VALUES(";
333
int jj=0;
334
foreach(XmlNode inode in nodec.ChildNodes )
335![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
336
if(jj == nodec.ChildNodes.Count-1)
337![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
338
if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")
339![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
340
sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + ")" ;
341
342
}
343
else
344![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
345
sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "')" ;
346
}
347
}
348
else
349![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
350
if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")
351![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
352
sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + "," ;
353
354
}
355
else
356![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
357
sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "'," ;
358
}
359
}
360
jj++;
361
}
362
olecmd.CommandText = sqlstr;
363
olecmd.ExecuteNonQuery();
364
365
}
366
MessageBox.Show(@"Excel文件:" + filePath + " 导出成功!");
367
}
368
catch(Exception ex)
369![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
370
MessageBox.Show(ex.Message);
371
}
372
finally
373![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
374
olecmd.Dispose();
375
olecon.Close();
376
olecon.Dispose();
377
378
}
379
}
380![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
381
/// change to string "null" if input is null
382
/// </summary>
383
/// <param name="obj"></param>
384
/// <returns></returns>
385
386
private static string isnull(string obj)
387![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
388
if(obj.Length >0)
389![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
390
return obj;
391
}
392
else
393![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
394
return "null";
395
}
396
}
397
总结:这个方法是最好的,速度飞快,比上面两种提高不止10倍,而且关键是不需要用到office组件,所以我正在用着这种方法,客户也满意。当然这个也有它不好的地方,有时候会受到导入的数据不符的异常困扰,而且为了赶时间,代码写的不好,一句话,能用但要改进的地方很多:)
398
399![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
400![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**////2007-03-02
401![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
402
最近发现几个导出到EXCEL的方法,这里先记录下来
403![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
404
4.本示例是用于将ListView中的内容倒入到Excel 与常用的逐单元格写不同的是,本例子采用数据写入到range的方法。该方法效率明显较高
405
Excel.Application app = new Excel.ApplicationClass();
406
if( app == null)
407![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
408
MessageBox.Show("Excel无法启动");
409
return;
410
}
411
app.Visible = true;
412
Excel.Workbooks wbs = app.Workbooks;
413
Excel.Workbook wb = wbs.Add(Missing.Value);
414
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
415
Excel.Range r = ws.get_Range("A1","H1");
416![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
object [] bjHeader =
{"标题1","标题2","标题3","标题4","标题5","标题6","标题7","标题8"};
417
r.Value = objHeader;
418
if (lv.Items.Count >0)
419![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
420
r = ws.get_Range("A2",Missing.Value);
421
object [,] bjData = new Object[this.lv.Items.Count,8];
422
foreach(ListViewItem lvi in lv.Items)
423![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
424
objData[lvi.Index,0] = lvi.Text;
425
objData[lvi.Index,1] = lvi.SubItems[1].Text;
426
objData[lvi.Index,2] = lvi.SubItems[2].Text;
427
objData[lvi.Index,3] = lvi.SubItems[3].Text;
428
objData[lvi.Index,4] = lvi.SubItems[4].Text;
429
objData[lvi.Index,5] = lvi.SubItems[5].Text;
430
objData[lvi.Index,6] = lvi.SubItems[6].Text;
431
objData[lvi.Index,7] = lvi.SubItems[7].Text;
432
}
433
r = r.get_Resize(lv.Items.Count,8);
434
r.Value = objData;
435
r.EntireColumn.AutoFit();
436
}
437
app = null;
438![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
439
5.由XML文件导出为EXCEL文件
440![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
441
目录下kfcccer.xml为原始数据XML文件,点击生成后会在同级目录下生成kfcccer.xls文件
442![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
443
页面代码如下:
444
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
445![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
446
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
447![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
448
<html xmlns="http://www.w3.org/1999/xhtml" >
449
<head runat="server">
450
<title>XML转换Excel演示</title>
451
</head>
452
<body>
453
<form. id="form1" runat="server">
454
<div>
455
<div>
456
<asp:Button ID="btnChange" runat="server" Font-Bold="True" Font-Size="18pt" ForeColor="Black"
457
Height="38px" nClick="btnChange_Click" Text="开始转换" Width="203px" /></div>
458
459
</div>
460
</form>
461
</body>
462
</html>
463![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
464
后台代码:
465
using System;
466
using System.Data;
467
using System.Configuration;
468
using System.Collections;
469
using System.Web;
470
using System.Web.Security;
471
using System.Web.UI;
472
using System.Web.UI.WebControls;
473
using System.Web.UI.WebControls.WebParts;
474
using System.Web.UI.HtmlControls;
475
using System.IO;
476![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
477
public partial class _Default : System.Web.UI.Page
478![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
479
protected void Page_Load(object sender, EventArgs e)
480![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
481![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
482
}
483
protected void btnChange_Click(object sender, EventArgs e)
484![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
485
try
486![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
487
//要转换的XML文件
488
string XMLFileName = Path.Combine(Request.PhysicalApplicationPath, "kfcccer.xml");
489
DataSet dsBook = new DataSet();
490
dsBook.ReadXml(XMLFileName);
491
int rows = dsBook.Tables[0].Rows.Count + 1;
492
int cols = dsBook.Tables[0].Columns.Count;
493![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
494
//将要生成的Excel文件
495
string ExcelFileName = Path.Combine(Request.PhysicalApplicationPath, "kfcccer.xls");
496
if (File.Exists(ExcelFileName))
497![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
498
File.Delete(ExcelFileName);
499
}
500
StreamWriter writer = new StreamWriter(ExcelFileName, false);
501
writer.WriteLine("<?xml version="1.0"?>");
502
writer.WriteLine("<?mso-application progid="Excel.Sheet"?>");
503
writer.WriteLine("<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"");
504
writer.WriteLine(" xmlns:o="urn:schemas-microsoft-com:office:office"");
505
writer.WriteLine(" xmlns:x="urn:schemas-microsoft-com:office:excel"");
506
writer.WriteLine(" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"");
507
writer.WriteLine(" xmlns:html="http://www.w3.org/TR/REC-html40/">");
508
writer.WriteLine(" <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">");
509
writer.WriteLine(" <Author>Automated Report Generator Example</Author>");
510
writer.WriteLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
511
writer.WriteLine(" <Company>51aspx.com</Company>");
512
writer.WriteLine(" <Version>11.6408</Version>");
513
writer.WriteLine(" </DocumentProperties>");
514
writer.WriteLine(" <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">");
515
writer.WriteLine(" <WindowHeight>8955</WindowHeight>");
516
writer.WriteLine(" <WindowWidth>11355</WindowWidth>");
517
writer.WriteLine(" <WindowTopX>480</WindowTopX>");
518
writer.WriteLine(" <WindowTopY>15</WindowTopY>");
519
writer.WriteLine(" <ProtectStructure>False</ProtectStructure>");
520
writer.WriteLine(" <ProtectWindows>False</ProtectWindows>");
521
writer.WriteLine(" </ExcelWorkbook>");
522
writer.WriteLine(" <Styles>");
523
writer.WriteLine(" <Style. ss:ID="Default" ss:Name="Normal">");
524
writer.WriteLine(" <Alignment ss:Vertical="Bottom"/>");
525
writer.WriteLine(" <Borders/>");
526
writer.WriteLine(" <Font/>");
527
writer.WriteLine(" <Interior/>");
528
writer.WriteLine(" <Protection/>");
529
writer.WriteLine(" </Style>");
530
writer.WriteLine(" <Style. ss:ID="s21">");
531
writer.WriteLine(" <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>");
532
writer.WriteLine(" </Style>");
533
writer.WriteLine(" </Styles>");
534
writer.WriteLine(" <Worksheet ss:Name="MyReport">");
535![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
writer.WriteLine(string.Format(" <Table ss:ExpandedColumnCount="
{0}" ss:ExpandedRowCount="
{1}" x:FullColumns="1"", cols.ToString(), rows.ToString()));
536
writer.WriteLine(" x:FullRows="1">");
537![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
538
//生成标题
539
writer.WriteLine("<Row>");
540
foreach (DataColumn eachCloumn in dsBook.Tables[0].Columns)
541![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
542
writer.Write("<Cell ss:StyleID="s21"><Data ss:Type="String">");
543
writer.Write(eachCloumn.ColumnName.ToString());
544
writer.WriteLine("</Data></Cell>");
545
}
546
writer.WriteLine("</Row>");
547![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
548
//生成数据记录
549
foreach (DataRow eachRow in dsBook.Tables[0].Rows)
550![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
551
writer.WriteLine("<Row>");
552
for (int currentRow = 0; currentRow != cols; currentRow++)
553![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
554
writer.Write("<Cell ss:StyleID="s21"><Data ss:Type="String">");
555
writer.Write(eachRow[currentRow].ToString());
556
writer.WriteLine("</Data></Cell>");
557
}
558
writer.WriteLine("</Row>");
559
}
560
writer.WriteLine(" </Table>");
561
writer.WriteLine(" <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");
562
writer.WriteLine(" <Selected/>");
563
writer.WriteLine(" <Panes>");
564
writer.WriteLine(" <Pane>");
565
writer.WriteLine(" <Number>3</Number>");
566
writer.WriteLine(" <ActiveRow>1</ActiveRow>");
567
writer.WriteLine(" </Pane>");
568
writer.WriteLine(" </Panes>");
569
writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
570
writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
571
writer.WriteLine(" </WorksheetOptions>");
572
writer.WriteLine(" </Worksheet>");
573
writer.WriteLine(" <Worksheet ss:Name="Sheet2">");
574
writer.WriteLine(" <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");
575
writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
576
writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
577
writer.WriteLine(" </WorksheetOptions>");
578
writer.WriteLine(" </Worksheet>");
579
writer.WriteLine(" <Worksheet ss:Name="Sheet3">");
580
writer.WriteLine(" <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");
581
writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
582
writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
583
writer.WriteLine(" </WorksheetOptions>");
584
writer.WriteLine(" </Worksheet>");
585
writer.WriteLine("</Workbook>");
586
writer.Close();
587
Response.Write("<script. language="javascript">" + "alert('" + "转换成功! 转换后的Excel文件名为: kfcccer.xls')" + "</script>");
588
}
589
catch (Exception ex)
590![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
591
Response.Write("<script. language="javascript">" + "alert('" + "操作失败! 出错信息: " + ex.Message + "')" + "</script>");
592
}
593
}
594
}
595![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
596
通过XML文件来实现的导入EXCEL的方法:
597![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
598
//fileName是要保存excel文件和文件名的路径
599![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
600
601![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
602
public static void exportToExcel(DataSet source, string fileName)
603![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
604![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
605![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
606
System.IO.StreamWriter excelDoc;
607![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
608
excelDoc = new System.IO.StreamWriter(fileName);
609
const string startExcelXML = "<xml version>\r\n<Workbook " +
610
"xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
611
" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
612
"xmlns:x=\"urn:schemas- microsoft-com:office:" +
613
"excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
614
"office:spreadsheet\">\r\n <Styles>\r\n " +
615
"<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
616
"<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
617
"\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
618
"\r\n <Protection/>\r\n </Style>\r\n " +
619
"<Style ss:ID=\"BoldColumn\">\r\n <Font " +
620
"x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +
621
"<Style ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
622
" ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
623
"ss:ID=\"Decimal\">\r\n <NumberFormat " +
624
"ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +
625
"<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
626
"ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
627
"ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
628
"ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +
629
"</Styles>\r\n ";
630
const string endExcelXML = "</Workbook>";
631![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
632
int rowCount = 0;
633
int sheetCount = 1;
634![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//*
635
<xml version>
636
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
637
xmlns:o="urn:schemas-microsoft-com:office:office"
638
xmlns:x="urn:schemas-microsoft-com:office:excel"
639
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
640
<Styles>
641
<Style ss:ID="Default" ss:Name="Normal">
642
<Alignment ss:Vertical="Bottom"/>
643
<Borders/>
644
<Font/>
645
<Interior/>
646
<NumberFormat/>
647
<Protection/>
648
</Style>
649
<Style ss:ID="BoldColumn">
650
<Font x:Family="Swiss" ss:Bold="1"/>
651
</Style>
652
<Style ss:ID="StringLiteral">
653
<NumberFormat ss:Format="@"/>
654
</Style>
655
<Style ss:ID="Decimal">
656
<NumberFormat ss:Format="0.0000"/>
657
</Style>
658
<Style ss:ID="Integer">
659
<NumberFormat ss:Format="0"/>
660
</Style>
661
<Style ss:ID="DateLiteral">
662
<NumberFormat ss:Format="mm/dd/yyyy;@"/>
663
</Style>
664
</Styles>
665
<Worksheet ss:Name="Sheet1">
666
</Worksheet>
667
</Workbook>
668
*/
669
excelDoc.Write(startExcelXML);
670
excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
671
excelDoc.Write("<Table>");
672
excelDoc.Write("<Row>");
673
for(int x = 0; x < source.Tables[0].Columns.Count; x++)
674![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
675
excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
676
excelDoc.Write(source.Tables[0].Columns[x].ColumnName);
677
excelDoc.Write("</Data></Cell>");
678
}
679
excelDoc.Write("</Row>");
680
foreach(DataRow x in source.Tables[0].Rows)
681![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
682
rowCount++;
683
//if the number of rows is > 64000 create a new page to continue output
684
if(rowCount==64000)
685![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
686
rowCount = 0;
687
sheetCount++;
688
excelDoc.Write("</Table>");
689
excelDoc.Write(" </Worksheet>");
690
excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
691
excelDoc.Write("<Table>");
692
}
693
excelDoc.Write("<Row>"); //ID=" + rowCount + "
694
for(int y = 0; y < source.Tables[0].Columns.Count; y++)
695![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
696
System.Type rowType;
697
rowType = x[y].GetType();
698
switch(rowType.ToString())
699![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
700
case "System.String":
701
string XMLstring = x[y].ToString();
702
XMLstring = XMLstring.Trim();
703
XMLstring = XMLstring.Replace("&","&");
704
XMLstring = XMLstring.Replace(">",">");
705
XMLstring = XMLstring.Replace("<","<");
706
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
707
"<Data ss:Type=\"String\">");
708
excelDoc.Write(XMLstring);
709
excelDoc.Write("</Data></Cell>");
710
break;
711
case "System.DateTime":
712
//Excel has a specific Date Format of YYYY-MM-DD followed by
713
//the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
714
//The Following Code puts the date stored in XMLDate
715
//to the format above
716
DateTime XMLDate = (DateTime)x[y];
717
string XMLDatetoString = ""; //Excel Converted Date
718
XMLDatetoString = XMLDate.Year.ToString() +
719
"-" +
720
(XMLDate.Month < 10 ? "0" +
721
XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
722
"-" +
723
(XMLDate.Day < 10 ? "0" +
724
XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
725
"T" +
726
(XMLDate.Hour < 10 ? "0" +
727
XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
728
":" +
729
(XMLDate.Minute < 10 ? "0" +
730
XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
731
":" +
732
(XMLDate.Second < 10 ? "0" +
733
XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
734
".000";
735
excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
736
"<Data ss:Type=\"DateTime\">");
737
excelDoc.Write(XMLDatetoString);
738
excelDoc.Write("</Data></Cell>");
739
break;
740
case "System.Boolean":
741
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
742
"<Data ss:Type=\"String\">");
743
excelDoc.Write(x[y].ToString());
744
excelDoc.Write("</Data></Cell>");
745
break;
746
case "System.Int16":
747
case "System.Int32":
748
case "System.Int64":
749
case "System.Byte":
750
excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
751
"<Data ss:Type=\"Number\">");
752
excelDoc.Write(x[y].ToString());
753
excelDoc.Write("</Data></Cell>");
754
break;
755
case "System.Decimal":
756
case "System.Double":
757
excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
758
"<Data ss:Type=\"Number\">");
759
excelDoc.Write(x[y].ToString());
760
excelDoc.Write("</Data></Cell>");
761
break;
762
case "System.DBNull":
763
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
764
"<Data ss:Type=\"String\">");
765
excelDoc.Write("");
766
excelDoc.Write("</Data></Cell>");
767
break;
768
default:
769
throw(new Exception(rowType.ToString() + " not handled."));
770
}
771
}
772
excelDoc.Write("</Row>");
773
}
774
excelDoc.Write("</Table>");
775
excelDoc.Write(" </Worksheet>");
776
excelDoc.Write(endExcelXML);
777
excelDoc.Close();
778
}
779![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
780![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
781![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
782![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
posted @
2008-09-21 13:26
Simmy.卧龙先生
阅读(
885)
评论()
编辑
收藏
举报