常用到的一个数据库导入功能，这样的话就省了很大一部分时间来处理程序上的问题而不是无休止的重复复制粘贴动作。
其他的废话不多说，直接上代码：
前提条件：
根目录下建立uploadfiles文件夹（用于保存上传的xls文件）；
aspx部分：
添加uploadfile和button控件即可。
aspx 部分代码：
复制代码
1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
2
3 <html xmlns="http://www.w3.org/1999/xhtml">
4 <head runat="server">
5 <title></title>
6 </head>
7 <body>
8 <form id="form1" runat="server">
9 <div>
10 <asp:FileUpload ID="FileUpload1" runat="server" />
11 <asp:Button ID="Button1" runat="server" Text="确定上传" onclick="Button1_Click" />
12 </div>
13 </form>
14 </body>
15 </html>
复制代码
cs 部分：
复制代码
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Web;
5 using System.Web.UI;
6 using System.Web.UI.WebControls;
7 using System.Data;
8 using System.Data.OleDb;
9 using System.IO; 10
11 namespace ImportExcel
12 {
13 public partial class _Default : System.Web.UI.Page
14 {
15 protected void Page_Load(object sender, EventArgs e)
16 {
17
18 }
19
20 #region 问题：未在本地计算机上注册Microsoft.ACE.OLEDB.12.0提供程序
21 // 解决访问Excel数据源时出现 未在本地计算机上注册Microsoft.ACE.OLEDB.12.0提供程序
22 // 1、确保安装了Microsoft.ACE.OLEDB.12.0驱动
23 // http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe
24 // 2、在vs中右击项目--》属性--》生成 下的 目标平台 改为x86
25 // 如果以上两个方法还是不行的话，用第三个方法
26 // 3、在对应的 IIS 应用程序池中，“设置应用程序池默认属性”右击/“高级设置”/"启用32位应用程序"，设置为 true。
27 #endregion
28
29 public System.Data.DataTable GetExcelDatatable(string fileUrl)
30 {
31 //支持.xls和.xlsx，即包括office2010等版本的 HDR=Yes代表第一行是标题，不是数据；
32 string cmdText = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
33 System.Data.DataTable dt = null;
34 //建立连接
35 OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));
36 try
37 {
38 //打开连接
39 if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
40 {
41 conn.Open();
42 }
43
44 System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
45 string strSql = "select * from [Sheet1$]";
46 OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
47 DataSet ds = new DataSet();
48 da.Fill(ds);
49 dt = ds.Tables[0];
50 return dt;
51 }
52 catch (Exception exc)
53 {
54 throw exc;
55 }
56 finally
57 {
58 conn.Close();
59 conn.Dispose();
60 }
61 }
62
63 protected void Button1_Click(object sender, EventArgs e)
64 {
65 if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
66 {
67 Response.Write("<script>alert('请您选择Excel文件')</script> ");
68 return;//当无文件时,返回
69 }
70 string IsXls = Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
71 if (IsXls != ".xlsx" || IsXls != ".xls")
72 {
73 Response.Write("<script>alert('只可以选择Excel文件')</script>");
74 return;//当选择的不是Excel文件时,返回
75 }
76 string filename = FileUpload1.FileName; //获取Execle文件名 DateTime日期函数
77 string savePath = Server.MapPath(("uploadfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
78 DataTable ds = new DataTable();
79 FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
80 ds = GetExcelDatatable(savePath); //调用自定义方法
81 DataRow[] dr = ds.Select(); //定义一个DataRow数组
82 int rowsnum = ds.Rows.Count;
83 int successly = 0;
84 if (rowsnum == 0)
85 {
86 Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
87 }
88 else
89 {
90 string _Result = "";
91 for (int i = 0; i < dr.Length; i++)
92 {
93 //前面除了你需要在建立一个“upfiles”的文件夹外，其他的都不用管了，你只需要通过下面的方式获取Excel的值，然后再将这些值用你的方式去插入到数据库里面
94 string title = dr[i]["标题"].ToString();
95 string linkurl = dr[i]["链接地址"].ToString();
96 string categoryname = dr[i]["分类"].ToString();
97 string customername = dr[i]["内容摘要"].ToString();
98
99 try
100 {
101 var uuid = Guid.NewGuid().ToString();
102 string sql = string.Format("insert into testdb(id,title,linkurl,categoryname,customername) values('{0}','{1}','{2}','{3}','{4}')", uuid, title, linkurl, categoryname, customername);
103 int count = SqlDbHelper.ExecuteNonQuery(sql);
104 if (count > 0)
105 successly++;
106
107 }
108 catch (Exception ex)
109 {
110 _Result = _Result + ex.InnerException + "\\n\\r";
111 }
112 }
113 if (successly == rowsnum)
114 {
115 string strmsg = "Excle表导入成功!";
116 System.Web.HttpContext.Current.Response.Write("<Script Language='JavaScript'>window.alert('" + strmsg + "');</script>");
117 }
118 else
119 {
120 Response.Write("<script>alert('Excle表导入失败!');</script>");
121 }
122 }
123 }
124 }
125 }
asp.net 项目基本上都是有数据库服务支持的，这就需要有一个比较常用的类支持文件。闲话不多说，直接上代码
复制代码
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Web;
5 using System.Data.SqlClient;
6 using System.Data;
7 using System.Configuration;
8
9 namespace ImportExcel
10 {
11 public class SqlDbHelper
12 {
13 /// <summary>
14 /// 连接字符串
15 /// </summary>
16 public static readonly string connectionString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
17
18 #region ExecuteNonQuery命令
19 /// <summary>
20 /// 对数据库执行增、删、改命令
21 /// </summary>
22 /// <param name="safeSql">T-Sql语句</param>
23 /// <returns>受影响的记录数</returns>
24 public static int ExecuteNonQuery(string safeSql)
25 {
26 using (SqlConnection Connection = new SqlConnection(connectionString))
27 {
28 Connection.Open();
29 SqlTransaction trans = Connection.BeginTransaction();
30 try
31 {
32 SqlCommand cmd = new SqlCommand(safeSql, Connection);
33 cmd.Transaction = trans;
34
35 if (Connection.State != ConnectionState.Open)
36 {
37 Connection.Open();
38 }
39 int result = cmd.ExecuteNonQuery();
40 trans.Commit();
41 return result;
42 }
43 catch
44 {
45 trans.Rollback();
46 return 0;
47 }
48 }
49 }
50
51 /// <summary>
52 /// 对数据库执行增、删、改命令
53 /// </summary>
54 /// <param name="sql">T-Sql语句</param>
55 /// <param name="values">参数数组</param>
56 /// <returns>受影响的记录数</returns>
57 public static int ExecuteNonQuery(string sql, SqlParameter[] values)
58 {
59 using (SqlConnection Connection = new SqlConnection(connectionString))
60 {
61 Connection.Open();
62 SqlTransaction trans = Connection.BeginTransaction();
63 try
64 {
65 SqlCommand cmd = new SqlCommand(sql, Connection);
66 cmd.Transaction = trans;
67 cmd.Parameters.AddRange(values);
68 if (Connection.State != ConnectionState.Open)
69 {
70 Connection.Open();
71 }
72 int result = cmd.ExecuteNonQuery();
73 trans.Commit();
74 return result;
75 }
76 catch (Exception ex)
77 {
78 trans.Rollback();
79 return 0;
80 }
81 }
82 }
83 #endregion
84
85 #region ExecuteScalar命令
86 /// <summary>
87 /// 查询结果集中第一行第一列的值
88 /// </summary>
89 /// <param name="safeSql">T-Sql语句</param>
90 /// <returns>第一行第一列的值</returns>
91 public static int ExecuteScalar(string safeSql)
92 {
93 using (SqlConnection Connection = new SqlConnection(connectionString))
94 {
95 if (Connection.State != ConnectionState.Open)
96 Connection.Open();
97 SqlCommand cmd = new SqlCommand(safeSql, Connection);
98 int result = Convert.ToInt32(cmd.ExecuteScalar());
99 return result;
100 }
101 }
102
103 /// <summary>
104 /// 查询结果集中第一行第一列的值
105 /// </summary>
106 /// <param name="sql">T-Sql语句</param>
107 /// <param name="values">参数数组</param>
108 /// <returns>第一行第一列的值</returns>
109 public static int ExecuteScalar(string sql, SqlParameter[] values)
110 {
111 using (SqlConnection Connection = new SqlConnection(connectionString))
112 {
113 if (Connection.State != ConnectionState.Open)
114 Connection.Open();
115 SqlCommand cmd = new SqlCommand(sql, Connection);
116 cmd.Parameters.AddRange(values);
117 int result = Convert.ToInt32(cmd.ExecuteScalar());
118 return result;
119 }
120 }
121 #endregion
122
123 #region ExecuteReader命令
124 /// <summary>
125 /// 创建数据读取器
126 /// </summary>
127 /// <param name="safeSql">T-Sql语句</param>
128 /// <param name="Connection">数据库连接</param>
129 /// <returns>数据读取器对象</returns>
130 public static SqlDataReader ExecuteReader(string safeSql, SqlConnection Connection)
131 {
132 if (Connection.State != ConnectionState.Open)
133 Connection.Open();
134 SqlCommand cmd = new SqlCommand(safeSql, Connection);
135 SqlDataReader reader = cmd.ExecuteReader();
136 return reader;
137 }
138
139 /// <summary>
140 /// 创建数据读取器
141 /// </summary>
142 /// <param name="sql">T-Sql语句</param>
143 /// <param name="values">参数数组</param>
144 /// <param name="Connection">数据库连接</param>
145 /// <returns>数据读取器</returns>
146 public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values, SqlConnection Connection)
147 {
148 if (Connection.State != ConnectionState.Open)
149 Connection.Open();
150 SqlCommand cmd = new SqlCommand(sql, Connection);
151 cmd.Parameters.AddRange(values);
152 SqlDataReader reader = cmd.ExecuteReader();
153 return reader;
154 }
155 #endregion
156
157 #region ExecuteDataTable命令
158 /// <summary>
159 /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable
160 /// </summary>
161 /// <param name="type">命令类型(T-Sql语句或者存储过程)</param>
162 /// <param name="safeSql">T-Sql语句或者存储过程的名称</param>
163 /// <param name="values">参数数组</param>
164 /// <returns>结果集DataTable</returns>
165 public static DataTable ExecuteDataTable(CommandType type, string safeSql, params SqlParameter[] values)
166 {
167 using (SqlConnection Connection = new SqlConnection(connectionString))
168 {
169 if (Connection.State != ConnectionState.Open)
170 Connection.Open();
171 DataSet ds = new DataSet();
172 SqlCommand cmd = new SqlCommand(safeSql, Connection);
173 cmd.CommandType = type;
174 SqlDataAdapter da = new SqlDataAdapter(cmd);
175 da.Fill(ds);
176 return ds.Tables[0];
177 }
178 }
179
180 /// <summary>
181 /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable
182 /// </summary>
183 /// <param name="safeSql">T-Sql语句</param>
184 /// <returns>结果集DataTable</returns>
185 public static DataTable ExecuteDataTable(string safeSql)
186 {
187 using (SqlConnection Connection = new SqlConnection(connectionString))
188 {
189 if (Connection.State != ConnectionState.Open)
190 Connection.Open();
191 DataSet ds = new DataSet();
192 SqlCommand cmd = new SqlCommand(safeSql, Connection);
193 SqlDataAdapter da = new SqlDataAdapter(cmd);
194 try
195 {
196 da.Fill(ds);
197 }
198 catch (Exception ex)
199 {
200
201 }
202 return ds.Tables[0];
203 }
204 }
205
206 /// <summary>
207 /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable
208 /// </summary>
209 /// <param name="sql">T-Sql语句</param>
210 /// <param name="values">参数数组</param>
211 /// <returns>结果集DataTable</returns>
212 public static DataTable ExecuteDataTable(string sql, params SqlParameter[] values)
213 {
214 using (SqlConnection Connection = new SqlConnection(connectionString))
215 {
216 if (Connection.State != ConnectionState.Open)
217 Connection.Open();
218 DataSet ds = new DataSet();
219 SqlCommand cmd = new SqlCommand(sql, Connection);
220 cmd.CommandTimeout = 0;
221 cmd.Parameters.AddRange(values);
222 SqlDataAdapter da = new SqlDataAdapter(cmd);
223 da.Fill(ds);
224 return ds.Tables[0];
225 }
226 }
227 #endregion
228
229 #region GetDataSet命令
230 /// <summary>
231 /// 取出数据
232 /// </summary>
233 /// <param name="safeSql">sql语句</param>
234 /// <param name="tabName">DataTable别名</param>
235 /// <param name="values"></param>
236 /// <returns></returns>
237 public static DataSet GetDataSet(string safeSql, string tabName, params SqlParameter[] values)
238 {
239 using (SqlConnection Connection = new SqlConnection(connectionString))
240 {
241 if (Connection.State != ConnectionState.Open)
242 Connection.Open();
243 DataSet ds = new DataSet();
244 SqlCommand cmd = new SqlCommand(safeSql, Connection);
245
246 if (values != null)
247 cmd.Parameters.AddRange(values);
248
249 SqlDataAdapter da = new SqlDataAdapter(cmd);
250 try
251 {
252 da.Fill(ds, tabName);
253 }
254 catch (Exception ex)
255 {
256
257 }
258 return ds;
259 }
260 }
261 #endregion
262
263 #region ExecureData 命令
264 /// <summary>
265 /// 批量修改数据
266 /// </summary>
267 /// <param name="ds">修改过的DataSet</param>
268 /// <param name="strTblName">表名</param>
269 /// <returns></returns>
270 public static int ExecureData(DataSet ds, string strTblName)
271 {
272 try
273 {
274 //创建一个数据库连接
275 using (SqlConnection Connection = new SqlConnection(connectionString))
276 {
277 if (Connection.State != ConnectionState.Open)
278 Connection.Open();
279
280 //创建一个用于填充DataSet的对象
281 SqlCommand myCommand = new SqlCommand("SELECT * FROM " + strTblName, Connection);
282 SqlDataAdapter myAdapter = new SqlDataAdapter();
283 //获取SQL语句，用于在数据库中选择记录
284 myAdapter.SelectCommand = myCommand;
285
286 //自动生成单表命令，用于将对DataSet所做的更改与数据库更改相对应
287 SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
288
289 return myAdapter.Update(ds, strTblName); //更新ds数据
290 }
291
292 }
293 catch (Exception err)
294 {
295 throw err;
296 }
297 }
298
299 #endregion
300 }
301 }
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。