xlsx

README

Xlsx2Go

Note: Github repository was renamed from xlsx to xlsx2go to make it more easier to distinct existing xlsx libraries. Previous address will be auto redirected, package will be named as before - xlsx.

package main

import (
	"fmt"
	"github.com/plandem/xlsx"
	"github.com/plandem/xlsx/format/conditional"
	"github.com/plandem/xlsx/format/conditional/rule"
	"github.com/plandem/xlsx/format/styles"
	"github.com/plandem/xlsx/types"
	"github.com/plandem/xlsx/types/comment"
	"github.com/plandem/xlsx/types/hyperlink"
)

func main() {
	xl, err := xlsx.Open("./test_files/example_simple.xlsx")
	if err != nil {
		panic(err)
	}

	defer xl.Close()

	redBoldYellow := xl.AddStyles(
		styles.New(
			styles.Font.Bold,
			styles.Font.Color("#ff0000"),
			styles.Fill.Type(styles.PatternTypeSolid),
			styles.Fill.Color("#FFFF00"),
		),
	)

	//iterating via indexes
	sheet := xl.Sheet(0)
	iMaxCol, iMaxRow := sheet.Dimension()
	for iRow := 0; iRow < iMaxRow; iRow++ {
		for iCol := 0; iCol < iMaxCol; iCol++ {
			if iRow % 2 == 0 && iCol % 2 == 0 {
				cell := sheet.Cell(iCol, iRow)
				cell.SetStyles(redBoldYellow)
			}
		}
	}

	//iterating via iterators
	for rows := sheet.Rows(); rows.HasNext(); {
		_, row := rows.Next()
		
		for cells := row.Cells(); cells.HasNext(); {
			iCol, iRow, cell := cells.Next()
			if iRow % 2 == 0 && iCol % 2 == 0 {
				cell.SetStyles(redBoldYellow)
			}
		}
	}
    	
	//walk through the range's cells
	for rows := sheet.Rows(); rows.HasNext(); {
		_, row := rows.Next()
		row.Walk(func(idx, iCol, iRow int, cell *xlsx.Cell) {
			if iRow % 2 == 0 && iCol % 2 == 0 {
				cell.SetStyles(redBoldYellow)
			}
		})
 	}

	//Add hyperlink and set value same time
	sheet.CellByRef("A1").SetValueWithHyperlink("Link To Google", "http://google.com")
	
	//Add hyperlink as string
	sheet.RangeByRef("B1:C3").SetHyperlink("spam@spam.it")	

	//Add hyperlink via helper type for advanced settings
	sheet.CellByRef("A7").SetHyperlink(hyperlink.New(
		hyperlink.ToFile("./example_simple.xlsx"),
		hyperlink.ToRef("C3", "Sheet1"),
		hyperlink.Tooltip("That's a tooltip"),
		hyperlink.Display("Something to display"), //Cell still holds own value
		hyperlink.Styles(redBoldYellow),
	))

	sheet.CellByRef("A1").RemoveHyperlink()
	
	//Merged Cells
	sheet.RangeByRef("A1:C3").Merge()
	sheet.RangeByRef("A1:C3").Split()
	
	//Rich Text
	sheet.CellByRef("F10").SetText(
		"plain text", 
		styles.New(
			styles.Font.Bold,
			styles.Font.Color("#ff0000"),
		),
		" red bold text ",
		"another plain text",
	)
	
	//Add comment as string
	sheet.CellByRef("F11").SetComment("A cell's comment")

	//Add comment via helper type for advanced settings
	sheet.CellByRef("F12").SetComment(comment.New(
		comment.Author("John Doe"),
		comment.Text(
			"plain text", 
			styles.New(
				styles.Font.Bold,
				styles.Font.Color("#ff0000"),
			),
			" red bold text ",
			"another plain text",
		),
	))
	
	sheet.CellByRef("F11").RemoveComment()
	
	//Conditional formatting
	sheet.AddConditional(conditional.New(
		conditional.AddRule(
			rule.Value.Between(35.5, 78, styles.New(
				styles.Font.Bold,
				styles.Font.Color("#ff0000"),
			)),
		),
		conditional.AddRule(
			rule.TimePeriod.Last7Days(styles.New(
				styles.Font.Bold,
				styles.Font.Color("#ff0000"),
			)),
		),
		conditional.AddRule(
			rule.IconSet.Type(rule.IconSetType3Arrows),
			rule.IconSet.Value(0, "90", ">="),
			rule.IconSet.Value(1, "50", ">"),
		),
	), "A1:A10", "B2", "C1:C10")
    	
	xl.SaveAs("test1.xlsx")
}

Introduction

Why another library to work with Excel XLSX in GO?

Truth be told, developing of any library starts with some personal goals of author. Someone wants simple library to read Excel files, someone wants to create a new file, other wants to add charts.

So what were the goals that time? It's a great pity, but I could not get a library that:

1. respects existing data/formatting - no corrupted files or lost formatting

What if I need to open a well formatted file created with my favorite desktop application and update only one value?! I must get almost same file with just one updated value. None of existing library was able to do it. Corrupted file or lost formatting is common issue.

1. works with big files - reasonable speed and memory footprint

Same here, someone could not open, others took forever to open with anomaly memory usage.

1. consistent and as small API as possible with enough features set to do most common tasks - learning curve means something

Why?! Because it's not rocket science - open/create file, create/read/update/delete sheets/rows/cols and use styles. XLSX is quite simple format to read/write and GO has quite powerful xml encoder/decoder, so the hardest part - that API.

1. easy to read/understand source code, easy to maintain, easy to contribute - no shadow places/hacks/magic, just read and understand

I was trying to contribute to existing libraries, but...actually it's much faster to create it from ground zero than to refactor existing and get satisfied results or fix some issues.

Benchmarks

It was not a goal to make best of the best, but the same time it's interesting to know pros/cons. For some cases this library is second, for other - best, but in case of reading huge files - the only.

	tealeg	excelize	xlsx
