From 7e77e14814658486267e3f237f484fa8e63a0cd4 Mon Sep 17 00:00:00 2001 From: xuri Date: Thu, 16 May 2019 13:36:50 +0800 Subject: Resolve #397, support set style by columns --- col.go | 62 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 61 insertions(+), 1 deletion(-) (limited to 'col.go') diff --git a/col.go b/col.go index 6b73fdc..db3a901 100644 --- a/col.go +++ b/col.go @@ -9,7 +9,10 @@ package excelize -import "math" +import ( + "math" + "strings" +) // Define the default cell size and EMU unit of measurement. const ( @@ -155,6 +158,63 @@ func (f *File) SetColOutlineLevel(sheet, col string, level uint8) error { return err } +// SetColStyle provides a function to set style of columns by given worksheet +// name, columns range and style ID. +// +// For example set style of column H on Sheet1: +// +// err = f.SetColStyle("Sheet1", "H", style) +// +// Set style of columns C:F on Sheet1: +// +// err = f.SetColStyle("Sheet1", "C:F", style) +// +func (f *File) SetColStyle(sheet, columns string, styleID int) error { + xlsx, err := f.workSheetReader(sheet) + if err != nil { + return err + } + var c1, c2 string + var min, max int + cols := strings.Split(columns, ":") + c1 = cols[0] + min, err = ColumnNameToNumber(c1) + if err != nil { + return err + } + if len(cols) == 2 { + c2 = cols[1] + max, err = ColumnNameToNumber(c2) + if err != nil { + return err + } + } else { + max = min + } + if max < min { + min, max = max, min + } + if xlsx.Cols == nil { + xlsx.Cols = &xlsxCols{} + } + var find bool + for idx, col := range xlsx.Cols.Col { + if col.Min == min && col.Max == max { + xlsx.Cols.Col[idx].Style = styleID + find = true + } + } + if !find { + xlsx.Cols.Col = append(xlsx.Cols.Col, xlsxCol{ + Min: min, + Max: max, + Width: 9, + Style: styleID, + }) + } + return nil +} + // SetColWidth provides a function to set the width of a single column or // multiple columns. For example: // -- 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 --- col.go | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'col.go') diff --git a/col.go b/col.go index db3a901..ffa0ca6 100644 --- a/col.go +++ b/col.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 a34d3b8c86d67d3ad0bc0dbedb69d3b4ebbc210f Mon Sep 17 00:00:00 2001 From: xuri Date: Tue, 24 Sep 2019 21:53:19 +0800 Subject: Compatibility improvement --- col.go | 10 +++++++--- 1 file changed, 7 insertions(+), 3 deletions(-) (limited to 'col.go') diff --git a/col.go b/col.go index ffa0ca6..be08c29 100644 --- a/col.go +++ b/col.go @@ -10,6 +10,7 @@ package excelize import ( + "errors" "math" "strings" ) @@ -112,19 +113,22 @@ func (f *File) GetColOutlineLevel(sheet, col string) (uint8, error) { for c := range xlsx.Cols.Col { colData := &xlsx.Cols.Col[c] if colData.Min <= colNum && colNum <= colData.Max { - level = colData.OutlineLevel + level = colData.OutlineLevel + 1 } } return level, err } // SetColOutlineLevel provides a function to set outline level of a single -// column by given worksheet name and column name. For example, set outline -// level of column D in Sheet1 to 2: +// column by given worksheet name and column name. The value of parameter +// 'level' is 1-7. For example, set outline level of column D in Sheet1 to 2: // // err := f.SetColOutlineLevel("Sheet1", "D", 2) // func (f *File) SetColOutlineLevel(sheet, col string, level uint8) error { + if level > 7 || level < 1 { + return errors.New("invalid outline level") + } colNum, err := ColumnNameToNumber(col) if err != nil { return err -- cgit v1.2.1 From eb520ae27757d4bca276fa2894bf461d75df9b37 Mon Sep 17 00:00:00 2001 From: xuri Date: Thu, 26 Sep 2019 22:28:14 +0800 Subject: Improve compatibility for charts --- col.go | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'col.go') diff --git a/col.go b/col.go index be08c29..5d4e764 100644 --- a/col.go +++ b/col.go @@ -113,7 +113,7 @@ func (f *File) GetColOutlineLevel(sheet, col string) (uint8, error) { for c := range xlsx.Cols.Col { colData := &xlsx.Cols.Col[c] if colData.Min <= colNum && colNum <= colData.Max { - level = colData.OutlineLevel + 1 + level = colData.OutlineLevel } } return level, err -- 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 --- col.go | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'col.go') diff --git a/col.go b/col.go index 5d4e764..f7e6bcd 100644 --- a/col.go +++ b/col.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 68754a2075f12ba3c2bdf3646e4a3e7a3fd829f5 Mon Sep 17 00:00:00 2001 From: Jacques Boscq Date: Tue, 21 Jan 2020 23:42:44 +0100 Subject: SetColVisible() can parse a column range + typos. --- col.go | 59 ++++++++++++++++++++++++++++++++++++----------------------- 1 file changed, 36 insertions(+), 23 deletions(-) (limited to 'col.go') diff --git a/col.go b/col.go index f7e6bcd..ff771f1 100644 --- a/col.go +++ b/col.go @@ -26,7 +26,7 @@ const ( // worksheet name and column name. For example, get visible state of column D // in Sheet1: // -// visiable, err := f.GetColVisible("Sheet1", "D") +// visible, err := f.GetColVisible("Sheet1", "D") // func (f *File) GetColVisible(sheet, col string) (bool, error) { visible := true @@ -52,45 +52,58 @@ func (f *File) GetColVisible(sheet, col string) (bool, error) { return visible, err } -// SetColVisible provides a function to set visible of a single column by given -// worksheet name and column name. For example, hide column D in Sheet1: +// SetColVisible provides a function to set visible columns by given worksheet +// name, columns range and visibility. +// +// For example hide column D on Sheet1: // // err := f.SetColVisible("Sheet1", "D", false) // -func (f *File) SetColVisible(sheet, col string, visible bool) error { - colNum, err := ColumnNameToNumber(col) +// Hide the columns from D to F (included) +// +// err := f.SetColVisible("Sheet1", "D:F", false) +// +func (f *File) SetColVisible(sheet, columns string, visible bool) error { + var max int + + colsTab := strings.Split(columns, ":") + min, err := ColumnNameToNumber(colsTab[0]) if err != nil { return err } - colData := xlsxCol{ - Min: colNum, - Max: colNum, - Hidden: !visible, - CustomWidth: true, + if len(colsTab) == 2 { + max, err = ColumnNameToNumber(colsTab[1]) + if err != nil { + return err + } + } else { + max = min + } + if max < min { + min, max = max, min } xlsx, err := f.workSheetReader(sheet) if err != nil { return err } - if xlsx.Cols == nil { + colData := xlsxCol{ + Min: min, + Max: max, + Width: 9, // default width + Hidden: !visible, + CustomWidth: true, + } + if xlsx.Cols != nil { + xlsx.Cols.Col = append(xlsx.Cols.Col, colData) + } else { cols := xlsxCols{} cols.Col = append(cols.Col, colData) xlsx.Cols = &cols - return err } - for v := range xlsx.Cols.Col { - if xlsx.Cols.Col[v].Min <= colNum && colNum <= xlsx.Cols.Col[v].Max { - colData = xlsx.Cols.Col[v] - } - } - colData.Min = colNum - colData.Max = colNum - colData.Hidden = !visible - colData.CustomWidth = true - xlsx.Cols.Col = append(xlsx.Cols.Col, colData) - return err + return nil } + // GetColOutlineLevel provides a function to get outline level of a single // column by given worksheet name and column name. For example, get outline // level of column D in Sheet1: -- cgit v1.2.1 From e51aff2d9562bbfb290ef76a948facb6d4660eff Mon Sep 17 00:00:00 2001 From: xuri Date: Fri, 7 Feb 2020 00:25:01 +0800 Subject: Resolve #570, flat columns for the column's operation --- col.go | 125 +++++++++++++++++++++++++++++++++++++++++++++-------------------- 1 file changed, 87 insertions(+), 38 deletions(-) (limited to 'col.go') diff --git a/col.go b/col.go index ff771f1..6f76800 100644 --- a/col.go +++ b/col.go @@ -13,6 +13,8 @@ import ( "errors" "math" "strings" + + "github.com/mohae/deepcopy" ) // Define the default cell size and EMU unit of measurement. @@ -59,7 +61,7 @@ func (f *File) GetColVisible(sheet, col string) (bool, error) { // // err := f.SetColVisible("Sheet1", "D", false) // -// Hide the columns from D to F (included) +// Hide the columns from D to F (included): // // err := f.SetColVisible("Sheet1", "D:F", false) // @@ -87,23 +89,31 @@ func (f *File) SetColVisible(sheet, columns string, visible bool) error { return err } colData := xlsxCol{ - Min: min, - Max: max, - Width: 9, // default width - Hidden: !visible, + Min: min, + Max: max, + Width: 9, // default width + Hidden: !visible, CustomWidth: true, } - if xlsx.Cols != nil { - xlsx.Cols.Col = append(xlsx.Cols.Col, colData) - } else { + if xlsx.Cols == nil { cols := xlsxCols{} cols.Col = append(cols.Col, colData) xlsx.Cols = &cols - } + return nil + } + xlsx.Cols.Col = flatCols(colData, xlsx.Cols.Col, func(fc, c xlsxCol) xlsxCol { + fc.BestFit = c.BestFit + fc.Collapsed = c.Collapsed + fc.CustomWidth = c.CustomWidth + fc.OutlineLevel = c.OutlineLevel + fc.Phonetic = c.Phonetic + fc.Style = c.Style + fc.Width = c.Width + return fc + }) return nil } - // GetColOutlineLevel provides a function to get outline level of a single // column by given worksheet name and column name. For example, get outline // level of column D in Sheet1: @@ -162,16 +172,16 @@ func (f *File) SetColOutlineLevel(sheet, col string, level uint8) error { xlsx.Cols = &cols return err } - for v := range xlsx.Cols.Col { - if xlsx.Cols.Col[v].Min <= colNum && colNum <= xlsx.Cols.Col[v].Max { - colData = xlsx.Cols.Col[v] - } - } - colData.Min = colNum - colData.Max = colNum - colData.OutlineLevel = level - colData.CustomWidth = true - xlsx.Cols.Col = append(xlsx.Cols.Col, colData) + xlsx.Cols.Col = flatCols(colData, xlsx.Cols.Col, func(fc, c xlsxCol) xlsxCol { + fc.BestFit = c.BestFit + fc.Collapsed = c.Collapsed + fc.CustomWidth = c.CustomWidth + fc.Hidden = c.Hidden + fc.Phonetic = c.Phonetic + fc.Style = c.Style + fc.Width = c.Width + return fc + }) return err } @@ -214,21 +224,21 @@ func (f *File) SetColStyle(sheet, columns string, styleID int) error { if xlsx.Cols == nil { xlsx.Cols = &xlsxCols{} } - var find bool - for idx, col := range xlsx.Cols.Col { - if col.Min == min && col.Max == max { - xlsx.Cols.Col[idx].Style = styleID - find = true - } - } - if !find { - xlsx.Cols.Col = append(xlsx.Cols.Col, xlsxCol{ - Min: min, - Max: max, - Width: 9, - Style: styleID, - }) - } + xlsx.Cols.Col = flatCols(xlsxCol{ + Min: min, + Max: max, + Width: 9, + Style: styleID, + }, xlsx.Cols.Col, func(fc, c xlsxCol) xlsxCol { + fc.BestFit = c.BestFit + fc.Collapsed = c.Collapsed + fc.CustomWidth = c.CustomWidth + fc.Hidden = c.Hidden + fc.OutlineLevel = c.OutlineLevel + fc.Phonetic = c.Phonetic + fc.Width = c.Width + return fc + }) return nil } @@ -261,16 +271,55 @@ func (f *File) SetColWidth(sheet, startcol, endcol string, width float64) error Width: width, CustomWidth: true, } - if xlsx.Cols != nil { - xlsx.Cols.Col = append(xlsx.Cols.Col, col) - } else { + if xlsx.Cols == nil { cols := xlsxCols{} cols.Col = append(cols.Col, col) xlsx.Cols = &cols + return err } + xlsx.Cols.Col = flatCols(col, xlsx.Cols.Col, func(fc, c xlsxCol) xlsxCol { + fc.BestFit = c.BestFit + fc.Collapsed = c.Collapsed + fc.Hidden = c.Hidden + fc.OutlineLevel = c.OutlineLevel + fc.Phonetic = c.Phonetic + fc.Style = c.Style + return fc + }) return err } +// flatCols provides a method for the column's operation functions to flatten +// and check the worksheet columns. +func flatCols(col xlsxCol, cols []xlsxCol, replacer func(fc, c xlsxCol) xlsxCol) []xlsxCol { + fc := []xlsxCol{} + for i := col.Min; i <= col.Max; i++ { + c := deepcopy.Copy(col).(xlsxCol) + c.Min, c.Max = i, i + fc = append(fc, c) + } + inFlat := func(colID int, cols []xlsxCol) (int, bool) { + for idx, c := range cols { + if c.Max == colID && c.Min == colID { + return idx, true + } + } + return -1, false + } + for _, column := range cols { + for i := column.Min; i <= column.Max; i++ { + if idx, ok := inFlat(i, fc); ok { + fc[idx] = replacer(fc[idx], column) + continue + } + c := deepcopy.Copy(column).(xlsxCol) + c.Min, c.Max = i, i + fc = append(fc, c) + } + } + return fc +} + // positionObjectPixels calculate the vertices that define the position of a // graphical object within the worksheet in pixels. // -- cgit v1.2.1