Convert Interop to ClosedXML

Question

I have 3 lines of C# Interop code that I am struggling to convert to ClosedXML. Can anyone please help?

str2 = worksheet.Range("B3", "L13").get_End(XlDirection.xlDown).get_Address(false, false, XlReferenceStyle.xlA1, System.Type.Missing, System.Type.Missing).Replace("B", "L");
object[,] objArray = (object[,]) worksheet.Range("B3", str2).Value;
str3 = worksheet.Range("B3", "L13").get_End(XlDirection.xlDown).get_Address(false, false, XlReferenceStyle.xlA1, System.Type.Missing, System.Type.Missing).Replace("B", "L");

Thanks

Hi, could you provide actual and expected outputs, to help narrow down the issue ? — matthiasbe
Hi @matthiasbe, The code is a bit complex that I can't understand what the actual and expected outputs of those specific lines are. I am trying to migrate a whole project from Interop to closedXML and only struggling with these couple of lines. Thanks — Toufic Diab
I understand, the thing is it's hard to understand this piece of code out of its context. I would advise you to read this to have ideas — matthiasbe

Aleksei Aleksei · Accepted Answer · 2019-05-09T06:29:26

As answered here

This snipped looks overly complicated. First, you create a range from B3 to L13, then move down to the last row (getting to B13, apparently), then replace B to L which gives you L13 and finally you create a range B3:L13 once more to get the values from there.

ClosedXML does not have a built-in method to convert a range into a two-dimensional array but this can be easily done manually:

var range = worksheet.Range("B3:L13");
var objArray = new object[range.ColumnCount(), range.RowCount()];
for (int i = 0; i < range.ColumnCount(); i++)
{
    for (int j = 0; j < range.RowCount(); j++)
    {
        objArray[i, j] = range.Cell(j+1, i+1).Value;
    }
}

Convert Interop to ClosedXML

1 Answers