go语言struct结构体支持导入导出excel

原创:golang06/18/2022发布pv:0uv:0ip:0twitter #golang

原文地址:https://www.douyacun.com/article/2b75b4c22b1d264d2ba961df6942b309

go-struct-excel

  1. struct支持导出为excel
  2. excel导入为struct
  3. 表头支持扩展,如:日期表头不确定长度
  4. excel第一行为备注
  5. excel表头进行汇总
  6. 标记某行为特殊颜色
  7. 如果字段为空,就不生成该表头
  8. 支持http响应
  9. 支持grpc响应

表头不支持重复

实际效果:

安装

go get github.com/douyacun/go-struct-excel

导出 excel

package main

type foo struct {
	Name    string          `excel:"姓名" json:"name"`
	Age     *int            `excel:"年龄,allowempty" json:"age"`
	Height  int             `excel:"身高,font{color:ff0000 size:16}" json:"height"`
	Holiday map[string]bool `excel:"假期,expand:regexp(^\\d{4}-\\d{2}-\\d{2}$)" json:"holiday"`
}

func (f foo) GatherHeaderRows() int {
	return 1
}

func (f foo) GatherHeader(sheet *Sheet) error {
	style, _ := sheet.GetCenterStyle()

	headerLine := "7"

	sheet.Excel.SetCellValue(sheet.SheetName, "A"+headerLine, "个人信息")
	sheet.Excel.MergeCell(sheet.SheetName, "A"+headerLine, "C"+headerLine)
	sheet.Excel.SetCellStyle(sheet.SheetName, "A"+headerLine, "C"+headerLine, style)
	sheet.Excel.SetCellValue(sheet.SheetName, "D"+headerLine, "假期信息")
	sheet.Excel.MergeCell(sheet.SheetName, "D"+headerLine, "I"+headerLine)
	return nil
}

func (f foo) Remarks() (string, int, int) {
	return `.特别注意:导入数值加逗号格式,很经常被Excel带成数值,可在前面加个'号,或设置单元格格式为文本			
			
.导入规则:
.全局名称不允许重复			
.各种包含类型枚举:可为空表示不定向,或输入:不限、包含、不包含			
`, 6, 9
}

测试:

package main

func TestNewExcel(t *testing.T) {
  excel := NewExcel("helloworld.xlsx")
  defer excel.File.Close()
  data := make([]*foo, 0)
  age := 28
  data = append(data, &foo{
    Name:   "h",
    Age:    &age,
    Height: 181,
    Holiday: map[string]bool{
      "2022-01-27": false,
      "2022-01-28": true,
      "2022-01-29": true,
    },
  }, &foo{
    Name:   "o",
    Age:    &age,
    Height: 182,
    Holiday: map[string]bool{
      "2022-01-27": true,
      "2022-01-28": true,
      "2022-01-30": true,
      "2022-02-09": true,
      "2022-12-09": true,
    },
  })

  if err := excel.AddSheet("hello").AddData(data); err != nil {
    t.Error(err)
    return
  }

  if err := excel.SaveAs(); err != nil {
    t.Errorf("文件保存失败: %s", err.Error())
    return
  }
  dir, _ := os.Getwd()
  fmt.Println("当前路径:", dir)
  return
}

http访问直接下载excel:

if err := excel.Response(ctx.Writer); err != nil {
  ctx.JSON(http.StatusOK, gin.H{"code": http.StatusBadRequest, "message": err.Error()})
}

grpc响应:

// 定义protobuf
syntax = "proto3";
option go_package = "douyacun/proto/common;common";

package common;

message ExcelFile {
  string filename = 1;
  bytes raw = 2;
}

message ExcelResponse {
  int32 code = 1;
  string error_message = 2;
  ExcelFile data = 3;
}

如果是http代理grpc服务,可以通过类型断言:ExcelResponse

package main

import "context"

func handler(ctx context.Context) error {
  switch resp.(type) {
  case *commonProto.ExcelResponse:
    return response(ctx.writer, resp.(*commonProto.ExcelResponse))
  default:
    return defaultHandle(ctx, req, resp)
  }
}

func response(w http.ResponseWriter) error {
  header := w.Header()

  byt, err := e.Bytes()
  if err != nil {
    return err
  }
  header["Accept-Length"] = []string{strconv.Itoa(len(byt))}
  header["Content-Type"] = []string{"application/vnd.ms-excel"}
  header["Access-Control-Expose-Headers"] = []string{"Content-Disposition"}
  header["Content-Disposition"] = []string{fmt.Sprintf("attachment; filename=\"%s\"", e.Filename)}
  w.Write(byt)
  return nil
}

excel tag说明:

  • tag英文逗号分隔,第一个作为表头名称,其他没有顺序要求
  • allowempty: 表头在场景一中需要展示,其他不需要。字段为指针类型,tag标记为 allowmepty
  • font: 设定此列富文本样式,支持 {color:ff0000 size:16 italic:true blod:true underline:true}
  • expand: 自动扩展表头,支持正则匹配表头,expand:regexp(^\\d{4}-\\d{2}-\\d{2}$)", 其中内置正则
    • expand:date: 2022-06-18
    • expand:datetime: 2022-06-18 08:27:39
    • expand:month: 2022-06

表头备注:

type ExcelRemarks interface {
Remarks() (remark string, row, col int) // 占用几行几列
}

汇总表头

package main

type ExcelGatherHeader interface {
    GatherHeaderRows() int // 汇总表头占几行,不包括字段行
    GatherHeader(sheet *Sheet) error // 汇总表头合并单元格,单元格样式需要自己实现
}

导入用法

package main

func TestReadData(t *testing.T) {
  excel, err := OpenExcel("helloword.xlsx")
  if err != nil {
    t.Error(err)
  }
  sheet, err := excel.OpenSheet("hello")
  if err != nil {
    t.Error(err)
  }
  if data, err := sheet.ReadData(foo{}); err != nil {
    t.Error(err)
  } else if d, ok := data.([]*foo); ok {
    if str, err := json.Marshal(d); err != nil {
      t.Error(err)
    } else {
      fmt.Println(string(str))
    }
  }
}

输出:

=== RUN   TestReadData
[{"name":"h","age":28,"height":181,"holiday":{"2022-01-27":false,"2022-01-28":true,"2022-01-29":true}},{"name":"o","age":28,"height":182,"holiday":{"2022-01-27":true,"2022-01-28":true,"2022-01-29":false,"2022-01-30":true,"2022-02-09":true,"2022-12-09":true}}]
--- PASS: TestReadData (0.00s)
PASS