excelize
excelize copied to clipboard
qax-os
When rename the sheet it removes the chart data from sheet.
Description I am trying to create a chart on excel sheet using AddChart() function. I executed the code successfully and It returned me the file as I am expecting it to be. But when before saving the file I want to rename the sheet name using xlsx.SetSheetName("Sheet1","New Name") It creates a file with empty chart.
Steps to reproduce the issue:
- Write following code and check the output it will return the correct chart with Graph.
package main
import (
"fmt"
"github.com/360EntSecGroup-Skylar/excelize"
)
func main() {
categories := map[string]string{"A1": "datum", "A2": "19-Nov-2018", "A3": "20-Nov-2018", "A4": "21-Nov-2018", "A5": "22-Nov-2018", "A6": "23-Nov-2018", "A7":"24-Nov-2018", "D1":"score totaal/h"}
values := map[string]float64{"D2": 3.0047, "D3": 3.3697, "D4": 2.7776, "D5": 1.5615, "D6": 2.5649, "D7": 4.3659}
xlsx := excelize.NewFile()
for k, v := range categories {
xlsx.SetCellValue("Sheet1", k, v)
}
for k, v := range values {
xlsx.SetCellValue("Sheet1", k, v)
}
xlsx.AddChart("Sheet1", "B13", `{"type":"line","dimension":{"width":480,"height":600},"series":[{"name":"Sheet1!$D$1","categories":"Sheet1!$A$2:$A$7","values":"Sheet1!$D$2:$D$7"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"bottom","show_legend_key":false},"title":{"name":"Fruit 3D Clustered Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":true,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero","x_axis":{"reverse_order":false},"y_axis":{"maximum":50,"minimum":0}}`)
// Save xlsx file by the given path.
err := xlsx.SaveAs("./Book1.xlsx")
if err != nil {
fmt.Println(err)
}
}
- Add the following line before saving the file
xlsx.SetSheetName("Sheet1","customsheetname")and it will save the file with empty chart.
Output of go version:
go version go1.9.4 linux/amd64
Excelize version or commit ID: 9a6f66a996eb83f16da13416c5fca361afe575b0
Environment details (OS, Microsoft Excel™ version, physical, etc.): Distributor ID: Ubuntu Description: Ubuntu 16.04.5 LTS Release: 16.04 Codename: xenial
@bharatsewani1993 @xuri I investigated this issue and found mistake in code for add chart. When you adding chart you set values with sheet name:
xlsx.AddChart("Sheet1", "B13",
`{"type":"line","dimension":{...},
"series":[{
"name":"Sheet1!$D$1", <---- here
"categories":"Sheet1!$A$2:$A$7", <---- here
"values":"Sheet1!$D$2:$D$7"}], <---- and here
"format":...`)
If you remove Sheet1 from there chart fills properly:
xlsx.AddChart("Sheet1", "B13",
`{"type":"line","dimension":{...},
"series":[{
"name":"$D$1", <---- here
"categories":"$A$2:$A$7", <---- here
"values":"$D$2:$D$7"}], <---- and here
"format":...`)
Yes, in order to solve this problem we need to check and update all references (such as xl/charts/chart*.xml) that to the workbook when we rename the workbook.
@xuri If you drop "Sheet1!" from source definition everything will still work even after renaming the sheet.
hi, chart data is not appearing also if you place a different string than 'Sheet1' in SetCellStyle or SetCellValue calls.
Hi, I encountered the same issue that the chart does not show any data.
I cannot confirm that removing the Sheet1! prefix in the settings will help. (For testing un-comment the code line // format := ...)
I can also not confirm that it will only work on Sheet1.
From my point of view it looks like there is an issue with sheet names that contains blanks. I took the code from the examples and modified it to illustrate the behavior.
import (
"fmt"
"strings"
"github.com/360EntSecGroup-Skylar/excelize"
)
func addChart(f *excelize.File, sheetKey string) {
categories := map[string]string{"A2": "Small", "A3": "Normal", "A4": "Large", "B1": "Apple", "C1": "Orange", "D1": "Pear"}
values := map[string]int{"B2": 2, "C2": 3, "D2": 3, "B3": 5, "C3": 2, "D3": 4, "B4": 6, "C4": 7, "D4": 8}
for k, v := range categories {
f.SetCellValue(sheetKey, k, v)
}
for k, v := range values {
f.SetCellValue(sheetKey, k, v)
}
format := strings.Replace(`{"type":"col3DClustered","series":[{"name":"Sheet1!$A$2","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$2:$D$2"},{"name":"Sheet1!$A$3","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$3:$D$3"},{"name":"Sheet1!$A$4","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$4:$D$4"}],"title":{"name":"Fruit 3D Clustered Column Chart"}}`, "Sheet1", sheetKey, -1)
// if a remove the sheet prefix 'sheet!' not data at all will be displayed in the chart
//format := strings.Replace(`{"type":"col3DClustered","series":[{"name":"Sheet1!$A$2","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$2:$D$2"},{"name":"Sheet1!$A$3","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$3:$D$3"},{"name":"Sheet1!$A$4","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$4:$D$4"}],"title":{"name":"Fruit 3D Clustered Column Chart"}}`, "Sheet1!", "", -1)
err := f.AddChart(sheetKey, "E1", format)
if err != nil {
fmt.Println(err)
return
}
}
func main() {
sheets := []string{"AnotherSheet", "A Sheet with blanks"}
f := excelize.NewFile()
for _, sheet := range sheets {
f.NewSheet(sheet)
addChart(f, sheet)
}
addChart(f, "Sheet1")
// Save xlsx file by the given path.
err := f.SaveAs("./Book1.xlsx")
if err != nil {
fmt.Println(err)
}
}
The code makes a new xls-file and adds three sheets with the sample chart and the last sheet does contain any data in the sheet. Sheet names with blanks works fine for SetCellValue but not for f.AddChart. Hope this provides more detail.