RandomGet	1!	3	2
RandomSet	1!	3	2
RandomSetStyle	1!	3	2
ReadBigFile	2	3	1
UpdateBigFile	2!!	3	1
ReadHugeFile	-	-	1
UpdateHugeFile	-	-	1

• ! - does not mutate information directly, so faster get/set, but slower read/write files - sometimes it can take forever to open file.
• !! - corrupted file after saving, lost styles/formatting

Benchmarks report

Documentation and Examples

For more detailed documentation and examples you can check godoc.org

Roadmap

• sheet: copy
• sheet: read as stream
• sheet: custom filters
• sheet: write as stream
• merged cells: merge/split for ranges, cols, rows
• hyperlinks: for cells, ranges, cols, rows
• range: copy
• row: copy
• col: copy
• cell: comments
• cell: formulas
• cell: typed getter/setter for values
• other: conditional formatting
• other: rich texts
• other: drawing
• other: unpack package to temp folder to reduce memory usage
• other: more tests

Contribution

• To prevent mess, sources have strict separation of markup and functionality. Document that describes OOXML is quite huge (about 6K pages), but the same time - functionality is not.
• All markup resides inside of 'ml' folders, only marshal/unmarshal is allowed here, no any functionality.
• Not every 'ml object' has related 'functional object' and vice versa.
• If you want some functionality, then wrap 'ml object' and do what you want.

OOXML edition

XML is compliant with part 1 of the 5th edition of the ECMA-376 Standard for Office Open XML

License

Documentation

Overview

Example (Access)

Demonstrates how to access differ information

package main

import (
	"fmt"
	"github.com/plandem/xlsx"
	"log"
	"strings"
)

func main() {
	xl, err := xlsx.Open("./test_files/example_simple.xlsx")
	if err != nil {
		log.Fatal(err)
	}

	defer xl.Close()

	// Get sheet by 0-based index
	sheet := xl.Sheet(0)

	// Get cell by 0-based indexes
	cell := sheet.Cell(13, 27)
	fmt.Println(cell.Value())

	// Get cell by reference
	cell = sheet.CellByRef("N28")
	fmt.Println(cell.Value())

	// Get row by 0-based index
	row := sheet.Row(9)
	fmt.Println(strings.Join(row.Values(), ","))

	// Get cell of row at 0-based col index
	cell = row.Cell(0)
	fmt.Println(cell.Value())

	// Get col by 0-based index
	col := sheet.Col(3)
	fmt.Println(strings.Join(col.Values(), ","))

	// Get cell of col at 0-based row index
	cell = col.Cell(0)
	fmt.Println(cell.Value())

	// Get range by references
	area := sheet.RangeByRef("D10:H13")
	fmt.Println(strings.Join(area.Values(), ","))

}

Output:

last cell
last cell
,,,1,2,3,4,5,,,,,,

,,,,,,,,,1,6,11,16,,,,,,,,,,,,,,,

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20

Example (Append)

Demonstrates how to append cols/rows/sheets.

package main

import (
	"fmt"
	"github.com/plandem/xlsx"
	"log"
	"strings"
)

func main() {
	xl, err := xlsx.Open("./test_files/example_simple.xlsx")
	if err != nil {
		log.Fatal(err)
	}

	defer xl.Close()

	sheet := xl.Sheet(0)

	// To append a new col/row, simple request it - sheet will be auto expanded.
	// E.g.: we have 14 cols, 28 rows.
	fmt.Println(sheet.Dimension())

	// Append 72 rows
	sheet.Row(99)
	fmt.Println(sheet.Dimension())

	// Append 36 cols
	sheet.Col(49)
	fmt.Println(sheet.Dimension())

	// Append 3 sheet
	fmt.Println(strings.Join(xl.SheetNames(), ","))
	xl.AddSheet("new sheet")
	xl.AddSheet("new sheet")
	xl.AddSheet("new sheet")
	fmt.Println(strings.Join(xl.SheetNames(), ","))

}

Output:

14 28
14 100
50 100
Sheet1
Sheet1,new sheet,new sheet1,new sheet2

Example (Copy)

Demonstrates how to copy information in sheet

package main

import (
	"fmt"
	"github.com/plandem/xlsx"
	"log"
	"strings"
)

func main() {
	xl, err := xlsx.Open("./test_files/example_iteration.xlsx")
	if err != nil {
		log.Fatal(err)
	}

	defer xl.Close()

	sheet := xl.Sheet(0)
	for rows := sheet.Rows(); rows.HasNext(); {
		_, row := rows.Next()
		fmt.Println(strings.Join(row.Values(), ","))
	}

	// Copy row to another row with index
	row := sheet.Row(0)
	row.CopyTo(4, false)
	for rows := sheet.Rows(); rows.HasNext(); {
		_, row := rows.Next()
		fmt.Println(strings.Join(row.Values(), ","))
	}

	// Copy col to another col with index
	col := sheet.Col(0)
	col.CopyTo(3, false)
	for rows := sheet.Rows(); rows.HasNext(); {
		_, row := rows.Next()
		fmt.Println(strings.Join(row.Values(), ","))
	}

	// Copy range to another range that started at indexes
	r := sheet.RangeByRef("A1:B3")
	r.CopyTo(3, 0)
	for rows := sheet.Rows(); rows.HasNext(); {
		_, row := rows.Next()
		fmt.Println(strings.Join(row.Values(), ","))
	}

	// Copy range to another range that started at ref
	r.CopyToRef("I4")
	for rows := sheet.Rows(); rows.HasNext(); {
		_, row := rows.Next()
		fmt.Println(strings.Join(row.Values(), ","))
	}

}

Output:

