merge.go

// Copyright 2016 - 2026 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.24.0 or later.

package excelize

import "strings"

// Rect gets merged cell rectangle coordinates sequence.
func (mc *xlsxMergeCell) Rect() ([]int, error) {
	var err error
	if mc.rect == nil {
		mergedCellsRef := mc.Ref
		if !strings.Contains(mergedCellsRef, ":") {
			mergedCellsRef += ":" + mergedCellsRef
		}
		mc.rect, err = rangeRefToCoordinates(mergedCellsRef)
	}
	return mc.rect, err
}

// MergeCell provides a function to merge cells by given range reference and
// sheet name. Merging cells only keeps the upper-left cell value, and
// discards the other values. For example create a merged cell of D3:E9 on
// Sheet1:
//
//	err := f.MergeCell("Sheet1", "D3", "E9")
//
// If you create a merged cell that overlaps with another existing merged cell,
// those merged cells that already exist will be removed. The cell references
// tuple after merging in the following range will be: A1(x3,y1) D1(x2,y1)
// A8(x3,y4) D8(x2,y4)
//
//	             B1(x1,y1)      D1(x2,y1)
//	           +------------------------+
//	           |                        |
//	A4(x3,y3)  |    C4(x4,y3)           |
//	+------------------------+          |
//	|          |             |          |
//	|          |B5(x1,y2)    | D5(x2,y2)|
//	|          +------------------------+
//	|                        |
//	|A8(x3,y4)      C8(x4,y4)|
//	+------------------------+
func (f *File) MergeCell(sheet, topLeftCell, bottomRightCell string) error {
	rect, err := rangeRefToCoordinates(topLeftCell + ":" + bottomRightCell)
	if err != nil {
		return err
	}
	// Correct the range reference, such correct C1:B3 to B1:C3.
	_ = sortCoordinates(rect)

	topLeftCell, _ = CoordinatesToCellName(rect[0], rect[1])
	bottomRightCell, _ = CoordinatesToCellName(rect[2], rect[3])

	ws, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	ws.mu.Lock()
	defer ws.mu.Unlock()
	for col := rect[0]; col <= rect[2]; col++ {
		for row := rect[1]; row <= rect[3]; row++ {
			if col == rect[0] && row == rect[1] {
				continue
			}
			ws.prepareSheetXML(col, row)
			c := &ws.SheetData.Row[row-1].C[col-1]
			c.setCellDefault("")
			_ = f.removeFormula(c, ws, sheet)
		}
	}
	ref := topLeftCell + ":" + bottomRightCell
	if ws.MergeCells != nil {
		ws.MergeCells.Cells = append(ws.MergeCells.Cells, &xlsxMergeCell{Ref: ref, rect: rect})
	} else {
		ws.MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: ref, rect: rect}}}
	}
	ws.MergeCells.Count = len(ws.MergeCells.Cells)
	return err
}

// UnmergeCell provides a function to unmerge a given range reference.
// For example unmerge range reference D3:E9 on Sheet1:
//
//	err := f.UnmergeCell("Sheet1", "D3", "E9")
//
// Attention: overlapped range will also be unmerged.
func (f *File) UnmergeCell(sheet, topLeftCell, bottomRightCell string) error {
	ws, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	ws.mu.Lock()
	defer ws.mu.Unlock()
	rect1, err := rangeRefToCoordinates(topLeftCell + ":" + bottomRightCell)
	if err != nil {
		return err
	}

	// Correct the range reference, such correct C1:B3 to B1:C3.
	_ = sortCoordinates(rect1)

	// return nil since no MergeCells in the sheet
	if ws.MergeCells == nil {
		return nil
	}
	if err = ws.mergeOverlapCells(); err != nil {
		return err
	}
	f.clearCalcCache()
	i := 0
	for _, mergeCell := range ws.MergeCells.Cells {
		if rect2, _ := rangeRefToCoordinates(mergeCell.Ref); isOverlap(rect1, rect2) {
			continue
		}
		ws.MergeCells.Cells[i] = mergeCell
		i++
	}
	ws.MergeCells.Cells = ws.MergeCells.Cells[:i]
	ws.MergeCells.Count = len(ws.MergeCells.Cells)
	if ws.MergeCells.Count == 0 {
		ws.MergeCells = nil
	}
	return nil
}

