Add support for plotting lines on scatter charts

[Zncl2222]

Description

Recently, I tried to plot multiple line series on a single chart with a dual Y-axis in different scale. However, I found that the Secondary argument in ChartAxis doesn't allow assigning specific line series to the secondary Y-axis. After some investigation, I realized that it might take some time to implement this, so I decided to use a "scatter plot with lines" and set the secondary Y-axis on the scatter plot instead. However, it appears that the current version does not support drawing lines on scatter plots.

Therefore, I would like to request adding support for plotting lines on scatter charts to provide more flexibility in usage. I have already implemented the code for this feature. If you think this is a good idea, I am ready to submit a pull request. Thank you!

Below is an example of the code snippet I used to plot lines on a scatter chart in my current implementation..

func main() {
	f := excelize.NewFile()
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()
	for idx, row := range [][]interface{}{
		{nil, "Apple", "Orange", "Pear"},
		{"Small", 2, 3, 3},
		{"Normal", 5, 2, 4},
		{"Large", 6, 7, 8},
	} {
		cell, err := excelize.CoordinatesToCellName(1, idx+1)
		if err != nil {
			fmt.Println(err)
			return
		}
		if err := f.SetSheetRow("Sheet1", cell, &row); err != nil {
			fmt.Println(err)
			return
		}
	}
	if err := f.AddChart("Sheet1", "E1", &excelize.Chart{
		Type: excelize.Scatter,
		Series: []excelize.ChartSeries{
			{
				Name:       "Sheet1!$A$2",
				Categories: "Sheet1!$B$1:$D$1",
				Values:     "Sheet1!$B$2:$D$2",
				Line:       excelize.ChartLine{ShowScatterLine: true},  // Set ShowScatterLine to true
				Marker:     excelize.ChartMarker{Symbol: "none"},
				Fill:       excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"00FF00"}},
			},
			{
				Name:       "Sheet1!$A$3",
				Categories: "Sheet1!$B$1:$D$1",
				Values:     "Sheet1!$B$3:$D$3",
				Line:       excelize.ChartLine{ShowScatterLine: true},
			},
			{
				Name:       "Sheet1!$A$4",
				Categories: "Sheet1!$B$1:$D$1",
				Values:     "Sheet1!$B$4:$D$4",
			},
		},
	}); err != nil {
		fmt.Println(err)
		return
	}
	if err := f.SaveAs("Book1.xlsx"); err != nil {
		fmt.Println(err)
	}
}

[xuri]

Thanks for your issue. If you mean not related to the trendline of chart, you should be using line type of the chart. The problem is set series of line chart as none doesn't work. The approach of fix should be add following code before drawing.go#L766:

if opts.Series[i].Line.Type == ChartLineNone {
    spPrLine.Ln.NoFill, spPrLine.Ln.SolidFill = &attrValString{}, nil
}

So you can set specified line as none of the chart:

func main() {
  f := excelize.NewFile()
  defer func() {
    if err := f.Close(); err != nil {
      fmt.Println(err)
    }
  }()
  for idx, row := range [][]interface{}{
    {nil, "Apple", "Orange", "Pear"},
    {"Small", 2, 3, 3},
    {"Normal", 5, 2, 4},
    {"Large", 6, 7, 8},
  } {
    cell, err := excelize.CoordinatesToCellName(1, idx+1)
    if err != nil {
      fmt.Println(err)
      return
    }
    if err := f.SetSheetRow("Sheet1", cell, &row); err != nil {
      fmt.Println(err)
      return
    }
  }
  if err := f.AddChart("Sheet1", "E1", &excelize.Chart{
-   Type: excelize.Scatter,
+   Type: excelize.Line,
    Series: []excelize.ChartSeries{
      {
        Name:       "Sheet1!$A$2",
        Categories: "Sheet1!$B$1:$D$1",
        Values:     "Sheet1!$B$2:$D$2",
-       Line:       excelize.ChartLine{ShowScatterLine: true},  // Set ShowScatterLine to true
        Marker:     excelize.ChartMarker{Symbol: "none"},
        Fill:       excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"00FF00"}},
      },
      {
        Name:       "Sheet1!$A$3",
        Categories: "Sheet1!$B$1:$D$1",
        Values:     "Sheet1!$B$3:$D$3",
-       Line:       excelize.ChartLine{ShowScatterLine: true},
+       Marker:     excelize.ChartMarker{Symbol: "circle"},
      },
      {
        Name:       "Sheet1!$A$4",
        Categories: "Sheet1!$B$1:$D$1",
        Values:     "Sheet1!$B$4:$D$4",
+       Marker:     excelize.ChartMarker{Symbol: "circle"},
+       Line:       excelize.ChartLine{Type: excelize.ChartLineNone},
      },
    },
  }); err != nil {
    fmt.Println(err)
    return
  }
  if err := f.SaveAs("Book1.xlsx"); err != nil {
    fmt.Println(err)
  }
}

