github.com/easysoft/zendata@v0.0.0-20240513203326-705bd5a7fd67/cmd/test/others/func/export/export-company.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/company/v1.xlsx" 16 sheetName1 := "company" 17 18 fileUtils.MkDirIfNeeded(filepath.Dir(filePath)) 19 20 db := comm.GetDB() 21 db.AutoMigrate( 22 &model.DataCompany{}, 23 ) 24 25 pos1 := make([]model.DataCompany, 0) 26 db.Order("id ASC").Find(&pos1) 27 28 f := excelize.NewFile() 29 index1 := f.NewSheet(sheetName1) 30 f.SetActiveSheet(index1) 31 32 sheet1 := f.GetSheetName(0) 33 f.DeleteSheet(sheet1) 34 35 var infos1 []model.TableInfo 36 db.Raw("desc " + model.DataCompany{}.TableName()).Scan(&infos1) 37 38 excelColNameArr1, excelColNameHeader1 := comm.GetExcelColsByTableDef(infos1) 39 fieldNames1 := comm.GetStructFields(model.DataCompany{}) 40 41 // gen headers 42 for index, name := range excelColNameHeader1 { 43 excelColName := excelColNameArr1[index] 44 excelColId := fmt.Sprintf("%s%d", excelColName, 1) 45 46 f.SetCellValue(sheetName1, excelColId, name) 47 } 48 49 // gen rows 50 for rowIndex, po := range pos1 { 51 for fieldIndex, fieldName := range fieldNames1 { 52 val := "" 53 54 if fieldName == "Id" { 55 val = fmt.Sprintf("%d", reflect.ValueOf(po).FieldByName(fieldName).Uint()) 56 } else { 57 val = reflect.ValueOf(po).FieldByName(fieldName).String() 58 } 59 60 excelColName := excelColNameArr1[fieldIndex] 61 excelColId := fmt.Sprintf("%s%d", excelColName, rowIndex+2) 62 63 f.SetCellValue(sheetName1, excelColId, val) 64 } 65 } 66 67 sheetName2 := "company_abbreviation" 68 pos := make([]model.DataCompanyAbbreviation, 0) 69 db.Order("id ASC").Find(&pos) 70 71 index2 := f.NewSheet(sheetName2) 72 f.SetActiveSheet(index2) 73 74 var infos []model.TableInfo 75 db.Raw("desc " + model.DataCompanyAbbreviation{}.TableName()).Scan(&infos) 76 77 excelColNameArr2, excelColNameHeader2 := comm.GetExcelColsByTableDef(infos) 78 fieldNames2 := comm.GetStructFields(model.DataCompanyAbbreviation{}) 79 80 // gen headers 81 for index, name := range excelColNameHeader2 { 82 excelColName := excelColNameArr2[index] 83 excelColId := fmt.Sprintf("%s%d", excelColName, 1) 84 85 f.SetCellValue(sheetName2, excelColId, name) 86 } 87 88 // gen rows 89 for rowIndex, po := range pos { 90 for fieldIndex, fieldName := range fieldNames2 { 91 val := "" 92 93 if fieldName == "Id" { 94 val = fmt.Sprintf("%d", reflect.ValueOf(po).FieldByName(fieldName).Uint()) 95 } else { 96 val = reflect.ValueOf(po).FieldByName(fieldName).String() 97 } 98 99 excelColName := excelColNameArr2[fieldIndex] 100 excelColId := fmt.Sprintf("%s%d", excelColName, rowIndex+2) 101 102 f.SetCellValue(sheetName2, excelColId, val) 103 } 104 } 105 106 f.SetActiveSheet(index1) 107 f.SaveAs(filePath) 108 }