ASP.NET中数据导入至Excel
把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![](/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![](/Images/OutliningIndicators/InBlock.gif)
35
System.Data.DataTable dt = ((System.Data.DataTable)(CreateDataSource().Tables[0]));
36![](/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![](/Images/OutliningIndicators/InBlock.gif)
51
oBook=oBooks[1];
52
oSheets=oBook.Worksheets;
53
oSheet=(Excel.Worksheet) oSheets[1];
54![](/Images/OutliningIndicators/InBlock.gif)
55
oBook = oBooks[1];
56
oSheet.Name="First Sheet";
57
oCells=oSheet.Cells;
58
DumpData(dt,oCells);
59![](/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![](/Images/OutliningIndicators/InBlock.gif)
65
oBook.Close(Missing.Value,Missing.Value,Missing.Value);
66![](/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![](/Images/OutliningIndicators/InBlock.gif)
86
}
87![](/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![](/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![](/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![](/Images/OutliningIndicators/InBlock.gif)
133![](/Images/OutliningIndicators/InBlock.gif)
134
Web 窗体设计器生成的代码
153
}
154
}
155![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
19
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
20
![](/Images/OutliningIndicators/InBlock.gif)
21
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
22
![](/Images/OutliningIndicators/InBlock.gif)
23
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
24
![](/Images/OutliningIndicators/InBlock.gif)
25
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
26
![](/Images/OutliningIndicators/InBlock.gif)
27
![](/Images/OutliningIndicators/InBlock.gif)
28
![](/Images/OutliningIndicators/InBlock.gif)
29
![](/Images/OutliningIndicators/InBlock.gif)
30
![](/Images/OutliningIndicators/InBlock.gif)
31
![](/Images/OutliningIndicators/InBlock.gif)
32
![](/Images/OutliningIndicators/InBlock.gif)
33
![](/Images/OutliningIndicators/InBlock.gif)
34
![](/Images/OutliningIndicators/InBlock.gif)
35
![](/Images/OutliningIndicators/InBlock.gif)
36
![](/Images/OutliningIndicators/InBlock.gif)
37
![](/Images/OutliningIndicators/InBlock.gif)
38
![](/Images/OutliningIndicators/InBlock.gif)
39
![](/Images/OutliningIndicators/InBlock.gif)
40
![](/Images/OutliningIndicators/InBlock.gif)
41
![](/Images/OutliningIndicators/InBlock.gif)
42
![](/Images/OutliningIndicators/InBlock.gif)
43
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
44
![](/Images/OutliningIndicators/InBlock.gif)
45
![](/Images/OutliningIndicators/InBlock.gif)
46
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
47
![](/Images/OutliningIndicators/InBlock.gif)
48
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
49
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
50
![](/Images/OutliningIndicators/InBlock.gif)
51
![](/Images/OutliningIndicators/InBlock.gif)
52
![](/Images/OutliningIndicators/InBlock.gif)
53
![](/Images/OutliningIndicators/InBlock.gif)
54
![](/Images/OutliningIndicators/InBlock.gif)
55
![](/Images/OutliningIndicators/InBlock.gif)
56
![](/Images/OutliningIndicators/InBlock.gif)
57
![](/Images/OutliningIndicators/InBlock.gif)
58
![](/Images/OutliningIndicators/InBlock.gif)
59
![](/Images/OutliningIndicators/InBlock.gif)
60
![](/Images/OutliningIndicators/InBlock.gif)
61
![](/Images/OutliningIndicators/InBlock.gif)
62
![](/Images/OutliningIndicators/InBlock.gif)
63
![](/Images/OutliningIndicators/InBlock.gif)
64
![](/Images/OutliningIndicators/InBlock.gif)
65
![](/Images/OutliningIndicators/InBlock.gif)
66
![](/Images/OutliningIndicators/InBlock.gif)
67
![](/Images/OutliningIndicators/InBlock.gif)
68
![](/Images/OutliningIndicators/InBlock.gif)
69
![](/Images/OutliningIndicators/InBlock.gif)
70
![](/Images/OutliningIndicators/InBlock.gif)
71
![](/Images/OutliningIndicators/InBlock.gif)
72
![](/Images/OutliningIndicators/InBlock.gif)
73
![](/Images/OutliningIndicators/InBlock.gif)
74
![](/Images/OutliningIndicators/InBlock.gif)
75
![](/Images/OutliningIndicators/InBlock.gif)
76
![](/Images/OutliningIndicators/InBlock.gif)
77
![](/Images/OutliningIndicators/InBlock.gif)
78
![](/Images/OutliningIndicators/InBlock.gif)
79
![](/Images/OutliningIndicators/InBlock.gif)
80
![](/Images/OutliningIndicators/InBlock.gif)
81
![](/Images/OutliningIndicators/InBlock.gif)
82
![](/Images/OutliningIndicators/InBlock.gif)
83
![](/Images/OutliningIndicators/InBlock.gif)
84
![](/Images/OutliningIndicators/InBlock.gif)
85
![](/Images/OutliningIndicators/InBlock.gif)
86
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
87
![](/Images/OutliningIndicators/InBlock.gif)
88
![](/Images/OutliningIndicators/InBlock.gif)
89
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
90
![](/Images/OutliningIndicators/InBlock.gif)
91
![](/Images/OutliningIndicators/InBlock.gif)
92
![](/Images/OutliningIndicators/InBlock.gif)
93
![](/Images/OutliningIndicators/InBlock.gif)
94
![](/Images/OutliningIndicators/InBlock.gif)
95
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
96
![](/Images/OutliningIndicators/InBlock.gif)
97
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
98
![](/Images/OutliningIndicators/InBlock.gif)
99
![](/Images/OutliningIndicators/InBlock.gif)
100
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
101
![](/Images/OutliningIndicators/InBlock.gif)
102
![](/Images/OutliningIndicators/InBlock.gif)
103
![](/Images/OutliningIndicators/InBlock.gif)
104
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
105
![](/Images/OutliningIndicators/InBlock.gif)
106
![](/Images/OutliningIndicators/InBlock.gif)
107
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
108
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
109
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
110
![](/Images/OutliningIndicators/InBlock.gif)
111
![](/Images/OutliningIndicators/InBlock.gif)
112
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
113
![](/Images/OutliningIndicators/InBlock.gif)
114
![](/Images/OutliningIndicators/InBlock.gif)
115
![](/Images/OutliningIndicators/InBlock.gif)
116
![](/Images/OutliningIndicators/InBlock.gif)
117
![](/Images/OutliningIndicators/InBlock.gif)
118
![](/Images/OutliningIndicators/InBlock.gif)
119
![](/Images/OutliningIndicators/InBlock.gif)
120
![](/Images/OutliningIndicators/InBlock.gif)
121
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
122
![](/Images/OutliningIndicators/InBlock.gif)
123
![](/Images/OutliningIndicators/InBlock.gif)
124
![](/Images/OutliningIndicators/InBlock.gif)
125
![](/Images/OutliningIndicators/InBlock.gif)
126
![](/Images/OutliningIndicators/InBlock.gif)
127
![](/Images/OutliningIndicators/InBlock.gif)
128
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
129
![](/Images/OutliningIndicators/InBlock.gif)
130
![](/Images/OutliningIndicators/InBlock.gif)
131
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
132
![](/Images/OutliningIndicators/InBlock.gif)
133
![](/Images/OutliningIndicators/InBlock.gif)
134
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
153
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
154
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
155
![](/Images/OutliningIndicators/None.gif)