360Excelize/sheet.go

// 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.
//
// Package excelize providing a set of functions that allow you to write to
// and read from XLSX / XLSM / XLTM files. Supports reading and writing
// spreadsheet documents generated by Microsoft Exce™ 2007 and later. Supports
// complex components by high compatibility, and provided streaming API for
// generating or reading data from a worksheet with huge amounts of data. This
// library needs Go version 1.10 or later.

package excelize

import (
	"bytes"
	"encoding/json"
	"encoding/xml"
	"errors"
	"fmt"
	"io"
	"io/ioutil"
	"log"
	"os"
	"path"
	"reflect"
	"regexp"
	"strconv"
	"strings"
	"unicode/utf8"

	"github.com/mohae/deepcopy"
)

// NewSheet provides function to create a new sheet by given worksheet name.
// When creating a new spreadsheet file, the default worksheet will be
// created. Returns the number of sheets in the workbook (file) after
// appending the new sheet.
func (f *File) NewSheet(name string) int {
	// Check if the worksheet already exists
	if f.GetSheetIndex(name) != -1 {
		return f.SheetCount
	}
	f.DeleteSheet(name)
	f.SheetCount++
	wb := f.workbookReader()
	sheetID := 0
	for _, v := range wb.Sheets.Sheet {
		if v.SheetID > sheetID {
			sheetID = v.SheetID
		}
	}
	sheetID++
	// Update docProps/app.xml
	f.setAppXML()
	// Update [Content_Types].xml
	f.setContentTypes("/xl/worksheets/sheet"+strconv.Itoa(sheetID)+".xml", ContentTypeSpreadSheetMLWorksheet)
	// Create new sheet /xl/worksheets/sheet%d.xml
	f.setSheet(sheetID, name)
	// Update xl/_rels/workbook.xml.rels
	rID := f.addRels("xl/_rels/workbook.xml.rels", SourceRelationshipWorkSheet, fmt.Sprintf("worksheets/sheet%d.xml", sheetID), "")
	// Update xl/workbook.xml
	f.setWorkbook(name, sheetID, rID)
	return f.GetSheetIndex(name)
}

// contentTypesReader provides a function to get the pointer to the
// [Content_Types].xml structure after deserialization.
func (f *File) contentTypesReader() *xlsxTypes {
	var err error

	if f.ContentTypes == nil {
		f.ContentTypes = new(xlsxTypes)
		if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML("[Content_Types].xml")))).
			Decode(f.ContentTypes); err != nil && err != io.EOF {
			log.Printf("xml decode error: %s", err)
		}
	}

	return f.ContentTypes
}

// contentTypesWriter provides a function to save [Content_Types].xml after
// serialize structure.
func (f *File) contentTypesWriter() {
	if f.ContentTypes != nil {
		output, _ := xml.Marshal(f.ContentTypes)
		f.saveFileList("[Content_Types].xml", output)
	}
}

// workbookReader provides a function to get the pointer to the xl/workbook.xml
// structure after deserialization.
func (f *File) workbookReader() *xlsxWorkbook {
	var err error
	if f.WorkBook == nil {
		f.WorkBook = new(xlsxWorkbook)
		if _, ok := f.xmlAttr["xl/workbook.xml"]; !ok {
			d := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML("xl/workbook.xml"))))
			f.xmlAttr["xl/workbook.xml"] = append(f.xmlAttr["xl/workbook.xml"], getRootElement(d)...)
			f.addNameSpaces("xl/workbook.xml", SourceRelationship)
		}
		if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML("xl/workbook.xml")))).
			Decode(f.WorkBook); err != nil && err != io.EOF {
			log.Printf("xml decode error: %s", err)
		}
	}
	return f.WorkBook
}

// workBookWriter provides a function to save xl/workbook.xml after serialize
// structure.
func (f *File) workBookWriter() {
	if f.WorkBook != nil {
		output, _ := xml.Marshal(f.WorkBook)
		f.saveFileList("xl/workbook.xml", replaceRelationshipsBytes(f.replaceNameSpaceBytes("xl/workbook.xml", output)))
	}
}

// workSheetWriter provides a function to save xl/worksheets/sheet%d.xml after
// serialize structure.
func (f *File) workSheetWriter() {
	for p, sheet := range f.Sheet {
		if sheet != nil {
			for k, v := range sheet.SheetData.Row {
				f.Sheet[p].SheetData.Row[k].C = trimCell(v.C)
			}
			output, _ := xml.Marshal(sheet)
			f.saveFileList(p, replaceRelationshipsBytes(f.replaceNameSpaceBytes(p, output)))
			ok := f.checked[p]
			if ok {
				delete(f.Sheet, p)
				f.checked[p] = false
			}
		}
	}
}

// trimCell provides a function to trim blank cells which created by fillColumns.
func trimCell(column []xlsxC) []xlsxC {
	rowFull := true
	for i := range column {
		rowFull = column[i].hasValue() && rowFull
	}
	if rowFull {
		return column
	}
	col := make([]xlsxC, len(column))
	i := 0
	for _, c := range column {
		if c.hasValue() {
			col[i] = c
			i++
		}
	}
	return col[0:i]
}

// setContentTypes provides a function to read and update property of contents
// type of the spreadsheet.
func (f *File) setContentTypes(partName, contentType string) {
	content := f.contentTypesReader()
	content.Overrides = append(content.Overrides, xlsxOverride{
		PartName:    partName,
		ContentType: contentType,
	})
}

// setSheet provides a function to update sheet property by given index.
func (f *File) setSheet(index int, name string) {
	xlsx := xlsxWorksheet{
		Dimension: &xlsxDimension{Ref: "A1"},
		SheetViews: &xlsxSheetViews{
			SheetView: []xlsxSheetView{{WorkbookViewID: 0}},
		},
	}
	path := "xl/worksheets/sheet" + strconv.Itoa(index) + ".xml"
	f.sheetMap[trimSheetName(name)] = path
	f.Sheet[path] = &xlsx
	f.xmlAttr[path] = append(f.xmlAttr[path], NameSpaceSpreadSheet)
}

// setWorkbook update workbook property of the spreadsheet. Maximum 31
// characters are allowed in sheet title.
func (f *File) setWorkbook(name string, sheetID, rid int) {
	content := f.workbookReader()
	content.Sheets.Sheet = append(content.Sheets.Sheet, xlsxSheet{
		Name:    trimSheetName(name),
		SheetID: sheetID,
		ID:      "rId" + strconv.Itoa(rid),
	})
}

// relsWriter provides a function to save relationships after
// serialize structure.
func (f *File) relsWriter() {
	for path, rel := range f.Relationships {
		if rel != nil {
			output, _ := xml.Marshal(rel)
			if strings.HasPrefix(path, "xl/worksheets/sheet/rels/sheet") {
				output = f.replaceNameSpaceBytes(path, output)
			}
			f.saveFileList(path, replaceRelationshipsBytes(output))
		}
	}
}

