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  }