From a13b21fe07e7d19a40529837b7148bc0261b9ae7 Mon Sep 17 00:00:00 2001 From: xuri Date: Sun, 11 Apr 2021 00:03:25 +0800 Subject: fixed the negative values series missing chart color, #65 fn: CUMIPMT and CUMPRINC --- calc.go | 75 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ calc_test.go | 28 +++++++++++++++++++++++ drawing.go | 21 +++++++++-------- 3 files changed, 114 insertions(+), 10 deletions(-) diff --git a/calc.go b/calc.go index 3b67ef9..aca69da 100644 --- a/calc.go +++ b/calc.go @@ -261,6 +261,8 @@ var tokenPriority = map[string]int{ // COUNTBLANK // CSC // CSCH +// CUMIPMT +// CUMPRINC // DATE // DATEDIF // DEC2BIN @@ -7162,6 +7164,79 @@ func (fn *formulaFuncs) ENCODEURL(argsList *list.List) formulaArg { // Financial Functions +// CUMIPMT function calculates the cumulative interest paid on a loan or +// investment, between two specified periods. The syntax of the function is: +// +// CUMIPMT(rate,nper,pv,start_period,end_period,type) +// +func (fn *formulaFuncs) CUMIPMT(argsList *list.List) formulaArg { + return fn.cumip("CUMIPMT", argsList) +} + +// CUMPRINC function calculates the cumulative payment on the principal of a +// loan or investment, between two specified periods. The syntax of the +// function is: +// +// CUMPRINC(rate,nper,pv,start_period,end_period,type) +// +func (fn *formulaFuncs) CUMPRINC(argsList *list.List) formulaArg { + return fn.cumip("CUMPRINC", argsList) +} + +// cumip is an implementation of the formula function CUMIPMT and CUMPRINC. +func (fn *formulaFuncs) cumip(name string, argsList *list.List) formulaArg { + if argsList.Len() != 6 { + return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires 6 arguments", name)) + } + rate := argsList.Front().Value.(formulaArg).ToNumber() + if rate.Type != ArgNumber { + return rate + } + nper := argsList.Front().Next().Value.(formulaArg).ToNumber() + if nper.Type != ArgNumber { + return nper + } + pv := argsList.Front().Next().Next().Value.(formulaArg).ToNumber() + if pv.Type != ArgNumber { + return pv + } + start := argsList.Back().Prev().Prev().Value.(formulaArg).ToNumber() + if start.Type != ArgNumber { + return start + } + end := argsList.Back().Prev().Value.(formulaArg).ToNumber() + if end.Type != ArgNumber { + return end + } + typ := argsList.Back().Value.(formulaArg).ToNumber() + if typ.Type != ArgNumber { + return typ + } + if typ.Number != 0 && typ.Number != 1 { + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) + } + if start.Number < 1 || start.Number > end.Number { + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) + } + num, ipmt := 0.0, newNumberFormulaArg(0) + for per := start.Number; per <= end.Number; per++ { + args := list.New().Init() + args.PushBack(rate) + args.PushBack(newNumberFormulaArg(per)) + args.PushBack(nper) + args.PushBack(pv) + args.PushBack(newNumberFormulaArg(0)) + args.PushBack(typ) + if name == "CUMIPMT" { + ipmt = fn.IPMT(args) + } else { + ipmt = fn.PPMT(args) + } + num += ipmt.Number + } + return newNumberFormulaArg(num) +} + // IPMT function calculates the interest payment, during a specific period of a // loan or investment that is paid in constant periodic payments, with a // constant interest rate. The syntax of the function is: diff --git a/calc_test.go b/calc_test.go index a3d9117..d105b14 100644 --- a/calc_test.go +++ b/calc_test.go @@ -1133,6 +1133,12 @@ func TestCalcCellValue(t *testing.T) { // ENCODEURL "=ENCODEURL(\"https://xuri.me/excelize/en/?q=Save As\")": "https%3A%2F%2Fxuri.me%2Fexcelize%2Fen%2F%3Fq%3DSave%20As", // Financial Functions + // CUMIPMT + "=CUMIPMT(0.05/12,60,50000,1,12,0)": "-2294.97753732664", + "=CUMIPMT(0.05/12,60,50000,13,24,0)": "-1833.1000665738893", + // CUMPRINC + "=CUMPRINC(0.05/12,60,50000,1,12,0)": "-9027.762649079885", + "=CUMPRINC(0.05/12,60,50000,13,24,0)": "-9489.640119832635", // IPMT "=IPMT(0.05/12,2,60,50000)": "-205.26988187971995", "=IPMT(0.035/4,2,8,0,5000,1)": "5.257455237829077", @@ -2030,6 +2036,28 @@ func TestCalcCellValue(t *testing.T) { // ENCODEURL "=ENCODEURL()": "ENCODEURL requires 1 argument", // Financial Functions + // CUMIPMT + "=CUMIPMT()": "CUMIPMT requires 6 arguments", + "=CUMIPMT(0,0,0,0,0,2)": "#N/A", + "=CUMIPMT(0,0,0,-1,0,0)": "#N/A", + "=CUMIPMT(0,0,0,1,0,0)": "#N/A", + "=CUMIPMT(\"\",0,0,0,0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=CUMIPMT(0,\"\",0,0,0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=CUMIPMT(0,0,\"\",0,0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=CUMIPMT(0,0,0,\"\",0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=CUMIPMT(0,0,0,0,\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=CUMIPMT(0,0,0,0,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // CUMPRINC + "=CUMPRINC()": "CUMPRINC requires 6 arguments", + "=CUMPRINC(0,0,0,0,0,2)": "#N/A", + "=CUMPRINC(0,0,0,-1,0,0)": "#N/A", + "=CUMPRINC(0,0,0,1,0,0)": "#N/A", + "=CUMPRINC(\"\",0,0,0,0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=CUMPRINC(0,\"\",0,0,0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=CUMPRINC(0,0,\"\",0,0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=CUMPRINC(0,0,0,\"\",0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=CUMPRINC(0,0,0,0,\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=CUMPRINC(0,0,0,0,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", // IPMT "=IPMT()": "IPMT requires at least 4 arguments", "=IPMT(0,0,0,0,0,0,0)": "IPMT allows at most 6 arguments", diff --git a/drawing.go b/drawing.go index 8b517f9..1d9a63b 100644 --- a/drawing.go +++ b/drawing.go @@ -744,16 +744,17 @@ func (f *File) drawChartSeries(formatSet *formatChart) *[]cSer { F: formatSet.Series[k].Name, }, }, - SpPr: f.drawChartSeriesSpPr(k, formatSet), - Marker: f.drawChartSeriesMarker(k, formatSet), - DPt: f.drawChartSeriesDPt(k, formatSet), - DLbls: f.drawChartSeriesDLbls(formatSet), - Cat: f.drawChartSeriesCat(formatSet.Series[k], formatSet), - Val: f.drawChartSeriesVal(formatSet.Series[k], formatSet), - XVal: f.drawChartSeriesXVal(formatSet.Series[k], formatSet), - YVal: f.drawChartSeriesYVal(formatSet.Series[k], formatSet), - BubbleSize: f.drawCharSeriesBubbleSize(formatSet.Series[k], formatSet), - Bubble3D: f.drawCharSeriesBubble3D(formatSet), + SpPr: f.drawChartSeriesSpPr(k, formatSet), + Marker: f.drawChartSeriesMarker(k, formatSet), + DPt: f.drawChartSeriesDPt(k, formatSet), + DLbls: f.drawChartSeriesDLbls(formatSet), + InvertIfNegative: &attrValBool{Val: boolPtr(false)}, + Cat: f.drawChartSeriesCat(formatSet.Series[k], formatSet), + Val: f.drawChartSeriesVal(formatSet.Series[k], formatSet), + XVal: f.drawChartSeriesXVal(formatSet.Series[k], formatSet), + YVal: f.drawChartSeriesYVal(formatSet.Series[k], formatSet), + BubbleSize: f.drawCharSeriesBubbleSize(formatSet.Series[k], formatSet), + Bubble3D: f.drawCharSeriesBubble3D(formatSet), }) } return &ser -- cgit v1.2.1