golang 利用反射写excel

excel操作接口封装

package excel

import (
	"context"
	"fmt"
	"reflect"
	"strings"

	"github.com/360EntSecGroup-Skylar/excelize"
)

type Field struct {
	Value interface{}
}

//excel操作接口
type IExcelHandler interface {
	// 获取处理器标识名，用途有：
	// 1.根据标识名替换某个处理器
	// 2.打印日志
	GetName() string

	//获取sheet name
	//打印日志
	GetSheetName() string

	// 获取需要写excel的数据
	GetField() *Field

	//获取源路径
	GetSourcePath() string

	//获取目标路径
	GetTargetPath() string

	//设置源路径
	SetSourcePath(path string) error

	//设置导出excel路径
	SetTargetPath(path string) error

	Write(ctx context.Context, xlsx *excelize.File, data interface{}) error

	OpenFile(ctx context.Context, path string) (*excelize.File, error)

	SaveAs(ctx context.Context, xlsx *excelize.File, path string) error
}

type WriteExcelHandle struct {
	HandlerName string //处理器标识名
	SheetName   string //excel表名
	StartIndex  int    //从第几行开始插入数据
	SourcePath  string //excel文件原始路径，比如，模板位置
	TargetPath  string //excel文件目标路径，比如，另存为路径
	IsSetBorder bool   //是否需要设置边框
	Data        *Field
}

func (w *WriteExcelHandle) GetName() string {
	if w == nil {
		return ""
	}
	return w.HandlerName
}

func (w *WriteExcelHandle) GetSheetName() string {
	if w == nil {
		return ""
	}
	return w.SheetName
}

func (w *WriteExcelHandle) GetField() *Field {
	if w == nil {
		return &Field{}
	}
	return w.Data
}

func (w *WriteExcelHandle) GetSourcePath() string {
	if w == nil {
		return ""
	}
	return w.SourcePath
}

func (w *WriteExcelHandle) GetTargetPath() string {
	if w == nil {
		return ""
	}
	return w.TargetPath
}

func (w *WriteExcelHandle) SetSourcePath(path string) error {
	if w == nil {
		return fmt.Errorf("WriteExcelHandle can not be nil")
	}
	w.SourcePath = path
	return nil
}

func (w *WriteExcelHandle) SetTargetPath(path string) error {
	if w == nil {
		return fmt.Errorf("WriteExcelHandle can not be nil")
	}
	w.TargetPath = path
	return nil
}

//打开文件
func (w *WriteExcelHandle) OpenFile(ctx context.Context, path string) (*excelize.File, error) {
	//打开excel文件
	xlsx, err := excelize.OpenFile(path)
	if err != nil {
		return nil, fmt.Errorf("open file failed：%s", err)
	}
	return xlsx, nil
}

//excel另存为
func (w *WriteExcelHandle) SaveAs(ctx context.Context, xlsx *excelize.File, path string) error {
	err := xlsx.SaveAs(path)
	if err != nil {
		return err
	}
	return nil
}

