Requirements Analysis
Requirements: How to automatically identify time-type data in Excel and convert it into the corresponding "Y-m-d H:i:s" type data.
Analysis: When excel is read, GetRows() returns all string types, and some time-type data will be converted. If all converted into float64 format, it will be converted into the corresponding string, and excelize provides a function
func ExcelDateToTime(excelDate float64, use1904Format bool) (, error) {
...
}
float64 can be converted to type, which can be easily converted into the corresponding "Y-m-d H:i:s" format string type data. So our difficulty lies in how to automatically identify the date and time type data in Excel
excel cell format
The following data on March 1st is written to excel, and excel will be recognized as 2024/3/1, but the corresponding cell format is different. Most of the cells will be the same if converted to regular types.
- March 1, 2024------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- 2024/3/1------------- yyyy/m/d-------------453352
- Mar-24------------- mmm-yy-------------453352
- March 2024-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- 2024/3/1 0:00-------------yyyy/m/d h:mm-------------453352
- '2024-03-01 00:00:00-------------------------------------------------2024-03-01 00:00:00
excelize read
func parseFileUrl(filePath string) ([]map[string]string, error) {
f, err := (filePath)
if err != nil {
return nil, err
}
sheetName := (0)
rows, err := (sheetName)
if err != nil {
return nil, err
}
if len(rows) > 0 {
for rowKey, cols := range rows {
if len(cols) > 0 {
for colKey, value := range cols {
(rowKey, "-", colKey, ":", value)
}
}
}
}
return nil, err
}
Results Print
0 - 0 : 45352 1 - 0 : 03-01-24 2 - 0 : Mar-24 3 - 0 : 45352 4 - 0 : 3/1/24 00:00 5 - 0 : 2024-03-01 00:00:00
From this we can see that the time type prints different contents, here we can find a way to convert them all into 45352
Here we convert them into regular types, most of the data of regular types are 45352
func parseFileUrl(filePath string) ([]map[string]string, error) {
f, err := (filePath)
if err != nil {
return nil, err
}
sheetName := (0)
rows, err := (sheetName)
if err != nil {
return nil, err
}
//Convert to regular type, corresponding to style NumFmt 0 styleId, _ := (&{NumFmt: 0})
//In the examples, column A is placed, so all column A data is converted into the corresponding regular type _ = (sheetName, "A", styleId)
rows, err = (sheetName)
if len(rows) > 0 {
for rowKey, cols := range rows {
if len(cols) > 0 {
for colKey, value := range cols {
(rowKey, "-", colKey, ":", value)
}
}
}
}
return nil, err
}
Print again
0 - 0 : 45352 1 - 0 : 45352 2 - 0 : 45352 3 - 0 : 45352 4 - 0 : 45352 5 - 0 : 2024-03-01 00:00:00
At this time, we can see that most of the data has been converted into 45352, so it is easy to convert the data into float64 type, then convert it into type, and finally convert it into the data type we want
func parseFileUrl(filePath string) ([]map[string]string, error) {
f, err := (filePath)
if err != nil {
return nil, err
}
sheetName := (0)
rows, err := (sheetName)
if err != nil {
return nil, err
}
styleId, _ := (&{NumFmt: 0})
_ = (sheetName, "A", styleId)
rows, err = (sheetName)
if len(rows) > 0 {
for rowKey, cols := range rows {
if len(cols) > 0 {
for colKey, value := range cols {
timeFloat, err := (value, 64)
if err != nil {
//err means that it cannot be converted to float64. Then it may be a string time to return. timeTime, err := ("2006-01-02 15:04:05", value)
if err != nil {
(rowKey, "-", colKey, ":", value)
} else {
value = ("2006-01-02 15:04:05")
(rowKey, "-", colKey, ":", value)
}
break
}
timeTime, _ := (timeFloat, false)
value = ("2006-01-02 15:04:05")
(rowKey, "-", colKey, ":", value)
}
}
}
}
return nil, err
}
Print results
0 - 0 : 2024-03-01 00:00:00 1 - 0 : 2024-03-01 00:00:00 2 - 0 : 2024-03-01 00:00:00 3 - 0 : 2024-03-01 00:00:00 4 - 0 : 2024-03-01 00:00:00 5 - 0 : 2024-03-01 00:00:00
At this time, our problem can be solved, specify the corresponding column, convert it to a regular type, and then convert it to float64 (returned for unconvertible data), then convert it to type, and finally convert it to the type we need
Advanced
So how to automatically convert?
In fact, we can convert according to the cell custom type, as in the above example, when the cell custom type is:
- yyyy"year"m"month"d"day"
- yyyy/m/d
- mmm-yy
- yyyy"year"m"month"
- yyyy/m/d h:mm
- ...
- When we are in a different category, we need to convert them into regular types and then convert them into the type we want according to subsequent operations.
How to judge the type in a custom way?
In fact, based on the above-mentioned operations of converting to conventional types, we can know which field is used to determine the cell format type.
styleId, _ := (&{NumFmt: 0})
NumFmt in Style to make the decision
We can look at the comments for NumFmt in excelize (there are detailed comments on the NewStyle method). Here I will only paste some of the comment codes
// Index | Format String // -------+---------------------------------------------------- // 0 | General // 1 | 0 // 2 | 0.00 // 3 | #,##0 // 4 | #,##0.00 // 5 | ($#,##0_);($#,##0) // 6 | ($#,##0_);[Red]($#,##0) // 7 | ($#,##0.00_);($#,##0.00) // 8 | ($#,##0.00_);[Red]($#,##0.00) // 9 | 0% // 10 | 0.00% // 11 | 0.00E+00 // 12 | # ?/? // 13 | # ??/?? // 14 | m/d/yy // 15 | d-mmm-yy // 16 | d-mmm // 17 | mmm-yy // 18 | h:mm AM/PM // 19 | h:mm:ss AM/PM // 20 | h:mm // 21 | h:mm:ss // 22 | m/d/yy h:mm // ... | ... // 37 | (#,##0_);(#,##0) // 38 | (#,##0_);[Red](#,##0) // 39 | (#,##0.00_);(#,##0.00) // 40 | (#,##0.00_);[Red](#,##0.00) // 41 | _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_) // 42 | _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_) // 43 | _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_) // 44 | _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_) // 45 | mm:ss // 46 | [h]:mm:ss // 47 | mm:ss.0 // 48 | ##0.0E+0 // 49 | @ // Number format code in zh-cn language: // // Index | Symbol // -------+------------------------------------------- // 27 | yyyy"year"m"month"// 28 | m "month"d"day"// 29 | m "month"d"day"// 30 | m-d-yy // 31 | yyyy's year, m, month, d, day"// 32 | h "hour"mm" minutes"// 33 | h "time"mm", minute, ss" seconds"// 34 | Morning/PM h "hour" mm" minutes// 35 | AM/PM h "hour"mm"min "ss" seconds// 36 | yyyy's "year"m" month// 50 | yyyy's "year"m" month// 51 | m "month"d" day// 52 | yyyy's year"m" month// 53 | m "month"d" day// 54 | m "month"d" day// 55 | Morning/PM h "hour" mm" minutes// 56 | AM/PM h "hour"mm" minutes"ss" seconds// 57 | yyyy's "year"m" month// 58 | m "month"d"day"
We can know that the corresponding value of NumFmt represents various cell formats. At this time, we can sort out the data we need to convert into regular types (just for reference, and there are no all the experiments. You can use common experiments yourself)
var ConversionTimeNumFmt = []int{
14, //m/d/yy
15, //d-mmm-yy
17, //mmm-yy
22, //m/d/yy h:mm
27, // yyyy"year"m"month" 30, //m-d-yy
31, //yyyyy"year"m"month"d"day" 36, //yyyy"year"m"month 50, //yyyy"year"m"month 52, //yyyy"year"m"month 57, //yyyy"year"m"month}
Okay, now we want to convert the cell format, so we can traverse all cell format types of Excel and see which fields need to be converted. But the next question comes again, how to know the corresponding cell format in Excel
How to know cell format in excel
Provided in excelize GetCellStyle() to get the styleId corresponding to all styles of this cell
// GetCellStyle provides a function to get cell style index by given worksheet
// name and cell reference. This function is concurrency safe.
func (f *File) GetCellStyle(sheet, cell string) (int, error) {
...
}
According to styleId we can find all the corresponding style configurations.
// GetStyle provides a function to get style definition by given style index.
func (f *File) GetStyle(idx int) (*Style, error) {
...
}
The cell format corresponds to
At this time, we only need to know the corresponding styleId of each cell to convert the data (there are three loops here. The first pass is to obtain the corresponding styleId, the second pass is to change the table style, convert the specified type into a regular type, and the third pass is to obtain the corresponding data)
// parseFileUrl parse file stream excelfunc parseFileUrl(filePath string) ([]map[string]string, error) {
f, err := (filePath)
if err != nil {
return nil, err
}
sheetName := (0)
rows, err := (sheetName)
if err != nil {
return nil, err
}
//Read all styleId arrays of excel styles := make([]int, 0)
//All styleId arrays that need to change the cell format needChangeStyleIds := make([]int, 0)
//Changed cells needChangeCells := make([]string, 0)
if len(rows) > 0 {
//The style corresponding to the style Id that needs to be converted into styleIdZero, _ := (&{NumFmt: 0})
for rowKey, cols := range rows {
if len(cols) > 0 {
for colKey, _ := range cols {
columnNumber, _ := (colKey+1, rowKey+1)
styleId, _ := (sheetName, columnNumber)
if !(styles, styleId) {
styles = append(styles, styleId)
}
}
}
}
(styles)
if len(styles) > 0 {
for _, styleId := range styles {
style, _ := (styleId)
if (ConversionTimeNumFmt, ) {
needChangeStyleIds = append(needChangeStyleIds, styleId)
}
}
}
for rowKey, cols := range rows {
if len(cols) > 0 {
for colKey, _ := range cols {
columnNumber, _ := (colKey+1, rowKey+1)
styleId, _ := (sheetName, columnNumber)
if (needChangeStyleIds, styleId) {
_ = (sheetName, columnNumber, columnNumber, styleIdZero)
needChangeCells = append(needChangeCells, columnNumber)
}
}
}
}
rows, err = (sheetName)
if err != nil {
return nil, err
}
if len(rows) > 0 {
for rowKey, cols := range rows {
if len(cols) > 0 {
for colKey, value := range cols {
columnNumber, _ := (colKey+1, rowKey+1)
if (needChangeCells, columnNumber) {
timeFloat, err := (value, 64)
if err != nil {
//err means that it cannot be converted to float64. Then it may be a string time to return. timeTime, err := ("2006-01-02 15:04:05", value)
if err != nil {
(rowKey, "-", colKey, ":", value)
} else {
value = ("2006-01-02 15:04:05")
(rowKey, "-", colKey, ":", value)
}
break
}
timeTime, _ := (timeFloat, false)
value = ("2006-01-02 15:04:05")
(rowKey, "-", colKey, ":", value)
}
}
}
}
}
}
return nil, err
}
var ConversionTimeNumFmt = []int{
14, //m/d/yy
15, //d-mmm-yy
17, //mmm-yy
22, //m/d/yy h:mm
27, // yyyy"year"m"month" 30, //m-d-yy
31, //yyyyy"year"m"month"d"day" 36, //yyyy"year"m"month 50, //yyyy"year"m"month 52, //yyyy"year"m"month 57, //yyyy"year"m"month}
The InArray method is
// InArray determines whether the element is in the array againfunc InArray[T int | float64 | string](array []T, value T) bool {
for _, v := range array {
if v == value {
return true
}
}
return false
}
Through three traversal operations, the time type is automatically converted
The above is the detailed content of the sample code (automatic conversion) of GO excel to read Excel for time type conversion. For more information about GO excel time type conversion, please follow my other related articles!