- 1.8.x

- 1.9.x

- 1.10.x

os:

- linux

## Introduction

With Excelize chart generation and management is as easy as a few lines of code. You can build charts based off data in your worksheet or generate charts without any data in your worksheet at all.

```go

package main

## 简介

使用 Excelize 生成图表十分简单，仅需几行代码。您可以根据工作表中的已有数据构建图表，或向工作表中添加数据并创建图表。

```go

package main

package excelize

import (

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.T == STCellFormulaTypeShared {

return getSharedForumula(xlsx, xlsx.SheetData.Row[k].C[i].F.Si)

}

}

}

}

package excelize

import (

_, ok := f.XLSX[drawingXML]

if ok { // Append Model

decodeWsDr := decodeWsDr{}

content.R = decodeWsDr.R

cNvPrID = len(decodeWsDr.OneCellAnchor) + len(decodeWsDr.TwoCellAnchor) + 1

for _, v := range decodeWsDr.OneCellAnchor {

package excelize

import (

f.adjustHelper(sheet, col, -1, -1)

}

func completeCol(xlsx *xlsxWorksheet, row, cell int) {

buffer := bytes.Buffer{}

for r := range xlsx.SheetData.Row {

package excelize

import (

// GetComments retrieves all comments and returns a map of worksheet name to

// the worksheet comments.

for n := range f.sheetMap {

commentID := f.GetSheetIndex(n)

commentsXML := "xl/comments" + strconv.Itoa(commentID) + ".xml"

if ok {

d := xlsxComments{}

xml.Unmarshal([]byte(c), &d)

}

}

return

c, ok := f.XLSX[drawingVML]

if ok {

d := decodeVmlDrawing{}

for _, v := range d.Shape {

s := xlsxShape{

ID: "_x0000_s1025",

c, ok := f.XLSX[commentsXML]

if ok {

d := xlsxComments{}

comments.CommentList.Comment = append(comments.CommentList.Comment, d.CommentList.Comment...)

}

comments.CommentList.Comment = append(comments.CommentList.Comment, cmt)

package excelize

import (

return time.Date(year, time.Month(month), day, hours, minutes, seconds, nanoseconds, time.UTC)

}

func doTheFliegelAndVanFlandernAlgorithm(jd int) (day, month, year int) {

l := jd + 68569

n := (4 * l) / 146097

package excelize

import (

)

type dateTest struct {

}

func TestTimeToExcelTime(t *testing.T) {

}

func TestTimeFromExcelTime(t *testing.T) {

}

package excelize

import (

}

if f.Sheet[name] == nil {

var xlsx xlsxWorksheet

if f.checked == nil {

f.checked = make(map[string]bool)

}

t.Error(err)

}

// Test get all the rows in a not exists worksheet.

// Test get all the rows in a worksheet.

for _, row := range rows {

for _, cell := range row {

t.Log(cell, "\t")

}

err = xlsx.Save()

if err != nil {

}

// Test write file to not exist directory.

err = xlsx.SaveAs("")

if err != nil {

t.Log(err)

}

// Test write file to given path.

err = xlsx.SaveAs("./test/Book2.xlsx")

if err != nil {

if err != nil {

t.Error(err)

}

err = xlsx.AddPicture("Sheet1", "C2", "./test/images/excel.png", "")

if err != nil {

t.Log(err)

}

err = xlsx.SaveAs("./test/Book3.xlsx")

xlsx.SetCellValue("Sheet1", "A1", 42920.5)

xlsx.SetCellValue("Sheet1", "A2", 42920.5)

if err != nil {

t.Log(err)

}

t.Log(RGBToHSL(250, 50, 100))

}

func trimSliceSpace(s []string) []string {

for {

if len(s) > 0 && s[len(s)-1] == "" {

package excelize

import (

// Save provides a function to override the xlsx file with origin path.

func (f *File) Save() error {

if f.Path == "" {

}

return f.SaveAs(f.Path)

}

// WriteTo implements io.WriterTo to write the file.

func (f *File) WriteTo(w io.Writer) (int64, error) {

buf := new(bytes.Buffer)

zw := zip.NewWriter(buf)

f.contentTypesWriter()

for path, content := range f.XLSX {

fi, err := zw.Create(path)

if err != nil {

}

_, err = fi.Write(content)

if err != nil {

}

}

}

package excelize

package excelize

import (

"io"

"log"

"math"

"unicode"

)

}

return []byte("{}")

}

package excelize

import (

// positioning is move and size with cells.

func (f *File) AddPicture(sheet, cell, picture, format string) error {

var err error

// Check picture exists first.

if _, err = os.Stat(picture); os.IsNotExist(err) {

return err

}

ext, ok := supportImageTypes[path.Ext(picture)]

if !ok {

}

}

formatSet, err := parseFormatPictureSet(format)

if err != nil {

return err

}

// Read sheet data.

xlsx := f.workSheetReader(sheet)

// Add first picture for given sheet, create xl/drawings/ and xl/drawings/_rels/ folder.

}

drawingHyperlinkRID = f.addDrawingRelationships(drawingID, SourceRelationshipHyperLink, formatSet.Hyperlink, hyperlinkType)

}

f.addContentTypePart(drawingID, "drawings")

return err

}

_, ok = f.XLSX[rels]

if ok {

ID.Reset()

rID = len(sheetRels.Relationships) + 1

ID.WriteString("rId")

ID.WriteString(strconv.Itoa(rID))

}

var rels = "xl/worksheets/_rels/" + strings.TrimPrefix(name, "xl/worksheets/") + ".rels"

var sheetRels xlsxWorkbookRels

for k, v := range sheetRels.Relationships {

if v.ID == rID {

sheetRels.Relationships = append(sheetRels.Relationships[:k], sheetRels.Relationships[k+1:]...)

_, ok := f.XLSX[rels]

if ok {

ID.Reset()

rID = len(drawingRels.Relationships) + 1

ID.WriteString("rId")

ID.WriteString(strconv.Itoa(rID))

}

// addMedia provides a function to add picture into folder xl/media/image by

count := f.countMedia()

media := "xl/media/image" + strconv.Itoa(count+1) + ext

}

// setContentTypePartImageExtensions provides a function to set the content

}

var rels = "xl/worksheets/_rels/" + strings.TrimPrefix(name, "xl/worksheets/") + ".rels"

var sheetRels xlsxWorkbookRels

for _, v := range sheetRels.Relationships {

if v.ID == rID {

return v.Target

return "", nil

}

decodeWsDr := decodeWsDr{}

cell = strings.ToUpper(cell)

fromCol := string(strings.Map(letterOnlyMapF, cell))

return nil

}

var drawingRels xlsxWorkbookRels

for _, v := range drawingRels.Relationships {

if v.ID == rID {

return &v

package excelize

import (

if len(ss) == 0 {

ss = f.readXML("xl/SharedStrings.xml")

}

f.SharedStrings = &sharedStrings

}

return f.SharedStrings

package excelize

import (

package excelize

import (

"encoding/json"

"encoding/xml"

"errors"

"os"

"path"

"strconv"

"github.com/mohae/deepcopy"

)

func (f *File) NewSheet(name string) int {

// Check if the worksheet already exists

if f.GetSheetIndex(name) != 0 {

return f.SheetCount

}

f.SheetCount++

// Update docProps/app.xml

f.setAppXML()

// Update [Content_Types].xml

// Create new sheet /xl/worksheets/sheet%d.xml

// Update xl/_rels/workbook.xml.rels

// Update xl/workbook.xml

}

// contentTypesReader provides a function to get the pointer to the

func (f *File) contentTypesReader() *xlsxTypes {

if f.ContentTypes == nil {

var content xlsxTypes

f.ContentTypes = &content

}

return f.ContentTypes

func (f *File) workbookReader() *xlsxWorkbook {

if f.WorkBook == nil {

var content xlsxWorkbook

f.WorkBook = &content

}

return f.WorkBook

return col[0:i]

}

func (f *File) setContentTypes(index int) {

content := f.contentTypesReader()

content.Overrides = append(content.Overrides, xlsxOverride{

})

}

func (f *File) setSheet(index int, name string) {

var xlsx xlsxWorksheet

xlsx.Dimension.Ref = "A1"

// setWorkbook update workbook property of XLSX. Maximum 31 characters are

// allowed in sheet title.

content := f.workbookReader()

content.Sheets.Sheet = append(content.Sheets.Sheet, xlsxSheet{

Name: trimSheetName(name),

ID: "rId" + strconv.Itoa(rid),

})

}

func (f *File) workbookRelsReader() *xlsxWorkbookRels {

if f.WorkBookRels == nil {

var content xlsxWorkbookRels

f.WorkBookRels = &content

}

return f.WorkBookRels

f.saveFileList("docProps/app.xml", []byte(templateDocpropsApp))

}

func replaceRelationshipsNameSpaceBytes(workbookMarshal []byte) []byte {

oldXmlns := []byte(`<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">`)

newXmlns := []byte(`<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x15" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main">`)

return bytes.Replace(workbookMarshal, oldXmlns, newXmlns, -1)

}

// worksheet numbers.

func (f *File) SetActiveSheet(index int) {

if index < 1 {

index = 1

}

}

for idx, name := range f.GetSheetMap() {

xlsx := f.workSheetReader(name)

if index == idx {

if len(xlsx.SheetViews.SheetView) > 0 {

xlsx.SheetViews.SheetView[0].TabSelected = true

TabSelected: true,

})

}

}

}

}

func (f *File) GetActiveSheetIndex() int {

for _, sheetView := range xlsx.SheetViews.SheetView {

if sheetView.TabSelected {

}

}

}

return 0

}

sheetMap := map[int]string{}

for _, v := range content.Sheets.Sheet {

for _, rel := range rels.Relationships {

sheetMap[rID] = v.Name

}

}

}

// SetSheetBackground provides a function to set background picture by given

func (f *File) SetSheetBackground(sheet, picture string) error {

var err error

// Check picture exists first.

}

ext, ok := supportImageTypes[path.Ext(picture)]

if !ok {

}

pictureID := f.countMedia() + 1

rID := f.addSheetRelationships(sheet, SourceRelationshipImage, "../media/image"+strconv.Itoa(pictureID)+ext, "")

f.addSheetPicture(sheet, rID)

f.setContentTypePartImageExtensions()

return err

}

for k, v := range content.Sheets.Sheet {

if v.Name == trimSheetName(name) && len(content.Sheets.Sheet) > 1 {

content.Sheets.Sheet = append(content.Sheets.Sheet[:k], content.Sheets.Sheet[k+1:]...)

target := f.deleteSheetFromWorkbookRels(v.ID)

f.deleteSheetFromContentTypes(target)

delete(f.sheetMap, name)

//

func (f *File) CopySheet(from, to int) error {

if from < 1 || to < 1 || from == to || f.GetSheetName(from) == "" || f.GetSheetName(to) == "" {

}

f.copySheet(from, to)

return nil

return visible

}

// trimSheetName provides a function to trim invaild characters by given worksheet

// name.

func trimSheetName(name string) string {

package excelize

// SheetPrOption is an option of a view of a worksheet. See SetSheetPrOptions().

FitToPage bool

// AutoPageBreaks is a SheetPrOption

AutoPageBreaks bool

)

func (o CodeName) setSheetPrOption(pr *xlsxSheetPr) {

pr.CodeName = string(o)

}

// Published(bool)

// FitToPage(bool)

// AutoPageBreaks(bool)

func (f *File) SetSheetPrOptions(name string, opts ...SheetPrOption) error {

sheet := f.workSheetReader(name)

pr := sheet.SheetPr

// Published(bool)

// FitToPage(bool)

// AutoPageBreaks(bool)

func (f *File) GetSheetPrOptions(name string, opts ...SheetPrOptionPtr) error {

sheet := f.workSheetReader(name)

pr := sheet.SheetPr

excelize.Published(false),

excelize.FitToPage(true),

excelize.AutoPageBreaks(true),

}

var _ = []excelize.SheetPrOptionPtr{

(*excelize.Published)(nil),

(*excelize.FitToPage)(nil),

(*excelize.AutoPageBreaks)(nil),

}

func ExampleFile_SetSheetPrOptions() {

excelize.Published(false),

excelize.FitToPage(true),

excelize.AutoPageBreaks(true),

); err != nil {

panic(err)

}

published excelize.Published

fitToPage excelize.FitToPage

autoPageBreaks excelize.AutoPageBreaks

)

if err := xl.GetSheetPrOptions(sheet,

&published,

&fitToPage,

&autoPageBreaks,

); err != nil {

panic(err)

}

fmt.Println("- published:", published)

fmt.Println("- fitToPage:", fitToPage)

fmt.Println("- autoPageBreaks:", autoPageBreaks)

// Output:

// Defaults:

// - codeName: ""

// - published: true

// - fitToPage: false

// - autoPageBreaks: false

}

func TestSheetPrOptions(t *testing.T) {

{new(excelize.Published), excelize.Published(false)},

{new(excelize.FitToPage), excelize.FitToPage(true)},

{new(excelize.AutoPageBreaks), excelize.AutoPageBreaks(true)},

} {

opt := test.nonDefault

t.Logf("option %T", opt)

package excelize

import "fmt"

ShowRowColHeaders bool

// ZoomScale is a SheetViewOption.

ZoomScale float64

/* TODO

// ShowWhiteSpace is a SheetViewOption.

ShowWhiteSpace bool

// Defaults for each option are described in XML schema for CT_SheetView

func (o DefaultGridColor) setSheetViewOption(view *xlsxSheetView) {

view.DefaultGridColor = boolPtr(bool(o))

}

excelize.ShowFormulas(false),

excelize.ShowGridLines(true),

excelize.ShowRowColHeaders(true),

// SheetViewOptionPtr are also SheetViewOption

new(excelize.DefaultGridColor),

new(excelize.RightToLeft),

new(excelize.ShowFormulas),

new(excelize.ShowGridLines),

new(excelize.ShowRowColHeaders),

}

var _ = []excelize.SheetViewOptionPtr{

(*excelize.ShowFormulas)(nil),

(*excelize.ShowGridLines)(nil),

(*excelize.ShowRowColHeaders)(nil),

}

func ExampleFile_SetSheetViewOptions() {

excelize.ShowGridLines(true),

excelize.ShowRowColHeaders(true),

excelize.ZoomScale(80),

); err != nil {

panic(err)

}

showGridLines excelize.ShowGridLines

showRowColHeaders excelize.ShowRowColHeaders

zoomScale excelize.ZoomScale

)

if err := xl.GetSheetViewOptions(sheet, 0,

&showGridLines,

&showRowColHeaders,

&zoomScale,

); err != nil {

panic(err)

}

fmt.Println("- showGridLines:", showGridLines)

fmt.Println("- showRowColHeaders:", showRowColHeaders)

fmt.Println("- zoomScale:", zoomScale)

if err := xl.SetSheetViewOptions(sheet, 0, excelize.ShowGridLines(false)); err != nil {

panic(err)

fmt.Println("After change:")

fmt.Println("- showGridLines:", showGridLines)

// Output:

// Default:

// - showGridLines: true

// - showRowColHeaders: true

// - zoomScale: 0

// After change:

// - showGridLines: false

}

func TestSheetViewOptionsErrors(t *testing.T) {

package excelize

import (

// criteriaType defined the list of valid criteria types.

var criteriaType = map[string]string{

"greater than or equal to": "greaterThanOrEqual",

}

// formatToString provides a function to return original string by given

func (f *File) stylesReader() *xlsxStyleSheet {

if f.Styles == nil {

var styleSheet xlsxStyleSheet

f.Styles = &styleSheet

}

return f.Styles

// structure after deserialization.

func (f *File) themeReader() *xlsxTheme {

var theme xlsxTheme

return &theme

}

package excelize

import (

col := TitleToNumber(formatSet.Column)

offset := col - hxAxis

if offset < 0 || offset > refRange {

}

filter.FilterColumn = &xlsxFilterColumn{

ColID: offset,

re := regexp.MustCompile(`"(?:[^"]|"")*"|\S+`)

token := re.FindAllString(formatSet.Expression, -1)

if len(token) != 3 && len(token) != 7 {

}

expressions, tokens, err := f.parseFilterExpression(formatSet.Expression, token)

if err != nil {

operator, ok := operators[strings.ToLower(tokens[1])]

if !ok {

// Convert the operator from a number to a descriptive string.

}

token := tokens[2]

// Special handling for Blanks/NonBlanks.

if re {

// Only allow Equals or NotEqual in this context.

if operator != 2 && operator != 5 {

}

token = strings.ToLower(token)

// The operator should always be 2 (=) to flag a "simple" equality in

// This file contains default templates for XML files we don't yet populated

// based on content.

package excelize

import "encoding/xml"

package excelize

import "encoding/xml"

Algn string `xml:"algn,attr,omitempty"`

Cap string `xml:"cap,attr,omitempty"`

Cmpd string `xml:"cmpd,attr,omitempty"`

NoFill string `xml:"a:noFill,omitempty"`

Round string `xml:"a:round,omitempty"`

SolidFill *aSolidFill `xml:"a:solidFill"`

} `json:"line"`

Marker struct {

Type string `json:"type"`

Width float64 `json:"width"`

Border struct {

Color string `json:"color"`

package excelize

import "encoding/xml"

Author string `json:"author"`

Text string `json:"text"`

}

package excelize

import "encoding/xml"

package excelize

import "encoding/xml"

package excelize

import "encoding/xml"

// Source relationship and namespace.

const (

)

var supportImageTypes = map[string]string{".gif": ".gif", ".jpg": ".jpeg", ".jpeg": ".jpeg", ".png": ".png"}

package excelize

import "encoding/xml"

package excelize

import "encoding/xml"

package excelize

import "encoding/xml"

package excelize

import "encoding/xml"

package excelize

import "encoding/xml"

// not checked it for completeness - it does as much as I need.

type xlsxSheet struct {

Name string `xml:"name,attr,omitempty"`

ID string `xml:"http://schemas.openxmlformats.org/officeDocument/2006/relationships id,attr,omitempty"`

State string `xml:"state,attr,omitempty"`

}

package excelize

import "encoding/xml"

MergeCells *xlsxMergeCells `xml:"mergeCells"`

PhoneticPr *xlsxPhoneticPr `xml:"phoneticPr"`

ConditionalFormatting []*xlsxConditionalFormatting `xml:"conditionalFormatting"`

Hyperlinks *xlsxHyperlinks `xml:"hyperlinks"`

PrintOptions *xlsxPrintOptions `xml:"printOptions"`

PageMargins *xlsxPageMargins `xml:"pageMargins"`

TransitionEntry bool `xml:"transitionEntry,attr,omitempty"`

TabColor *xlsxTabColor `xml:"tabColor,omitempty"`

PageSetUpPr *xlsxPageSetUpPr `xml:"pageSetUpPr,omitempty"`

}

// xlsxPageSetUpPr directly maps the pageSetupPr element in the namespace

// xlsxDataValidations expresses all data validation information for cells in a

// sheet which have data validation features applied.

type xlsxDataValidations struct {

}

// xlsxC directly maps the c element in the namespace

// xlsxSheetProtection collection expresses the sheet protection options to

// enforce when the sheet is protected.

type xlsxSheetProtection struct {

}

// xlsxPhoneticPr (Phonetic Properties) represents a collection of phonetic

MultiRange string `json:"multi_range,omitempty"`

BarColor string `json:"bar_color,omitempty"`

}