From cbe919fdf6c00733513494680b89171b8b1b41a1 Mon Sep 17 00:00:00 2001 From: xuri Date: Sun, 4 Aug 2019 20:24:59 +0800 Subject: New feature: sparkline supported --- sparkline.go | 509 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 509 insertions(+) create mode 100644 sparkline.go (limited to 'sparkline.go') diff --git a/sparkline.go b/sparkline.go new file mode 100644 index 0000000..73e125e --- /dev/null +++ b/sparkline.go @@ -0,0 +1,509 @@ +// Copyright 2016 - 2019 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 files. Support reads and writes XLSX file generated by +// Microsoft Excel™ 2007 and later. Support save file without losing original +// charts of XLSX. This library needs Go version 1.8 or later. + +package excelize + +import ( + "encoding/xml" + "errors" + "strings" +) + +// addSparklineGroupByStyle provides a function to create x14:sparklineGroups +// element by given sparkline style ID. +func (f *File) addSparklineGroupByStyle(ID int) *xlsxX14SparklineGroup { + groups := []*xlsxX14SparklineGroup{ + { + ColorSeries: &xlsxTabColor{Theme: 4, Tint: -0.499984740745262}, + ColorNegative: &xlsxTabColor{Theme: 5}, + ColorMarkers: &xlsxTabColor{Theme: 4, Tint: -0.499984740745262}, + ColorFirst: &xlsxTabColor{Theme: 4, Tint: 0.39997558519241921}, + ColorLast: &xlsxTabColor{Theme: 4, Tint: 0.39997558519241921}, + ColorHigh: &xlsxTabColor{Theme: 4}, + ColorLow: &xlsxTabColor{Theme: 4}, + }, // 0 + { + ColorSeries: &xlsxTabColor{Theme: 4, Tint: -0.499984740745262}, + ColorNegative: &xlsxTabColor{Theme: 5}, + ColorMarkers: &xlsxTabColor{Theme: 4, Tint: -0.499984740745262}, + ColorFirst: &xlsxTabColor{Theme: 4, Tint: 0.39997558519241921}, + ColorLast: &xlsxTabColor{Theme: 4, Tint: 0.39997558519241921}, + ColorHigh: &xlsxTabColor{Theme: 4}, + ColorLow: &xlsxTabColor{Theme: 4}, + }, // 1 + { + ColorSeries: &xlsxTabColor{Theme: 5, Tint: -0.499984740745262}, + ColorNegative: &xlsxTabColor{Theme: 6}, + ColorMarkers: &xlsxTabColor{Theme: 5, Tint: -0.499984740745262}, + ColorFirst: &xlsxTabColor{Theme: 5, Tint: 0.39997558519241921}, + ColorLast: &xlsxTabColor{Theme: 5, Tint: 0.39997558519241921}, + ColorHigh: &xlsxTabColor{Theme: 5}, + ColorLow: &xlsxTabColor{Theme: 5}, + }, // 2 + { + ColorSeries: &xlsxTabColor{Theme: 6, Tint: -0.499984740745262}, + ColorNegative: &xlsxTabColor{Theme: 7}, + ColorMarkers: &xlsxTabColor{Theme: 6, Tint: -0.499984740745262}, + ColorFirst: &xlsxTabColor{Theme: 6, Tint: 0.39997558519241921}, + ColorLast: &xlsxTabColor{Theme: 6, Tint: 0.39997558519241921}, + ColorHigh: &xlsxTabColor{Theme: 6}, + ColorLow: &xlsxTabColor{Theme: 6}, + }, // 3 + { + ColorSeries: &xlsxTabColor{Theme: 7, Tint: -0.499984740745262}, + ColorNegative: &xlsxTabColor{Theme: 8}, + ColorMarkers: &xlsxTabColor{Theme: 7, Tint: -0.499984740745262}, + ColorFirst: &xlsxTabColor{Theme: 7, Tint: 0.39997558519241921}, + ColorLast: &xlsxTabColor{Theme: 7, Tint: 0.39997558519241921}, + ColorHigh: &xlsxTabColor{Theme: 7}, + ColorLow: &xlsxTabColor{Theme: 7}, + }, // 4 + { + ColorSeries: &xlsxTabColor{Theme: 8, Tint: -0.499984740745262}, + ColorNegative: &xlsxTabColor{Theme: 9}, + ColorMarkers: &xlsxTabColor{Theme: 8, Tint: -0.499984740745262}, + ColorFirst: &xlsxTabColor{Theme: 8, Tint: 0.39997558519241921}, + ColorLast: &xlsxTabColor{Theme: 8, Tint: 0.39997558519241921}, + ColorHigh: &xlsxTabColor{Theme: 8}, + ColorLow: &xlsxTabColor{Theme: 8}, + }, // 5 + { + ColorSeries: &xlsxTabColor{Theme: 9, Tint: -0.499984740745262}, + ColorNegative: &xlsxTabColor{Theme: 4}, + ColorMarkers: &xlsxTabColor{Theme: 9, Tint: -0.499984740745262}, + ColorFirst: &xlsxTabColor{Theme: 9, Tint: 0.39997558519241921}, + ColorLast: &xlsxTabColor{Theme: 9, Tint: 0.39997558519241921}, + ColorHigh: &xlsxTabColor{Theme: 9}, + ColorLow: &xlsxTabColor{Theme: 9}, + }, // 6 + { + ColorSeries: &xlsxTabColor{Theme: 4, Tint: -0.249977111117893}, + ColorNegative: &xlsxTabColor{Theme: 5}, + ColorMarkers: &xlsxTabColor{Theme: 5, Tint: -0.249977111117893}, + ColorFirst: &xlsxTabColor{Theme: 5, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 5, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 5}, + ColorLow: &xlsxTabColor{Theme: 5}, + }, // 7 + { + ColorSeries: &xlsxTabColor{Theme: 5, Tint: -0.249977111117893}, + ColorNegative: &xlsxTabColor{Theme: 6}, + ColorMarkers: &xlsxTabColor{Theme: 6, Tint: -0.249977111117893}, + ColorFirst: &xlsxTabColor{Theme: 6, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 6, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 6, Tint: -0.249977111117893}, + ColorLow: &xlsxTabColor{Theme: 6, Tint: -0.249977111117893}, + }, // 8 + { + ColorSeries: &xlsxTabColor{Theme: 6, Tint: -0.249977111117893}, + ColorNegative: &xlsxTabColor{Theme: 7}, + ColorMarkers: &xlsxTabColor{Theme: 7, Tint: -0.249977111117893}, + ColorFirst: &xlsxTabColor{Theme: 7, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 7, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 7, Tint: -0.249977111117893}, + ColorLow: &xlsxTabColor{Theme: 7, Tint: -0.249977111117893}, + }, // 9 + { + ColorSeries: &xlsxTabColor{Theme: 7, Tint: -0.249977111117893}, + ColorNegative: &xlsxTabColor{Theme: 8}, + ColorMarkers: &xlsxTabColor{Theme: 8, Tint: -0.249977111117893}, + ColorFirst: &xlsxTabColor{Theme: 8, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 8, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 8, Tint: -0.249977111117893}, + ColorLow: &xlsxTabColor{Theme: 8, Tint: -0.249977111117893}, + }, // 10 + { + ColorSeries: &xlsxTabColor{Theme: 8, Tint: -0.249977111117893}, + ColorNegative: &xlsxTabColor{Theme: 9}, + ColorMarkers: &xlsxTabColor{Theme: 9, Tint: -0.249977111117893}, + ColorFirst: &xlsxTabColor{Theme: 9, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 9, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 9, Tint: -0.249977111117893}, + ColorLow: &xlsxTabColor{Theme: 9, Tint: -0.249977111117893}, + }, // 11 + { + ColorSeries: &xlsxTabColor{Theme: 9, Tint: -0.249977111117893}, + ColorNegative: &xlsxTabColor{Theme: 4}, + ColorMarkers: &xlsxTabColor{Theme: 4, Tint: -0.249977111117893}, + ColorFirst: &xlsxTabColor{Theme: 4, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 4, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 4, Tint: -0.249977111117893}, + ColorLow: &xlsxTabColor{Theme: 4, Tint: -0.249977111117893}, + }, // 12 + { + ColorSeries: &xlsxTabColor{Theme: 4}, + ColorNegative: &xlsxTabColor{Theme: 5}, + ColorMarkers: &xlsxTabColor{Theme: 4, Tint: -0.249977111117893}, + ColorFirst: &xlsxTabColor{Theme: 4, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 4, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 4, Tint: -0.249977111117893}, + ColorLow: &xlsxTabColor{Theme: 4, Tint: -0.249977111117893}, + }, // 13 + { + ColorSeries: &xlsxTabColor{Theme: 5}, + ColorNegative: &xlsxTabColor{Theme: 6}, + ColorMarkers: &xlsxTabColor{Theme: 5, Tint: -0.249977111117893}, + ColorFirst: &xlsxTabColor{Theme: 5, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 5, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 5, Tint: -0.249977111117893}, + ColorLow: &xlsxTabColor{Theme: 5, Tint: -0.249977111117893}, + }, // 14 + { + ColorSeries: &xlsxTabColor{Theme: 6}, + ColorNegative: &xlsxTabColor{Theme: 7}, + ColorMarkers: &xlsxTabColor{Theme: 6, Tint: -0.249977111117893}, + ColorFirst: &xlsxTabColor{Theme: 6, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 6, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 6, Tint: -0.249977111117893}, + ColorLow: &xlsxTabColor{Theme: 6, Tint: -0.249977111117893}, + }, // 15 + { + ColorSeries: &xlsxTabColor{Theme: 7}, + ColorNegative: &xlsxTabColor{Theme: 8}, + ColorMarkers: &xlsxTabColor{Theme: 7, Tint: -0.249977111117893}, + ColorFirst: &xlsxTabColor{Theme: 7, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 7, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 7, Tint: -0.249977111117893}, + ColorLow: &xlsxTabColor{Theme: 7, Tint: -0.249977111117893}, + }, // 16 + { + ColorSeries: &xlsxTabColor{Theme: 8}, + ColorNegative: &xlsxTabColor{Theme: 9}, + ColorMarkers: &xlsxTabColor{Theme: 8, Tint: -0.249977111117893}, + ColorFirst: &xlsxTabColor{Theme: 8, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 8, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 8, Tint: -0.249977111117893}, + ColorLow: &xlsxTabColor{Theme: 8, Tint: -0.249977111117893}, + }, // 17 + { + ColorSeries: &xlsxTabColor{Theme: 9}, + ColorNegative: &xlsxTabColor{Theme: 4}, + ColorMarkers: &xlsxTabColor{Theme: 9, Tint: -0.249977111117893}, + ColorFirst: &xlsxTabColor{Theme: 9, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 9, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 9, Tint: -0.249977111117893}, + ColorLow: &xlsxTabColor{Theme: 9, Tint: -0.249977111117893}, + }, // 18 + { + ColorSeries: &xlsxTabColor{Theme: 4, Tint: 0.39997558519241921}, + ColorNegative: &xlsxTabColor{Theme: 0, Tint: -0.499984740745262}, + ColorMarkers: &xlsxTabColor{Theme: 4, Tint: 0.79998168889431442}, + ColorFirst: &xlsxTabColor{Theme: 4, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 4, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 4, Tint: -0.499984740745262}, + ColorLow: &xlsxTabColor{Theme: 4, Tint: -0.499984740745262}, + }, // 19 + { + ColorSeries: &xlsxTabColor{Theme: 5, Tint: 0.39997558519241921}, + ColorNegative: &xlsxTabColor{Theme: 0, Tint: -0.499984740745262}, + ColorMarkers: &xlsxTabColor{Theme: 5, Tint: 0.79998168889431442}, + ColorFirst: &xlsxTabColor{Theme: 5, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 5, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 5, Tint: -0.499984740745262}, + ColorLow: &xlsxTabColor{Theme: 5, Tint: -0.499984740745262}, + }, // 20 + { + ColorSeries: &xlsxTabColor{Theme: 6, Tint: 0.39997558519241921}, + ColorNegative: &xlsxTabColor{Theme: 0, Tint: -0.499984740745262}, + ColorMarkers: &xlsxTabColor{Theme: 6, Tint: 0.79998168889431442}, + ColorFirst: &xlsxTabColor{Theme: 6, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 6, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 6, Tint: -0.499984740745262}, + ColorLow: &xlsxTabColor{Theme: 6, Tint: -0.499984740745262}, + }, // 21 + { + ColorSeries: &xlsxTabColor{Theme: 7, Tint: 0.39997558519241921}, + ColorNegative: &xlsxTabColor{Theme: 0, Tint: -0.499984740745262}, + ColorMarkers: &xlsxTabColor{Theme: 7, Tint: 0.79998168889431442}, + ColorFirst: &xlsxTabColor{Theme: 7, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 7, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 7, Tint: -0.499984740745262}, + ColorLow: &xlsxTabColor{Theme: 7, Tint: -0.499984740745262}, + }, // 22 + { + ColorSeries: &xlsxTabColor{Theme: 8, Tint: 0.39997558519241921}, + ColorNegative: &xlsxTabColor{Theme: 0, Tint: -0.499984740745262}, + ColorMarkers: &xlsxTabColor{Theme: 8, Tint: 0.79998168889431442}, + ColorFirst: &xlsxTabColor{Theme: 8, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 8, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 8, Tint: -0.499984740745262}, + ColorLow: &xlsxTabColor{Theme: 8, Tint: -0.499984740745262}, + }, // 23 + { + ColorSeries: &xlsxTabColor{Theme: 9, Tint: 0.39997558519241921}, + ColorNegative: &xlsxTabColor{Theme: 0, Tint: -0.499984740745262}, + ColorMarkers: &xlsxTabColor{Theme: 9, Tint: 0.79998168889431442}, + ColorFirst: &xlsxTabColor{Theme: 9, Tint: -0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 9, Tint: -0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 9, Tint: -0.499984740745262}, + ColorLow: &xlsxTabColor{Theme: 9, Tint: -0.499984740745262}, + }, // 24 + { + ColorSeries: &xlsxTabColor{Theme: 1, Tint: 0.499984740745262}, + ColorNegative: &xlsxTabColor{Theme: 1, Tint: 0.249977111117893}, + ColorMarkers: &xlsxTabColor{Theme: 1, Tint: 0.249977111117893}, + ColorFirst: &xlsxTabColor{Theme: 1, Tint: 0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 1, Tint: 0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 1, Tint: 0.249977111117893}, + ColorLow: &xlsxTabColor{Theme: 1, Tint: 0.249977111117893}, + }, // 25 + { + ColorSeries: &xlsxTabColor{Theme: 1, Tint: 0.34998626667073579}, + ColorNegative: &xlsxTabColor{Theme: 0, Tint: 0.249977111117893}, + ColorMarkers: &xlsxTabColor{Theme: 0, Tint: 0.249977111117893}, + ColorFirst: &xlsxTabColor{Theme: 0, Tint: 0.249977111117893}, + ColorLast: &xlsxTabColor{Theme: 0, Tint: 0.249977111117893}, + ColorHigh: &xlsxTabColor{Theme: 0, Tint: 0.249977111117893}, + ColorLow: &xlsxTabColor{Theme: 0, Tint: 0.249977111117893}, + }, // 26 + { + ColorSeries: &xlsxTabColor{RGB: "FF323232"}, + ColorNegative: &xlsxTabColor{RGB: "FFD00000"}, + ColorMarkers: &xlsxTabColor{RGB: "FFD00000"}, + ColorFirst: &xlsxTabColor{RGB: "FFD00000"}, + ColorLast: &xlsxTabColor{RGB: "FFD00000"}, + ColorHigh: &xlsxTabColor{RGB: "FFD00000"}, + ColorLow: &xlsxTabColor{RGB: "FFD00000"}, + }, // 27 + { + ColorSeries: &xlsxTabColor{RGB: "FF000000"}, + ColorNegative: &xlsxTabColor{RGB: "FF0070C0"}, + ColorMarkers: &xlsxTabColor{RGB: "FF0070C0"}, + ColorFirst: &xlsxTabColor{RGB: "FF0070C0"}, + ColorLast: &xlsxTabColor{RGB: "FF0070C0"}, + ColorHigh: &xlsxTabColor{RGB: "FF0070C0"}, + ColorLow: &xlsxTabColor{RGB: "FF0070C0"}, + }, // 28 + { + ColorSeries: &xlsxTabColor{RGB: "FF376092"}, + ColorNegative: &xlsxTabColor{RGB: "FFD00000"}, + ColorMarkers: &xlsxTabColor{RGB: "FFD00000"}, + ColorFirst: &xlsxTabColor{RGB: "FFD00000"}, + ColorLast: &xlsxTabColor{RGB: "FFD00000"}, + ColorHigh: &xlsxTabColor{RGB: "FFD00000"}, + ColorLow: &xlsxTabColor{RGB: "FFD00000"}, + }, // 29 + { + ColorSeries: &xlsxTabColor{RGB: "FF0070C0"}, + ColorNegative: &xlsxTabColor{RGB: "FF000000"}, + ColorMarkers: &xlsxTabColor{RGB: "FF000000"}, + ColorFirst: &xlsxTabColor{RGB: "FF000000"}, + ColorLast: &xlsxTabColor{RGB: "FF000000"}, + ColorHigh: &xlsxTabColor{RGB: "FF000000"}, + ColorLow: &xlsxTabColor{RGB: "FF000000"}, + }, // 30 + { + ColorSeries: &xlsxTabColor{RGB: "FF5F5F5F"}, + ColorNegative: &xlsxTabColor{RGB: "FFFFB620"}, + ColorMarkers: &xlsxTabColor{RGB: "FFD70077"}, + ColorFirst: &xlsxTabColor{RGB: "FF5687C2"}, + ColorLast: &xlsxTabColor{RGB: "FF359CEB"}, + ColorHigh: &xlsxTabColor{RGB: "FF56BE79"}, + ColorLow: &xlsxTabColor{RGB: "FFFF5055"}, + }, // 31 + { + ColorSeries: &xlsxTabColor{RGB: "FF5687C2"}, + ColorNegative: &xlsxTabColor{RGB: "FFFFB620"}, + ColorMarkers: &xlsxTabColor{RGB: "FFD70077"}, + ColorFirst: &xlsxTabColor{RGB: "FF777777"}, + ColorLast: &xlsxTabColor{RGB: "FF359CEB"}, + ColorHigh: &xlsxTabColor{RGB: "FF56BE79"}, + ColorLow: &xlsxTabColor{RGB: "FFFF5055"}, + }, // 32 + { + ColorSeries: &xlsxTabColor{RGB: "FFC6EFCE"}, + ColorNegative: &xlsxTabColor{RGB: "FFFFC7CE"}, + ColorMarkers: &xlsxTabColor{RGB: "FF8CADD6"}, + ColorFirst: &xlsxTabColor{RGB: "FFFFDC47"}, + ColorLast: &xlsxTabColor{RGB: "FFFFEB9C"}, + ColorHigh: &xlsxTabColor{RGB: "FF60D276"}, + ColorLow: &xlsxTabColor{RGB: "FFFF5367"}, + }, // 33 + { + ColorSeries: &xlsxTabColor{RGB: "FF00B050"}, + ColorNegative: &xlsxTabColor{RGB: "FFFF0000"}, + ColorMarkers: &xlsxTabColor{RGB: "FF0070C0"}, + ColorFirst: &xlsxTabColor{RGB: "FFFFC000"}, + ColorLast: &xlsxTabColor{RGB: "FFFFC000"}, + ColorHigh: &xlsxTabColor{RGB: "FF00B050"}, + ColorLow: &xlsxTabColor{RGB: "FFFF0000"}, + }, // 34 + { + ColorSeries: &xlsxTabColor{Theme: 3}, + ColorNegative: &xlsxTabColor{Theme: 9}, + ColorMarkers: &xlsxTabColor{Theme: 8}, + ColorFirst: &xlsxTabColor{Theme: 4}, + ColorLast: &xlsxTabColor{Theme: 5}, + ColorHigh: &xlsxTabColor{Theme: 6}, + ColorLow: &xlsxTabColor{Theme: 7}, + }, // 35 + { + ColorSeries: &xlsxTabColor{Theme: 1}, + ColorNegative: &xlsxTabColor{Theme: 9}, + ColorMarkers: &xlsxTabColor{Theme: 8}, + ColorFirst: &xlsxTabColor{Theme: 4}, + ColorLast: &xlsxTabColor{Theme: 5}, + ColorHigh: &xlsxTabColor{Theme: 6}, + ColorLow: &xlsxTabColor{Theme: 7}, + }, // 36 + } + return groups[ID] +} + +// AddSparkline provides a function to add sparklines to the worksheet by +// given formatting options. Sparklines are small charts that fit in a single +// cell and are used to show trends in data. Sparklines are a feature of Excel +// 2010 and later only. You can write them to an XLSX file that can be read by +// Excel 2007 but they won't be displayed. For example, add a grouped +// sparkline. Changes are applied to all three: +// +// err := f.AddSparkline("Sheet1", &excelize.SparklineOption{ +// Location: []string{"A1", "A2", "A3"}, +// Range: []string{"Sheet2!A1:J1", "Sheet2!A2:J2", "Sheet2!A3:J3"}, +// Markers: true, +// }) +// +// The following shows the formatting options of sparkline supported by excelize: +// +// Parameter | Description +// -----------+-------------------------------------------- +// Location | Required, must have the same number with 'Range' parameter +// Range |Required, must have the same number with 'Location' parameter +// Type | Enumeration value: line, column, win_loss +// Style | Value range: 0 - 35 +// Hight | Toggle sparkine high points +// Low | Toggle sparkine low points +// First | Toggle sparkine first points +// Last | Toggle sparkine last points +// Negative | Toggle sparkine negative points +// Markers | Toggle sparkine markers +// ColorAxis | An RGB Color is specified as RRGGBB +// Axis | Show sparkline axis +// +func (f *File) AddSparkline(sheet string, opt *SparklineOption) error { + // parameter validation + ws, err := f.parseFormatAddSparklineSet(sheet, opt) + if err != nil { + return err + } + // Handle the sparkline type + sparkType := "line" + sparkTypes := map[string]string{"line": "line", "column": "column", "win_loss": "stacked"} + if opt.Type != "" { + specifiedSparkTypes, ok := sparkTypes[opt.Type] + if !ok { + return errors.New("parameter 'Type' must be 'line', 'column' or 'win_loss'") + } + sparkType = specifiedSparkTypes + } + group := f.addSparklineGroupByStyle(opt.Style) + group.Type = sparkType + group.ColorAxis = &xlsxColor{RGB: "FF000000"} + group.DisplayEmptyCellsAs = "gap" + group.High = opt.High + group.Low = opt.Low + group.First = opt.First + group.Last = opt.Last + group.Negative = opt.Negative + group.DisplayXAxis = opt.Axis + group.Markers = opt.Markers + if opt.SeriesColor != "" { + group.ColorSeries = &xlsxTabColor{ + RGB: getPaletteColor(opt.SeriesColor), + } + } + if opt.Reverse { + group.RightToLeft = opt.Reverse + } + f.addSparkline(opt, group) + if ws.ExtLst.Ext != "" { // append mode ext + decodeExtLst := decodeWorksheetExt{} + err = xml.Unmarshal([]byte(""+ws.ExtLst.Ext+""), &decodeExtLst) + if err != nil { + return err + } + for idx, ext := range decodeExtLst.Ext { + // hack: add back missing namespace + decodeExtLst.Ext[idx].XMLNSX14 = decodeExtLst.Ext[idx].X14 + decodeExtLst.Ext[idx].XMLNSX15 = decodeExtLst.Ext[idx].X15 + decodeExtLst.Ext[idx].XMLNSX14 = "" + decodeExtLst.Ext[idx].XMLNSX15 = "" + if ext.URI == ExtURISparklineGroups { + decodeSparklineGroups := decodeX14SparklineGroups{} + _ = xml.Unmarshal([]byte(ext.Content), &decodeSparklineGroups) + sparklineGroupBytes, _ := xml.Marshal(group) + groups := xlsxX14SparklineGroups{ + XMLNSXM: NameSpaceSpreadSheetExcel2006Main, + Content: decodeSparklineGroups.Content + string(sparklineGroupBytes), + } + sparklineGroupsBytes, _ := xml.Marshal(groups) + decodeExtLst.Ext[idx].Content = string(sparklineGroupsBytes) + } + } + extLstBytes, _ := xml.Marshal(decodeExtLst) + extLst := string(extLstBytes) + ws.ExtLst = &xlsxExtLst{ + Ext: strings.TrimSuffix(strings.TrimPrefix(extLst, ""), ""), + } + } else { + groups := xlsxX14SparklineGroups{ + XMLNSXM: NameSpaceSpreadSheetExcel2006Main, + SparklineGroups: []*xlsxX14SparklineGroup{group}, + } + sparklineGroupsBytes, _ := xml.Marshal(groups) + extLst := xlsxWorksheetExt{ + XMLNSX14: NameSpaceSpreadSheetX14, + URI: ExtURISparklineGroups, + Content: string(sparklineGroupsBytes), + } + extBytes, _ := xml.Marshal(extLst) + ws.ExtLst.Ext = string(extBytes) + } + return nil +} + +// parseFormatAddSparklineSet provides a function to validate sparkline +// properties. +func (f *File) parseFormatAddSparklineSet(sheet string, opt *SparklineOption) (*xlsxWorksheet, error) { + ws, err := f.workSheetReader(sheet) + if err != nil { + return ws, err + } + if opt == nil { + return ws, errors.New("parameter is required") + } + if len(opt.Location) < 1 { + return ws, errors.New("parameter 'Location' is required") + } + if len(opt.Range) < 1 { + return ws, errors.New("parameter 'Range' is required") + } + // The ranges and locations must match.\ + if len(opt.Location) != len(opt.Range) { + return ws, errors.New(`must have the same number of 'Location' and 'Range' parameters`) + } + if opt.Style < 0 || opt.Style > 35 { + return ws, errors.New("parameter 'Style' must betweent 0-35") + } + if ws.ExtLst == nil { + ws.ExtLst = &xlsxExtLst{} + } + return ws, err +} + +// addSparkline provides a function to create a sparkline in a sparkline group +// by given properties. +func (f *File) addSparkline(opt *SparklineOption, group *xlsxX14SparklineGroup) { + for idx, location := range opt.Location { + group.Sparklines.Sparkline = append(group.Sparklines.Sparkline, &xlsxX14Sparkline{ + F: opt.Range[idx], + Sqref: location, + }) + } +} -- cgit v1.2.1 From 9c70d0ac868f66badf2663cc7b4b3c46d5411131 Mon Sep 17 00:00:00 2001 From: xuri Date: Sun, 11 Aug 2019 00:36:14 +0800 Subject: Documentation updated, Go 1.10+ required --- sparkline.go | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sparkline.go') diff --git a/sparkline.go b/sparkline.go index 73e125e..18eae6c 100644 --- a/sparkline.go +++ b/sparkline.go @@ -5,7 +5,7 @@ // Package excelize providing a set of functions that allow you to write to // and read from XLSX files. Support reads and writes XLSX file generated by // Microsoft Excel™ 2007 and later. Support save file without losing original -// charts of XLSX. This library needs Go version 1.8 or later. +// charts of XLSX. This library needs Go version 1.10 or later. package excelize -- cgit v1.2.1 From 407fb55c20a2524c4eccad9361120dee2a2719cd Mon Sep 17 00:00:00 2001 From: xuri Date: Wed, 21 Aug 2019 23:03:34 +0800 Subject: Update the Godoc --- sparkline.go | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-) (limited to 'sparkline.go') diff --git a/sparkline.go b/sparkline.go index 18eae6c..314ea83 100644 --- a/sparkline.go +++ b/sparkline.go @@ -374,15 +374,15 @@ func (f *File) addSparklineGroupByStyle(ID int) *xlsxX14SparklineGroup { // Parameter | Description // -----------+-------------------------------------------- // Location | Required, must have the same number with 'Range' parameter -// Range |Required, must have the same number with 'Location' parameter +// Range | Required, must have the same number with 'Location' parameter // Type | Enumeration value: line, column, win_loss // Style | Value range: 0 - 35 -// Hight | Toggle sparkine high points -// Low | Toggle sparkine low points -// First | Toggle sparkine first points -// Last | Toggle sparkine last points -// Negative | Toggle sparkine negative points -// Markers | Toggle sparkine markers +// Hight | Toggle sparkline high points +// Low | Toggle sparkline low points +// First | Toggle sparkline first points +// Last | Toggle sparkline last points +// Negative | Toggle sparkline negative points +// Markers | Toggle sparkline markers // ColorAxis | An RGB Color is specified as RRGGBB // Axis | Show sparkline axis // -- cgit v1.2.1 From a34d3b8c86d67d3ad0bc0dbedb69d3b4ebbc210f Mon Sep 17 00:00:00 2001 From: xuri Date: Tue, 24 Sep 2019 21:53:19 +0800 Subject: Compatibility improvement --- sparkline.go | 10 ++-------- 1 file changed, 2 insertions(+), 8 deletions(-) (limited to 'sparkline.go') diff --git a/sparkline.go b/sparkline.go index 314ea83..b09dbf4 100644 --- a/sparkline.go +++ b/sparkline.go @@ -429,11 +429,6 @@ func (f *File) AddSparkline(sheet string, opt *SparklineOption) error { return err } for idx, ext := range decodeExtLst.Ext { - // hack: add back missing namespace - decodeExtLst.Ext[idx].XMLNSX14 = decodeExtLst.Ext[idx].X14 - decodeExtLst.Ext[idx].XMLNSX15 = decodeExtLst.Ext[idx].X15 - decodeExtLst.Ext[idx].XMLNSX14 = "" - decodeExtLst.Ext[idx].XMLNSX15 = "" if ext.URI == ExtURISparklineGroups { decodeSparklineGroups := decodeX14SparklineGroups{} _ = xml.Unmarshal([]byte(ext.Content), &decodeSparklineGroups) @@ -458,9 +453,8 @@ func (f *File) AddSparkline(sheet string, opt *SparklineOption) error { } sparklineGroupsBytes, _ := xml.Marshal(groups) extLst := xlsxWorksheetExt{ - XMLNSX14: NameSpaceSpreadSheetX14, - URI: ExtURISparklineGroups, - Content: string(sparklineGroupsBytes), + URI: ExtURISparklineGroups, + Content: string(sparklineGroupsBytes), } extBytes, _ := xml.Marshal(extLst) ws.ExtLst.Ext = string(extBytes) -- cgit v1.2.1 From b1b3c0d15158abc71267da5893de020f047c3872 Mon Sep 17 00:00:00 2001 From: Alex Geer Date: Thu, 19 Dec 2019 19:30:48 +0300 Subject: =?UTF-8?q?Fix=20#539=20Fixed=20error=20opening=20excel=20file=20c?= =?UTF-8?q?reated=20in=20encoding=20d=E2=80=A6=20(#540)?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit * Fixed issue #539 Fixed error opening excel file created in encoding different from UTF-8, added logging of possible errors when decoding XML if the function does not provide exit with an error * Added test for CharsetReader * Fixed #discussion_r359397878 Discussion: https://github.com/360EntSecGroup-Skylar/excelize/pull/540#discussion_r359397878 * Fixed go fmt * go mod tidy and removed unused imports * The code has been refactored --- sparkline.go | 87 +++++++++++++++++++++++++++++++++++++++++------------------- 1 file changed, 60 insertions(+), 27 deletions(-) (limited to 'sparkline.go') diff --git a/sparkline.go b/sparkline.go index b09dbf4..9ad5087 100644 --- a/sparkline.go +++ b/sparkline.go @@ -10,8 +10,10 @@ package excelize import ( + "bytes" "encoding/xml" "errors" + "io" "strings" ) @@ -386,23 +388,40 @@ func (f *File) addSparklineGroupByStyle(ID int) *xlsxX14SparklineGroup { // ColorAxis | An RGB Color is specified as RRGGBB // Axis | Show sparkline axis // -func (f *File) AddSparkline(sheet string, opt *SparklineOption) error { +func (f *File) AddSparkline(sheet string, opt *SparklineOption) (err error) { + var ( + ws *xlsxWorksheet + sparkType string + sparkTypes map[string]string + specifiedSparkTypes string + ok bool + group *xlsxX14SparklineGroup + groups *xlsxX14SparklineGroups + decodeExtLst *decodeWorksheetExt + idx int + ext *xlsxWorksheetExt + decodeSparklineGroups *decodeX14SparklineGroups + sparklineGroupBytes []byte + sparklineGroupsBytes []byte + extLst string + extLstBytes, extBytes []byte + ) + // parameter validation - ws, err := f.parseFormatAddSparklineSet(sheet, opt) - if err != nil { - return err + if ws, err = f.parseFormatAddSparklineSet(sheet, opt); err != nil { + return } // Handle the sparkline type - sparkType := "line" - sparkTypes := map[string]string{"line": "line", "column": "column", "win_loss": "stacked"} + sparkType = "line" + sparkTypes = map[string]string{"line": "line", "column": "column", "win_loss": "stacked"} if opt.Type != "" { - specifiedSparkTypes, ok := sparkTypes[opt.Type] - if !ok { - return errors.New("parameter 'Type' must be 'line', 'column' or 'win_loss'") + if specifiedSparkTypes, ok = sparkTypes[opt.Type]; !ok { + err = errors.New("parameter 'Type' must be 'line', 'column' or 'win_loss'") + return } sparkType = specifiedSparkTypes } - group := f.addSparklineGroupByStyle(opt.Style) + group = f.addSparklineGroupByStyle(opt.Style) group.Type = sparkType group.ColorAxis = &xlsxColor{RGB: "FF000000"} group.DisplayEmptyCellsAs = "gap" @@ -423,43 +442,57 @@ func (f *File) AddSparkline(sheet string, opt *SparklineOption) error { } f.addSparkline(opt, group) if ws.ExtLst.Ext != "" { // append mode ext - decodeExtLst := decodeWorksheetExt{} - err = xml.Unmarshal([]byte(""+ws.ExtLst.Ext+""), &decodeExtLst) - if err != nil { - return err + decodeExtLst = new(decodeWorksheetExt) + if err = f.xmlNewDecoder(bytes.NewReader([]byte("" + ws.ExtLst.Ext + ""))). + Decode(decodeExtLst); err != nil && err != io.EOF { + return } - for idx, ext := range decodeExtLst.Ext { + for idx, ext = range decodeExtLst.Ext { if ext.URI == ExtURISparklineGroups { - decodeSparklineGroups := decodeX14SparklineGroups{} - _ = xml.Unmarshal([]byte(ext.Content), &decodeSparklineGroups) - sparklineGroupBytes, _ := xml.Marshal(group) - groups := xlsxX14SparklineGroups{ + decodeSparklineGroups = new(decodeX14SparklineGroups) + if err = f.xmlNewDecoder(bytes.NewReader([]byte(ext.Content))). + Decode(decodeSparklineGroups); err != nil && err != io.EOF { + return + } + if sparklineGroupBytes, err = xml.Marshal(group); err != nil { + return + } + groups = &xlsxX14SparklineGroups{ XMLNSXM: NameSpaceSpreadSheetExcel2006Main, Content: decodeSparklineGroups.Content + string(sparklineGroupBytes), } - sparklineGroupsBytes, _ := xml.Marshal(groups) + if sparklineGroupsBytes, err = xml.Marshal(groups); err != nil { + return + } decodeExtLst.Ext[idx].Content = string(sparklineGroupsBytes) } } - extLstBytes, _ := xml.Marshal(decodeExtLst) - extLst := string(extLstBytes) + if extLstBytes, err = xml.Marshal(decodeExtLst); err != nil { + return + } + extLst = string(extLstBytes) ws.ExtLst = &xlsxExtLst{ Ext: strings.TrimSuffix(strings.TrimPrefix(extLst, ""), ""), } } else { - groups := xlsxX14SparklineGroups{ + groups = &xlsxX14SparklineGroups{ XMLNSXM: NameSpaceSpreadSheetExcel2006Main, SparklineGroups: []*xlsxX14SparklineGroup{group}, } - sparklineGroupsBytes, _ := xml.Marshal(groups) - extLst := xlsxWorksheetExt{ + if sparklineGroupsBytes, err = xml.Marshal(groups); err != nil { + return + } + ext = &xlsxWorksheetExt{ URI: ExtURISparklineGroups, Content: string(sparklineGroupsBytes), } - extBytes, _ := xml.Marshal(extLst) + if extBytes, err = xml.Marshal(ext); err != nil { + return + } ws.ExtLst.Ext = string(extBytes) } - return nil + + return } // parseFormatAddSparklineSet provides a function to validate sparkline -- cgit v1.2.1 From ae2865d9237cfd27d7bc4fbef3870b3361597be8 Mon Sep 17 00:00:00 2001 From: xuri Date: Sun, 22 Dec 2019 00:02:09 +0800 Subject: Improve code coverage unit tests --- sparkline.go | 103 +++++++++++++++++++++++++++++++---------------------------- 1 file changed, 55 insertions(+), 48 deletions(-) (limited to 'sparkline.go') diff --git a/sparkline.go b/sparkline.go index 9ad5087..47c8d5a 100644 --- a/sparkline.go +++ b/sparkline.go @@ -390,21 +390,14 @@ func (f *File) addSparklineGroupByStyle(ID int) *xlsxX14SparklineGroup { // func (f *File) AddSparkline(sheet string, opt *SparklineOption) (err error) { var ( - ws *xlsxWorksheet - sparkType string - sparkTypes map[string]string - specifiedSparkTypes string - ok bool - group *xlsxX14SparklineGroup - groups *xlsxX14SparklineGroups - decodeExtLst *decodeWorksheetExt - idx int - ext *xlsxWorksheetExt - decodeSparklineGroups *decodeX14SparklineGroups - sparklineGroupBytes []byte - sparklineGroupsBytes []byte - extLst string - extLstBytes, extBytes []byte + ws *xlsxWorksheet + sparkType string + sparkTypes map[string]string + specifiedSparkTypes string + ok bool + group *xlsxX14SparklineGroup + groups *xlsxX14SparklineGroups + sparklineGroupsBytes, extBytes []byte ) // parameter validation @@ -442,38 +435,9 @@ func (f *File) AddSparkline(sheet string, opt *SparklineOption) (err error) { } f.addSparkline(opt, group) if ws.ExtLst.Ext != "" { // append mode ext - decodeExtLst = new(decodeWorksheetExt) - if err = f.xmlNewDecoder(bytes.NewReader([]byte("" + ws.ExtLst.Ext + ""))). - Decode(decodeExtLst); err != nil && err != io.EOF { + if err = f.appendSparkline(ws, group, groups); err != nil { return } - for idx, ext = range decodeExtLst.Ext { - if ext.URI == ExtURISparklineGroups { - decodeSparklineGroups = new(decodeX14SparklineGroups) - if err = f.xmlNewDecoder(bytes.NewReader([]byte(ext.Content))). - Decode(decodeSparklineGroups); err != nil && err != io.EOF { - return - } - if sparklineGroupBytes, err = xml.Marshal(group); err != nil { - return - } - groups = &xlsxX14SparklineGroups{ - XMLNSXM: NameSpaceSpreadSheetExcel2006Main, - Content: decodeSparklineGroups.Content + string(sparklineGroupBytes), - } - if sparklineGroupsBytes, err = xml.Marshal(groups); err != nil { - return - } - decodeExtLst.Ext[idx].Content = string(sparklineGroupsBytes) - } - } - if extLstBytes, err = xml.Marshal(decodeExtLst); err != nil { - return - } - extLst = string(extLstBytes) - ws.ExtLst = &xlsxExtLst{ - Ext: strings.TrimSuffix(strings.TrimPrefix(extLst, ""), ""), - } } else { groups = &xlsxX14SparklineGroups{ XMLNSXM: NameSpaceSpreadSheetExcel2006Main, @@ -482,11 +446,10 @@ func (f *File) AddSparkline(sheet string, opt *SparklineOption) (err error) { if sparklineGroupsBytes, err = xml.Marshal(groups); err != nil { return } - ext = &xlsxWorksheetExt{ + if extBytes, err = xml.Marshal(&xlsxWorksheetExt{ URI: ExtURISparklineGroups, Content: string(sparklineGroupsBytes), - } - if extBytes, err = xml.Marshal(ext); err != nil { + }); err != nil { return } ws.ExtLst.Ext = string(extBytes) @@ -534,3 +497,47 @@ func (f *File) addSparkline(opt *SparklineOption, group *xlsxX14SparklineGroup) }) } } + +// appendSparkline provides a function to append sparkline to sparkline +// groups. +func (f *File) appendSparkline(ws *xlsxWorksheet, group *xlsxX14SparklineGroup, groups *xlsxX14SparklineGroups) (err error) { + var ( + idx int + decodeExtLst *decodeWorksheetExt + decodeSparklineGroups *decodeX14SparklineGroups + ext *xlsxWorksheetExt + sparklineGroupsBytes, sparklineGroupBytes, extLstBytes []byte + ) + decodeExtLst = new(decodeWorksheetExt) + if err = f.xmlNewDecoder(bytes.NewReader([]byte("" + ws.ExtLst.Ext + ""))). + Decode(decodeExtLst); err != nil && err != io.EOF { + return + } + for idx, ext = range decodeExtLst.Ext { + if ext.URI == ExtURISparklineGroups { + decodeSparklineGroups = new(decodeX14SparklineGroups) + if err = f.xmlNewDecoder(bytes.NewReader([]byte(ext.Content))). + Decode(decodeSparklineGroups); err != nil && err != io.EOF { + return + } + if sparklineGroupBytes, err = xml.Marshal(group); err != nil { + return + } + groups = &xlsxX14SparklineGroups{ + XMLNSXM: NameSpaceSpreadSheetExcel2006Main, + Content: decodeSparklineGroups.Content + string(sparklineGroupBytes), + } + if sparklineGroupsBytes, err = xml.Marshal(groups); err != nil { + return + } + decodeExtLst.Ext[idx].Content = string(sparklineGroupsBytes) + } + } + if extLstBytes, err = xml.Marshal(decodeExtLst); err != nil { + return + } + ws.ExtLst = &xlsxExtLst{ + Ext: strings.TrimSuffix(strings.TrimPrefix(string(extLstBytes), ""), ""), + } + return +} -- cgit v1.2.1 From 09485b3f9f0aefc58d51462aed65c2416205c591 Mon Sep 17 00:00:00 2001 From: xuri Date: Sun, 29 Dec 2019 16:02:31 +0800 Subject: Improve code coverage unit tests --- sparkline.go | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sparkline.go') diff --git a/sparkline.go b/sparkline.go index 47c8d5a..ef99da6 100644 --- a/sparkline.go +++ b/sparkline.go @@ -1,4 +1,4 @@ -// Copyright 2016 - 2019 The excelize Authors. All rights reserved. Use of +// Copyright 2016 - 2020 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. // -- cgit v1.2.1 From 0f2a9053246c3ae45e6c7ba911a1fb135664abdf Mon Sep 17 00:00:00 2001 From: xuri Date: Thu, 2 Apr 2020 00:41:14 +0800 Subject: Performance improvements --- sparkline.go | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sparkline.go') diff --git a/sparkline.go b/sparkline.go index ef99da6..f1e1f40 100644 --- a/sparkline.go +++ b/sparkline.go @@ -516,7 +516,7 @@ func (f *File) appendSparkline(ws *xlsxWorksheet, group *xlsxX14SparklineGroup, for idx, ext = range decodeExtLst.Ext { if ext.URI == ExtURISparklineGroups { decodeSparklineGroups = new(decodeX14SparklineGroups) - if err = f.xmlNewDecoder(bytes.NewReader([]byte(ext.Content))). + if err = f.xmlNewDecoder(bytes.NewReader(stringToBytes(ext.Content))). Decode(decodeSparklineGroups); err != nil && err != io.EOF { return } -- cgit v1.2.1 From 6e90fa6b1d00b2c4ce85e79ee4054ee847fbbc87 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Olivier=20Mengu=C3=A9?= Date: Wed, 8 Apr 2020 18:49:13 +0200 Subject: Replace bytes.NewReader(stringToBytes(s)) with strings.NewReader(s) (#610) --- sparkline.go | 5 ++--- 1 file changed, 2 insertions(+), 3 deletions(-) (limited to 'sparkline.go') diff --git a/sparkline.go b/sparkline.go index f1e1f40..ce5be4c 100644 --- a/sparkline.go +++ b/sparkline.go @@ -10,7 +10,6 @@ package excelize import ( - "bytes" "encoding/xml" "errors" "io" @@ -509,14 +508,14 @@ func (f *File) appendSparkline(ws *xlsxWorksheet, group *xlsxX14SparklineGroup, sparklineGroupsBytes, sparklineGroupBytes, extLstBytes []byte ) decodeExtLst = new(decodeWorksheetExt) - if err = f.xmlNewDecoder(bytes.NewReader([]byte("" + ws.ExtLst.Ext + ""))). + if err = f.xmlNewDecoder(strings.NewReader("" + ws.ExtLst.Ext + "")). Decode(decodeExtLst); err != nil && err != io.EOF { return } for idx, ext = range decodeExtLst.Ext { if ext.URI == ExtURISparklineGroups { decodeSparklineGroups = new(decodeX14SparklineGroups) - if err = f.xmlNewDecoder(bytes.NewReader(stringToBytes(ext.Content))). + if err = f.xmlNewDecoder(strings.NewReader(ext.Content)). Decode(decodeSparklineGroups); err != nil && err != io.EOF { return } -- cgit v1.2.1