How can I get Excel cells to "right-size" to their content?

Question

I want the Excel spreadsheet cells I populate with C# to expand or contract so that all their content displays without manually adjusting the width of the cells - displaying at "just enough" width to display the data - no more, no less.

I tried this:

_xlSheet = (MSExcel.Excel.Worksheet)_xlSheets.Item[1];
_xlSheet.Columns.AutoFit();
_xlSheet.Rows.AutoFit();

...but it does nothing in my current project (it works fine in a small POC sandbox app that contains no ranges). Speaking of ranges, the reason this doesn't work might have something to do with my having created cell ranges like so:

var rowRngMemberName = _xlSheet.Range[_xlSheet.Cells[1, 1], _xlSheet.Cells[1, 6]];
rowRngMemberName.Merge(Type.Missing);
rowRngMemberName.Font.Bold = true;
rowRngMemberName.Font.Italic = true;
rowRngMemberName.Font.Size = 20;
rowRngMemberName.Value2 = shortName;

...and then adding "normal"/generic single-cell values after that.

In other words, I have values that span multiple columns - several rows of that. Then below that, I revert to "one cell, one value" mode.

Is this the problem?

If so, how can I resolve it?

Is it possible to have independent sections of a spreadsheet whose formatting (autofitting) isn't affected by other parts of the sheet?

UPDATE

As for getting multiple rows to accommodate a value, I'm using this code:

private void AddDescription(String desc)
{
    int curDescriptionBottomRow = curDescriptionTopRow + 3;

    var range =
        _xlSheet.Range[_xlSheet.Cells[curDescriptionTopRow, 1], _xlSheet.Cells[curDescriptionBottomRow, 1]];
    range.Merge();

    range.Font.Bold = true;
    range.VerticalAlignment = XlVAlign.xlVAlignCenter;
    range.Value2 = desc;
}

...and here's what it accomplishes:

Have you tried using the following at the end of your code after performing all of the inserts? The AutoFit function only autofits to the 'current' size of the cells, it doesn't autofit values that get entered in the future. _xlSheet.UsedRange.Columns.AutoFit(); — Kris B, Nov 05 '15 at 21:15
Another thing I noticed is you appear to be using C# interop. That library is very difficult to use. Meaning it can easily leave dangling references of excel hanging in the background if you don't manage the objects correctly. Please refer to the link here: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects — Kris B, Nov 05 '15 at 21:17
Kris B - make your comment an answer and I'll accept it. Moving the autofit code until after populating the sheet worked. — B. Clay Shannon-B. Crow Raven, Nov 10 '15 at 22:19

score 1 · Accepted Answer · answered Nov 17 '15 at 16:31

1

AutoFit is what is needed, after all, but the key is to call it at the right time - after all other manipulation has been done. Otherwise, subsequent manipulation can lose the autofittedness.

answered Nov 17 '15 at 16:31

B. Clay Shannon-B. Crow Raven

8,547
144
472
862

score 0 · Answer 2 · answered Nov 05 '15 at 21:11

0

If I get what you are asking correctly you are looking to wrap text... at least thats the official term for it...

 xlWorkSheet.Range["A4:A4"].Cells.WrapText = true;

Here is the documentation: https://msdn.microsoft.com/en-us/library/office/ff821514.aspx

answered Nov 05 '15 at 21:11

Ya Wang

1,758
1
19
41

No, I want the header info to span several columns, but columns below that to not stretch out from Timbuktu to Tehran. – B. Clay Shannon-B. Crow Raven Nov 05 '15 at 21:23
1

@B.ClayShannon You might want to consider merge and center. http://stackoverflow.com/questions/532199/merging-cells-in-excel-using-c-sharp – Ya Wang Nov 05 '15 at 21:28
I am doing merging (recently, I'm an Excel newbie); my current problem is horizontal, not vertical. I simply want all cells to show all their data, without having to 2-click in their gutter. ISTM that this would be the default behavior, for who wants truncated data? – B. Clay Shannon-B. Crow Raven Nov 05 '15 at 21:37
@pnuts: Merge is how I was able to get a label to span four rows and be vertically centered; how can that be accomplished without Merge? – B. Clay Shannon-B. Crow Raven Nov 05 '15 at 22:33
I will update my question with the code I'm using and what it produces. – B. Clay Shannon-B. Crow Raven Nov 05 '15 at 22:56

How can I get Excel cells to "right-size" to their content?

UPDATE

2 Answers2