无法使用oledb连接更新excel woksheet中的单元格 [英] Not able to update cell in excel woksheet using oledb connection

问题描述

我试图通过编写这段代码来更新execl sheet paricular sheet：

I am trying to update execl sheet paricular sheet by writing this code:

connExcel.Open();
        string SheetName = null;
        DataTable dtExcelSchema = null;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        //checking sheet name
        if (dtExcelSchema.Rows[2]["TABLE_NAME"].ToString().Contains("_xlnm#Database"))
        {
            SheetName = dtExcelSchema.Rows[2]["TABLE_NAME"].ToString();
        }

        if (!dtExcelSchema.Rows[2]["TABLE_NAME"].ToString().Contains("_xlnm#Database"))
        {
            SheetName = dtExcelSchema.Rows[2]["TABLE_NAME"].ToString();
        }
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, SheetName, null });
        OleDbCommand cmd = new OleDbCommand("UPDATE [" + SheetName + "] SET F11=22052", connExcel);
        int result = cmd.ExecuteNonQuery();
        Console.WriteLine(result);
        connExcel.Close();

但是这个更新查询正在更新整个列，而我只想更新excel中的特定单元格，说'C9'符合excel表格。

But this update query is updating the whole column while i just want update particular cell in excel say 'C9' accodring to excel sheet.

任何帮助都将不胜感激。

Any help will be appreciated.

我的Excel工作表看起来像这样..

My excel sheet looks like this..

推荐答案

您好，

您需要在UPDATE语句中添加WHERE条件，例如

You need to add a WHERE condition to the UPDATE statement e.g.

OleDbCommand cmd = new OleDbCommand("UPDATE [" + SheetName + "] SET F11=22052 WHERE F3 = 'Whatever'", connExcel);

简而言之，你需要像在常规数据库表中那样关键字。

In short, you need something to key off of as you would in a regular database table.

这篇关于无法使用oledb连接更新excel woksheet中的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！