workbook.go

// Copyright 2016 - 2025 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 XLAM / XLSM / XLSX / XLTM / XLTX files. Supports reading and
// writing spreadsheet documents generated by Microsoft Excel™ 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.23 or later.

package excelize

import (
	"bytes"
	"encoding/xml"
	"io"
	"path/filepath"
	"reflect"
	"strconv"
	"strings"
)

// SetWorkbookProps provides a function to sets workbook properties.
func (f *File) SetWorkbookProps(opts *WorkbookPropsOptions) error {
	if opts == nil {
		return nil
	}
	wb, err := f.workbookReader()
	if err != nil {
		return err
	}
	if wb.WorkbookPr == nil {
		wb.WorkbookPr = new(xlsxWorkbookPr)
	}
	setNoPtrFieldsVal([]string{"Date1904", "FilterPrivacy", "CodeName"},
		reflect.ValueOf(*opts), reflect.ValueOf(wb.WorkbookPr).Elem())
	return err
}

// GetWorkbookProps provides a function to gets workbook properties.
func (f *File) GetWorkbookProps() (WorkbookPropsOptions, error) {
	var opts WorkbookPropsOptions
	wb, err := f.workbookReader()
	if err != nil {
		return opts, err
	}
	if wb.WorkbookPr == nil {
		return opts, err
	}
	setPtrFieldsVal([]string{"Date1904", "FilterPrivacy", "CodeName"},
		reflect.ValueOf(*wb.WorkbookPr), reflect.ValueOf(&opts).Elem())
	return opts, err
}

// SetCalcProps provides a function to sets calculation properties. Optional
// value of "CalcMode" property is: "manual", "auto" or "autoNoTable". Optional
// value of "RefMode" property is: "A1" or "R1C1".
func (f *File) SetCalcProps(opts *CalcPropsOptions) error {
	if opts == nil {
		return nil
	}
	wb, err := f.workbookReader()
	if err != nil {
		return err
	}
	if wb.CalcPr == nil {
		wb.CalcPr = new(xlsxCalcPr)
	}
	if opts.CalcMode != nil && inStrSlice(supportedCalcMode, *opts.CalcMode, true) == -1 {
		return newInvalidOptionalValue("CalcMode", *opts.CalcMode, supportedCalcMode)
	}
	if opts.RefMode != nil && inStrSlice(supportedRefMode, *opts.RefMode, true) == -1 {
		return newInvalidOptionalValue("RefMode", *opts.RefMode, supportedRefMode)
	}
	setNoPtrFieldsVal([]string{
		"CalcCompleted", "CalcOnSave", "ForceFullCalc", "FullCalcOnLoad", "FullPrecision", "Iterate",
		"IterateDelta",
		"CalcMode", "RefMode",
	}, reflect.ValueOf(*opts), reflect.ValueOf(wb.CalcPr).Elem())
	if opts.CalcID != nil {
		wb.CalcPr.CalcID = int(*opts.CalcID)
	}
	if opts.ConcurrentManualCount != nil {
		wb.CalcPr.ConcurrentManualCount = int(*opts.ConcurrentManualCount)
	}
	if opts.IterateCount != nil {
		wb.CalcPr.IterateCount = int(*opts.IterateCount)
	}
	wb.CalcPr.ConcurrentCalc = opts.ConcurrentCalc
	return err
}

// GetCalcProps provides a function to gets calculation properties.
func (f *File) GetCalcProps() (CalcPropsOptions, error) {
	var opts CalcPropsOptions
	wb, err := f.workbookReader()
	if err != nil {
		return opts, err
	}
	if wb.CalcPr == nil {
		return opts, err
	}
	setPtrFieldsVal([]string{
		"CalcCompleted", "CalcOnSave", "ForceFullCalc", "FullCalcOnLoad", "FullPrecision", "Iterate",
		"IterateDelta",
		"CalcMode", "RefMode",
	}, reflect.ValueOf(*wb.CalcPr), reflect.ValueOf(&opts).Elem())
	opts.CalcID = uintPtr(uint(wb.CalcPr.CalcID))
	opts.ConcurrentManualCount = uintPtr(uint(wb.CalcPr.ConcurrentManualCount))
	opts.IterateCount = uintPtr(uint(wb.CalcPr.IterateCount))
	opts.ConcurrentCalc = wb.CalcPr.ConcurrentCalc
	return opts, err
}

