xlsx

package module
v1.0.4 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Nov 2, 2019 License: MIT Imports: 31 Imported by: 14

README

Xlsx2Go

Build Status Code Coverage Go Report Card GoDoc License FOSSA Status Donate

package main

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

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

	//create a new sheet
	sheet := xl.AddSheet("The first sheet")

	//access by ref
	cell := sheet.CellByRef("A2")

	//set value
	cell.SetValue("Easy Peasy")

	//set cool styles
	cell.SetStyles(styles.New(
		styles.Font.Bold,
		styles.Font.Color("#ff0000"),
		styles.Fill.Type(styles.PatternTypeSolid),
		styles.Fill.Color("#ffff00"),
		styles.Border.Color("#009000"),
		styles.Border.Type(styles.BorderStyleMedium),
	))

	//add comment
	cell.SetComment("No Comment!")

	//add hyperlink
	sheet.CellByRef("A4").SetValueWithHyperlink("wikipedia", "http://google.com")

	//merge cells
	sheet.RangeByRef("A6:A7").Merge()
	sheet.CellByRef("A6").SetValue("merged cell")

	//iterating
	for iRow := 1; iRow < 7; iRow++ {
		//access by indexes
		cell := sheet.Cell(1, iRow)
		cell.SetValue(iRow)
	}

	//add conditional formatting
	sheet.AddConditional(conditional.New(
		conditional.AddRule(
			rule.Value.Between(1, 3, styles.New(
				styles.Font.Bold,
				styles.Font.Color("#ff0000"),
			)),
		),
		conditional.AddRule(
			rule.IconSet.Type(rule.IconSetType3Arrows),
		),
	), "B2:B7")

	xl.SaveAs("./foo.xlsx")
}

Documentation

Roadmap

  • sheet: copy
  • sheet: custom filters
  • sheet: streaming
  • 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: more optimization
  • 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

FOSSA Status

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)
	}
}

Output:
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)
}

Output:
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"
	"github.com/plandem/xlsx/format/styles"

	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),
		rowOptions.Styles(styles.New(
			styles.Alignment.VAlign(styles.VAlignJustify),
		)),
	)
	sheet.Row(9).SetOptions(ro)

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

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

Output:
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()
}

Output:
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

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

This section is empty.

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 (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 (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 (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 (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(s interface{})

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 (r *Range) RemoveHyperlink()

RemoveHyperlink removes hyperlink from cell

func (*Range) Reset 

func (r *Range) Reset()

Reset resets each cell data into zero state 

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(s interface{})

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 SheetMode added in v1.0.4 

type SheetMode byte
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

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

Source Files

View all Source files

Directories

Path Synopsis
format
conditional
conditional/rule
styles
color
ml
ml/primitives
number_format
number_format/convert
validator
comment
hyperlink
options/column
options/row
options/sheet

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.