已经把excel文件上传到服务器，怎么将excel中的数据导入到SQL数据库呢？用asp.net和c#

Question

以下代码只能上传excel文件，实现不了将excel中的数据导入到SQL数据库里面，麻烦各位教教我怎么改~~~public partial class Web_DataImport : System.Web.UI.Page
{    
    protected void Page_Load(object sender, EventArgs e)
    {    }
    protected void BtnImport_Click(object sender, EventArgs e)
    {
        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文件时,返回
        }
        string filename = FileUpload1.FileName;              //获取Execle文件名  DateTime日期函数
        string savePath = Server.MapPath(("upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
        FileUpload1.SaveAs(savePath);                        //SaveAs 将上传的文件内容保存在服务器上
        DataSet ds = ExcelSqlConnection(savePath, filename);           //调用自定义方法
        DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组
        int rowsnum = ds.Tables[0].Rows.Count;
        if (rowsnum == 0)
        {
            Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
        }
        else
        {
            for (int i = 0; i < dr.Length; i++)
            {
                //前面需要在建立一个“upfiles”的文件夹，通过下面的方式获取Excel的值，然后再将这些值用插入到数据库里面
                string id = dr[i]["编号"].ToString();
                string name = dr[i]["姓名"].ToString();
                string sex = dr[i]["性别"].ToString();
                string password = dr[i]["密码"].ToString();
                string rclass = dr[i]["班级"].ToString();
                string profession = dr[i]["专业"].ToString();
                string role = dr[i]["身份"].ToString();                //Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
            }
            Response.Write("<script>alert('Excle表导入成功!');</script>");
        }
    }    #region 连接Excel  读取Excel数据   并返回DataSet数据集合
    /// <summary>
    /// 连接Excel  读取Excel数据   并返回DataSet数据集合
    /// </summary>
    /// <param name="filepath">Excel服务器路径</param>
    /// <param name="tableName">Excel表名称</param>
    /// <returns></returns>
    public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName)
    {
        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
        OleDbConnection ExcelConn = new OleDbConnection(strCon);        try
        {
            string strCom = string.Format("SELECT * FROM [Sheet1$]");
            ExcelConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
            DataSet ds = new DataSet();
            myCommand.Fill(ds, "[" + tableName + "$]");
            ExcelConn.Close();
            return ds;
        }
        catch
        {
            ExcelConn.Close();
            return null;
        }
    }
    #endregion
}

Answer 1

for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                        {
                         string intostr = "insert into  //你的语句                              sqlcon.Open();
                                sqlcom = new SqlCommand(update_j_sqlstr, sqlcon);
                                sqlcom.ExecuteNonQuery();//更新
                                sqlcon.Close();
                            }

Answer 2

直接用SQLSERVER自带的导入功能，多好！

Answer 3

将EXCEL中的数据导入datatable 然后遍历利用事务一条一条的插入//执行事务处理
public void DoTran()
{ 　//建立连接并打开
　SqlConnection myConn=GetConn();
　myConn.Open();
　SqlCommand myComm=new SqlCommand();
　//SqlTransaction myTran=new SqlTransaction();
　//注意，SqlTransaction类无公开的构造函数
　SqlTransaction myTran;
　//创建一个事务
　myTran=myConn.BeginTransaction();
　try
　{
　　//从此开始，基于该连接的数据操作都被认为是事务的一部分
　　//下面绑定连接和事务对象
　　myComm.Connection=myConn;
　　myComm.Transaction=myTran; //定位到pubs数据库
　　myComm.CommandText="USE pubs";
　　myComm.ExecuteNonQuery();//操作1
　　myComm.CommandText=""; //操作2
　　myComm.ExecuteNonQuery();
　　　//提交事务
　　myTran.Commit();
　}
　catch(Exception err)
　{
    myTran.Roback();
　　throw new ApplicationException("事务操作出错，系统信息："+err.Message);
　 }
　finally
　{
　　myConn.Close();
　 }
}

Answer 4

还是推荐通过SQL server 导入 很方便

Answer 5

for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
  {
  string intostr = "insert into //你的语句 sqlcon.Open();
  sqlcom = new SqlCommand(update_j_sqlstr, sqlcon);
  sqlcom.ExecuteNonQuery();//更新
  sqlcon.Close();
  }