// setAppXML update docProps/app.xml file of XML.
func (f *File) setAppXML() {
	f.saveFileList("docProps/app.xml", []byte(templateDocpropsApp))
}

// replaceRelationshipsBytes; Some tools that read spreadsheet files have very
// strict requirements about the structure of the input XML. This function is
// a horrible hack to fix that after the XML marshalling is completed.
func replaceRelationshipsBytes(content []byte) []byte {
	oldXmlns := stringToBytes(`xmlns:relationships="http://schemas.openxmlformats.org/officeDocument/2006/relationships" relationships`)
	newXmlns := stringToBytes("r")
	return bytesReplace(content, oldXmlns, newXmlns, -1)
}

// SetActiveSheet provides a function to set the default active sheet of the
// workbook by a given index. Note that the active index is different from the
// ID returned by function GetSheetMap(). It should be greater or equal to 0
// and less than the total worksheet numbers.
func (f *File) SetActiveSheet(index int) {
	if index < 0 {
		index = 0
	}
	wb := f.workbookReader()
	for activeTab := range wb.Sheets.Sheet {
		if activeTab == index {
			if wb.BookViews == nil {
				wb.BookViews = &xlsxBookViews{}
			}
			if len(wb.BookViews.WorkBookView) > 0 {
				wb.BookViews.WorkBookView[0].ActiveTab = activeTab
			} else {
				wb.BookViews.WorkBookView = append(wb.BookViews.WorkBookView, xlsxWorkBookView{
					ActiveTab: activeTab,
				})
			}
		}
	}
	for idx, name := range f.GetSheetList() {
		xlsx, err := f.workSheetReader(name)
		if err != nil {
			// Chartsheet or dialogsheet
			return
		}
		if xlsx.SheetViews == nil {
			xlsx.SheetViews = &xlsxSheetViews{
				SheetView: []xlsxSheetView{{WorkbookViewID: 0}},
			}
		}
		if len(xlsx.SheetViews.SheetView) > 0 {
			xlsx.SheetViews.SheetView[0].TabSelected = false
		}
		if index == idx {
			if len(xlsx.SheetViews.SheetView) > 0 {
				xlsx.SheetViews.SheetView[0].TabSelected = true
			} else {
				xlsx.SheetViews.SheetView = append(xlsx.SheetViews.SheetView, xlsxSheetView{
					TabSelected: true,
				})
			}
		}
	}
}

// GetActiveSheetIndex provides a function to get active sheet index of the
// spreadsheet. If not found the active sheet will be return integer 0.
func (f *File) GetActiveSheetIndex() (index int) {
	var sheetID = f.getActiveSheetID()
	wb := f.workbookReader()
	if wb != nil {
		for idx, sheet := range wb.Sheets.Sheet {
			if sheet.SheetID == sheetID {
				index = idx
			}
		}
	}
	return
}

// getActiveSheetID provides a function to get active sheet index of the
// spreadsheet. If not found the active sheet will be return integer 0.
func (f *File) getActiveSheetID() int {
	wb := f.workbookReader()
	if wb != nil {
		if wb.BookViews != nil && len(wb.BookViews.WorkBookView) > 0 {
			activeTab := wb.BookViews.WorkBookView[0].ActiveTab
			if len(wb.Sheets.Sheet) > activeTab && wb.Sheets.Sheet[activeTab].SheetID != 0 {
				return wb.Sheets.Sheet[activeTab].SheetID
			}
		}
		if len(wb.Sheets.Sheet) >= 1 {
			return wb.Sheets.Sheet[0].SheetID
		}
	}
	return 0
}

// SetSheetName provides a function to set the worksheet name by given old and
// new worksheet names. Maximum 31 characters are allowed in sheet title and
// this function only changes the name of the sheet and will not update the
// sheet name in the formula or reference associated with the cell. So there
// may be problem formula error or reference missing.
func (f *File) SetSheetName(oldName, newName string) {
	oldName = trimSheetName(oldName)
	newName = trimSheetName(newName)
	content := f.workbookReader()
	for k, v := range content.Sheets.Sheet {
		if v.Name == oldName {
			content.Sheets.Sheet[k].Name = newName
			f.sheetMap[newName] = f.sheetMap[oldName]
			delete(f.sheetMap, oldName)
		}
	}
}

// getSheetNameByID provides a function to get worksheet name of the
// spreadsheet by given worksheet ID. If given sheet ID is invalid, will
// return an empty string.
func (f *File) getSheetNameByID(ID int) string {
	wb := f.workbookReader()
	if wb == nil || ID < 1 {
		return ""
	}
	for _, sheet := range wb.Sheets.Sheet {
		if ID == sheet.SheetID {
			return sheet.Name
		}
	}
	return ""
}

// GetSheetName provides a function to get the sheet name of the workbook by
// the given sheet index. If the given sheet index is invalid, it will return
// an empty string.
func (f *File) GetSheetName(index int) (name string) {
	for idx, sheet := range f.GetSheetList() {
		if idx == index {
			name = sheet
		}
	}
	return
}

// getSheetID provides a function to get worksheet ID of the spreadsheet by
// given sheet name. If given worksheet name is invalid, will return an
// integer type value -1.
func (f *File) getSheetID(name string) int {
	var ID = -1
	for sheetID, sheet := range f.GetSheetMap() {
		if sheet == trimSheetName(name) {
			ID = sheetID
		}
	}
	return ID
}

// GetSheetIndex provides a function to get a sheet index of the workbook by
// the given sheet name. If the given sheet name is invalid, it will return an
// integer type value 0.
func (f *File) GetSheetIndex(name string) int {
	var idx = -1
	for index, sheet := range f.GetSheetList() {
		if sheet == trimSheetName(name) {
			idx = index
		}
	}
	return idx
}

// GetSheetMap provides a function to get worksheets, chart sheets, dialog
// sheets ID and name map of the workbook. For example:
//
//    f, err := excelize.OpenFile("Book1.xlsx")
//    if err != nil {
//        return
//    }
//    for index, name := range f.GetSheetMap() {
//        fmt.Println(index, name)
//    }
//
func (f *File) GetSheetMap() map[int]string {
	wb := f.workbookReader()
	sheetMap := map[int]string{}
	if wb != nil {
		for _, sheet := range wb.Sheets.Sheet {
			sheetMap[sheet.SheetID] = sheet.Name
		}
	}
	return sheetMap
}

// GetSheetList provides a function to get worksheets, chart sheets, and
// dialog sheets name list of the workbook.
func (f *File) GetSheetList() (list []string) {
	wb := f.workbookReader()
	if wb != nil {
		for _, sheet := range wb.Sheets.Sheet {
			list = append(list, sheet.Name)
		}
	}
	return
}