// ProtectWorkbook provides a function to prevent other users from viewing
// hidden worksheets, adding, moving, deleting, or hiding worksheets, and
// renaming worksheets in a workbook. The optional field AlgorithmName
// specified hash algorithm, support XOR, MD4, MD5, SHA-1, SHA2-56, SHA-384,
// and SHA-512 currently, if no hash algorithm specified, will be using the XOR
// algorithm as default. The generated workbook only works on Microsoft Office
// 2007 and later. For example, protect workbook with protection settings:
//
//	err := f.ProtectWorkbook(&excelize.WorkbookProtectionOptions{
//	    Password:      "password",
//	    LockStructure: true,
//	})
func (f *File) ProtectWorkbook(opts *WorkbookProtectionOptions) error {
	wb, err := f.workbookReader()
	if err != nil {
		return err
	}
	if wb.WorkbookProtection == nil {
		wb.WorkbookProtection = new(xlsxWorkbookProtection)
	}
	if opts == nil {
		opts = &WorkbookProtectionOptions{}
	}
	wb.WorkbookProtection = &xlsxWorkbookProtection{
		LockStructure: opts.LockStructure,
		LockWindows:   opts.LockWindows,
	}
	if opts.Password != "" {
		if opts.AlgorithmName == "" {
			opts.AlgorithmName = "SHA-512"
		}
		hashValue, saltValue, err := genISOPasswdHash(opts.Password, opts.AlgorithmName, "", int(workbookProtectionSpinCount))
		if err != nil {
			return err
		}
		wb.WorkbookProtection.WorkbookAlgorithmName = opts.AlgorithmName
		wb.WorkbookProtection.WorkbookSaltValue = saltValue
		wb.WorkbookProtection.WorkbookHashValue = hashValue
		wb.WorkbookProtection.WorkbookSpinCount = int(workbookProtectionSpinCount)
	}
	return err
}

// UnprotectWorkbook provides a function to remove protection for workbook,
// specified the optional password parameter to remove workbook protection with
// password verification.
func (f *File) UnprotectWorkbook(password ...string) error {
	wb, err := f.workbookReader()
	if err != nil {
		return err
	}
	// password verification
	if len(password) > 0 {
		if wb.WorkbookProtection == nil {
			return ErrUnprotectWorkbook
		}
		if wb.WorkbookProtection.WorkbookAlgorithmName != "" {
			// check with given salt value
			hashValue, _, err := genISOPasswdHash(password[0], wb.WorkbookProtection.WorkbookAlgorithmName, wb.WorkbookProtection.WorkbookSaltValue, wb.WorkbookProtection.WorkbookSpinCount)
			if err != nil {
				return err
			}
			if wb.WorkbookProtection.WorkbookHashValue != hashValue {
				return ErrUnprotectWorkbookPassword
			}
		}
	}
	wb.WorkbookProtection = nil
	return err
}

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

// getWorkbookPath provides a function to get the path of the workbook.xml in
// the spreadsheet.
func (f *File) getWorkbookPath() (path string) {
	if rels, _ := f.relsReader("_rels/.rels"); rels != nil {
		rels.mu.Lock()
		defer rels.mu.Unlock()
		for _, rel := range rels.Relationships {
			if rel.Type == SourceRelationshipOfficeDocument {
				path = strings.TrimPrefix(rel.Target, "/")
				return
			}
		}
	}
	return
}

