How to determine cell width based on cell contents?

Home › Forums › SpreadsheetML › How to determine cell width based on cell contents?

Tagged: ,

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • September 26, 2016 at 8:47 pm #3841
    sliu
    Participant

    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!

    September 27, 2016 at 1:14 pm #3844
    Jim Snyder
    Participant

    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.

    September 27, 2016 at 6:10 pm #3846
    sliu
    Participant

    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

    September 27, 2016 at 6:14 pm #3847
    Jim Snyder
    Participant

    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

    September 27, 2016 at 7:15 pm #3848
    sliu
    Participant

    Ok, got it. OpenXml has the calculation.

    Thanks!

    September 27, 2016 at 7:17 pm #3849
    Jim Snyder
    Participant

    You are welcome! I am still learning it myself…

  • Author
    Posts
Viewing 6 posts - 1 through 6 (of 6 total)
  • You must be logged in to reply to this topic.