.net数据库实现Excel的导入与导出
1.default.aspx文件
View Code
1 <form id="form1" runat="server">
2 <table style="width: 858px">
3 <tr>
4 <td style="width: 334px">
5 <asp:Button ID="readFromDB" runat="server"
6
7 OnClick="readFromDB_Click" Text="从数据库读取数据" />
8 <asp:GridView ID="GridView1" runat="server"
9
10 AutoGenerateColumns="True" BackColor="White" BorderColor="#E7E7FF"
11
12 BorderStyle="None" BorderWidth="1px" CellPadding="3" Font-Names="Arial"
13
14 Font-Size="12px" GridLines="Horizontal" RowStyle-HorizontalAlign="Center"
15
16 Width="98%">
17 <FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
18
19 <RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C"
20
21 HorizontalAlign="Center" />
22 <SelectedRowStyle BackColor="#738A9C" Font-Bold="True"
23
24 ForeColor="#F7F7F7" />
25 <PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C"
26
27 HorizontalAlign="Right" />
28 <HeaderStyle BackColor="#4A3C8C" Font-Bold="True"
29
30 ForeColor="#F7F7F7" HorizontalAlign="Center" />
31 <AlternatingRowStyle BackColor="#F7F7F7" /> </asp:GridView>
32 <asp:FileUpload ID="FileUpload1" runat="server" />
33 <asp:Button ID="Import" runat="server" OnClick="Import_Click" Text="
34
35 导入" />
36 <asp:Button ID="output" runat="server" OnClick="btnOut_Click" Text="
37
38 导出" />
39 </td>
40 </tr>
41 </table>
42 </form>
2.default.aspx.cs文件
View Code
1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Collections;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.WebControls;
9 using System.Web.UI.WebControls.WebParts;
10 using System.Web.UI.HtmlControls;
11 using System.Data.SqlClient;
12 using System.Data.OleDb;
13 using System.Text;
14 using JJoobb.Web;
15 using System.IO;
16 public partial class _Default : System.Web.UI.Page
17 {
18 string strConn = SiteSetting.ConnectionString;
19 string sqlSelectALL = SiteSetting.sqlSelALL;
20 string exToDB = SiteSetting.ExToDB;
21 protected void Page_Load(object sender, EventArgs e)
22 {
23
24 }
25 protected void readFromDB_Click(object sender, EventArgs e)
26 {
27 Bind();
28 }
29 public void btnOut_Click(object sender, EventArgs e)
30 {
31 try
32 {
33 CreateExcel(getds());
34 Response.Write("<script>alert('数据导出成功!')</script>");
35 }
36 catch
37 {
38 Response.Write("<script>alert('数据导出失败!')</script>");
39 }
40 }
41 public void CreateExcel(DataSet ds)
42 {
43 string outPutPath=SiteSetting.OutPutPath +DateTime.Now.ToString
44
45 ("yyyyMMddHHmmss-")+SiteSetting.FileName;
46 FileStream file = new FileStream(outPutPath, FileMode.Create);
47 StreamWriter sw = new StreamWriter(file,Encoding.Default);//设置编码为当
48
49 面页面编码
50 string colHeaders = "", ls_item = "";
51 //定义表对象与行对象，同时用DataSet对其值进行初始化
52 DataTable dt = ds.Tables[0];
53 DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数
54
55 据筛选目的
56 int i = 0;
57 int cl = dt.Columns.Count;
58 //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车
59
60 符
61 for (i = 0; i < cl; i++)
62 {
63 if (i == (cl - 1))//最后一列，加\n
64 {
65 colHeaders += dt.Columns[i].Caption.ToString() + "\n";
66 }
67 else
68 {
69 colHeaders += dt.Columns[i].Caption.ToString() + "\t";
70 }
71 }
72 sw.Write(colHeaders);
73 foreach (DataRow row in myRow)
74 {
75 //当前行数据写入输出流，并且置空ls_item以便下行数据
76 for (i = 0; i < cl; i++)
77 {
78 if (i == (cl - 1))
79 {
80 ls_item += row[i].ToString() + "\n";
81 }
82 else
83 {
84 ls_item += row[i].ToString() + "\t";
85 }
86 }
87 sw.Write(ls_item);
88 ls_item = "";
89 }
90 sw.Flush();
91 sw.Close();
92 file.Close();
93 }
94
95 //从数据库取出要导出的Detset数据集
96 private DataSet getds()
97 {
98 SqlConnection conns = new SqlConnection(strConn);
99 SqlDataAdapter da = new SqlDataAdapter(sqlSelectALL, conns);
100 DataSet ds = new DataSet();
101 da.Fill(ds);
102 conns.Close();
103 conns.Dispose();
104 return ds;
105 }
106 //绑定数据
107 public void Bind()
108 {
109 DataSet ds = new DataSet();
110 using (SqlConnection conn = new SqlConnection())
111 {
112 SqlDataAdapter sda = new SqlDataAdapter(sqlSelectALL, strConn);
113 sda.Fill(ds, "ex_test");
114 }
115 GridView1.DataSource = ds.Tables["ex_test"];
116 GridView1.DataBind();
117 }
118 //导入数据
119 protected void Import_Click(object sender, EventArgs e)
120 {
121 string getErrMsg = "";
122 DataSet excelDs = new DataSet();
123 if (FileUpload1.PostedFile.FileName == "")
124 {
125 Response.Write("<script language=javascript>alert('请选择要上传的文件
126
127 ！');</script>");
128 return;
129 }
130 //从Excel读取数据
131 string filePath = FileUpload1.PostedFile.FileName;
132 string connString = exToDB + filePath;
133 OleDbConnection excelConn = new OleDbConnection(connString);
134 OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM
135
136 [Sheet1$]", excelConn);
137 try
138 {
139 ExcelDA.Fill(excelDs, "ex_test");
140 }
141 catch (Exception err)
142 {
143 Response.Write(err.Message);
144 }
145 finally
146 {
147 excelConn.Close();
148 excelConn = null;
149 }
150 //将数据写入数据库
151 if (excelDs.Tables[0].Rows.Count != 0)
152 {
153 SqlConnection sqlConn = new SqlConnection(strConn);
154 sqlConn.Open();
155 SqlCommand myCommand = sqlConn.CreateCommand();
156 SqlTransaction myTrans = sqlConn.BeginTransaction();
157 myCommand.Transaction = myTrans;
158 try
159 {
160 for (int i = 0; i < excelDs.Tables[0].Rows.Count; i++)
161 {
162 string sql = "insert into ex_test(id, name, sex,email,address) values('"
163
164 + excelDs.Tables[0].Rows[i]["编号"].ToString() + "','" + excelDs.Tables[0].Rows[i]["
165
166 姓名"].ToString() + "','" + excelDs.Tables[0].Rows[i]["性别"].ToString() + "','" +
167
168 excelDs.Tables[0].Rows[i]["邮箱"].ToString() + "','" + excelDs.Tables[0].Rows[i]["地
169
170 址"].ToString() + "')";
171 myCommand.CommandText = sql; myCommand.ExecuteNonQuery();
172 }
173 myTrans.Commit();
174 }
175 catch (Exception ex)
176 {
177 getErrMsg = ex.Message.ToString();
178 Response.Write(ex.Message.ToString());
179 myTrans.Rollback();
180 }
181 finally
182 {
183 sqlConn.Close();
184 sqlConn = null;
185 }
186
187 }
188 //返回提示信息
189 if (getErrMsg == "" || getErrMsg == null)
190 {
191 Response.Write("<script language='Javascript'>alert('导入成功！')
192
193 </script>");
194 Bind();
195 }
196 else
197 {
198 Response.Write("<script language='Javascript'>alert('导入失败！')
199
200 </script>");
201 return;
202 }
203 }
204
205 }
3.SiteSetting.cs
View Code
1 using System;
2 using System.Configuration;
3 using System.Collections.Generic;
4 using System.Text;
5
6 /// <summary>
7 /// SiteSetting 的摘要说明
8 /// </summary>
9 namespace JJoobb.Web
10 {
11 public class SiteSetting
12 {
13 //数据库连接字符串
14 public static string ConnectionString =
15
16 ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
17
18 //导出文件名称
19 public static string FileName = "OutPut.xls";
20
21 //导出文件地址
22 public static string OutPutPath = "C:\\Documents and
23
24 Settings\\Administrator\\桌面\\excleFile\\";
25
26 //sql语句
27 public static string sqlSelALL = "select
28
29 id,title,contents,userName,phone,email,CONVERT(varchar(100), AddDate, 20)
30
31 AddDate,ReContents from Feedback";
32
33 //从excel读数据
34 public static string ExToDB = "Provider=Microsoft.Jet.OLEDB.4.0;Extended
35
36 Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";Data Source=";
37 }
38 }
4.web.config
View Code
<connectionStrings>
<add name="connectionString"
connectionString="server=local;uid=sa;pwd=pwd;database=TestExcel"
providerName="System.Data.SqlClient"/>
</connectionStrings>
