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

Question

1.00/5 (1 vote)

See more:

I want the cells I write to expand or contract so that all their content displays without manually adjusting the width of the cells.

I tried this:

C#

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

...but it does nothing.

It might be because I've already monkeyed with the columns by spanning them earlier, like so:

C#

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;

Is this the problem? If so, how can I resolve it?

BTW, I am referencing Microsoft.Office.Interop.Excel

Identity = Microsoft.Office.Interop.Excel

Path = C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15\Microsoft.Office.Interop.Excel.dll

(I realize this can differ for you, but it might provide useful info)

Version: v2.0.50727

Posted 3-Nov-15 9:55am

B. Clay Shannon

Updated 3-Nov-15 9:58am

v2

Add a Solution

Comments

j snooze 3-Nov-15 17:46pm

are you doing the autofit before the data is in there or after? I put a quick demo together and the columns.autofit() is definitely working for me. I even tried merging a couple cells to see if that was it. Still autofits the data. I even removed the autofit code to verify it was messy without autofit and it was. I did have table like data displaying, do you have disjointed sets of data? (rows with spaces and more data?)

B. Clay Shannon 3-Nov-15 17:56pm

Before; I can also get it to work on a simple app, without the other manipulation as shown above.

j snooze 4-Nov-15 17:43pm

I would think you wouldn't autofit until after the data is there. Wouldn't be very efficient if excel had to resize and check column widths everytime new data is entered into a cell.

B. Clay Shannon 4-Nov-15 17:50pm

Good point. I'll change it and check what happens, although probably nothing yet.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)