facebooktwittermenuarrow-up

GemBox Support Forum

Equivalent to Transpose

Hi,

Is there an equivalent feature to transpose in Excel.Interop?

Thanks,
Shlomi

Hi Shlomi,

There is no direct API for this, but nevertheless, you could create it.
For example, something like this:

public static void CopyToTranspose(this CellRange source, ExcelWorksheet destinationSheet, string topLeftCell)
{
    CellRange.PositionToRowColumn(topLeftCell, out int row, out int column);
    var destination = destinationSheet.Cells.GetSubrangeRelative(row, column, source.Height, source.Width);

    if (source[0].Worksheet == destinationSheet && source.Overlaps(destination))
        throw new InvalidOperationException();

    for (int r = 0; r < source.Height; r++)
        for (int c = 0; c < source.Width;c++)
        {
            ExcelCell sourceCell = source[r, c];
            ExcelCell destinationCell = destination[c, r];

            destinationCell.Value = sourceCell.Value;
            destinationCell.Style = sourceCell.Style;
        }
}

Also, here is how you can use that extension method:

static void Main()
{
    var workbook = ExcelFile.Load("input.xlsx");

    var worksheet1 = workbook.Worksheets[0];
    var range = worksheet1.Cells.GetSubrange("A1:C5");

    var worksheet2 = workbook.Worksheets[1];
    range.CopyToTranspose(worksheet2, "B2");

    workbook.Save("output.xlsx");
}

I hope this helps.

Regards,
Mario

thanks. it helps a lot.