Excel Buffer with extended formatting

AlexDen

Hello all,

I would like to share a solution that allows to export data to excel via "Excel Buffer" with extended formatting: alignment, cell borders and etc.
You can find source code with code samples on GitHub:
It doesn't require any additional components, it uses only OpenXML components for extended formatting.

This solution is developed for Nav 2017 version, but I think that some functions can be easily transferred to other versions.

Developer101

Works in NAV 2018. Only need changing the dotnet nav openxml variables from version 10.0.0.0 to 11.0.0.0

radek.bb

@AlexDen
By a chance... Have you tried to implement AutoFit for your openxml solution?
I found this and it looks like it is not too easy...
https://stackoverflow.com/questions/31197038/how-to-autofit-excel-column-using-openxml-package

AlexDen

Hi,

No, I haven't tried it.

You can try to find cell with maximum number of characters in the column and set width for it as STRLEN("Cell Value as text")*7+5 (if you use default font size).
But it will not work for formulas as the result will be known only after calculation.

radek.bb

Thanks anyway
Your solution woks great. I updated it to match latest BC version of standard Excel Buffer. Now every object using standard can switch to using new solution only by changing table number of Buffer variable.

AutoFit is a 'nice to have' anyway.
It is a bit overkill as to do it in proper way we would need to wait until CloseBook and then re-open it, re-read with recalculation of all formulas and then find a proper width/height for each column/row.
Does not sound like worth the hassle.
Another way would be to wait for closing the book and re-open it in actual Excel at client - and then just call auto fit for all sheets - but I prefer to stay within Open XML.

Cheers

xavierho970304

Hi guys, may I know how to use excel buffer to set column width and wrap text for a specific column? I using BC version

omerfaruk

Please vote on this idea to have these awesome properties on BC SaaS as well:

https://experience.dynamics.com/ideas/idea/?ideaid=268af51c-6bb1-ee11-92bd-000d3a0f8799

AlexDen

It seems Microsoft has no plans to extend the current "Excel Buffer" functionality. So, I implemented an example of how it is possible to create an Excel file in BC Cloud with extended formatting.