如何按名称获取和比较Excel电子表格中的单元格内容?

I'm trying to compare a cell with a string to replace it if it is equal. But when I try to do the code below the 0x800A03EC error occurs.

我试着比较一个单元格和一个字符串，如果它是相等的，就把它替换掉。但是当我尝试执行0x800A03EC下面的代码时，就会发生错误。

                int cont = 0;
                string cell;
                do
                {
                    cont++;
                    cell = rCol.ToUpper() + cont.ToString(); // = "D1"
                    string cellData = ((Excel.Range)sheet.Cells[cell]).Value2.ToString();

                    if (cellData == from)
                    {
                        sheet.Cells[cell] = to;
                    }
                } while (sheet.Cells[cell] == null);

How can I do this?

我该怎么做呢?

2 个解决方案

#1

If you know the cell you want to check, for example A1, you can do it like this:

如果你知道你要检查的单元格，例如A1，你可以这样做:

using System;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace Test
{
    class Program
    {
        static void Main(string[] args)
        {
            // create app
            var excelApp = new Excel.Application();
            // open workbook
            var workbook = excelApp.Workbooks.Open(
                @"C:\Users\Home\Documents\Book1.xlsx",
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
            // open sheet
            var sheet = (Excel.Worksheet)workbook.Sheets[1];

            // create some variables
            var from = "Pete";
            var to = "Dave";
            // compare cell A1 [1,1] with 'from'
            if (string.Equals(sheet.Cells[1,1].Value, from))
            {
                sheet.Cells[1, 1].Value = to;
            }

            // save the workbook
            workbook.Save();
            // close the workbook and release resources
            workbook.Close(true, workbook.Path);
            Marshal.ReleaseComObject(workbook);
            workbook = null;
        }
    }
}

#2

Try this to get at a simple range:

试着做一个简单的范围:

int row = 1;
string col = "D";        
string text = sheet.get_Range(col + row.ToString()).Value;

The 0x800A03EC error is a value returned by Excel, which means NAME_NOT_FOUND (see this SA question). Looks like you were passing a parameter that Excel could not find, probably because you were passing a string ("D1"), rather than two integer parameters (4,1).

0x800A03EC错误是Excel返回的值，这意味着NAME_NOT_FOUND(请参见这个SA问题)。看起来您正在传递一个Excel找不到的参数，可能是因为您正在传递一个字符串(“D1”)，而不是两个整数参数(4,1)。

It's impossible to tell where you are getting the rCol.ToUpper() value, without seeing more of your code. However, if you are attempting to go through a series of columns and rows to check for an equality condition (that's what it looks like you are attempting), you will quickly run into the pesky problem of how to increment the column value using capital letters (just try it; not much fun!).

如果不查看更多的代码，就不可能知道rcoll . toupper()值在哪里。但是，如果您试图遍历一系列列和行来检查相等性条件(这就是您要尝试的)，您将很快遇到如何使用大写字母增加列值的麻烦问题(只要尝试一下;没有多少乐趣!)。

One solution I did in VB recently was to use the native Excel function index, which uses numeric values to get at a particular cell. You would need to cast an object of the type Excel.WorksheetFunction to use that function. But I have since discovered there are easier solutions than using an Excel function:

我最近在VB中做的一个解决方案是使用本机Excel函数索引，它使用数值来获取特定的单元格。您需要强制转换Excel类型的对象。使用该函数的工作表函数。但我发现有比使用Excel更简单的解决方案:

using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace exceltest
{
class Program
{

    static void Main(string[] args)
    {
        Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook workbook = xl.Workbooks.Open(@"C:\test.xlsx");
        Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.Sheets[1];
        xl.Visible = true;

        //use this if you want to use native Excel functions (such as index)
        Microsoft.Office.Interop.Excel.WorksheetFunction wsFunc = xl.WorksheetFunction;

        int maxNum = 100; // set maximum number of rows/columns to search

        string from = "blah";
        string to = "blum";

        //this is pretty slow, since it has to interact with 10,000 cells in Excel
        // just one example of how to access and set cell values           
        for (int col = 1; col <= maxNum; col++)
        {
            for (int row = 1; row <= maxNum; row ++)
            {
                Range cell = (Range)sheet.Cells[row, col];
                if ((string)cell.Value == from) //cast to string to avoid null reference exceptions
                {
                    cell.Value = to;
                }
            }
        }
    }
}

}

#1