repo_description: Go language library for reading and writing Microsoft Excel™ (XLAM / XLSM / XLSX / XLTM / XLTX) spreadsheets
repo_topics: agent ai analytics chart ecma-376 excel excelize formula go mcp microsoft office ooxml spreadsheet statistics table vba visualization xlsx xml
code: // 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.25.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]
  }
  