Go xlsx存储解决方案

邮箱号/github/QQ  645171033@qq.com

如有更好的改进方案，请QQ联系我

---

 上源码

/*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *    
*
*   @Title:
*       Save data to xlsx
*
*   @Description:
*		AddDataToNewSheet  			不支持嵌套结构体，仅支持平铺结构体
*		AddColumnTitle				只支持json tag
*		TransferInterfaceToString 	目前只能支持基础类型
*	
*   @Remarks:
*		
*
*   @Functions:
*      
*
*   @Author: 
*       XiangChen
*
*   @Date:
*       2021/10/19 
*
*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  */
import (
	"errors"
	"github.com/tealeg/xlsx"
	"log"
	"os"
	"reflect"
	"strconv"
)

// SaveXlsx 存储xlsx
func SaveXlsx(xlsxFile *xlsx.File, path string) error {
	err := DeleteFileIfExist(path)
	if err != nil {
		return err
	}
	err = xlsxFile.Save(path)
	if err != nil {
		return err
	}
	return nil
}

// AddDataToNewSheet 添加数据到新的sheet中
func AddDataToNewSheet(xlsxFile *xlsx.File, sheetName string, dataList []interface{}, fun func(data *interface{})) error {
	println("+++++++++ Start add data to sheet+++++++++")
	if dataList == nil {
		return errors.New("DataList is empty ! ")
	}
	var sheet *xlsx.Sheet
	sheet, err := xlsxFile.AddSheet(sheetName)
	if err != nil {
		return err
	}
	if len(dataList)>1 {
		AddColumnTitle(dataList[0], sheet)
	}
	if len(dataList) == 0 {
		return nil
	}
	for i := range dataList {
		data := dataList[i]
		row := sheet.AddRow()
		if fun != nil {
			fun(&data)
		}
		TransferDataToRow(data, row)
	}
	return nil
}

// AddColumnTitle 通过tag添加首行
func AddColumnTitle(structModel interface{}, sheet *xlsx.Sheet) {
	objValue := reflect.ValueOf(structModel)
	row := sheet.AddRow()
	for i := 0; i < objValue.NumField(); i++ {
		fieldInfo := objValue.Type().Field(i)
		tag := fieldInfo.Tag
		name := tag.Get("json")
		log.Println(name)
		cell := row.AddCell()
		cell.SetString(name)
	}
}

// TransferDataToRow 平铺结构体
func TransferDataToRow(data interface{}, row *xlsx.Row) {
	objValue := reflect.ValueOf(data)
	objType := reflect.TypeOf(data)
	for i := 0; i < objValue.NumField(); i++ {
		cell := row.AddCell()
		str := TransferInterfaceToString(objType.Field(i), objValue.Field(i).Interface())
		cell.SetString(str)
	}
}

// PathExists 文件是否存在
func PathExists(path string) (bool, error) {
	_, err := os.Stat(path)
	if err == nil {
		return true, nil
	}
	if os.IsNotExist(err) {
		return false, nil
	}
	return false, err
}

// DeleteFileIfExist 删除文件
func DeleteFileIfExist(path string) error {
	flag, err := PathExists(path)
	if err != nil {
		return err
	}
	if flag {
		err := os.Remove(path)
		if err != nil {
			return err
		}
	}
	return nil
}

// TransferInterfaceToString 转换其他类型到String类型
func TransferInterfaceToString(structField reflect.StructField, data interface{}) string {
	var (
		float32Ptr *float32
		float64Ptr *float64
		intPtr     *int
		int8Ptr    *int8
		int16Ptr   *int16
		int32Ptr   *int32
		int64Ptr   *int64
		stringPtr  *string
	)

	switch structField.Type {

	case reflect.TypeOf(float64Ptr):
		d := data.(*float64)
		if d == nil {
			return ""
		} else {
			return strconv.FormatFloat(*d, 'f', 6, 64)
		}
	case reflect.TypeOf(float32Ptr):
		d := data.(*float32)
		if d == nil {
			return ""
		} else {
			return strconv.FormatFloat(float64(*d), 'f', 6, 32)
		}
	case reflect.TypeOf(float32(1)):
		return strconv.FormatFloat(float64(data.(float32)), 'f', 6, 32)
	case reflect.TypeOf(float64(1)):
		return strconv.FormatFloat(data.(float64), 'f', 6, 64)
	case reflect.TypeOf(intPtr):
		d := data.(*int)
		if d == nil {
			return ""
		} else {
			return strconv.Itoa(*d)
		}
	case reflect.TypeOf(int8Ptr):
		d := data.(*int)
		if d == nil {
			return ""
		} else {
			return strconv.Itoa(*d)
		}
	case reflect.TypeOf(int16Ptr):
		d := data.(*int)
		if d == nil {
			return ""
		} else {
			return strconv.Itoa(*d)
		}
	case reflect.TypeOf(int32Ptr):
		d := data.(*int)
		if d == nil {
			return ""
		} else {
			return strconv.Itoa(*d)
		}
	case reflect.TypeOf(int64Ptr):
		d := data.(*int)
		if d == nil {
			return ""
		} else {
			return strconv.Itoa(*d)
		}
	case reflect.TypeOf(1):
		return strconv.Itoa(data.(int))
	case reflect.TypeOf(int8(1)):
		return strconv.Itoa(int(data.(int8)))
	case reflect.TypeOf(int16(1)):
		return strconv.Itoa(int(data.(int16)))
	case reflect.TypeOf(int32(1)):
		return strconv.Itoa(int(data.(int32)))
	case reflect.TypeOf(int64(1)):
		return strconv.Itoa(int(data.(int64)))
	case reflect.TypeOf(stringPtr):
		d := data.(*string)
		if d == nil {
			return ""
		} else {
			return *d
		}
	case reflect.TypeOf(""):
		return data.(string)
	default:
		return ""
	}
}

---

 

上demo

import (
	"github.com/tealeg/xlsx"
	"log"
)

type Test struct {
	A string `json:"A"`
	B string `json:"B"`
	C int64  `json:"中文"`
}

func main() {
	test:=Test{
		A: "Te Amo ",
		B: "Something for nothing",
		C: 12,
	}
	file := xlsx.NewFile()
	err := AddDataToNewSheet(file,"测试sheet",[]interface{}{test},nil)
	if err != nil {
		log.Println(err)
	}
	err = AddDataToNewSheet(file,"测试sheet2",[]interface{}{test},nil)
	if err != nil {
		log.Println(err)
	}
	err = SaveXlsx(file, "./test.xlsx")
	if err != nil {
		log.Println(err)
	}
}