If you'd like to create a PR for this, that's would be great.

[Zncl2222]

Update 2024/09/04:

I have made some changes about adding the line function for scatter chart and pushed the code to my repository. You can see the changes here.

Feature-Commit
Unittest-Commit

If you do not accept this change, I will go ahead to create a PR to fix the issue you mentioned. If you do accept the change, should I create a separate issue to fix the problem you mentioned, or should I include the fix in this issue as well?

Thanks !

---

Thank you for your reply. I am willing to create a PR to fix the problem you mentioned. However, it might be better to review my new explanation first, as I believe these two problems could be different issues.

Sorry I do not make my original problem clear enough. I understand that excelize.Line can be used to plot a line graph. The reason that I want to use Scatter Chart to plot the Line is because I need to plot a chart that include the Column Bar, and two Lines with dual Y Axis (The second Y Axis's scale is much less than the First Y Axis). For example, I use the following code snippet to plot the chart. The current scale of the second Y-Axis is 0 to 350, but I need it to range from 0 to 6.

func main {
	f := excelize.NewFile()
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()
	for idx, row := range [][]interface{}{
		{nil, "Apple", "Orange"},
		{"Small", 200, 300, 300},
		{"Normal", 5, 2, 4},
	} {
		cell, err := excelize.CoordinatesToCellName(1, idx+1)
		if err != nil {
			fmt.Println(err)
		}
		if err := f.SetSheetRow("Sheet1", cell, &row); err != nil {
			fmt.Println(err)
		}
	}
	if err := f.AddChart("Sheet1", "E1",
		&excelize.Chart{
			Type: excelize.Col,
			Series: []excelize.ChartSeries{
				{
					Name:       "Sheet1!$A$2",
					Categories: "Sheet1!$B$1:$D$1",
					Values:     "Sheet1!$B$2:$D$2",
				},
			},
			YAxis: excelize.ChartAxis{Secondary: true}},
		&excelize.Chart{
			Type: excelize.Line,
			Series: []excelize.ChartSeries{
				{
					Name:       "Sheet1!$A$2",
					Categories: "Sheet1!$B$1:$D$1",
					Values:     "Sheet1!$B$2:$D$2",
					Marker:     excelize.ChartMarker{Symbol: "none"},
					Fill:       excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"00FF00"}},
				},
				{
					Name:       "Sheet1!$A$3",
					Categories: "Sheet1!$B$1:$D$1",
					Values:     "Sheet1!$B$3:$D$3",
				},
			},
			YAxis: excelize.ChartAxis{Secondary: true},
		}); err != nil {
		fmt.Println(err)
	}
	if err := f.SaveAs("Book1.xlsx"); err != nil {
		fmt.Println(err)
	}
}

What I actually need is

When I try to plot the same type of Chart excelize.Line in different Combo, it appears that the last combo overwrites the previous one with the same ChartType. As a result, the second line with values 200, 300, 300 is missing in the following picture

Code Snippet (Seperate Line Chart in differnt Combo)

func main() {
	f := excelize.NewFile()
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()
	for idx, row := range [][]interface{}{
		{nil, "Apple", "Orange"},
		{"Small", 200, 300, 300},
		{"Normal", 5, 2, 4},
	} {
		cell, err := excelize.CoordinatesToCellName(1, idx+1)
		if err != nil {
			fmt.Println(err)
		}
		if err := f.SetSheetRow("Sheet1", cell, &row); err != nil {
			fmt.Println(err)
		}
	}
	if err := f.AddChart("Sheet1", "E1",
		&excelize.Chart{
			Type: excelize.Col,
			Series: []excelize.ChartSeries{
				{
					Name:       "Sheet1!$A$2",
					Categories: "Sheet1!$B$1:$D$1",
					Values:     "Sheet1!$B$2:$D$2",
				},
			},
			YAxis: excelize.ChartAxis{Secondary: true}},
		&excelize.Chart{
			Type: excelize.Line,
			Series: []excelize.ChartSeries{
				{
					Name:       "Sheet1!$A$2",
					Categories: "Sheet1!$B$1:$D$1",
					Values:     "Sheet1!$B$2:$D$2",
					Marker:     excelize.ChartMarker{Symbol: "none"},
					Fill:       excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"00FF00"}},
				},
			},
		},
		&excelize.Chart{
			Type: excelize.Line,
			Series: []excelize.ChartSeries{
				{
					Name:       "Sheet1!$A$3",
					Categories: "Sheet1!$B$1:$D$1",
					Values:     "Sheet1!$B$3:$D$3",
				},
			},
			YAxis: excelize.ChartAxis{Secondary: true},
		}); err != nil {
		fmt.Println(err)
	}
	if err := f.SaveAs("Book1.xlsx"); err != nil {
		fmt.Println(err)
	}
}

