一个简单的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分抱歉抱歉