// getSheetMap provides a function to get worksheet name and XML file path map
// of XLSX.
func (f *File) getSheetMap() map[string]string {
	content := f.workbookReader()
	rels := f.relsReader("xl/_rels/workbook.xml.rels")
	maps := map[string]string{}
	for _, v := range content.Sheets.Sheet {
		for _, rel := range rels.Relationships {
			if rel.ID == v.ID {
				// Construct a target XML as xl/worksheets/sheet%d by split path, compatible with different types of relative paths in workbook.xml.rels, for example: worksheets/sheet%d.xml and /xl/worksheets/sheet%d.xml
				pathInfo := strings.Split(rel.Target, "/")
				pathInfoLen := len(pathInfo)
				if pathInfoLen > 1 {
					maps[v.Name] = fmt.Sprintf("xl/%s", strings.Join(pathInfo[pathInfoLen-2:], "/"))
				}
			}
		}
	}
	return maps
}

// SetSheetBackground provides a function to set background picture by given
// worksheet name and file path.
func (f *File) SetSheetBackground(sheet, picture string) error {
	var err error
	// Check picture exists first.
	if _, err = os.Stat(picture); os.IsNotExist(err) {
		return err
	}
	ext, ok := supportImageTypes[path.Ext(picture)]
	if !ok {
		return errors.New("unsupported image extension")
	}
	file, _ := ioutil.ReadFile(picture)
	name := f.addMedia(file, ext)
	sheetRels := "xl/worksheets/_rels/" + strings.TrimPrefix(f.sheetMap[trimSheetName(sheet)], "xl/worksheets/") + ".rels"
	rID := f.addRels(sheetRels, SourceRelationshipImage, strings.Replace(name, "xl", "..", 1), "")
	f.addSheetPicture(sheet, rID)
	f.addSheetNameSpace(sheet, SourceRelationship)
	f.setContentTypePartImageExtensions()
	return err
}

// DeleteSheet provides a function to delete worksheet in a workbook by given
// worksheet name. Use this method with caution, which will affect changes in
// references such as formulas, charts, and so on. If there is any referenced
// value of the deleted worksheet, it will cause a file error when you open it.
// This function will be invalid when only the one worksheet is left.
func (f *File) DeleteSheet(name string) {
	if f.SheetCount == 1 || f.GetSheetIndex(name) == -1 {
		return
	}
	sheetName := trimSheetName(name)
	wb := f.workbookReader()
	wbRels := f.relsReader("xl/_rels/workbook.xml.rels")
	for idx, sheet := range wb.Sheets.Sheet {
		if sheet.Name == sheetName {
			wb.Sheets.Sheet = append(wb.Sheets.Sheet[:idx], wb.Sheets.Sheet[idx+1:]...)
			var sheetXML, rels string
			if wbRels != nil {
				for _, rel := range wbRels.Relationships {
					if rel.ID == sheet.ID {
						sheetXML = fmt.Sprintf("xl/%s", rel.Target)
						pathInfo := strings.Split(rel.Target, "/")
						if len(pathInfo) == 2 {
							rels = fmt.Sprintf("xl/%s/_rels/%s.rels", pathInfo[0], pathInfo[1])
						}
					}
				}
			}
			target := f.deleteSheetFromWorkbookRels(sheet.ID)
			f.deleteSheetFromContentTypes(target)
			f.deleteCalcChain(sheet.SheetID, "") // Delete CalcChain
			delete(f.sheetMap, sheetName)
			delete(f.XLSX, sheetXML)
			delete(f.XLSX, rels)
			delete(f.Relationships, rels)
			delete(f.Sheet, sheetXML)
			delete(f.xmlAttr, sheetXML)
			f.SheetCount--
		}
	}
	if wb.BookViews != nil {
		for idx, bookView := range wb.BookViews.WorkBookView {
			if bookView.ActiveTab >= f.SheetCount {
				wb.BookViews.WorkBookView[idx].ActiveTab--
			}
		}
	}
	f.SetActiveSheet(len(f.GetSheetMap()))
}

// deleteSheetFromWorkbookRels provides a function to remove worksheet
// relationships by given relationships ID in the file
// xl/_rels/workbook.xml.rels.
func (f *File) deleteSheetFromWorkbookRels(rID string) string {
	content := f.relsReader("xl/_rels/workbook.xml.rels")
	for k, v := range content.Relationships {
		if v.ID == rID {
			content.Relationships = append(content.Relationships[:k], content.Relationships[k+1:]...)
			return v.Target
		}
	}
	return ""
}

// deleteSheetFromContentTypes provides a function to remove worksheet
// relationships by given target name in the file [Content_Types].xml.
func (f *File) deleteSheetFromContentTypes(target string) {
	content := f.contentTypesReader()
	for k, v := range content.Overrides {
		if v.PartName == "/xl/"+target {
			content.Overrides = append(content.Overrides[:k], content.Overrides[k+1:]...)
		}
	}
}

// CopySheet provides a function to duplicate a worksheet by gave source and
// target worksheet index. Note that currently doesn't support duplicate
// workbooks that contain tables, charts or pictures. For Example:
//
//    // Sheet1 already exists...
//    index := f.NewSheet("Sheet2")
//    err := f.CopySheet(1, index)
//    return err
//
func (f *File) CopySheet(from, to int) error {
	if from < 0 || to < 0 || from == to || f.GetSheetName(from) == "" || f.GetSheetName(to) == "" {
		return errors.New("invalid worksheet index")
	}
	return f.copySheet(from, to)
}

// copySheet provides a function to duplicate a worksheet by gave source and
// target worksheet name.
func (f *File) copySheet(from, to int) error {
	fromSheet := f.GetSheetName(from)
	sheet, err := f.workSheetReader(fromSheet)
	if err != nil {
		return err
	}
	worksheet := deepcopy.Copy(sheet).(*xlsxWorksheet)
	toSheetID := strconv.Itoa(f.getSheetID(f.GetSheetName(to)))
	path := "xl/worksheets/sheet" + toSheetID + ".xml"
	if len(worksheet.SheetViews.SheetView) > 0 {
		worksheet.SheetViews.SheetView[0].TabSelected = false
	}
	worksheet.Drawing = nil
	worksheet.TableParts = nil
	worksheet.PageSetUp = nil
	f.Sheet[path] = worksheet
	toRels := "xl/worksheets/_rels/sheet" + toSheetID + ".xml.rels"
	fromRels := "xl/worksheets/_rels/sheet" + strconv.Itoa(f.getSheetID(fromSheet)) + ".xml.rels"
	_, ok := f.XLSX[fromRels]
	if ok {
		f.XLSX[toRels] = f.XLSX[fromRels]
	}
	fromSheetXMLPath, _ := f.sheetMap[trimSheetName(fromSheet)]
	fromSheetAttr, _ := f.xmlAttr[fromSheetXMLPath]
	f.xmlAttr[path] = fromSheetAttr
	return err
}

