xlsxtra

package module
v0.0.0-...-8accf50 Latest Latest
Warning

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

 Go to latest Published: Jul 28, 2016 License: MIT Imports: 7 Imported by: 0

README

xlsxtra - extra utilities for xlsx

Travis CI codecov goreportcard Documentation and Examples Software License

This was developed as an extension for the xlsx package. It contains the following utilities to manipulate excel files:

  • Sort(), SortByHeaders: multi-column (reverse) sort of selected rows (Note that columns are one based, not zero based to make reverse sort possible.)
  • AddBool(), AddInt(), AddFloat(), ...: shortcut to add a cell to a row with the right type.
  • NewStyle(): create a style and set the ApplyFill, ApplyFont, ApplyBorder and ApplyAlignment automatically.
  • NewStyles(): create a slice of styles based on a color palette
  • Sheets: access sheets by name instead of by index
  • Col: access cell values of a row by column header title
  • SetRowStyle: set style of all cells in a row
  • ToString: convert a xlsx.Row to a slice of strings
Example

Add cells and retrieve cell values by column title header:

type Item struct {
	Name   string
	Price  float64
	Amount int
}
sheet, err := xlsx.NewFile().AddSheet("Shopping Basket")
if err != nil {
	fmt.Println(err)
	return
}

// column header
var titles = []string{"item", "price", "amount", "total"}
header := sheet.AddRow()
for _, title := range titles {
	xlsxtra.AddString(header, title)
}
style := xlsxtra.NewStyle(
	"00ff0000", // color
	&xlsx.Font{Size: 10, Name: "Arial", Bold: true}, // bold
	nil, // border
	nil, // alignment
)
xlsxtra.SetRowStyle(header, style)

// items
var items = []Item{
	{"chocolate", 4.99, 2},
	{"cookies", 6.45, 3},
}
var row *xlsx.Row
for i, item := range items {
	row = sheet.AddRow()
	xlsxtra.AddString(row, item.Name)
	xlsxtra.AddFloat(row, item.Price, "0.00")
	xlsxtra.AddInt(row, item.Amount)
	xlsxtra.AddFormula(row,
		fmt.Sprintf("B%d*C%d", i+1, i+1), "0.00")
}

// column Col type
col := xlsxtra.NewCol(header)
price, err := col.Float(row, "price")
if err != nil {
	fmt.Println(err)
	return
}
fmt.Println(price)
// Output: 6.45

Multi column sort:

sheet, err := xlsxtra.OpenSheet(
	"xlsxtra_test.xlsx", "sort_test.go")
if err != nil {
	fmt.Println(err)
	return
}

// multi column sort
xlsxtra.Sort(sheet, 1, -1,
	3,  // last name
	-2, // first name
	6, // ip address
)
for _, row := range sheet.Rows {
	fmt.Println(strings.Join(xlsxtra.ToString(row), ", "))
}
fmt.Println()
// by header
headers := xlsxtra.NewHeaders(sheet.Rows[0])
err = xlsxtra.SortByHeaders(sheet, 1, -1, headers,
	"-amount",
	"first_name",
)
if err != nil {
	fmt.Println(err)
	return
}
for _, row := range sheet.Rows {
	fmt.Println(strings.Join(xlsxtra.ToString(row), ", "))
}

// Output:
// id, first_name, last_name, email, gender, amount
// 9, Donald, Bryant, lharper8@wunderground.com, Female, 100000000
// 7, Donald, Bryant, dbryant6@redcross.org, Male, 3000000
// 10, Donald, Bryant, hmarshall9@stumbleupon.com, Male, € 9
// 4, Teresa, Hunter, thall3@arizona.edu, Female, 6000
// 5, Joshua, Hunter, jstone4@google.cn, Male, 50000
// 8, Jacqueline, Hunter, jfields7@dagondesign.com, Female, 20000000
// 2, Harry, Hunter, hhunter1@webnode.com, Male, 80
// 6, Rose, Spencer, rjohnson5@odnoklassniki.ru, Female, 400000
// 1, Jimmy, Spencer, jspencer0@cnet.com, Male, 9
// 3, Benjamin, Spencer, bmorgan2@unblog.fr, Male, 700
//
// id, first_name, last_name, email, gender, amount
// 9, Donald, Bryant, lharper8@wunderground.com, Female, 100000000
// 8, Jacqueline, Hunter, jfields7@dagondesign.com, Female, 20000000
// 7, Donald, Bryant, dbryant6@redcross.org, Male, 3000000
// 6, Rose, Spencer, rjohnson5@odnoklassniki.ru, Female, 400000
// 5, Joshua, Hunter, jstone4@google.cn, Male, 50000
// 4, Teresa, Hunter, thall3@arizona.edu, Female, 6000
// 3, Benjamin, Spencer, bmorgan2@unblog.fr, Male, 700
// 2, Harry, Hunter, hhunter1@webnode.com, Male, 80
// 10, Donald, Bryant, hmarshall9@stumbleupon.com, Male, € 9
// 1, Jimmy, Spencer, jspencer0@cnet.com, Male, 9
Documentation