As I mentioned, I think implementing a method to assign specific data as Secondary YAxis will be much more complicated than just plotting the Scatter Chart with a line and the Line Chart to achieve my purpose. However, when I tried to do that, I found that the Scatter Chart couldn't plot the line, so I am requesting this issue to make the Scatter Chart have more flexible usage.

My current method to reach my requirement is to plot the chart with three combos with the order like

1. Column Bar Chart
2. Scatter Chart
3. Line Chart

Set Line Chart as Secondary Y Axis and make my Scatter Chart looks like a Line Chart.

I add a new member in ChartLine Structure
xmlChart.go#L607

type ChartLine struct {
	Type            ChartLineType
	Smooth          bool
	Width           float64
+	ShowScatterLine bool
}

Modify the code at drawing.go#L753-L758 to

func (f *File) drawChartSeriesSpPr(i int, opts *Chart) *cSpPr {
	spPr := &cSpPr{SolidFill: &aSolidFill{SchemeClr: &aSchemeClr{Val: "accent" + strconv.Itoa((opts.order+i)%6+1)}}}
	spPr = f.drawShapeFill(opts.Series[i].Fill, spPr)
	spPrScatter := &cSpPr{
		Ln: &aLn{
			W:      25400,
-			NoFill: &attrValString{},
		},
	}
+	if opts.Series[i].Line.ShowScatterLine {
+		spPrScatter.Ln.SolidFill = spPr.SolidFill
+		spPrScatter.Ln.W = f.ptToEMUs(opts.Series[i].Line.Width)
+		spPrScatter.Ln.Cap = "rnd"
+	} else {
+		spPrScatter.Ln.NoFill = &attrValString{}
+	}

	spPrLine := &cSpPr{
		Ln: &aLn{
			W:         f.ptToEMUs(opts.Series[i].Line.Width),
			Cap:       "rnd", // rnd, sq, flat
			SolidFill: spPr.SolidFill,
		},
	}
	if opts.Series[i].Line.Type == ChartLineNone {
		spPrLine.Ln.NoFill, spPrLine.Ln.SolidFill = &attrValString{}, nil
	}
	if chartSeriesSpPr, ok := map[ChartType]*cSpPr{
		Line: spPrLine, Scatter: spPrScatter,
	}[opts.Type]; ok {
		return chartSeriesSpPr
	}
	if spPr.SolidFill.SrgbClr != nil {
		return spPr
	}
	return nil
}

Then use the following code snippet to plot the chart, and the result meets my requirements.

func main() {
	f := excelize.NewFile()
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()
	for idx, row := range [][]interface{}{
		{nil, "Apple", "Orange", "Peer"},
		{"Small", 200, 300, 300},
		{"Normal", 5, 2, 4},
	} {
		cell, err := excelize.CoordinatesToCellName(1, idx+1)
		if err != nil {
			fmt.Println(err)
		}
		if err := f.SetSheetRow("Sheet1", cell, &row); err != nil {
			fmt.Println(err)
		}
	}
	if err := f.AddChart("Sheet1", "E1",
		&excelize.Chart{
			Type: excelize.Col,
			Series: []excelize.ChartSeries{
				{
					Name:       "Sheet1!$A$2",
					Categories: "Sheet1!$B$1:$D$1",
					Values:     "Sheet1!$B$2:$D$2",
				},
			},
		},
		&excelize.Chart{
			Type: excelize.Scatter,
			Series: []excelize.ChartSeries{
				{
					Name:       "Sheet1!$A$2",
					Categories: "Sheet1!$B$1:$D$1",
					Values:     "Sheet1!$B$2:$D$2",
					Marker:     excelize.ChartMarker{Symbol: "none"},
					Fill:       excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"00FF00"}},
					Line:       excelize.ChartLine{ShowScatterLine: true},
				},
			},
		},
		&excelize.Chart{
			Type: excelize.Line,
			Series: []excelize.ChartSeries{
				{
					Name:       "Sheet1!$A$3",
					Categories: "Sheet1!$B$1:$D$1",
					Values:     "Sheet1!$B$3:$D$3",
					Marker:     excelize.ChartMarker{Symbol: "circle", Size: 5, Fill: excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"00FF00"}}},
				},
			},
			YAxis: excelize.ChartAxis{Secondary: true}},
	); err != nil {
		fmt.Println(err)
	}
	if err := f.SaveAs("Book1.xlsx"); err != nil {
		fmt.Println(err)
	}
}

