Text Extraction

Workbook.ExtractText walks every sheet and returns the non-empty cells as formatted strings, each paired with the Cell it came from. It is the quick way to get searchable text out of a workbook, and because each result carries its cell you can go back for the reference, the raw value or the style whenever the string alone is not enough.

There is a sheet-level Sheet.ExtractText with the same shape, useful when you only care about one tab.

Two ways to consume the result

Text() throws away all structure. There is no sheet separator and no row break, only a newline after each cell value, so a wide sheet and a tall one produce output you cannot tell apart. Reach for Cells the moment position matters.

Extracting

wb, err := spreadsheet.Open("extract_styles.xlsx")
if err != nil {
    panic(err)
}
defer wb.Close()

extracted := wb.ExtractText()
fmt.Println(extracted.Text())

for _, ct := range extracted.Sheets[0].Cells {
    fmt.Printf("%s: %s\n", ct.Cell.Reference(), ct.Text)
}

The text is the cell’s formatted value, meaning number formats have already been applied. A date stored as the number 45000 comes back as the date string Excel would show, not as 45000. If you want the underlying value instead, go through the cell: ct.Cell.GetValueAsNumber(), GetValueAsTime() or GetRawValue().

Reading the styling

The cell in a CellText is a full Cell, so its style index gets you to the formatting through the workbook’s style sheet:

cellStyles := wb.StyleSheet.CellStyles()
style := cellStyles[*ct.Cell.X().SAttr]
font := style.GetFont()
fill := style.GetFill()

SAttr is a pointer and is nil for a cell that was never given a style, so check it before dereferencing. Both the font color and the fill color in the example come back as a theme index plus a tint rather than an RGB value, because that is how Excel stores colors picked from the theme palette; resolving them to concrete colors means looking the index up in the workbook theme.

Limitations

Empty cells are skipped, and so is any cell whose formatted value is the empty string. The Cells slice is therefore dense over the cells that had content, not a grid. Indexing it as row*columns + column only works when every cell in the region is populated, which is true of the example’s fixture and generally not true of real workbooks. Use Cell.Reference() to find out where a value actually came from.

Workbook.Sheets() skips sheets marked hidden or very hidden, and ExtractText goes through it, so hidden tabs contribute nothing.

Formula cells contribute their cached result. A workbook whose formulas have never been calculated has nothing cached, and those cells drop out of the extraction entirely.

Only cell text is extracted. Text inside charts, comments, headers, footers and images is not part of the result.

Run the example

The example prints the flattened text of extract_styles.xlsx, then walks a four-by-four block of cells reporting the bold and italic flags, the font color theme and tint, and the fill color theme and tint for each.

git clone https://github.com/unidoc/unioffice-examples.git
cd unioffice-examples/spreadsheet/text_extraction
go run main.go

If this is your first time using UniOffice, follow the getting started guide to create an API key and set up your development environment.

Sample output

Cell 00
Cell 01
Cell 02
Cell 03
Cell 10
...

Row: 0, Column: 0
Text: Cell 00
Font color theme: 0
Font color tint: -0.1499984740745262
Cell color theme: 9
Cell color tint: 0.7999816888943144

Row: 0, Column: 1
Text: Cell 01
Bold
Font color theme: 0
Font color tint: -0.249977111117893
Cell color theme: 9
Cell color tint: 0.7999816888943144