Excel Buffer with extended formatting

AlexDen
AlexDen Member Posts: 86
in NAV Tips & Tricks
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.

Comments

  • Developer101
    Developer101 Member Posts: 569
    Works in NAV 2018. Only need changing the dotnet nav openxml variables from version 10.0.0.0 to 11.0.0.0
    United Kingdom
  • radek.bb
    radek.bb Member Posts: 49
    @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
    AlexDen Member Posts: 86
    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
    radek.bb Member Posts: 49
    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
    xavierho970304 Member Posts: 12
    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
    omerfaruk Member Posts: 16
    edited 2024-01-12
    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
    AlexDen Member Posts: 86
    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.

Categories