See godoc for more documentation and examples.

License

Released under the MIT License.

Documentation

Overview

Package xlsxtra provides extra utilities for the xlsx package (https://github.com/tealeg/xlsx) to manipulate excel files:

- Sort(), SortByHeaders: multi-column (reverse) sort of selected rows. (Note that columns are one based, not zero based to make reverse sort possible.)

- AddBool(), AddInt(), AddFloat(), ...: shortcut to add a cell to a row with the right type.

- NewStyle(): create a style and set the ApplyFill, ApplyFont, ApplyBorder and ApplyAlignment automatically.

- NewStyles(): create a slice of styles based on a color palette

- Sheets: access sheets by name instead of by index

- Col: access cell values of a row by column header title

- SetRowStyle: set style of all cells in a row

- ToString: convert a xlsx.Row to a slice of strings

See Col(umn) and Sort example for a quick introduction.

Example 
package main

import (
	"fmt"

	"github.com/stanim/xlsxtra"
)

func main() {
	fmt.Println(xlsxtra.ColStr[26], xlsxtra.StrCol["AA"])
}

Output:

Z 27

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	// ColStr maps an integer column index to its name
	ColStr [maxCol]string
	// StrCol maps a column name to its integer index
	StrCol = make(map[string]int)
)

Functions

func Abs 

func Abs(s string) string

Abs converts a coordinate to an absolute coordinate (An invalid string is returned unaltered.)

Example 
package main

import (
	"fmt"

	"github.com/stanim/xlsxtra"
)

func main() {
	fmt.Println(xlsxtra.Abs("B12"))
	fmt.Println(xlsxtra.Abs("C5:G20"))
	fmt.Println(xlsxtra.Abs("Invalid"))
}

Output:

$B$12
$C$5:$G$20
Invalid

func ColRange 

func ColRange(start, end string) []string

ColRange gives a range of intervals. (Returns empty slice for invalid input.)

Example 
package main

import (
	"fmt"

	"github.com/stanim/xlsxtra"
)

func main() {
	fmt.Println(xlsxtra.ColRange("X", "AD"))
	fmt.Println(xlsxtra.ColRange("1", "AD"))
}

Output:

[X Y Z AA AB AC AD]
[]

func Coord 

func Coord(col, row int) string

Coord converts integer col and row to string coordinate. (col is one based.)

Example 
package main

import (
	"fmt"

	"github.com/stanim/xlsxtra"
)

func main() {
	fmt.Println(xlsxtra.Coord(2, 12))
	fmt.Println(xlsxtra.Coord(0, 12))
}

Output:

B12
?12

func CoordAbs 

func CoordAbs(col, row int) string

CoordAbs converts integer col and row to absolute string coordinate. (col is one based.)

Example 
package main

import (
	"fmt"

	"github.com/stanim/xlsxtra"
)

func main() {
	fmt.Println(xlsxtra.CoordAbs(2, 12))
	fmt.Println(xlsxtra.CoordAbs(0, 12))
}

Output:

$B$12
?12

func NewStyle 

func NewStyle(color string, font *xlsx.Font,
	border *xlsx.Border, align *xlsx.Alignment) *xlsx.Style

NewStyle creates a new style with color and boldness

func NewStyles 

func NewStyles(colors []string, font *xlsx.Font,
	border *xlsx.Border,
	align *xlsx.Alignment) []*xlsx.Style

NewStyles creates styles with color and boldness

func RangeBounds 

func RangeBounds(rg string) (int, int, int, int, error)

RangeBounds converts a range string into boundaries: min_col, min_row, max_col, max_row. Cell coordinates will be converted into a range with the cell at both end.

Example 
package main

import (
	"fmt"

	"github.com/stanim/xlsxtra"
)

