RFC: add option to automatically adjust the cell sizes

[set-soft]

Hi @hildogjr , @devbisme and any wanting to collaborate:

The topic

I'm commiting a new functionality to spreadsheet.py. This functionality is oriented to adjust the cell sizes in order to make their content more readable without needing any user intervention.

I guess this isn't the only way to solve this problem, perhaps a macro could trigger some command to force it, I don't know. This is one of the reasons to ask for comments.

KiBot does it with its BoM output, so I'm adding some mechanism to KiCost. By default is disabled. It can be enabled from KiBot. I can add a command line option to enable it, please comment about it.

How it affects the spreadheet

I generated two versions of the spreadsheet related to the acquire-PWM. I selected it randomly, but turned out to be an excellent example.

Here is the XLSX loaded with LibreOffice 7.0.4 without adjust:

And here is the same file with adjust:

Again: this is how it looks without any user intervention, just load it.

Here is the same file using Google Docs, no adjust:

With adjust:

Same file using Excel from online Office 365, no adjust:

With adjust:

IMHO is much better the adjusted result using the three applications.

Again: maybe there is a better solution, but it must work for all major spreadsheets.

Comments?

[hildogjr]

@set-soft, I liked! I tried this some time ago but the method that I have used worked just in one/two of the three office suites. Also better code definition and class use, as you started to, provides better maintenance.

Macro could be trick, since each office may use one language and it will depend on user macro permissions.

Some history: I remember that I would like a new color highlight on "Ext$" column for show that "next price tier is cheaper" (for example to purchase 10 instead 9 units of some part). But come out that I needed a "conditional formatting using LOOKUP function and the got price tiers" that just worked at Google Sheets and LibreOffice...

[set-soft]

Hi @hildogjr ! I can add a command line option to enable it. Keeping the current behavior as default. I thought about an option called --max_col_width when used it enables the adjust using the provided value as limit. What do you think?

BTW: I was thinking about the posibility of having an HTML output. Is too much work, and I don't know enough Java Script to implement it by my self, but it could enable very interesting things. Like more complex operations you mention.

[hildogjr]

I didn't test this branch yet, I was thinking that --max_col_width could be default active with some default value (this new visualization is better than the old) and maybe doing --max_col_width=off to return to the old behavior.

I just know enough HTML and Java Script for web scraping and data science.

[set-soft]

I didn't test this branch yet, I was thinking that --max_col_width could be default active with some default value (this new visualization is better than the old) and maybe doing --max_col_width=off to return to the old behavior.

What about using 0 to disable it? In this way the argument is always an integer. Otherwise it must be an string and then we must validate it.

I just know enough HTML and Java Script for web scraping and data science.

If you have some time (and motivation) it would be nice to take a small KiCost example in XLSX format and try to generate an equivalent HTML. The aspect isn't relevant, if we use CSS for all the tags. The most important thing to solve are the equations, they must be coded in JavaScript and linked to the cells using the content change events. Only the cells that the user could need to change should be input texts, the others should be static, or changed by the JavaScript code.

If we manage to get a template coding it on Python is simple, I can do this.

[hildogjr]

Agree to the integer/zero input.

I am thinking how to re-use or code some common code between the HTML/XLSX outputs. If possible to interpret the Excel formulas into JavaScript or at least create HTML/XLSX template files into KiCost path...