Hi,I'm putting some text in a WorksheetMergedCellsRegion like so and would expect the row to adjust. But it doesn't. WorksheetMergedCellsRegion region = billingSheet.MergedCellsRegions.Add(cell.RowIndex, 1, cell.RowIndex, 3); region.Value = "- " + invoiceAnnotation.Description; region.CellFormat.Font.Bold = ExcelDefaultableBoolean.True; region.CellFormat.WrapText = ExcelDefaultableBoolean.True; region.CellFormat.RightBorderStyle = CellBorderLineStyle.Medium;Could you please tell me if I'm doing something wrong. Adjusting row height works for normal (unmerged) cells.Thanx!
Ok. But since I needed this, I wrote myself a little extension method that implements a 'workaround'.
It just sets the width of a dummy column to the total width of the region and copies the regions value to a cell in the same row. That way I let excel adjust the row height and then I simply hide the dummy column. The extension takes a columnIndex as a parameter, that way you can place the dummy column somewhere outside your actual data.
Works for me :)
Here is the code:
public static void AutoFitHeight( this WorksheetMergedCellsRegion region, int dummyColumnIndex) { // Set the width of the dummy column to the width of the region int width = region.Sum(worksheetCell => region.Worksheet.Columns[worksheetCell.ColumnIndex].Width); region.Worksheet.Columns[dummyColumnIndex].Width = width; region.CellFormat.WrapText = ExcelDefaultableBoolean.True; //Get the dummy cell for the row WorksheetCell dummyCell = region.Worksheet.Rows[region.FirstRow].Cells[20]; //Set the value dummyCell.Value = region.Value; dummyCell.CellFormat.WrapText = ExcelDefaultableBoolean.True; //hide the dummy column region.Worksheet.Columns[dummyColumnIndex].Hidden = true; }
This is just the way Excel works. If you open up Microsoft Excel, merged two cells in the same row, set Wrap Text, and type a string which is too long, the row height will not increase when the value is committed. It will only do this for wrapped values in single cells when the row height has not already been set.