func main() {
	fmt.Println(xlsxtra.RangeBounds("A1:E6"))
	fmt.Println(xlsxtra.RangeBounds("$A$1:$E$6"))
	fmt.Println(xlsxtra.RangeBounds("A1"))
	// invalid: no column name given
	fmt.Println(xlsxtra.RangeBounds("11:E6"))
	// invalid: row zero does not exist
	fmt.Println(xlsxtra.RangeBounds("A0:E6"))
}

Output:

1 1 5 6 <nil>
1 1 5 6 <nil>
1 1 1 1 <nil>
0 0 0 0 Invalid range "11:E6"
0 0 0 0 Invalid range "A0:E6"

func Sort 

func Sort(sheet *Sheet, start, end int,
	columns ...int)

Sort sheet rows according to multi column. (Note that columns are one based, not zero based to make reverse sort possible.)

Example

ExampleSort demonstrates multi column sort 

package main

import (
	"fmt"
	"strings"

	"github.com/stanim/xlsxtra"
)

func main() {
	sheet, err := xlsxtra.OpenSheet(
		"xlsxtra_test.xlsx", "sort_test.go")
	if err != nil {
		fmt.Println(err)
		return
	}
	// multi column sort
	xlsxtra.Sort(sheet, 1, -1,
		3,  // last name
		-2, // first name (reverse order)
		7,  // test empty column
		6,  // ip address
	)
	for _, row := range sheet.Rows {
		fmt.Println(
			strings.Join(xlsxtra.ToString(row.Cells), ", "))
	}
	fmt.Println()
	// by header
	col := xlsxtra.NewCol(sheet, 1)
	err = xlsxtra.SortByHeaders(sheet, 1, -1, col,
		"-amount", // reverse order
		"first_name",
	)
	if err != nil {
		fmt.Println(err)
		return
	}
	for _, row := range sheet.Rows {
		fmt.Println(strings.Join(xlsxtra.ToString(row.Cells), ", "))
	}

}

Output:

id, first_name, last_name, email, gender, amount
9, Donald, Bryant, lharper8@wunderground.com, Female, 100000000
7, Donald, Bryant, dbryant6@redcross.org, Male, 3000000
10, Donald, Bryant, hmarshall9@stumbleupon.com, Male, € 9
4, Teresa, Hunter, thall3@arizona.edu, Female, 6000
5, Joshua, Hunter, jstone4@google.cn, Male, 50000
8, Jacqueline, Hunter, jfields7@dagondesign.com, Female, 20000000
2, Harry, Hunter, hhunter1@webnode.com, Male, 80
6, Rose, Spencer, rjohnson5@odnoklassniki.ru, Female, 400000
1, Jimmy, Spencer, jspencer0@cnet.com, Male, 9
3, Benjamin, Spencer, bmorgan2@unblog.fr, Male, 700

id, first_name, last_name, email, gender, amount
9, Donald, Bryant, lharper8@wunderground.com, Female, 100000000
8, Jacqueline, Hunter, jfields7@dagondesign.com, Female, 20000000
7, Donald, Bryant, dbryant6@redcross.org, Male, 3000000
6, Rose, Spencer, rjohnson5@odnoklassniki.ru, Female, 400000
5, Joshua, Hunter, jstone4@google.cn, Male, 50000
4, Teresa, Hunter, thall3@arizona.edu, Female, 6000
3, Benjamin, Spencer, bmorgan2@unblog.fr, Male, 700
2, Harry, Hunter, hhunter1@webnode.com, Male, 80
10, Donald, Bryant, hmarshall9@stumbleupon.com, Male, € 9
1, Jimmy, Spencer, jspencer0@cnet.com, Male, 9

func SortByHeaders 

func SortByHeaders(sheet *Sheet, start, end int,
	col Col, headers ...string) error

SortByHeaders sort sheet rows by multiple column header titles. (If a header title is prefixed by "-", it will be reversed sorted.)

func SplitCoord 

func SplitCoord(coord string) (string, int, error)

SplitCoord splits a coordinate string into column and row. (For example "AA19" is split into "AA" & "19")

func ToString 

func ToString(cells []*xlsx.Cell) []string

ToString converts row to string slice

Example 
package main

import (
	"fmt"

	"github.com/stanim/xlsxtra"
	"github.com/tealeg/xlsx"
)

func main() {
	headers := []string{"Rob", "Robert", "Ken"}
	sheet, err := xlsx.NewFile().AddSheet("Sheet1")
	if err != nil {
		fmt.Println(err)
	}
	row := sheet.AddRow()
	for _, title := range headers {
		row.AddCell().SetString(title)
	}
	fmt.Printf("%v", xlsxtra.ToString(row.Cells))
}