// getWorkbookRelsPath provides a function to get the path of the workbook.xml.rels
// in the spreadsheet.
func (f *File) getWorkbookRelsPath() (path string) {
	wbPath := f.getWorkbookPath()
	wbDir := filepath.Dir(wbPath)
	if wbDir == "." {
		path = "_rels/" + filepath.Base(wbPath) + ".rels"
		return
	}
	path = strings.TrimPrefix(filepath.Dir(wbPath)+"/_rels/"+filepath.Base(wbPath)+".rels", "/")
	return
}

// deleteWorkbookRels provides a function to delete relationships in
// xl/_rels/workbook.xml.rels by given type and target.
func (f *File) deleteWorkbookRels(relType, relTarget string) (string, error) {
	var rID string
	rels, err := f.relsReader(f.getWorkbookRelsPath())
	if err != nil {
		return rID, err
	}
	if rels == nil {
		rels = &xlsxRelationships{}
	}
	for k, v := range rels.Relationships {
		if v.Type == relType && v.Target == relTarget {
			rID = v.ID
			rels.Relationships = append(rels.Relationships[:k], rels.Relationships[k+1:]...)
		}
	}
	return rID, err
}

// workbookReader provides a function to get the pointer to the workbook.xml
// structure after deserialization.
func (f *File) workbookReader() (*xlsxWorkbook, error) {
	var err error
	if f.WorkBook == nil {
		wbPath := f.getWorkbookPath()
		f.WorkBook = new(xlsxWorkbook)
		if attrs, ok := f.xmlAttr.Load(wbPath); !ok {
			d := f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML(wbPath))))
			if attrs == nil {
				attrs = []xml.Attr{}
			}
			attrs = append(attrs.([]xml.Attr), getRootElement(d)...)
			f.xmlAttr.Store(wbPath, attrs)
			f.addNameSpaces(wbPath, SourceRelationship)
		}
		if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML(wbPath)))).
			Decode(f.WorkBook); err != nil && err != io.EOF {
			return f.WorkBook, err
		}
	}
	return f.WorkBook, err
}

// workBookWriter provides a function to save workbook.xml after serialize
// structure.
func (f *File) workBookWriter() {
	if f.WorkBook != nil {
		if f.WorkBook.DecodeAlternateContent != nil {
			f.WorkBook.AlternateContent = &xlsxAlternateContent{
				Content: f.WorkBook.DecodeAlternateContent.Content,
				XMLNSMC: SourceRelationshipCompatibility.Value,
			}
		}
		f.WorkBook.DecodeAlternateContent = nil
		output, _ := xml.Marshal(f.WorkBook)
		f.saveFileList(f.getWorkbookPath(), replaceRelationshipsBytes(f.replaceNameSpaceBytes(f.getWorkbookPath(), output)))
	}
}

// setContentTypePartRelsExtensions provides a function to set the content type
// for relationship parts and the Main Document part.
func (f *File) setContentTypePartRelsExtensions() error {
	var rels bool
	content, err := f.contentTypesReader()
	if err != nil {
		return err
	}
	for _, v := range content.Defaults {
		if v.Extension == "rels" {
			rels = true
		}
	}
	if !rels {
		content.Defaults = append(content.Defaults, xlsxDefault{
			Extension:   "rels",
			ContentType: ContentTypeRelationships,
		})
	}
	return err
}

// setContentTypePartImageExtensions provides a function to set the content type
// for relationship parts and the Main Document part.
func (f *File) setContentTypePartImageExtensions() error {
	imageTypes := map[string]string{
		"bmp": "image/", "jpeg": "image/", "png": "image/", "gif": "image/",
		"svg": "image/", "tiff": "image/", "emf": "image/x-", "wmf": "image/x-",
		"emz": "image/x-", "wmz": "image/x-",
	}
	content, err := f.contentTypesReader()
	if err != nil {
		return err
	}
	content.mu.Lock()
	defer content.mu.Unlock()
	for _, file := range content.Defaults {
		delete(imageTypes, file.Extension)
	}
	for extension, prefix := range imageTypes {
		content.Defaults = append(content.Defaults, xlsxDefault{
			Extension:   extension,
			ContentType: prefix + extension,
		})
	}
	return err
}