// SetSheetVisible provides a function to set worksheet visible by given worksheet
// name. A workbook must contain at least one visible worksheet. If the given
// worksheet has been activated, this setting will be invalidated. Sheet state
// values as defined by https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.sheetstatevalues
//
//    visible
//    hidden
//    veryHidden
//
// For example, hide Sheet1:
//
//    err := f.SetSheetVisible("Sheet1", false)
//
func (f *File) SetSheetVisible(name string, visible bool) error {
	name = trimSheetName(name)
	content := f.workbookReader()
	if visible {
		for k, v := range content.Sheets.Sheet {
			if v.Name == name {
				content.Sheets.Sheet[k].State = ""
			}
		}
		return nil
	}
	count := 0
	for _, v := range content.Sheets.Sheet {
		if v.State != "hidden" {
			count++
		}
	}
	for k, v := range content.Sheets.Sheet {
		xlsx, err := f.workSheetReader(v.Name)
		if err != nil {
			return err
		}
		tabSelected := false
		if len(xlsx.SheetViews.SheetView) > 0 {
			tabSelected = xlsx.SheetViews.SheetView[0].TabSelected
		}
		if v.Name == name && count > 1 && !tabSelected {
			content.Sheets.Sheet[k].State = "hidden"
		}
	}
	return nil
}

// parseFormatPanesSet provides a function to parse the panes settings.
func parseFormatPanesSet(formatSet string) (*formatPanes, error) {
	format := formatPanes{}
	err := json.Unmarshal([]byte(formatSet), &format)
	return &format, err
}

// SetPanes provides a function to create and remove freeze panes and split panes
// by given worksheet name 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
//                                    |
//    topRight (Top Right Pane)       | Top right pane, when both vertical and horizontal
//                                    | 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!K16:
//
//    f.SetPanes("Sheet1", `{"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"}]}`)
//
// An example of how to freeze rows 1 to 9 in the Sheet1 and set the active cell
// ranges on Sheet1!A11:XFD11:
//
//    f.SetPanes("Sheet1", `{"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"}]}`)
//
// An example of how to create split panes in the Sheet1 and set the active cell
// on Sheet1!J60:
//
//    f.SetPanes("Sheet1", `{"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"}]}`)
//
// An example of how to unfreeze and remove all panes on Sheet1:
//
//    f.SetPanes("Sheet1", `{"freeze":false,"split":false}`)
//
func (f *File) SetPanes(sheet, panes string) error {
	fs, _ := parseFormatPanesSet(panes)
	xlsx, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	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
	return err
}

// GetSheetVisible provides a function to get worksheet visible by given worksheet
// name. For example, get visible state of Sheet1:
//
//    f.GetSheetVisible("Sheet1")
//
func (f *File) GetSheetVisible(name string) bool {
	content := f.workbookReader()
	visible := false
	for k, v := range content.Sheets.Sheet {
		if v.Name == trimSheetName(name) {
			if content.Sheets.Sheet[k].State == "" || content.Sheets.Sheet[k].State == "visible" {
				visible = true
			}
		}
	}
	return visible
}

// SearchSheet provides a function to get coordinates by given worksheet name,
// cell value, and regular expression. The function doesn't support searching
// on the calculated result, formatted numbers and conditional lookup
// currently. If it is a merged cell, it will return the coordinates of the
// upper left corner of the merged area.
//
// An example of search the coordinates of the value of "100" on Sheet1:
//
//    result, err := f.SearchSheet("Sheet1", "100")
//
// An example of search the coordinates where the numerical value in the range
// of "0-9" of Sheet1 is described:
//
//    result, err := f.SearchSheet("Sheet1", "[0-9]", true)
//
func (f *File) SearchSheet(sheet, value string, reg ...bool) ([]string, error) {
	var (
		regSearch bool
		result    []string
	)
	for _, r := range reg {
		regSearch = r
	}
	name, ok := f.sheetMap[trimSheetName(sheet)]
	if !ok {
		return result, ErrSheetNotExist{sheet}
	}
	if f.Sheet[name] != nil {
		// flush data
		output, _ := xml.Marshal(f.Sheet[name])
		f.saveFileList(name, f.replaceNameSpaceBytes(name, output))
	}
	return f.searchSheet(name, value, regSearch)
}

// searchSheet provides a function to get coordinates by given worksheet name,
// cell value, and regular expression.
func (f *File) searchSheet(name, value string, regSearch bool) (result []string, err error) {
	var (
		cellName, inElement string
		cellCol, row        int
		d                   *xlsxSST
	)

	d = f.sharedStringsReader()
	decoder := f.xmlNewDecoder(bytes.NewReader(f.readXML(name)))
	for {
		var token xml.Token
		token, err = decoder.Token()
		if err != nil || token == nil {
			if err == io.EOF {
				err = nil
			}
			break
		}
		switch startElement := token.(type) {
		case xml.StartElement:
			inElement = startElement.Name.Local
			if inElement == "row" {
				row, err = attrValToInt("r", startElement.Attr)
				if err != nil {
					return
				}
			}
			if inElement == "c" {
				colCell := xlsxC{}
				_ = decoder.DecodeElement(&colCell, &startElement)
				val, _ := colCell.getValueFrom(f, d)
				if regSearch {
					regex := regexp.MustCompile(value)
					if !regex.MatchString(val) {
						continue
					}
				} else {
					if val != value {
						continue
					}
				}
				cellCol, _, err = CellNameToCoordinates(colCell.R)
				if err != nil {
					return result, err
				}
				cellName, err = CoordinatesToCellName(cellCol, row)
				if err != nil {
					return result, err
				}
				result = append(result, cellName)
			}
		default:
		}
	}
	return
}

// attrValToInt provides a function to convert the local names to an integer
// by given XML attributes and specified names.
func attrValToInt(name string, attrs []xml.Attr) (val int, err error) {
	for _, attr := range attrs {
		if attr.Name.Local == name {
			val, err = strconv.Atoi(attr.Value)
			if err != nil {
				return
			}
		}
	}
	return
}

