diff options
| -rw-r--r-- | cell.go | 24 | ||||
| -rw-r--r-- | date.go | 119 | ||||
| -rw-r--r-- | excelize.go | 9 | ||||
| -rw-r--r-- | excelize_test.go | 33 | ||||
| -rw-r--r-- | rows.go | 6 | ||||
| -rw-r--r-- | styles.go | 179 |
6 files changed, 357 insertions, 13 deletions
@@ -6,8 +6,10 @@ import ( "strings" ) -// GetCellValue provides function to get value from cell by given sheet index -// and axis in XLSX file. +// GetCellValue provides function to get formatted value from cell by given +// sheet index and axis in XLSX file. If it is possible to apply a format to the +// cell value, it will do so, if not then an error will be returned, along with +// the raw value of the cell. func (f *File) GetCellValue(sheet, axis string) string { xlsx := f.workSheetReader(sheet) axis = strings.ToUpper(axis) @@ -44,17 +46,29 @@ func (f *File) GetCellValue(sheet, axis string) string { xlsxSI := 0 xlsxSI, _ = strconv.Atoi(r.V) xml.Unmarshal([]byte(f.readXML("xl/sharedStrings.xml")), &shardStrings) - return shardStrings.SI[xlsxSI].T + return f.formattedValue(r.S, shardStrings.SI[xlsxSI].T) case "str": - return r.V + return f.formattedValue(r.S, r.V) default: - return r.V + return f.formattedValue(r.S, r.V) } } } return "" } +// formattedValue provides function to returns a value after formatted. If it is +// possible to apply a format to the cell value, it will do so, if not then an +// error will be returned, along with the raw value of the cell. +func (f *File) formattedValue(s int, v string) string { + if s == 0 { + return v + } + var styleSheet xlsxStyleSheet + xml.Unmarshal([]byte(f.readXML("xl/styles.xml")), &styleSheet) + return builtInNumFmtFunc[styleSheet.CellXfs.Xf[s].NumFmtID](styleSheet.CellXfs.Xf[s].NumFmtID, v) +} + // GetCellFormula provides function to get formula from cell by given sheet // index and axis in XLSX file. func (f *File) GetCellFormula(sheet, axis string) string { @@ -0,0 +1,119 @@ +package excelize + +import ( + "math" + "time" +) + +// timeLocationUTC defined the UTC time location. +var timeLocationUTC, _ = time.LoadLocation("UTC") + +// timeToUTCTime provides function to convert time to UTC time. +func timeToUTCTime(t time.Time) time.Time { + return time.Date(t.Year(), t.Month(), t.Day(), t.Hour(), t.Minute(), t.Second(), t.Nanosecond(), timeLocationUTC) +} + +// timeToExcelTime provides function to convert time to Excel time. +func timeToExcelTime(t time.Time) float64 { + return float64(t.UnixNano())/8.64e13 + 25569.0 +} + +// shiftJulianToNoon provides function to process julian date to noon. +func shiftJulianToNoon(julianDays, julianFraction float64) (float64, float64) { + switch { + case -0.5 < julianFraction && julianFraction < 0.5: + julianFraction += 0.5 + case julianFraction >= 0.5: + julianDays++ + julianFraction -= 0.5 + case julianFraction <= -0.5: + julianDays-- + julianFraction += 1.5 + } + return julianDays, julianFraction +} + +// fractionOfADay provides function to return the integer values for hour, +// minutes, seconds and nanoseconds that comprised a given fraction of a day. +// values would round to 1 us. +func fractionOfADay(fraction float64) (hours, minutes, seconds, nanoseconds int) { + + const ( + c1us = 1e3 + c1s = 1e9 + c1day = 24 * 60 * 60 * c1s + ) + + frac := int64(c1day*fraction + c1us/2) + nanoseconds = int((frac%c1s)/c1us) * c1us + frac /= c1s + seconds = int(frac % 60) + frac /= 60 + minutes = int(frac % 60) + hours = int(frac / 60) + return +} + +// julianDateToGregorianTime provides function to convert julian date to +// gregorian time. +func julianDateToGregorianTime(part1, part2 float64) time.Time { + part1I, part1F := math.Modf(part1) + part2I, part2F := math.Modf(part2) + julianDays := part1I + part2I + julianFraction := part1F + part2F + julianDays, julianFraction = shiftJulianToNoon(julianDays, julianFraction) + day, month, year := doTheFliegelAndVanFlandernAlgorithm(int(julianDays)) + hours, minutes, seconds, nanoseconds := fractionOfADay(julianFraction) + return time.Date(year, time.Month(month), day, hours, minutes, seconds, nanoseconds, time.UTC) +} + +// By this point generations of programmers have repeated the algorithm sent to +// the editor of "Communications of the ACM" in 1968 (published in CACM, volume +// 11, number 10, October 1968, p.657). None of those programmers seems to have +// found it necessary to explain the constants or variable names set out by +// Henry F. Fliegel and Thomas C. Van Flandern. Maybe one day I'll buy that +// jounal and expand an explanation here - that day is not today. +func doTheFliegelAndVanFlandernAlgorithm(jd int) (day, month, year int) { + l := jd + 68569 + n := (4 * l) / 146097 + l = l - (146097*n+3)/4 + i := (4000 * (l + 1)) / 1461001 + l = l - (1461*i)/4 + 31 + j := (80 * l) / 2447 + d := l - (2447*j)/80 + l = j / 11 + m := j + 2 - (12 * l) + y := 100*(n-49) + i + l + return d, m, y +} + +// timeFromExcelTime provides function to convert an excelTime representation +// (stored as a floating point number) to a time.Time. +func timeFromExcelTime(excelTime float64, date1904 bool) time.Time { + var date time.Time + var intPart = int64(excelTime) + // Excel uses Julian dates prior to March 1st 1900, and Gregorian + // thereafter. + if intPart <= 61 { + const OFFSET1900 = 15018.0 + const OFFSET1904 = 16480.0 + const MJD0 float64 = 2400000.5 + var date time.Time + if date1904 { + date = julianDateToGregorianTime(MJD0, excelTime+OFFSET1904) + } else { + date = julianDateToGregorianTime(MJD0, excelTime+OFFSET1900) + } + return date + } + var floatPart = excelTime - float64(intPart) + var dayNanoSeconds float64 = 24 * 60 * 60 * 1000 * 1000 * 1000 + if date1904 { + date = time.Date(1904, 1, 1, 0, 0, 0, 0, time.UTC) + } else { + date = time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC) + } + durationDays := time.Duration(intPart) * time.Hour * 24 + durationPart := time.Duration(dayNanoSeconds * floatPart) + return date.Add(durationDays).Add(durationPart) +} diff --git a/excelize.go b/excelize.go index 39adb87..18d08e2 100644 --- a/excelize.go +++ b/excelize.go @@ -4,11 +4,13 @@ import ( "archive/zip" "bytes" "encoding/xml" + "fmt" "io" "io/ioutil" "os" "strconv" "strings" + "time" ) // File define a populated XLSX file struct. @@ -84,8 +86,13 @@ func (f *File) SetCellValue(sheet, axis string, value interface{}) { f.SetCellStr(sheet, axis, t) case []byte: f.SetCellStr(sheet, axis, string(t)) - default: + case time.Time: + f.SetCellDefault(sheet, axis, strconv.FormatFloat(float64(timeToExcelTime(timeToUTCTime(value.(time.Time)))), 'f', -1, 32)) + f.SetCellStyle(sheet, axis, axis, `{"number_format": 22}`) + case nil: f.SetCellStr(sheet, axis, "") + default: + f.SetCellStr(sheet, axis, fmt.Sprintf("%v", value)) } } diff --git a/excelize_test.go b/excelize_test.go index 7a8dcf3..d16937b 100644 --- a/excelize_test.go +++ b/excelize_test.go @@ -7,6 +7,7 @@ import ( "io/ioutil" "strconv" "testing" + "time" ) func TestOpenFile(t *testing.T) { @@ -66,6 +67,8 @@ func TestOpenFile(t *testing.T) { xlsx.SetCellValue("Sheet2", "F2", float32(42)) xlsx.SetCellValue("Sheet2", "F2", float64(42)) xlsx.SetCellValue("Sheet2", "G2", nil) + xlsx.SetCellValue("Sheet2", "G3", uint8(8)) + xlsx.SetCellValue("Sheet2", "G4", time.Now()) // Test completion column. xlsx.SetCellValue("Sheet2", "M2", nil) // Test read cell value with given axis large than exists row. @@ -322,18 +325,40 @@ func TestSetCellStyleBorder(t *testing.T) { } } -func TestSetCellStyleFill(t *testing.T) { +func TestSetCellStyleNumberFormat(t *testing.T) { xlsx, err := OpenFile("./test/Workbook_2.xlsx") if err != nil { t.Log(err) } - xlsx.SetCellValue("Sheet1", "N23", 42920.5) // Test only set fill and number format for a cell. - err = xlsx.SetCellStyle("Sheet1", "N23", "N23", `{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":4},"number_format":22}`) + col := []string{"L", "M", "N", "O", "P"} + data := []int{0, 1, 2, 3, 4, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49} + value := []string{"37947.7500001", "-37947.7500001", "0.007", "2.1", "String"} + for i, v := range value { + for k, d := range data { + c := col[i] + strconv.Itoa(k+1) + var val float64 + val, err = strconv.ParseFloat(v, 64) + if err != nil { + xlsx.SetCellValue("Sheet2", c, v) + } else { + xlsx.SetCellValue("Sheet2", c, val) + } + err := xlsx.SetCellStyle("Sheet2", c, c, `{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":5},"number_format": `+strconv.Itoa(d)+`}`) + if err != nil { + t.Log(err) + } + t.Log(xlsx.GetCellValue("Sheet2", c)) + } + } + err = xlsx.Save() if err != nil { t.Log(err) } - err = xlsx.SetCellStyle("Sheet1", "N24", "N24", `{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":5},"number_format":23}`) +} + +func TestSetCellStyleFill(t *testing.T) { + xlsx, err := OpenFile("./test/Workbook_2.xlsx") if err != nil { t.Log(err) } @@ -141,10 +141,10 @@ func (xlsx *xlsxC) getValueFrom(f *File, d *xlsxSST) (string, error) { } return value, nil } - return d.SI[xlsxSI].T, nil + return f.formattedValue(xlsx.S, d.SI[xlsxSI].T), nil case "str": - return xlsx.V, nil + return f.formattedValue(xlsx.S, xlsx.V), nil default: - return xlsx.V, nil + return f.formattedValue(xlsx.S, xlsx.V), nil } } @@ -3,6 +3,8 @@ package excelize import ( "encoding/json" "encoding/xml" + "fmt" + "math" "strconv" "strings" ) @@ -45,6 +47,183 @@ var builtInNumFmt = map[int]string{ 49: "@", } +// builtInNumFmtFunc defined the format conversion functions map. Partial format +// code doesn't support currently and will return original string. +var builtInNumFmtFunc = map[int]func(i int, v string) string{ + 0: formatToString, + 1: formatToInt, + 2: formatToFloat, + 3: formatToInt, + 4: formatToFloat, + 9: formatToC, + 10: formatToD, + 11: formatToE, + 12: formatToString, // Doesn't support currently + 13: formatToString, // Doesn't support currently + 14: parseTime, + 15: parseTime, + 16: parseTime, + 17: parseTime, + 18: parseTime, + 19: parseTime, + 20: parseTime, + 21: parseTime, + 22: parseTime, + 37: formatToA, + 38: formatToA, + 39: formatToB, + 40: formatToB, + 41: formatToString, // Doesn't support currently + 42: formatToString, // Doesn't support currently + 43: formatToString, // Doesn't support currently + 44: formatToString, // Doesn't support currently + 45: parseTime, + 46: parseTime, + 47: parseTime, + 48: formatToE, + 49: formatToString, +} + +// formatToString provides function to return original string by given built-in +// number formats code and cell string. +func formatToString(i int, v string) string { + return v +} + +// formatToInt provides function to convert original string to integer format as +// string type by given built-in number formats code and cell string. +func formatToInt(i int, v string) string { + f, err := strconv.ParseFloat(v, 64) + if err != nil { + return v + } + return fmt.Sprintf("%d", int(f)) +} + +// formatToFloat provides function to convert original string to float format as +// string type by given built-in number formats code and cell string. +func formatToFloat(i int, v string) string { + f, err := strconv.ParseFloat(v, 64) + if err != nil { + return v + } + return fmt.Sprintf("%.2f", f) +} + +// formatToA provides function to convert original string to special format as +// string type by given built-in number formats code and cell string. +func formatToA(i int, v string) string { + f, err := strconv.ParseFloat(v, 64) + if err != nil { + return v + } + if f < 0 { + t := int(math.Abs(f)) + return fmt.Sprintf("(%d)", t) + } + t := int(f) + return fmt.Sprintf("%d", t) +} + +// formatToB provides function to convert original string to special format as +// string type by given built-in number formats code and cell string. +func formatToB(i int, v string) string { + f, err := strconv.ParseFloat(v, 64) + if err != nil { + return v + } + if f < 0 { + return fmt.Sprintf("(%.2f)", f) + } + return fmt.Sprintf("%.2f", f) +} + +// formatToC provides function to convert original string to special format as +// string type by given built-in number formats code and cell string. +func formatToC(i int, v string) string { + f, err := strconv.ParseFloat(v, 64) + if err != nil { + return v + } + f = f * 100 + return fmt.Sprintf("%d%%", int(f)) +} + +// formatToD provides function to convert original string to special format as +// string type by given built-in number formats code and cell string. +func formatToD(i int, v string) string { + f, err := strconv.ParseFloat(v, 64) + if err != nil { + return v + } + f = f * 100 + return fmt.Sprintf("%.2f%%", f) +} + +// formatToE provides function to convert original string to special format as +// string type by given built-in number formats code and cell string. +func formatToE(i int, v string) string { + f, err := strconv.ParseFloat(v, 64) + if err != nil { + return v + } + return fmt.Sprintf("%.e", f) +} + +// parseTime provides function to returns a string parsed using time.Time. +// Replace Excel placeholders with Go time placeholders. For example, replace +// yyyy with 2006. These are in a specific order, due to the fact that m is used +// in month, minute, and am/pm. It would be easier to fix that with regular +// expressions, but if it's possible to keep this simple it would be easier to +// maintain. Full-length month and days (e.g. March, Tuesday) have letters in +// them that would be replaced by other characters below (such as the 'h' in +// March, or the 'd' in Tuesday) below. First we convert them to arbitrary +// characters unused in Excel Date formats, and then at the end, turn them to +// what they should actually be. +func parseTime(i int, v string) string { + f, err := strconv.ParseFloat(v, 64) + if err != nil { + return v + } + val := timeFromExcelTime(f, false) + format := builtInNumFmt[i] + + replacements := []struct{ xltime, gotime string }{ + {"yyyy", "2006"}, + {"yy", "06"}, + {"mmmm", "%%%%"}, + {"dddd", "&&&&"}, + {"dd", "02"}, + {"d", "2"}, + {"mmm", "Jan"}, + {"mmss", "0405"}, + {"ss", "05"}, + {"hh", "15"}, + {"h", "3"}, + {"mm:", "04:"}, + {":mm", ":04"}, + {"mm", "01"}, + {"am/pm", "pm"}, + {"m/", "1/"}, + {"%%%%", "January"}, + {"&&&&", "Monday"}, + } + for _, repl := range replacements { + format = strings.Replace(format, repl.xltime, repl.gotime, 1) + } + // If the hour is optional, strip it out, along with the possible dangling + // colon that would remain. + if val.Hour() < 1 { + format = strings.Replace(format, "]:", "]", 1) + format = strings.Replace(format, "[3]", "", 1) + format = strings.Replace(format, "[15]", "", 1) + } else { + format = strings.Replace(format, "[3]", "3", 1) + format = strings.Replace(format, "[15]", "15", 1) + } + return val.Format(format) +} + // parseFormatStyleSet provides function to parse the format settings of the // borders. func parseFormatStyleSet(style string) (*formatCellStyle, error) { |