Replacing character in a column of excel file

Question

I am reading a specific column in an excel file and trying to change values in it. The file has records like the following

12345*5
12445*6
12312*9

I want to replace the '*' with '_'.

I get the following error 'Cannot implicitly convert type 'object[*,*]' to 'string[*,*]'. An explicit conversion exists (are you missing a cast?)'

I have looked the error online but have not been able to fix this error.

    static void Main(string[] args)
    {
        var xlApp = new Excel.Application();
        var xlWorkbook = xlApp.Workbooks.Open(@"path");
        var xlWorksheet = xlWorkbook.Sheets[1];

        Excel.Range xlRange = xlWorksheet.UsedRange;

        ChangeValue(xlWorksheet);
    }

    private static void ChangeValue(Excel.Worksheet oWorksheet)
    {

        var colNo = oWorksheet.UsedRange.Columns.Count;
        var rowNo = oWorksheet.UsedRange.Rows.Count;

        string[,] array = oWorksheet.UsedRange.Value;
        for (var j = 1; j <= colNo; j++)
        {
            for (var i = 1; i <= rowNo; i++)
            {
                if (array[i, j] == "Number Field")
                {
                    for (var m = i + 1; m < rowNo; m++)
                    {
                        if(array[m, j].Contains('*'))
                        {
                            array[m, j].Replace('*', '_');
                        }
                    }
                    oWorksheet.UsedRange.Value = array;
                    return;
                }
            }
        }
    }

You can try to change string[,] to object[,] and make a cast to string after your second for statement. Plus, your replace line will not actually update the entry in the array because Replace is returning a new string. — K. H., Feb 01 '18 at 17:28

score 0 · Answer 1 · answered Feb 01 '18 at 17:34

The property you are using, Range.Value, is not a simple two dimensional string array. That's why you're getting the error. It returns an XML object which may have values, in which case you will need to explicitly cast those values to strings before you can store them in a string array.

Since you are iterating by column and row, you may be best served by converting to string without assigning a new object when you check your conditions or fetch your values. I haven't tested it, but something like this may work:

oWorksheet.UsedRange.Value[i, j].ToString() == "Number Field"

lucky · Answer 2 · 2018-02-01T19:40:33.510

The error is pretty clear that tells you are trying to cast object[,] array to string[,] array.

string[,] array = oWorksheet.UsedRange.Value;

So, modify your code like this.

private static void ChangeValue(Worksheet oWorksheet)
{
    var colNo = oWorksheet.UsedRange.Columns.Count;
    var rowNo = oWorksheet.UsedRange.Rows.Count;
    for (var j = 1; j <= colNo; j++)
    {
        for (var i = 1; i <= rowNo; i++)
        {
            Range row = oWorksheet.Rows.Cells[i, j];
            if (row.Value.ToString().Contains("*"))
            {
                row.Value = row.Value.ToString().Replace("*","_");
            }
        }
    }
}

Then the main method looks like;

static void Main(string[] args)
{
    var xlApp = new Microsoft.Office.Interop.Excel.Application();
    var xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\Administrator\source\repos\ConsoleApp1\ConsoleApp1\bin\Debug\asd.xlsx");
    var xlWorksheet = xlWorkbook.Sheets[1];
    ChangeValue(xlWorksheet);
    xlWorkbook.Application.Save();
    xlWorkbook.Save();
    xlWorkbook.Close();
    Marshal.FinalReleaseComObject(xlWorksheet);
    Marshal.FinalReleaseComObject(xlWorkbook);
    xlWorksheet = null;
    xlWorkbook = null;
    xlApp.Quit();
    Marshal.FinalReleaseComObject(xlApp);
    xlApp = null;
    Console.ReadLine();
}

Also, you can use xlWorkbook.Application.Save to save changes.

Thank you for the answer. Any idea why the values are not getting replaced? — Maddy, Feb 01 '18 at 18:43
I get the following error `System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800A03EC'` — Maddy, Feb 01 '18 at 18:56
https://stackoverflow.com/questions/12714626/exception-from-hresult-0x800a03ec-error — lucky, Feb 01 '18 at 19:18
can you possibly share you file? This helped me to solve my problem https://stackoverflow.com/questions/12714626/exception-from-hresult-0x800a03ec-error BUT the characters still dont get replaced. — Maddy, Feb 01 '18 at 19:30

score 0 · Answer 3 · answered Feb 01 '18 at 17:47

Can you try this? Assign the range at the end of 2 for loop. Like:

for (var j = 1; j <= colNo; j++)
        {
            for (var i = 1; i <= rowNo; i++)
            {
                if (array[i, j] == "Number Field")
                {
                    for (var m = i + 1; m < rowNo; m++)
                    {
                        if(array[m, j].Contains('*'))
                        {
                            array[m, j].Replace('*', '_');
                        }
                    }

                    return;
                }
            }
        }
oWorksheet.UsedRange.Value = array;

Replacing character in a column of excel file

3 Answers3