// SetHeaderFooter provides a function to set headers and footers by given
// worksheet name and the control characters.
//
// Headers and footers are specified using the following settings fields:
//
//     Fields           | Description
//    ------------------+-----------------------------------------------------------
//     AlignWithMargins | Align header footer margins with page margins
//     DifferentFirst   | Different first-page header and footer indicator
//     DifferentOddEven | Different odd and even page headers and footers indicator
//     ScaleWithDoc     | Scale header and footer with document scaling
//     OddFooter        | Odd Page Footer
//     OddHeader        | Odd Header
//     EvenFooter       | Even Page Footer
//     EvenHeader       | Even Page Header
//     FirstFooter      | First Page Footer
//     FirstHeader      | First Page Header
//
// The following formatting codes can be used in 6 string type fields:
// OddHeader, OddFooter, EvenHeader, EvenFooter, FirstFooter, FirstHeader
//
//     Formatting Code        | Description
//    ------------------------+-------------------------------------------------------------------------
//     &&                     | The character "&"
//                            |
//     &font-size             | Size of the text font, where font-size is a decimal font size in points
//                            |
//     &"font name,font type" | A text font-name string, font name, and a text font-type string,
//                            | font type
//                            |
//     &"-,Regular"           | Regular text format. Toggles bold and italic modes to off
//                            |
//     &A                     | Current worksheet's tab name
//                            |
//     &B or &"-,Bold"        | Bold text format, from off to on, or vice versa. The default mode is off
//                            |
//     &D                     | Current date
//                            |
//     &C                     | Center section
//                            |
//     &E                     | Double-underline text format
//                            |
//     &F                     | Current workbook's file name
//                            |
//     &G                     | Drawing object as background
//                            |
//     &H                     | Shadow text format
//                            |
//     &I or &"-,Italic"      | Italic text format
//                            |
//     &K                     | Text font color
//                            |
//                            | An RGB Color is specified as RRGGBB
//                            |
//                            | A Theme Color is specified as TTSNNN where TT is the theme color Id,
//                            | S is either "+" or "-" of the tint/shade value, and NNN is the
//                            | tint/shade value
//                            |
//     &L                     | Left section
//                            |
//     &N                     | Total number of pages
//                            |
//     &O                     | Outline text format
//                            |
//     &P[[+|-]n]             | Without the optional suffix, the current page number in decimal
//                            |
//     &R                     | Right section
//                            |
//     &S                     | Strikethrough text format
//                            |
//     &T                     | Current time
//                            |
//     &U                     | Single-underline text format. If double-underline mode is on, the next
//                            | occurrence in a section specifier toggles double-underline mode to off;
//                            | otherwise, it toggles single-underline mode, from off to on, or vice
//                            | versa. The default mode is off
//                            |
//     &X                     | Superscript text format
//                            |
//     &Y                     | Subscript text format
//                            |
//     &Z                     | Current workbook's file path
//
// For example:
//
//    err := f.SetHeaderFooter("Sheet1", &excelize.FormatHeaderFooter{
//        DifferentFirst:   true,
//        DifferentOddEven: true,
//        OddHeader:        "&R&P",
//        OddFooter:        "&C&F",
//        EvenHeader:       "&L&P",
//        EvenFooter:       "&L&D&R&T",
//        FirstHeader:      `&CCenter &"-,Bold"Bold&"-,Regular"HeaderU+000A&D`,
//    })
//
// This example shows:
//
// - The first page has its own header and footer
//
// - Odd and even-numbered pages have different headers and footers
//
// - Current page number in the right section of odd-page headers
//
// - Current workbook's file name in the center section of odd-page footers
//
// - Current page number in the left section of even-page headers
//
// - Current date in the left section and the current time in the right section
// of even-page footers
//
// - The text "Center Bold Header" on the first line of the center section of
// the first page, and the date on the second line of the center section of
// that same page
//
// - No footer on the first page
//
func (f *File) SetHeaderFooter(sheet string, settings *FormatHeaderFooter) error {
	xlsx, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	if settings == nil {
		xlsx.HeaderFooter = nil
		return err
	}

	v := reflect.ValueOf(*settings)
	// Check 6 string type fields: OddHeader, OddFooter, EvenHeader, EvenFooter,
	// FirstFooter, FirstHeader
	for i := 4; i < v.NumField()-1; i++ {
		if v.Field(i).Len() >= 255 {
			return fmt.Errorf("field %s must be less than 255 characters", v.Type().Field(i).Name)
		}
	}
	xlsx.HeaderFooter = &xlsxHeaderFooter{
		AlignWithMargins: settings.AlignWithMargins,
		DifferentFirst:   settings.DifferentFirst,
		DifferentOddEven: settings.DifferentOddEven,
		ScaleWithDoc:     settings.ScaleWithDoc,
		OddHeader:        settings.OddHeader,
		OddFooter:        settings.OddFooter,
		EvenHeader:       settings.EvenHeader,
		EvenFooter:       settings.EvenFooter,
		FirstFooter:      settings.FirstFooter,
		FirstHeader:      settings.FirstHeader,
	}
	return err
}

// ProtectSheet provides a function to prevent other users from accidentally
// or deliberately changing, moving, or deleting data in a worksheet. For
// example, protect Sheet1 with protection settings:
//
//    err := f.ProtectSheet("Sheet1", &excelize.FormatSheetProtection{
//        Password:      "password",
//        EditScenarios: false,
//    })
//
func (f *File) ProtectSheet(sheet string, settings *FormatSheetProtection) error {
	xlsx, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	if settings == nil {
		settings = &FormatSheetProtection{
			EditObjects:       true,
			EditScenarios:     true,
			SelectLockedCells: true,
		}
	}
	xlsx.SheetProtection = &xlsxSheetProtection{
		AutoFilter:          settings.AutoFilter,
		DeleteColumns:       settings.DeleteColumns,
		DeleteRows:          settings.DeleteRows,
		FormatCells:         settings.FormatCells,
		FormatColumns:       settings.FormatColumns,
		FormatRows:          settings.FormatRows,
		InsertColumns:       settings.InsertColumns,
		InsertHyperlinks:    settings.InsertHyperlinks,
		InsertRows:          settings.InsertRows,
		Objects:             settings.EditObjects,
		PivotTables:         settings.PivotTables,
		Scenarios:           settings.EditScenarios,
		SelectLockedCells:   settings.SelectLockedCells,
		SelectUnlockedCells: settings.SelectUnlockedCells,
		Sheet:               true,
		Sort:                settings.Sort,
	}
	if settings.Password != "" {
		xlsx.SheetProtection.Password = genSheetPasswd(settings.Password)
	}
	return err
}

// UnprotectSheet provides a function to unprotect an Excel worksheet.
func (f *File) UnprotectSheet(sheet string) error {
	xlsx, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	xlsx.SheetProtection = nil
	return err
}

// trimSheetName provides a function to trim invaild characters by given worksheet
// name.
func trimSheetName(name string) string {
	if strings.ContainsAny(name, ":\\/?*[]") || utf8.RuneCountInString(name) > 31 {
		r := make([]rune, 0, 31)
		for _, v := range name {
			switch v {
			case 58, 92, 47, 63, 42, 91, 93: // replace :\/?*[]
				continue
			default:
				r = append(r, v)
			}
			if len(r) == 31 {
				break
			}
		}
		name = string(r)
	}
	return name
}

// PageLayoutOption is an option of a page layout of a worksheet. See
// SetPageLayout().
type PageLayoutOption interface {
	setPageLayout(layout *xlsxPageSetUp)
}

// PageLayoutOptionPtr is a writable PageLayoutOption. See GetPageLayout().
type PageLayoutOptionPtr interface {
	PageLayoutOption
	getPageLayout(layout *xlsxPageSetUp)
}

type (
	// PageLayoutOrientation defines the orientation of page layout for a
	// worksheet.
	PageLayoutOrientation string
	// PageLayoutPaperSize defines the paper size of the worksheet
	PageLayoutPaperSize int
	// FitToHeight specified number of vertical pages to fit on
	FitToHeight int
	// FitToWidth specified number of horizontal pages to fit on
	FitToWidth int
)

const (
	// OrientationPortrait indicates page layout orientation id portrait.
	OrientationPortrait = "portrait"
	// OrientationLandscape indicates page layout orientation id landscape.
	OrientationLandscape = "landscape"
)

// setPageLayout provides a method to set the orientation for the worksheet.
func (o PageLayoutOrientation) setPageLayout(ps *xlsxPageSetUp) {
	ps.Orientation = string(o)
}

