README
¶
xls
A maintained fork of extrame/xls at tag
v0.0.1 — upstream commit
f02a04c
— re-moduled as github.com/Standard-Innovation/xls and carrying number-format
fixes. Apache-2.0, as upstream: see LICENSE and NOTICE.
This is not original work. Apart from the files NOTICE lists as added or
modified, every file is upstream's, unmodified — including LICENSE, which
is upstream's verbatim. NOTICE lists exactly which files differ from
v0.0.1, and what changed in each.
What changed and why is summarised below. The change-by-change record lives in this repository's commit history rather than in a design document checked in here, so there is one place to read it and it cannot drift from the code.
Why this fork exists
v0.0.1 decodes some numeric cells wrongly, and there is no seam to correct it
from outside the package: Row.cols and WorkSheet.rows are unexported,
contentHandler is an unexported interface, and although WorkBook.Formats is
exported, Format.str is not — so a consumer can learn a cell's number-format
index but never its format string. The rendering has to be fixed in the reader.
Two defects are fixed here, and they have to be fixed together: correcting only the first turns a numeric cell that rendered as a timestamp into one that renders 100x too large. A third change renders percent formats.
-
XfRk.Stringrendered every RK cell carrying a user-defined number format (index >= 164, with a FORMAT record) as an RFC3339 timestamp, whatever the format actually was — so a cell carrying a numeric format rendered as a date. It now classifies the format string and takes the timestamp path only for a real date format. Date-formatted cells still render as RFC3339 timestamps.Their values are unchanged for the plain-integer and float encodings — between them, every whole-day date. A date time stored in the integer/hundredths encoding does change, and is corrected: Excel uses that encoding whenever the serial times 100 is integral (06:00, 12:00, 18:00 and every 14.4-minute multiple), and such a cell used to render as a plausible but wrong date, because
timeFromExcelTimeoverflows int64 on the hundred-fold serial. A serial for 2024-01-15 12:00 rendered as2028-11-09T08:54:12Z. -
RK.numberdecoded the RK payload with an unsigned shift, so negative 30-bit integers decoded as large positive values near 2^30; and it ignored the record's fX100 flag on the integer variant, so a cell holding 9999.25 decoded as 999925. Excel prefers that encoding for any value with at most two decimals that fits 30 bits, which covers most two-decimal values. Upstream's master branch carries an equivalent fix. -
Percent-formatted cells rendered as their bare stored fraction, so a cell Excel prints as
80.00%read0.8, and a caller could not tell a share from a share x 100. A percent-formatted cell now renders as Excel prints it: the stored value cut to 15 significant digits, Excel's display precision, then scaled by 100; rounded half away from zero; padded and trimmed from the outermost0placeholders; grouped as the format says; then%. The section for the value's sign applies, as in Excel. This applies to RK, MULRK and NUMBER cells under the built-in0%(9) or0.00%(10), or under a FORMAT record for any index whose sections are all percent formats this package can reproduce exactly. For percent formats, a FORMAT record for an index decides over the built-in percent indices.
Not fixed here: apart from percent formats, NumberCol.String still ignores
number formats, so a NUMBER-encoded cell renders as its full stored precision
rather than as printed, and NUMBER cells do not take the date path that RK
cells do. FORMULA cells still render as FormulaCol. Rendering numbers per
their format string needs the whole numeric format grammar and changes the
output of every formatted numeric cell, so it is deliberately separate work.
Known limitation of the format classification
isDateFormat recognises Excel's ASCII date codes (y m d h s). Excel
normally writes those codes even for a localised display format, but a file
written by some localised builds can carry native codes instead — a Cyrillic
дд.мм.гггг, say. Such a format is classified as numeric, so the cell renders
as its serial number rather than as a date. v0.0.1 rendered it as a date by
rendering everything as a date, which is the defect being fixed here. The
failure mode is a visibly wrong cell rather than a silently wrong value, and
covering it properly means a per-locale code table, so it is left open.
Known limitations of percent rendering
A percent format is rendered only when each of its numeric sections is made
of 0 and # placeholders, one ., , grouping, and one trailing %,
with colour or plain locale tags such as [Red] or [$-409]; a negative
section may also start its number with a literal -. A fourth section
applies only to text and is not read. Every cell under any other percent
format keeps its bare stored value rather than a rendering that differs from
Excel's. The whole format falls back, not one section, so a column never
mixes percentages with bare fractions. That covers formats with:
- literal text, quoted or not, including signs, brackets and spaces
(
+0.0%,0.0% "p.a.",0.00 %,0.0%;(0.0%),%0); - padding or fill (
0.00%_),*), or?placeholders; - a fraction (
# ?/?%), an exponent (0.00E+00%), a,that scales by 1000, consecutive commas, or more than one%; - a condition in any section (
0%;[>1]0.0%), a currency tag ([$€-407]), or a locale tag that selects native digits; - an empty section (
0%;).
A negative value that rounds to zero under a single-section format keeps
its sign, so -0.00001 under 0.00% renders as -0.00%. NaN and infinite
values keep their bare value.
Known issues inherited from upstream
worksheet.goprints record-reading errors to stdout withfmt.Println.addFormatcallsos.Exit(1)when the format map is nil.- The compound-file reader sizes two slices from header counts nothing
validates, so a few KB of input can reserve roughly 250 MB. Both are bounded
by that reader's own limit, both are reported upstream, and neither can be
clamped here without trading a bounded allocation for a silently truncated
read.
container.goclamps the one field where a clamp provably changes no decode. - The BIFF5 FORMAT record has a one-byte length, which
Head.Size uint16misreads. - The tree predates modern gofmt comment spacing and is left as upstream had it
so the fix stays reviewable against
v0.0.1. CI's gofmt check therefore covers only the files this fork adds, and not the inherited ones it edits.
Do not sync XfRk.String from upstream
Upstream's master branch has since rewritten the same block, and its version must not be taken:
- it renders the date path through the format string (
goyymmdd) rather than RFC3339, which breaks the ISO-timestamp shape callers read dates in; - it classifies formats by substring (
"#",".00","m/y","h:"), which reads a plain0.0as a date; and - its
NumberCol.Stringdereferenceswb.Formats[fNo].strfor any non-zero format index, without checking that a FORMAT record exists — so it nil-panics on any built-in format other than index 0, which its own guard happens to exclude.
Its RK.number fix is sound, and equivalent to this one: it shifts
int32(rk) >> 2 in both branches and relies on the sign-extended high bits
being shifted out past bit 63, where this one shifts the unsigned word in the
float branch. Same values, different text.
Upstream README
The text below is upstream's, and describes the package under its original module path.
Pure Golang xls library writen by MinkTech(chinese).
Thanks for contributions from Tamás Gulácsi.
English User please mailto Liu Ming
This is a xls library writen in pure Golang. Almostly it is translated from the libxls library in c.
It has just the reading function without the format.
Basic Usage
- Use Open function for open file
- Use OpenReader function for open xls from a reader
These methods will open a workbook object for reading, like
func (w *WorkBook) ReadAllCells() (res [][]string) {
for _, sheet := range w.Sheets {
w.PrepareSheet(sheet)
if sheet.MaxRow != 0 {
temp := make([][]string, sheet.MaxRow+1)
for k, row := range sheet.Rows {
data := make([]string, 0)
if len(row.Cols) > 0 {
for _, col := range row.Cols {
if uint16(len(data)) <= col.LastCol() {
data = append(data, make([]string, col.LastCol()-uint16(len(data))+1)...)
}
str := col.String(w)
for i := uint16(0); i < col.LastCol()-col.FirstCol()+1; i++ {
data[col.FirstCol()+i] = str[i]
}
}
temp[k] = data
}
}
res = append(res, temp...)
}
}
return
}
Documentation
¶
Overview ¶
xls package use to parse the 97 -2004 microsoft xls file(".xls" suffix, NOT ".xlsx" suffix )
there are some example in godoc, please follow them.
Index ¶
- Constants
- Variables
- type BlankCol
- type CellRange
- type Col
- type Coler
- type Font
- type FontInfo
- type Format
- type FormulaCol
- type HyperLink
- type LabelsstCol
- type MulBlankCol
- type MulrkCol
- type NumberCol
- type RK
- type Ranger
- type RkCol
- type Row
- type SstInfo
- type WorkBook
- type WorkSheet
- type Xf5
- type Xf8
- type XfRk
Examples ¶
Constants ¶
const MJD_0 float64 = 2400000.5
const MJD_JD2000 float64 = 51544.5
Variables ¶
var ErrIsInt = fmt.Errorf("is int")
var ErrNoDirectory = errors.New("xls: compound file header declares no directory stream")
ErrNoDirectory reports a header whose directory stream begins at the end-of-chain sentinel, so the container declares no directory at all.
The container reader underneath builds its directory entries by walking that chain and then indexes the first entry unconditionally, so an empty walk leaves it indexing an empty slice. Rejecting here converts a panic into an error, and cannot reject anything that reads: a container with no directory has nowhere to keep a workbook stream.
var ErrNoWorkbookStream = errors.New("xls: compound file holds no workbook stream")
ErrNoWorkbookStream reports a compound file that holds no stream a workbook can be read from: no directory entry reachable from the root is named "Workbook" (BIFF8) or "Book" (BIFF5). A container whose directory entries exist but are not linked into its directory tree reaches this.
var ErrShortHeader = errors.New("xls: input is shorter than a compound file header")
ErrShortHeader reports input too short to hold a compound file header. It is a permanent property of the input, not a transient read failure: a caller that retries on io.EOF must not retry this, so it deliberately does not wrap io.EOF.
Functions ¶
This section is empty.
Types ¶
type FormulaCol ¶
type FormulaCol struct {
Header struct {
Col
IndexXf uint16
Result [8]byte
Flags uint16
// contains filtered or unexported fields
}
Bts []byte
}
func (*FormulaCol) String ¶
func (c *FormulaCol) String(wb *WorkBook) []string
type HyperLink ¶
type HyperLink struct {
CellRange
Description string
TextMark string
TargetFrame string
Url string
ShortedFilePath string
ExtendedFilePath string
IsUrl bool
}
hyperlink type's content
type LabelsstCol ¶
func (*LabelsstCol) String ¶
func (c *LabelsstCol) String(wb *WorkBook) []string
type MulBlankCol ¶
func (*MulBlankCol) LastCol ¶
func (c *MulBlankCol) LastCol() uint16
func (*MulBlankCol) String ¶
func (c *MulBlankCol) String(wb *WorkBook) []string
type Row ¶
type Row struct {
// contains filtered or unexported fields
}
Row the data of one row
func (*Row) Col ¶
Col Get the Nth Col from the Row, if has not, return nil. Suggest use Has function to test it.
type WorkBook ¶
type WorkBook struct {
Is5ver bool
Type uint16
Codepage uint16
Xfs []st_xf_data
Fonts []Font
Formats map[uint16]*Format
Author string
// contains filtered or unexported fields
}
xls workbook type
func Open ¶
Open one xls file
Example ¶
if xlFile, err := Open("Table.xls"); err == nil {
fmt.Println(xlFile.Author)
}
func OpenReader ¶
OpenReader reads a workbook from a compound file.
The charset parameter both of these used to take is gone. It was never read: the container layer it was handed to ignored it, and the BIFF layer carries its own encoding logic, so it had been inert since before this fork. It survived earlier revisions of this change on the grounds that dropping a parameter breaks published callers for nothing — an argument that stops applying in the one release where the signature is already breaking.
func (*WorkBook) GetSheet ¶
Get one sheet by its number
Example ¶
Output: read the content of first two cols in each row
if xlFile, err := Open("Table.xls"); err == nil {
if sheet1 := xlFile.GetSheet(0); sheet1 != nil {
fmt.Print("Total Lines ", sheet1.MaxRow, sheet1.Name)
col1 := sheet1.Row(0).Col(0)
col2 := sheet1.Row(0).Col(0)
for i := 0; i <= (int(sheet1.MaxRow)); i++ {
row1 := sheet1.Row(i)
col1 = row1.Col(0)
col2 = row1.Col(1)
fmt.Print("\n", col1, ",", col2)
}
}
}
func (*WorkBook) NumSheets ¶
Get the number of all sheets, look into example
Example ¶
if xlFile, err := Open("Table.xls"); err == nil {
for i := 0; i < xlFile.NumSheets(); i++ {
sheet := xlFile.GetSheet(i)
fmt.Println(sheet.Name)
}
}
func (*WorkBook) Parse ¶
func (w *WorkBook) Parse(buf io.ReadSeeker)
func (*WorkBook) ReadAllCells ¶
helper function to read all cells from file Notice: the max value is the limit of the max capacity of lines. Warning: the helper function will need big memeory if file is large.
type WorkSheet ¶
type WorkSheet struct {
Name string
//NOTICE: this is the max row number of the sheet, so it should be count -1
MaxRow uint16
// contains filtered or unexported fields
}
WorkSheet in one WorkBook
Source Files