github.com/easysoft/zendata@v0.0.0-20240513203326-705bd5a7fd67/cmd/test/others/func/export/export-areacode.go (about) 1 package main 2 3 import ( 4 "fmt" 5 "path/filepath" 6 "reflect" 7 8 "github.com/360EntSecGroup-Skylar/excelize/v2" 9 "github.com/easysoft/zendata/cmd/test/others/func/comm" 10 "github.com/easysoft/zendata/cmd/test/others/func/model" 11 fileUtils "github.com/easysoft/zendata/pkg/utils/file" 12 ) 13 14 func main() { 15 filePath := "data/areacode/v1.xlsx" 16 sheetNameCity := "city" 17 sheetNameCountry := "country" 18 19 fileUtils.RmFile(filePath) 20 21 fileUtils.MkDirIfNeeded(filepath.Dir(filePath)) 22 23 db := comm.GetDB() 24 25 pos := make([]model.AreaCodeCity, 0) 26 db.Where("NOT deleted").Find(&pos) 27 28 f := excelize.NewFile() 29 30 index := f.NewSheet(sheetNameCity) 31 f.SetActiveSheet(index) 32 33 sheet1 := f.GetSheetName(0) 34 f.DeleteSheet(sheet1) 35 36 var infos []model.TableInfo 37 db.Raw("desc " + model.AreaCodeCity{}.TableName()).Scan(&infos) 38 39 excelColNameArr, excelColNameHeader := comm.GetExcelColsByTableDef(infos) 40 fieldNames := comm.GetStructFields(model.AreaCodeCity{}) 41 42 // gen headers 43 for index, name := range excelColNameHeader { 44 excelColName := excelColNameArr[index] 45 excelColId := fmt.Sprintf("%s%d", excelColName, 1) 46 47 f.SetCellValue(sheetNameCity, excelColId, name) 48 } 49 50 // gen rows 51 for rowIndex, po := range pos { 52 for fieldIndex, fieldName := range fieldNames { 53 val := "" 54 55 if fieldName == "Id" { 56 val = fmt.Sprintf("%d", reflect.ValueOf(po).FieldByName(fieldName).Uint()) 57 } else { 58 val = reflect.ValueOf(po).FieldByName(fieldName).String() 59 } 60 61 excelColName := excelColNameArr[fieldIndex] 62 excelColId := fmt.Sprintf("%s%d", excelColName, rowIndex+2) 63 64 f.SetCellValue(sheetNameCity, excelColId, val) 65 } 66 } 67 68 // 69 pos2 := make([]model.AreaCodeCountry, 0) 70 db.Where("NOT deleted").Find(&pos2) 71 72 index2 := f.NewSheet(sheetNameCountry) 73 f.SetActiveSheet(index2) 74 75 var infos2 []model.TableInfo 76 db.Raw("desc " + model.AreaCodeCountry{}.TableName()).Scan(&infos2) 77 78 excelColNameArr2, excelColNameHeader2 := comm.GetExcelColsByTableDef(infos2) 79 fieldNames2 := comm.GetStructFields(model.AreaCodeCountry{}) 80 81 // gen headers 82 for index, name := range excelColNameHeader2 { 83 excelColName := excelColNameArr2[index] 84 excelColId := fmt.Sprintf("%s%d", excelColName, 1) 85 86 f.SetCellValue(sheetNameCountry, excelColId, name) 87 } 88 89 // gen rows 90 for rowIndex, po := range pos2 { 91 for fieldIndex, fieldName := range fieldNames2 { 92 val := "" 93 94 if fieldName == "Id" { 95 val = fmt.Sprintf("%d", reflect.ValueOf(po).FieldByName(fieldName).Uint()) 96 } else { 97 val = reflect.ValueOf(po).FieldByName(fieldName).String() 98 } 99 100 excelColName := excelColNameArr2[fieldIndex] 101 excelColId := fmt.Sprintf("%s%d", excelColName, rowIndex+2) 102 103 f.SetCellValue(sheetNameCountry, excelColId, val) 104 } 105 } 106 107 f.SaveAs(filePath) 108 }