// setContentTypePartVMLExtensions provides a function to set the content type
// for relationship parts and the Main Document part.
func (f *File) setContentTypePartVMLExtensions() error {
	var vml bool
	content, err := f.contentTypesReader()
	if err != nil {
		return err
	}
	content.mu.Lock()
	defer content.mu.Unlock()
	for _, v := range content.Defaults {
		if v.Extension == "vml" {
			vml = true
		}
	}
	if !vml {
		content.Defaults = append(content.Defaults, xlsxDefault{
			Extension:   "vml",
			ContentType: ContentTypeVML,
		})
	}
	return err
}

// addContentTypePart provides a function to add content type part relationships
// in the file [Content_Types].xml by given index and content type.
func (f *File) addContentTypePart(index int, contentType string) error {
	setContentType := map[string]func() error{
		"comments": f.setContentTypePartVMLExtensions,
		"drawings": f.setContentTypePartImageExtensions,
	}
	partNames := map[string]string{
		"chart":         "/xl/charts/chart" + strconv.Itoa(index) + ".xml",
		"chartsheet":    "/xl/chartsheets/sheet" + strconv.Itoa(index) + ".xml",
		"comments":      "/xl/comments" + strconv.Itoa(index) + ".xml",
		"drawings":      "/xl/drawings/drawing" + strconv.Itoa(index) + ".xml",
		"table":         "/xl/tables/table" + strconv.Itoa(index) + ".xml",
		"pivotTable":    "/xl/pivotTables/pivotTable" + strconv.Itoa(index) + ".xml",
		"pivotCache":    "/xl/pivotCache/pivotCacheDefinition" + strconv.Itoa(index) + ".xml",
		"sharedStrings": "/xl/sharedStrings.xml",
		"slicer":        "/xl/slicers/slicer" + strconv.Itoa(index) + ".xml",
		"slicerCache":   "/xl/slicerCaches/slicerCache" + strconv.Itoa(index) + ".xml",
	}
	contentTypes := map[string]string{
		"chart":         ContentTypeDrawingML,
		"chartsheet":    ContentTypeSpreadSheetMLChartsheet,
		"comments":      ContentTypeSpreadSheetMLComments,
		"drawings":      ContentTypeDrawing,
		"table":         ContentTypeSpreadSheetMLTable,
		"pivotTable":    ContentTypeSpreadSheetMLPivotTable,
		"pivotCache":    ContentTypeSpreadSheetMLPivotCacheDefinition,
		"sharedStrings": ContentTypeSpreadSheetMLSharedStrings,
		"slicer":        ContentTypeSlicer,
		"slicerCache":   ContentTypeSlicerCache,
	}
	s, ok := setContentType[contentType]
	if ok {
		if err := s(); err != nil {
			return err
		}
	}
	content, err := f.contentTypesReader()
	if err != nil {
		return err
	}
	content.mu.Lock()
	defer content.mu.Unlock()
	for _, v := range content.Overrides {
		if v.PartName == partNames[contentType] {
			return err
		}
	}
	content.Overrides = append(content.Overrides, xlsxOverride{
		PartName:    partNames[contentType],
		ContentType: contentTypes[contentType],
	})
	return f.setContentTypePartRelsExtensions()
}

// removeContentTypesPart provides a function to remove relationships by given
// content type and part name in the file [Content_Types].xml.
func (f *File) removeContentTypesPart(contentType, partName string) error {
	if !strings.HasPrefix(partName, "/") {
		partName = "/xl/" + partName
	}
	content, err := f.contentTypesReader()
	if err != nil {
		return err
	}
	content.mu.Lock()
	defer content.mu.Unlock()
	for k, v := range content.Overrides {
		if v.PartName == partName && v.ContentType == contentType {
			content.Overrides = append(content.Overrides[:k], content.Overrides[k+1:]...)
		}
	}
	return err
}