Header 1,Header 2
Value 1-1,Value 2-1
Value 1-2,Value 2-2
Header 1,Header 2
Value 1-1,Value 2-1
Value 1-2,Value 2-2
,
Header 1,Header 2
Header 1,Header 2,,Header 1
Value 1-1,Value 2-1,,Value 1-1
Value 1-2,Value 2-2,,Value 1-2
,,,
Header 1,Header 2,,Header 1
Header 1,Header 2,,Header 1,Header 2
Value 1-1,Value 2-1,,Value 1-1,Value 2-1
Value 1-2,Value 2-2,,Value 1-2,Value 2-2
,,,,
Header 1,Header 2,,Header 1,
Header 1,Header 2,,Header 1,Header 2,,,,,
Value 1-1,Value 2-1,,Value 1-1,Value 2-1,,,,,
Value 1-2,Value 2-2,,Value 1-2,Value 2-2,,,,,
,,,,,,,,Header 1,Header 2
Header 1,Header 2,,Header 1,,,,,Value 1-1,Value 2-1
,,,,,,,,Value 1-2,Value 2-2

Example (Delete)

Demonstrates how to delete information

package main

import (
	"fmt"
	"github.com/plandem/xlsx"
	"log"
	"strings"
)

func main() {
	xl, err := xlsx.Open("./test_files/example_simple.xlsx")
	if err != nil {
		log.Fatal(err)
	}

	defer xl.Close()

	sheet := xl.Sheet(0)

	fmt.Println(sheet.Dimension())

	// Delete col
	fmt.Println(strings.Join(sheet.Col(3).Values(), ","))
	sheet.DeleteCol(3)
	fmt.Println(sheet.Dimension())
	fmt.Println(strings.Join(sheet.Col(3).Values(), ","))

	// Delete row
	fmt.Println(strings.Join(sheet.Row(3).Values(), ","))
	sheet.DeleteRow(3)
	fmt.Println(sheet.Dimension())
	fmt.Println(strings.Join(sheet.Row(3).Values(), ","))

	// Delete sheet
	fmt.Println(strings.Join(xl.SheetNames(), ","))
	xl.DeleteSheet(0)
	fmt.Println(strings.Join(xl.SheetNames(), ","))

}

Output:

14 28
,,,,,,,,,1,6,11,16,,,,,,,,,,,,,,,
13 28
,merged cols,,merged rows+cols,,,,,,2,7,12,17,,,,,,,,,,,,,,,
,,merged rows,merged rows+cols,,,,,,,,,
13 27
with trailing space   ,,merged rows,,,,,,,,,,
Sheet1

Example (Files)

Demonstrates how to create/open/save XLSX files

package main

import (
	"github.com/plandem/xlsx"
	"log"
	"os"
)

func main() {
	// Create a new XLSX file
	xl := xlsx.New()

	// Open the XLSX file using file name
	xl, err := xlsx.Open("./test_files/example_simple.xlsx")
	if err != nil {
		log.Fatal(err)
	}

	defer xl.Close()

	// Open the XLSX file using file handler
	zipFile, err := os.Open("./test_files/example_simple.xlsx")
	if err != nil {
		log.Fatal(err)
	}

	xl, err = xlsx.Open(zipFile)
	if err != nil {
		log.Fatal(err)
	}

	// Update the existing XLSX file
	err = xl.Save()
	if err != nil {
		log.Fatal(err)
	}

	// Save the XLSX file under different name
	err = xl.SaveAs("new_file.xlsx")
	if err != nil {
		log.Fatal(err)
	}
}

Example (Formatting)

Demonstrates how to add style formatting

package main

import (
	"github.com/plandem/xlsx"
	"github.com/plandem/xlsx/format/styles"
	"log"
)

func main() {
	xl, err := xlsx.Open("./test_files/example_simple.xlsx")
	if err != nil {
		log.Fatal(err)
	}

	defer xl.Close()

	// Create a new format for a bold font with red color and yellow solid background
	redBold := styles.New(
		styles.Font.Bold,
		styles.Font.Color("#ff0000"),
		styles.Fill.Background("#ffff00"),
		styles.Fill.Type(styles.PatternTypeSolid),
	)

	// Add formatting to xlsx
	styleID := xl.AddStyles(redBold)

	sheet := xl.Sheet(0)

	// Set formatting for cell
	sheet.CellByRef("N28").SetStyles(styleID)

	// Set DEFAULT formatting for row. Affects cells not yet allocated in the row.
	// In other words, this style applies to new cells.
	sheet.Row(9).SetStyles(styleID)

	// Set DEFAULT formatting for col. Affects cells not yet allocated in the col.
	// In other words, this style applies to new cells.
	sheet.Col(3).SetStyles(styleID)

	//set formatting for all cells in range
	sheet.RangeByRef("D10:H13").SetStyles(styleID)
}

Example (GettersAndSetters)

Demonstrates how to get/set value for cell

package main

import (
	"fmt"
	"github.com/plandem/xlsx"
	"time"
)

