把asp.net中的数据直接导入至excel表中的例子。
1.web.config中加入
<identity impersonate="true"/>
2.用Microsoft.Excel 11.0 Object Library COM组件
3.下面代码。
1.web.config中加入
<identity impersonate="true"/>
2.用Microsoft.Excel 11.0 Object Library COM组件
3.下面代码。
1
using System;
2
using System.Collections;
3
using System.ComponentModel;
4
using System.Data;
5
using System.Drawing;
6
using System.Web;
7
using System.Web.SessionState;
8
using System.Web.UI;
9
using System.Web.UI.WebControls;
10
using System.Web.UI.HtmlControls;
11
using System.Data.SqlClient;
12
using System.Configuration;
13
using System.Runtime.InteropServices;
14
using Excel;
15
using System.Reflection;
16![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
namespace CSharpNET_ExcelTest
18
{
19
/// <summary>
20
/// WebForm1 的摘要说明。
21
/// </summary>
22
public class WebForm1 : System.Web.UI.Page
23
{
24
private void Page_Load(object sender, System.EventArgs e)
25
{
26
Excel.Application oExcel=new Excel.Application();
27
Excel.Workbooks oBooks;
28
Excel.Workbook oBook;
29
Excel.Sheets oSheets;
30
Excel.Worksheet oSheet;
31
Excel.Range oCells;
32
string sFile,sTemplate;
33
DataSet objDataset=new DataSet();
34![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
35
System.Data.DataTable dt = ((System.Data.DataTable)(CreateDataSource().Tables[0]));
36![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
37
sFile=Server.MapPath(Request.ApplicationPath)+@"\MyExcel.xls";
38
sTemplate=Server.MapPath(Request.ApplicationPath) +@"\MyTemplate.xls";
39
oExcel.DisplayAlerts=false;
40
oBooks=oExcel.Workbooks;
41
42
try
43
{
44
// 在VB.net中一般用open
45
oBooks.Add(Server.MapPath(Request.ApplicationPath) + @"\MyTemplate.xls") ;
46
}
47
catch
48
{
49
}
50![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
51
oBook=oBooks[1];
52
oSheets=oBook.Worksheets;
53
oSheet=(Excel.Worksheet) oSheets[1];
54![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
55
oBook = oBooks[1];
56
oSheet.Name="First Sheet";
57
oCells=oSheet.Cells;
58
DumpData(dt,oCells);
59![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
60
// 注意此处为10个参数
61
oSheet.SaveAs(sFile,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
62
Excel.XlSaveAsAccessMode.xlExclusive,Missing.Value,Missing.Value,
63
Missing.Value);
64![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
65
oBook.Close(Missing.Value,Missing.Value,Missing.Value);
66![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
67
oExcel.Quit();
68
Marshal.ReleaseComObject(oCells);
69
Marshal.ReleaseComObject(oSheet);
70
71
Marshal.ReleaseComObject(oSheets) ;
72
Marshal.ReleaseComObject(oBook);
73
Marshal.ReleaseComObject(oBooks);
74
Marshal.ReleaseComObject(oExcel);
75
76
oExcel = null;
77
oBooks = null;
78
oBook = null;
79
80
oSheets = null;
81
oSheet = null ;
82
oCells = null;
83
System.GC.Collect();
84
Response.Redirect(sFile);
85![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
86
}
87![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
88
private void DumpData(System.Data.DataTable dt,Excel.Range oCells)
89
{
90
int iCol;
91
int iRow;
92
DataRow dr;
93
object[] ary;
94
for (iCol=0;iCol<dt.Columns.Count;iCol++)
95
{
96
oCells[2,iCol+1]=dt.Columns[iCol].ToString();
97
}
98![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
99
for (iRow=0 ;iRow<dt.Rows.Count;iRow++)
100
{
101
dr=dt.Rows[iRow];
102
ary=dr.ItemArray;
103
for(iCol=0 ;iCol<ary.GetUpperBound(0);iCol++)
104
{
105
oCells[iRow+3,iCol+1]=ary[iCol].ToString();
106
Response.Write(ary[iCol].ToString()+"\t");
107
}
108
}
109
}
110![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
111
public System.Data.DataSet CreateDataSource()
112
{
113
System.Data.DataSet ds = new System.Data.DataSet();
114
System.Data.DataTable dt = new System.Data.DataTable();
115
System.Data.DataRow dr;
116
dt.Columns.Add(new DataColumn("身份证号码", typeof(string)));
117
dt.Columns.Add(new DataColumn("图书单价", typeof(float)));
118
dt.Columns.Add(new DataColumn("购买数量", typeof(Int32)));
119
dt.Columns.Add(new DataColumn("总价格", typeof(float)));
120
for (Int32 i = 0; i <= 10; i++)
121
{
122
dr = dt.NewRow();
123
dr[0] = "123456789123456789";
124
dr[1] = 100 * i / 3;
125
dr[2] = i + 5;
126
dr[3] = (float)dr[1] * (Int32) dr[2];
127
dt.Rows.Add(dr);
128
}
129
ds.Tables.Add(dt);
130
return ds;
131
}
132![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
133![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
134
#region Web 窗体设计器生成的代码
135
override protected void OnInit(EventArgs e)
136
{
137
//
138
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
139
//
140
InitializeComponent();
141
base.OnInit(e);
142
}
143
144
/// <summary>
145
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
146
/// 此方法的内容。
147
/// </summary>
148
private void InitializeComponent()
149
{
150
this.Load += new System.EventHandler(this.Page_Load);
151
}
152
#endregion
153
}
154
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
71
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
72
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
73
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
74
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
75
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
76
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
77
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
78
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
79
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
80
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
81
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
82
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
83
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
84
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
85
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
86
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
87
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
88
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
89
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
90
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
91
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
92
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
93
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
94
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
95
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
96
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
97
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
98
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
99
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
100
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
101
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
102
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
103
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
104
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
105
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
106
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
107
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
108
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
109
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
110
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
111
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
112
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
113
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
114
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
115
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
116
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
117
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
118
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
119
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
120
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
121
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
122
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
123
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
124
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
125
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
126
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
127
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
128
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
129
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
130
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
131
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
132
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
133
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
134
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
135
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
136
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
137
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
138
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
139
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
140
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
141
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
142
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
143
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
144
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
145
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
146
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
147
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
148
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
149
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
150
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
151
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
152
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
153
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
154
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)