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  }