func main() {
	xl := xlsx.New()
	defer xl.Close()

	sheet := xl.AddSheet("test sheet")

	now, _ := time.Parse("02 Jan 06 15:04 MST", time.RFC822)

	//set values by typed method
	sheet.CellByRef("A1").SetText("string")
	sheet.CellByRef("B1").SetInlineText("inline string")
	sheet.CellByRef("C1").SetBool(true)
	sheet.CellByRef("D1").SetInt(12345)
	sheet.CellByRef("E1").SetFloat(123.123)
	sheet.CellByRef("F1").SetDateTime(now)
	sheet.CellByRef("G1").SetDate(now)
	sheet.CellByRef("H1").SetTime(now)
	sheet.CellByRef("I1").SetDeltaTime(now)
	sheet.CellByRef("K1").SetValueWithFormat(-1234, "")

	//set values by unified method
	sheet.CellByRef("A2").SetValue("string")
	sheet.CellByRef("B2").SetValue(true)
	sheet.CellByRef("C2").SetValue(12345)
	sheet.CellByRef("D2").SetValue(123.123)
	sheet.CellByRef("E2").SetValue(now)

	//get raw values that were set via typed setter
	fmt.Println(sheet.CellByRef("A1").Value())
	fmt.Println(sheet.CellByRef("B1").Value())
	fmt.Println(sheet.CellByRef("C1").Value())
	fmt.Println(sheet.CellByRef("D1").Value())
	fmt.Println(sheet.CellByRef("E1").Value())
	fmt.Println(sheet.CellByRef("F1").Value())
	fmt.Println(sheet.CellByRef("G1").Value())
	fmt.Println(sheet.CellByRef("H1").Value())
	fmt.Println(sheet.CellByRef("I1").Value())
	fmt.Println(sheet.CellByRef("K1").Value())

	//get raw values that were set that via general setter
	fmt.Println(sheet.CellByRef("A2").Value())
	fmt.Println(sheet.CellByRef("B2").Value())
	fmt.Println(sheet.CellByRef("C2").Value())
	fmt.Println(sheet.CellByRef("D2").Value())
	fmt.Println(sheet.CellByRef("E2").Value())

	//get typed values and error if invalid type (values were set via typed setter)
	_ = sheet.CellByRef("A1").String()
	_ = sheet.CellByRef("B1").String()
	_, _ = sheet.CellByRef("C1").Bool()
	_, _ = sheet.CellByRef("D1").Int()
	_, _ = sheet.CellByRef("E1").Float()
	_, _ = sheet.CellByRef("F1").Date()

	//get typed values and error if invalid type (values were set via general setter)
	_ = sheet.CellByRef("A2").String()
	_, _ = sheet.CellByRef("B2").Bool()
	_, _ = sheet.CellByRef("C2").Int()
	_, _ = sheet.CellByRef("D2").Float()
	_, _ = sheet.CellByRef("E2").Date()

}

Output:

string
inline string
1
12345
123.123
2006-01-02T15:04:00
2006-01-02T15:04:00
2006-01-02T15:04:00
2006-01-02T15:04:00
-1234
string
1
12345
123.123
2006-01-02T15:04:00

Example (Insert)

Demonstrates how to insert cols/rows

package main

import (
	"fmt"
	"github.com/plandem/xlsx"
	"log"
	"strings"
)

func main() {
	xl, err := xlsx.Open("./test_files/example_simple.xlsx")
	if err != nil {
		log.Fatal(err)
	}

	defer xl.Close()

	sheet := xl.Sheet(0)

	fmt.Println(sheet.Dimension())
	fmt.Println(strings.Join(sheet.Col(3).Values(), ","))

	// Insert a new col
	sheet.InsertCol(3)
	fmt.Println(sheet.Dimension())
	fmt.Println(strings.Join(sheet.Col(3).Values(), ","))
	fmt.Println(strings.Join(sheet.Col(4).Values(), ","))

	// Insert a new row
	fmt.Println(strings.Join(sheet.Row(9).Values(), ","))
	sheet.InsertRow(3)
	fmt.Println(sheet.Dimension())
	fmt.Println(strings.Join(sheet.Row(9).Values(), ","))
	fmt.Println(strings.Join(sheet.Row(10).Values(), ","))

}

Output:

14 28
,,,,,,,,,1,6,11,16,,,,,,,,,,,,,,,
15 28
,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,1,6,11,16,,,,,,,,,,,,,,,
,,,,1,2,3,4,5,,,,,,
15 29
,,,,,,,,,,,,,,
,,,,1,2,3,4,5,,,,,,

Example (Iterate)

Demonstrates how to iterate

package main

import (
	"fmt"
	"github.com/plandem/xlsx"
	"log"
)

func main() {
	xl, err := xlsx.Open("./test_files/example_iteration.xlsx")
	if err != nil {
		log.Fatal(err)
	}

	defer xl.Close()

	// Get sheet by 0-based index
	sheet := xl.Sheet(0)

	// Iterate by indexes
	totalCols, totalRows := sheet.Dimension()
	for rIdx := 0; rIdx < totalRows; rIdx++ {
		for cIdx := 0; cIdx < totalCols; cIdx++ {
			fmt.Println(sheet.Cell(cIdx, rIdx).Value())
		}
	}

	// Iterate rows via iterator
	for rows := sheet.Rows(); rows.HasNext(); {
		_, row := rows.Next()
		for cells := row.Cells(); cells.HasNext(); {
			_, _, cell := cells.Next()
			fmt.Println(cell.Value())
		}
	}

	// Iterate cols via iterator
	for cols := sheet.Cols(); cols.HasNext(); {
		_, col := cols.Next()
		for cells := col.Cells(); cells.HasNext(); {
			_, _, cell := cells.Next()
			fmt.Println(cell.Value())
		}
	}

	// Iterate range's cells via iterator
	r := sheet.RangeByRef("A1:B3")
	for cells := r.Cells(); cells.HasNext(); {
		_, _, cell := cells.Next()
		fmt.Println(cell.Value())
	}

	// Iterate sheets via iterator
	for sheets := xl.Sheets(); sheets.HasNext(); {
		_, sheet := sheets.Next()
		fmt.Println(sheet.Name())
	}

}

Output:

Header 1
Header 2
Value 1-1
Value 2-1
Value 1-2
Value 2-2
Header 1
Header 2
Value 1-1
Value 2-1
Value 1-2
Value 2-2
Header 1
Value 1-1
Value 1-2
Header 2
Value 2-1
Value 2-2
Header 1
Header 2
Value 1-1
Value 2-1
Value 1-2
Value 2-2
First Sheet
Second Sheet
Last Sheet

Example (Options)

Demonstrates how to set options of rows/cols/sheets

package main

import (
	"github.com/plandem/xlsx"

	colOptions "github.com/plandem/xlsx/types/options/column"

	rowOptions "github.com/plandem/xlsx/types/options/row"

	sheetOptions "github.com/plandem/xlsx/types/options/sheet"
	"log"
)