By the way, I found a problem during the testing. I'm not sure if it is a bug. If it is indeed a bug, I can open a new issue to address it.

If I plot the Chart with the combos order in the current excelize V2.8.1 version like

1. Column Bar
2. Line
3. Scatter

And set Scatter as Second Y-Axis, the X-Axis of the scatter is not align to the Column Bar and Line.

Code snippet to reproduce

func main() {
	f := excelize.NewFile()
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()
	for idx, row := range [][]interface{}{
		{nil, "Apple", "Orange", "Peer"},
		{"Small", 200, 300, 300},
		{"Normal", 5, 2, 4},
	} {
		cell, err := excelize.CoordinatesToCellName(1, idx+1)
		if err != nil {
			fmt.Println(err)
		}
		if err := f.SetSheetRow("Sheet1", cell, &row); err != nil {
			fmt.Println(err)
		}
	}
	if err := f.AddChart("Sheet1", "E1",
		&excelize.Chart{
			Type: excelize.Col,
			Series: []excelize.ChartSeries{
				{
					Name:       "Sheet1!$A$2",
					Categories: "Sheet1!$B$1:$D$1",
					Values:     "Sheet1!$B$2:$D$2",
				},
			},
		},
		&excelize.Chart{
			Type: excelize.Line,
			Series: []excelize.ChartSeries{
				{
					Name:       "Sheet1!$A$2",
					Categories: "Sheet1!$B$1:$D$1",
					Values:     "Sheet1!$B$2:$D$2",
					Marker:     excelize.ChartMarker{Symbol: "none"},
					Fill:       excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"00FF00"}},
				},
			},
		},
		&excelize.Chart{
			Type: excelize.Scatter,
			Series: []excelize.ChartSeries{
				{
					Name:       "Sheet1!$A$3",
					Categories: "Sheet1!$B$1:$D$1",
					Values:     "Sheet1!$B$3:$D$3",
					Marker:     excelize.ChartMarker{Symbol: "circle", Size: 5, Fill: excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"00FF00"}}},
				},
			},
			YAxis: excelize.ChartAxis{Secondary: true}},
	); err != nil {
		fmt.Println(err)
	}
	if err := f.SaveAs("Book1.xlsx"); err != nil {
		fmt.Println(err)
	}
}

[xuri]

Thanks for your feedback. This library already supports set type of chart line as none by excelize.ChartLineNone enumeration, the ShowScatterLine field value conflicts with it, so I do not suggest adding it. I think the root cause of this problem is the library does not create combo charts with the same type. I fixed it, please upgrade to the master branch code by go get -u github.com/xuri/excelize/v2@master. This feature will be released in the next version. Now you can create chart like this by following code:

What I actually need is

package main

import (
    "fmt"

    "github.com/xuri/excelize/v2"
)

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    for idx, row := range [][]interface{}{
        {nil, "Apple", "Orange"},
        {"Small", 200, 300, 300},
        {"Normal", 5, 2, 4},
    } {
        cell, err := excelize.CoordinatesToCellName(1, idx+1)
        if err != nil {
            fmt.Println(err)
        }
        if err := f.SetSheetRow("Sheet1", cell, &row); err != nil {
            fmt.Println(err)
        }
    }
    if err := f.AddChart("Sheet1", "E1",
        &excelize.Chart{
            Type: excelize.Col,
            Series: []excelize.ChartSeries{
                {
                    Name:       "Sheet1!$A$2",
                    Categories: "Sheet1!$B$1:$D$1",
                    Values:     "Sheet1!$B$2:$D$2",
                },
            },
        },
        &excelize.Chart{
            Type: excelize.Line,
            Series: []excelize.ChartSeries{
                {
                    Name:       "Sheet1!$A$2",
                    Categories: "Sheet1!$B$1:$D$1",
                    Values:     "Sheet1!$B$2:$D$2",
                    Marker:     excelize.ChartMarker{Symbol: "none"},
                    Fill:       excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"00FF00"}},
                },
            },
        },
        &excelize.Chart{
            Type: excelize.Line,
            Series: []excelize.ChartSeries{
                {
                    Name:       "Sheet1!$A$3",
                    Categories: "Sheet1!$B$1:$D$1",
                    Values:     "Sheet1!$B$3:$D$3",
                    Marker: excelize.ChartMarker{
                        Fill: excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"EA7E35"}},
                    },
                    Line: excelize.ChartLine{Type: excelize.ChartLineNone},
                },
            },
            YAxis: excelize.ChartAxis{Secondary: true},
        },
    ); err != nil {
        fmt.Println(err)
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

[Zncl2222]

Thanks for the help