Get numeric formatted text from a cell

[cognitivepedr0]

Hello,

I am not entirely sure this isn't already possible and I haven't spotted it, however I would need to be able to print out of a XLSX cell the same text which is rendered on the screen when it comes to numerical value, for instance (1,200) - at the moment I get what is called the "row" value, namely -1200 but I would need to the former string.

[cognitivepedr0]

hi all - am I talking rubbish ? It seems to be that numbers value formatted as (123) do not get printed following the same formatting and what I get is the row value.

Is that actually correct or am I doing anything wrong ?

[xuri]

Thanks for your feedback. Which version of Excelize are you using, could you provide code or more details to reproduce this issue?

[cognitivepedr0]

I am following the tutorial and I am writing a simple script in an empty folder, no module defined.

go get github.com/xuri/excelize

You can find the code as a txt file
excel3.txt attachment

The
test.xlsx file is enough to show the issue:

./excel3 /tmp/test.xlsx 1
GetCellValue() -123134
A B
-123134 124214214
10000000 -1232121

But this is not how the numbers are formatted:

Hope this helps.

[xuri]

The Excelize not support to parse all the number formats of the cell, so you'll got the original value of the cell which same with the value you can see in the formula bar.

[cognitivepedr0]

Is there a quick way for me to add the support for such format ?

…

On Wed, Dec 2, 2020 at 10:48 AM xuri ***@***.***> wrote: The Excelize not support to parse all the number formats of the cell, so you'll got the original value of the cell which same with the value you can see in the formula bar. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#660 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQGTMGQSEMZB5GSVFUASXQTSSYLRRANCNFSM4OTGA7EA> .

[xuri]

I think we need to implement ECMA-376, ISO/IEC 29500 §18.8.31 number format strings lexer and parser to format values like Excel and other spreadsheet softwares.

[DGollings]

not directly relevant to this issue, but pasting my workaround here for changing excel built in date formats to rfc3339. Might be useful for anyone hitting the issue I had (unexpected date formats)

Takes the output of f.GetRows and reformats anything it recognizes as a excel date format

func convertDates(f *excelize.File, sheetName string, rows [][]string) [][]string {
	for y := range rows {
		for x := range rows[y] {
			coords, _ := excelize.CoordinatesToCellName(x+1, y+1)
			style, err := f.GetCellStyle(sheetName, coords)
			if err != nil {
				_, _ = fmt.Println(err)
				continue
			}
			rows[y][x] = convertIfDate(f, style, rows[y][x])
		}
	}
	return rows
}

func convertIfDate(f *excelize.File, s int, v string) string {
	if s == 0 {
		return v
	}
	styleSheet := f.Styles
	if s >= len(styleSheet.CellXfs.Xf) {
		return v
	}
	var numFmtID int
	if styleSheet.CellXfs.Xf[s].NumFmtID != nil {
		numFmtID = *styleSheet.CellXfs.Xf[s].NumFmtID
	}
	var timeFormat string
	switch numFmtID {
	case 14:
		//"mm-dd-yy"
		timeFormat = "01-02-06"
	case 15:
		//"d-mmm-yy"
		timeFormat = "02-Jan-06"
	case 16:
		//"d-mmm"
		timeFormat = "02-Jan"
	case 17:
		//"mmm-yy"
		timeFormat = "Jan-06"
	case 22:
		//"m/d/yy h:mm"
		timeFormat = "1/2/06 15:04"
	default:
		return v
	}
	t, err := time.Parse(timeFormat, v)
	if err != nil {
		return v
	}
	return t.Format(time.RFC3339)
}

No error handling unless you consider spewing errors on to the console error handling.
When in doubt, return original value
And it only handles what I would consider easy date formats, only tested with 14 but works well enough for me

[nathj07]

In case it's helpful I thought I'd a sample XLSX file here
number_format_test.xlsx
The result from this library is:

"Sheet1\nCurrency Custom Number 100 100 50908.00 100000 100000 590.00 250000 250000 8.56 1000000 1000000 12345.00",

The number formatting is not applied. I understand, from reading this issue that it may be a substantial amount of work. In our use case it's not a deal breaker, but it would be good to see this resolved.

Thanks for looking into this, and for this excellent library

[xuri]

Thanks for your feedback. I'll consider add the currency number format support in the future.

[xuri]

Hi @cognitivepedr0, @nathj07. Sorry for the late reply. Since the commit dfdd97c, the library supports format cell value by built-in number format code which contains currency symbol, patrial literal in the number format code, placeholder, padding, and rounds numbers. This resolved the cell value read result issue in the test attachment as your provided. Also, reference issue #1199. Please try to upgrade to the master branch code and this feature will be released in the next version.

[oderwat]

This breaks our application because we suddenly get currency symbols in some columns. Is there a way to not get modifications to the values? We tried

		colCells, err = h.rows.Columns(excelize.Options{
			RawCellValue: true,
		})

And also adding this option when opening the file. Neither will revert it back to the raw number. What do we need to do?

[xuri]

Hi @oderwat, thanks for your feedback. Could you provide a workbook as a file attachment without confidential info to reproducible this problem?

[oderwat]

auk_ksg.xlsx

The marked fields (of the second sheet) were reported without the € in past versions of excelize.

[xuri]

Hi @oderwat, thanks for your feedback. Which version of the Excelize and Go language are you using? I have tested with the master branch code by following the code, and it works well. Please try to upgrade and use the master branch code.

package main

import (
    "fmt"

    "github.com/xuri/excelize/v2"
)

func main() {
    f, err := excelize.OpenFile("auk_ksg.xlsx")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    rows, err := f.Rows(f.GetSheetList()[1])
    if err != nil {
        fmt.Println(err)
        return
    }
    for rows.Next() {
        col, err := rows.Columns(excelize.Options{RawCellValue: true})
        if err != nil {
            fmt.Println(err)
            return
        }
        for _, colCell := range col {
            fmt.Print(colCell, "\t")
        }
    }
}

[oderwat]

We used the lastest release (2.8.0) and go 1.21.1. I let a college check with your master branch, she will report here.

[jobe2015]

Thank you very much for your explanation. I checked our code and found that we called the function in another place where the parameter was not passed. So everything is fine.

[oderwat]

@xuri I also want to thank you for the swift reply and the very useful package!