func main() {
	xl, err := xlsx.Open("./test_files/example_simple.xlsx")
	if err != nil {
		log.Fatal(err)
	}

	defer xl.Close()

	sheet := xl.Sheet(0)

	// set options for row
	ro := rowOptions.New(
		rowOptions.Hidden(true),
		rowOptions.Height(10.0),
		rowOptions.Collapsed(true),
	)
	sheet.Row(9).SetOptions(ro)

	// set options for col
	co := colOptions.New(
		colOptions.Hidden(true),
		colOptions.Width(10.0),
		colOptions.Collapsed(true),
	)
	sheet.Col(3).SetOptions(co)

	// set options for sheet
	so := sheetOptions.New(
		sheetOptions.Visibility(sheetOptions.VisibilityVeryHidden),
	)
	sheet.SetOptions(so)
}

Example (Streams)

Demonstrates how to open sheet in streaming mode

package main

import (
	"fmt"
	"github.com/plandem/xlsx"
	"log"
	"strings"
)

func main() {
	xl, err := xlsx.Open("./test_files/example_simple.xlsx")
	if err != nil {
		log.Fatal(err)
	}

	defer xl.Close()

	// Open sheet in stream reading mode with single phase.
	// Some meta information is NOT available (e.g. merged cells).
	sheet := xl.Sheet(0, xlsx.SheetModeStream)
	for rows := sheet.Rows(); rows.HasNext(); {
		_, row := rows.Next()
		fmt.Println(strings.Join(row.Values(), ","))
	}
	sheet.Close()

	// Open sheet in stream reading mode with multi phases.
	// Meta information is available.
	sheet = xl.Sheet(0, xlsx.SheetModeStream, xlsx.SheetModeMultiPhase)
	for rows := sheet.Rows(); rows.HasNext(); {
		_, row := rows.Next()
		fmt.Println(strings.Join(row.Values(), ","))
	}
	sheet.Close()
}

Example (Update)

Demonstrates how to update information

package main

import (
	"fmt"
	"github.com/plandem/xlsx"
	"log"
	"strings"
)

func main() {
	xl, err := xlsx.Open("./test_files/example_simple.xlsx")
	if err != nil {
		log.Fatal(err)
	}

	defer xl.Close()

	sheet := xl.Sheet(0)

	// Update value of cell
	cell := sheet.Cell(13, 27)
	fmt.Println(cell.Value())
	cell.SetValue("new value")
	fmt.Println(cell.Value())

	// Update value of cells in row
	row := sheet.Row(9)
	fmt.Println(strings.Join(row.Values(), ","))
	row.Walk(func(idx, cIdx, rIdx int, c *xlsx.Cell) {
		c.SetValue(idx)
	})
	fmt.Println(strings.Join(row.Values(), ","))

	// Update value of cells in col
	col := sheet.Col(3)
	fmt.Println(strings.Join(col.Values(), ","))
	col.Walk(func(idx, cIdx, rIdx int, c *xlsx.Cell) {
		c.SetValue(idx)
	})
	fmt.Println(strings.Join(col.Values(), ","))

	// Update value of cells in range
	area := sheet.RangeByRef("D10:H13")
	fmt.Println(strings.Join(area.Values(), ","))
	area.Walk(func(idx, cIdx, rIdx int, c *xlsx.Cell) {
		c.SetValue(idx)
	})
	fmt.Println(strings.Join(area.Values(), ","))

}

Output:

last cell
new value
,,,1,2,3,4,5,,,,,,
0,1,2,3,4,5,6,7,8,9,10,11,12,13
,,,,,,,,,3,6,11,16,,,,,,,,,,,,,,,
0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27
9,4,5,6,7,10,7,8,9,10,11,12,13,14,15,12,17,18,19,20
0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19

Example (Walk)

Demonstrate walk cells using callback

package main

import (
	"fmt"
	"github.com/plandem/xlsx"
	"log"
)

func main() {
	xl, err := xlsx.Open("./test_files/example_iteration.xlsx")
	if err != nil {
		log.Fatal(err)
	}

	defer xl.Close()

	// Get sheet by 0-based index
	sheet := xl.Sheet(0)

	// Walk through the cells of row
	row := sheet.Row(0)
	row.Walk(func(idx, cIdx, rIdx int, c *xlsx.Cell) {
		fmt.Println(c.Value())
	})

	// Walk through the cells of col
	col := sheet.Col(0)
	col.Walk(func(idx, cIdx, rIdx int, c *xlsx.Cell) {
		fmt.Println(c.Value())
	})

	// Walk through the cells of range
	area := sheet.RangeByRef("A1:B3")
	area.Walk(func(idx, cIdx, rIdx int, c *xlsx.Cell) {
		fmt.Println(c.Value())
	})

}

Output:

Header 1
Header 2
Header 1
Value 1-1
Value 1-2
Header 1
Header 2
Value 1-1
Value 2-1
Value 1-2
Value 2-2

Index

• Constants
• type Cell
  • func (c *Cell) Bool() (bool, error)
  • func (c *Cell) Clear()
  • func (c *Cell) Comment() string
  • func (c *Cell) Date() (time.Time, error)
  • func (c *Cell) Float() (float64, error)
  • func (c *Cell) HasFormula() bool
  • func (c *Cell) Hyperlink() *hyperlink.Info
  • func (c *Cell) Int() (int, error)
  • func (c *Cell) RemoveComment()
  • func (c *Cell) RemoveHyperlink()
  • func (c *Cell) Reset()
  • func (c *Cell) SetBool(value bool)
  • func (c *Cell) SetComment(comment interface{}) error
  • func (c *Cell) SetDate(value time.Time)
  • func (c *Cell) SetDateTime(value time.Time)
  • func (c *Cell) SetDeltaTime(value time.Time)
  • func (c *Cell) SetFloat(value float64)
  • func (c *Cell) SetGeneral(value string)
  • func (c *Cell) SetHyperlink(link interface{}) error
  • func (c *Cell) SetInlineText(parts ...interface{}) error
  • func (c *Cell) SetInt(value int)
  • func (c *Cell) SetStyles(s interface{})
  • func (c *Cell) SetText(parts ...interface{}) error
  • func (c *Cell) SetTime(value time.Time)
  • func (c *Cell) SetUint(value uint)
  • func (c *Cell) SetValue(value interface{})
  • func (c *Cell) SetValueWithFormat(value interface{}, formatCode string)
  • func (c *Cell) SetValueWithHyperlink(value interface{}, link interface{}) error
  • func (c *Cell) String() string
  • func (c *Cell) Styles() styles.DirectStyleID
  • func (c *Cell) Type() types.CellType
  • func (c *Cell) Uint() (uint, error)
  • func (c *Cell) Value() string
