Excel数据导入sql数据库方法.doc

Excel 导入数据到sql server 2010中 代码实现：using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Data.SqlClient;using System.Data.OleDb;using System.Collections;using System.IO;public partial class pageitems_excelin : System.Web.UI.Page protected void Page_Load(object sender, EventArgs e) protected void Button1_Click(object sender, EventArgs e) if (FileUpload1.HasFile = false) Page.RegisterStartupScript(,alert(请您选择Excel文件); ); return;/当无文件时,返回 string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower(); if (IsXls != .xlsx & IsXls != .xls) Page.RegisterStartupScript(,alert(只可以选择Excel文件);); return;/当选择的不是Excel文件时,返回 SqlConnection cn = SqlHelp.GetCon(); cn.Open(); string strpath = FileUpload1.PostedFile.FileName.ToString(); /获取Execle文件路径 string filename = FileUpload1.FileName; /获取Execle文件名 DataSet ds = ExecleDs(strpath, filename); DataRow dr = ds.Tables0.Select(); /定义一个DataRow数组 int rowsnum = ds.Tables0.Rows.Count; if (rowsnum = 0) Page.RegisterStartupScript(,alert(Excel表为空表,无数据!);); /当Excel表为空时,对用户进行提示 else for (int i = 0; i dr.Length; i+) string sqlcheck = select count(*) from name where name= + driname.ToString() + ; /检查用户是否存在 int num = Convert.ToInt32(SqlHelp.ExObject(sqlcheck); if (num = 0) string insertstr = insert into name(name,sex,age) values( + driname.ToString() + , + drisex.ToString() + , + driage.ToString() + ); SqlCommand cmd = new SqlCommand(insertstr, cn); try cmd.ExecuteNonQuery(); catch (Exception ex) /捕捉异常 Page.RegisterStartupScript(,alert(添加失败: + ex.Message + );); else Page.RegisterStartupScript(,alert(+driname.ToString()+,已存在.!);); /若存在,将已存在用户信息打出,并提示此用户无法插入 continue; Page.RegisterStartupScript(,alert(Excel表导入成功!);); cn.Close(); public DataSet ExecleDs(string filenameurl,string table) /string strConn = Provider=Microsoft.Jet.OleDb.4.0; + data source= +filenameurl+ ;Extended Properties=Excel 4.0; HDR=YES; IMEX=1; string strConn = Provider=Microsoft.ACE.OleDb.12.0; + data source= + filenameurl + ;Extended Properties=Excel 8.0; HDR=YES; IMEX=1; OleDbConnection conn = new OleDbConnection(strConn); OleDbDataAdapter odda = new OleDbDataAdapter(select * from Sheet1$,conn); DataSet ds = new DataSet(); odda.Fill(ds,table); return