// getPageLayout provides a method to get the orientation for the worksheet.
func (o *PageLayoutOrientation) getPageLayout(ps *xlsxPageSetUp) {
	// Excel default: portrait
	if ps == nil || ps.Orientation == "" {
		*o = OrientationPortrait
		return
	}
	*o = PageLayoutOrientation(ps.Orientation)
}

// setPageLayout provides a method to set the paper size for the worksheet.
func (p PageLayoutPaperSize) setPageLayout(ps *xlsxPageSetUp) {
	ps.PaperSize = int(p)
}

// getPageLayout provides a method to get the paper size for the worksheet.
func (p *PageLayoutPaperSize) getPageLayout(ps *xlsxPageSetUp) {
	// Excel default: 1
	if ps == nil || ps.PaperSize == 0 {
		*p = 1
		return
	}
	*p = PageLayoutPaperSize(ps.PaperSize)
}

// setPageLayout provides a method to set the fit to height for the worksheet.
func (p FitToHeight) setPageLayout(ps *xlsxPageSetUp) {
	if int(p) > 0 {
		ps.FitToHeight = int(p)
	}
}

// getPageLayout provides a method to get the fit to height for the worksheet.
func (p *FitToHeight) getPageLayout(ps *xlsxPageSetUp) {
	if ps == nil || ps.FitToHeight == 0 {
		*p = 1
		return
	}
	*p = FitToHeight(ps.FitToHeight)
}

// setPageLayout provides a method to set the fit to width for the worksheet.
func (p FitToWidth) setPageLayout(ps *xlsxPageSetUp) {
	if int(p) > 0 {
		ps.FitToWidth = int(p)
	}
}

// getPageLayout provides a method to get the fit to width for the worksheet.
func (p *FitToWidth) getPageLayout(ps *xlsxPageSetUp) {
	if ps == nil || ps.FitToWidth == 0 {
		*p = 1
		return
	}
	*p = FitToWidth(ps.FitToWidth)
}

// SetPageLayout provides a function to sets worksheet page layout.
//
// Available options:
//   PageLayoutOrientation(string)
//   PageLayoutPaperSize(int)
//
// The following shows the paper size sorted by excelize index number:
//
//     Index | Paper Size
//    -------+-----------------------------------------------
//       1   | Letter paper (8.5 in. by 11 in.)
//       2   | Letter small paper (8.5 in. by 11 in.)
//       3   | Tabloid paper (11 in. by 17 in.)
//       4   | Ledger paper (17 in. by 11 in.)
//       5   | Legal paper (8.5 in. by 14 in.)
//       6   | Statement paper (5.5 in. by 8.5 in.)
//       7   | Executive paper (7.25 in. by 10.5 in.)
//       8   | A3 paper (297 mm by 420 mm)
//       9   | A4 paper (210 mm by 297 mm)
//       10  | A4 small paper (210 mm by 297 mm)
//       11  | A5 paper (148 mm by 210 mm)
//       12  | B4 paper (250 mm by 353 mm)
//       13  | B5 paper (176 mm by 250 mm)
//       14  | Folio paper (8.5 in. by 13 in.)
//       15  | Quarto paper (215 mm by 275 mm)
//       16  | Standard paper (10 in. by 14 in.)
//       17  | Standard paper (11 in. by 17 in.)
//       18  | Note paper (8.5 in. by 11 in.)
//       19  | #9 envelope (3.875 in. by 8.875 in.)
//       20  | #10 envelope (4.125 in. by 9.5 in.)
//       21  | #11 envelope (4.5 in. by 10.375 in.)
//       22  | #12 envelope (4.75 in. by 11 in.)
//       23  | #14 envelope (5 in. by 11.5 in.)
//       24  | C paper (17 in. by 22 in.)
//       25  | D paper (22 in. by 34 in.)
//       26  | E paper (34 in. by 44 in.)
//       27  | DL envelope (110 mm by 220 mm)
//       28  | C5 envelope (162 mm by 229 mm)
//       29  | C3 envelope (324 mm by 458 mm)
//       30  | C4 envelope (229 mm by 324 mm)
//       31  | C6 envelope (114 mm by 162 mm)
//       32  | C65 envelope (114 mm by 229 mm)
//       33  | B4 envelope (250 mm by 353 mm)
//       34  | B5 envelope (176 mm by 250 mm)
//       35  | B6 envelope (176 mm by 125 mm)
//       36  | Italy envelope (110 mm by 230 mm)
//       37  | Monarch envelope (3.875 in. by 7.5 in.).
//       38  | 6 3/4 envelope (3.625 in. by 6.5 in.)
//       39  | US standard fanfold (14.875 in. by 11 in.)
//       40  | German standard fanfold (8.5 in. by 12 in.)
//       41  | German legal fanfold (8.5 in. by 13 in.)
//       42  | ISO B4 (250 mm by 353 mm)
//       43  | Japanese postcard (100 mm by 148 mm)
//       44  | Standard paper (9 in. by 11 in.)
//       45  | Standard paper (10 in. by 11 in.)
//       46  | Standard paper (15 in. by 11 in.)
//       47  | Invite envelope (220 mm by 220 mm)
//       50  | Letter extra paper (9.275 in. by 12 in.)
//       51  | Legal extra paper (9.275 in. by 15 in.)
//       52  | Tabloid extra paper (11.69 in. by 18 in.)
//       53  | A4 extra paper (236 mm by 322 mm)
//       54  | Letter transverse paper (8.275 in. by 11 in.)
//       55  | A4 transverse paper (210 mm by 297 mm)
//       56  | Letter extra transverse paper (9.275 in. by 12 in.)
//       57  | SuperA/SuperA/A4 paper (227 mm by 356 mm)
//       58  | SuperB/SuperB/A3 paper (305 mm by 487 mm)
//       59  | Letter plus paper (8.5 in. by 12.69 in.)
//       60  | A4 plus paper (210 mm by 330 mm)
//       61  | A5 transverse paper (148 mm by 210 mm)
//       62  | JIS B5 transverse paper (182 mm by 257 mm)
//       63  | A3 extra paper (322 mm by 445 mm)
//       64  | A5 extra paper (174 mm by 235 mm)
//       65  | ISO B5 extra paper (201 mm by 276 mm)
//       66  | A2 paper (420 mm by 594 mm)
//       67  | A3 transverse paper (297 mm by 420 mm)
//       68  | A3 extra transverse paper (322 mm by 445 mm)
//       69  | Japanese Double Postcard (200 mm x 148 mm)
//       70  | A6 (105 mm x 148 mm)
//       71  | Japanese Envelope Kaku #2
//       72  | Japanese Envelope Kaku #3
//       73  | Japanese Envelope Chou #3
//       74  | Japanese Envelope Chou #4
//       75  | Letter Rotated (11in x 8 1/2 11 in)
//       76  | A3 Rotated (420 mm x 297 mm)
//       77  | A4 Rotated (297 mm x 210 mm)
//       78  | A5 Rotated (210 mm x 148 mm)
//       79  | B4 (JIS) Rotated (364 mm x 257 mm)
//       80  | B5 (JIS) Rotated (257 mm x 182 mm)
//       81  | Japanese Postcard Rotated (148 mm x 100 mm)
//       82  | Double Japanese Postcard Rotated (148 mm x 200 mm)
//       83  | A6 Rotated (148 mm x 105 mm)
//       84  | Japanese Envelope Kaku #2 Rotated
//       85  | Japanese Envelope Kaku #3 Rotated
//       86  | Japanese Envelope Chou #3 Rotated
//       87  | Japanese Envelope Chou #4 Rotated
//       88  | B6 (JIS) (128 mm x 182 mm)
//       89  | B6 (JIS) Rotated (182 mm x 128 mm)
//       90  | (12 in x 11 in)
//       91  | Japanese Envelope You #4
//       92  | Japanese Envelope You #4 Rotated
//       93  | PRC 16K (146 mm x 215 mm)
//       94  | PRC 32K (97 mm x 151 mm)
//       95  | PRC 32K(Big) (97 mm x 151 mm)
//       96  | PRC Envelope #1 (102 mm x 165 mm)
//       97  | PRC Envelope #2 (102 mm x 176 mm)
//       98  | PRC Envelope #3 (125 mm x 176 mm)
//       99  | PRC Envelope #4 (110 mm x 208 mm)
//       100 | PRC Envelope #5 (110 mm x 220 mm)
//       101 | PRC Envelope #6 (120 mm x 230 mm)
//       102 | PRC Envelope #7 (160 mm x 230 mm)
//       103 | PRC Envelope #8 (120 mm x 309 mm)
//       104 | PRC Envelope #9 (229 mm x 324 mm)
//       105 | PRC Envelope #10 (324 mm x 458 mm)
//       106 | PRC 16K Rotated
//       107 | PRC 32K Rotated
//       108 | PRC 32K(Big) Rotated
//       109 | PRC Envelope #1 Rotated (165 mm x 102 mm)
//       110 | PRC Envelope #2 Rotated (176 mm x 102 mm)
//       111 | PRC Envelope #3 Rotated (176 mm x 125 mm)
//       112 | PRC Envelope #4 Rotated (208 mm x 110 mm)
//       113 | PRC Envelope #5 Rotated (220 mm x 110 mm)
//       114 | PRC Envelope #6 Rotated (230 mm x 120 mm)
//       115 | PRC Envelope #7 Rotated (230 mm x 160 mm)
//       116 | PRC Envelope #8 Rotated (309 mm x 120 mm)
//       117 | PRC Envelope #9 Rotated (324 mm x 229 mm)
//       118 | PRC Envelope #10 Rotated (458 mm x 324 mm)
//
func (f *File) SetPageLayout(sheet string, opts ...PageLayoutOption) error {
	s, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	ps := s.PageSetUp
	if ps == nil {
		ps = new(xlsxPageSetUp)
		s.PageSetUp = ps
	}

	for _, opt := range opts {
		opt.setPageLayout(ps)
	}
	return err
}

