且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

Excel没有使用oledb C#的更新值

更新时间:2023-12-03 11:57:10

您需要检查表是否已存在,如果是,则只需添加记录即可.

You need to check if table already exist , If yes , then simply go for adding records.

这是一个可行的例子

        try
        {
            var connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyLearn\ExcelWorkBook.xls;Extended Properties=Excel 8.0";

            var sqlText = "CREATE TABLE TimeData ([HH] INT, [MM] INT,[AM / PM] VARCHAR(10))";

            using (var excelConnection = new OleDbConnection(connectionString))
            {
                object HHValue = TimeInHH.Text;
                object MMValue = TimeInMM.Text;
                object AMPMValue = Combo1AMPM.Text;

                // Executing this command will create the worksheet inside of the workbook
                // the table name will be the new worksheet name
                using (var command = new OleDbCommand(sqlText, excelConnection))
                {
                    if (excelConnection.State != ConnectionState.Open)
                    {
                        excelConnection.Open();
                        //check if table already exists.
                        var exists = excelConnection.GetSchema("Tables", new string[4] { null, null, "TimeData", "TABLE" }).Rows.Count > 0;

                        if(!exists)
                           command.ExecuteNonQuery();
                    }
                }

                // Add (insert) data to the worksheet
                var commandText = $"Insert Into TimeData ([HH], [MM], [AM / PM]) Values (@PropertyOne, @PropertyTwo, @PropertyThree)";

                using (var command = new OleDbCommand(commandText, excelConnection))
                {
                    // We need to allow for nulls just like we would with
                    // sql, if your data is null a DBNull.Value should be used
                    // instead of null 
                    command.Parameters.AddWithValue("@PropertyOne", HHValue ?? DBNull.Value);
                    command.Parameters.AddWithValue("@PropertyTwo", MMValue ?? DBNull.Value);
                    command.Parameters.AddWithValue("@PropertyThree", AMPMValue ?? DBNull.Value);
                    command.ExecuteNonQuery();
                }
            }


        }
        catch (Exception ex)
        {
            Console.WriteLine("Exception: " + ex.Message);
        }
        finally
        {
            Console.WriteLine("Executing finally block.");
        }

    }