Output:

[Rob Robert Ken]

func Transpose 

func Transpose(cells [][]*xlsx.Cell) [][]*xlsx.Cell

Transpose rows into columns and vice versa

Types

type Col 

type Col map[string]int

Col retrieves values by header label from a row

Example 
package main

import (
	"fmt"

	"github.com/stanim/xlsxtra"
	"github.com/tealeg/xlsx"
)

func main() {
	type Item struct {
		Name   string
		Price  float64
		Amount int
	}
	sheet, err := xlsxtra.NewFile().AddSheet("Basket")
	if err != nil {
		fmt.Println(err)
		return
	}
	// column header
	var headers = []string{"item", "price", "amount", "total"}
	header := sheet.AddRow()
	for _, title := range headers {
		header.AddString(title)
	}
	style := xlsxtra.NewStyle(
		"00ff0000", // color
		&xlsx.Font{Size: 10, Name: "Arial", Bold: true}, // bold
		nil, // border
		nil, // alignment
	)
	header.SetStyle(style)
	// items
	var items = []Item{
		{"chocolate", 4.99, 2},
		{"cookies", 6.45, 3},
	}
	var row *xlsxtra.Row
	for i, item := range items {
		row = sheet.AddRow()
		row.AddString(item.Name)
		row.AddFloat("0.00", item.Price)
		row.AddInt(item.Amount)
		row.AddFormula("0.00",
			fmt.Sprintf("B%d*C%d", i+1, i+1))
	}
	// column Col type
	col := xlsxtra.NewCol(sheet, 1)
	price, err := col.Float(row, "price")
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println(price)
}

Output:

6.45

func NewCol 

func NewCol(sheet *Sheet, row int) Col

NewCol creates a new Col from a header row

func (Col) Bool 

func (c Col) Bool(row *Row, header string) (bool,
	error)

Bool value of (row,col) in spreadsheet

func (Col) BoolMap 

func (c Col) BoolMap(row *Row, headers []string) (
	map[string]bool, error)

BoolMap value of (row,col) in spreadsheet

func (Col) Float 

func (c Col) Float(row *Row, header string) (float64,
	error)

Float value of (row,col) in spreadsheet

func (Col) Index 

func (c Col) Index(title string) (int, error)

Index of a given column header title

func (Col) IndexRow 

func (c Col) IndexRow(
	row *Row, title string) (int, error)

IndexRow returns index of a given column header title inside a row.

func (Col) Indices 

func (c Col) Indices(headers ...string) (
	[]int, error)

Indices of given column header titles

func (Col) Int 

func (c Col) Int(row *Row, header string) (int,
	error)

Int value of (row,col) in spreadsheet

func (Col) String 

func (c Col) String(row *Row, header string) (string,
	error)

String value of (row,col) in spreadsheet

func (Col) StringFloatMap 

func (c Col) StringFloatMap(row *Row, header string,
	dmap map[string]float64, val float64, sep string,
	chars int) error

StringFloatMap converts column with days string into a map of floats.

type File 

type File struct {
	*xlsx.File
	// contains filtered or unexported fields
}

File extends xlsx.File

func NewFile 

func NewFile() *File

NewFile creates new spreadsheet file

func OpenFile 

func OpenFile(fn string) (*File, error)

OpenFile opens a new excel file

func (*File) AddSheet 

func (f *File) AddSheet(name string) (*Sheet, error)

AddSheet with certain name to spreadsheet file

func (*File) SheetByIndex 

func (f *File) SheetByIndex(index int) *Sheet

SheetByIndex get sheet by index from spreadsheet

func (*File) SheetByName 

func (f *File) SheetByName(name string) (*Sheet, error)

SheetByName get sheet by name from spreadsheet

func (*File) SheetMap 

func (f *File) SheetMap() map[string]*Sheet

SheetMap returns a map of sheets by name

func (*File) SheetRange 

func (f *File) SheetRange(start, end int) []*Sheet

SheetRange returns sheet range including end sheet. Negative indices can be used.

type MultiColumnSort 

type MultiColumnSort struct {
	Sheet      *Sheet
	Columns    []int
	Start, End int
}

MultiColumnSort implements the Sort interface. It provides multi-column sort for certain rows of a sheet, which are selected by begin and end indices. If End is is -1, the last row of the sheet will be selected.

func NewMultiColumnSort 

func NewMultiColumnSort(
	sheet *Sheet, start, end int) *MultiColumnSort

NewMultiColumnSort creates a new multi column sorter.

