go-struct-excel

安装

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
}

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

// 定义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;
}

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
}

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