Getting "p12" when reading a time formatted as 11:32:34 AM

[danielbprice]

Description

I am new to working in detail with Excel files; please forgive my ignorance, and thanks in advance for your help. I am receiving XLSX files from a customer, and I have no control over their contents. Among many other columns, in the input files we have a column of timestamps, like this:

However when I process the file with excelize, this column comes out from GetRows() like this:

Steps to reproduce the issue:

1. See the code at https://github.com/danielbprice/excelize-bug-report/blob/main/test.go, it's not much more than:

	sheets := f.GetSheetList()
	rows, err := f.GetRows(sheets[0])
	fmt.Printf("Rows: %#v", rows)

2. Build this code, and feed it the xlsx file at https://github.com/danielbprice/excelize-bug-report/blob/main/test2.xlsx .
   This spreadsheet contains a cut-down test case with only a few interesting cells; I made it using copy/paste from the original customer data in order to try to preserve everything.

Describe the results you received:

When run, the program prints the following, mangling the "pm" designator to "p12":

Rows: [][]string{[]string{"date", "time"}, []string{"Sunday, November 28, 2021", "11:43:29 p12"}, []string{"Sunday, November 28, 2021", "11:39:01 p12"}}

Describe the results you expected:

Times printed as 11:43:29 pm, 11:39:01 pm.

When I load this file into python:

Python 3.8.10 (default, Sep 28 2021, 16:10:42)
[GCC 9.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="test2.xlsx")
>>> sheet = workbook.active
>>> sheet["B1"].value
'time'
>>> sheet["B2"].value
datetime.time(11, 43, 29)
>>> sheet["B3"].value
datetime.time(11, 39, 1)

Which seems right.

Output of go version:

go version go1.16.2 linux/amd64

Excelize version or commit ID:

github.com/xuri/excelize/v2 v2.4.1

Environment details (OS, Microsoft Excel™ version, physical, etc.):
Linux x86

[danielbprice]

Ah, I found it. Here is a delve session which illustrates the bug:

(dlv) n
> github.com/xuri/excelize/v2.parseTime() /home/dp/go/pkg/mod/github.com/xuri/excelize/[email protected]/styles.go:1035 (PC: 0x60ffc7)
  1030:				goFmt = strings.Replace(goFmt, "H", "15", 1)
  1031:			}
  1032:		}
  1033:
  1034:		for _, repl := range replacements {
=>1035:			goFmt = strings.Replace(goFmt, repl.xltime, repl.gotime, 1)
  1036:		}
  1037:		for _, repl := range replacementsGlobal {
  1038:			goFmt = strings.Replace(goFmt, repl.xltime, repl.gotime, -1)
  1039:		}
  1040:		// If the hour is optional, strip it out, along with the possible dangling
(dlv) p repl
struct { github.com/xuri/excelize/v2.xltime string; github.com/xuri/excelize/v2.gotime string } {xltime: "m", gotime: "1"}
(dlv) p goFmt
"3:04:05 pm"
(dlv) p repl
struct { github.com/xuri/excelize/v2.xltime string; github.com/xuri/excelize/v2.gotime string } {xltime: "m", gotime: "1"}
(dlv) n
> github.com/xuri/excelize/v2.parseTime() /home/dp/go/pkg/mod/github.com/xuri/excelize/[email protected]/styles.go:1034 (PC: 0x610022)
  1029:				goFmt = strings.Replace(goFmt, "h", "15", 1)
  1030:				goFmt = strings.Replace(goFmt, "H", "15", 1)
  1031:			}
  1032:		}
  1033:
=>1034:		for _, repl := range replacements {
  1035:			goFmt = strings.Replace(goFmt, repl.xltime, repl.gotime, 1)
  1036:		}
  1037:		for _, repl := range replacementsGlobal {
  1038:			goFmt = strings.Replace(goFmt, repl.xltime, repl.gotime, -1)
  1039:		}
(dlv) n
> github.com/xuri/excelize/v2.parseTime() /home/dp/go/pkg/mod/github.com/xuri/excelize/[email protected]/styles.go:1035 (PC: 0x60ffc7)
  1030:				goFmt = strings.Replace(goFmt, "H", "15", 1)
  1031:			}
  1032:		}
  1033:
  1034:		for _, repl := range replacements {
=>1035:			goFmt = strings.Replace(goFmt, repl.xltime, repl.gotime, 1)
  1036:		}
  1037:		for _, repl := range replacementsGlobal {
  1038:			goFmt = strings.Replace(goFmt, repl.xltime, repl.gotime, -1)
  1039:		}
  1040:		// If the hour is optional, strip it out, along with the possible dangling
(dlv) p goFmt
"3:04:05 p1"
(dlv) p repl

So in parseTime() we have a replacements table:

(dlv) p  replacements
[]struct { github.com/xuri/excelize/v2.xltime string; github.com/xuri/excelize/v2.gotime string } len: 26, cap: 26, [
	{xltime: "YYYY", gotime: "2006"},
	{xltime: "YY", gotime: "06"},
	{xltime: "MM", gotime: "01"},
	{xltime: "M", gotime: "1"},
	{xltime: "DD", gotime: "02"},
	{xltime: "D", gotime: "2"},
	{xltime: "yyyy", gotime: "2006"},
	{xltime: "yy", gotime: "06"},
	{xltime: "mmmm", gotime: "%%%%"},
	{xltime: "dddd", gotime: "&&&&"},
	{xltime: "dd", gotime: "02"},
	{xltime: "d", gotime: "2"},
	{xltime: "mmm", gotime: "Jan"},
	{xltime: "mmss", gotime: "0405"},
	{xltime: "ss", gotime: "05"},
	{xltime: "s", gotime: "5"},
	{xltime: "mm:", gotime: "04:"},
	{xltime: ":mm", gotime: ":04"},
	{xltime: "m:", gotime: "4:"},
	{xltime: ":m", gotime: ":4"},
	{xltime: "mm", gotime: "01"},
	{xltime: "am/pm", gotime: "pm"},  <---- inserts pm
	{xltime: "m/", gotime: "1/"},
	{xltime: "m", gotime: "1"},  <---- overwrites pm
	{xltime: "%%%%", gotime: "January"},
	{xltime: "&&&&", gotime: "Monday"},
]

So when we are building up a go time format string like: "03:04:05 pm", then two iterations later, we blow over top of the "m" in "pm" and replace it with a 1.

[danielbprice]

Oh rats, this is a duplicate of #1060. Hopefully this will help the next person find this. Could you please make a release with this fix? This bug is pretty bad!

[xuri]

Thanks for your issue, this patch will be release in the next version v2.5.0.