CODEWITHSUNDEEP
c#exceloffice-interopxlsxexcel-interop
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10247

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

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:

enter image description here

Upvotes: 0

Views: 829

Answers (2)

B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10247

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.

Upvotes: 1

Ya Wang
Ya Wang

Reputation: 1808

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

Upvotes: 0

Related Questions