Excel Import

  • Resolved ckettl

    (@ckettl)


    1 week, 1 day ago

    Hi,

    I’m using the Excel import function and the FIXED command. The content of a cell is “=FIXED(1507.5,2,TRUE)” but the “true” addition will not work at all. It shows “1507.5”. Without “TRUE” “=FIXED(1507.5,2)” it works and shows “1,507.50” but I don’t want the 1000 separator. In Excel the fixed command works fine. Where is my mistake?

    Regards

    Christain

Viewing 4 replies - 1 through 4 (of 4 total)
  • Plugin Author Tobias Bäthge

    (@tobiasbg)

    1 week, 1 day ago

    Hi @ckettl,

    Thanks for your post and sorry for the trouble!

    Nice find! Indeed, it looks like the formula evaluation library that TablePress uses internally is not properly taking into account the Decimals parameter, when the No_commas parameter is set to true.

    I have created an issue in the formula evaluation library’s issue tracker, to make the developers aware of this. They’ll likely fix this shortly and I will then of course integrate this fix into TablePress!

    Best wishes,
    Tobias

    Plugin Author Tobias Bäthge

    (@tobiasbg)

    6 days, 19 hours ago

    Hi @ckettl,

    Good news! This will be fixed in TablePress 3.3.2, coming in about two weeks!

    In the meantime, if you already want to fix this on your site, so that you can run the import with a working implementation of the FIXED function, you can simply apply the change that is highlighted in this pull request in the file “/wp-content/plugins/tablepress/libraries/vendor/PhpSpreadsheet/Calculation/TextData/Format.php” on your site, e.g. via the “Plugin Editor” in WordPress.

    Thanks again for reporting this, I really appreciate it!

    Best wishes,
    Tobias

    Thread Starter ckettl

    (@ckettl)

    3 days, 23 hours ago

    Hi Tobias,

    thanks for your very quick response. It works very well.

    Unfortunately I found another issue using Excel import. I am using formulas in excel. As long as the cells “only” includes numbers everthing works well. Example: cell $A$1 includes “=A2+A3”. As long as cells A2 and cell A3 is 1 the result in A1 is 2. Now I want A2 printed in bold so A2 includs “<strong>1</strong>. Then the result in $A$1 fails. Cells used in formulas must not include any other information but numbers.

    Regards,

    Christian

    Plugin Author Tobias Bäthge

    (@tobiasbg)

    3 days, 20 hours ago

    Hi @ckettl,

    Yes, that’s correct. Unfortunately, it’s not possible to have HTML tags in cells which are then used for calculations as well.

    The workaround that I recommend for is to separate the formula calculation and the formatting: Basically, duplicate the rows and hide the first one (via the “Selected rows: Hide” button). In that row, only use plain numbers for the calculations. Then, in the second row, only apply the formatting with a cell content like <strong>{=F4}</strong> (assuming that “4” is the first (hidden) row with the plain number and/or calculation results).

    Best wishes,
    Tobias

Viewing 4 replies - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.