func (*MultiColumnSort) Len 

func (m *MultiColumnSort) Len() int

Len is part of sort.Interface.

func (*MultiColumnSort) Less 

func (m *MultiColumnSort) Less(i, j int) bool

Less is part of sort.Interface. It is implemented by looping along the indices until it finds a comparison that is either Less or !Less.

func (*MultiColumnSort) Sort 

func (m *MultiColumnSort) Sort(columns ...int)

Sort executes the multi-column sort of the rows

func (*MultiColumnSort) Swap 

func (m *MultiColumnSort) Swap(i, j int)

Swap is part of sort.Interface.

type Row 

type Row struct {
	*xlsx.Row
}

Row of a sheet

func Rows 

func Rows(rows []*xlsx.Row) []*Row

Rows converts slice of xlsx.Row into Row

func (*Row) AddBool 

func (row *Row) AddBool(x ...bool) *xlsx.Cell

AddBool adds a cell with bool as 1 or 0 to a row

func (*Row) AddEmpty 

func (row *Row) AddEmpty(n int)

AddEmpty adds n empty cells to a row

func (*Row) AddFloat 

func (row *Row) AddFloat(format string, x ...float64,
) *xlsx.Cell

AddFloat adds a cell with float64 value to a row

func (*Row) AddFormula 

func (row *Row) AddFormula(format string,
	formula ...string) *xlsx.Cell

AddFormula adds a cell with formula to a row

func (*Row) AddInt 

func (row *Row) AddInt(x ...int) *xlsx.Cell

AddInt adds a cell with int value to a row

func (*Row) AddString 

func (row *Row) AddString(x ...string) *xlsx.Cell

AddString adds a cell with string value to a row

func (*Row) SetStyle 

func (row *Row) SetStyle(style *xlsx.Style)

SetStyle set style to all cells of a row

type Sheet 

type Sheet struct {
	*xlsx.Sheet
}

Sheet extends xlsx.Sheet

func OpenSheet 

func OpenSheet(fn string, name string) (
	*Sheet, error)

OpenSheet open a sheet from an xlsx file. If you need to use multiple sheets from one file use the Sheets type instead.

func Sheets 

func Sheets(sheets []*xlsx.Sheet) []*Sheet

Sheets converts slice of xlsx.Sheet into Sheet

func (*Sheet) AddRow 

func (sheet *Sheet) AddRow() *Row

AddRow adds a row to a sheet

func (*Sheet) Cell 

func (sheet *Sheet) Cell(coord string) (
	*xlsx.Cell, error)

Cell returns a cell based on coordinate string.

Example 
package main

import (
	"fmt"

	"github.com/stanim/xlsxtra"
)

func main() {
	file := xlsxtra.NewFile()
	sheet, err := file.AddSheet("Sheet")
	if err != nil {
		fmt.Println(err)
		return
	}
	row := sheet.AddRow()
	cell := row.AddCell()
	cell.Value = "I am a cell!"
	cell, err = sheet.Cell("A1")
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println(cell.Value)
}

Output:

I am a cell!

func (*Sheet) CellRange 

func (sheet *Sheet) CellRange(rg string) (
	[][]*xlsx.Cell, error)

CellRange returns all cells by row

Example 
package main

import (
	"fmt"

	"github.com/stanim/xlsxtra"
	"github.com/tealeg/xlsx"
)

func main() {
	var print = func(cells [][]*xlsx.Cell) {
		for _, r := range cells {
			fmt.Printf("|")
			for _, c := range r {
				fmt.Printf("%s|", c.Value)
			}
			fmt.Println()
		}
	}
	file := xlsxtra.NewFile()
	sheet, err := file.AddSheet("Sheet")
	if err != nil {
		fmt.Println(err)
		return
	}
	data := [][]string{
		{"A1", "B1"},
		{"A2", "B2"},
	}
	for _, r := range data {
		row := sheet.AddRow()
		for _, c := range r {
			row.AddString(c)
		}
	}
	cells, err := sheet.CellRange("A1:B2")
	if err != nil {
		fmt.Println(err)
		return
	}
	print(cells)
	fmt.Println()
	print(xlsxtra.Transpose(cells))
}

Output:

|A1|B1|
|A2|B2|

|A1|A2|
|B1|B2|

func (*Sheet) Row 

func (sheet *Sheet) Row(row int) *Row

Row returns one based row

func (*Sheet) RowRange 

func (sheet *Sheet) RowRange(start, end int) []*Row

RowRange return all rows.

Source Files

View all Source files

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.