Merged cells not stretching to show all it content

Hi!

I’m trying to populate an excel template with some data from my winform, the richtextbox that has the content is large, with a lot of lines, im trying to make it fit merged rows of the same column, but doesnt matter how many rows i merge, it just stretch to a limit that doesnt show the whole content.

I tried the Autofit() but it doesnt stretch all the merged cells, just the first one, even if i add the Autofit() to all the merged cells.

For example:

I have a merged cell at B18 to B20, if we stretch each line manually of these merged cells over excel, it will get to a great Height, but if i add the value from gembox.spreadsheet and add the autofit to B18 or B18, B19 and B20, it wont get to the same Height as the manual method.

I appreciate any hints about it.

Hi Ricardo,

Can you send us your Excel file so that we can take a look at it?
Perhaps you could try using CellRange.AutoFitRowHeight(Boolean).

I hope this helps.

Regards,
Mario

Hi!

this is the link of the file (shared over google drive):

In this document, im using richtextbox componentes on a winforms to populate the rows of A18, B18, C18, D18, E18, F18, G18 (and it merged cells), but the height just go as it was just 2 merged rows, not 8 merged rows as it is in the document.

i tried using
“worksheet.Cells.AutoFitRowHeight(true);”
and
“worksheet.Cells.AutoFitRowHeight(autoFitMergedRanges: true);”
but got no new results.

Any help is appreciated.

Hi Ricardo,

That should work, here is what I tried:

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

var random = new Random();
foreach (var cell in worksheet.Cells.GetSubrange("A18:G18"))
    cell.Value = string.Concat(Enumerable.Repeat("Sample text. ", random.Next(10, 50)));

worksheet.Cells.AutoFitRowHeight(true);

workbook.Save("output.xlsx");

And here is the resulting “output.xlsx”:

Can you send us a small Visual Studio project that reproduces your issue so that we can investigate it?

Also just as an FYI, the alternative approach that you could try is instead of using merged cells, you just increase the height of the row “18”, like this:

With this you will be able to just use the AutoFit on rows:

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

var random = new Random();
foreach (var cell in worksheet.Cells.GetSubrange("A18:G18"))
    cell.Value = string.Concat(Enumerable.Repeat("Sample text. ", random.Next(10, 50)));

foreach (var row in worksheet.Rows)
    row.AutoFit();

workbook.Save("Output_Without_Merged_cells.xlsx");

I hope this helps.

Regards,
Mario

Hi!

Thanks for replying!

The problem appear when there is a huge amount of data to be placed there. Like the print below.

I dont know if you can see it properly, but only one cell strech, the others stay the same default height and the full content of the cell isnt visible, missing some part.

Ill try to make a small project to reproduce this situation.

Thanks!

Hi Ricardo,

Yes, that is expected.
You see, Microsoft Excel doesn’t support auto-fitting merged cells.
GemBox.Spreadsheet supports it in that way, by increasing just the first row.

Also, note that the row’s height cannot be increased infinitely.
You’ll need to split your content into multiple cells (rows).

Regards,
Mario

Hi!

Ohh that explain what is being happening. Im so sorry for using your time for this mistake, ill rewrite my code to bypass that limitation.

Thanks a lot for it.