//利用反射的特点写excel,根据tag获取列名
//其中tag命名规则如下：xlsx:"列index"或者xlsx:"列index-行index"
//如果指定了行index，则写入指定的cell中,否则行index需要根据startIndex动态生成
func (w *WriteExcelHandle) Write(ctx context.Context, xlsx *excelize.File, data interface{}) error {
	typ := reflect.TypeOf(data)
	val := reflect.Indirect(reflect.ValueOf(data))
	if typ.Kind() == reflect.Slice {
		for i := 0; i < val.Len(); i++ {
			//不强转的话，直接对这个新的interface{}再做一次反射
			r := val.Index(i).Interface()
			d := reflect.TypeOf(r).Elem()
			for j := 0; j < d.NumField(); j++ {
				xlsxTag := d.Field(j).Tag.Get("xlsx")
				arrayTag := strings.Split(xlsxTag, "-")
				var column string
				var cellIndex string
				if len(arrayTag) <= 0 {
					return fmt.Errorf("invalid xlsx tag")
				}
				if len(arrayTag) >= 1 {
					column = arrayTag[0]
				}
				//拼接单元格索引
				cellIndex = fmt.Sprintf("%s%d", column, i+w.StartIndex)
				if len(arrayTag) == 2 {
					rowIndex := arrayTag[1]
					if rowIndex == "" {
						return fmt.Errorf("invalid xlsx tag")
					}
					cellIndex = fmt.Sprintf("%s%s", column, rowIndex)
				}
				// 设置内容
				switch d.Field(j).Type.String() {
				//目前只有string类型，有其他类型再进行扩展
				case "string":
					fieldVal := reflect.ValueOf(r).Elem().Field(j).String()
					xlsx.SetCellValue(w.SheetName, cellIndex, fieldVal)
				}
				//设置边框
				if w.IsSetBorder {
					SetBorder(xlsx, w.SheetName, cellIndex, cellIndex)
				}
			}
			// 测试环境编译的版本用的是go1.12.9，而复制黏贴单元格功能需要go1.15及以上
			// //复制黏贴行,最后一次不复制
			// if i < val.Len()-1 {
			// 	xlsx.DuplicateRowTo(w.SheetName, i+w.StartIndex, i+w.StartIndex+1)
			// }
		}
	}
	return nil
}

//设置边框
func SetBorder(xlsx *excelize.File, sheetName string, hcell, vcell string) error {
	style, err := xlsx.NewStyle(`{"border":[{"type":"bottom","color":"000000","style":1},{"type":"right","color":"000000","style":1}]}`)
	if err != nil {
		return err
	}
	xlsx.SetCellStyle(sheetName, hcell, vcell, style)
	return nil
}

事例：

package main

import (
	"context"
	"fmt"
	"reflect"
	"strings"
	"time"
    //需要引入封装好的excel包
)

type Record struct {
	Name string `xlsx:"A-姓名"`
	Age  int32  `xlsx:"B-年齡"`
}

func main() {
var records []*Record
	records = append(records, &Record{
		Name: "小明",
		Age:  11,
	})
	records = append(records, &Record{
		Name: "小华",
		Age:  12,
	})
//数据写Excel
	sourcePath := "excel第一次打开的路径"
	targetPath := "excel保存路径"
	excelHandlers := make([]excel.IExcelHandler, 0)
//sheet1
	var ISheet1Handler excel.IExcelHandler
	ISheet1Handler = &excel.WriteExcelHandle{
		HandlerName: "write sheet1 handler",
		SheetName:   "sheet1",
		StartIndex:  2, //这个值依赖于创建的excel模板
		Data:        &excel.Field{Value: records},
		IsSetBorder: true,
	}
	excelHandlers = append(excelHandlers, ISheet1Handler)
	//sheet2
	var ISheet2Handler excel.IExcelHandler
	ISheet2Handler = &excel.WriteExcelHandle{
		HandlerName: "write sheet2 handler",
		SheetName:   "sheet2",
		StartIndex:  2,
		TargetPath:  targetPath,
		Data:        &excel.Field{Value: sellGoodsOverviewHandle.Output},
	}
	excelHandlers = append(excelHandlers, ISheet2Handler)

	writeExcel(context.Background(), excelHandlers...)
}

func writeExcel(ctx context.Context, handler ...excel.IExcelHandler) (string, error) {
	var err error
	var path string
	xlsx := new(excelize.File)
	for index, h := range handler {
		//首先打开文件
		if index == 0 {
			xlsx, err = h.OpenFile(ctx, h.GetSourcePath())
			if err != nil {
				uclog.Error("open file error:%s", err.Error())
			}
		}
		err = h.Write(ctx, xlsx, h.GetField().Value)
		if err != nil {
			uclog.Error(h.GetName()+"error:%s", err.Error())
			return "", fmt.Errorf(h.GetName()+"error:%s", err.Error())
		}
		//数据写结束，保存excel
		if index == len(handler)-1 {
			err = h.SaveAs(ctx, xlsx, h.GetTargetPath())
			if err != nil {
				uclog.Error("save file error:%s", err.Error())
			}
			path = h.GetTargetPath()
		}
		uclog.Info("%s to %s sucess", h.GetName(), h.GetSheetName())
	}
	return path, nil
}