From 7e77e14814658486267e3f237f484fa8e63a0cd4 Mon Sep 17 00:00:00 2001
From: xuri <xuri.me@gmail.com>
Date: Thu, 16 May 2019 13:36:50 +0800
Subject: Resolve #397, support set style by columns
---
col.go | 62 +++++++++++++++++++-
col_test.go | 176 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
excelize_test.go | 157 ++-----------------------------------------------
3 files changed, 242 insertions(+), 153 deletions(-)
create mode 100644 col_test.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:
//
diff --git a/col_test.go b/col_test.go
new file mode 100644
index 0000000..e3164d4
--- /dev/null
+++ b/col_test.go
@@ -0,0 +1,176 @@
+package excelize
+
+import (
+ "path/filepath"
+ "testing"
+
+ "github.com/stretchr/testify/assert"
+)
+
+func TestColumnVisibility(t *testing.T) {
+ t.Run("TestBook1", func(t *testing.T) {
+ f, err := prepareTestBook1()
+ if !assert.NoError(t, err) {
+ t.FailNow()
+ }
+
+ assert.NoError(t, f.SetColVisible("Sheet1", "F", false))
+ assert.NoError(t, f.SetColVisible("Sheet1", "F", true))
+ visible, err := f.GetColVisible("Sheet1", "F")
+ assert.Equal(t, true, visible)
+ assert.NoError(t, err)
+
+ // Test get column visiable on not exists worksheet.
+ _, err = f.GetColVisible("SheetN", "F")
+ assert.EqualError(t, err, "sheet SheetN is not exist")
+
+ // Test get column visiable with illegal cell coordinates.
+ _, err = f.GetColVisible("Sheet1", "*")
+ assert.EqualError(t, err, `invalid column name "*"`)
+ assert.EqualError(t, f.SetColVisible("Sheet1", "*", false), `invalid column name "*"`)
+
+ f.NewSheet("Sheet3")
+ assert.NoError(t, f.SetColVisible("Sheet3", "E", false))
+
+ assert.EqualError(t, f.SetColVisible("SheetN", "E", false), "sheet SheetN is not exist")
+ assert.NoError(t, f.SaveAs(filepath.Join("test", "TestColumnVisibility.xlsx")))
+ })
+
+ t.Run("TestBook3", func(t *testing.T) {
+ f, err := prepareTestBook3()
+ if !assert.NoError(t, err) {
+ t.FailNow()
+ }
+ f.GetColVisible("Sheet1", "B")
+ })
+}
+
+func TestOutlineLevel(t *testing.T) {
+ f := NewFile()
+ f.GetColOutlineLevel("Sheet1", "D")
+ f.NewSheet("Sheet2")
+ f.SetColOutlineLevel("Sheet1", "D", 4)
+ f.GetColOutlineLevel("Sheet1", "D")
+ f.GetColOutlineLevel("Shee2", "A")
+ f.SetColWidth("Sheet2", "A", "D", 13)
+ f.SetColOutlineLevel("Sheet2", "B", 2)
+ f.SetRowOutlineLevel("Sheet1", 2, 250)
+
+ // Test set and get column outline level with illegal cell coordinates.
+ assert.EqualError(t, f.SetColOutlineLevel("Sheet1", "*", 1), `invalid column name "*"`)
+ _, err := f.GetColOutlineLevel("Sheet1", "*")
+ assert.EqualError(t, err, `invalid column name "*"`)
+
+ // Test set column outline level on not exists worksheet.
+ assert.EqualError(t, f.SetColOutlineLevel("SheetN", "E", 2), "sheet SheetN is not exist")
+
+ assert.EqualError(t, f.SetRowOutlineLevel("Sheet1", 0, 1), "invalid row number 0")
+ level, err := f.GetRowOutlineLevel("Sheet1", 2)
+ assert.NoError(t, err)
+ assert.Equal(t, uint8(250), level)
+
+ _, err = f.GetRowOutlineLevel("Sheet1", 0)
+ assert.EqualError(t, err, `invalid row number 0`)
+
+ level, err = f.GetRowOutlineLevel("Sheet1", 10)
+ assert.NoError(t, err)
+ assert.Equal(t, uint8(0), level)
+
+ err = f.SaveAs(filepath.Join("test", "TestOutlineLevel.xlsx"))
+ if !assert.NoError(t, err) {
+ t.FailNow()
+ }
+
+ f, err = OpenFile(filepath.Join("test", "Book1.xlsx"))
+ if !assert.NoError(t, err) {
+ t.FailNow()
+ }
+ f.SetColOutlineLevel("Sheet2", "B", 2)
+}
+
+func TestSetColStyle(t *testing.T) {
+ f := NewFile()
+ style, err := f.NewStyle(`{"fill":{"type":"pattern","color":["#94d3a2"],"pattern":1}}`)
+ assert.NoError(t, err)
+ // Test set column style on not exists worksheet.
+ assert.EqualError(t, f.SetColStyle("SheetN", "E", style), "sheet SheetN is not exist")
+ // Test set column style with illegal cell coordinates.
+ assert.EqualError(t, f.SetColStyle("Sheet1", "*", style), `invalid column name "*"`)
+ assert.EqualError(t, f.SetColStyle("Sheet1", "A:*", style), `invalid column name "*"`)
+
+ assert.NoError(t, f.SetColStyle("Sheet1", "B", style))
+ // Test set column style with already exists column with style.
+ assert.NoError(t, f.SetColStyle("Sheet1", "B", style))
+ assert.NoError(t, f.SetColStyle("Sheet1", "D:C", style))
+ assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetColStyle.xlsx")))
+}
+
+func TestColWidth(t *testing.T) {
+ f := NewFile()
+ f.SetColWidth("Sheet1", "B", "A", 12)
+ f.SetColWidth("Sheet1", "A", "B", 12)
+ f.GetColWidth("Sheet1", "A")
+ f.GetColWidth("Sheet1", "C")
+
+ // Test set and get column width with illegal cell coordinates.
+ _, err := f.GetColWidth("Sheet1", "*")
+ assert.EqualError(t, err, `invalid column name "*"`)
+ assert.EqualError(t, f.SetColWidth("Sheet1", "*", "B", 1), `invalid column name "*"`)
+ assert.EqualError(t, f.SetColWidth("Sheet1", "A", "*", 1), `invalid column name "*"`)
+
+ // Test set column width on not exists worksheet.
+ assert.EqualError(t, f.SetColWidth("SheetN", "B", "A", 12), "sheet SheetN is not exist")
+
+ // Test get column width on not exists worksheet.
+ _, err = f.GetColWidth("SheetN", "A")
+ assert.EqualError(t, err, "sheet SheetN is not exist")
+
+ assert.NoError(t, f.SaveAs(filepath.Join("test", "TestColWidth.xlsx")))
+ convertRowHeightToPixels(0)
+}
+
+func TestInsertCol(t *testing.T) {
+ f := NewFile()
+ sheet1 := f.GetSheetName(1)
+
+ fillCells(f, sheet1, 10, 10)
+
+ f.SetCellHyperLink(sheet1, "A5", "https://github.com/360EntSecGroup-Skylar/excelize", "External")
+ f.MergeCell(sheet1, "A1", "C3")
+
+ err := f.AutoFilter(sheet1, "A2", "B2", `{"column":"B","expression":"x != blanks"}`)
+ if !assert.NoError(t, err) {
+ t.FailNow()
+ }
+
+ assert.NoError(t, f.InsertCol(sheet1, "A"))
+
+ // Test insert column with illegal cell coordinates.
+ assert.EqualError(t, f.InsertCol("Sheet1", "*"), `invalid column name "*"`)
+
+ assert.NoError(t, f.SaveAs(filepath.Join("test", "TestInsertCol.xlsx")))
+}
+
+func TestRemoveCol(t *testing.T) {
+ f := NewFile()
+ sheet1 := f.GetSheetName(1)
+
+ fillCells(f, sheet1, 10, 15)
+
+ f.SetCellHyperLink(sheet1, "A5", "https://github.com/360EntSecGroup-Skylar/excelize", "External")
+ f.SetCellHyperLink(sheet1, "C5", "https://github.com", "External")
+
+ f.MergeCell(sheet1, "A1", "B1")
+ f.MergeCell(sheet1, "A2", "B2")
+
+ assert.NoError(t, f.RemoveCol(sheet1, "A"))
+ assert.NoError(t, f.RemoveCol(sheet1, "A"))
+
+ // Test remove column with illegal cell coordinates.
+ assert.EqualError(t, f.RemoveCol("Sheet1", "*"), `invalid column name "*"`)
+
+ // Test remove column on not exists worksheet.
+ assert.EqualError(t, f.RemoveCol("SheetN", "B"), "sheet SheetN is not exist")
+
+ assert.NoError(t, f.SaveAs(filepath.Join("test", "TestRemoveCol.xlsx")))
+}
diff --git a/excelize_test.go b/excelize_test.go
index 3509cb8..85df09b 100644
--- a/excelize_test.go
+++ b/excelize_test.go
@@ -244,30 +244,6 @@ func TestNewFile(t *testing.T) {
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestNewFile.xlsx")))
}
-func TestColWidth(t *testing.T) {
- xlsx := NewFile()
- xlsx.SetColWidth("Sheet1", "B", "A", 12)
- xlsx.SetColWidth("Sheet1", "A", "B", 12)
- xlsx.GetColWidth("Sheet1", "A")
- xlsx.GetColWidth("Sheet1", "C")
-
- // Test set and get column width with illegal cell coordinates.
- _, err := xlsx.GetColWidth("Sheet1", "*")
- assert.EqualError(t, err, `invalid column name "*"`)
- assert.EqualError(t, xlsx.SetColWidth("Sheet1", "*", "B", 1), `invalid column name "*"`)
- assert.EqualError(t, xlsx.SetColWidth("Sheet1", "A", "*", 1), `invalid column name "*"`)
-
- // Test get column width on not exists worksheet.
- _, err = xlsx.GetColWidth("SheetN", "A")
- assert.EqualError(t, err, "sheet SheetN is not exist")
-
- err = xlsx.SaveAs(filepath.Join("test", "TestColWidth.xlsx"))
- if err != nil {
- t.Error(err)
- }
- convertRowHeightToPixels(0)
-}
-
func TestAddDrawingVML(t *testing.T) {
// Test addDrawingVML with illegal cell coordinates.
f := NewFile()
@@ -744,44 +720,6 @@ func TestSheetVisibility(t *testing.T) {
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSheetVisibility.xlsx")))
}
-func TestColumnVisibility(t *testing.T) {
- t.Run("TestBook1", func(t *testing.T) {
- f, err := prepareTestBook1()
- if !assert.NoError(t, err) {
- t.FailNow()
- }
-
- assert.NoError(t, f.SetColVisible("Sheet1", "F", false))
- assert.NoError(t, f.SetColVisible("Sheet1", "F", true))
- visible, err := f.GetColVisible("Sheet1", "F")
- assert.Equal(t, true, visible)
- assert.NoError(t, err)
-
- // Test get column visiable on not exists worksheet.
- _, err = f.GetColVisible("SheetN", "F")
- assert.EqualError(t, err, "sheet SheetN is not exist")
-
- // Test get column visiable with illegal cell coordinates.
- _, err = f.GetColVisible("Sheet1", "*")
- assert.EqualError(t, err, `invalid column name "*"`)
- assert.EqualError(t, f.SetColVisible("Sheet1", "*", false), `invalid column name "*"`)
-
- f.NewSheet("Sheet3")
- assert.NoError(t, f.SetColVisible("Sheet3", "E", false))
-
- assert.EqualError(t, f.SetColVisible("SheetN", "E", false), "sheet SheetN is not exist")
- assert.NoError(t, f.SaveAs(filepath.Join("test", "TestColumnVisibility.xlsx")))
- })
-
- t.Run("TestBook3", func(t *testing.T) {
- f, err := prepareTestBook3()
- if !assert.NoError(t, err) {
- t.FailNow()
- }
- f.GetColVisible("Sheet1", "B")
- })
-}
-
func TestCopySheet(t *testing.T) {
f, err := prepareTestBook1()
if !assert.NoError(t, err) {
@@ -870,8 +808,11 @@ func TestAddComments(t *testing.T) {
}
s := strings.Repeat("c", 32768)
- f.AddComment("Sheet1", "A30", `{"author":"`+s+`","text":"`+s+`"}`)
- f.AddComment("Sheet2", "B7", `{"author":"Excelize: ","text":"This is a comment."}`)
+ assert.NoError(t, f.AddComment("Sheet1", "A30", `{"author":"`+s+`","text":"`+s+`"}`))
+ assert.NoError(t, f.AddComment("Sheet2", "B7", `{"author":"Excelize: ","text":"This is a comment."}`))
+
+ // Test add comment on not exists worksheet.
+ assert.EqualError(t, f.AddComment("SheetN", "B7", `{"author":"Excelize: ","text":"This is a comment."}`), "sheet SheetN is not exist")
if assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddComments.xlsx"))) {
assert.Len(t, f.GetComments(), 2)
@@ -990,52 +931,6 @@ func TestAddChart(t *testing.T) {
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddChart.xlsx")))
}
-func TestInsertCol(t *testing.T) {
- f := NewFile()
- sheet1 := f.GetSheetName(1)
-
- fillCells(f, sheet1, 10, 10)
-
- f.SetCellHyperLink(sheet1, "A5", "https://github.com/360EntSecGroup-Skylar/excelize", "External")
- f.MergeCell(sheet1, "A1", "C3")
-
- err := f.AutoFilter(sheet1, "A2", "B2", `{"column":"B","expression":"x != blanks"}`)
- if !assert.NoError(t, err) {
- t.FailNow()
- }
-
- assert.NoError(t, f.InsertCol(sheet1, "A"))
-
- // Test insert column with illegal cell coordinates.
- assert.EqualError(t, f.InsertCol("Sheet1", "*"), `invalid column name "*"`)
-
- assert.NoError(t, f.SaveAs(filepath.Join("test", "TestInsertCol.xlsx")))
-}
-
-func TestRemoveCol(t *testing.T) {
- f := NewFile()
- sheet1 := f.GetSheetName(1)
-
- fillCells(f, sheet1, 10, 15)
-
- f.SetCellHyperLink(sheet1, "A5", "https://github.com/360EntSecGroup-Skylar/excelize", "External")
- f.SetCellHyperLink(sheet1, "C5", "https://github.com", "External")
-
- f.MergeCell(sheet1, "A1", "B1")
- f.MergeCell(sheet1, "A2", "B2")
-
- assert.NoError(t, f.RemoveCol(sheet1, "A"))
- assert.NoError(t, f.RemoveCol(sheet1, "A"))
-
- // Test remove column with illegal cell coordinates.
- assert.EqualError(t, f.RemoveCol("Sheet1", "*"), `invalid column name "*"`)
-
- // Test remove column on not exists worksheet.
- assert.EqualError(t, f.RemoveCol("SheetN", "B"), "sheet SheetN is not exist")
-
- assert.NoError(t, f.SaveAs(filepath.Join("test", "TestRemoveCol.xlsx")))
-}
-
func TestSetPane(t *testing.T) {
f := NewFile()
f.SetPanes("Sheet1", `{"freeze":false,"split":false}`)
@@ -1162,48 +1057,6 @@ func TestSetSheetRow(t *testing.T) {
assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetSheetRow.xlsx")))
}
-func TestOutlineLevel(t *testing.T) {
- f := NewFile()
- f.NewSheet("Sheet2")
- f.SetColOutlineLevel("Sheet1", "D", 4)
- f.GetColOutlineLevel("Sheet1", "D")
- f.GetColOutlineLevel("Shee2", "A")
- f.SetColWidth("Sheet2", "A", "D", 13)
- f.SetColOutlineLevel("Sheet2", "B", 2)
- f.SetRowOutlineLevel("Sheet1", 2, 250)
-
- // Test set and get column outline level with illegal cell coordinates.
- assert.EqualError(t, f.SetColOutlineLevel("Sheet1", "*", 1), `invalid column name "*"`)
- _, err := f.GetColOutlineLevel("Sheet1", "*")
- assert.EqualError(t, err, `invalid column name "*"`)
-
- // Test set column outline level on not exists worksheet.
- assert.EqualError(t, f.SetColOutlineLevel("SheetN", "E", 2), "sheet SheetN is not exist")
-
- assert.EqualError(t, f.SetRowOutlineLevel("Sheet1", 0, 1), "invalid row number 0")
- level, err := f.GetRowOutlineLevel("Sheet1", 2)
- assert.NoError(t, err)
- assert.Equal(t, uint8(250), level)
-
- _, err = f.GetRowOutlineLevel("Sheet1", 0)
- assert.EqualError(t, err, `invalid row number 0`)
-
- level, err = f.GetRowOutlineLevel("Sheet1", 10)
- assert.NoError(t, err)
- assert.Equal(t, uint8(0), level)
-
- err = f.SaveAs(filepath.Join("test", "TestOutlineLevel.xlsx"))
- if !assert.NoError(t, err) {
- t.FailNow()
- }
-
- f, err = OpenFile(filepath.Join("test", "Book1.xlsx"))
- if !assert.NoError(t, err) {
- t.FailNow()
- }
- f.SetColOutlineLevel("Sheet2", "B", 2)
-}
-
func TestThemeColor(t *testing.T) {
t.Log(ThemeColor("000000", -0.1))
t.Log(ThemeColor("000000", 0))
--
cgit v1.2.1