从数据库导入Excel的方法总结
要在ASP.Net中把数据库中的数据导入到Excel中有如下的几种方法:
一. RenderControl的方法
1、如果出现下面的错误提示可用重载VerifyRenderingInServerForm方法解决。
错误提示:
类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标记内
在后台文件中重载VerifyRenderingInServerForm方法,如:
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}
2、如果设置为 GetEncoding("GB2312"),导出的文件将会出现乱码。
可用Response.ContentEncoding = System.Text.Encoding.UTF7;
或者Encoding.UTF8等来解决,不过导入格式和字体上个人感觉UTF7比UTF8效果好些;
因人而异了:)
3. 在导出数据项前加上' '
导入Excel 时, 数字字符的数据会因Excel中的科学计数法, 导致数值过大的数据丢失后几位数.
二. 逐行写入法
private void ExportToExcel()
{
//设置文件格式为Excel
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Response.ContentType = "application/ms-excel";
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
//设置导出的文件名,要替换你自己的文件
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Response.AddHeader("Content-Disposition", "inline;filename=Filename.xls");
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
//写第一行的表格头(Grid的表头)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
for( int iCol=0; iCol< 列数; iCol++)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Response.Write( “表格头的内容” + Convert.ToChar(9));
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Response.Write("\n");
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
foreach (每行)
{
for( int iCol=0; iCol< 列数; iCol++)
Response.Write( “实际的数据,即Grid的内容” + Convert.ToChar(9));
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Response.Write("\n");
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
Response.Write("\n");
Response.End();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
一. RenderControl的方法
1
*
2
* // by XiaoYin [10/22/2006]
3
*/
4
using System;
5
using System.Data;
6
using System.Configuration;
7
using System.Web;
8
using System.Web.Security;
9
using System.Web.UI;
10
using System.Web.UI.WebControls;
11
using System.Web.UI.WebControls.WebParts;
12
using System.Web.UI.HtmlControls;
13
using System.Data.SqlClient;
14
using System.Xml;
15![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
public partial class _Default : System.Web.UI.Page
17
{
18![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
19
/**//// <summary>
20
/// 链接字符串
21
/// </summary>
22
public string ConnectString
23
{
24
get
25
{
26
return ConfigurationManager.AppSettings["ConnectionString"];
27
}
28
}
29![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
30
/**//// <summary>
31
/// 重载VerifyRenderingInServerForm方法
32
/// 确认在运行时为指定的 ASP.NET 服务器控件呈现 HtmlForm 控件。
33
/// </summary>
34
/// <param name="control">ASP.NET 服务器控件,它必须位于 HtmlForm 控件中</param>
35
public override void VerifyRenderingInServerForm(Control control)
36
{
37
//base.VerifyRenderingInServerForm(control);
38
}
39![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
40![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
41
protected void Page_Load(object sender, EventArgs e)
42
{
43
if (!Page.IsPostBack)
44
{
45
BindData();
46
}
47
}
48![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
49
/**//// <summary>
50
/// 绑定数据
51
/// </summary>
52
public void BindData()
53
{
54
// 查询
55
string query = "SELECT * FROM Categories";
56
SqlConnection myConnection = new SqlConnection(ConnectString);
57
SqlDataAdapter ad = new SqlDataAdapter(query, myConnection);
58
DataSet ds = new DataSet();
59
ad.Fill(ds, "Categories");
60
GridView1.DataSource = ds;
61
GridView1.DataBind();
62
}
63![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
64
/**//// <summary>
65
/// 内存分页
66
/// </summary>
67
/// <param name="sender"></param>
68
/// <param name="e"></param>
69
protected void Paging(object sender, GridViewPageEventArgs e)
70
{
71
GridView1.PageIndex = e.NewPageIndex;
72
BindData();
73
}
74![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
75
protected void Button1_Click(object sender, EventArgs e)
76
{
77
Response.Clear();
78
Response.Buffer = true;
79
Response.Charset = "GB2312";
80
Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
81
//gaoyang [10/21/2006] 经测试如果设置为 GetEncoding("GB2312"),导出的文件将会出现乱码。
82
Response.ContentEncoding = System.Text.Encoding.UTF7;
83![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
84
//设置输出文件类型为excel文件。
85
Response.ContentType = "application/ms-excel";
86
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
87
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
88
this.GridView1.RenderControl(oHtmlTextWriter);
89
Response.Output.Write(oStringWriter.ToString());
90
Response.Flush();
91
Response.End();
92
}
93
}
94![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
注意以下几点:![](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/ExpandedBlockStart.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.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/ExpandedSubBlockEnd.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.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/ExpandedSubBlockEnd.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.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/ExpandedSubBlockStart.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.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/ExpandedSubBlockEnd.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.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/ExpandedSubBlockEnd.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.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/ExpandedSubBlockEnd.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.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/ExpandedSubBlockEnd.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/ExpandedSubBlockStart.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/InBlock.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/InBlock.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/ExpandedSubBlockEnd.gif)
93
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
94
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
1、如果出现下面的错误提示可用重载VerifyRenderingInServerForm方法解决。
错误提示:
类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标记内
在后台文件中重载VerifyRenderingInServerForm方法,如:
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}
2、如果设置为 GetEncoding("GB2312"),导出的文件将会出现乱码。
可用Response.ContentEncoding = System.Text.Encoding.UTF7;
或者Encoding.UTF8等来解决,不过导入格式和字体上个人感觉UTF7比UTF8效果好些;
因人而异了:)
3. 在导出数据项前加上' '
导入Excel 时, 数字字符的数据会因Excel中的科学计数法, 导致数值过大的数据丢失后几位数.
二. 逐行写入法
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)