// GetMergeCells provides a function to get all merged cells from a specific
// worksheet. If the `withoutValues` parameter is set to true, it will not
// return the cell values of merged cells, only the range reference will be
// returned. For example get all merged cells on Sheet1:
//
//	mergeCells, err := f.GetMergeCells("Sheet1")
//
// If you want to get merged cells without cell values, you can use the
// following code:
//
//	mergeCells, err := f.GetMergeCells("Sheet1", true)
func (f *File) GetMergeCells(sheet string, withoutValues ...bool) ([]MergeCell, error) {
	var (
		mergeCells []MergeCell
		withoutVal bool
	)
	if len(withoutValues) > 0 {
		withoutVal = withoutValues[0]
	}
	ws, err := f.workSheetReader(sheet)
	if err != nil {
		return mergeCells, err
	}
	if ws.MergeCells != nil {
		if err = ws.mergeOverlapCells(); err != nil {
			return mergeCells, err
		}
		mergeCells = make([]MergeCell, 0, len(ws.MergeCells.Cells))
		for i := range ws.MergeCells.Cells {
			ref, val := ws.MergeCells.Cells[i].Ref, ""
			if !withoutVal {
				cell := strings.Split(ref, ":")[0]
				val, _ = f.GetCellValue(sheet, cell)
			}
			mergeCells = append(mergeCells, []string{ref, val})
		}
	}
	return mergeCells, err
}

// mergeOverlapCells merge overlap cells.
func (ws *xlsxWorksheet) mergeOverlapCells() error {
	var (
		err      error
		rectList [][]int
		merged   = true
	)
	for _, cell := range ws.MergeCells.Cells {
		if cell == nil {
			continue
		}
		if cell.rect, err = cell.Rect(); err != nil {
			return err
		}
		rectList = append(rectList, cell.rect)
	}
	for merged {
		merged = false
		var mergedRectList [][]int
		used := make([]bool, len(rectList))
		for i := 0; i < len(rectList); i++ {
			if used[i] {
				continue
			}
			r1 := rectList[i]
			for j := i + 1; j < len(rectList); j++ {
				if used[j] {
					continue
				}
				if r2 := rectList[j]; isOverlap(r1, r2) {
					r1 = []int{
						min(r1[0], r2[0]),
						min(r1[1], r2[1]),
						max(r1[2], r2[2]),
						max(r1[3], r2[3]),
					}
					merged, used[j] = true, true
				}
			}
			mergedRectList = append(mergedRectList, r1)
		}
		rectList = mergedRectList
	}
	ws.MergeCells.Cells = make([]*xlsxMergeCell, 0, len(rectList))
	for _, r := range rectList {
		ref, _ := coordinatesToRangeRef(r)
		ws.MergeCells.Cells = append(ws.MergeCells.Cells, &xlsxMergeCell{Ref: ref, rect: r})
	}
	ws.MergeCells.Count = len(ws.MergeCells.Cells)
	return err
}

// MergeCell define a merged cell data.
// It consists of the following structure.
// example: []string{"D4:E10", "cell value"}
type MergeCell []string

// GetCellValue returns merged cell value.
func (m *MergeCell) GetCellValue() string {
	return (*m)[1]
}

// GetStartAxis returns the top left cell reference of merged range, for
// example: "C2".
func (m *MergeCell) GetStartAxis() string {
	return strings.Split((*m)[0], ":")[0]
}

// GetEndAxis returns the bottom right cell reference of merged range, for
// example: "D4".
func (m *MergeCell) GetEndAxis() string {
	return strings.Split((*m)[0], ":")[1]
}