xou/elixlsx

Setting "automatic" width of a column

Opened this issue · 6 comments

andershansson commented

This is more a question or maybe a suggestion for improvement.

Is it possible to set the column width to automatically adapt to the width of the content of the data in the cells? In Excel/LibreOffice you can easily adapt the width by double clicking the resize handle of the column but is there any way to programatically achieve this without a hardcoded width?

unti1x commented

I thought it'd be something wild, because office programs rely on font size to calculate column width, and it's quiet difficult to do it in Elixir (I guess we'd have to use Erlang's gs module, it wouldn't be super fast and it won't work without font files). But after making some research I found an option named bestFit for a col tag. I'm going to dig into rfcs but it looks like this is what we actually need. At least PHPExcel uses it https://github.com/PHPOffice/PHPExcel/blob/master/Classes/PHPExcel/Writer/Excel2007/Worksheet.php#L397

  • 👍3
  • 🎉1
kuon commented

If this can be done without measuring font, I think it's good, otherwise it's a bad idea.

unti1x commented

Well, yes and no. Option bestFit exists, but not all the office applications understand it. PHPExcel and some ruby libraries write it with default width. I'm stuck in the middle of process because sources came pretty messy. And now I'm out of free time to finish it. Going to continue as soon as possible.

kuon commented

I really feel that measuring the text is really a bad idea as platforms have different font rendering, it would include a huge dependency, and it will be slow.

I also think that this should be let to the library user. Imagine a column with 90% of items within 100px wide, but 10% above that, only the library user can decide if 100px is a good fit or not.

If you have some code measuring text, I would include it in the README, and let people start from it.

  • 👍1
dzfranklin commented

It seems like the width unit is the width of some letter in the default font. Some people on StackOverflow set the column width to the max cell length (in characters) in the column times 1.25. This isn't ideal, but neither is defaulting to the default column width.

What about a method that uses this rough approximation to set the size of every column that doesn't have an explicitly set width?

unti1x commented

There is no need in this, just take a look at my first comment. Unfortunately I'm out of spare time right now and I didn't use Elixir for a while, so y'all are free to submit your own PRs