How to determine cell width based on cell contents?

[sliuParticipant]

Hi Eric,

In Excel, when double clicking on a column divider, Excel automatically resizes the width of the column based on its contents in this column. How do we do this in OpenXml?

Thanks in advance!!!

[sliuParticipant]

I meant how to calculate the width of a column so that when creating Spreadsheet via OpenXml the column width could be set reasonably.

Thanks!

[Jim SnyderParticipant]

Try this link as a starter:
https://social.msdn.microsoft.com/Forums/office/en-US/1d93eca8-2949-4d12-8dd9-15cc24128b10/trouble-added-columns-to-define-width-using-openxml?forum=oxmlsdk

Vincent Tan wrote about this. Unfortunately, he no longer participates:
http://polymathprogrammer.com/2010/01/11/custom-column-widths-in-excel-open-xml/

I personally haven’t done it, but the links are better than no answer.

[sliuParticipant]

In these two posts, they already knew the width of the columns. My question is how to determine the width of columns. For example, I have a string “Try this link as a starter”. What is the column width that can show all the words in the cell.

Thanks for replying

[Jim SnyderParticipant]

You didn’t read far enough:

There, you have your widths. Go back to your code and plug that in. Tada, mission accomplished.

“Wait, so why can’t those widths be calculated?”

Well, they can. I just think the effort’s not worth it. First, you need to know the font of the text in that cell/column. Then there’s some weird truncation calculation based on the width of characters in that font… You know what, here’s an extract from the Open XML SDK documentation:

width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] / {Maximum Digit Width} * 256) / 256

[sliuParticipant]

Ok, got it. OpenXml has the calculation.

Thanks!

[Jim SnyderParticipant]

You are welcome! I am still learning it myself…

[Author]

Posts