C# Excel VSTO can not convert string to String.Array

Question

I am new to VSTO C# excel add-in. I am looking to find total count of not null/empty rows in a range. My Code looks at the range "A4:E4" and count total number of rows.

This is the code :

private void button1_Click(object sender, RibbonControlEventArgs e)
        {
            Workbook workbook =  Globals.ThisAddIn.GetWorkBook("c:\\temp\\testfile.xlsx");
            Worksheet mergeSheet = workbook.Worksheets["Data"];
            Excel.Range mergeCells = mergeSheet.Range["A4:E4"];
            var colValues = (System.Array)mergeCells.Columns[1].Cells.Value;
            var strArray = colValues.OfType<object>().Select(o => o.ToString()).ToArray();
            var rowCount = strArray.Length;
        }

 [public Excel.Workbook GetWorkBook(string pathName)
        {
            return (Excel.Workbook)Application.Workbooks.Open(pathName);
        }][1]

I get error var colValues = (System.Array)mergeCells.Columns[1].Cells.Value;on line :

Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'Cannot convert type 'string' to 'System.Array''

It works when I have two rows in my range. I have hardcoded range A4:E4 to produce the error. My excel sheet (testfile.xlsx) looks like below:

Any ideas how do I resolve this?

Same line of code works when I have two rows. Eg and following line is updated

Excel.Range mergeCells = mergeSheet.Range["A4:E5"];

Hi! You only access the first column (mergeCells.Columns[1].Cells.Value) instead of all the columns in your range (mergeCells.Cells.Value). There is a working sample in another question, so I have taken the liberty to link the other question as a duplicate. — Heinzi
– Heinzi, Commented Aug 8, 2018 at 14:15
mergeCells.Cells.Value gives me count of 5. I am looking for 1. since I have only 1 row. — ProgSky
– ProgSky, Commented Aug 8, 2018 at 14:20
Ah, I see, I will remove the duplicate marker then. Where in your code to you test for "emptyness" of a row? — Heinzi
– Heinzi, Commented Aug 8, 2018 at 14:29
This line is causing me trouble, var colValues = (System.Array)mergeCells.Columns[1].Cells.Value; This row has only one value. Note that same line works when mergeCells range has two rows. — ProgSky
– ProgSky, Commented Aug 8, 2018 at 15:02

Heinzi · Accepted Answer · 2018-08-08 15:27:18Z

1

The problem is that Range.Value can return different types of objects. Among others, it can return

a single value of type String, if the range contains a single cell containing a string or
an array of values, if the range contains more than one cell.

The simplest solution would be to count the number of cells and "wrap" the special "single value" case in an array:

var range = mergeCells.Columns[1].Cells;
var values = (range.Count == 1)
    ? new object[] { range.Value })
    : ((Sytem.Array)range.Value).Cast<object>();

answered Aug 8, 2018 at 15:27

Heinzi

173k61 gold badges386 silver badges554 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

This line is causing me trouble, var colValues = (System.Array)mergeCells.Columns[1].Cells.Value

This row has only one value. Note that same line works when mergeCells range has two rows.

Why it does not work for single cell range:

The Value of a single cell is not an array (it's a Long, String, DateTime, etc.) and won't be cast to an array in that manner. You can see this by testing like below:

var myArray = (System.Array)"hello"

This will give same failure for other types:

Why it works for multi-cell range:

The Value of a multi-cell range will return a variant array of the individual cell values, which either is, or can be cast to a System.Array

There may be a better resolution, but at least you should be able to do like:

var colValues;
if (mergeCells.Columns[1].Cells.Count > 1)
{
    colValues = (System.Array)mergeCells.Columns[1].Cells.Value;
}
else
{
    // NB: you may need to cast other data types to string
    //     or you could use .Cells[0].Text.Split()
    colValues = (System.Array)mergeCells.Columns[1].Cells[0].Value.Split();
}

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 8, 2018 at 15:23

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

1 Comment

ProgSky Over a year ago

Thanks for the helpful explanation.

Collectives™ on Stack Overflow

C# Excel VSTO can not convert string to String.Array

2 Answers 2

Comments

Why it does not work for single cell range:

Why it works for multi-cell range:

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Why it does not work for single cell range:

Why it works for multi-cell range:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related