• type Col
  • func (c *Col) Cell(rowIndex int) *Cell
  • func (c *Col) CopyTo(cIdx int, withOptions bool)
  • func (c *Col) SetOptions(o *options.Info)
  • func (c *Col) SetStyles(styleID styles.DirectStyleID)
  • func (c *Col) Styles() styles.DirectStyleID
• type ColIterator
• type Range
  • func (r *Range) Bounds() types.Bounds
  • func (r *Range) Cells() RangeIterator
  • func (r *Range) Clear()
  • func (r *Range) CopyTo(cIdx, rIdx int)
  • func (r *Range) CopyToRef(ref types.Ref)
  • func (r *Range) Merge() error
  • func (r *Range) RemoveHyperlink()
  • func (r *Range) Reset()
  • func (r *Range) SetHyperlink(link interface{}) error
  • func (r *Range) SetStyles(styleID styles.DirectStyleID)
  • func (r *Range) Split()
  • func (r *Range) Values() []string
  • func (r *Range) Walk(cb func(idx, cIdx, rIdx int, c *Cell))
• type RangeIterator
• type Row
  • func (r *Row) Cell(colIndex int) *Cell
  • func (r *Row) CopyTo(rIdx int, withOptions bool)
  • func (r *Row) SetOptions(o *options.Info)
  • func (r *Row) SetStyles(styleID styles.DirectStyleID)
  • func (r *Row) Styles() styles.DirectStyleID
• type RowIterator
• type Sheet
• type SheetIterator
• type Spreadsheet
  • func New() *Spreadsheet
  • func Open(f interface{}) (*Spreadsheet, error)
  • func (xl *Spreadsheet) AddSheet(name string, options ...sheetMode) Sheet
  • func (xl *Spreadsheet) AddStyles(style *styles.Info) styles.DirectStyleID
  • func (xl *Spreadsheet) DeleteSheet(i int)
  • func (xl *Spreadsheet) IsValid() error
  • func (xl *Spreadsheet) ResolveStyles(styleID styles.DirectStyleID) *styles.Info
  • func (xl *Spreadsheet) Sheet(i int, options ...sheetMode) Sheet
  • func (xl *Spreadsheet) SheetByName(name string, options ...sheetMode) Sheet
  • func (xl *Spreadsheet) SheetNames() []string
  • func (xl *Spreadsheet) Sheets() SheetIterator

Examples

• Package (Access)
• Package (Append)
• Package (Copy)
• Package (Delete)
• Package (Files)
• Package (Formatting)
• Package (GettersAndSetters)
• Package (Insert)
• Package (Iterate)
• Package (Options)
• Package (Streams)
• Package (Update)
• Package (Walk)

Constants

const (
	SheetModeStream          sheetMode //In stream mode only forward reading/writing is allowed
	SheetModeMultiPhase                //Sheet will be iterated two times: first one to load meta information (e.g. merged cells) and another one for sheet data. Only for SheetModeStream mode.
	SheetModeIgnoreDimension           //Ignore dimension information during reading or skip it during writing
)

List of all possible open modes for Sheet. Mode applies only once, except SheetModeStream and few modes can be combined. E.g.: SheetModeStream, SheetModeMultiPhase

Types

type Cell

type Cell struct {
	// contains filtered or unexported fields
}

Cell is a higher level object that wraps ml.Cell with functionality

func (*Cell) Bool

func (c *Cell) Bool() (bool, error)

Bool try to convert and return current raw value as bool

func (*Cell) Clear

func (c *Cell) Clear()

Clear clears cell's value

func (*Cell) Comment

func (c *Cell) Comment() string

Comment returns text of comment if there is any comment or empty string

func (*Cell) Date

func (c *Cell) Date() (time.Time, error)

Date try to convert and return current raw value as time.Time

func (*Cell) Float

func (c *Cell) Float() (float64, error)

Float try to convert and return current raw value as float64

func (*Cell) HasFormula

func (c *Cell) HasFormula() bool

HasFormula returns true if cell has formula

func (*Cell) Hyperlink

func (c *Cell) Hyperlink() *hyperlink.Info

Hyperlink returns resolved hyperlink.Info if there is any hyperlink or nil otherwise

func (*Cell) Int

func (c *Cell) Int() (int, error)

Int try to convert and return current raw value as signed integer

func (*Cell) RemoveComment

func (c *Cell) RemoveComment()

RemoveComment removes comment from cell

func (*Cell) RemoveHyperlink

func (c *Cell) RemoveHyperlink()

RemoveHyperlink removes hyperlink from cell

func (*Cell) Reset

func (c *Cell) Reset()

Reset resets current current cell information

func (*Cell) SetBool

func (c *Cell) SetBool(value bool)

SetBool sets a bool value

func (*Cell) SetComment

func (c *Cell) SetComment(comment interface{}) error

SetComment sets comment for cell, where comment can be string or comment.Info

func (*Cell) SetDate

func (c *Cell) SetDate(value time.Time)

SetDate sets a time value with number format for date

func (*Cell) SetDateTime

func (c *Cell) SetDateTime(value time.Time)

SetDateTime sets a time value with number format for datetime

func (*Cell) SetDeltaTime

