Created attachment 30044 [details] DataFormatter changes Some Excel formats are displayed incorrect in the application (fractional, accounting and currency formats). For example, this formats displays incorrect: Category - Fraction: 1/4 (Up to one digit (1/4) cell type) --> 0/ 21/25 (Up to two digits (21/25) cell type) --> 1/ 1/2 (As halves (1/2) cell type) --> 0/2 2/4 (As quarters (2/4) cell type) --> 0/4 Category - Accounting: 0,001000000 (Decimal places - 9, Symbol - р.) --> 0,00 $ 1,234567890 (Decimal places - 9, Symbol - $English(Canada)) --> 1,23 Category - Currency: 0,001000000 (Decimal places - 9, Symbol - р.) --> 0,00 $ 1,234567890 (Decimal places - 9, Symbol - $English(Canada)) --> 1,23
Are you able to work up a quick unit test to go with this? Just something that tries to format a couple of different numbers with each of the formats. That way, we'll know it's fixed, and we'll also be able to make sure it doesn't get broken in future!
Created attachment 30430 [details] 3/4's of a patch, might be good enough Thanks to Ryan Krueger and https://issues.apache.org/jira/browse/TIKA-1132 for the custom formatting test case on fractions. I added some test cases, and offer a 3/4's patch. This patch passes 3148 tests and fails on 38. Without the patch, there are 3062 failurs and 124 successes. I refactored the fraction calculator to avoid the n^2 calculations that could lead to 100,000,000 calculations if the format was ##/####. By "refactored," I mean copied and pasted from org.apache.math.fraction.
We debated adding a dependency on commons math, but decided against it. Copying their code is probably more sensible, though someone came up with a contribution which we accepted in the mean time... Hopefully someone with slightly stronger math skills can offer an opinion on your suggested fix!
Created attachment 30431 [details] patch for fraction format Completely agree. Should have stepped away from the computer before submitting the last. This passes all tests. Still only handles fraction formatting.
Thanks for this patch Tim! For the unit test, I renamed it to TestFractionFormat + changed package, and updated how it gets the test text file to be consistent with the xls, as well as re-enabling the single test One thing I've just found - inner class Fraction of CellNumberFormat - seems to do something similar. Any chance you could cast an eye over it, and decide if we can rationalise / refactor to just have the one? Patch applied (with tweaks described above) in r1494986, thanks
(In reply to Nick Burch from comment #5) > Thanks for this patch Tim! > > For the unit test, I renamed it to TestFractionFormat + changed package, and > updated how it gets the test text file to be consistent with the xls, as > well as re-enabling the single test > > One thing I've just found - inner class Fraction of CellNumberFormat - seems > to do something similar. Any chance you could cast an eye over it, and > decide if we can rationalise / refactor to just have the one? > > Patch applied (with tweaks described above) in r1494986, thanks Thank you, Nick. Will do.
Created attachment 30981 [details] Fraction cells in 2003 format Adding an excel sheet suitable for use as test of fraction formatting, HTH
Thank you, Chris. POI-55419 covers the examples you offered. Fraction formatting is still not perfect...there are some rounding differences btwn Java and MSExcel...but I think the fraction component of this issue should be closed. I haven't had a chance to look into the currency and accounting items that are raised by this issue. If you have a need and POI is failing for you in those areas, please submit a file that shows those problems. Thank you, again!
Just in case it isn't clear - we're still waiting on some new tests (unit test + sample file) that shows up the last remaining parts of this issue around currenct / accounting formatting With those, it should be possible to investigate what logic changes are still needed, then work on those.
The currency / accounting pieces may have been solved by the work for #58536, or at least partially, would be great if someone could check and write a unit test for any parts that remain outstanding!