streamxlsx

package module
v1.1.2 Latest Latest
Warning

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

Go to latest
Published: Feb 13, 2021 License: MIT Imports: 10 Imported by: 0

README

streaming XLSX writer for simple tabular data

An .xlsx file is not much more than some gzipped XML files. If all you want is to generate some spreadsheets with tabular data, there is no reason we can't generate the file completely streaming.

This small library does just that. It generates an XLSX file on the fly, streaming. The main goal is to have a way to offer exports of data, for example via http.

Example:

See also ./example_test.go.

	s := streamxls.New(buf)
	s.WriteRow("first row with a simple string", 3.1415)
	s.WriteRow("this is row 2")
	s.WriteRow("3digits pi:", s.Format("0.000", 3.1415))
	s.WriteRow("click there:", Hyperlink{"http://example.com", "clickme", "I'm a tooltip"})
	s.WriteSheet("that was sheet 1")
	s.WriteRow("13") // that's a new sheet
	s.Close()

features

  • streams (almost) the whole file
  • support for basic spreadsheet features: number formatting, hyperlinks, sheets
  • currently no support for colors, fonts, borders
  • likely never support for graphs, merged cells, hidden columns, or formulas.

status

"seems to work". The files have been tested with: gnumeric, Goog spreadsheets, online office 365 Excel, offline Excel, Numbers, Emacs, LibreOffice Calc.

see also

https://github.com/TheDataShed/xlsxreader
https://github.com/tealeg/xlsx/
https://docs.microsoft.com/en-us/office/open-xml/working-with-sheets
https://github.com/psmithuk/xlsx

Limits for xlsx files: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

Documentation

Overview

Package streamxlsx implements a streaming .xlsx (Excel spreadsheat) file writer.

The focus is to easily generate tabular data files.

Example
fh, err := os.Create("./example.xlsx")
if err != nil {
	panic(err)
}
defer fh.Close()

s := New(fh)
s.WriteRow("a string", "hello world!", "expected: 'hello world!'")
s.WriteRow("also a string", "14", "expected: '14'")
s.WriteRow("a number", 14, "expected: 14")
s.WriteRow("a number negative number", s.Format("#,##0 ;[red](#,##0)", -14), "expected: (14)-red")
s.WriteRow("a float", 3.1415, "expected: 3.1415")
s.WriteRow("a float, formatted", s.Format("0.00", 3.1415), "expected: 3.14")
s.WriteRow("a float, also formatted", s.Format("0.000", 3.1415), "expected: 3.142")
s.WriteRow("a link", Hyperlink{"http://example.com", "clickme", "I'm a tooltip"}, "expected: link to 'http://example.com', title'clickme', tooltip 'I'm a tooltip'")
s.WriteRow("a link", Hyperlink{"http://example.com/v2", "clickmev2", "I'm a tooltipv2"}, "expected: link to 'http://example.com/v2', title'clickmev2', tooltip 'I'm a tooltipv2'")
s.WriteRow("a datetime", s.Format(DefaultDatetimeFormat, time.Date(2010, 10, 10, 10, 10, 10, 0, time.UTC)), "expected: 10/10/2010 10:10 (or 10/10/10)")
s.WriteRow("bools", true, false)
s.WriteRow("empty cell", nil, "<-- empty cell")
s.WriteRow()
s.WriteRow()
s.WriteRow("there should be another sheet with a single value")
s.WriteSheet("that was sheet 1")

s.WriteRow("13")
s.WriteSheet("that was sheet 2")
if err := s.Close(); err != nil {
	panic(err)
}
Output:

Index

Examples

Constants

View Source
const DefaultDatetimeFormat = "m/d/yy h:mm"

Variables

This section is empty.

Functions

func AsRef

func AsRef(column, row int) string

AsRef makes an 'A13' style ref. Arguments are 0-based.

Types

type Cell

type Cell struct {
	Ref          string  `xml:"r,attr"` // "A1" &c.
	Type         string  `xml:"t,attr,omitempty"`
	Style        *int    `xml:"s,attr,omitempty"`
	Value        string  `xml:"v,omitempty"`
	InlineString *string `xml:"is>t,omitempty"`
	// contains filtered or unexported fields
}

