求助 asp.net excel导入数据库

是不是数据类型不一致啊，可能把11205认为是Int的，而K11205为字符串

将Excel中的数据读取到DataSet中,然后遍历DataSet

using System;
using System.Collections;
using System.Configuration;
using System.Data;
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.Data.SqlClient;
using System.Data.OleDb;
using System.Text;
using System.IO;public partial class update_excel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            /*try
            {
                string username = Session["username"].ToString();
                Session["username"] = username;
                SqlConnection con = new SqlConnection("server=192.168.3.180;database=jjjs;uid=sa;password=sa;");
                con.Open();
                string sql_name = "select * from users where name='" + username + "'";
                SqlCommand cmd = new SqlCommand(sql_name, con);
                SqlDataReader sdr = cmd.ExecuteReader();
                if (sdr.HasRows)
                {
                    sdr.Read();
                    if (sdr["department"].ToString() == "项目执行中心")
                    {
                        con.Close();
                    }
                    else
                    {
                        con.Close();
                        Response.Write("<script language='javascript'>");
                        Response.Write("alert('" + username + "，您没有权限进行此项操作！');");
                        Response.Write("location.href='right.aspx';");
                        Response.Write("</script>");
                        Response.End();
                    }
                }
                else
                {
                    con.Close();
                    Response.Write("<script language='javascript'>");
                    Response.Write("alert('" + username + "，您没有权限进行此项操作！');");
                    Response.Write("location.href='right.aspx';");
                    Response.Write("</script>");
                    Response.End();
                }
            }
            catch (NullReferenceException nre)
            {
                Response.Write("<script language='javascript'>");
                Response.Write("alert('你还没有登陆，请登陆！');");
                Response.Write("parent.location.href='http://192.168.65.155/';");
                Response.Write("</script>");
                Response.End();
            }*/
        }
    }    protected void BtnImport_Click(object sender, EventArgs e)
    {
        string filename = string.Empty;
        try
        {
            filename = UpLoadXls(FileExcel);//上传XLS文件
            ImportXlsToData(filename);//将XLS文件的数据导入数据库
            if (filename != string.Empty && System.IO.File.Exists(filename))
            {
                System.IO.File.Delete(filename);//删除上传的XLS文件
            }
            //LblMessage.Text = "数据导入成功！";
        }
        catch (Exception ex)
        {
            LblMessage.Text = ex.Message;
        }
    }    /// <summary>
    /// 上传Excel文件
    /// </summary>
    /// <param name="inputfile">上传的控件名</param>
    /// <returns></returns>
    private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
    {
        string orifilename = string.Empty;
        string uploadfilepath = string.Empty;
        string modifyfilename = string.Empty;
        string fileExtend = "";//文件扩展名
        int fileSize = 0;//文件大小
        try
        {
            if (inputfile.Value != string.Empty)
            {
                //得到文件的大小
                fileSize = inputfile.PostedFile.ContentLength;
                if (fileSize == 0)
                {
                    throw new Exception("导入的Excel文件大小为0，请检查是否正确！");
                }
                //得到扩展名
                fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                if (fileExtend.ToLower() != "xls" && fileExtend.ToLower() != "xlsx")
                {
                    throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");
                }
                //路径
                uploadfilepath = Server.MapPath("~/Service/GraduateChannel/GraduateApply/ImgUpLoads");
                //新文件名
                modifyfilename = System.Guid.NewGuid().ToString();
                modifyfilename += "." + inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                //判断是否有该目录
                System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
                if (!dir.Exists)
                {
                    dir.Create();
                }
                orifilename = uploadfilepath + "\\" + modifyfilename;
                //如果存在,删除文件
                if (File.Exists(orifilename))
                {
                    File.Delete(orifilename);
                }
                // 上传文件
                inputfile.PostedFile.SaveAs(orifilename);
            }
            else
            {
                throw new Exception("请选择要导入的Excel文件!");
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return orifilename;
    }    //// <summary>
    /// 从Excel提取数据--》Dataset
    /// </summary>
    /// <param name="filename">Excel文件路径名</param>
    private void ImportXlsToData(string fileName)
    {
        try
        {
            if (fileName == string.Empty)
            {
                throw new ArgumentNullException("Excel文件上传失败！");
            }            string oleDBConnString = String.Empty;
            oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";
            oleDBConnString += "Data Source=";
            oleDBConnString += fileName;
            oleDBConnString += ";Extended Properties=Excel 8.0;";
            OleDbConnection oleDBConn = null;
            OleDbDataAdapter oleAdMaster = null;
            DataTable m_tableName = new DataTable();
            DataSet ds = new DataSet();            oleDBConn = new OleDbConnection(oleDBConnString);
            oleDBConn.Open();
            m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);            if (m_tableName != null && m_tableName.Rows.Count > 0)
            {                m_tableName.TableName = m_tableName.Rows[0]["TABLE_NAME"].ToString();            }
            string sqlMaster;
            sqlMaster = " SELECT *  FROM [" + m_tableName.TableName + "]";
            oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
            oleAdMaster.Fill(ds, "m_tableName");
            oleAdMaster.Dispose();
            oleDBConn.Close();
            oleDBConn.Dispose();            AddDatasetToSQL(ds, 12);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }    /// <summary>
    /// 将Dataset的数据导入数据库
    /// </summary>
    /// <param name="pds">数据集</param>
    /// <param name="Cols">数据集列数</param>
    /// <returns></returns>
    private bool AddDatasetToSQL(DataSet pds, int Cols)
    {
        int ic, ir;
        ic = pds.Tables[0].Columns.Count;
        if (pds.Tables[0].Columns.Count < Cols)
        {
            throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
        }
        ir = pds.Tables[0].Rows.Count;
        if (pds != null && pds.Tables[0].Rows.Count > 0)
        {
            SqlConnection con = new SqlConnection("server=192.168.3.180;database=jjjs;uid=sa;password=sa;");
            con.Open();
            SqlCommand sqlcom = con.CreateCommand();
            sqlcom.Transaction = con.BeginTransaction();
            string sql_update = "";
            try {
                int j = pds.Tables[0].Rows.Count;
                for (int i = 9; i < j; i++)
                {
                    string dabh = pds.Tables[0].Rows[i][2].ToString().Trim();
                    if (dabh != "")
                    {
                        //Update(pds.Tables[0].Rows[i][2].ToString(), float.Parse(pds.Tables[0].Rows[i][11].ToString()));
                        float lxtz = (float)((int)Math.Round(float.Parse(pds.Tables[0].Rows[i][12].ToString())));
                        sql_update = "update project_bak set lxtz='" + lxtz + "' where dabh='" + pds.Tables[0].Rows[i][2].ToString() + "'";
                        sqlcom.CommandText = sql_update;
                        int k = sqlcom.ExecuteNonQuery();
                        if (k == 0) {
                            throw new Exception("档案编号" + dabh + "不存在，请确认Excel表格！");
                        }
                    }
                }
                sqlcom.Transaction.Commit();
                con.Close();
            }
            catch (Exception ex)
            {
                sqlcom.Transaction.Rollback();
                con.Close();
                throw new Exception("导入数据出错：" + ex);
            }
        }
        else
        {
            throw new Exception("导入数据为空！");
        }
        return true;
    }    /// <summary>
    /// 插入数据到数据库
    /// </summary>
    /*public void Update(string dabh, float lxtz)
    {
         sqlcom.CommandText = "udpate project_bak set lxtz='" + lxtz + "' where dabh='" + dabh + "'";
         sqlcom.ExecuteNonQuery();
    }*/
}就是dabh，取不到字符

这个问题我也遇到，承蒙孟老大指教Excel连接字符串要加 IMEX=1 表示按文本读取strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/xx/xx.xls") + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";

调试易

求助 asp.net excel导入数据库

解决方案 »