从Excel更新数据库数据，调试:来自Excel表的数据行值为空，为实际值

Question

我开发了一个Web应用程序，它使用 ASP.NET 和C#从Excel导入数据库数据。这是一个代码序列，有时无法正常工作。例如，当我将 excel 文件导入数据库并且行验证条件(正在工作(字符串之一等于 Agentie(来自数据库(和 _Agentie(来自 excel 文件(时，它应该更新数据库中的所有数据行，但如果数据是数字(来自 excel(，否则它会像空一样更新。

CREATE TABLE [dbo].[Table] (
[AgentieID]  INT            IDENTITY (1, 1) NOT NULL,
[NrCrt]      VARCHAR (50)  NOT NULL,
[Agentie]    VARCHAR (50)  NOT NULL,
[Intrare]    VARCHAR (50)  NOT NULL,
[Iesire]     VARCHAR (50)  NOT NULL,
[Explicatii] VARCHAR (300) NOT NULL,
[Operator]   VARCHAR (50)  NOT NULL,
[Data_Ora]   VARCHAR (50)  NOT NULL,
[Sold]       VARCHAR (50)  NOT NULL,
[UltimaOp]   VARCHAR (50)  NOT NULL,
[Zile]       VARCHAR (50)  NOT NULL,
PRIMARY KEY CLUSTERED ([AgentieID] ASC)

(;

        protected void btnImport_Click(object sender, EventArgs e)
    {
        if (FileUpload1.PostedFile.ContentType == "application/vnd.ms-excel" ||
            FileUpload1.PostedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        {
            try
            {
                string fileName = Path.Combine(Server.MapPath("~/ImportDocument"), Guid.NewGuid().ToString() + Path.GetExtension(FileUpload1.PostedFile.FileName));
                FileUpload1.PostedFile.SaveAs(fileName);
                string conString = "";
                string ext = Path.GetExtension(FileUpload1.PostedFile.FileName);
                if (ext.ToLower() == ".xls")
                {
                    conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties="Excel 8.0;HDR=Yes;IMEX=2""; ;
                }
                else if (ext.ToLower() == ".xlsx")
                {
                    conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties="Excel 12.0;HDR=Yes;IMEX=2"";
                }

                string query = "Select [NrCrt],[Agentie],[Intrare],[Iesire],[Explicatii],[Operator],[Data_Ora],[Sold],[UltimaOp],[Zile] from [Agentii$]";
                OleDbConnection con = new OleDbConnection(conString);
                if (con.State == System.Data.ConnectionState.Closed)
                {
                    con.Open();
                }                   
               OleDbConnection con = new OleDbConnection(conString);
                if (con.State == System.Data.ConnectionState.Closed)
                {
                    con.Open();
                }
                OleDbCommand cmd = new OleDbCommand(query, con);
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);                   
                DataSet ds = new DataSet();
                da.Fill(ds);
                da.Dispose();
                con.Close();
                con.Dispose();
                // Import to Database
                using (databaseAgentii3Entities dc = new databaseAgentii3Entities())
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                    ////////////////////////////Here is a problem !!!////////////////////////////////////////////////
                        string _Agentie = dr["Agentie"].ToString();
                        string _Sold = dr["Sold"].ToString();
                         var v = dc.Tables.Where(a => string.Equals(a.Agentie, _Agentie)).FirstOrDefault();
                        if (v != null)
                        {
                            // Update here
                            //v.AgentieID = dr["AgentieID"].ToString();
                            v.NrCrt = dr["NrCrt"].ToString();
                            v.Agentie = dr["Agentie"].ToString();
                            v.Intrare = dr["Intrare"].ToString();
                            v.Iesire = dr["Iesire"].ToString();
                            v.Explicatii = dr["Explicatii"].ToString();
                            v.Operator = dr["Operator"].ToString();
                            v.Data_Ora = dr["Data_Ora"].ToString();
                            v.Sold = dr["Sold"].ToString();
                            v.UltimaOp = dr["UltimaOp"].ToString();
                            v.Zile = dr["Zile"].ToString();
                        }
                        ////////////////////////////////////////////////////////////////////////////////////////////////
                        else
                        {
                            // Insert
                            dc.Tables.Add(new Table
                            {
                                //AgentieID = dr["AgentieID"].ToString(),
                                NrCrt = dr["NrCrt"].ToString(),
                                Agentie = dr["Agentie"].ToString(),
                                Intrare = dr["Intrare"].ToString(),
                                Iesire = dr["Iesire"].ToString(),
                                Explicatii = dr["Explicatii"].ToString(),
                                Operator = dr["Operator"].ToString(),
                                Data_Ora = dr["Data_Ora"].ToString(),
                                Sold = dr["Sold"].ToString(),
                                UltimaOp = dr["UltimaOp"].ToString(),
                                Zile = dr["Zile"].ToString()
                            });
                        }
                    }
                    dc.SaveChanges();
                }
                populateDatabaseData();
                lblMessage.Text = "Successfully data import done!";
            }
            catch (Exception)
            {
                throw;
            }
        }
    }

Answer 1

如果 Excel 中的列混合了字符串和数值数据，请将连接字符串更改为使用 IMEX=1 而不是 IMEX=2 来指示混合列类型。否则，如果有足够的数值，则将假定该列是数字，并且任何非数值都将被清空。