diff options
| -rw-r--r-- | .travis.yml | 2 | ||||
| -rw-r--r-- | chart.go | 160 | ||||
| -rw-r--r-- | excelize_test.go | 1 | ||||
| -rw-r--r-- | lib.go | 11 | ||||
| -rw-r--r-- | sheetpr.go | 139 | ||||
| -rw-r--r-- | sheetpr_test.go | 153 | ||||
| -rw-r--r-- | sheetview.go | 139 | ||||
| -rw-r--r-- | sheetview_test.go | 128 | ||||
| -rw-r--r-- | styles.go | 18 | ||||
| -rw-r--r-- | xmlChart.go | 1 | ||||
| -rw-r--r-- | xmlWorkbook.go | 71 | ||||
| -rw-r--r-- | xmlWorksheet.go | 9 |
12 files changed, 742 insertions, 90 deletions
diff --git a/.travis.yml b/.travis.yml index 8285809..f4e5c0c 100644 --- a/.travis.yml +++ b/.travis.yml @@ -9,7 +9,7 @@ go: script: - go vet ./... - - go test -v -coverprofile=coverage.txt -covermode=atomic + - go test ./... -v -coverprofile=coverage.txt -covermode=atomic after_success: - bash <(curl -s https://codecov.io/bash) @@ -9,57 +9,62 @@ import ( // This section defines the currently supported chart types. const ( - Bar = "bar" - Bar3D = "bar3D" - Doughnut = "doughnut" - Line = "line" - Pie = "pie" - Pie3D = "pie3D" - Radar = "radar" - Scatter = "scatter" + Bar = "bar" + BarStacked = "barStacked" + Bar3D = "bar3D" + Doughnut = "doughnut" + Line = "line" + Pie = "pie" + Pie3D = "pie3D" + Radar = "radar" + Scatter = "scatter" ) // This section defines the default value of chart properties. var ( chartView3DRotX = map[string]int{ - Bar: 0, - Bar3D: 15, - Doughnut: 0, - Line: 0, - Pie: 0, - Pie3D: 30, - Radar: 0, - Scatter: 0, + Bar: 0, + BarStacked: 0, + Bar3D: 15, + Doughnut: 0, + Line: 0, + Pie: 0, + Pie3D: 30, + Radar: 0, + Scatter: 0, } chartView3DRotY = map[string]int{ - Bar: 0, - Bar3D: 20, - Doughnut: 0, - Line: 0, - Pie: 0, - Pie3D: 0, - Radar: 0, - Scatter: 0, + Bar: 0, + BarStacked: 0, + Bar3D: 20, + Doughnut: 0, + Line: 0, + Pie: 0, + Pie3D: 0, + Radar: 0, + Scatter: 0, } chartView3DDepthPercent = map[string]int{ - Bar: 100, - Bar3D: 100, - Doughnut: 100, - Line: 100, - Pie: 100, - Pie3D: 100, - Radar: 100, - Scatter: 100, + Bar: 100, + BarStacked: 100, + Bar3D: 100, + Doughnut: 100, + Line: 100, + Pie: 100, + Pie3D: 100, + Radar: 100, + Scatter: 100, } chartView3DRAngAx = map[string]int{ - Bar: 0, - Bar3D: 1, - Doughnut: 0, - Line: 0, - Pie: 0, - Pie3D: 0, - Radar: 0, - Scatter: 0} + Bar: 0, + BarStacked: 0, + Bar3D: 1, + Doughnut: 0, + Line: 0, + Pie: 0, + Pie3D: 0, + Radar: 0, + Scatter: 0} chartLegendPosition = map[string]string{ "bottom": "b", "left": "l", @@ -128,16 +133,17 @@ func parseFormatChartSet(formatSet string) *formatChart { // // The following shows the type of chart supported by excelize: // -// Type | Chart -// ----------+---------------- -// bar | bar chart -// bar3D | 3D bar chart -// doughnut | doughnut chart -// line | line chart -// pie | pie chart -// pie3D | 3D pie chart -// radar | radar chart -// scatter | scatter chart +// Type | Chart +// ------------+---------------- +// bar | bar chart +// barStacked | stacked bar chart +// bar3D | 3D bar chart +// doughnut | doughnut chart +// line | line chart +// pie | pie chart +// pie3D | 3D pie chart +// radar | radar chart +// scatter | scatter chart // // In Excel a chart series is a collection of information that defines which data is plotted such as values, axis labels and formatting. // @@ -383,14 +389,15 @@ func (f *File) addChart(formatSet *formatChart) { }, } plotAreaFunc := map[string]func(*formatChart) *cPlotArea{ - Bar: f.drawBarChart, - Bar3D: f.drawBarChart, - Doughnut: f.drawDoughnutChart, - Line: f.drawLineChart, - Pie3D: f.drawPie3DChart, - Pie: f.drawPieChart, - Radar: f.drawRadarChart, - Scatter: f.drawScatterChart, + Bar: f.drawBarChart, + BarStacked: f.drawBarChart, + Bar3D: f.drawBarChart, + Doughnut: f.drawDoughnutChart, + Line: f.drawLineChart, + Pie3D: f.drawPie3DChart, + Pie: f.drawPieChart, + Radar: f.drawRadarChart, + Scatter: f.drawScatterChart, } xlsxChartSpace.Chart.PlotArea = plotAreaFunc[formatSet.Type](formatSet) @@ -399,7 +406,7 @@ func (f *File) addChart(formatSet *formatChart) { f.saveFileList(media, string(chart)) } -// drawBarChart provides function to draw the c:plotArea element for bar and +// drawBarChart provides function to draw the c:plotArea element for bar, barStacked and // bar3D chart by given format sets. func (f *File) drawBarChart(formatSet *formatChart) *cPlotArea { c := cCharts{ @@ -419,16 +426,27 @@ func (f *File) drawBarChart(formatSet *formatChart) *cPlotArea { {Val: 753999904}, }, } + if formatSet.Type == "barStacked" { + c.Grouping.Val = "stacked" + c.Overlap = &attrValInt{Val: 100} + } + catAx := f.drawPlotAreaCatAx() + valAx := f.drawPlotAreaValAx() charts := map[string]*cPlotArea{ "bar": { BarChart: &c, - CatAx: f.drawPlotAreaCatAx(), - ValAx: f.drawPlotAreaValAx(), + CatAx: catAx, + ValAx: valAx, + }, + "barStacked": { + BarChart: &c, + CatAx: catAx, + ValAx: valAx, }, "bar3D": { Bar3DChart: &c, - CatAx: f.drawPlotAreaCatAx(), - ValAx: f.drawPlotAreaValAx(), + CatAx: catAx, + ValAx: valAx, }, } return charts[formatSet.Type] @@ -590,7 +608,7 @@ func (f *File) drawChartSeriesSpPr(i int, formatSet *formatChart) *cSpPr { }, }, } - chartSeriesSpPr := map[string]*cSpPr{Bar: nil, Bar3D: nil, Doughnut: nil, Line: spPrLine, Pie: nil, Pie3D: nil, Radar: nil, Scatter: spPrScatter} + chartSeriesSpPr := map[string]*cSpPr{Bar: nil, BarStacked: nil, Bar3D: nil, Doughnut: nil, Line: spPrLine, Pie: nil, Pie3D: nil, Radar: nil, Scatter: spPrScatter} return chartSeriesSpPr[formatSet.Type] } @@ -619,7 +637,7 @@ func (f *File) drawChartSeriesDPt(i int, formatSet *formatChart) []*cDPt { }, }, }} - chartSeriesDPt := map[string][]*cDPt{Bar: nil, Bar3D: nil, Doughnut: nil, Line: nil, Pie: dpt, Pie3D: dpt, Radar: nil, Scatter: nil} + chartSeriesDPt := map[string][]*cDPt{Bar: nil, BarStacked: nil, Bar3D: nil, Doughnut: nil, Line: nil, Pie: dpt, Pie3D: dpt, Radar: nil, Scatter: nil} return chartSeriesDPt[formatSet.Type] } @@ -631,7 +649,7 @@ func (f *File) drawChartSeriesCat(v formatChartSeries, formatSet *formatChart) * F: v.Categories, }, } - chartSeriesCat := map[string]*cCat{Bar: cat, Bar3D: cat, Doughnut: cat, Line: cat, Pie: cat, Pie3D: cat, Radar: cat, Scatter: nil} + chartSeriesCat := map[string]*cCat{Bar: cat, BarStacked: cat, Bar3D: cat, Doughnut: cat, Line: cat, Pie: cat, Pie3D: cat, Radar: cat, Scatter: nil} return chartSeriesCat[formatSet.Type] } @@ -643,7 +661,7 @@ func (f *File) drawChartSeriesVal(v formatChartSeries, formatSet *formatChart) * F: v.Values, }, } - chartSeriesVal := map[string]*cVal{Bar: val, Bar3D: val, Doughnut: val, Line: val, Pie: val, Pie3D: val, Radar: val, Scatter: nil} + chartSeriesVal := map[string]*cVal{Bar: val, BarStacked: val, Bar3D: val, Doughnut: val, Line: val, Pie: val, Pie3D: val, Radar: val, Scatter: nil} return chartSeriesVal[formatSet.Type] } @@ -669,7 +687,7 @@ func (f *File) drawChartSeriesMarker(i int, formatSet *formatChart) *cMarker { }, }, } - chartSeriesMarker := map[string]*cMarker{Bar: nil, Bar3D: nil, Doughnut: nil, Line: nil, Pie: nil, Pie3D: nil, Radar: nil, Scatter: marker} + chartSeriesMarker := map[string]*cMarker{Bar: nil, BarStacked: nil, Bar3D: nil, Doughnut: nil, Line: nil, Pie: nil, Pie3D: nil, Radar: nil, Scatter: marker} return chartSeriesMarker[formatSet.Type] } @@ -681,7 +699,7 @@ func (f *File) drawChartSeriesXVal(v formatChartSeries, formatSet *formatChart) F: v.Categories, }, } - chartSeriesXVal := map[string]*cCat{Bar: nil, Bar3D: nil, Doughnut: nil, Line: nil, Pie: nil, Pie3D: nil, Radar: nil, Scatter: cat} + chartSeriesXVal := map[string]*cCat{Bar: nil, BarStacked: nil, Bar3D: nil, Doughnut: nil, Line: nil, Pie: nil, Pie3D: nil, Radar: nil, Scatter: cat} return chartSeriesXVal[formatSet.Type] } @@ -693,7 +711,7 @@ func (f *File) drawChartSeriesYVal(v formatChartSeries, formatSet *formatChart) F: v.Values, }, } - chartSeriesYVal := map[string]*cVal{Bar: nil, Bar3D: nil, Doughnut: nil, Line: nil, Pie: nil, Pie3D: nil, Radar: nil, Scatter: val} + chartSeriesYVal := map[string]*cVal{Bar: nil, BarStacked: nil, Bar3D: nil, Doughnut: nil, Line: nil, Pie: nil, Pie3D: nil, Radar: nil, Scatter: val} return chartSeriesYVal[formatSet.Type] } @@ -715,7 +733,7 @@ func (f *File) drawChartDLbls(formatSet *formatChart) *cDLbls { // format sets. func (f *File) drawChartSeriesDLbls(formatSet *formatChart) *cDLbls { dLbls := f.drawChartDLbls(formatSet) - chartSeriesDLbls := map[string]*cDLbls{Bar: dLbls, Bar3D: dLbls, Doughnut: dLbls, Line: dLbls, Pie: dLbls, Pie3D: dLbls, Radar: dLbls, Scatter: nil} + chartSeriesDLbls := map[string]*cDLbls{Bar: dLbls, BarStacked: dLbls, Bar3D: dLbls, Doughnut: dLbls, Line: dLbls, Pie: dLbls, Pie3D: dLbls, Radar: dLbls, Scatter: nil} return chartSeriesDLbls[formatSet.Type] } diff --git a/excelize_test.go b/excelize_test.go index cdd7ac6..3e5665f 100644 --- a/excelize_test.go +++ b/excelize_test.go @@ -817,6 +817,7 @@ func TestAddChart(t *testing.T) { xlsx.AddChart("Sheet1", "X30", `{"type":"pie","series":[{"name":"=Sheet1!$A$30","categories":"=Sheet1!$B$29:$D$29","values":"=Sheet1!$B$30:$D$30"}],"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 Pie Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":false,"show_val":false},"show_blanks_as":"gap"}`) xlsx.AddChart("Sheet2", "P1", `{"type":"radar","series":[{"name":"=Sheet1!$A$30","categories":"=Sheet1!$B$29:$D$29","values":"=Sheet1!$B$30:$D$30"},{"name":"=Sheet1!$A$31","categories":"=Sheet1!$B$29:$D$29","values":"=Sheet1!$B$31:$D$31"},{"name":"=Sheet1!$A$32","categories":"=Sheet1!$B$29:$D$29","values":"=Sheet1!$B$32:$D$32"}],"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":"top_right","show_legend_key":false},"title":{"name":"Fruit Radar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"span"}`) xlsx.AddChart("Sheet2", "X1", `{"type":"scatter","series":[{"name":"=Sheet1!$A$30","categories":"=Sheet1!$B$29:$D$29","values":"=Sheet1!$B$30:$D$30"},{"name":"=Sheet1!$A$31","categories":"=Sheet1!$B$29:$D$29","values":"=Sheet1!$B$31:$D$31"},{"name":"=Sheet1!$A$32","categories":"=Sheet1!$B$29:$D$29","values":"=Sheet1!$B$32:$D$32"}],"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 Scatter Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`) + xlsx.AddChart("Sheet2", "P16", `{"type":"barStacked","series":[{"name":"=Sheet1!$A$30","categories":"=Sheet1!$B$29:$D$29","values":"=Sheet1!$B$30:$D$30"},{"name":"=Sheet1!$A$31","categories":"=Sheet1!$B$29:$D$29","values":"=Sheet1!$B$31:$D$31"},{"name":"=Sheet1!$A$32","categories":"=Sheet1!$B$29:$D$29","values":"=Sheet1!$B$32:$D$32"}],"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":"left","show_legend_key":false},"title":{"name":"Fruit Bar Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero"}`) // Save xlsx file by the given path. err = xlsx.SaveAs("./test/Workbook_addchart.xlsx") if err != nil { @@ -121,3 +121,14 @@ func deepCopy(dst, src interface{}) error { } return gob.NewDecoder(bytes.NewBuffer(buf.Bytes())).Decode(dst) } + +// boolPtr returns a pointer to a bool with the given value. +func boolPtr(b bool) *bool { return &b } + +// defaultTrue returns true if b is nil, or the pointed value. +func defaultTrue(b *bool) bool { + if b == nil { + return true + } + return *b +} diff --git a/sheetpr.go b/sheetpr.go new file mode 100644 index 0000000..7b8df54 --- /dev/null +++ b/sheetpr.go @@ -0,0 +1,139 @@ +package excelize + +// SheetPrOption is an option of a view of a worksheet. See SetSheetPrOptions(). +type SheetPrOption interface { + setSheetPrOption(view *xlsxSheetPr) +} + +// SheetPrOptionPtr is a writable SheetPrOption. See GetSheetPrOptions(). +type SheetPrOptionPtr interface { + SheetPrOption + getSheetPrOption(view *xlsxSheetPr) +} + +type ( + // CodeName is a SheetPrOption + CodeName string + // EnableFormatConditionsCalculation is a SheetPrOption + EnableFormatConditionsCalculation bool + // Published is a SheetPrOption + Published bool + // FitToPage is a SheetPrOption + FitToPage bool + // AutoPageBreaks is a SheetPrOption + AutoPageBreaks bool +) + +func (o CodeName) setSheetPrOption(pr *xlsxSheetPr) { + pr.CodeName = string(o) +} + +func (o *CodeName) getSheetPrOption(pr *xlsxSheetPr) { + if pr == nil { + *o = "" + return + } + *o = CodeName(pr.CodeName) +} + +func (o EnableFormatConditionsCalculation) setSheetPrOption(pr *xlsxSheetPr) { + pr.EnableFormatConditionsCalculation = boolPtr(bool(o)) +} + +func (o *EnableFormatConditionsCalculation) getSheetPrOption(pr *xlsxSheetPr) { + if pr == nil { + *o = true + return + } + *o = EnableFormatConditionsCalculation(defaultTrue(pr.EnableFormatConditionsCalculation)) +} + +func (o Published) setSheetPrOption(pr *xlsxSheetPr) { + pr.Published = boolPtr(bool(o)) +} + +func (o *Published) getSheetPrOption(pr *xlsxSheetPr) { + if pr == nil { + *o = true + return + } + *o = Published(defaultTrue(pr.Published)) +} + +func (o FitToPage) setSheetPrOption(pr *xlsxSheetPr) { + if pr.PageSetUpPr == nil { + if !o { + return + } + pr.PageSetUpPr = new(xlsxPageSetUpPr) + } + pr.PageSetUpPr.FitToPage = bool(o) +} + +func (o *FitToPage) getSheetPrOption(pr *xlsxSheetPr) { + // Excel default: false + if pr == nil || pr.PageSetUpPr == nil { + *o = false + return + } + *o = FitToPage(pr.PageSetUpPr.FitToPage) +} + +func (o AutoPageBreaks) setSheetPrOption(pr *xlsxSheetPr) { + if pr.PageSetUpPr == nil { + if !o { + return + } + pr.PageSetUpPr = new(xlsxPageSetUpPr) + } + pr.PageSetUpPr.AutoPageBreaks = bool(o) +} + +func (o *AutoPageBreaks) getSheetPrOption(pr *xlsxSheetPr) { + // Excel default: false + if pr == nil || pr.PageSetUpPr == nil { + *o = false + return + } + *o = AutoPageBreaks(pr.PageSetUpPr.AutoPageBreaks) +} + +// SetSheetPrOptions provides function to sets worksheet properties. +// +// Available options: +// CodeName(string) +// EnableFormatConditionsCalculation(bool) +// Published(bool) +// FitToPage(bool) +// AutoPageBreaks(bool) +func (f *File) SetSheetPrOptions(name string, opts ...SheetPrOption) error { + sheet := f.workSheetReader(name) + pr := sheet.SheetPr + if pr == nil { + pr = new(xlsxSheetPr) + sheet.SheetPr = pr + } + + for _, opt := range opts { + opt.setSheetPrOption(pr) + } + return nil +} + +// GetSheetPrOptions provides function to gets worksheet properties. +// +// Available options: +// CodeName(string) +// EnableFormatConditionsCalculation(bool) +// Published(bool) +// FitToPage(bool) +// AutoPageBreaks(bool) +func (f *File) GetSheetPrOptions(name string, opts ...SheetPrOptionPtr) error { + sheet := f.workSheetReader(name) + pr := sheet.SheetPr + + for _, opt := range opts { + opt.getSheetPrOption(pr) + } + return nil +} diff --git a/sheetpr_test.go b/sheetpr_test.go new file mode 100644 index 0000000..8c11a1a --- /dev/null +++ b/sheetpr_test.go @@ -0,0 +1,153 @@ +package excelize_test + +import ( + "fmt" + "reflect" + "testing" + + "github.com/mohae/deepcopy" + "github.com/xuri/excelize" +) + +var _ = []excelize.SheetPrOption{ + excelize.CodeName("hello"), + excelize.EnableFormatConditionsCalculation(false), + excelize.Published(false), + excelize.FitToPage(true), + excelize.AutoPageBreaks(true), +} + +var _ = []excelize.SheetPrOptionPtr{ + (*excelize.CodeName)(nil), + (*excelize.EnableFormatConditionsCalculation)(nil), + (*excelize.Published)(nil), + (*excelize.FitToPage)(nil), + (*excelize.AutoPageBreaks)(nil), +} + +func ExampleFile_SetSheetPrOptions() { + xl := excelize.NewFile() + const sheet = "Sheet1" + + if err := xl.SetSheetPrOptions(sheet, + excelize.CodeName("code"), + excelize.EnableFormatConditionsCalculation(false), + excelize.Published(false), + excelize.FitToPage(true), + excelize.AutoPageBreaks(true), + ); err != nil { + panic(err) + } + // Output: +} + +func ExampleFile_GetSheetPrOptions() { + xl := excelize.NewFile() + const sheet = "Sheet1" + + var ( + codeName excelize.CodeName + enableFormatConditionsCalculation excelize.EnableFormatConditionsCalculation + published excelize.Published + fitToPage excelize.FitToPage + autoPageBreaks excelize.AutoPageBreaks + ) + + if err := xl.GetSheetPrOptions(sheet, + &codeName, + &enableFormatConditionsCalculation, + &published, + &fitToPage, + &autoPageBreaks, + ); err != nil { + panic(err) + } + fmt.Println("Defaults:") + fmt.Printf("- codeName: %q\n", codeName) + fmt.Println("- enableFormatConditionsCalculation:", enableFormatConditionsCalculation) + fmt.Println("- published:", published) + fmt.Println("- fitToPage:", fitToPage) + fmt.Println("- autoPageBreaks:", autoPageBreaks) + // Output: + // Defaults: + // - codeName: "" + // - enableFormatConditionsCalculation: true + // - published: true + // - fitToPage: false + // - autoPageBreaks: false +} + +func TestSheetPrOptions(t *testing.T) { + const sheet = "Sheet1" + for _, test := range []struct { + container excelize.SheetPrOptionPtr + nonDefault excelize.SheetPrOption + }{ + {new(excelize.CodeName), excelize.CodeName("xx")}, + {new(excelize.EnableFormatConditionsCalculation), excelize.EnableFormatConditionsCalculation(false)}, + {new(excelize.Published), excelize.Published(false)}, + {new(excelize.FitToPage), excelize.FitToPage(true)}, + {new(excelize.AutoPageBreaks), excelize.AutoPageBreaks(true)}, + } { + opt := test.nonDefault + t.Logf("option %T", opt) + + def := deepcopy.Copy(test.container).(excelize.SheetPrOptionPtr) + val1 := deepcopy.Copy(def).(excelize.SheetPrOptionPtr) + val2 := deepcopy.Copy(def).(excelize.SheetPrOptionPtr) + + xl := excelize.NewFile() + // Get the default value + if err := xl.GetSheetPrOptions(sheet, def); err != nil { + t.Fatalf("%T: %s", opt, err) + } + // Get again and check + if err := xl.GetSheetPrOptions(sheet, val1); err != nil { + t.Fatalf("%T: %s", opt, err) + } + if !reflect.DeepEqual(val1, def) { + t.Fatalf("%T: value should not have changed", opt) + } + // Set the same value + if err := xl.SetSheetPrOptions(sheet, val1); err != nil { + t.Fatalf("%T: %s", opt, err) + } + // Get again and check + if err := xl.GetSheetPrOptions(sheet, val1); err != nil { + t.Fatalf("%T: %s", opt, err) + } + if !reflect.DeepEqual(val1, def) { + t.Fatalf("%T: value should not have changed", opt) + } + + // Set a different value + if err := xl.SetSheetPrOptions(sheet, test.nonDefault); err != nil { + t.Fatalf("%T: %s", opt, err) + } + if err := xl.GetSheetPrOptions(sheet, val1); err != nil { + t.Fatalf("%T: %s", opt, err) + } + // Get again and compare + if err := xl.GetSheetPrOptions(sheet, val2); err != nil { + t.Fatalf("%T: %s", opt, err) + } + if !reflect.DeepEqual(val2, val1) { + t.Fatalf("%T: value should not have changed", opt) + } + // Value should not be the same as the default + if reflect.DeepEqual(val1, def) { + t.Fatalf("%T: value should have changed from default", opt) + } + + // Restore the default value + if err := xl.SetSheetPrOptions(sheet, def); err != nil { + t.Fatalf("%T: %s", opt, err) + } + if err := xl.GetSheetPrOptions(sheet, val1); err != nil { + t.Fatalf("%T: %s", opt, err) + } + if !reflect.DeepEqual(val1, def) { + t.Fatalf("%T: value should now be the same as default", opt) + } + } +} diff --git a/sheetview.go b/sheetview.go new file mode 100644 index 0000000..f05e751 --- /dev/null +++ b/sheetview.go @@ -0,0 +1,139 @@ +package excelize + +import "fmt" + +// SheetViewOption is an option of a view of a worksheet. See SetSheetViewOptions(). +type SheetViewOption interface { + setSheetViewOption(view *xlsxSheetView) +} + +// SheetViewOptionPtr is a writable SheetViewOption. See GetSheetViewOptions(). +type SheetViewOptionPtr interface { + SheetViewOption + getSheetViewOption(view *xlsxSheetView) +} + +type ( + // DefaultGridColor is a SheetViewOption. + DefaultGridColor bool + // RightToLeft is a SheetViewOption. + RightToLeft bool + // ShowFormulas is a SheetViewOption. + ShowFormulas bool + // ShowGridLines is a SheetViewOption. + ShowGridLines bool + // ShowRowColHeaders is a SheetViewOption. + ShowRowColHeaders bool + /* TODO + // ShowWhiteSpace is a SheetViewOption. + ShowWhiteSpace bool + // ShowZeros is a SheetViewOption. + ShowZeros bool + // WindowProtection is a SheetViewOption. + WindowProtection bool + */ +) + +// Defaults for each option are described in XML schema for CT_SheetView + +func (o DefaultGridColor) setSheetViewOption(view *xlsxSheetView) { + view.DefaultGridColor = boolPtr(bool(o)) +} + +func (o *DefaultGridColor) getSheetViewOption(view *xlsxSheetView) { + *o = DefaultGridColor(defaultTrue(view.DefaultGridColor)) // Excel default: true +} + +func (o RightToLeft) setSheetViewOption(view *xlsxSheetView) { + view.RightToLeft = bool(o) // Excel default: false +} + +func (o *RightToLeft) getSheetViewOption(view *xlsxSheetView) { + *o = RightToLeft(view.RightToLeft) +} + +func (o ShowFormulas) setSheetViewOption(view *xlsxSheetView) { + view.ShowFormulas = bool(o) // Excel default: false +} + +func (o *ShowFormulas) getSheetViewOption(view *xlsxSheetView) { + *o = ShowFormulas(view.ShowFormulas) // Excel default: false +} + +func (o ShowGridLines) setSheetViewOption(view *xlsxSheetView) { + view.ShowGridLines = boolPtr(bool(o)) +} + +func (o *ShowGridLines) getSheetViewOption(view *xlsxSheetView) { + *o = ShowGridLines(defaultTrue(view.ShowGridLines)) // Excel default: true +} + +func (o ShowRowColHeaders) setSheetViewOption(view *xlsxSheetView) { + view.ShowRowColHeaders = boolPtr(bool(o)) +} + +func (o *ShowRowColHeaders) getSheetViewOption(view *xlsxSheetView) { + *o = ShowRowColHeaders(defaultTrue(view.ShowRowColHeaders)) // Excel default: true +} + +// getSheetView returns the SheetView object +func (f *File) getSheetView(sheetName string, viewIndex int) (*xlsxSheetView, error) { + xlsx := f.workSheetReader(sheetName) + if viewIndex < 0 { + if viewIndex < -len(xlsx.SheetViews.SheetView) { + return nil, fmt.Errorf("view index %d out of range", viewIndex) + } + viewIndex = len(xlsx.SheetViews.SheetView) + viewIndex + } else if viewIndex >= len(xlsx.SheetViews.SheetView) { + return nil, fmt.Errorf("view index %d out of range", viewIndex) + } + + return &(xlsx.SheetViews.SheetView[viewIndex]), nil +} + +// SetSheetViewOptions sets sheet view options. +// The viewIndex may be negative and if so is counted backward (-1 is the last view). +// +// Available options: +// DefaultGridColor(bool) +// RightToLeft(bool) +// ShowFormulas(bool) +// ShowGridLines(bool) +// ShowRowColHeaders(bool) +// Example: +// err = f.SetSheetViewOptions("Sheet1", -1, ShowGridLines(false)) +func (f *File) SetSheetViewOptions(name string, viewIndex int, opts ...SheetViewOption) error { + view, err := f.getSheetView(name, viewIndex) + if err != nil { + return err + } + + for _, opt := range opts { + opt.setSheetViewOption(view) + } + return nil +} + +// GetSheetViewOptions gets the value of sheet view options. +// The viewIndex may be negative and if so is counted backward (-1 is the last view). +// +// Available options: +// DefaultGridColor(bool) +// RightToLeft(bool) +// ShowFormulas(bool) +// ShowGridLines(bool) +// ShowRowColHeaders(bool) +// Example: +// var showGridLines excelize.ShowGridLines +// err = f.GetSheetViewOptions("Sheet1", -1, &showGridLines) +func (f *File) GetSheetViewOptions(name string, viewIndex int, opts ...SheetViewOptionPtr) error { + view, err := f.getSheetView(name, viewIndex) + if err != nil { + return err + } + + for _, opt := range opts { + opt.getSheetViewOption(view) + } + return nil +} diff --git a/sheetview_test.go b/sheetview_test.go new file mode 100644 index 0000000..674a683 --- /dev/null +++ b/sheetview_test.go @@ -0,0 +1,128 @@ +package excelize_test + +import ( + "fmt" + "testing" + + "github.com/xuri/excelize" +) + +var _ = []excelize.SheetViewOption{ + excelize.DefaultGridColor(true), + excelize.RightToLeft(false), + excelize.ShowFormulas(false), + excelize.ShowGridLines(true), + excelize.ShowRowColHeaders(true), + // SheetViewOptionPtr are also SheetViewOption + new(excelize.DefaultGridColor), + new(excelize.RightToLeft), + new(excelize.ShowFormulas), + new(excelize.ShowGridLines), + new(excelize.ShowRowColHeaders), +} + +var _ = []excelize.SheetViewOptionPtr{ + (*excelize.DefaultGridColor)(nil), + (*excelize.RightToLeft)(nil), + (*excelize.ShowFormulas)(nil), + (*excelize.ShowGridLines)(nil), + (*excelize.ShowRowColHeaders)(nil), +} + +func ExampleFile_SetSheetViewOptions() { + xl := excelize.NewFile() + const sheet = "Sheet1" + + if err := xl.SetSheetViewOptions(sheet, 0, + excelize.DefaultGridColor(false), + excelize.RightToLeft(false), + excelize.ShowFormulas(true), + excelize.ShowGridLines(true), + excelize.ShowRowColHeaders(true), + ); err != nil { + panic(err) + } + // Output: +} + +func ExampleFile_GetSheetViewOptions() { + xl := excelize.NewFile() + const sheet = "Sheet1" + + var ( + defaultGridColor excelize.DefaultGridColor + rightToLeft excelize.RightToLeft + showFormulas excelize.ShowFormulas + showGridLines excelize.ShowGridLines + showRowColHeaders excelize.ShowRowColHeaders + ) + + if err := xl.GetSheetViewOptions(sheet, 0, + &defaultGridColor, + &rightToLeft, + &showFormulas, + &showGridLines, + &showRowColHeaders, + ); err != nil { + panic(err) + } + + fmt.Println("Default:") + fmt.Println("- defaultGridColor:", defaultGridColor) + fmt.Println("- rightToLeft:", rightToLeft) + fmt.Println("- showFormulas:", showFormulas) + fmt.Println("- showGridLines:", showGridLines) + fmt.Println("- showRowColHeaders:", showRowColHeaders) + + if err := xl.SetSheetViewOptions(sheet, 0, excelize.ShowGridLines(false)); err != nil { + panic(err) + } + + if err := xl.GetSheetViewOptions(sheet, 0, &showGridLines); err != nil { + panic(err) + } + + fmt.Println("After change:") + fmt.Println("- showGridLines:", showGridLines) + + // Output: + // Default: + // - defaultGridColor: true + // - rightToLeft: false + // - showFormulas: false + // - showGridLines: true + // - showRowColHeaders: true + // After change: + // - showGridLines: false +} + +func TestSheetViewOptionsErrors(t *testing.T) { + xl := excelize.NewFile() + const sheet = "Sheet1" + + if err := xl.GetSheetViewOptions(sheet, 0); err != nil { + t.Errorf("Unexpected error: %s", err) + } + if err := xl.GetSheetViewOptions(sheet, -1); err != nil { + t.Errorf("Unexpected error: %s", err) + } + if err := xl.GetSheetViewOptions(sheet, 1); err == nil { + t.Error("Error expected but got nil") + } + if err := xl.GetSheetViewOptions(sheet, -2); err == nil { + t.Error("Error expected but got nil") + } + + if err := xl.SetSheetViewOptions(sheet, 0); err != nil { + t.Errorf("Unexpected error: %s", err) + } + if err := xl.SetSheetViewOptions(sheet, -1); err != nil { + t.Errorf("Unexpected error: %s", err) + } + if err := xl.SetSheetViewOptions(sheet, 1); err == nil { + t.Error("Error expected but got nil") + } + if err := xl.SetSheetViewOptions(sheet, -2); err == nil { + t.Error("Error expected but got nil") + } +} @@ -925,6 +925,7 @@ func formatToE(i int, v string) string { // 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. +// Based off: http://www.ozgrid.com/Excel/CustomFormats.htm func parseTime(i int, v string) string { f, err := strconv.ParseFloat(v, 64) if err != nil { @@ -943,8 +944,6 @@ func parseTime(i int, v string) string { {"mmm", "Jan"}, {"mmss", "0405"}, {"ss", "05"}, - {"hh", "15"}, - {"h", "3"}, {"mm:", "04:"}, {":mm", ":04"}, {"mm", "01"}, @@ -953,6 +952,15 @@ func parseTime(i int, v string) string { {"%%%%", "January"}, {"&&&&", "Monday"}, } + // It is the presence of the "am/pm" indicator that determines if this is + // a 12 hour or 24 hours time format, not the number of 'h' characters. + if is12HourTime(format) { + format = strings.Replace(format, "hh", "03", 1) + format = strings.Replace(format, "h", "3", 1) + } else { + format = strings.Replace(format, "hh", "15", 1) + format = strings.Replace(format, "h", "15", 1) + } for _, repl := range replacements { format = strings.Replace(format, repl.xltime, repl.gotime, 1) } @@ -960,6 +968,7 @@ func parseTime(i int, v string) string { // colon that would remain. if val.Hour() < 1 { format = strings.Replace(format, "]:", "]", 1) + format = strings.Replace(format, "[03]", "", 1) format = strings.Replace(format, "[3]", "", 1) format = strings.Replace(format, "[15]", "", 1) } else { @@ -969,6 +978,11 @@ func parseTime(i int, v string) string { return val.Format(format) } +// is12HourTime checks whether an Excel time format string is a 12 hours form. +func is12HourTime(format string) bool { + return strings.Contains(format, "am/pm") || strings.Contains(format, "AM/PM") || strings.Contains(format, "a/p") || strings.Contains(format, "A/P") +} + // stylesReader provides function to get the pointer to the structure after // deserialization of xl/styles.xml. func (f *File) stylesReader() *xlsxStyleSheet { diff --git a/xmlChart.go b/xmlChart.go index 5ca3586..229760e 100644 --- a/xmlChart.go +++ b/xmlChart.go @@ -316,6 +316,7 @@ type cCharts struct { DLbls *cDLbls `xml:"c:dLbls"` HoleSize *attrValInt `xml:"c:holeSize"` Smooth *attrValBool `xml:"c:smooth"` + Overlap *attrValInt `xml:"c:overlap"` AxID []*attrValInt `xml:"c:axId"` } diff --git a/xmlWorkbook.go b/xmlWorkbook.go index b04b173..816d5a4 100644 --- a/xmlWorkbook.go +++ b/xmlWorkbook.go @@ -20,18 +20,19 @@ type xlsxWorkbookRelation struct { // http://schemas.openxmlformats.org/spreadsheetml/2006/main - currently I have // not checked it for completeness - it does as much as I need. type xlsxWorkbook struct { - XMLName xml.Name `xml:"http://schemas.openxmlformats.org/spreadsheetml/2006/main workbook"` - FileVersion *xlsxFileVersion `xml:"fileVersion"` - WorkbookPr *xlsxWorkbookPr `xml:"workbookPr"` - WorkbookProtection *xlsxWorkbookProtection `xml:"workbookProtection"` - BookViews xlsxBookViews `xml:"bookViews"` - Sheets xlsxSheets `xml:"sheets"` - ExternalReferences *xlsxExternalReferences `xml:"externalReferences"` - DefinedNames *xlsxDefinedNames `xml:"definedNames"` - CalcPr *xlsxCalcPr `xml:"calcPr"` - PivotCaches *xlsxPivotCaches `xml:"pivotCaches"` - ExtLst *xlsxExtLst `xml:"extLst"` - FileRecoveryPr *xlsxFileRecoveryPr `xml:"fileRecoveryPr"` + XMLName xml.Name `xml:"http://schemas.openxmlformats.org/spreadsheetml/2006/main workbook"` + FileVersion *xlsxFileVersion `xml:"fileVersion"` + WorkbookPr *xlsxWorkbookPr `xml:"workbookPr"` + WorkbookProtection *xlsxWorkbookProtection `xml:"workbookProtection"` + BookViews xlsxBookViews `xml:"bookViews"` + Sheets xlsxSheets `xml:"sheets"` + ExternalReferences *xlsxExternalReferences `xml:"externalReferences"` + DefinedNames *xlsxDefinedNames `xml:"definedNames"` + CalcPr *xlsxCalcPr `xml:"calcPr"` + CustomWorkbookViews *xlsxCustomWorkbookViews `xml:"customWorkbookViews"` + PivotCaches *xlsxPivotCaches `xml:"pivotCaches"` + ExtLst *xlsxExtLst `xml:"extLst"` + FileRecoveryPr *xlsxFileRecoveryPr `xml:"fileRecoveryPr"` } // xlsxFileRecoveryPr maps sheet recovery information. This element defines @@ -233,3 +234,49 @@ type xlsxCalcPr struct { IterateDelta float64 `xml:"iterateDelta,attr,omitempty"` RefMode string `xml:"refMode,attr,omitempty"` } + +// xlsxCustomWorkbookViews defines the collection of custom workbook views that +// are defined for this workbook. A customWorkbookView is similar in concept to +// a workbookView in that its attributes contain settings related to the way +// that the workbook should be displayed on a screen by a spreadsheet +// application. +type xlsxCustomWorkbookViews struct { + CustomWorkbookView []xlsxCustomWorkbookView `xml:"customWorkbookView"` +} + +// xlsxCustomWorkbookView directly maps the customWorkbookView element. This +// element specifies a single custom workbook view. A custom workbook view +// consists of a set of display and print settings that you can name and apply +// to a workbook. You can create more than one custom workbook view of the same +// workbook. Custom Workbook Views are not required in order to construct a +// valid SpreadsheetML document, and are not necessary if the document is never +// displayed by a spreadsheet application, or if the spreadsheet application has +// a fixed display for workbooks. However, if a spreadsheet application chooses +// to implement configurable display modes, the customWorkbookView element +// should be used to persist the settings for those display modes. +type xlsxCustomWorkbookView struct { + ActiveSheetID *int `xml:"activeSheetId,attr"` + AutoUpdate *bool `xml:"autoUpdate,attr"` + ChangesSavedWin *bool `xml:"changesSavedWin,attr"` + GUID *string `xml:"guid,attr"` + IncludeHiddenRowCol *bool `xml:"includeHiddenRowCol,attr"` + IncludePrintSettings *bool `xml:"includePrintSettings,attr"` + Maximized *bool `xml:"maximized,attr"` + MergeInterval int `xml:"mergeInterval,attr"` + Minimized *bool `xml:"minimized,attr"` + Name *string `xml:"name,attr"` + OnlySync *bool `xml:"onlySync,attr"` + PersonalView *bool `xml:"personalView,attr"` + ShowComments *string `xml:"showComments,attr"` + ShowFormulaBar *bool `xml:"showFormulaBar,attr"` + ShowHorizontalScroll *bool `xml:"showHorizontalScroll,attr"` + ShowObjects *string `xml:"showObjects,attr"` + ShowSheetTabs *bool `xml:"showSheetTabs,attr"` + ShowStatusbar *bool `xml:"showStatusbar,attr"` + ShowVerticalScroll *bool `xml:"showVerticalScroll,attr"` + TabRatio *int `xml:"tabRatio,attr"` + WindowHeight *int `xml:"windowHeight,attr"` + WindowWidth *int `xml:"windowWidth,attr"` + XWindow *int `xml:"xWindow,attr"` + YWindow *int `xml:"yWindow,attr"` +} diff --git a/xmlWorksheet.go b/xmlWorksheet.go index d0f4f5d..748ca1f 100644 --- a/xmlWorksheet.go +++ b/xmlWorksheet.go @@ -145,17 +145,18 @@ type xlsxSheetViews struct { // last sheetView definition is loaded, and the others are discarded. When // multiple windows are viewing the same sheet, multiple sheetView elements // (with corresponding workbookView entries) are saved. +// See https://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.sheetview.aspx type xlsxSheetView struct { WindowProtection bool `xml:"windowProtection,attr,omitempty"` ShowFormulas bool `xml:"showFormulas,attr,omitempty"` - ShowGridLines string `xml:"showGridLines,attr,omitempty"` - ShowRowColHeaders bool `xml:"showRowColHeaders,attr,omitempty"` + ShowGridLines *bool `xml:"showGridLines,attr"` + ShowRowColHeaders *bool `xml:"showRowColHeaders,attr"` ShowZeros bool `xml:"showZeros,attr,omitempty"` RightToLeft bool `xml:"rightToLeft,attr,omitempty"` TabSelected bool `xml:"tabSelected,attr,omitempty"` ShowWhiteSpace *bool `xml:"showWhiteSpace,attr"` ShowOutlineSymbols bool `xml:"showOutlineSymbols,attr,omitempty"` - DefaultGridColor bool `xml:"defaultGridColor,attr"` + DefaultGridColor *bool `xml:"defaultGridColor,attr"` View string `xml:"view,attr,omitempty"` TopLeftCell string `xml:"topLeftCell,attr,omitempty"` ColorID int `xml:"colorId,attr,omitempty"` @@ -195,7 +196,7 @@ type xlsxSheetPr struct { CodeName string `xml:"codeName,attr,omitempty"` EnableFormatConditionsCalculation *bool `xml:"enableFormatConditionsCalculation,attr"` FilterMode bool `xml:"filterMode,attr,omitempty"` - Published bool `xml:"published,attr,omitempty"` + Published *bool `xml:"published,attr"` SyncHorizontal bool `xml:"syncHorizontal,attr,omitempty"` SyncVertical bool `xml:"syncVertical,attr,omitempty"` TransitionEntry bool `xml:"transitionEntry,attr,omitempty"` |