1

createXLSX: format column as currency

Hi,

how can I format a column as currency with the new function createXLSX?

Thanks in advance.

15 replies

null
    • Fred
    • 6 mths ago
    • Reported - view

    You would do it using Ninox format() command when you create your JSON data, instead of in the createXLSX() command. From the looks of it you can't do any number formatting in createXLSX().

    • Puff
    • 6 mths ago
    • Reported - view

    Thanks, yes I know the format function. But the result will be a string, not a number / real. So no SUM-function in the sheet will work. Without the numbers are shown without two decimals, that's looks not very nice.

      • Fred
      • 6 mths ago
      • Reported - view

      playing around, I can get the .00 at the end of my numbers and that transfers to the the XLSX sheet:

      but I can't seem to figure out formulas yet.

    • Puff
    • 6 mths ago
    • Reported - view

    Yes, you can format the numbers in this way. But now they are strings! So, no way to make computing with it.

    • Fred
    • 6 mths ago
    • Reported - view

    If you have time, next Thursday, Nioxus will hold their weekly Zoom seminar and will be going in-depth with createXLSX(). You can sign up with this link.

    • terri.1
    • 6 mths ago
    • Reported - view

    Here is the free registration link for the Ninox Learning Lab which is offered most Thursdays at 12:00pm EDT and this Thursday, May 16th the topic is EXPORTING FORMATTED EXCEL SPREADSHEETS: https://us02web.zoom.us/webinar/register/WN_suzu4kttTqeKTRv9O6mifA

    www.nioxus.com

    support@nioxus.com

    • Fred
    • 6 mths ago
    • Reported - view

    Well, according to Andy at Nioxus the Ninox docs for createXLXS() are not completely accurate. In addition he talked about a format function that is not defined in the docs as well. Ninox is aware that the docs are incomplete and will update it. Hopefully soon, we just won't know when they do.

    • NIOXUS
    • 6 mths ago
    • Reported - view

    Check out the new exportXLSX training videos on YouTube. At the end of part IV we talk about formatting currency.

      • Fred
      • 6 mths ago
      • Reported - view

      I just looked at video 130 and you only talk about making column G right justified. You don't mention anything about setting it to Currenty or Date with in createXLSX().

      • Puff
      • 6 mths ago
      • Reported - view

       Can you please send a link to the video? Thank you.

    • NIOXUS
    • 6 mths ago
    • Reported - view

    The only thing you need to do to have your dates come out correctly is to use the text() tag inside your JSON code to ensure that the date content is rendered as text in Excel

    Regarding the numeric/currency values, do all of your formatting in Ninox in terms of currency symbols, decimal and thousand separators, etc. Once you have the number field appearing in Ninox as it should, then either save the content as text() in a Ninox formula field and refer to that field within the rows definition in JSON or, again, use the text() modifier in the JSON code block where you define the rows so that you can retain all of your formatting in Excel. The other thing you will want to do is, when defining the column header section for the number/currency columns, make sure to hard-code a "right" horizontal alignment.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 6 mths ago
      • Reported - view

       

      Work Perfect

      • Puff
      • 6 mths ago
      • Reported - view

       Thanks, but are you sure that these columns can be calculated using a sum function?

      • terri.1
      • 6 mths ago
      • Reported - view

       Hi… so glad you could join the Ninox Learning Lab this last Thursday!  We answered this in class but here is the answer:  Depending on how you send the data from Ninox to excel,  you may need to either convert text to numbers in excel or format excel to represent the numbers as currency using the appropriate symbol.

Content aside

  • 1 Likes
  • 6 mths agoLast active
  • 15Replies
  • 184Views
  • 5 Following