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 ¶
const DefaultDatetimeFormat = "m/d/yy h:mm"
Variables ¶
This section is empty.
Functions ¶
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.
type Hyperlink ¶
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 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) 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 ¶
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 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.
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"/>
Source Files