func (c *Cell) SetDeltaTime(value time.Time)

SetDeltaTime sets a time value with number format for delta time

func (*Cell) SetFloat

func (c *Cell) SetFloat(value float64)

SetFloat sets a float value

func (*Cell) SetGeneral

func (c *Cell) SetGeneral(value string)

SetGeneral sets the value as general type

func (*Cell) SetHyperlink

func (c *Cell) SetHyperlink(link interface{}) error

SetHyperlink sets hyperlink for cell, where link can be string or hyperlink.Info

func (*Cell) SetInlineText

func (c *Cell) SetInlineText(parts ...interface{}) error

SetInlineText sets inline rich text

func (*Cell) SetInt

func (c *Cell) SetInt(value int)

SetInt sets an signed integer value

func (*Cell) SetStyles

func (c *Cell) SetStyles(s interface{})

SetStyles sets style format to requested DirectStyleID or styles.Info

func (*Cell) SetText

func (c *Cell) SetText(parts ...interface{}) error

SetText sets shared rich text

func (*Cell) SetTime

func (c *Cell) SetTime(value time.Time)

SetTime sets a time value with number format for time

func (*Cell) SetUint

func (c *Cell) SetUint(value uint)

SetUint sets an unsigned integer value

func (*Cell) SetValue

func (c *Cell) SetValue(value interface{})

nolint SetValue sets a value

func (*Cell) SetValueWithFormat

func (c *Cell) SetValueWithFormat(value interface{}, formatCode string)

SetValueWithFormat is helper function that internally works as SetValue and SetStyles with NumberFormat

func (*Cell) SetValueWithHyperlink

func (c *Cell) SetValueWithHyperlink(value interface{}, link interface{}) error

SetValueWithHyperlink is helper function that internally works as SetValue and SetHyperlink

func (*Cell) String

func (c *Cell) String() string

String returns formatted value as string respecting cell number format and type. Any errors ignored to conform String() interface.

func (*Cell) Styles

func (c *Cell) Styles() styles.DirectStyleID

Styles returns DirectStyleID of active format for cell

func (*Cell) Type

func (c *Cell) Type() types.CellType

Type returns current type of cell

func (*Cell) Uint

func (c *Cell) Uint() (uint, error)

Uint try to convert and return current raw value as unsigned integer

func (*Cell) Value

func (c *Cell) Value() string

Value returns current raw value of cell

type Col

type Col struct {
	*Range
	// contains filtered or unexported fields
}

Col is a higher level object that wraps ml.Col with functionality. Inherits functionality of Range

func (*Col) Cell

func (c *Col) Cell(rowIndex int) *Cell

Cell returns cell of col at row with rowIndex

func (*Col) CopyTo

func (c *Col) CopyTo(cIdx int, withOptions bool)

CopyTo copies col cells into another col with cIdx index. N.B.: Merged cells are not supported

func (*Col) SetOptions

func (c *Col) SetOptions(o *options.Info)

SetOptions sets options for column

func (*Col) SetStyles

func (c *Col) SetStyles(styleID styles.DirectStyleID)

SetStyles sets default style for the column. Affects cells not yet allocated in the column. In other words, this style applies to new cells.

func (*Col) Styles

func (c *Col) Styles() styles.DirectStyleID

Styles returns DirectStyleID of default format for column

type ColIterator

type ColIterator interface {
	//Next returns next Col in sheet and corresponding index
	Next() (idx int, col *Col)

	//HasNext returns true if there are cols to iterate or false in other case
	HasNext() bool
}

ColIterator is a interface for iterating cols inside of sheet

type Range

type Range struct {
	// contains filtered or unexported fields
}

Range is a object that provides some functionality for cells inside of range. E.g.: A1:D12

func (*Range) Bounds

func (r *Range) Bounds() types.Bounds

Bounds returns bounds of range

func (*Range) Cells

func (r *Range) Cells() RangeIterator

Cells returns iterator for all cells in range

func (*Range) Clear

func (r *Range) Clear()

Clear clears each cell value in range

func (*Range) CopyTo

func (r *Range) CopyTo(cIdx, rIdx int)

CopyTo copies range cells into another range starting indexes cIdx and rIdx N.B.: Merged cells are not supported

func (*Range) CopyToRef

func (r *Range) CopyToRef(ref types.Ref)

CopyToRef copies range cells into another range starting with ref. N.B.: Merged cells are not supported

func (*Range) Merge

func (r *Range) Merge() error

Merge merges range

func (*Range) RemoveHyperlink

func (r *Range) RemoveHyperlink()

RemoveHyperlink removes hyperlink from cell

func (*Range) Reset

func (r *Range) Reset()

Reset resets each cell data into zero state

func (*Range) SetHyperlink

func (r *Range) SetHyperlink(link interface{}) error

SetHyperlink sets hyperlink for range, where link can be string or HyperlinkInfo

func (*Range) SetStyles

func (r *Range) SetStyles(styleID styles.DirectStyleID)

SetStyles sets style format to all cells in range

func (*Range) Split

func (r *Range) Split()

Split splits cells in range

func (*Range) Values

func (r *Range) Values() []string

Values returns values for all cells in range

func (*Range) Walk

func (r *Range) Walk(cb func(idx, cIdx, rIdx int, c *Cell))

Walk calls callback cb for each Cell in range

type RangeIterator

type RangeIterator interface {
	//Next returns next Cell in range and corresponding indexes
	Next() (cIdx int, rIdx int, cell *Cell)

	//HasNext returns true if there are cells to iterate or false in other case
	HasNext() bool
}

RangeIterator is a interface for iterating cells inside of range

type Row

type Row struct {
	*Range
	// contains filtered or unexported fields
}

Row is a higher level object that wraps ml.Row with functionality. Inherits functionality of Range

func (*Row) Cell

func (r *Row) Cell(colIndex int) *Cell

Cell returns cell of row at col with colIndex

func (*Row) CopyTo

