func (f *File) prepareCellStyle(xlsx *xlsxWorksheet, col, style int) int {
	if xlsx.Cols != nil && style == 0 {
		for _, v := range xlsx.Cols.Col {
			if v.Min <= col && col <= v.Max {
				style = v.Style
			}
		}
	}
	return style
}
prepareCellStyle provides function to prepare style index of cell in
worksheet by given column index and style index.
package excelize
import (
	"encoding/xml"
	"fmt"
	"reflect"
	"strconv"
	"strings"
	"time"
)
func (f *File) mergeCellsParser(xlsx *xlsxWorksheet, axis string) string {
	axis = strings.ToUpper(axis)
	if xlsx.MergeCells != nil {
		for i := 0; i < len(xlsx.MergeCells.Cells); i++ {
			if checkCellInArea(axis, xlsx.MergeCells.Cells[i].Ref) {
				axis = strings.Split(xlsx.MergeCells.Cells[i].Ref, ":")[0]
			}
		}
	}
	return axis
}
func (f *File) SetCellValue(sheet, axis string, value interface{}) {
	switch t := value.(type) {
	case float32:
		f.SetCellDefault(sheet, axis, strconv.FormatFloat(float64(value.(float32)), 'f', -1, 32))
	case float64:
		f.SetCellDefault(sheet, axis, strconv.FormatFloat(float64(value.(float64)), 'f', -1, 64))
	case string:
		f.SetCellStr(sheet, axis, t)
	case []byte:
		f.SetCellStr(sheet, axis, string(t))
	case time.Duration:
		f.SetCellDefault(sheet, axis, strconv.FormatFloat(float64(value.(time.Duration).Seconds()/86400), 'f', -1, 32))
		f.setDefaultTimeStyle(sheet, axis, 21)
	case time.Time:
		f.SetCellDefault(sheet, axis, strconv.FormatFloat(float64(timeToExcelTime(timeToUTCTime(value.(time.Time)))), 'f', -1, 64))
		f.setDefaultTimeStyle(sheet, axis, 22)
	case nil:
		f.SetCellStr(sheet, axis, "")
	case bool:
		f.SetCellBool(sheet, axis, bool(value.(bool)))
	default:
		f.setCellIntValue(sheet, axis, value)
	}
}
func (f *File) setCellIntValue(sheet, axis string, value interface{}) {
	switch value.(type) {
	case int:
		f.SetCellInt(sheet, axis, value.(int))
	case int8:
		f.SetCellInt(sheet, axis, int(value.(int8)))
	case int16:
		f.SetCellInt(sheet, axis, int(value.(int16)))
	case int32:
		f.SetCellInt(sheet, axis, int(value.(int32)))
	case int64:
		f.SetCellInt(sheet, axis, int(value.(int64)))
	case uint:
		f.SetCellInt(sheet, axis, int(value.(uint)))
	case uint8:
		f.SetCellInt(sheet, axis, int(value.(uint8)))
	case uint16:
		f.SetCellInt(sheet, axis, int(value.(uint16)))
	case uint32:
		f.SetCellInt(sheet, axis, int(value.(uint32)))
	case uint64:
		f.SetCellInt(sheet, axis, int(value.(uint64)))
	default:
		f.SetCellStr(sheet, axis, fmt.Sprintf("%v", value))
	}
}
func (f *File) SetCellBool(sheet, axis string, value bool) {
	xlsx := f.workSheetReader(sheet)
	axis = f.mergeCellsParser(xlsx, axis)
	col := string(strings.Map(letterOnlyMapF, axis))
	row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis))
	if err != nil {
		return
	}
	xAxis := row - 1
	yAxis := TitleToNumber(col)
	rows := xAxis + 1
	cell := yAxis + 1
	completeRow(xlsx, rows, cell)
	completeCol(xlsx, rows, cell)
	xlsx.SheetData.Row[xAxis].C[yAxis].S = f.prepareCellStyle(xlsx, cell, xlsx.SheetData.Row[xAxis].C[yAxis].S)
	xlsx.SheetData.Row[xAxis].C[yAxis].T = "b"
	if value {
		xlsx.SheetData.Row[xAxis].C[yAxis].V = "1"
	} else {
		xlsx.SheetData.Row[xAxis].C[yAxis].V = "0"
	}
}
func (f *File) GetCellValue(sheet, axis string) string {
	xlsx := f.workSheetReader(sheet)
	axis = f.mergeCellsParser(xlsx, axis)
	row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis))
	if err != nil {
		return ""
	}
	xAxis := row - 1
	rows := len(xlsx.SheetData.Row)
	if rows > 1 {
		lastRow := xlsx.SheetData.Row[rows-1].R
		if lastRow >= rows {
			rows = lastRow
		}
	}
	if rows < xAxis {
		return ""
	}
	for k := range xlsx.SheetData.Row {
		if xlsx.SheetData.Row[k].R == row {
			for i := range xlsx.SheetData.Row[k].C {
				if axis == xlsx.SheetData.Row[k].C[i].R {
					val, _ := xlsx.SheetData.Row[k].C[i].getValueFrom(f, f.sharedStringsReader())
					return val
				}
			}
		}
	}
	return ""
}
func (f *File) formattedValue(s int, v string) string {
	if s == 0 {
		return v
	}
	styleSheet := f.stylesReader()
	ok := builtInNumFmtFunc[styleSheet.CellXfs.Xf[s].NumFmtID]
	if ok != nil {
		return ok(styleSheet.CellXfs.Xf[s].NumFmtID, v)
	}
	return v
}
func (f *File) GetCellStyle(sheet, axis string) int {
	xlsx := f.workSheetReader(sheet)
	axis = f.mergeCellsParser(xlsx, axis)
	col := string(strings.Map(letterOnlyMapF, axis))
	row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis))
	if err != nil {
		return 0
	}
	xAxis := row - 1
	yAxis := TitleToNumber(col)
	rows := xAxis + 1
	cell := yAxis + 1
	completeRow(xlsx, rows, cell)
	completeCol(xlsx, rows, cell)
	return f.prepareCellStyle(xlsx, cell, xlsx.SheetData.Row[xAxis].C[yAxis].S)
}
func (f *File) GetCellFormula(sheet, axis string) string {
	xlsx := f.workSheetReader(sheet)
	axis = f.mergeCellsParser(xlsx, axis)
	row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis))
	if err != nil {
		return ""
	}
	xAxis := row - 1
	rows := len(xlsx.SheetData.Row)
	if rows > 1 {
		lastRow := xlsx.SheetData.Row[rows-1].R
		if lastRow >= rows {
			rows = lastRow
		}
	}
	if rows < xAxis {
		return ""
	}
	for k := range xlsx.SheetData.Row {
		if xlsx.SheetData.Row[k].R == row {
			for i := range xlsx.SheetData.Row[k].C {
				if axis == xlsx.SheetData.Row[k].C[i].R {
					if xlsx.SheetData.Row[k].C[i].F != nil {
						return xlsx.SheetData.Row[k].C[i].F.Content
					}
				}
			}
		}
	}
	return ""
}
func (f *File) SetCellFormula(sheet, axis, formula string) {
	xlsx := f.workSheetReader(sheet)
	axis = f.mergeCellsParser(xlsx, axis)
	col := string(strings.Map(letterOnlyMapF, axis))
	row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis))
	if err != nil {
		return
	}
	xAxis := row - 1
	yAxis := TitleToNumber(col)
	rows := xAxis + 1
	cell := yAxis + 1
	completeRow(xlsx, rows, cell)
	completeCol(xlsx, rows, cell)
	if xlsx.SheetData.Row[xAxis].C[yAxis].F != nil {
		xlsx.SheetData.Row[xAxis].C[yAxis].F.Content = formula
	} else {
		f := xlsxF{
			Content: formula,
		}
		xlsx.SheetData.Row[xAxis].C[yAxis].F = &f
	}
}
func (f *File) SetCellHyperLink(sheet, axis, link, linkType string) {
	xlsx := f.workSheetReader(sheet)
	axis = f.mergeCellsParser(xlsx, axis)
	linkTypes := map[string]xlsxHyperlink{
		"External": {},
		"Location": {Location: link},
	}
	hyperlink, ok := linkTypes[linkType]
	if !ok || axis == "" {
		return
	}
	hyperlink.Ref = axis
	if linkType == "External" {
		rID := f.addSheetRelationships(sheet, SourceRelationshipHyperLink, link, linkType)
		hyperlink.RID = "rId" + strconv.Itoa(rID)
	}
	if xlsx.Hyperlinks == nil {
		xlsx.Hyperlinks = &xlsxHyperlinks{}
	}
	xlsx.Hyperlinks.Hyperlink = append(xlsx.Hyperlinks.Hyperlink, hyperlink)
}
func (f *File) GetCellHyperLink(sheet, axis string) (bool, string) {
	var link bool
	var target string
	xlsx := f.workSheetReader(sheet)
	axis = f.mergeCellsParser(xlsx, axis)
	if xlsx.Hyperlinks == nil || axis == "" {
		return link, target
	}
	for h := range xlsx.Hyperlinks.Hyperlink {
		if xlsx.Hyperlinks.Hyperlink[h].Ref == axis {
			link = true
			target = xlsx.Hyperlinks.Hyperlink[h].Location
			if xlsx.Hyperlinks.Hyperlink[h].RID != "" {
				target = f.getSheetRelationshipsTargetByID(sheet, xlsx.Hyperlinks.Hyperlink[h].RID)
			}
		}
	}
	return link, target
}
func (f *File) MergeCell(sheet, hcell, vcell string) {
	if hcell == vcell {
		return
	}
	hcell = strings.ToUpper(hcell)
	vcell = strings.ToUpper(vcell)
	hcol := string(strings.Map(letterOnlyMapF, hcell))
	hrow, _ := strconv.Atoi(strings.Map(intOnlyMapF, hcell))
	hyAxis := hrow - 1
	hxAxis := TitleToNumber(hcol)
	vcol := string(strings.Map(letterOnlyMapF, vcell))
	vrow, _ := strconv.Atoi(strings.Map(intOnlyMapF, vcell))
	vyAxis := vrow - 1
	vxAxis := TitleToNumber(vcol)
	if vxAxis < hxAxis {
		hcell, vcell = vcell, hcell
		vxAxis, hxAxis = hxAxis, vxAxis
	}
	if vyAxis < hyAxis {
		hcell, vcell = vcell, hcell
		vyAxis, hyAxis = hyAxis, vyAxis
	}
	xlsx := f.workSheetReader(sheet)
	if xlsx.MergeCells != nil {
		mergeCell := xlsxMergeCell{}
		mergeCell.Ref = ToAlphaString(hxAxis) + strconv.Itoa(hyAxis+1) + ":" + ToAlphaString(vxAxis) + strconv.Itoa(vyAxis+1)
		for i := 0; i < len(xlsx.MergeCells.Cells); i++ {
			if checkCellInArea(hcell, xlsx.MergeCells.Cells[i].Ref) || checkCellInArea(strings.Split(xlsx.MergeCells.Cells[i].Ref, ":")[0], mergeCell.Ref) {
				xlsx.MergeCells.Cells = append(xlsx.MergeCells.Cells[:i], xlsx.MergeCells.Cells[i+1:]...)
			} else if checkCellInArea(vcell, xlsx.MergeCells.Cells[i].Ref) || checkCellInArea(strings.Split(xlsx.MergeCells.Cells[i].Ref, ":")[1], mergeCell.Ref) {
				xlsx.MergeCells.Cells = append(xlsx.MergeCells.Cells[:i], xlsx.MergeCells.Cells[i+1:]...)
			}
		}
		xlsx.MergeCells.Cells = append(xlsx.MergeCells.Cells, &mergeCell)
	} else {
		mergeCell := xlsxMergeCell{}
		mergeCell.Ref = ToAlphaString(hxAxis) + strconv.Itoa(hyAxis+1) + ":" + ToAlphaString(vxAxis) + strconv.Itoa(vyAxis+1)
		mergeCells := xlsxMergeCells{}
		mergeCells.Cells = append(mergeCells.Cells, &mergeCell)
		xlsx.MergeCells = &mergeCells
	}
}
func (f *File) SetCellInt(sheet, axis string, value int) {
	xlsx := f.workSheetReader(sheet)
	axis = f.mergeCellsParser(xlsx, axis)
	col := string(strings.Map(letterOnlyMapF, axis))
	row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis))
	if err != nil {
		return
	}
	xAxis := row - 1
	yAxis := TitleToNumber(col)
	rows := xAxis + 1
	cell := yAxis + 1
	completeRow(xlsx, rows, cell)
	completeCol(xlsx, rows, cell)
	xlsx.SheetData.Row[xAxis].C[yAxis].S = f.prepareCellStyle(xlsx, cell, xlsx.SheetData.Row[xAxis].C[yAxis].S)
	xlsx.SheetData.Row[xAxis].C[yAxis].T = ""
	xlsx.SheetData.Row[xAxis].C[yAxis].V = strconv.Itoa(value)
}