diff options
| -rw-r--r-- | excelize_test.go | 19 | ||||
| -rw-r--r-- | sheet.go | 126 | ||||
| -rw-r--r-- | xmlWorksheet.go | 17 |
3 files changed, 160 insertions, 2 deletions
diff --git a/excelize_test.go b/excelize_test.go index 5b942e1..613da1b 100644 --- a/excelize_test.go +++ b/excelize_test.go @@ -142,6 +142,7 @@ func TestBrokenFile(t *testing.T) { // Test set active sheet without BookViews and Sheets maps in xl/workbook.xml. f3, err := OpenFile("./test/badWorkbook.xlsx") + f3.GetActiveSheetIndex() f3.SetActiveSheet(2) if err != nil { t.Log(err) @@ -563,7 +564,8 @@ func TestSetDeleteSheet(t *testing.T) { t.Log(err) } xlsx.DeleteSheet("Sheet1") - err = xlsx.Save() + xlsx.AddComment("Sheet1", "A1", `{"author":"Excelize: ","text":"This is a comment."}`) + err = xlsx.SaveAs("./test/Workbook_delete_sheet.xlsx") if err != nil { t.Log(err) } @@ -832,6 +834,21 @@ func TestInsertRow(t *testing.T) { } } +func TestSetPane(t *testing.T) { + xlsx := NewFile() + xlsx.SetPanes("Sheet1", `{"freeze":false,"split":false}`) + xlsx.NewSheet(2, "Panes 2") + xlsx.SetPanes("Sheet2", `{"freeze":true,"split":false,"x_split":1,"y_split":0,"top_left_cell":"B1","active_pane":"topRight","panes":[{"sqref":"K16","active_cell":"K16","pane":"topRight"}]}`) + xlsx.NewSheet(3, "Panes 3") + xlsx.SetPanes("Sheet3", `{"freeze":false,"split":true,"x_split":3270,"y_split":1800,"top_left_cell":"N57","active_pane":"bottomLeft","panes":[{"sqref":"I36","active_cell":"I36"},{"sqref":"G33","active_cell":"G33","pane":"topRight"},{"sqref":"J60","active_cell":"J60","pane":"bottomLeft"},{"sqref":"O60","active_cell":"O60","pane":"bottomRight"}]}`) + xlsx.NewSheet(4, "Panes 4") + xlsx.SetPanes("Sheet4", `{"freeze":true,"split":false,"x_split":0,"y_split":9,"top_left_cell":"A34","active_pane":"bottomLeft","panes":[{"sqref":"A11:XFD11","active_cell":"A11","pane":"bottomLeft"}]}`) + err := xlsx.SaveAs("./test/Workbook_set_panes.xlsx") + if err != nil { + t.Log(err) + } +} + func TestRemoveRow(t *testing.T) { xlsx := NewFile() for j := 1; j <= 10; j++ { @@ -2,6 +2,7 @@ package excelize import ( "bytes" + "encoding/json" "encoding/xml" "errors" "os" @@ -497,6 +498,131 @@ func (f *File) SetSheetVisible(name string, visible bool) { } } +// parseFormatPanesSet provides function to parse the panes settings. +func parseFormatPanesSet(formatSet string) *formatPanes { + format := formatPanes{} + json.Unmarshal([]byte(formatSet), &format) + return &format +} + +// SetPanes provides function to create and remove freeze panes and split panes +// by given worksheet index and panes format set. +// +// activePane defines the pane that is active. The possible values for this +// attribute are defined in the following table: +// +// Enumeration Value | Description +// --------------------------------+------------------------------------------------------------- +// bottomLeft (Bottom Left Pane) | Bottom left pane, when both vertical and horizontal +// | splits are applied. +// | +// | This value is also used when only a horizontal split has +// | been applied, dividing the pane into upper and lower +// | regions. In that case, this value specifies the bottom +// | pane. +// | +// bottomRight (Bottom Right Pane) | Bottom right pane, when both vertical and horizontal +// | splits are applied. +// | +// topLeft (Top Left Pane) | Top left pane, when both vertical and horizontal splits +// | are applied. +// | +// | This value is also used when only a horizontal split has +// | been applied, dividing the pane into upper and lower +// | regions. In that case, this value specifies the top pane. +// | +// | This value is also used when only a vertical split has +// | been applied, dividing the pane into right and left +// | regions. In that case, this value specifies the left pane +// | +// | Top right pane, when both vertical and horizontal +// | splits are applied. +// | +// topRight (Top Right Pane) | This value is also used when only a vertical split has +// | splits are applied. +// | +// | +// | This value is also used when only a vertical split has +// | been applied, dividing the pane into right and left +// | regions. In that case, this value specifies the right +// | pane. +// +// Pane state type is restricted to the values supported currently listed in the following table: +// +// Enumeration Value | Description +// --------------------------------+------------------------------------------------------------- +// frozen (Frozen) | Panes are frozen, but were not split being frozen. In +// | this state, when the panes are unfrozen again, a single +// | pane results, with no split. +// | +// | In this state, the split bars are not adjustable. +// | +// split (Split) | Panes are split, but not frozen. In this state, the split +// | bars are adjustable by the user. +// +// x_split (Horizontal Split Position): Horizontal position of the split, in +// 1/20th of a point; 0 (zero) if none. If the pane is frozen, this value +// indicates the number of columns visible in the top pane. +// +// y_split (Vertical Split Position): Vertical position of the split, in 1/20th +// of a point; 0 (zero) if none. If the pane is frozen, this value indicates the +// number of rows visible in the left pane. The possible values for this +// attribute are defined by the W3C XML Schema double datatype. +// +// top_left_cell: Location of the top left visible cell in the bottom right pane +// (when in Left-To-Right mode). +// +// sqref (Sequence of References): Range of the selection. Can be non-contiguous +// set of ranges. +// +// An example of how to freeze column A in the Sheet1 and set the active cell on +// Sheet1!A16: +// +// xlsx.SetPanes("Sheet1", `{"freeze":true,"split":false,"x_split":1,"y_split":0,"topLeftCell":"B1","activePane":"topRight","panes":[{"sqref":"K16","active_cell":"K16","pane":"topRight"}]}`) +// +// An example of how to freeze rows 1 to 9 in the Sheet1 and set the active cell +// on Sheet1!A11: +// +// xlsx.SetPanes("Sheet1", `{"freeze":true,"split":false,"x_split":0,"y_split":9,"topLeftCell":"A34","activePane":"bottomLeft","panes":[{"sqref":"A11:XFD11","active_cell":"A11","pane":"bottomLeft"}]}`) +// +// An example of how to create split panes in the Sheet1 and set the active cell +// on Sheet1!J60: +// +// xlsx.SetPanes("Sheet1", `{"freeze":false,"split":true,"x_split":3270,"y_split":1800,"topLeftCell":"N57","activePane":"bottomLeft","panes":[{"sqref":"I36","active_cell":"I36"},{"sqref":"G33","active_cell":"G33","pane":"topRight"},{"sqref":"J60","active_cell":"J60","pane":"bottomLeft"},{"sqref":"O60","active_cell":"O60","pane":"bottomRight"}]}`) +// +// An example of how to unfreeze and remove all panes on Sheet1: +// +// xlsx.SetPanes("Sheet1", `{"freeze":false,"split":false}`) +// +func (f *File) SetPanes(sheet, panes string) { + fs := parseFormatPanesSet(panes) + xlsx := f.workSheetReader(sheet) + p := &xlsxPane{ + ActivePane: fs.ActivePane, + TopLeftCell: fs.TopLeftCell, + XSplit: float64(fs.XSplit), + YSplit: float64(fs.YSplit), + } + if fs.Freeze { + p.State = "frozen" + } + xlsx.SheetViews.SheetView[len(xlsx.SheetViews.SheetView)-1].Pane = p + if !(fs.Freeze) && !(fs.Split) { + if len(xlsx.SheetViews.SheetView) > 0 { + xlsx.SheetViews.SheetView[len(xlsx.SheetViews.SheetView)-1].Pane = nil + } + } + s := []*xlsxSelection{} + for _, p := range fs.Panes { + s = append(s, &xlsxSelection{ + ActiveCell: p.ActiveCell, + Pane: p.Pane, + SQRef: p.SQRef, + }) + } + xlsx.SheetViews.SheetView[len(xlsx.SheetViews.SheetView)-1].Selection = s +} + // GetSheetVisible provides function to get worksheet visible by given worksheet // name. For example, get visible state of Sheet1: // diff --git a/xmlWorksheet.go b/xmlWorksheet.go index f31d16c..ca757fd 100644 --- a/xmlWorksheet.go +++ b/xmlWorksheet.go @@ -172,7 +172,7 @@ type xlsxSheetView struct { // selection. type xlsxSelection struct { ActiveCell string `xml:"activeCell,attr,omitempty"` - ActiveCellID int `xml:"activeCellId,attr"` + ActiveCellID *int `xml:"activeCellId,attr"` Pane string `xml:"pane,attr,omitempty"` SQRef string `xml:"sqref,attr,omitempty"` } @@ -450,3 +450,18 @@ type xlsxPicture struct { type xlsxLegacyDrawing struct { RID string `xml:"http://schemas.openxmlformats.org/officeDocument/2006/relationships id,attr,omitempty"` } + +// formatPanes directly maps the settings of the panes. +type formatPanes struct { + Freeze bool `json:"freeze"` + Split bool `json:"split"` + XSplit int `json:"x_split"` + YSplit int `json:"y_split"` + TopLeftCell string `json:"top_left_cell"` + ActivePane string `json:"active_pane"` + Panes []struct { + SQRef string `json:"sqref"` + ActiveCell string `json:"active_cell"` + Pane string `json:"pane"` + } `json:"panes"` +} |