func (r *Row) CopyTo(rIdx int, withOptions bool)

CopyTo copies row cells into another row with rIdx index. N.B.: Merged cells are not supported

func (*Row) SetOptions

func (r *Row) SetOptions(o *options.Info)

SetOptions sets options for row

func (*Row) SetStyles

func (r *Row) SetStyles(styleID styles.DirectStyleID)

SetStyles sets default style for the row. Affects cells not yet allocated in the row. In other words, this style applies to new cells.

func (*Row) Styles

func (r *Row) Styles() styles.DirectStyleID

Styles returns DirectStyleID of default format for row

type RowIterator

type RowIterator interface {
	//Next returns next Row in sheet and corresponding index
	Next() (idx int, row *Row)

	//HasNext returns true if there are rows to iterate or false in other case
	HasNext() bool
}

RowIterator is a interface for iterating rows inside of sheet

type Sheet

type Sheet interface {
	//Cell returns a cell for 0-based indexes
	Cell(colIndex, rowIndex int) *Cell
	//CellByRef returns a cell for ref
	CellByRef(cellRef types.CellRef) *Cell
	//Rows returns iterator for all rows of sheet
	Rows() RowIterator
	//Row returns a row for 0-based index
	Row(index int) *Row
	//Cols returns iterator for all cols of sheet
	Cols() ColIterator
	//Col returns a col for 0-based index
	Col(index int) *Col
	//Range returns a range for indexes
	Range(fromCol, fromRow, toCol, toRow int) *Range
	//RangeByRef returns a range for ref
	RangeByRef(ref types.Ref) *Range
	//Dimension returns total number of cols and rows in sheet
	Dimension() (cols int, rows int)
	//SetDimension sets total number of cols and rows in sheet
	SetDimension(cols, rows int)
	//InsertRow inserts a row at 0-based index and returns it. Using to insert a row between other rows.
	InsertRow(index int) *Row
	//DeleteRow deletes a row at 0-based index
	DeleteRow(index int)
	//InsertCol inserts a col at 0-based index and returns it. Using to insert a col between other cols.
	InsertCol(index int) *Col
	//DeleteCol deletes a col at 0-based index
	DeleteCol(index int)
	//MergeRows merges rows between 0-based fromIndex and toIndex
	MergeRows(fromIndex, toIndex int) error
	//MergeCols merges cols between 0-based fromIndex and toIndex
	MergeCols(fromIndex, toIndex int) error
	//SplitRows splits rows between 0-based fromIndex and toIndex
	SplitRows(fromIndex, toIndex int)
	//SplitCols splits cols between 0-based fromIndex and toIndex
	SplitCols(fromIndex, toIndex int)
	//AddConditional adds conditional formatting to sheet, with additional refs if required
	AddConditional(conditional *conditional.Info, refs ...types.Ref) error
	//DeleteConditional deletes conditional formatting for refs
	DeleteConditional(refs ...types.Ref)
	//AutoFilter adds auto filter in provided Ref range with additional settings if required
	AutoFilter(ref types.Ref, settings ...interface{})
	//AddFilter adds a custom filter to column with 0-based colIndex
	AddFilter(colIndex int, settings ...interface{}) error
	//DeleteFilter deletes a filter from column with 0-based colIndex
	DeleteFilter(colIndex int)
	//Name returns name of sheet
	Name() string
	//SetName sets a name for sheet
	SetName(name string)
	//Set sets options for sheet
	SetOptions(o *options.Info)
	//SetActive sets the sheet as active
	SetActive()
	//Close frees allocated by sheet resources
	Close()
	// contains filtered or unexported methods
}

Sheet is interface for a higher level object that wraps ml.Worksheet with functionality

type SheetIterator

type SheetIterator interface {
	//Next returns next Sheet in Spreadsheet and corresponding index
	Next() (idx int, sheet Sheet)

	//HasNext returns true if there are sheets to iterate or false in other case
	HasNext() bool
}

SheetIterator is a interface for iterating sheets inside of Spreadsheet

type Spreadsheet

type Spreadsheet struct {
	ooxml.Package
	// contains filtered or unexported fields
}

Spreadsheet is a higher level object that wraps OOXML package with XLSX functionality

func New

func New() *Spreadsheet

New creates and returns a new XLSX document

func Open

func Open(f interface{}) (*Spreadsheet, error)

Open opens a XLSX file with name or io.Reader

func (*Spreadsheet) AddSheet

func (xl *Spreadsheet) AddSheet(name string, options ...sheetMode) Sheet

AddSheet adds a new sheet with name to document

func (*Spreadsheet) AddStyles

func (xl *Spreadsheet) AddStyles(style *styles.Info) styles.DirectStyleID

AddStyles adds a new style formatting to document and return related ID that can be used lately

func (*Spreadsheet) DeleteSheet

func (xl *Spreadsheet) DeleteSheet(i int)

DeleteSheet deletes the sheet with required 0-based index

func (*Spreadsheet) IsValid

func (xl *Spreadsheet) IsValid() error

IsValid validates document and return error if there is any error. Using right before saving.

func (*Spreadsheet) ResolveStyles

func (xl *Spreadsheet) ResolveStyles(styleID styles.DirectStyleID) *styles.Info

ResolveStyles returns style formatting for styleID or nil if there is no any styles with such styleID

func (*Spreadsheet) Sheet

func (xl *Spreadsheet) Sheet(i int, options ...sheetMode) Sheet

Sheet returns a sheet by 0-based index with required open mode options

func (*Spreadsheet) SheetByName

func (xl *Spreadsheet) SheetByName(name string, options ...sheetMode) Sheet

SheetByName returns a sheet by name with required open mode options

func (*Spreadsheet) SheetNames

func (xl *Spreadsheet) SheetNames() []string

SheetNames returns a names of all sheets

func (*Spreadsheet) Sheets

func (xl *Spreadsheet) Sheets() SheetIterator

Sheets returns iterator for all sheets of Spreadsheet