diff options
| -rw-r--r-- | col.go | 62 | ||||
| -rw-r--r-- | col_test.go | 176 | ||||
| -rw-r--r-- | excelize_test.go | 157 |
3 files changed, 242 insertions, 153 deletions
@@ -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)) |