How to Merge cells based on content size

GemBox.Spreadsheet
1

Hi, I am trying to populate UI data to excel spreadsheet and I am stuck with a problem.

Suppose I want to populate colum A with content which can take upto 2000 characters. Currently how I am doing this is, populating the content to A1 cell and wrapping the text but the problem is when I wrap the text it increases the entire row height which I don’t want. Because Column B,C so on has some data which is not dependent on Column A and the unnecessary space is odd. (screenshot attached)

So I want something like, based on the content size can I start populating from cell A1 and merge all cells below it to fit the exact rows which is required to populate all data?

One way is to fix the rows and merge to max rows but I want it to be dynamic and not have any empty spaces.

Please let me know if there is any better way to do it.

20211031_172341
20211031_1723411080×1729 246 KB

2

Hi,

Try this:

var workbook = ExcelFile.Load("input.xlsx");
var worksheet = workbook.Worksheets[0];

for (int rowIndex = 0; rowIndex < worksheet.Rows.Count; ++rowIndex)
{
    var row = worksheet.Rows[rowIndex];
    var cell = row.Cells[0];

    if (cell.MergedRange != null)
        continue;

    int currentRowHeight = row.Height;

    cell.Style.WrapText = true;
    cell.Row.AutoFit();

    int desiredRowHeight = row.Height;
    row.Height = currentRowHeight;

    if (currentRowHeight >= desiredRowHeight)
        continue;

    int rowCounter = 0;
    while (currentRowHeight < desiredRowHeight)
    {
        ++rowCounter;
        currentRowHeight += worksheet.Rows[rowIndex + rowCounter].Height;
    }

    worksheet.Cells.GetSubrangeRelative(row.Index + 1, cell.Column.Index, 1, rowCounter).Insert(InsertShiftDirection.Down);
    worksheet.Cells.GetSubrangeRelative(row.Index, cell.Column.Index, 1, rowCounter + 1).Merged = true;
}

workbook.Save("output.xlsx");

Does this work for you?

Regards,
Mario