We help IT Professionals succeed at work.

Workaround for Excel not Wrapping Text in Merged Cells

TimAttaway
TimAttaway used Ask the Experts™
on
I am creating a spreadsheet using Open Office XML.  The spreadsheet contains merged cells that contain text.  In many cases, the text is longer than the size of the merged cells so it needs to wrap.  I have the cell style set to wrap, but Excel ignores it.  From searching on the internet, it seems that Excel ignoring "wrap' on merged cells is a long standing problem.  The only workarounds that I see offered are to manually resize the rows, which would not be a problem if I were creating the spreadsheet manually.

Does anybody know a way to programmatically assess the size of a text string to figure out what the height of the row needs to be?  Given a string, a font, a font size, font characteristics (bold, italics, whatever), and the width of the cell (in points or pixels), how can I compute what the height of row containing the cell needs to be so that I can see the entire string?

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I found the Graphics.MeasureString method, which looked promising, but I need something that will work in a class that is not necessarily bound to any particular control.  It will be running on a web server.

Commented:
Check this file for column Size
size.xlsm

Commented:
This one showing according to font
size.xlsm

Author

Commented:
Thanks, but this does not really have anything to do with the question that I asked. I need something that can run on a server creating the sheet using open office xml, not a macro that runs inside the spreadsheet.
Actually I figured it out.  I'll document the result in case someone else needs to know.  The routine below will return the height of the string after it is fitted into the box with width "columnWidth".  It assumes that the string will fit into a box the size of columnWidth X 1000.  Use a bigger number if needed.  The size that is returned will be expressed in whatever units are specified in the routine, i.e. "g.PageUnit = GraphicsUnit.Point;".  Change that to a different unit if needed.
 
internal static float MeasureAString(string what, 
                                             float columnWidth, 
                                             string theFont, 
                                             int theFontSize, 
                                             bool isItBold, 
                                             bool isItItalics,
                                             bool isItUnderlined)
        {
            Graphics g = Graphics.FromImage(new Bitmap(1, 1));
            FontStyle theFontStyle = FontStyle.Regular;
            if (isItBold) { theFontStyle = theFontStyle | FontStyle.Bold; }
            if (isItItalics) { theFontStyle = theFontStyle | FontStyle.Italic; }
            if (isItUnderlined) { theFontStyle = theFontStyle | FontStyle.Underline; }
            Font stringFont = new Font(theFont, theFontSize, theFontStyle);
            SizeF layoutSize = new SizeF(columnWidth, 1000.0F);
            StringFormat newStringFormat = new StringFormat();
            SizeF stringSize = new SizeF();
            g.PageUnit = GraphicsUnit.Point;
            stringSize = g.MeasureString(what, stringFont, layoutSize, newStringFormat);
            return stringSize.Height;
        }

Open in new window

Author

Commented:
I solved this issue myself.  I am posting the results in case they may be useful to someone else.