一个简单的excel导入导出的例子
1 新建个网站：
2 页面html如下：
<head runat="server"> <title>无标题页</title></head><body> <form id="form1" runat="server"> <div> <asp:FileUpload ID="FileUpload1" runat="server" /> <asp:Label ID="Label1" runat="server"></asp:Label> <br /> <asp:Button ID="btnIn" runat="server" οnclick="btnIn_Click" Text="上传并导入" /> </div> </form> <p> 点击此处下载模版<a href="学生.xls">学生.xls</a></p></body></html>
3.页面后台代码如下：
using System;using System.Configuration;using System.Data;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;using System.Web.UI.MobileControls;using System.Collections;
public partial class _Default : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) {
} protected void btnIn_Click(object sender, EventArgs e) { #region//判断 string filename; if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件 { Response.Write("<script>alert('请您选择Excel文件!')</script>"); return; } string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名 if (IsXls != ".xls") { Response.Write("<script>alert('只可以选择Excel文件!')</script>"); return;//当选择的不是Excel文件时,返回 } filename = FileUpload1.FileName; //获取Execle文件名 ViewState["filename"] = filename; string path = this.FileUpload1.PostedFile.FileName; //获取文件的绝对路径 注意：如果获取到的只是文件名的话原因是ie浏览器安全限制，解决方法 “工具-Internet选项-安全-自定义级别-启用‘将文件上载到服务器时包含本地路径’ 即可，或者把安全级别调小也可 if (path == filename) { Response.Write("<script>alert('出错啦！原因:获取不到文件的绝对路径，导致附加不成功。解决办法：打开IE浏览器，依次单击“工具-Internet选项-安全-自定义级别-启‘将文件上载到服务器时包含本地路径’” 即可,或者依次单击“工具-Internet选项-安全-自定义级别-‘默认级别’或者更低”')</script>"); return; } Label1.Text = "当前操作EXCEL文件为：" + filename; Label1.Visible = true;
string savePath = Server.MapPath("ExcelModle/");//设置保存文件夹 if (!System.IO.Directory.Exists(savePath))//如果不存在则创建文件夹 { System.IO.Directory.CreateDirectory(savePath); } FileUpload1.SaveAs(savePath + filename);//上传并保存excel文件 #endregion #region 读取excel ExcelHelper bllExcel = new ExcelHelper(); DataSet ds = bllExcel.CheckExecleDs(savePath + filename, filename); //调用自定义方法读取excel 返回 DataSet if (ds != null && ds.Tables[0].Rows.Count > 0) {
string[] list=new string[2] ; for (int i = 0; i <ds.Tables[0].Columns.Count; i++) { string columnName = ds.Tables[0].Columns[i].ColumnName;//获取列名 if (columnName == "姓名") { list[0] = columnName;//保存列名 } if (columnName == "联系电话") { list[1] = columnName;//保存列名 }
} foreach (DataRow row in ds.Tables[0].Rows) { //循环取出行数据 并添加到数据库 //for(int i=0;i<=list.Count;i++){ string name = row[list[0].ToString()].ToString(); string tell = row[list[1].ToString()].ToString(); string sql = "insert into users (user_name,user_tell) values('"+name+"','"+tell+"')"; DB mydb = new DB(); if (!mydb.openDB()) { Response.Write("error!"); return; } mydb.execute(sql); mydb.closeDB(); //} }
} #endregion
}}
3.新建excel读取类类ExcelHelper.cs 代码如下（）
using System;using System.IO;using System.Text;using System.Data;using System.Reflection;using System.Diagnostics;using System.Collections;using System.Data.OleDb;
public class ExcelHelper {
#region 读取excel /// <summary>查询EXCEL电子表格添加到DATASET /// /// </summary> /// <param name="filenameurl">服务器路径</param> /// <param name="table">表名</param> /// /// <returns></returns> public DataSet CheckExecleDs(string filenameurl, string table) { string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataSet ds = new DataSet(); OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn); odda.Fill(ds, table); conn.Close(); return ds; } #endregion
}//end class
4,新建数据库访问类 DB.cs
代码如下：
using System;using System.Data;using System.Data.OleDb;
/// <summary>/// DB 的摘要说明/// </summary>public class DB{ public Boolean status; public string errmsg; private OleDbConnection conn; public string optionresult = ""; public DB() { status = false; errmsg = ""; } public Boolean openDB() { //string dbhost = "db.88138.cc"; //string dbhost = "cdn760.fastcache.biz"; //string username = "h192316"; //string password = "odaynj"; string database = System.Web.HttpContext.Current.Server.MapPath("~/") + "//AGdata//AG.mdb";//注意数据库要放在新建文件夹AGdata中 //string connstr = "Server=" + dbhost + ";database=" + database + ";uid=" + username + ";pwd=" + password + ";"; string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + database + ";"; //string dbhost = "db.pc95500.com"; //string username = "h191241"; //string password = "jasyjasy"; //string database = "dbh191241"; //string connstr = "Server=" + dbhost + ";database=" + database + ";uid=" + username + ";pwd=" + password + ";"; conn = new OleDbConnection(connstr); try { conn.Open(); status = true; } catch (Exception ee) { status = false; errmsg = ee.Message; } return status; } public void getchild(string typeid, int depth, DB mydb) { string depstr = ""; for (int i = 0; i < depth; i++) { depstr += "├"; } string sql = "select TypeID,TypeName from webtype where typeid=" + typeid; DataTable dt = mydb.getDataSet(sql); foreach (DataRow dr in dt.Rows) { optionresult += "<option value='"+dr["TypeID"].ToString()+"'>"+depstr+dr["TypeName"].ToString()+"</option>"; //ListItem li = new ListItem(); //li.Text = depstr + dr["TypeName"].ToString(); //li.Value = dr["TypeID"].ToString(); //WebType.Items.Add(li); } sql = "select typeid from webtype where parentid=" + typeid; dt = mydb.getDataSet(sql); foreach (DataRow dr in dt.Rows) { getchild(dr["typeid"].ToString(), depth + 1, mydb); } } /// <summary> /// 返回DataTable /// </summary> /// <param name="sql">sql语句</param> /// <returns></returns> public DataTable getDataSet(string sql) { try { OleDbDataAdapter da = new OleDbDataAdapter(sql, conn); DataTable dt = new DataTable(); da.Fill(dt); return dt; } catch (Exception ee) { errmsg = ee.Message; return null; } } /// <summary> /// 返回datatable【0】 /// </summary> /// <param name="sql">sql语句</param> /// <param name="currentpage"> 页数</param> /// <param name="pagesize">每页数据量</param> /// <returns></returns> public DataTable getFenYeDataSet(string sql, int currentpage, int pagesize) { try { OleDbDataAdapter da = new OleDbDataAdapter(sql, conn); DataSet ds = new DataSet(); da.Fill(ds, (currentpage - 1) * pagesize, pagesize, "table1"); return ds.Tables[0]; } catch (Exception ee) { errmsg = ee.Message; return null; } } /// <summary> /// 是否存在 /// </summary> /// <param name="sql">sql语句</param> /// <returns></returns> public int execute(string sql) { try { OleDbCommand cmd = new OleDbCommand(sql, conn); return cmd.ExecuteNonQuery(); } catch (Exception ee) { errmsg = ee.Message; return -1; } } public void closeDB() { conn.Close(); status = false; }}
5 数据库结构 这里是acess数据库
user_id 自增
user_name 文本
user_tell 文本
相关例子在此可以下载http://download.csdn.net/source/3228865 发布的时候忘了设置成0分资源了···不知道咋滴改··现在要1分抱歉抱歉
