代码拉取完成，页面将自动刷新
同步操作将从 xuri/excelize 强制同步，此操作会覆盖自 Fork 仓库以来所做的任何修改，且无法恢复！！！
确定后同步将在后台操作，完成时将刷新页面，请耐心等待。
Close function
// Copyright 2016 - 2022 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.15 or later.
package excelize
import (
"encoding/xml"
"fmt"
"os"
"reflect"
"strconv"
"strings"
"time"
)
// CellType is the type of cell value type.
type CellType byte
// Cell value types enumeration.
const (
CellTypeUnset CellType = iota
CellTypeBool
CellTypeDate
CellTypeError
CellTypeFormula
CellTypeInlineString
CellTypeNumber
CellTypeSharedString
)
const (
// STCellFormulaTypeArray defined the formula is an array formula.
STCellFormulaTypeArray = "array"
// STCellFormulaTypeDataTable defined the formula is a data table formula.
STCellFormulaTypeDataTable = "dataTable"
// STCellFormulaTypeNormal defined the formula is a regular cell formula.
STCellFormulaTypeNormal = "normal"
// STCellFormulaTypeShared defined the formula is part of a shared formula.
STCellFormulaTypeShared = "shared"
)
// cellTypes mapping the cell's data type and enumeration.
var cellTypes = map[string]CellType{
"b": CellTypeBool,
"d": CellTypeDate,
"n": CellTypeNumber,
"e": CellTypeError,
"s": CellTypeSharedString,
"str": CellTypeFormula,
"inlineStr": CellTypeInlineString,
}
// GetCellValue provides a function to get formatted value from cell by given
// worksheet name and cell reference in spreadsheet. The return value is
// converted to the 'string' data type. This function is concurrency safe. If
// the cell format can be applied to the value of a cell, the applied value
// will be returned, otherwise the original value will be returned. All cells'
// values will be the same in a merged range.
func (f *File) GetCellValue(sheet, cell string, opts ...Options) (string, error) {
return f.getCellStringFunc(sheet, cell, func(x *xlsxWorksheet, c *xlsxC) (string, bool, error) {
val, err := c.getValueFrom(f, f.sharedStringsReader(), parseOptions(opts...).RawCellValue)
return val, true, err
})
}
// GetCellType provides a function to get the cell's data type by given
// worksheet name and cell reference in spreadsheet file.
func (f *File) GetCellType(sheet, cell string) (CellType, error) {
var (
err error
cellTypeStr string
cellType CellType
)
if cellTypeStr, err = f.getCellStringFunc(sheet, cell, func(x *xlsxWorksheet, c *xlsxC) (string, bool, error) {
return c.T, true, nil
}); err != nil {
return CellTypeUnset, err
}
cellType = cellTypes[cellTypeStr]
return cellType, err
}
// SetCellValue provides a function to set the value of a cell. This function
// is concurrency safe. The specified coordinates should not be in the first
// row of the table, a complex number can be set with string text. The
// following shows the supported data types:
//
// int
// int8
// int16
// int32
// int64
// uint
// uint8
// uint16
// uint32
// uint64
// float32
// float64
// string
// []byte
// time.Duration
// time.Time
// bool
// nil
//
// Note that default date format is m/d/yy h:mm of time.Time type value. You
// can set numbers format by the SetCellStyle function. If you need to set the
// specialized date in Excel like January 0, 1900 or February 29, 1900, these
// times can not representation in Go language time.Time data type. Please set
// the cell value as number 0 or 60, then create and bind the date-time number
// format style for the cell.
func (f *File) SetCellValue(sheet, cell string, value interface{}) error {
var err error
switch v := value.(type) {
case int, int8, int16, int32, int64, uint, uint8, uint16, uint32, uint64:
err = f.setCellIntFunc(sheet, cell, v)
case float32:
err = f.SetCellFloat(sheet, cell, float64(v), -1, 32)
case float64:
err = f.SetCellFloat(sheet, cell, v, -1, 64)
case string:
err = f.SetCellStr(sheet, cell, v)
case []byte:
err = f.SetCellStr(sheet, cell, string(v))
case time.Duration:
_, d := setCellDuration(v)
err = f.SetCellDefault(sheet, cell, d)
if err != nil {
return err
}
err = f.setDefaultTimeStyle(sheet, cell, 21)
case time.Time:
err = f.setCellTimeFunc(sheet, cell, v)
case bool:
err = f.SetCellBool(sheet, cell, v)
case nil:
err = f.SetCellDefault(sheet, cell, "")
default:
err = f.SetCellStr(sheet, cell, fmt.Sprint(value))
}
return err
}
// String extracts characters from a string item.
func (x xlsxSI) String() string {
var value strings.Builder
if x.T != nil {
value.WriteString(x.T.Val)
}
for _, s := range x.R {
if s.T != nil {
value.WriteString(s.T.Val)
}
}
if value.Len() == 0 {
return ""
}
return bstrUnmarshal(value.String())
}
// hasValue determine if cell non-blank value.
func (c *xlsxC) hasValue() bool {
return c.S != 0 || c.V != "" || c.F != nil || c.T != ""
}
// removeFormula delete formula for the cell.
func (f *File) removeFormula(c *xlsxC, ws *xlsxWorksheet, sheet string) {
if c.F != nil && c.Vm == nil {
f.deleteCalcChain(f.getSheetID(sheet), c.R)
if c.F.T == STCellFormulaTypeShared && c.F.Ref != "" {
si := c.F.Si
for r, row := range ws.SheetData.Row {
for col, cell := range row.C {
if cell.F != nil && cell.F.Si != nil && *cell.F.Si == *si {
ws.SheetData.Row[r].C[col].F = nil
}
}
}
}
c.F = nil
}
}
// setCellIntFunc is a wrapper of SetCellInt.
func (f *File) setCellIntFunc(sheet, cell string, value interface{}) error {
var err error
switch v := value.(type) {
case int:
err = f.SetCellInt(sheet, cell, v)
case int8:
err = f.SetCellInt(sheet, cell, int(v))
case int16:
err = f.SetCellInt(sheet, cell, int(v))
case int32:
err = f.SetCellInt(sheet, cell, int(v))
case int64:
err = f.SetCellInt(sheet, cell, int(v))
case uint:
err = f.SetCellInt(sheet, cell, int(v))
case uint8:
err = f.SetCellInt(sheet, cell, int(v))
case uint16:
err = f.SetCellInt(sheet, cell, int(v))
case uint32:
err = f.SetCellInt(sheet, cell, int(v))
case uint64:
err = f.SetCellInt(sheet, cell, int(v))
}
return err
}
// setCellTimeFunc provides a method to process time type of value for
// SetCellValue.
func (f *File) setCellTimeFunc(sheet, cell string, value time.Time) error {
ws, err := f.workSheetReader(sheet)
if err != nil {
return err
}
c, col, row, err := f.prepareCell(ws, cell)
if err != nil {
return err
}
ws.Lock()
c.S = f.prepareCellStyle(ws, col, row, c.S)
ws.Unlock()
date1904, wb := false, f.workbookReader()
if wb != nil && wb.WorkbookPr != nil {
date1904 = wb.WorkbookPr.Date1904
}
var isNum bool
if isNum, err = c.setCellTime(value, date1904); err != nil {
return err
}
if isNum {
_ = f.setDefaultTimeStyle(sheet, cell, 22)
}
return err
}
// setCellTime prepares cell type and Excel time by given Go time.Time type
// timestamp.
func (c *xlsxC) setCellTime(value time.Time, date1904 bool) (isNum bool, err error) {
var excelTime float64
_, offset := value.In(value.Location()).Zone()
value = value.Add(time.Duration(offset) * time.Second)
if excelTime, err = timeToExcelTime(value, date1904); err != nil {
return
}
isNum = excelTime > 0
if isNum {
c.setCellDefault(strconv.FormatFloat(excelTime, 'f', -1, 64))
} else {
c.setCellDefault(value.Format(time.RFC3339Nano))
}
return
}
// setCellDuration prepares cell type and value by given Go time.Duration type
// time duration.
func setCellDuration(value time.Duration) (t string, v string) {
v = strconv.FormatFloat(value.Seconds()/86400, 'f', -1, 32)
return
}
// SetCellInt provides a function to set int type value of a cell by given
// worksheet name, cell reference and cell value.
func (f *File) SetCellInt(sheet, cell string, value int) error {
ws, err := f.workSheetReader(sheet)
if err != nil {
return err
}
c, col, row, err := f.prepareCell(ws, cell)
if err != nil {
return err
}
ws.Lock()
defer ws.Unlock()
c.S = f.prepareCellStyle(ws, col, row, c.S)
c.T, c.V = setCellInt(value)
c.IS = nil
f.removeFormula(c, ws, sheet)
return err
}
// setCellInt prepares cell type and string type cell value by a given
// integer.
func setCellInt(value int) (t string, v string) {
v = strconv.Itoa(value)
return
}
// SetCellBool provides a function to set bool type value of a cell by given
// worksheet name, cell reference and cell value.
func (f *File) SetCellBool(sheet, cell string, value bool) error {
ws, err := f.workSheetReader(sheet)
if err != nil {
return err
}
c, col, row, err := f.prepareCell(ws, cell)
if err != nil {
return err
}
ws.Lock()
defer ws.Unlock()
c.S = f.prepareCellStyle(ws, col, row, c.S)
c.T, c.V = setCellBool(value)
c.IS = nil
f.removeFormula(c, ws, sheet)
return err
}
// setCellBool prepares cell type and string type cell value by a given
// boolean value.
func setCellBool(value bool) (t string, v string) {
t = "b"
if value {
v = "1"
} else {
v = "0"
}
return
}
// SetCellFloat sets a floating point value into a cell. The precision
// parameter specifies how many places after the decimal will be shown
// while -1 is a special value that will use as many decimal places as
// necessary to represent the number. bitSize is 32 or 64 depending on if a
// float32 or float64 was originally used for the value. For Example:
//
// var x float32 = 1.325
// f.SetCellFloat("Sheet1", "A1", float64(x), 2, 32)
func (f *File) SetCellFloat(sheet, cell string, value float64, precision, bitSize int) error {
ws, err := f.workSheetReader(sheet)
if err != nil {
return err
}
c, col, row, err := f.prepareCell(ws, cell)
if err != nil {
return err
}
ws.Lock()
defer ws.Unlock()
c.S = f.prepareCellStyle(ws, col, row, c.S)
c.T, c.V = setCellFloat(value, precision, bitSize)
c.IS = nil
f.removeFormula(c, ws, sheet)
return err
}
// setCellFloat prepares cell type and string type cell value by a given
// float value.
func setCellFloat(value float64, precision, bitSize int) (t string, v string) {
v = strconv.FormatFloat(value, 'f', precision, bitSize)
return
}
// SetCellStr provides a function to set string type value of a cell. Total
// number of characters that a cell can contain 32767 characters.
func (f *File) SetCellStr(sheet, cell, value string) error {
ws, err := f.workSheetReader(sheet)
if err != nil {
return err
}
c, col, row, err := f.prepareCell(ws, cell)
if err != nil {
return err
}
ws.Lock()
defer ws.Unlock()
c.S = f.prepareCellStyle(ws, col, row, c.S)
c.T, c.V, err = f.setCellString(value)
c.IS = nil
f.removeFormula(c, ws, sheet)
return err
}
// setCellString provides a function to set string type to shared string
// table.
func (f *File) setCellString(value string) (t, v string, err error) {
if len(value) > TotalCellChars {
value = value[:TotalCellChars]
}
t = "s"
var si int
if si, err = f.setSharedString(value); err != nil {
return
}
v = strconv.Itoa(si)
return
}
// sharedStringsLoader load shared string table from system temporary file to
// memory, and reset shared string table for reader.
func (f *File) sharedStringsLoader() (err error) {
f.Lock()
defer f.Unlock()
if path, ok := f.tempFiles.Load(defaultXMLPathSharedStrings); ok {
f.Pkg.Store(defaultXMLPathSharedStrings, f.readBytes(defaultXMLPathSharedStrings))
f.tempFiles.Delete(defaultXMLPathSharedStrings)
if err = os.Remove(path.(string)); err != nil {
return
}
f.SharedStrings = nil
}
if f.sharedStringTemp != nil {
if err := f.sharedStringTemp.Close(); err != nil {
return err
}
f.tempFiles.Delete(defaultTempFileSST)
f.sharedStringItem, err = nil, os.Remove(f.sharedStringTemp.Name())
f.sharedStringTemp = nil
}
return
}
// setSharedString provides a function to add string to the share string table.
func (f *File) setSharedString(val string) (int, error) {
if err := f.sharedStringsLoader(); err != nil {
return 0, err
}
sst := f.sharedStringsReader()
f.Lock()
defer f.Unlock()
if i, ok := f.sharedStringsMap[val]; ok {
return i, nil
}
sst.Count++
sst.UniqueCount++
t := xlsxT{Val: val}
val, t.Space = trimCellValue(val)
sst.SI = append(sst.SI, xlsxSI{T: &t})
f.sharedStringsMap[val] = sst.UniqueCount - 1
return sst.UniqueCount - 1, nil
}
// trimCellValue provides a function to set string type to cell.
func trimCellValue(value string) (v string, ns xml.Attr) {
if len(value) > TotalCellChars {
value = value[:TotalCellChars]
}
if len(value) > 0 {
prefix, suffix := value[0], value[len(value)-1]
for _, ascii := range []byte{9, 10, 13, 32} {
if prefix == ascii || suffix == ascii {
ns = xml.Attr{
Name: xml.Name{Space: NameSpaceXML, Local: "space"},
Value: "preserve",
}
break
}
}
}
v = bstrMarshal(value)
return
}
// setCellValue set cell data type and value for (inline) rich string cell or
// formula cell.
func (c *xlsxC) setCellValue(val string) {
if c.F != nil {
c.setStr(val)
return
}
c.setInlineStr(val)
}
// setInlineStr set cell data type and value which containing an (inline) rich
// string.
func (c *xlsxC) setInlineStr(val string) {
c.T, c.V, c.IS = "inlineStr", "", &xlsxSI{T: &xlsxT{}}
c.IS.T.Val, c.IS.T.Space = trimCellValue(val)
}
// setStr set cell data type and value which containing a formula string.
func (c *xlsxC) setStr(val string) {
c.T, c.IS = "str", nil
c.V, c.XMLSpace = trimCellValue(val)
}
// getCellDate parse cell value which containing a boolean.
func (c *xlsxC) getCellBool(f *File, raw bool) (string, error) {
if !raw {
if c.V == "1" {
return "TRUE", nil
}
if c.V == "0" {
return "FALSE", nil
}
}
return f.formattedValue(c.S, c.V, raw), nil
}
// setCellDefault prepares cell type and string type cell value by a given
// string.
func (c *xlsxC) setCellDefault(value string) {
if ok, _, _ := isNumeric(value); !ok {
c.setInlineStr(value)
c.IS.T.Val = value
return
}
c.V = value
}
// getCellDate parse cell value which contains a date in the ISO 8601 format.
func (c *xlsxC) getCellDate(f *File, raw bool) (string, error) {
if !raw {
layout := "20060102T150405.999"
if strings.HasSuffix(c.V, "Z") {
layout = "20060102T150405Z"
if strings.Contains(c.V, "-") {
layout = "2006-01-02T15:04:05Z"
}
} else if strings.Contains(c.V, "-") {
layout = "2006-01-02 15:04:05Z"
}
if timestamp, err := time.Parse(layout, strings.ReplaceAll(c.V, ",", ".")); err == nil {
excelTime, _ := timeToExcelTime(timestamp, false)
c.V = strconv.FormatFloat(excelTime, 'G', 15, 64)
}
}
return f.formattedValue(c.S, c.V, raw), nil
}
// getValueFrom return a value from a column/row cell, this function is
// intended to be used with for range on rows an argument with the spreadsheet
// opened file.
func (c *xlsxC) getValueFrom(f *File, d *xlsxSST, raw bool) (string, error) {
f.Lock()
defer f.Unlock()
switch c.T {
case "b":
return c.getCellBool(f, raw)
case "d":
return c.getCellDate(f, raw)
case "s":
if c.V != "" {
xlsxSI := 0
xlsxSI, _ = strconv.Atoi(c.V)
if _, ok := f.tempFiles.Load(defaultXMLPathSharedStrings); ok {
return f.formattedValue(c.S, f.getFromStringItem(xlsxSI), raw), nil
}
if len(d.SI) > xlsxSI {
return f.formattedValue(c.S, d.SI[xlsxSI].String(), raw), nil
}
}
return f.formattedValue(c.S, c.V, raw), nil
case "inlineStr":
if c.IS != nil {
return f.formattedValue(c.S, c.IS.String(), raw), nil
}
return f.formattedValue(c.S, c.V, raw), nil
default:
if isNum, precision, decimal := isNumeric(c.V); isNum && !raw {
if precision > 15 {
c.V = strconv.FormatFloat(decimal, 'G', 15, 64)
} else {
c.V = strconv.FormatFloat(decimal, 'f', -1, 64)
}
}
return f.formattedValue(c.S, c.V, raw), nil
}
}
// SetCellDefault provides a function to set string type value of a cell as
// default format without escaping the cell.
func (f *File) SetCellDefault(sheet, cell, value string) error {
ws, err := f.workSheetReader(sheet)
if err != nil {
return err
}
c, col, row, err := f.prepareCell(ws, cell)
if err != nil {
return err
}
ws.Lock()
defer ws.Unlock()
c.S = f.prepareCellStyle(ws, col, row, c.S)
c.setCellDefault(value)
f.removeFormula(c, ws, sheet)
return err
}
// GetCellFormula provides a function to get formula from cell by given
// worksheet name and cell reference in spreadsheet.
func (f *File) GetCellFormula(sheet, cell string) (string, error) {
return f.getCellStringFunc(sheet, cell, func(x *xlsxWorksheet, c *xlsxC) (string, bool, error) {
if c.F == nil {
return "", false, nil
}
if c.F.T == STCellFormulaTypeShared && c.F.Si != nil {
return getSharedFormula(x, *c.F.Si, c.R), true, nil
}
return c.F.Content, true, nil
})
}
// FormulaOpts can be passed to SetCellFormula to use other formula types.
type FormulaOpts struct {
Type *string // Formula type
Ref *string // Shared formula ref
}
// SetCellFormula provides a function to set formula on the cell is taken
// according to the given worksheet name and cell formula settings. The result
// of the formula cell can be calculated when the worksheet is opened by the
// Office Excel application or can be using the "CalcCellValue" function also
// can get the calculated cell value. If the Excel application doesn't
// calculate the formula automatically when the workbook has been opened,
// please call "UpdateLinkedValue" after setting the cell formula functions.
//
// Example 1, set normal formula "=SUM(A1,B1)" for the cell "A3" on "Sheet1":
//
// err := f.SetCellFormula("Sheet1", "A3", "=SUM(A1,B1)")
//
// Example 2, set one-dimensional vertical constant array (row array) formula
// "1,2,3" for the cell "A3" on "Sheet1":
//
// err := f.SetCellFormula("Sheet1", "A3", "={1,2,3}")
//
// Example 3, set one-dimensional horizontal constant array (column array)
// formula '"a","b","c"' for the cell "A3" on "Sheet1":
//
// err := f.SetCellFormula("Sheet1", "A3", "={\"a\",\"b\",\"c\"}")
//
// Example 4, set two-dimensional constant array formula '{1,2,"a","b"}' for
// the cell "A3" on "Sheet1":
//
// formulaType, ref := excelize.STCellFormulaTypeArray, "A3:A3"
// err := f.SetCellFormula("Sheet1", "A3", "={1,2,\"a\",\"b\"}",
// excelize.FormulaOpts{Ref: &ref, Type: &formulaType})
//
// Example 5, set range array formula "A1:A2" for the cell "A3" on "Sheet1":
//
// formulaType, ref := excelize.STCellFormulaTypeArray, "A3:A3"
// err := f.SetCellFormula("Sheet1", "A3", "=A1:A2",
// excelize.FormulaOpts{Ref: &ref, Type: &formulaType})
//
// Example 6, set shared formula "=A1+B1" for the cell "C1:C5"
// on "Sheet1", "C1" is the master cell:
//
// formulaType, ref := excelize.STCellFormulaTypeShared, "C1:C5"
// err := f.SetCellFormula("Sheet1", "C1", "=A1+B1",
// excelize.FormulaOpts{Ref: &ref, Type: &formulaType})
//
// Example 7, set table formula "=SUM(Table1[[A]:[B]])" for the cell "C2"
// on "Sheet1":
//
// package main
//
// import (
// "fmt"
//
// "github.com/xuri/excelize/v2"
// )
//
// func main() {
// f := excelize.NewFile()
// for idx, row := range [][]interface{}{{"A", "B", "C"}, {1, 2}} {
// if err := f.SetSheetRow("Sheet1", fmt.Sprintf("A%d", idx+1), &row); err != nil {
// fmt.Println(err)
// return
// }
// }
// if err := f.AddTable("Sheet1", "A1", "C2",
// `{"table_name":"Table1","table_style":"TableStyleMedium2"}`); err != nil {
// fmt.Println(err)
// return
// }
// formulaType := excelize.STCellFormulaTypeDataTable
// if err := f.SetCellFormula("Sheet1", "C2", "=SUM(Table1[[A]:[B]])",
// excelize.FormulaOpts{Type: &formulaType}); err != nil {
// fmt.Println(err)
// return
// }
// if err := f.SaveAs("Book1.xlsx"); err != nil {
// fmt.Println(err)
// }
// }
func (f *File) SetCellFormula(sheet, cell, formula string, opts ...FormulaOpts) error {
ws, err := f.workSheetReader(sheet)
if err != nil {
return err
}
c, _, _, err := f.prepareCell(ws, cell)
if err != nil {
return err
}
if formula == "" {
c.F = nil
f.deleteCalcChain(f.getSheetID(sheet), cell)
return err
}
if c.F != nil {
c.F.Content = formula
} else {
c.F = &xlsxF{Content: formula}
}
for _, opt := range opts {
if opt.Type != nil {
if *opt.Type == STCellFormulaTypeDataTable {
return err
}
c.F.T = *opt.Type
if c.F.T == STCellFormulaTypeShared {
if err = ws.setSharedFormula(*opt.Ref); err != nil {
return err
}
}
}
if opt.Ref != nil {
c.F.Ref = *opt.Ref
}
}
c.T, c.IS = "str", nil
return err
}
// setSharedFormula set shared formula for the cells.
func (ws *xlsxWorksheet) setSharedFormula(ref string) error {
coordinates, err := rangeRefToCoordinates(ref)
if err != nil {
return err
}
_ = sortCoordinates(coordinates)
cnt := ws.countSharedFormula()
for c := coordinates[0]; c <= coordinates[2]; c++ {
for r := coordinates[1]; r <= coordinates[3]; r++ {
prepareSheetXML(ws, c, r)
cell := &ws.SheetData.Row[r-1].C[c-1]
if cell.F == nil {
cell.F = &xlsxF{}
}
cell.F.T = STCellFormulaTypeShared
cell.F.Si = &cnt
}
}
return err
}
// countSharedFormula count shared formula in the given worksheet.
func (ws *xlsxWorksheet) countSharedFormula() (count int) {
for _, row := range ws.SheetData.Row {
for _, cell := range row.C {
if cell.F != nil && cell.F.Si != nil && *cell.F.Si+1 > count {
count = *cell.F.Si + 1
}
}
}
return
}
// GetCellHyperLink gets a cell hyperlink based on the given worksheet name and
// cell reference. If the cell has a hyperlink, it will return 'true' and
// the link address, otherwise it will return 'false' and an empty link
// address.
//
// For example, get a hyperlink to a 'H6' cell on a worksheet named 'Sheet1':
//
// link, target, err := f.GetCellHyperLink("Sheet1", "H6")
func (f *File) GetCellHyperLink(sheet, cell string) (bool, string, error) {
// Check for correct cell name
if _, _, err := SplitCellName(cell); err != nil {
return false, "", err
}
ws, err := f.workSheetReader(sheet)
if err != nil {
return false, "", err
}
if cell, err = f.mergeCellsParser(ws, cell); err != nil {
return false, "", err
}
if ws.Hyperlinks != nil {
for _, link := range ws.Hyperlinks.Hyperlink {
if link.Ref == cell {
if link.RID != "" {
return true, f.getSheetRelationshipsTargetByID(sheet, link.RID), err
}
return true, link.Location, err
}
}
}
return false, "", err
}
// HyperlinkOpts can be passed to SetCellHyperlink to set optional hyperlink
// attributes (e.g. display value)
type HyperlinkOpts struct {
Display *string
Tooltip *string
}
// SetCellHyperLink provides a function to set cell hyperlink by given
// worksheet name and link URL address. LinkType defines two types of
// hyperlink "External" for website or "Location" for moving to one of cell in
// this workbook. Maximum limit hyperlinks in a worksheet is 65530. This
// function is only used to set the hyperlink of the cell and doesn't affect
// the value of the cell. If you need to set the value of the cell, please use
// the other functions such as `SetCellStyle` or `SetSheetRow`. The below is
// example for external link.
//
// display, tooltip := "https://github.com/xuri/excelize", "Excelize on GitHub"
// if err := f.SetCellHyperLink("Sheet1", "A3",
// "https://github.com/xuri/excelize", "External", excelize.HyperlinkOpts{
// Display: &display,
// Tooltip: &tooltip,
// }); err != nil {
// fmt.Println(err)
// }
// // Set underline and font color style for the cell.
// style, err := f.NewStyle(&excelize.Style{
// Font: &excelize.Font{Color: "#1265BE", Underline: "single"},
// })
// if err != nil {
// fmt.Println(err)
// }
// err = f.SetCellStyle("Sheet1", "A3", "A3", style)
//
// This is another example for "Location":
//
// err := f.SetCellHyperLink("Sheet1", "A3", "Sheet1!A40", "Location")
func (f *File) SetCellHyperLink(sheet, cell, link, linkType string, opts ...HyperlinkOpts) error {
// Check for correct cell name
if _, _, err := SplitCellName(cell); err != nil {
return err
}
ws, err := f.workSheetReader(sheet)
if err != nil {
return err
}
if cell, err = f.mergeCellsParser(ws, cell); err != nil {
return err
}
var linkData xlsxHyperlink
idx := -1
if ws.Hyperlinks == nil {
ws.Hyperlinks = new(xlsxHyperlinks)
}
for i, hyperlink := range ws.Hyperlinks.Hyperlink {
if hyperlink.Ref == cell {
idx = i
linkData = hyperlink
break
}
}
if len(ws.Hyperlinks.Hyperlink) > TotalSheetHyperlinks {
return ErrTotalSheetHyperlinks
}
switch linkType {
case "External":
sheetPath, _ := f.getSheetXMLPath(sheet)
sheetRels := "xl/worksheets/_rels/" + strings.TrimPrefix(sheetPath, "xl/worksheets/") + ".rels"
rID := f.setRels(linkData.RID, sheetRels, SourceRelationshipHyperLink, link, linkType)
linkData = xlsxHyperlink{
Ref: cell,
}
linkData.RID = "rId" + strconv.Itoa(rID)
f.addSheetNameSpace(sheet, SourceRelationship)
case "Location":
linkData = xlsxHyperlink{
Ref: cell,
Location: link,
}
default:
return fmt.Errorf("invalid link type %q", linkType)
}
for _, o := range opts {
if o.Display != nil {
linkData.Display = *o.Display
}
if o.Tooltip != nil {
linkData.Tooltip = *o.Tooltip
}
}
if idx == -1 {
ws.Hyperlinks.Hyperlink = append(ws.Hyperlinks.Hyperlink, linkData)
return err
}
ws.Hyperlinks.Hyperlink[idx] = linkData
return err
}
// getCellRichText returns rich text of cell by given string item.
func getCellRichText(si *xlsxSI) (runs []RichTextRun) {
for _, v := range si.R {
run := RichTextRun{
Text: v.T.Val,
}
if v.RPr != nil {
font := Font{Underline: "none"}
font.Bold = v.RPr.B != nil
font.Italic = v.RPr.I != nil
if v.RPr.U != nil {
font.Underline = "single"
if v.RPr.U.Val != nil {
font.Underline = *v.RPr.U.Val
}
}
if v.RPr.RFont != nil && v.RPr.RFont.Val != nil {
font.Family = *v.RPr.RFont.Val
}
if v.RPr.Sz != nil && v.RPr.Sz.Val != nil {
font.Size = *v.RPr.Sz.Val
}
font.Strike = v.RPr.Strike != nil
if v.RPr.Color != nil {
font.Color = strings.TrimPrefix(v.RPr.Color.RGB, "FF")
if v.RPr.Color.Theme != nil {
font.ColorTheme = v.RPr.Color.Theme
}
font.ColorIndexed = v.RPr.Color.Indexed
font.ColorTint = v.RPr.Color.Tint
}
run.Font = &font
}
runs = append(runs, run)
}
return
}
// GetCellRichText provides a function to get rich text of cell by given
// worksheet.
func (f *File) GetCellRichText(sheet, cell string) (runs []RichTextRun, err error) {
ws, err := f.workSheetReader(sheet)
if err != nil {
return
}
c, _, _, err := f.prepareCell(ws, cell)
if err != nil {
return
}
siIdx, err := strconv.Atoi(c.V)
if err != nil || c.T != "s" {
return
}
sst := f.sharedStringsReader()
if len(sst.SI) <= siIdx || siIdx < 0 {
return
}
runs = getCellRichText(&sst.SI[siIdx])
return
}
// newRpr create run properties for the rich text by given font format.
func newRpr(fnt *Font) *xlsxRPr {
rpr := xlsxRPr{}
trueVal := ""
if fnt.Bold {
rpr.B = &trueVal
}
if fnt.Italic {
rpr.I = &trueVal
}
if fnt.Strike {
rpr.Strike = &trueVal
}
if fnt.Underline != "" {
rpr.U = &attrValString{Val: &fnt.Underline}
}
if fnt.Family != "" {
rpr.RFont = &attrValString{Val: &fnt.Family}
}
if inStrSlice([]string{"baseline", "superscript", "subscript"}, fnt.VertAlign, true) != -1 {
rpr.VertAlign = &attrValString{Val: &fnt.VertAlign}
}
if fnt.Size > 0 {
rpr.Sz = &attrValFloat{Val: &fnt.Size}
}
rpr.Color = newFontColor(fnt)
return &rpr
}
// setRichText provides a function to set rich text of a cell.
func setRichText(runs []RichTextRun) ([]xlsxR, error) {
var (
textRuns []xlsxR
totalCellChars int
)
for _, textRun := range runs {
totalCellChars += len(textRun.Text)
if totalCellChars > TotalCellChars {
return textRuns, ErrCellCharsLength
}
run := xlsxR{T: &xlsxT{}}
run.T.Val, run.T.Space = trimCellValue(textRun.Text)
fnt := textRun.Font
if fnt != nil {
run.RPr = newRpr(fnt)
}
textRuns = append(textRuns, run)
}
return textRuns, nil
}
// SetCellRichText provides a function to set cell with rich text by given
// worksheet. For example, set rich text on the A1 cell of the worksheet named
// Sheet1:
//
// package main
//
// import (
// "fmt"
//
// "github.com/xuri/excelize/v2"
// )
//
// func main() {
// f := excelize.NewFile()
// if err := f.SetRowHeight("Sheet1", 1, 35); err != nil {
// fmt.Println(err)
// return
// }
// if err := f.SetColWidth("Sheet1", "A", "A", 44); err != nil {
// fmt.Println(err)
// return
// }
// if err := f.SetCellRichText("Sheet1", "A1", []excelize.RichTextRun{
// {
// Text: "bold",
// Font: &excelize.Font{
// Bold: true,
// Color: "2354e8",
// Family: "Times New Roman",
// },
// },
// {
// Text: " and ",
// Font: &excelize.Font{
// Family: "Times New Roman",
// },
// },
// {
// Text: "italic ",
// Font: &excelize.Font{
// Bold: true,
// Color: "e83723",
// Italic: true,
// Family: "Times New Roman",
// },
// },
// {
// Text: "text with color and font-family,",
// Font: &excelize.Font{
// Bold: true,
// Color: "2354e8",
// Family: "Times New Roman",
// },
// },
// {
// Text: "\r\nlarge text with ",
// Font: &excelize.Font{
// Size: 14,
// Color: "ad23e8",
// },
// },
// {
// Text: "strike",
// Font: &excelize.Font{
// Color: "e89923",
// Strike: true,
// },
// },
// {
// Text: " superscript",
// Font: &excelize.Font{
// Color: "dbc21f",
// VertAlign: "superscript",
// },
// },
// {
// Text: " and ",
// Font: &excelize.Font{
// Size: 14,
// Color: "ad23e8",
// VertAlign: "baseline",
// },
// },
// {
// Text: "underline",
// Font: &excelize.Font{
// Color: "23e833",
// Underline: "single",
// },
// },
// {
// Text: " subscript.",
// Font: &excelize.Font{
// Color: "017505",
// VertAlign: "subscript",
// },
// },
// }); err != nil {
// fmt.Println(err)
// return
// }
// style, err := f.NewStyle(&excelize.Style{
// Alignment: &excelize.Alignment{
// WrapText: true,
// },
// })
// if err != nil {
// fmt.Println(err)
// return
// }
// if err := f.SetCellStyle("Sheet1", "A1", "A1", style); err != nil {
// fmt.Println(err)
// return
// }
// if err := f.SaveAs("Book1.xlsx"); err != nil {
// fmt.Println(err)
// }
// }
func (f *File) SetCellRichText(sheet, cell string, runs []RichTextRun) error {
ws, err := f.workSheetReader(sheet)
if err != nil {
return err
}
c, col, row, err := f.prepareCell(ws, cell)
if err != nil {
return err
}
if err := f.sharedStringsLoader(); err != nil {
return err
}
c.S = f.prepareCellStyle(ws, col, row, c.S)
si, sst := xlsxSI{}, f.sharedStringsReader()
if si.R, err = setRichText(runs); err != nil {
return err
}
for idx, strItem := range sst.SI {
if reflect.DeepEqual(strItem, si) {
c.T, c.V = "s", strconv.Itoa(idx)
return err
}
}
sst.SI = append(sst.SI, si)
sst.Count++
sst.UniqueCount++
c.T, c.V = "s", strconv.Itoa(len(sst.SI)-1)
return err
}
// SetSheetRow writes an array to row by given worksheet name, starting
// cell reference and a pointer to array type 'slice'. This function is
// concurrency safe. For example, writes an array to row 6 start with the cell
// B6 on Sheet1:
//
// err := f.SetSheetRow("Sheet1", "B6", &[]interface{}{"1", nil, 2})
func (f *File) SetSheetRow(sheet, cell string, slice interface{}) error {
return f.setSheetCells(sheet, cell, slice, rows)
}
// SetSheetCol writes an array to column by given worksheet name, starting
// cell reference and a pointer to array type 'slice'. For example, writes an
// array to column B start with the cell B6 on Sheet1:
//
// err := f.SetSheetCol("Sheet1", "B6", &[]interface{}{"1", nil, 2})
func (f *File) SetSheetCol(sheet, cell string, slice interface{}) error {
return f.setSheetCells(sheet, cell, slice, columns)
}
// setSheetCells provides a function to set worksheet cells value.
func (f *File) setSheetCells(sheet, cell string, slice interface{}, dir adjustDirection) error {
col, row, err := CellNameToCoordinates(cell)
if err != nil {
return err
}
// Make sure 'slice' is a Ptr to Slice
v := reflect.ValueOf(slice)
if v.Kind() != reflect.Ptr || v.Elem().Kind() != reflect.Slice {
return ErrParameterInvalid
}
v = v.Elem()
for i := 0; i < v.Len(); i++ {
var cell string
var err error
if dir == rows {
cell, err = CoordinatesToCellName(col+i, row)
} else {
cell, err = CoordinatesToCellName(col, row+i)
}
// Error should never happen here. But keep checking to early detect regressions
// if it will be introduced in the future.
if err != nil {
return err
}
if err := f.SetCellValue(sheet, cell, v.Index(i).Interface()); err != nil {
return err
}
}
return err
}
// getCellInfo does common preparation for all set cell value functions.
func (f *File) prepareCell(ws *xlsxWorksheet, cell string) (*xlsxC, int, int, error) {
var err error
cell, err = f.mergeCellsParser(ws, cell)
if err != nil {
return nil, 0, 0, err
}
col, row, err := CellNameToCoordinates(cell)
if err != nil {
return nil, 0, 0, err
}
prepareSheetXML(ws, col, row)
ws.Lock()
defer ws.Unlock()
return &ws.SheetData.Row[row-1].C[col-1], col, row, err
}
// getCellStringFunc does common value extraction workflow for all get cell
// value function. Passed function implements specific part of required
// logic.
func (f *File) getCellStringFunc(sheet, cell string, fn func(x *xlsxWorksheet, c *xlsxC) (string, bool, error)) (string, error) {
ws, err := f.workSheetReader(sheet)
if err != nil {
return "", err
}
cell, err = f.mergeCellsParser(ws, cell)
if err != nil {
return "", err
}
_, row, err := CellNameToCoordinates(cell)
if err != nil {
return "", err
}
ws.Lock()
defer ws.Unlock()
lastRowNum := 0
if l := len(ws.SheetData.Row); l > 0 {
lastRowNum = ws.SheetData.Row[l-1].R
}
// keep in mind: row starts from 1
if row > lastRowNum {
return "", nil
}
for rowIdx := range ws.SheetData.Row {
rowData := &ws.SheetData.Row[rowIdx]
if rowData.R != row {
continue
}
for colIdx := range rowData.C {
colData := &rowData.C[colIdx]
if cell != colData.R {
continue
}
val, ok, err := fn(ws, colData)
if err != nil {
return "", err
}
if ok {
return val, nil
}
}
}
return "", nil
}
// formattedValue provides a function to returns a value after formatted. If
// it is possible to apply a format to the cell value, it will do so, if not
// then an error will be returned, along with the raw value of the cell.
func (f *File) formattedValue(s int, v string, raw bool) string {
if raw {
return v
}
if s == 0 {
return v
}
styleSheet := f.stylesReader()
if s >= len(styleSheet.CellXfs.Xf) {
return v
}
var numFmtID int
if styleSheet.CellXfs.Xf[s].NumFmtID != nil {
numFmtID = *styleSheet.CellXfs.Xf[s].NumFmtID
}
date1904, wb := false, f.workbookReader()
if wb != nil && wb.WorkbookPr != nil {
date1904 = wb.WorkbookPr.Date1904
}
if ok := builtInNumFmtFunc[numFmtID]; ok != nil {
return ok(v, builtInNumFmt[numFmtID], date1904)
}
if styleSheet == nil || styleSheet.NumFmts == nil {
return v
}
for _, xlsxFmt := range styleSheet.NumFmts.NumFmt {
if xlsxFmt.NumFmtID == numFmtID {
return format(v, xlsxFmt.FormatCode, date1904)
}
}
return v
}
// prepareCellStyle provides a function to prepare style index of cell in
// worksheet by given column index and style index.
func (f *File) prepareCellStyle(ws *xlsxWorksheet, col, row, style int) int {
if style != 0 {
return style
}
if row <= len(ws.SheetData.Row) {
if styleID := ws.SheetData.Row[row-1].S; styleID != 0 {
return styleID
}
}
if ws.Cols != nil {
for _, c := range ws.Cols.Col {
if c.Min <= col && col <= c.Max && c.Style != 0 {
return c.Style
}
}
}
return style
}
// mergeCellsParser provides a function to check merged cells in worksheet by
// given cell reference.
func (f *File) mergeCellsParser(ws *xlsxWorksheet, cell string) (string, error) {
cell = strings.ToUpper(cell)
if ws.MergeCells != nil {
for i := 0; i < len(ws.MergeCells.Cells); i++ {
if ws.MergeCells.Cells[i] == nil {
ws.MergeCells.Cells = append(ws.MergeCells.Cells[:i], ws.MergeCells.Cells[i+1:]...)
i--
continue
}
ok, err := f.checkCellInRangeRef(cell, ws.MergeCells.Cells[i].Ref)
if err != nil {
return cell, err
}
if ok {
cell = strings.Split(ws.MergeCells.Cells[i].Ref, ":")[0]
}
}
}
return cell, nil
}
// checkCellInRangeRef provides a function to determine if a given cell reference
// in a range.
func (f *File) checkCellInRangeRef(cell, reference string) (bool, error) {
col, row, err := CellNameToCoordinates(cell)
if err != nil {
return false, err
}
if rng := strings.Split(reference, ":"); len(rng) != 2 {
return false, err
}
coordinates, err := rangeRefToCoordinates(reference)
if err != nil {
return false, err
}
return cellInRef([]int{col, row}, coordinates), err
}
// cellInRef provides a function to determine if a given range is within a
// range.
func cellInRef(cell, ref []int) bool {
return cell[0] >= ref[0] && cell[0] <= ref[2] && cell[1] >= ref[1] && cell[1] <= ref[3]
}
// isOverlap find if the given two rectangles overlap or not.
func isOverlap(rect1, rect2 []int) bool {
return cellInRef([]int{rect1[0], rect1[1]}, rect2) ||
cellInRef([]int{rect1[2], rect1[1]}, rect2) ||
cellInRef([]int{rect1[0], rect1[3]}, rect2) ||
cellInRef([]int{rect1[2], rect1[3]}, rect2) ||
cellInRef([]int{rect2[0], rect2[1]}, rect1) ||
cellInRef([]int{rect2[2], rect2[1]}, rect1) ||
cellInRef([]int{rect2[0], rect2[3]}, rect1) ||
cellInRef([]int{rect2[2], rect2[3]}, rect1)
}
// parseSharedFormula generate dynamic part of shared formula for target cell
// by given column and rows distance and origin shared formula.
func parseSharedFormula(dCol, dRow int, orig []byte) (res string, start int) {
var (
end int
stringLiteral bool
)
for end = 0; end < len(orig); end++ {
c := orig[end]
if c == '"' {
stringLiteral = !stringLiteral
}
if stringLiteral {
continue // Skip characters in quotes
}
if c >= 'A' && c <= 'Z' || c == '$' {
res += string(orig[start:end])
start = end
end++
foundNum := false
for ; end < len(orig); end++ {
idc := orig[end]
if idc >= '0' && idc <= '9' || idc == '$' {
foundNum = true
} else if idc >= 'A' && idc <= 'Z' {
if foundNum {
break
}
} else {
break
}
}
if foundNum {
cellID := string(orig[start:end])
res += shiftCell(cellID, dCol, dRow)
start = end
}
}
}
return
}
// getSharedFormula find a cell contains the same formula as another cell,
// the "shared" value can be used for the t attribute and the si attribute can
// be used to refer to the cell containing the formula. Two formulas are
// considered to be the same when their respective representations in
// R1C1-reference notation, are the same.
//
// Note that this function not validate ref tag to check the cell whether in
// allow range reference, and always return origin shared formula.
func getSharedFormula(ws *xlsxWorksheet, si int, cell string) string {
for _, r := range ws.SheetData.Row {
for _, c := range r.C {
if c.F != nil && c.F.Ref != "" && c.F.T == STCellFormulaTypeShared && c.F.Si != nil && *c.F.Si == si {
col, row, _ := CellNameToCoordinates(cell)
sharedCol, sharedRow, _ := CellNameToCoordinates(c.R)
dCol := col - sharedCol
dRow := row - sharedRow
orig := []byte(c.F.Content)
res, start := parseSharedFormula(dCol, dRow, orig)
if start < len(orig) {
res += string(orig[start:])
}
return res
}
}
}
return ""
}
// shiftCell returns the cell shifted according to dCol and dRow taking into
// consideration absolute references with dollar sign ($)
func shiftCell(cellID string, dCol, dRow int) string {
fCol, fRow, _ := CellNameToCoordinates(cellID)
signCol, signRow := "", ""
if strings.Index(cellID, "$") == 0 {
signCol = "$"
} else {
// Shift column
fCol += dCol
}
if strings.LastIndex(cellID, "$") > 0 {
signRow = "$"
} else {
// Shift row
fRow += dRow
}
colName, _ := ColumnNumberToName(fCol)
return signCol + colName + signRow + strconv.Itoa(fRow)
}
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
马建仓 AI 助手
