Date Cells in Excel Exported as Numbers Instead of Dates

[xxxcoffee]

I encountered an issue while working with Excel files. Specifically, when I read a Date type cell (including its style), modify some values, and then write it to another Excel file using SetCellStr and SetCellStyle, the date cell in the new Excel file appears as a number. However, the cell value remains a string. If I attempt to edit the cell, its value changes to a number reflecting an Excel date day.

During debugging, I observed that the GetCellStyle function calls the extractNumFmt function to read the NumFmt from Excel, and the xf.NumFmtID property has the correct value for CustomNumFmt (like 178 for yyyy/mmmm etc.).

The problem seems to occur because the range s.NumFmts.NumFmt does not correctly find the appropriate NumFmt for style.NumFmt, leading to the value error.

To resolve this, I added a piece of code to stop the loop when it finds the correct NumFmtID. This adjustment corrected the issue for various formats, such as currency, date, and precision.

Here’s the modified code:

func (f *File) extractNumFmt(xf xlsxXf, s *xlsxStyleSheet, style *Style) {
    if xf.NumFmtID != nil {
        numFmtID := *xf.NumFmtID
        if _, ok := builtInNumFmt[numFmtID]; ok || isLangNumFmt(numFmtID) {
            style.NumFmt = numFmtID
            return
        }
        if s.NumFmts != nil {
            for _, numFmt := range s.NumFmts.NumFmt {

                // HERE IS THE CODE!!! LOOK AT ME! LOOK AT ME!
                if numFmt.NumFmtID == numFmtID {
                    style.NumFmt = numFmtID
                    style.CustomNumFmt = &numFmt.FormatCode
                    break
                }
                // HERE IS THE CODE!!! LOOK AT ME! LOOK AT ME!

                style.CustomNumFmt = &numFmt.FormatCode
                if strings.Contains(numFmt.FormatCode, ";[Red]") {
                    style.NegRed = true
                }
                for numFmtID, fmtCode := range currencyNumFmt {
                    if style.NegRed {
                        fmtCode += ";[Red]" + fmtCode
                    }
                    if numFmt.FormatCode == fmtCode {
                        style.NumFmt = numFmtID
                    }
                }
            }
        }
    }
}

[xxxcoffee]

I encountered an issue while working with Excel files. Specifically, when I read a Date type cell (including its style), modify some values, and then write it to another Excel file using SetCellStr and SetCellStyle, the date cell in the new Excel file appears as a number. However, the cell value remains a string. If I attempt to edit the cell, its value changes to a number reflecting an Excel date day.

During debugging, I observed that the GetCellStyle function calls the extractNumFmt function to read the NumFmt from Excel, and the xf.NumFmtID property has the correct value for CustomNumFmt (like 178 for yyyy/mmmm etc.).

The problem seems to occur because the range s.NumFmts.NumFmt does not correctly find the appropriate NumFmt for style.NumFmt, leading to the value error.

To resolve this, I added a piece of code to stop the loop when it finds the correct NumFmtID. This adjustment corrected the issue for various formats, such as currency, date, and precision.

Here’s the modified code:

func (f *File) extractNumFmt(xf xlsxXf, s *xlsxStyleSheet, style *Style) {
    if xf.NumFmtID != nil {
        numFmtID := *xf.NumFmtID
        if _, ok := builtInNumFmt[numFmtID]; ok || isLangNumFmt(numFmtID) {
            style.NumFmt = numFmtID
            return
        }
        if s.NumFmts != nil {
            for _, numFmt := range s.NumFmts.NumFmt {

                // HERE IS THE CODE!!! LOOK AT ME! LOOK AT ME!
                if numFmt.NumFmtID == numFmtID {
                    style.NumFmt = numFmtID
                    style.CustomNumFmt = &numFmt.FormatCode
                    break
                }
                // HERE IS THE CODE!!! LOOK AT ME! LOOK AT ME!

                style.CustomNumFmt = &numFmt.FormatCode
                if strings.Contains(numFmt.FormatCode, ";[Red]") {
                    style.NegRed = true
                }
                for numFmtID, fmtCode := range currencyNumFmt {
                    if style.NegRed {
                        fmtCode += ";[Red]" + fmtCode
                    }
                    if numFmt.FormatCode == fmtCode {
                        style.NumFmt = numFmtID
                    }
                }
            }
        }
    }
}

By the way, my version is v2.8.0

[xxxcoffee]

Ha, you fix it. thanks