// GetPageLayout provides a function to gets worksheet page layout.
//
// Available options:
//   PageLayoutOrientation(string)
//   PageLayoutPaperSize(int)
//   FitToHeight(int)
//   FitToWidth(int)
func (f *File) GetPageLayout(sheet string, opts ...PageLayoutOptionPtr) error {
	s, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	ps := s.PageSetUp

	for _, opt := range opts {
		opt.getPageLayout(ps)
	}
	return err
}

// SetDefinedName provides a function to set the defined names of the workbook
// or worksheet. If not specified scope, the default scope is workbook.
// For example:
//
//    f.SetDefinedName(&excelize.DefinedName{
//        Name:     "Amount",
//        RefersTo: "Sheet1!$A$2:$D$5",
//        Comment:  "defined name comment",
//        Scope:    "Sheet2",
//    })
//
func (f *File) SetDefinedName(definedName *DefinedName) error {
	wb := f.workbookReader()
	d := xlsxDefinedName{
		Name:    definedName.Name,
		Comment: definedName.Comment,
		Data:    definedName.RefersTo,
	}
	if definedName.Scope != "" {
		if sheetID := f.getSheetID(definedName.Scope); sheetID != 0 {
			sheetID--
			d.LocalSheetID = &sheetID
		}
	}
	if wb.DefinedNames != nil {
		for _, dn := range wb.DefinedNames.DefinedName {
			var scope string
			if dn.LocalSheetID != nil {
				scope = f.getSheetNameByID(*dn.LocalSheetID + 1)
			}
			if scope == definedName.Scope && dn.Name == definedName.Name {
				return errors.New("the same name already exists on the scope")
			}
		}
		wb.DefinedNames.DefinedName = append(wb.DefinedNames.DefinedName, d)
		return nil
	}
	wb.DefinedNames = &xlsxDefinedNames{
		DefinedName: []xlsxDefinedName{d},
	}
	return nil
}

// DeleteDefinedName provides a function to delete the defined names of the
// workbook or worksheet. If not specified scope, the default scope is
// workbook. For example:
//
//    f.DeleteDefinedName(&excelize.DefinedName{
//        Name:     "Amount",
//        Scope:    "Sheet2",
//    })
//
func (f *File) DeleteDefinedName(definedName *DefinedName) error {
	wb := f.workbookReader()
	if wb.DefinedNames != nil {
		for idx, dn := range wb.DefinedNames.DefinedName {
			var scope string
			if dn.LocalSheetID != nil {
				scope = f.getSheetNameByID(*dn.LocalSheetID + 1)
			}
			if scope == definedName.Scope && dn.Name == definedName.Name {
				wb.DefinedNames.DefinedName = append(wb.DefinedNames.DefinedName[:idx], wb.DefinedNames.DefinedName[idx+1:]...)
				return nil
			}
		}
	}
	return errors.New("no defined name on the scope")
}

// GetDefinedName provides a function to get the defined names of the workbook
// or worksheet.
func (f *File) GetDefinedName() []DefinedName {
	var definedNames []DefinedName
	wb := f.workbookReader()
	if wb.DefinedNames != nil {
		for _, dn := range wb.DefinedNames.DefinedName {
			definedName := DefinedName{
				Name:     dn.Name,
				Comment:  dn.Comment,
				RefersTo: dn.Data,
				Scope:    "Workbook",
			}
			if dn.LocalSheetID != nil && *dn.LocalSheetID >= 0 {
				definedName.Scope = f.getSheetNameByID(*dn.LocalSheetID + 1)
			}
			definedNames = append(definedNames, definedName)
		}
	}
	return definedNames
}

