github.com/artisanhe/tools@v1.0.1-0.20210607022958-19a8fef2eb04/slice_to_excel/to_excel.go (about) 1 package slice_to_excel 2 3 import ( 4 "fmt" 5 "reflect" 6 7 "github.com/tealeg/xlsx" 8 9 "github.com/artisanhe/tools/timelib" 10 ) 11 12 type XLSXLabel interface { 13 Label() string 14 } 15 16 func GetExcel(sheetName string, srcSlice interface{}) (file *xlsx.File, err error) { 17 if reflect.TypeOf(srcSlice).Kind() != reflect.Slice { 18 panic(fmt.Errorf("srcSlice must be an slice")) 19 } 20 21 file = xlsx.NewFile() 22 23 err = SetExcel(file, sheetName, reflect.ValueOf(srcSlice)) 24 25 return 26 } 27 28 func SetExcel(f *xlsx.File, sheetName string, reflectValue reflect.Value) (err error) { 29 sheet, _ := f.AddSheet(sheetName) 30 row := sheet.AddRow() 31 reflectType := reflectValue.Type().Elem() 32 33 setLabelRow(row, reflectType) 34 35 for i := 0; i < reflectValue.Len(); i++ { 36 row := sheet.AddRow() 37 err := setToExcelRow(row, reflectValue.Index(i)) 38 if err != nil { 39 return err 40 } 41 } 42 43 return err 44 } 45 46 func setLabelRow(row *xlsx.Row, reflectType reflect.Type) { 47 if reflectType.Kind() != reflect.Struct { 48 panic(fmt.Sprintf("item is not struct")) 49 } 50 51 for i := 0; i < reflectType.NumField(); i++ { 52 structField := reflectType.Field(i) 53 54 if structField.Anonymous { 55 setLabelRow(row, structField.Type) 56 continue 57 } 58 xlsTag := structField.Tag.Get("xlsx") 59 if xlsTag != "" { 60 cell := row.AddCell() 61 cell.SetValue(xlsTag) 62 } 63 } 64 } 65 66 func setToExcelRow(row *xlsx.Row, reflectValue reflect.Value) (err error) { 67 reflectValue = reflect.Indirect(reflectValue) 68 reflectType := reflectValue.Type() 69 70 for i := 0; i < reflectType.NumField(); i++ { 71 structField := reflectType.Field(i) 72 structFieldValue := reflectValue.Field(i) 73 74 if structField.Anonymous { 75 err = setToExcelRow(row, structFieldValue) 76 continue 77 } 78 79 xlsTag := structField.Tag.Get("xlsx") 80 if xlsTag != "" { 81 cell := row.AddCell() 82 formatTag := structField.Tag.Get("format") 83 if formatTag != "" { 84 if t, ok := structFieldValue.Interface().(int64); ok { 85 if formatTag == "currency" { 86 cell.SetFloat(float64(t) / 100) 87 } 88 } 89 } else { 90 if t, ok := structFieldValue.Interface().(timelib.MySQLTimestamp); ok { 91 if !t.IsZero() { 92 cell.SetString(t.String()) 93 } else { 94 cell.SetString("") 95 } 96 } else if t, ok := structFieldValue.Interface().(XLSXLabel); ok { 97 cell.SetString(t.Label()) 98 } else if t, ok := structFieldValue.Interface().(fmt.Stringer); ok { 99 cell.SetString(t.String()) 100 } else { 101 switch structFieldValue.Kind() { 102 case reflect.String, reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32: 103 cell.SetValue(structFieldValue.Interface()) 104 case reflect.Int64, reflect.Uint64: 105 cell.SetString(fmt.Sprintf("%d", structFieldValue.Interface())) 106 case reflect.Float32: 107 cell.SetFloat(float64(structFieldValue.Interface().(float32))) 108 case reflect.Float64: 109 cell.SetFloat(structFieldValue.Interface().(float64)) 110 case reflect.Bool: 111 cell.SetBool(structFieldValue.Interface().(bool)) 112 default: 113 panic(fmt.Sprintf("field[%s] type [%s] is not support", structField.Name, structFieldValue.Kind())) 114 } 115 } 116 } 117 } 118 } 119 return 120 }