你好(net5354）这段代码放在哪哈

Answer 6

PasteExcelToExe软件能把EXCEL中数据自动导入EXE文件输入数据的画面中，并能自动提交

Answer 7

遍历你导出到DataSet中的数据 ，然后逐条的insert

Answer 8

 DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组
        int rowsnum = ds.Tables[0].Rows.Count;
        if (rowsnum == 0)
        {
            Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
        }
        else
        {
            for (int i = 0; i < dr.Length; i++)
            {
                //前面需要在建立一个“upfiles”的文件夹，通过下面的方式获取Excel的值，然后再将这些值用插入到数据库里面
                string id = dr[i]["编号"].ToString();
                string name = dr[i]["姓名"].ToString();
                string sex = dr[i]["性别"].ToString();
                string password = dr[i]["密码"].ToString();
                string rclass = dr[i]["班级"].ToString();
                string profession = dr[i]["专业"].ToString();
                string role = dr[i]["身份"].ToString();                //Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
            }
            Response.Write("<script>alert('Excle表导入成功!');</script>");
        }把这段换成6楼的代码

Answer 9

 bConvert = MathFile(customDs, modelDs);
                string dllPath = server.MapPath("/bin/ERJC.DAL.dll");//获取实体类的程序集路径，反射
                Assembly assembly = Assembly.LoadFile(dllPath);
                string[] classPaths = dllPath.Split('\\');
                string classPath = classPaths[classPaths.Length - 1].Replace("dll", "");
                string strClassName = string.Empty;
                if (bConvert)
                {
                    db.Connection.Open();
                    System.Data.Common.DbTransaction DBTran = db.Connection.BeginTransaction();
                    try
                    {
                        StringBuilder sb = new StringBuilder();
                        foreach (System.Data.DataTable dt in customDs.Tables)
                        {
                            strClassName = dt.TableName;
                            object obj = assembly.CreateInstance(classPath + strClassName);
                            foreach (DataRow dr in dt.Rows)
                            {
                                sb.AppendFormat("insert into  {0}(", strClassName);
                                for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                                {
                                    if (dr[colIndex] != null)
                                    {
                                        sb.AppendFormat("{0},", dt.Columns[colIndex].ColumnName);
                                    }
                                }
                                sb.Remove(sb.Length - 1, 1);
                                sb.Append(") values (");
                                for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                                {
                                    Type type = obj.GetType();
                                    try
                                    {
                                        if (dr[colIndex] != null)
                                        {
                                            foreach (PropertyInfo p in type.GetProperties())
                                            {
                                                if (p.Name == dt.Columns[colIndex].ColumnName)
                                                {
                                                    string dataType = p.PropertyType.FullName;
                                                    if (dataType.Contains("Int32"))
                                                    {
                                                        sb.AppendFormat("{0},", Convert.ToInt32(dr[colIndex]));
                                                    }
                                                    else if (dataType.Contains("Int64"))
                                                    {
                                                        sb.AppendFormat("{0},", Convert.ToInt64(dr[colIndex]));
                                                    }
                                                    else if (dataType.Contains("ToDecimal"))
                                                    {
                                                        sb.AppendFormat("{0},", Convert.ToDecimal(dr[colIndex]));
                                                    }
                                                    else if (dataType.Contains("DateTime"))
                                                    {
                                                        sb.AppendFormat("'{0}',", Convert.ToDateTime(dr[colIndex]));
                                                    }
                                                    else if (dataType.Contains("String"))
                                                    {
                                                        sb.AppendFormat("'{0}',", Convert.ToString(dr[colIndex]));
                                                    }
                                                }
                                            }
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        bConvert = false;
                                        msg = "数据转换出错，请检查数据格式是否正确";
                                        throw ex;
                                    }
                                    finally
                                    {
                                        db.Connection.Close();
                                    }
                                }
                                sb.Remove(sb.Length - 1, 1);
                                sb.Append(")");
                            }
                            db.ExecuteStoreCommand(sb.ToString(), null);
                        }
                        DBTran.Commit();
                        db.SaveChanges();
                        msg = "文件导入成功";
                        bConvert = true;
                    }                    catch (Exception ex)
                    {
                        bConvert = false;
                        msg = "文件导入失败";
                        DBTran.Rollback();
                        throw ex;
                    }
                    finally
                    {
                        db.Connection.Close();
                    }

Answer 10

FileUpload1.SaveAs(savePath);我怎么会有错误：E:\NetWork_School\NetWork_UI\ContentManageSystem\User\upfiles\20132427020939导出用户信息.xls”的一部分。

Answer 11

Refer案例：
http://www.cnblogs.com/insus/archive/2012/06/12/2545801.html