Ref: #660, #764, #1093, #1112, #1133 This improve number format support

- Introduced NFP (number format parser) dependencies module
- Initialize custom dates and times number format support
- Dependencies module upgraded

1 files changed, 356 insertions, 0 deletions

diff --git a/numfmt.go b/numfmt.go
new file mode 100644
index 0000000..a724405
--- /dev/null
+++ b/numfmt.go
@@ -0,0 +1,356 @@
+// Copyright 2016 - 2022 The excelize Authors. All rights reserved. Use of
+// this source code is governed by a BSD-style license that can be found in
+// the LICENSE file.
+//
+// Package excelize providing a set of functions that allow you to write to
+// and read from XLSX / XLSM / XLTM files. Supports reading and writing
+// spreadsheet documents generated by Microsoft Excel™ 2007 and later. Supports
+// complex components by high compatibility, and provided streaming API for
+// generating or reading data from a worksheet with huge amounts of data. This
+// library needs Go version 1.15 or later.
+
+package excelize
+
+import (
+	"fmt"
+	"strconv"
+	"strings"
+	"time"
+
+	"github.com/xuri/nfp"
+)
+
+// supportedTokenTypes list the supported number format token types currently.
+var supportedTokenTypes = []string{
+	nfp.TokenTypeCurrencyLanguage,
+	nfp.TokenTypeDateTimes,
+	nfp.TokenTypeElapsedDateTimes,
+	nfp.TokenTypeGeneral,
+	nfp.TokenTypeLiteral,
+	nfp.TokenSubTypeLanguageInfo,
+}
+
+// numberFormat directly maps the number format parser runtime required
+// fields.
+type numberFormat struct {
+	section                                                                 []nfp.Section
+	t                                                                       time.Time
+	sectionIdx                                                              int
+	isNumberic, hours, seconds                                              bool
+	number                                                                  float64
+	ap, afterPoint, beforePoint, localCode, result, value, valueSectionType string
+}
+
+// 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 {
+		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
+// expression. If the given number format is not supported, this will return
+// the original cell value.
+func format(value, numFmt string) string {
+	p := nfp.NumberFormatParser()
+	nf := numberFormat{section: p.Parse(numFmt), value: value}
+	nf.number, nf.valueSectionType = nf.getValueSectionType(value)
+	nf.prepareNumberic(value)
+	for i, section := range nf.section {
+		nf.sectionIdx = i
+		if section.Type != nf.valueSectionType {
+			continue
+		}
+		switch section.Type {
+		case nfp.TokenSectionPositive:
+			return nf.positiveHandler()
+		case nfp.TokenSectionNegative:
+			return nf.negativeHandler()
+		case nfp.TokenSectionZero:
+			return nf.zeroHandler()
+		default:
+			return nf.textHandler()
+		}
+	}
+	return value
+}
+
+// positiveHandler will be handling positive selection for a number format
+// expression.
+func (nf *numberFormat) positiveHandler() (result string) {
+	nf.t, nf.hours, nf.seconds = timeFromExcelTime(nf.number, false), false, false
+	for i, token := range nf.section[nf.sectionIdx].Items {
+		if inStrSlice(supportedTokenTypes, token.TType, true) == -1 || token.TType == nfp.TokenTypeGeneral {
+			result = fmt.Sprint(nf.number)
+			return
+		}
+		if token.TType == nfp.TokenTypeCurrencyLanguage {
+			if err := nf.currencyLanguageHandler(i, token); err != nil {
+				result = fmt.Sprint(nf.number)
+				return
+			}
+		}
+		if token.TType == nfp.TokenTypeDateTimes {
+			nf.dateTimesHandler(i, token)
+		}
+		if token.TType == nfp.TokenTypeElapsedDateTimes {
+			nf.elapsedDateTimesHandler(token)
+		}
+		if token.TType == nfp.TokenTypeLiteral {
+			nf.result += token.TValue
+			continue
+		}
+	}
+	result = nf.result
+	return
+}
+
+// currencyLanguageHandler will be handling currency and language types tokens for a number
+// format expression.
+func (nf *numberFormat) currencyLanguageHandler(i int, token nfp.Token) (err error) {
+	for _, part := range token.Parts {
+		if inStrSlice(supportedTokenTypes, part.Token.TType, true) == -1 {
+			err = ErrUnsupportedNumberFormat
+			return
+		}
+		if nf.localCode = part.Token.TValue; nf.localCode != "409" {
+			err = ErrUnsupportedNumberFormat
+			return
+		}
+	}
+	return
+}
+
+// dateTimesHandler will be handling date and times types tokens for a number
+// format expression.
+func (nf *numberFormat) dateTimesHandler(i int, token nfp.Token) {
+	if idx := inStrSlice(nfp.AmPm, strings.ToUpper(token.TValue), false); idx != -1 {
+		if nf.ap == "" {
+			nextHours := nf.hoursNext(i)
+			aps := strings.Split(token.TValue, "/")
+			nf.ap = aps[0]
+			if nextHours > 12 {
+				nf.ap = aps[1]
+			}
+		}
+		nf.result += nf.ap
+		return
+	}
+	if strings.Contains(strings.ToUpper(token.TValue), "M") {
+		l := len(token.TValue)
+		if l == 1 && !nf.hours && !nf.secondsNext(i) {
+			nf.result += strconv.Itoa(int(nf.t.Month()))
+			return
+		}
+		if l == 2 && !nf.hours && !nf.secondsNext(i) {
+			nf.result += fmt.Sprintf("%02d", int(nf.t.Month()))
+			return
+		}
+		if l == 3 {
+			nf.result += nf.t.Month().String()[:3]
+			return
+		}
+		if l == 4 || l > 5 {
+			nf.result += nf.t.Month().String()
+			return
+		}
+		if l == 5 {
+			nf.result += nf.t.Month().String()[:1]
+			return
+		}
+	}
+	nf.yearsHandler(i, token)
+	nf.daysHandler(i, token)
+	nf.hoursHandler(i, token)
+	nf.minutesHandler(token)
+	nf.secondsHandler(token)
+}
+
+// yearsHandler will be handling years in the date and times types tokens for a
+// number format expression.
+func (nf *numberFormat) yearsHandler(i int, token nfp.Token) {
+	years := strings.Contains(strings.ToUpper(token.TValue), "Y")
+	if years && len(token.TValue) <= 2 {
+		nf.result += strconv.Itoa(nf.t.Year())[2:]
+		return
+	}
+	if years && len(token.TValue) > 2 {
+		nf.result += strconv.Itoa(nf.t.Year())
+		return
+	}
+}
+
+// daysHandler will be handling days in the date and times types tokens for a
+// number format expression.
+func (nf *numberFormat) daysHandler(i int, token nfp.Token) {
+	if strings.Contains(strings.ToUpper(token.TValue), "D") {
+		switch len(token.TValue) {
+		case 1:
+			nf.result += strconv.Itoa(nf.t.Day())
+			return
+		case 2:
+			nf.result += fmt.Sprintf("%02d", nf.t.Day())
+			return
+		case 3:
+			nf.result += nf.t.Weekday().String()[:3]
+			return
+		default:
+			nf.result += nf.t.Weekday().String()
+			return
+		}
+	}
+}
+
+// hoursHandler will be handling hours in the date and times types tokens for a
+// number format expression.
+func (nf *numberFormat) hoursHandler(i int, token nfp.Token) {
+	nf.hours = strings.Contains(strings.ToUpper(token.TValue), "H")
+	if nf.hours {
+		h := nf.t.Hour()
+		ap, ok := nf.apNext(i)
+		if ok {
+			nf.ap = ap[0]
+			if h > 12 {
+				h -= 12
+				nf.ap = ap[1]
+			}
+		}
+		if nf.ap != "" && nf.hoursNext(i) == -1 && h > 12 {
+			h -= 12
+		}
+		switch len(token.TValue) {
+		case 1:
+			nf.result += strconv.Itoa(h)
+			return
+		default:
+			nf.result += fmt.Sprintf("%02d", h)
+			return
+		}
+	}
+}
+
+// minutesHandler will be handling minutes in the date and times types tokens
+// for a number format expression.
+func (nf *numberFormat) minutesHandler(token nfp.Token) {
+	if strings.Contains(strings.ToUpper(token.TValue), "M") {
+		nf.hours = false
+		switch len(token.TValue) {
+		case 1:
+			nf.result += strconv.Itoa(nf.t.Minute())
+			return
+		default:
+			nf.result += fmt.Sprintf("%02d", nf.t.Minute())
+			return
+		}
+	}
+}
+
+// secondsHandler will be handling seconds in the date and times types tokens
+// for a number format expression.
+func (nf *numberFormat) secondsHandler(token nfp.Token) {
+	nf.seconds = strings.Contains(strings.ToUpper(token.TValue), "S")
+	if nf.seconds {
+		switch len(token.TValue) {
+		case 1:
+			nf.result += strconv.Itoa(nf.t.Second())
+			return
+		default:
+			nf.result += fmt.Sprintf("%02d", nf.t.Second())
+			return
+		}
+	}
+}
+
+// elapsedDateTimesHandler will be handling elapsed date and times types tokens
+// for a number format expression.
+func (nf *numberFormat) elapsedDateTimesHandler(token nfp.Token) {
+	if strings.Contains(strings.ToUpper(token.TValue), "H") {
+		nf.result += fmt.Sprintf("%.f", nf.t.Sub(excel1900Epoc).Hours())
+		return
+	}
+	if strings.Contains(strings.ToUpper(token.TValue), "M") {
+		nf.result += fmt.Sprintf("%.f", nf.t.Sub(excel1900Epoc).Minutes())
+		return
+	}
+	if strings.Contains(strings.ToUpper(token.TValue), "S") {
+		nf.result += fmt.Sprintf("%.f", nf.t.Sub(excel1900Epoc).Seconds())
+		return
+	}
+}
+
+// hoursNext detects if a token of type hours exists after a given tokens list.
+func (nf *numberFormat) hoursNext(i int) int {
+	tokens := nf.section[nf.sectionIdx].Items
+	for idx := i + 1; idx < len(tokens); idx++ {
+		if tokens[idx].TType == nfp.TokenTypeDateTimes {
+			if strings.Contains(strings.ToUpper(tokens[idx].TValue), "H") {
+				t := timeFromExcelTime(nf.number, false)
+				return t.Hour()
+			}
+		}
+	}
+	return -1
+}
+
+// apNext detects if a token of type AM/PM exists after a given tokens list.
+func (nf *numberFormat) apNext(i int) ([]string, bool) {
+	tokens := nf.section[nf.sectionIdx].Items
+	for idx := i + 1; idx < len(tokens); idx++ {
+		if tokens[idx].TType == nfp.TokenTypeDateTimes {
+			if strings.Contains(strings.ToUpper(tokens[idx].TValue), "H") {
+				return nil, false
+			}
+			if i := inStrSlice(nfp.AmPm, tokens[idx].TValue, false); i != -1 {
+				return strings.Split(tokens[idx].TValue, "/"), true
+			}
+		}
+	}
+	return nil, false
+}
+
+// secondsNext detects if a token of type seconds exists after a given tokens
+// list.
+func (nf *numberFormat) secondsNext(i int) bool {
+	tokens := nf.section[nf.sectionIdx].Items
+	for idx := i + 1; idx < len(tokens); idx++ {
+		if tokens[idx].TType == nfp.TokenTypeDateTimes {
+			return strings.Contains(strings.ToUpper(tokens[idx].TValue), "S")
+		}
+	}
+	return false
+}
+
+// negativeHandler will be handling negative selection for a number format
+// expression.
+func (nf *numberFormat) negativeHandler() string {
+	return fmt.Sprint(nf.number)
+}
+
+// zeroHandler will be handling zero selection for a number format expression.
+func (nf *numberFormat) zeroHandler() string {
+	return fmt.Sprint(nf.number)
+}
+
+// textHandler will be handling text selection for a number format expression.
+func (nf *numberFormat) textHandler() string {
+	return fmt.Sprint(nf.value)
+}
+
+// getValueSectionType returns its applicable number format expression section
+// based on the given value.
+func (nf *numberFormat) getValueSectionType(value string) (float64, string) {
+	number, err := strconv.ParseFloat(value, 64)
+	if err != nil {
+		return number, nfp.TokenSectionText
+	}
+	if number > 0 {
+		return number, nfp.TokenSectionPositive
+	}
+	if number < 0 {
+		return number, nfp.TokenSectionNegative
+	}
+	return number, nfp.TokenSectionZero
+}