These can be passed to `WriteRow()` if you want total control. WriteRow() will fill int the `.Ref` value. Exactly one of Value or InlineString should be set.

func (Cell) String

func (c Cell) String() string
type Hyperlink struct {
	URL, Title, Tooltip string
}

Adds a hyperlink in a cell. You can use these as a value in WriteRow(). (implementation detail: parts of the hyperlink datastructure is only written when closing a sheet, so they are buffered)

type NumFmt

type NumFmt struct {
	ID   int    `xml:"numFmtId,attr"`
	Code string `xml:"formatCode,attr"`
}

type StreamXLSX

type StreamXLSX struct {

	// The stylesheet will be written on Close(). You generally won't want to
	// use this directly, but via `Format()`.
	Styles *Stylesheet
	// contains filtered or unexported fields
}

func New

func New(w io.Writer) *StreamXLSX

New creates a new file. Do Close() it afterwards. No need to check every write for errors, Close() will return the last error.

A StreamXLSX is not safe to use with multiple go routines at the same time.

func (*StreamXLSX) Close

func (s *StreamXLSX) Close() error

Finish writing the spreadsheet.

func (*StreamXLSX) Format

func (s *StreamXLSX) Format(code string, cell interface{}) Cell

Adds a number format to a cell. Examples or formats are "0.00", "0%", ... This is used to wrap a value in a WriteRow().

func (*StreamXLSX) WriteRow

func (s *StreamXLSX) WriteRow(vs ...interface{}) error

Write a row to the current sheet. No values is a valid (empty) row, and not every row needs to have the same number of elements.

Supported cell datatypes:

all ints and uints, floats, string, bool

Additional special cases:

[]byte: will be base64 encoded
time.Time: handled, but you need to Format() it. For example: s.Format("mm-dd-yy", aTimeTime)
Hyperlink{}: will make the cell a hyperlink
Cell{}: if you want to set everything manually

See Format() to apply number formatting to cells.

func (*StreamXLSX) WriteSheet

func (s *StreamXLSX) WriteSheet(title string) error

WriteSheet closes the currenly open sheet, with the given title. The process is you first do all the `WriteRow()`s for a sheet, followed by its WriteSheet(). There is always an open sheet. You don't have to close the final sheet, but it'll give you a boring name ("sheet N").

type Stylesheet

type Stylesheet struct {
	NumFmts      []NumFmt
	CellXfs      []Xf
	CellStyleXfs []Xf
}

A Stylesheet has all used formats and styles. There is exactly one per document. It's recommended to use `Format()` to work with styles, since that hides all the details.

note: this could have support for fonts, fills, and borders.

func (*Stylesheet) GetCellID

func (s *Stylesheet) GetCellID(xf Xf) int

makes a CellXF ID The ID is the entry in the array, 0-based

func (*Stylesheet) GetCellStyleID

func (s *Stylesheet) GetCellStyleID(xf Xf) int

makes a CellStyleXf ID The ID is the entry in the array, 0-based

func (*Stylesheet) GetNumFmtID

func (s *Stylesheet) GetNumFmtID(code string) int

Get or create the ID for a numfmt. It can return a "default" ID, or create a custom ID. Example of a code is "0.00".

type TestCell added in v1.1.1

type TestCell struct {
	Ref   string
	Type  string
	Value string
	Style int
}

type TestFile added in v1.1.1

type TestFile struct {
	Sheets []TestSheet
}

TestFile is used to test the package. It doesn't implement a full reader.

func TestParse added in v1.1.1

func TestParse(b []byte) (*TestFile, error)

type TestSheet added in v1.1.1

type TestSheet struct {
	Name  string
	Cells []TestCell
}

type Xf

type Xf struct {
	NumFmtID          int  `xml:"numFmtId,attr"`
	FontID            int  `xml:"fontId,attr"`
	FillID            int  `xml:"fillId,attr"`
	BorderID          int  `xml:"borderId,attr"`
	ApplyNumberFormat int  `xml:"applyNumberFormat,attr,omitempty"`
	XfID              *int `xml:"xfId,attr,omitempty"`
}

Xf is either a CellXF or a CellStyleXf. <xf numFmtId="0" fontId="8" fillId="4" borderId="0" xfId="3"/>

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL