GO excelize 读取excel进行时间类型转换（自动转换）

GO excelize 读取excel进行时间类型转换（自动转换）

需求分析

需求：如何自动识别excel中的时间类型数据并转化成对应的 "Y-m-d H:i:s"类型数据。

分析：excelize在读取excel时，GetRows() 返回的都是字符串类型,并且有些时间类型的数据会进行转换，如果全部转化成 float64 格式，然后转换成对应的字符串，并且excelize提供函数

func ExcelDateToTime(excelDate float64, use1904Format bool) (time.Time, error) {...
}

可以将float64 转换成time.Time 类型，time.Time 很容易转化成对应"Y-m-d H:i:s"格式字符串类型数据。所以我们的难点就在于如何自动识别excel中时日期时间类型数据

excel 单元格格式

以下3月1日数据写入到excel中，excel都会识别成2024/3/1，但是对应单元格格式不同，转化为常规类型的话大部分都相同

• 2024年3月1日------------- yyyy"年"m"月"d"日"-------------453352
• 2024/3/1------------- yyyy/m/d-------------453352
• Mar-24------------- mmm-yy-------------453352
• 2024年3月------------- yyyy"年"m"月"-------------453352
• 2024/3/1 0:00-------------yyyy/m/d h:mm-------------453352
• '2024-03-01 00:00:00-------------通用-------------2024-03-01 00:00:00

• excelize 读取

  func parseFileUrl(filePath string) ([]map[string]string, error) {f, err := excelize.OpenFile(filePath)if err != nil {return nil, err}sheetName := f.GetSheetName(0)rows, err := f.GetRows(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 {fmt.Println(rowKey, "-", colKey, ":", value)}}}}return nil, err
  }
  

  结果打印

  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
  

  由此我们可以看出时间类型打印出来的内容不尽相同，这里我们可以想办法把他们全部转化成45352
  
  这里我们就把他们转化成常规类型，常规类型的数据大部分都为45352

  func parseFileUrl(filePath string) ([]map[string]string, error) {f, err := excelize.OpenFile(filePath)if err != nil {return nil, err}sheetName := f.GetSheetName(0)rows, err := f.GetRows(sheetName)if err != nil {return nil, err}//转化为常规类型，对应style NumFmt 0styleId, _ := f.NewStyle(&excelize.Style{NumFmt: 0})//示例例中都放入A列，所以将A列数据全部转化成对应的常规类型_ = f.SetColStyle(sheetName, "A", styleId)rows, err = f.GetRows(sheetName)if len(rows) > 0 {for rowKey, cols := range rows {if len(cols) > 0 {for colKey, value := range cols {fmt.Println(rowKey, "-", colKey, ":", value)}}}}return nil, err
  }
  

  再次进行打印

  0 - 0 : 45352
  1 - 0 : 45352
  2 - 0 : 45352
  3 - 0 : 45352
  4 - 0 : 45352
  5 - 0 : 2024-03-01 00:00:00
  

  这时我们就可以看到大部分数据都已经转化成了45352，所以接下来很简单将数据转化成float64类型，再转化成time.time类型，最后转化成我们想要的数据类型

  func parseFileUrl(filePath string) ([]map[string]string, error) {f, err := excelize.OpenFile(filePath)if err != nil {return nil, err}sheetName := f.GetSheetName(0)rows, err := f.GetRows(sheetName)if err != nil {return nil, err}styleId, _ := f.NewStyle(&excelize.Style{NumFmt: 0})_ = f.SetColStyle(sheetName, "A", styleId)rows, err = f.GetRows(sheetName)if len(rows) > 0 {for rowKey, cols := range rows {if len(cols) > 0 {for colKey, value := range cols {timeFloat, err := strconv.ParseFloat(value, 64)if err != nil {//err 说明无法转化成float64 那么有可能本身是字符串时间进行返回timeTime, err := time.Parse("2006-01-02 15:04:05", value)if err != nil {fmt.Println(rowKey, "-", colKey, ":", value)} else {value = timeTime.Format("2006-01-02 15:04:05")fmt.Println(rowKey, "-", colKey, ":", value)}break}timeTime, _ := excelize.ExcelDateToTime(timeFloat, false)value = timeTime.Format("2006-01-02 15:04:05")fmt.Println(rowKey, "-", colKey, ":", value)}}}}return nil, err
  }
  

  打印结果

  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
  

  此时可以解决了我们的问题，指定对应的列，转化为常规类型，然后再转化为float64（针对无法转化的数据返回），再转化为time.time类型,最后转化成我们需要的类型

  进阶

  那么如何自动进行转化？
  

  其实我们可以根据单元格自定义类型来进行转化，正如上面的例子，如当单元格自定义类型为：

• yyyy"年"m"月"d"日"
• yyyy/m/d
• mmm-yy
• yyyy"年"m"月"
• yyyy/m/d h:mm
• ...

• 等类型的时候我们需要将他们转化成常规类型，然后根据后续操作转化成我们想要的类型。

  如何自定类型判断呢？
  

  其实根据上述转化成常规类型的操作我们就可以知道是哪个字段来进行确定单元格格式类型

  	styleId, _ := f.NewStyle(&excelize.Style{NumFmt: 0})
  

  Style 中的 NumFmt 来进行决定

  我们可以看下excelize 中 针对 NumFmt 的注释(在NewStyle方法上面有详细注释)，这里我只粘贴部分注释代码

  //	 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"年"m"月"
  //	 28    | m"月"d"日"
  //	 29    | m"月"d"日"
  //	 30    | m-d-yy
  //	 31    | yyyy"年"m"月"d"日"
  //	 32    | h"时"mm"分"
  //	 33    | h"时"mm"分"ss"秒"
  //	 34    | 上午/下午 h"时"mm"分"
  //	 35    | 上午/下午 h"时"mm"分"ss"秒
  //	 36    | yyyy"年"m"月
  //	 50    | yyyy"年"m"月
  //	 51    | m"月"d"日
  //	 52    | yyyy"年"m"月
  //	 53    | m"月"d"日
  //	 54    | m"月"d"日
  //	 55    | 上午/下午 h"时"mm"分
  //	 56    | 上午/下午 h"时"mm"分"ss"秒
  //	 57    | yyyy"年"m"月
  //	 58    | m"月"d"日"
  

  我们可以知道NumFmt 对应的值代表着各种单元格格式，此时我们可以整理一下我们需要将其转化成常规类型的数据(只做参考，并没有全部实验，自己可以把常用的实验一下)

  var ConversionTimeNumFmt = []int{14, //m/d/yy15, //d-mmm-yy17, //mmm-yy22, //m/d/yy h:mm27, // yyyy"年"m"月"30, //m-d-yy31, //yyyy"年"m"月"d"日"36, //yyyy"年"m"月50, //yyyy"年"m"月52, //yyyy"年"m"月57, //yyyy"年"m"月
  }
  

  好了，现在我们要转换的单元格格式了，那么接下来我们就可以遍历一下excel的全部单元格格式类型，看一下哪些字段需要进行转化了，不过接下来问题又来了，如何知道excel里面对应的单元格格式呢

  如何知道excel中单元格格式
  

  excelize 中提供方法 GetCellStyle() 可以获取该单元格的所有样式对应的styleId

  // 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) {...
  }
  

  根据styleId 我们可以找到对应的所有样式配置 GetStyle()

  // GetStyle provides a function to get style definition by given style index.
  func (f *File) GetStyle(idx int) (*Style, error) {...
  }
  

  单元格格式 就对应的是 style.NumFmt

  这时我们只需要知道每一个单元格的styleId 对应的 style.NumFmt 就可以将数据进行转化（这里做了三个循环，第一遍是获取了对应styleId, 第二遍是更改表样式，将指定类型转化为常规类型，第三遍就是获取对应的数据）

  // parseFileUrl 解析文件流excel
  func parseFileUrl(filePath string) ([]map[string]string, error) {f, err := excelize.OpenFile(filePath)if err != nil {return nil, err}sheetName := f.GetSheetName(0)rows, err := f.GetRows(sheetName)if err != nil {return nil, err}//读取excel 所有styleId 数组styles := make([]int, 0)//所有需要更改单元格格式的 styleId 数组needChangeStyleIds := make([]int, 0)//所更改的cellsneedChangeCells := make([]string, 0)if len(rows) > 0 {//需要转化成的 style 对应style IdstyleIdZero, _ := f.NewStyle(&excelize.Style{NumFmt: 0})for rowKey, cols := range rows {if len(cols) > 0 {for colKey, _ := range cols {columnNumber, _ := excelize.CoordinatesToCellName(colKey+1, rowKey+1)styleId, _ := f.GetCellStyle(sheetName, columnNumber)if !arrayHelper.InArray(styles, styleId) {styles = append(styles, styleId)}}}}fmt.Println(styles)if len(styles) > 0 {for _, styleId := range styles {style, _ := f.GetStyle(styleId)if arrayHelper.InArray(ConversionTimeNumFmt, style.NumFmt) {needChangeStyleIds = append(needChangeStyleIds, styleId)}}}for rowKey, cols := range rows {if len(cols) > 0 {for colKey, _ := range cols {columnNumber, _ := excelize.CoordinatesToCellName(colKey+1, rowKey+1)styleId, _ := f.GetCellStyle(sheetName, columnNumber)if arrayHelper.InArray(needChangeStyleIds, styleId) {_ = f.SetCellStyle(sheetName, columnNumber, columnNumber, styleIdZero)needChangeCells = append(needChangeCells, columnNumber)}}}}rows, err = f.GetRows(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, _ := excelize.CoordinatesToCellName(colKey+1, rowKey+1)if arrayHelper.InArray(needChangeCells, columnNumber) {timeFloat, err := strconv.ParseFloat(value, 64)if err != nil {//err 说明无法转化成float64 那么有可能本身是字符串时间进行返回timeTime, err := time.Parse("2006-01-02 15:04:05", value)if err != nil {fmt.Println(rowKey, "-", colKey, ":", value)} else {value = timeTime.Format("2006-01-02 15:04:05")fmt.Println(rowKey, "-", colKey, ":", value)}break}timeTime, _ := excelize.ExcelDateToTime(timeFloat, false)value = timeTime.Format("2006-01-02 15:04:05")fmt.Println(rowKey, "-", colKey, ":", value)}}}}}}return nil, err
  }var ConversionTimeNumFmt = []int{14, //m/d/yy15, //d-mmm-yy17, //mmm-yy22, //m/d/yy h:mm27, // yyyy"年"m"月"30, //m-d-yy31, //yyyy"年"m"月"d"日"36, //yyyy"年"m"月50, //yyyy"年"m"月52, //yyyy"年"m"月57, //yyyy"年"m"月
  }
  

  其中InArray方法为

  // InArray 判断元素是否再数组内
  func InArray[T int | float64 | string](array []T, value T) bool {for _, v := range array {if v == value {return true}}return false
  }
  

  通过三次遍历操作，自动转化了时间类型