summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorxuri <xuri.me@gmail.com>2022-03-19 00:05:47 +0800
committerxuri <xuri.me@gmail.com>2022-03-19 00:05:47 +0800
commit94f197c4fe6531f96a42fe4e960c1c921a3ee0e8 (patch)
tree1506b7d9e7584257768843e7723af2208fe6ce20
parent14b461420fc3d3b06b01d7b0584b422b3e1b40fb (diff)
This improved formula calculate precision and added zero placeholder number format support
Diffstat
-rw-r--r--.github/workflows/go.yml2
-rw-r--r--calc_test.go200
-rw-r--r--cell.go22
-rw-r--r--file.go2
-rw-r--r--lib.go7
-rw-r--r--lib_test.go4
-rw-r--r--numfmt.go52
-rw-r--r--numfmt_test.go8
-rw-r--r--picture_test.go4
-rw-r--r--rows.go7
10 files changed, 178 insertions, 130 deletions
diff --git a/.github/workflows/go.yml b/.github/workflows/go.yml
index b984114..8310222 100644
--- a/.github/workflows/go.yml
+++ b/.github/workflows/go.yml
@@ -5,7 +5,7 @@ jobs:
test:
strategy:
matrix:
- go-version: [1.15.x, 1.16.x, 1.17.x]
+ go-version: [1.15.x, 1.16.x, 1.17.x, 1.18.x]
os: [ubuntu-latest, macos-latest, windows-latest]
targetplatform: [x86, x64]
diff --git a/calc_test.go b/calc_test.go
index 6f08554..d350037 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -65,19 +65,19 @@ func TestCalcCellValue(t *testing.T) {
`="A"<>"A"`: "FALSE",
// Engineering Functions
// BESSELI
- "=BESSELI(4.5,1)": "15.389222753735925",
+ "=BESSELI(4.5,1)": "15.3892227537359",
"=BESSELI(32,1)": "5502845511211.25",
// BESSELJ
"=BESSELJ(1.9,2)": "0.329925727692387",
// BESSELK
"=BESSELK(0.05,0)": "3.11423403428966",
- "=BESSELK(0.05,1)": "19.90967432724863",
+ "=BESSELK(0.05,1)": "19.9096743272486",
"=BESSELK(0.05,2)": "799.501207124235",
"=BESSELK(3,2)": "0.0615104585619118",
// BESSELY
"=BESSELY(0.05,0)": "-1.97931100684153",
- "=BESSELY(0.05,1)": "-12.789855163794034",
- "=BESSELY(0.05,2)": "-509.61489554491976",
+ "=BESSELY(0.05,1)": "-12.789855163794",
+ "=BESSELY(0.05,2)": "-509.61489554492",
"=BESSELY(9,2)": "-0.229082087487741",
// BIN2DEC
"=BIN2DEC(\"10\")": "2",
@@ -213,7 +213,7 @@ func TestCalcCellValue(t *testing.T) {
"=IMCOSH(\"2-i\")": "2.0327230070196656-3.0518977991518i",
"=IMCOSH(COMPLEX(1,-1))": "0.8337300251311491-0.9888977057628651i",
// IMCOT
- "=IMCOT(0.5)": "1.830487721712452",
+ "=IMCOT(0.5)": "1.83048772171245",
"=IMCOT(\"3+0.5i\")": "-0.4793455787473728-2.016092521506228i",
"=IMCOT(\"2-i\")": "-0.171383612909185+0.8213297974938518i",
"=IMCOT(COMPLEX(1,-1))": "0.21762156185440268+0.868014142895925i",
@@ -247,7 +247,7 @@ func TestCalcCellValue(t *testing.T) {
"=IMREAL(\"3i\")": "0",
"=IMREAL(COMPLEX(4,1))": "4",
// IMSEC
- "=IMSEC(0.5)": "1.139493927324549",
+ "=IMSEC(0.5)": "1.13949392732455",
"=IMSEC(\"3+0.5i\")": "-0.8919131797403304+0.05875317818173977i",
"=IMSEC(\"2-i\")": "-0.4131493442669401-0.687527438655479i",
"=IMSEC(COMPLEX(1,-1))": "0.49833703055518686-0.5910838417210451i",
@@ -271,7 +271,7 @@ func TestCalcCellValue(t *testing.T) {
"=IMSQRT(\"i\")": "0.7071067811865476+0.7071067811865476i",
"=IMSQRT(\"2-i\")": "1.455346690225355-0.34356074972251244i",
"=IMSQRT(\"5+2i\")": "2.27872385417085+0.4388421169022545i",
- "=IMSQRT(6)": "2.449489742783178",
+ "=IMSQRT(6)": "2.44948974278318",
"=IMSQRT(\"-2-4i\")": "1.1117859405028423-1.7989074399478673i",
// IMSUB
"=IMSUB(\"5+i\",\"1+4i\")": "4-3i",
@@ -313,17 +313,17 @@ func TestCalcCellValue(t *testing.T) {
"=ABS(2-4.5)": "2.5",
"=ABS(ABS(-1))": "1",
// ACOS
- "=ACOS(-1)": "3.141592653589793",
+ "=ACOS(-1)": "3.14159265358979",
"=ACOS(0)": "1.5707963267949",
"=ACOS(ABS(0))": "1.5707963267949",
// ACOSH
"=ACOSH(1)": "0",
- "=ACOSH(2.5)": "1.566799236972411",
+ "=ACOSH(2.5)": "1.56679923697241",
"=ACOSH(5)": "2.29243166956118",
"=ACOSH(ACOSH(5))": "1.47138332153668",
// ACOT
"=_xlfn.ACOT(1)": "0.785398163397448",
- "=_xlfn.ACOT(-2)": "2.677945044588987",
+ "=_xlfn.ACOT(-2)": "2.67794504458899",
"=_xlfn.ACOT(0)": "1.5707963267949",
"=_xlfn.ACOT(_xlfn.ACOT(0))": "0.566911504941009",
// ACOTH
@@ -445,9 +445,9 @@ func TestCalcCellValue(t *testing.T) {
`=_xlfn.DECIMAL("70122",8)`: "28754",
`=_xlfn.DECIMAL("0x70122",8)`: "28754",
// DEGREES
- "=DEGREES(1)": "57.29577951308232",
- "=DEGREES(2.5)": "143.2394487827058",
- "=DEGREES(DEGREES(1))": "3282.806350011744",
+ "=DEGREES(1)": "57.2957795130823",
+ "=DEGREES(2.5)": "143.239448782706",
+ "=DEGREES(DEGREES(1))": "3282.80635001174",
// EVEN
"=EVEN(23)": "24",
"=EVEN(2.22)": "4",
@@ -461,7 +461,7 @@ func TestCalcCellValue(t *testing.T) {
"=EXP(0.1)": "1.10517091807565",
"=EXP(0)": "1",
"=EXP(-5)": "0.00673794699908547",
- "=EXP(EXP(0))": "2.718281828459045",
+ "=EXP(EXP(0))": "2.71828182845905",
// FACT
"=FACT(3)": "6",
"=FACT(6)": "720",
@@ -473,12 +473,12 @@ func TestCalcCellValue(t *testing.T) {
"=FACTDOUBLE(13)": "135135",
"=FACTDOUBLE(FACTDOUBLE(1))": "1",
// FLOOR
- "=FLOOR(26.75,0.1)": "26.700000000000003",
+ "=FLOOR(26.75,0.1)": "26.7",
"=FLOOR(26.75,0.5)": "26.5",
"=FLOOR(26.75,1)": "26",
"=FLOOR(26.75,10)": "20",
"=FLOOR(26.75,20)": "20",
- "=FLOOR(-26.75,-0.1)": "-26.700000000000003",
+ "=FLOOR(-26.75,-0.1)": "-26.7",
"=FLOOR(-26.75,-1)": "-26",
"=FLOOR(-26.75,-5)": "-25",
"=FLOOR(FLOOR(26.75,1),1)": "26",
@@ -493,7 +493,7 @@ func TestCalcCellValue(t *testing.T) {
"=_xlfn.FLOOR.MATH(-58.55,10)": "-60",
"=_xlfn.FLOOR.MATH(_xlfn.FLOOR.MATH(1),10)": "0",
// _xlfn.FLOOR.PRECISE
- "=_xlfn.FLOOR.PRECISE(26.75,0.1)": "26.700000000000003",
+ "=_xlfn.FLOOR.PRECISE(26.75,0.1)": "26.7",
"=_xlfn.FLOOR.PRECISE(26.75,0.5)": "26.5",
"=_xlfn.FLOOR.PRECISE(26.75,1)": "26",
"=_xlfn.FLOOR.PRECISE(26.75)": "26",
@@ -524,7 +524,7 @@ func TestCalcCellValue(t *testing.T) {
"=ISO.CEILING(22.25,0.1)": "22.3",
"=ISO.CEILING(22.25,10)": "30",
"=ISO.CEILING(-22.25,1)": "-22",
- "=ISO.CEILING(-22.25,0.1)": "-22.200000000000003",
+ "=ISO.CEILING(-22.25,0.1)": "-22.2",
"=ISO.CEILING(-22.25,5)": "-20",
"=ISO.CEILING(-22.25,0)": "0",
"=ISO.CEILING(1,ISO.CEILING(1,0))": "0",
@@ -539,7 +539,7 @@ func TestCalcCellValue(t *testing.T) {
`=LCM(0,LCM(0,0))`: "0",
// LN
"=LN(1)": "0",
- "=LN(100)": "4.605170185988092",
+ "=LN(100)": "4.60517018598809",
"=LN(0.5)": "-0.693147180559945",
"=LN(LN(100))": "1.5271796258079",
// LOG
@@ -557,7 +557,7 @@ func TestCalcCellValue(t *testing.T) {
// IMLOG2
"=IMLOG2(\"5+2i\")": "2.4289904975637864+0.5489546632866347i",
"=IMLOG2(\"2-i\")": "1.1609640474436813-0.6689021062254881i",
- "=IMLOG2(6)": "2.584962500721156",
+ "=IMLOG2(6)": "2.58496250072116",
"=IMLOG2(\"3i\")": "1.584962500721156+2.266180070913597i",
"=IMLOG2(\"4+i\")": "2.04373142062517+0.3534295024167349i",
// IMPOWER
@@ -604,7 +604,7 @@ func TestCalcCellValue(t *testing.T) {
"=ODD(-3)": "-3",
"=ODD(ODD(1))": "1",
// PI
- "=PI()": "3.141592653589793",
+ "=PI()": "3.14159265358979",
// POWER
"=POWER(4,2)": "16",
"=POWER(4,POWER(1,1))": "4",
@@ -619,9 +619,9 @@ func TestCalcCellValue(t *testing.T) {
"=QUOTIENT(QUOTIENT(1,2),3)": "0",
// RADIANS
"=RADIANS(50)": "0.872664625997165",
- "=RADIANS(-180)": "-3.141592653589793",
- "=RADIANS(180)": "3.141592653589793",
- "=RADIANS(360)": "6.283185307179586",
+ "=RADIANS(-180)": "-3.14159265358979",
+ "=RADIANS(180)": "3.14159265358979",
+ "=RADIANS(360)": "6.28318530717959",
"=RADIANS(RADIANS(360))": "0.109662271123215",
// ROMAN
"=ROMAN(499,0)": "CDXCIX",
@@ -634,9 +634,9 @@ func TestCalcCellValue(t *testing.T) {
"=ROMAN(1999,5)": "MIM",
"=ROMAN(1999,ODD(1))": "MLMVLIV",
// ROUND
- "=ROUND(100.319,1)": "100.30000000000001",
- "=ROUND(5.28,1)": "5.300000000000001",
- "=ROUND(5.9999,3)": "6.000000000000002",
+ "=ROUND(100.319,1)": "100.3",
+ "=ROUND(5.28,1)": "5.3",
+ "=ROUND(5.9999,3)": "6",
"=ROUND(99.5,0)": "100",
"=ROUND(-6.3,0)": "-6",
"=ROUND(-100.5,0)": "-101",
@@ -646,18 +646,18 @@ func TestCalcCellValue(t *testing.T) {
"=ROUND(ROUND(100,1),-1)": "100",
// ROUNDDOWN
"=ROUNDDOWN(99.999,1)": "99.9",
- "=ROUNDDOWN(99.999,2)": "99.99000000000002",
+ "=ROUNDDOWN(99.999,2)": "99.99",
"=ROUNDDOWN(99.999,0)": "99",
"=ROUNDDOWN(99.999,-1)": "90",
- "=ROUNDDOWN(-99.999,2)": "-99.99000000000002",
+ "=ROUNDDOWN(-99.999,2)": "-99.99",
"=ROUNDDOWN(-99.999,-1)": "-90",
"=ROUNDDOWN(ROUNDDOWN(100,1),-1)": "100",
// ROUNDUP`
- "=ROUNDUP(11.111,1)": "11.200000000000001",
- "=ROUNDUP(11.111,2)": "11.120000000000003",
+ "=ROUNDUP(11.111,1)": "11.2",
+ "=ROUNDUP(11.111,2)": "11.12",
"=ROUNDUP(11.111,0)": "12",
"=ROUNDUP(11.111,-1)": "20",
- "=ROUNDUP(-11.111,2)": "-11.120000000000003",
+ "=ROUNDUP(-11.111,2)": "-11.12",
"=ROUNDUP(-11.111,-1)": "-20",
"=ROUNDUP(ROUNDUP(100,1),-1)": "100",
// SEC
@@ -681,26 +681,26 @@ func TestCalcCellValue(t *testing.T) {
// SINH
"=SINH(0)": "0",
"=SINH(0.5)": "0.521095305493747",
- "=SINH(-2)": "-3.626860407847019",
+ "=SINH(-2)": "-3.62686040784702",
"=SINH(SINH(0))": "0",
// SQRT
"=SQRT(4)": "2",
"=SQRT(SQRT(16))": "2",
// SQRTPI
- "=SQRTPI(5)": "3.963327297606011",
+ "=SQRTPI(5)": "3.96332729760601",
"=SQRTPI(0.2)": "0.792665459521202",
- "=SQRTPI(100)": "17.72453850905516",
+ "=SQRTPI(100)": "17.7245385090552",
"=SQRTPI(0)": "0",
"=SQRTPI(SQRTPI(0))": "0",
// STDEV
- "=STDEV(F2:F9)": "10724.978287523809",
+ "=STDEV(F2:F9)": "10724.9782875238",
"=STDEV(MUNIT(2))": "0.577350269189626",
"=STDEV(0,INT(0))": "0",
"=STDEV(INT(1),INT(1))": "0",
// STDEV.S
- "=STDEV.S(F2:F9)": "10724.978287523809",
+ "=STDEV.S(F2:F9)": "10724.9782875238",
// STDEVA
- "=STDEVA(F2:F9)": "10724.978287523809",
+ "=STDEVA(F2:F9)": "10724.9782875238",
"=STDEVA(MUNIT(2))": "0.577350269189626",
"=STDEVA(0,INT(0))": "0",
// POISSON.DIST
@@ -723,7 +723,7 @@ func TestCalcCellValue(t *testing.T) {
"=SUM(SUM(1+2/1)*2-3/2,2)": "6.5",
"=((3+5*2)+3)/5+(-6)/4*2+3": "3.2",
"=1+SUM(SUM(1,2*3),4)*-4/2+5+(4+2)*3": "2",
- "=1+SUM(SUM(1,2*3),4)*4/3+5+(4+2)*3": "38.666666666666664",
+ "=1+SUM(SUM(1,2*3),4)*4/3+5+(4+2)*3": "38.6666666666667",
"=SUM(1+ROW())": "2",
"=SUM((SUM(2))+1)": "3",
// SUMIF
@@ -754,7 +754,7 @@ func TestCalcCellValue(t *testing.T) {
// SUMXMY2
"=SUMXMY2(A1:A4,B1:B4)": "18",
// TAN
- "=TAN(1.047197551)": "1.732050806782486",
+ "=TAN(1.047197551)": "1.73205080678249",
"=TAN(0)": "0",
"=TAN(TAN(0))": "0",
// TANH
@@ -796,7 +796,7 @@ func TestCalcCellValue(t *testing.T) {
"=BETADIST(0.4,2,100)": "1",
"=BETADIST(0.75,3,4)": "0.96240234375",
"=BETADIST(0.2,0.7,4)": "0.71794309318323",
- "=BETADIST(0.01,3,4)": "1.955359E-05",
+ "=BETADIST(0.01,3,4)": "1.9553589999999998e-05",
"=BETADIST(0.75,130,140)": "1",
// BETAINV
"=BETAINV(0.2,4,5,0,1)": "0.303225844664082",
@@ -808,7 +808,7 @@ func TestCalcCellValue(t *testing.T) {
// CHIINV
"=CHIINV(0.5,1)": "0.454936423119572",
"=CHIINV(0.75,1)": "0.101531044267622",
- "=CHIINV(0.1,2)": "4.605170185988088",
+ "=CHIINV(0.1,2)": "4.60517018598809",
"=CHIINV(0.8,2)": "0.446287102628419",
// CONFIDENCE
"=CONFIDENCE(0.05,0.07,100)": "0.0137197479028414",
@@ -850,10 +850,10 @@ func TestCalcCellValue(t *testing.T) {
"=FISHERINV(INT(0))": "0",
"=FISHERINV(2.8)": "0.992631520201128",
// GAMMA
- "=GAMMA(0.1)": "9.513507698668732",
+ "=GAMMA(0.1)": "9.51350769866873",
"=GAMMA(INT(1))": "1",
"=GAMMA(1.5)": "0.886226925452758",
- "=GAMMA(5.5)": "52.34277778455352",
+ "=GAMMA(5.5)": "52.3427777845535",
// GAMMA.DIST
"=GAMMA.DIST(6,3,2,FALSE)": "0.112020903827694",
"=GAMMA.DIST(6,3,2,TRUE)": "0.576809918873156",
@@ -861,10 +861,10 @@ func TestCalcCellValue(t *testing.T) {
"=GAMMADIST(6,3,2,FALSE)": "0.112020903827694",
"=GAMMADIST(6,3,2,TRUE)": "0.576809918873156",
// GAMMA.INV
- "=GAMMA.INV(0.5,3,2)": "5.348120627447122",
+ "=GAMMA.INV(0.5,3,2)": "5.34812062744712",
"=GAMMA.INV(0.5,0.5,1)": "0.227468211559786",
// GAMMAINV
- "=GAMMAINV(0.5,3,2)": "5.348120627447122",
+ "=GAMMAINV(0.5,3,2)": "5.34812062744712",
"=GAMMAINV(0.5,0.5,1)": "0.227468211559786",
// GAMMALN
"=GAMMALN(4.5)": "2.45373657084244",
@@ -925,11 +925,11 @@ func TestCalcCellValue(t *testing.T) {
"=NORMDIST(0.8,1,0.3,TRUE)": "0.252492537546923",
"=NORMDIST(50,40,20,FALSE)": "0.017603266338215",
// NORM.INV
- "=NORM.INV(0.6,5,2)": "5.506694205719997",
+ "=NORM.INV(0.6,5,2)": "5.50669420572",
// NORMINV
- "=NORMINV(0.6,5,2)": "5.506694205719997",
- "=NORMINV(0.99,40,1.5)": "43.489521811582044",
- "=NORMINV(0.02,40,1.5)": "36.91937663649545",
+ "=NORMINV(0.6,5,2)": "5.50669420572",
+ "=NORMINV(0.99,40,1.5)": "43.489521811582",
+ "=NORMINV(0.02,40,1.5)": "36.9193766364954",
// NORM.S.DIST
"=NORM.S.DIST(0.8,TRUE)": "0.788144601416603",
// NORMSDIST
@@ -1052,7 +1052,7 @@ func TestCalcCellValue(t *testing.T) {
"=TRIMMEAN(A1:B4,10%)": "2.5",
"=TRIMMEAN(A1:B4,70%)": "2.5",
// VAR
- "=VAR(1,3,5,0,C1)": "4.916666666666667",
+ "=VAR(1,3,5,0,C1)": "4.91666666666667",
"=VAR(1,3,5,0,C1,TRUE)": "4",
// VARA
"=VARA(1,3,5,0,C1)": "4.7",
@@ -1063,16 +1063,16 @@ func TestCalcCellValue(t *testing.T) {
// VAR.P
"=VAR.P(A1:A5)": "1.25",
// VAR.S
- "=VAR.S(1,3,5,0,C1)": "4.916666666666667",
+ "=VAR.S(1,3,5,0,C1)": "4.91666666666667",
"=VAR.S(1,3,5,0,C1,TRUE)": "4",
// VARPA
"=VARPA(1,3,5,0,C1)": "3.76",
"=VARPA(1,3,5,0,C1,TRUE)": "3.22222222222222",
// WEIBULL
- "=WEIBULL(1,3,1,FALSE)": "1.103638323514327",
+ "=WEIBULL(1,3,1,FALSE)": "1.10363832351433",
"=WEIBULL(2,5,1.5,TRUE)": "0.985212776817482",
// WEIBULL.DIST
- "=WEIBULL.DIST(1,3,1,FALSE)": "1.103638323514327",
+ "=WEIBULL.DIST(1,3,1,FALSE)": "1.10363832351433",
"=WEIBULL.DIST(2,5,1.5,TRUE)": "0.985212776817482",
// Information Functions
// ERROR.TYPE
@@ -1286,7 +1286,7 @@ func TestCalcCellValue(t *testing.T) {
"=YEARFRAC(\"01/31/2015\",\"03/31/2015\")": "0.166666666666667",
"=YEARFRAC(\"01/30/2015\",\"03/31/2015\")": "0.166666666666667",
"=YEARFRAC(\"02/29/2000\", \"02/29/2008\")": "8",
- "=YEARFRAC(\"02/29/2000\", \"02/29/2008\",1)": "7.998175182481752",
+ "=YEARFRAC(\"02/29/2000\", \"02/29/2008\",1)": "7.99817518248175",
"=YEARFRAC(\"02/29/2000\", \"01/29/2001\",1)": "0.915300546448087",
"=YEARFRAC(\"02/29/2000\", \"03/29/2000\",1)": "0.0792349726775956",
"=YEARFRAC(\"01/31/2000\", \"03/29/2000\",4)": "0.163888888888889",
@@ -1472,7 +1472,7 @@ func TestCalcCellValue(t *testing.T) {
"=VALUE(\"5,000\")": "5000",
"=VALUE(\"20%\")": "0.2",
"=VALUE(\"12:00:00\")": "0.5",
- "=VALUE(\"01/02/2006 15:04:05\")": "38719.62783564815",
+ "=VALUE(\"01/02/2006 15:04:05\")": "38719.6278356481",
// Conditional Functions
// IF
"=IF(1=1)": "TRUE",
@@ -1627,16 +1627,16 @@ func TestCalcCellValue(t *testing.T) {
"=COUPPCD(\"10/25/2011\",\"01/01/2012\",4)": "40817",
// CUMIPMT
"=CUMIPMT(0.05/12,60,50000,1,12,0)": "-2294.97753732664",
- "=CUMIPMT(0.05/12,60,50000,13,24,0)": "-1833.1000665738893",
+ "=CUMIPMT(0.05/12,60,50000,13,24,0)": "-1833.10006657389",
// CUMPRINC
- "=CUMPRINC(0.05/12,60,50000,1,12,0)": "-9027.762649079885",
- "=CUMPRINC(0.05/12,60,50000,13,24,0)": "-9489.640119832635",
+ "=CUMPRINC(0.05/12,60,50000,1,12,0)": "-9027.76264907988",
+ "=CUMPRINC(0.05/12,60,50000,13,24,0)": "-9489.64011983263",
// DB
"=DB(0,1000,5,1)": "0",
"=DB(10000,1000,5,1)": "3690",
"=DB(10000,1000,5,2)": "2328.39",
"=DB(10000,1000,5,1,6)": "1845",
- "=DB(10000,1000,5,6,6)": "238.52712458788187",
+ "=DB(10000,1000,5,6,6)": "238.527124587882",
// DDB
"=DDB(0,1000,5,1)": "0",
"=DDB(10000,1000,5,1)": "4000",
@@ -1651,13 +1651,13 @@ func TestCalcCellValue(t *testing.T) {
// DOLLARFR
"=DOLLARFR(1.0625,16)": "1.01",
// DURATION
- "=DURATION(\"04/01/2015\",\"03/31/2025\",10%,8%,4)": "6.674422798483131",
+ "=DURATION(\"04/01/2015\",\"03/31/2025\",10%,8%,4)": "6.67442279848313",
// EFFECT
"=EFFECT(0.1,4)": "0.103812890625",
"=EFFECT(0.025,2)": "0.02515625",
// FV
- "=FV(0.05/12,60,-1000)": "68006.08284084337",
- "=FV(0.1/4,16,-2000,0,1)": "39729.46089416617",
+ "=FV(0.05/12,60,-1000)": "68006.0828408434",
+ "=FV(0.1/4,16,-2000,0,1)": "39729.4608941662",
"=FV(0,16,-2000)": "32000",
// FVSCHEDULE
"=FVSCHEDULE(10000,A1:A5)": "240000",
@@ -1665,60 +1665,60 @@ func TestCalcCellValue(t *testing.T) {
// INTRATE
"=INTRATE(\"04/01/2005\",\"03/31/2010\",1000,2125)": "0.225",
// IPMT
- "=IPMT(0.05/12,2,60,50000)": "-205.26988187971995",
- "=IPMT(0.035/4,2,8,0,5000,1)": "5.257455237829077",
+ "=IPMT(0.05/12,2,60,50000)": "-205.26988187972",
+ "=IPMT(0.035/4,2,8,0,5000,1)": "5.25745523782908",
// ISPMT
- "=ISPMT(0.05/12,1,60,50000)": "-204.8611111111111",
- "=ISPMT(0.05/12,2,60,50000)": "-201.38888888888886",
- "=ISPMT(0.05/12,2,1,50000)": "208.33333333333334",
+ "=ISPMT(0.05/12,1,60,50000)": "-204.861111111111",
+ "=ISPMT(0.05/12,2,60,50000)": "-201.388888888889",
+ "=ISPMT(0.05/12,2,1,50000)": "208.333333333333",
// MDURATION
- "=MDURATION(\"04/01/2015\",\"03/31/2025\",10%,8%,4)": "6.543551763218756",
+ "=MDURATION(\"04/01/2015\",\"03/31/2025\",10%,8%,4)": "6.54355176321876",
// NOMINAL
"=NOMINAL(0.025,12)": "0.0247180352381129",
// NPER
- "=NPER(0.04,-6000,50000)": "10.338035071507665",
- "=NPER(0,-6000,50000)": "8.333333333333334",
- "=NPER(0.06/4,-2000,60000,30000,1)": "52.794773709274764",
+ "=NPER(0.04,-6000,50000)": "10.3380350715077",
+ "=NPER(0,-6000,50000)": "8.33333333333333",
+ "=NPER(0.06/4,-2000,60000,30000,1)": "52.7947737092748",
// NPV
- "=NPV(0.02,-5000,\"\",800)": "-4133.025759323337",
+ "=NPV(0.02,-5000,\"\",800)": "-4133.02575932334",
// ODDFPRICE
- "=ODDFPRICE(\"02/01/2017\",\"03/31/2021\",\"12/01/2016\",\"03/31/2017\",5.5%,3.5%,100,2)": "107.69183025662932",
- "=ODDFPRICE(\"02/01/2017\",\"03/31/2021\",\"12/01/2016\",\"03/31/2017\",5.5%,3.5%,100,4,1)": "106.76691501092883",
- "=ODDFPRICE(\"02/01/2017\",\"03/31/2021\",\"12/01/2016\",\"03/31/2017\",5.5%,3.5%,100,4,3)": "106.7819138146997",
- "=ODDFPRICE(\"02/01/2017\",\"03/31/2021\",\"12/01/2016\",\"03/31/2017\",5.5%,3.5%,100,4,4)": "106.77191377246672",
- "=ODDFPRICE(\"11/11/2008\",\"03/01/2021\",\"10/15/2008\",\"03/01/2009\",7.85%,6.25%,100,2,1)": "113.59771747407883",
- "=ODDFPRICE(\"02/01/2017\",\"03/31/2021\",\"12/01/2016\",\"09/30/2017\",5.5%,3.5%,100,4,0)": "106.72930611878041",
- "=ODDFPRICE(\"11/11/2008\",\"03/29/2021\", \"08/15/2008\", \"03/29/2009\", 0.0785, 0.0625, 100, 2, 1)": "113.61826640813996",
+ "=ODDFPRICE(\"02/01/2017\",\"03/31/2021\",\"12/01/2016\",\"03/31/2017\",5.5%,3.5%,100,2)": "107.691830256629",
+ "=ODDFPRICE(\"02/01/2017\",\"03/31/2021\",\"12/01/2016\",\"03/31/2017\",5.5%,3.5%,100,4,1)": "106.766915010929",
+ "=ODDFPRICE(\"02/01/2017\",\"03/31/2021\",\"12/01/2016\",\"03/31/2017\",5.5%,3.5%,100,4,3)": "106.7819138147",
+ "=ODDFPRICE(\"02/01/2017\",\"03/31/2021\",\"12/01/2016\",\"03/31/2017\",5.5%,3.5%,100,4,4)": "106.771913772467",
+ "=ODDFPRICE(\"11/11/2008\",\"03/01/2021\",\"10/15/2008\",\"03/01/2009\",7.85%,6.25%,100,2,1)": "113.597717474079",
+ "=ODDFPRICE(\"02/01/2017\",\"03/31/2021\",\"12/01/2016\",\"09/30/2017\",5.5%,3.5%,100,4,0)": "106.72930611878",
+ "=ODDFPRICE(\"11/11/2008\",\"03/29/2021\", \"08/15/2008\", \"03/29/2009\", 0.0785, 0.0625, 100, 2, 1)": "113.61826640814",
// PDURATION
- "=PDURATION(0.04,10000,15000)": "10.33803507150765",
+ "=PDURATION(0.04,10000,15000)": "10.3380350715076",
// PMT
"=PMT(0,8,0,5000,1)": "-625",
- "=PMT(0.035/4,8,0,5000,1)": "-600.8520271804658",
+ "=PMT(0.035/4,8,0,5000,1)": "-600.852027180466",
// PRICE
- "=PRICE(\"04/01/2012\",\"02/01/2020\",12%,10%,100,2)": "110.65510517844305",
- "=PRICE(\"04/01/2012\",\"02/01/2020\",12%,10%,100,2,4)": "110.65510517844305",
- "=PRICE(\"04/01/2012\",\"03/31/2020\",12%,10%,100,2)": "110.83448359321572",
- "=PRICE(\"01/01/2010\",\"06/30/2010\",0.5,1,1,1,4)": "8.924190888476605",
+ "=PRICE(\"04/01/2012\",\"02/01/2020\",12%,10%,100,2)": "110.655105178443",
+ "=PRICE(\"04/01/2012\",\"02/01/2020\",12%,10%,100,2,4)": "110.655105178443",
+ "=PRICE(\"04/01/2012\",\"03/31/2020\",12%,10%,100,2)": "110.834483593216",
+ "=PRICE(\"01/01/2010\",\"06/30/2010\",0.5,1,1,1,4)": "8.92419088847661",
// PPMT
- "=PPMT(0.05/12,2,60,50000)": "-738.2918003208238",
- "=PPMT(0.035/4,2,8,0,5000,1)": "-606.1094824182949",
+ "=PPMT(0.05/12,2,60,50000)": "-738.291800320824",
+ "=PPMT(0.035/4,2,8,0,5000,1)": "-606.109482418295",
// PRICEDISC
"=PRICEDISC(\"04/01/2017\",\"03/31/2021\",2.5%,100)": "90",
"=PRICEDISC(\"04/01/2017\",\"03/31/2021\",2.5%,100,3)": "90",
// PRICEMAT
- "=PRICEMAT(\"04/01/2017\",\"03/31/2021\",\"01/01/2017\",4.5%,2.5%)": "107.17045454545453",
- "=PRICEMAT(\"04/01/2017\",\"03/31/2021\",\"01/01/2017\",4.5%,2.5%,0)": "107.17045454545453",
+ "=PRICEMAT(\"04/01/2017\",\"03/31/2021\",\"01/01/2017\",4.5%,2.5%)": "107.170454545455",
+ "=PRICEMAT(\"04/01/2017\",\"03/31/2021\",\"01/01/2017\",4.5%,2.5%,0)": "107.170454545455",
// PV
"=PV(0,60,1000)": "-60000",
- "=PV(5%/12,60,1000)": "-52990.70632392748",
- "=PV(10%/4,16,2000,0,1)": "-26762.75545288113",
+ "=PV(5%/12,60,1000)": "-52990.7063239275",
+ "=PV(10%/4,16,2000,0,1)": "-26762.7554528811",
// RATE
"=RATE(60,-1000,50000)": "0.0061834131621292",
"=RATE(24,-800,0,20000,1)": "0.00325084350160374",
"=RATE(48,-200,8000,3,1,0.5)": "0.0080412665831637",
// RECEIVED
- "=RECEIVED(\"04/01/2011\",\"03/31/2016\",1000,4.5%)": "1290.3225806451612",
- "=RECEIVED(\"04/01/2011\",\"03/31/2016\",1000,4.5%,0)": "1290.3225806451612",
+ "=RECEIVED(\"04/01/2011\",\"03/31/2016\",1000,4.5%)": "1290.32258064516",
+ "=RECEIVED(\"04/01/2011\",\"03/31/2016\",1000,4.5%,0)": "1290.32258064516",
// RRI
"=RRI(10,10000,15000)": "0.0413797439924106",
// SLN
@@ -1729,7 +1729,7 @@ func TestCalcCellValue(t *testing.T) {
// TBILLEQ
"=TBILLEQ(\"01/01/2017\",\"06/30/2017\",2.5%)": "0.0256680731364276",
// TBILLPRICE
- "=TBILLPRICE(\"02/01/2017\",\"06/30/2017\",2.75%)": "98.86180555555556",
+ "=TBILLPRICE(\"02/01/2017\",\"06/30/2017\",2.75%)": "98.8618055555556",
// TBILLYIELD
"=TBILLYIELD(\"02/01/2017\",\"06/30/2017\",99)": "0.024405125076266",
// VDB
@@ -1739,10 +1739,10 @@ func TestCalcCellValue(t *testing.T) {
"=VDB(10000,1000,5,3,5,0.2,FALSE)": "3600",
"=VDB(10000,1000,5,3,5,0.2,TRUE)": "693.633024",
"=VDB(24000,3000,10,0,0.875,2)": "4200",
- "=VDB(24000,3000,10,0.1,1)": "4233.599999999999",
- "=VDB(24000,3000,10,0.1,1,1)": "2138.3999999999996",
- "=VDB(24000,3000,100,50,100,1)": "10377.294418465235",
- "=VDB(24000,3000,100,50,100,2)": "5740.072322090805",
+ "=VDB(24000,3000,10,0.1,1)": "4233.6",
+ "=VDB(24000,3000,10,0.1,1,1)": "2138.4",
+ "=VDB(24000,3000,100,50,100,1)": "10377.2944184652",
+ "=VDB(24000,3000,100,50,100,2)": "5740.0723220908",
// YIELD
"=YIELD(\"01/01/2010\",\"06/30/2015\",10%,101,100,4)": "0.0975631546829798",
"=YIELD(\"01/01/2010\",\"06/30/2015\",10%,101,100,4,4)": "0.0976269355643988",
@@ -4284,7 +4284,7 @@ func TestCalcXNPV(t *testing.T) {
}
f := prepareCalcData(cellData)
formulaList := map[string]string{
- "=XNPV(B1,B2:B7,A2:A7)": "4447.938009440515",
+ "=XNPV(B1,B2:B7,A2:A7)": "4447.93800944052",
}
for formula, expected := range formulaList {
assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula))
diff --git a/cell.go b/cell.go
index c3b62dc..6ecce4f 100644
--- a/cell.go
+++ b/cell.go
@@ -1088,22 +1088,12 @@ func (f *File) formattedValue(s int, v string, raw bool) string {
if raw {
return v
}
- precise := v
- isNum, precision := isNumeric(v)
- if isNum {
- if precision > 15 {
- precise = roundPrecision(v, 15)
- }
- if precision <= 15 {
- precise = roundPrecision(v, -1)
- }
- }
if s == 0 {
- return precise
+ return v
}
styleSheet := f.stylesReader()
if s >= len(styleSheet.CellXfs.Xf) {
- return precise
+ return v
}
var numFmtID int
if styleSheet.CellXfs.Xf[s].NumFmtID != nil {
@@ -1112,17 +1102,17 @@ func (f *File) formattedValue(s int, v string, raw bool) string {
ok := builtInNumFmtFunc[numFmtID]
if ok != nil {
- return ok(precise, builtInNumFmt[numFmtID])
+ return ok(v, builtInNumFmt[numFmtID])
}
if styleSheet == nil || styleSheet.NumFmts == nil {
- return precise
+ return v
}
for _, xlsxFmt := range styleSheet.NumFmts.NumFmt {
if xlsxFmt.NumFmtID == numFmtID {
- return format(precise, xlsxFmt.FormatCode)
+ return format(v, xlsxFmt.FormatCode)
}
}
- return precise
+ return v
}
// prepareCellStyle provides a function to prepare style index of cell in
diff --git a/file.go b/file.go
index 0cfed05..0135e20 100644
--- a/file.go
+++ b/file.go
@@ -81,7 +81,7 @@ func (f *File) SaveAs(name string, opt ...Options) error {
return ErrWorkbookExt
}
f.setContentTypePartProjectExtensions(contentType)
- file, err := os.OpenFile(filepath.Clean(name), os.O_WRONLY|os.O_TRUNC|os.O_CREATE, 0600)
+ file, err := os.OpenFile(filepath.Clean(name), os.O_WRONLY|os.O_TRUNC|os.O_CREATE, 0o600)
if err != nil {
return err
}
diff --git a/lib.go b/lib.go
index 5bbbec9..439e50a 100644
--- a/lib.go
+++ b/lib.go
@@ -688,12 +688,15 @@ func isNumeric(s string) (bool, int) {
if i == 0 && v == '-' {
continue
}
- if e && (v == '+' || v == '-') {
+ if e && v == '-' {
+ return true, 0
+ }
+ if e && v == '+' {
p = 15
continue
}
return false, 0
- } else if dot {
+ } else {
p++
}
n = true
diff --git a/lib_test.go b/lib_test.go
index 1e2f324..027e5dd 100644
--- a/lib_test.go
+++ b/lib_test.go
@@ -342,7 +342,7 @@ func TestReadBytes(t *testing.T) {
func TestUnzipToTemp(t *testing.T) {
os.Setenv("TMPDIR", "test")
defer os.Unsetenv("TMPDIR")
- assert.NoError(t, os.Chmod(os.TempDir(), 0444))
+ assert.NoError(t, os.Chmod(os.TempDir(), 0o444))
f := NewFile()
data := []byte("PK\x03\x040000000PK\x01\x0200000" +
"0000000000000000000\x00" +
@@ -364,7 +364,7 @@ func TestUnzipToTemp(t *testing.T) {
_, err = f.unzipToTemp(z.File[0])
require.Error(t, err)
- assert.NoError(t, os.Chmod(os.TempDir(), 0755))
+ assert.NoError(t, os.Chmod(os.TempDir(), 0o755))
_, err = f.unzipToTemp(z.File[0])
assert.EqualError(t, err, "EOF")
diff --git a/numfmt.go b/numfmt.go
index 50ce1f3..3b20e02 100644
--- a/numfmt.go
+++ b/numfmt.go
@@ -13,6 +13,7 @@ package excelize
import (
"fmt"
+ "math"
"strconv"
"strings"
"time"
@@ -41,13 +42,15 @@ type numberFormat struct {
var (
// supportedTokenTypes list the supported number format token types currently.
supportedTokenTypes = []string{
+ nfp.TokenSubTypeLanguageInfo,
+ nfp.TokenTypeColor,
nfp.TokenTypeCurrencyLanguage,
nfp.TokenTypeDateTimes,
nfp.TokenTypeElapsedDateTimes,
nfp.TokenTypeGeneral,
nfp.TokenTypeLiteral,
nfp.TokenTypeTextPlaceHolder,
- nfp.TokenSubTypeLanguageInfo,
+ nfp.TokenTypeZeroPlaceHolder,
}
// supportedLanguageInfo directly maps the supported language ID and tags.
supportedLanguageInfo = map[string]languageInfo{
@@ -276,11 +279,9 @@ var (
// prepareNumberic split the number into two before and after parts by a
// decimal point.
func (nf *numberFormat) prepareNumberic(value string) {
- prec := 0
- if nf.isNumberic, prec = isNumeric(value); !nf.isNumberic {
+ if nf.isNumberic, _ = isNumeric(value); !nf.isNumberic {
return
}
- nf.beforePoint, nf.afterPoint = value[:len(value)-prec-1], value[len(value)-prec:]
}
// format provides a function to return a string parse by number format
@@ -336,6 +337,20 @@ func (nf *numberFormat) positiveHandler() (result string) {
nf.result += token.TValue
continue
}
+ if token.TType == nfp.TokenTypeZeroPlaceHolder && token.TValue == "0" {
+ if isNum, precision := isNumeric(nf.value); isNum {
+ if nf.number < 1 {
+ nf.result += "0"
+ continue
+ }
+ if precision > 15 {
+ nf.result += roundPrecision(nf.value, 15)
+ } else {
+ nf.result += fmt.Sprintf("%.f", nf.number)
+ }
+ continue
+ }
+ }
}
result = nf.result
return
@@ -874,8 +889,33 @@ func (nf *numberFormat) secondsNext(i int) bool {
// negativeHandler will be handling negative selection for a number format
// expression.
-func (nf *numberFormat) negativeHandler() string {
- return nf.value
+func (nf *numberFormat) negativeHandler() (result string) {
+ for _, token := range nf.section[nf.sectionIdx].Items {
+ if inStrSlice(supportedTokenTypes, token.TType, true) == -1 || token.TType == nfp.TokenTypeGeneral {
+ result = nf.value
+ return
+ }
+ if token.TType == nfp.TokenTypeLiteral {
+ nf.result += token.TValue
+ continue
+ }
+ if token.TType == nfp.TokenTypeZeroPlaceHolder && token.TValue == "0" {
+ if isNum, precision := isNumeric(nf.value); isNum {
+ if math.Abs(nf.number) < 1 {
+ nf.result += "0"
+ continue
+ }
+ if precision > 15 {
+ nf.result += strings.TrimLeft(roundPrecision(nf.value, 15), "-")
+ } else {
+ nf.result += fmt.Sprintf("%.f", math.Abs(nf.number))
+ }
+ continue
+ }
+ }
+ }
+ result = nf.result
+ return
}
// zeroHandler will be handling zero selection for a number format expression.
diff --git a/numfmt_test.go b/numfmt_test.go
index 80534e9..7dc3f77 100644
--- a/numfmt_test.go
+++ b/numfmt_test.go
@@ -996,6 +996,14 @@ func TestNumFmt(t *testing.T) {
{"44896.18957170139", "[$-435]mmmmm dd yyyy h:mm AM/PM", "D 01 2022 4:32 AM"},
{"text_", "General", "text_"},
{"text_", "\"=====\"@@@\"--\"@\"----\"", "=====text_text_text_--text_----"},
+ {"0.0450685976001E+21", "0_);[Red]\\(0\\)", "45068597600100000000"},
+ {"8.0450685976001E+21", "0_);[Red]\\(0\\)", "8045068597600100000000"},
+ {"8.0450685976001E-21", "0_);[Red]\\(0\\)", "0"},
+ {"8.04506", "0_);[Red]\\(0\\)", "8"},
+ {"-0.0450685976001E+21", "0_);[Red]\\(0\\)", "(45068597600100000000)"},
+ {"-8.0450685976001E+21", "0_);[Red]\\(0\\)", "(8045068597600100000000)"},
+ {"-8.0450685976001E-21", "0_);[Red]\\(0\\)", "(0)"},
+ {"-8.04506", "0_);[Red]\\(0\\)", "(8)"},
} {
result := format(item[0], item[1])
assert.Equal(t, item[2], result, item)
diff --git a/picture_test.go b/picture_test.go
index 8da7c3d..fbbdf11 100644
--- a/picture_test.go
+++ b/picture_test.go
@@ -102,7 +102,7 @@ func TestGetPicture(t *testing.T) {
file, raw, err := f.GetPicture("Sheet1", "F21")
assert.NoError(t, err)
if !assert.NotEmpty(t, filepath.Join("test", file)) || !assert.NotEmpty(t, raw) ||
- !assert.NoError(t, ioutil.WriteFile(filepath.Join("test", file), raw, 0644)) {
+ !assert.NoError(t, ioutil.WriteFile(filepath.Join("test", file), raw, 0o644)) {
t.FailNow()
}
@@ -137,7 +137,7 @@ func TestGetPicture(t *testing.T) {
file, raw, err = f.GetPicture("Sheet1", "F21")
assert.NoError(t, err)
if !assert.NotEmpty(t, filepath.Join("test", file)) || !assert.NotEmpty(t, raw) ||
- !assert.NoError(t, ioutil.WriteFile(filepath.Join("test", file), raw, 0644)) {
+ !assert.NoError(t, ioutil.WriteFile(filepath.Join("test", file), raw, 0o644)) {
t.FailNow()
}
diff --git a/rows.go b/rows.go
index ec94c64..ae7e01e 100644
--- a/rows.go
+++ b/rows.go
@@ -459,6 +459,13 @@ func (c *xlsxC) getValueFrom(f *File, d *xlsxSST, raw bool) (string, error) {
}
return f.formattedValue(c.S, c.V, raw), nil
default:
+ if isNum, precision := isNumeric(c.V); isNum && !raw {
+ if precision == 0 {
+ c.V = roundPrecision(c.V, 15)
+ } else {
+ c.V = roundPrecision(c.V, -1)
+ }
+ }
return f.formattedValue(c.S, c.V, raw), nil
}
}
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-16 04:34:10 +0000