// GroupSheets provides a function to group worksheets by given worksheets
// name. Group worksheets must contain an active worksheet.
func (f *File) GroupSheets(sheets []string) error {
	// check an active worksheet in group worksheets
	var inActiveSheet bool
	activeSheet := f.GetActiveSheetIndex()
	sheetMap := f.GetSheetList()
	for idx, sheetName := range sheetMap {
		for _, s := range sheets {
			if s == sheetName && idx == activeSheet {
				inActiveSheet = true
			}
		}
	}
	if !inActiveSheet {
		return errors.New("group worksheet must contain an active worksheet")
	}
	// check worksheet exists
	ws := []*xlsxWorksheet{}
	for _, sheet := range sheets {
		xlsx, err := f.workSheetReader(sheet)
		if err != nil {
			return err
		}
		ws = append(ws, xlsx)
	}
	for _, s := range ws {
		sheetViews := s.SheetViews.SheetView
		if len(sheetViews) > 0 {
			for idx := range sheetViews {
				s.SheetViews.SheetView[idx].TabSelected = true
			}
			continue
		}
	}
	return nil
}

// UngroupSheets provides a function to ungroup worksheets.
func (f *File) UngroupSheets() error {
	activeSheet := f.GetActiveSheetIndex()
	for index, sheet := range f.GetSheetList() {
		if activeSheet == index {
			continue
		}
		ws, _ := f.workSheetReader(sheet)
		sheetViews := ws.SheetViews.SheetView
		if len(sheetViews) > 0 {
			for idx := range sheetViews {
				ws.SheetViews.SheetView[idx].TabSelected = false
			}
		}
	}
	return nil
}

// InsertPageBreak create a page break to determine where the printed page
// ends and where begins the next one by given worksheet name and axis, so the
// content before the page break will be printed on one page and after the
// page break on another.
func (f *File) InsertPageBreak(sheet, cell string) (err error) {
	var ws *xlsxWorksheet
	var row, col int
	var rowBrk, colBrk = -1, -1
	if ws, err = f.workSheetReader(sheet); err != nil {
		return
	}
	if col, row, err = CellNameToCoordinates(cell); err != nil {
		return
	}
	col--
	row--
	if col == row && col == 0 {
		return
	}
	if ws.RowBreaks == nil {
		ws.RowBreaks = &xlsxBreaks{}
	}
	if ws.ColBreaks == nil {
		ws.ColBreaks = &xlsxBreaks{}
	}

	for idx, brk := range ws.RowBreaks.Brk {
		if brk.ID == row {
			rowBrk = idx
		}
	}
	for idx, brk := range ws.ColBreaks.Brk {
		if brk.ID == col {
			colBrk = idx
		}
	}

	if row != 0 && rowBrk == -1 {
		ws.RowBreaks.Brk = append(ws.RowBreaks.Brk, &xlsxBrk{
			ID:  row,
			Max: 16383,
			Man: true,
		})
		ws.RowBreaks.ManualBreakCount++
	}
	if col != 0 && colBrk == -1 {
		ws.ColBreaks.Brk = append(ws.ColBreaks.Brk, &xlsxBrk{
			ID:  col,
			Max: 1048575,
			Man: true,
		})
		ws.ColBreaks.ManualBreakCount++
	}
	ws.RowBreaks.Count = len(ws.RowBreaks.Brk)
	ws.ColBreaks.Count = len(ws.ColBreaks.Brk)
	return
}

// RemovePageBreak remove a page break by given worksheet name and axis.
func (f *File) RemovePageBreak(sheet, cell string) (err error) {
	var ws *xlsxWorksheet
	var row, col int
	if ws, err = f.workSheetReader(sheet); err != nil {
		return
	}
	if col, row, err = CellNameToCoordinates(cell); err != nil {
		return
	}
	col--
	row--
	if col == row && col == 0 {
		return
	}
	removeBrk := func(ID int, brks []*xlsxBrk) []*xlsxBrk {
		for i, brk := range brks {
			if brk.ID == ID {
				brks = append(brks[:i], brks[i+1:]...)
			}
		}
		return brks
	}
	if ws.RowBreaks == nil || ws.ColBreaks == nil {
		return
	}
	rowBrks := len(ws.RowBreaks.Brk)
	colBrks := len(ws.ColBreaks.Brk)
	if rowBrks > 0 && rowBrks == colBrks {
		ws.RowBreaks.Brk = removeBrk(row, ws.RowBreaks.Brk)
		ws.ColBreaks.Brk = removeBrk(col, ws.ColBreaks.Brk)
		ws.RowBreaks.Count = len(ws.RowBreaks.Brk)
		ws.ColBreaks.Count = len(ws.ColBreaks.Brk)
		ws.RowBreaks.ManualBreakCount--
		ws.ColBreaks.ManualBreakCount--
		return
	}
	if rowBrks > 0 && rowBrks > colBrks {
		ws.RowBreaks.Brk = removeBrk(row, ws.RowBreaks.Brk)
		ws.RowBreaks.Count = len(ws.RowBreaks.Brk)
		ws.RowBreaks.ManualBreakCount--
		return
	}
	if colBrks > 0 && colBrks > rowBrks {
		ws.ColBreaks.Brk = removeBrk(col, ws.ColBreaks.Brk)
		ws.ColBreaks.Count = len(ws.ColBreaks.Brk)
		ws.ColBreaks.ManualBreakCount--
	}
	return
}

// relsReader provides a function to get the pointer to the structure
// after deserialization of xl/worksheets/_rels/sheet%d.xml.rels.
func (f *File) relsReader(path string) *xlsxRelationships {
	var err error

	if f.Relationships[path] == nil {
		_, ok := f.XLSX[path]
		if ok {
			c := xlsxRelationships{}
			if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML(path)))).
				Decode(&c); err != nil && err != io.EOF {
				log.Printf("xml decode error: %s", err)
			}
			f.Relationships[path] = &c
		}
	}

	return f.Relationships[path]
}

// fillSheetData ensures there are enough rows, and columns in the chosen
// row to accept data. Missing rows are backfilled and given their row number
// Uses the last populated row as a hint for the size of the next row to add
func prepareSheetXML(xlsx *xlsxWorksheet, col int, row int) {
	rowCount := len(xlsx.SheetData.Row)
	sizeHint := 0
	if rowCount > 0 {
		sizeHint = len(xlsx.SheetData.Row[rowCount-1].C)
	}
	if rowCount < row {
		// append missing rows
		for rowIdx := rowCount; rowIdx < row; rowIdx++ {
			xlsx.SheetData.Row = append(xlsx.SheetData.Row, xlsxRow{R: rowIdx + 1, C: make([]xlsxC, 0, sizeHint)})
		}
	}
	rowData := &xlsx.SheetData.Row[row-1]
	fillColumns(rowData, col, row)
}

func fillColumns(rowData *xlsxRow, col, row int) {
	cellCount := len(rowData.C)
	if cellCount < col {
		for colIdx := cellCount; colIdx < col; colIdx++ {
			cellName, _ := CoordinatesToCellName(colIdx+1, row)
			rowData.C = append(rowData.C, xlsxC{R: cellName})
		}
	}
}

func makeContiguousColumns(xlsx *xlsxWorksheet, fromRow, toRow, colCount int) {
	for ; fromRow < toRow; fromRow++ {
		rowData := &xlsx.SheetData.Row[fromRow-1]
		fillColumns(rowData, colCount, fromRow)
	}
}