golang excelize表格生成及邮件发送（一）

本文实现的功能如下：
1、接收前端传过来的json数据
2、将json数据解析到结构体
3、引入第三方工具包excelize，将结构体的数据渲染到表格内
4、引入内置包net/smtp,将excel作为附件，将邮件发送到固定收件人

下面直接上代码：

func send_email(writer http.ResponseWriter,req *http.Request){
	body, err := ioutil.ReadAll(req.Body)
	Check(err)
	var newDemo xlsxForm
	_ = json.Unmarshal(body,&newDemo)
	writer.Write([]byte("successful."))
}

func main(){
	server := http.Server{Addr:"localhost:8080",}
	http.HandleFunc("/send_email",send_email)
	server.ListenAndServe()
}

1、首先起一个服务端，解析req的body，也就是json体，然后返回给前端一个成功的标志，这里返回一个字符串“successful.”

func Check(err error){
	if err != nil{
		log.Fatal(err)
	}
}

2、因为golang大部分方法都是会返回err参数，所以先定义一个check方法，减少代码量，更优雅。

type xlsxForm struct {
	Name string `json:"name"`
	Data struct{
		Headers []string `json:"headers"`
		Content []([]string) `json:"content"`
		}
	Recipients []string `json:"recipients"`
	}

//样例json
{
    "name":"test_demo",
    "data":{
        "headers":["name","age","class"],   
        "content":[                           
            ["蒙恬","100","1610"],
            ["蒙玡","99","1519"],
            ["鲁班七号","88","1520"],
            ["鲁班大师","77","1520"],
            ["鬼剑武藏","66","1520"]
        ]
    },
    "recipients":["*********@163.com"]  
}

3、然后定义一个结构体用来接收json的值，这个根据自己传递的具体业务内容的层次关系确定。
补充：json："name",这个相当于给json的key打上标签，默认用标签的名字，若没有标签，则用结构体默认的名字

func createXlsx(n xlsxForm) (filename string,rece_list []string) {
	mappingXlsx := map[int]string{
		1:"A",
		2:"B",
		3:"C",
		4:"D",
		5:"E",
		6:"F",
		7:"G",
		8:"H",
		9:"I",
		10:"J",
		11:"K",
		12:"L",
		13:"M",
		14:"N",
		15:"O",
		16:"P",
		17:"Q",
		18:"R",
		19:"S",
		20:"T",
		21:"U",
		22:"V",
		23:"W",
		24:"X",
		25:"Y",
		26:"Z",
	}
	f := excelize.NewFile()
	indexXiShu := f.NewSheet("Sheet2")
	Email_name := n.Name
	Recipients_list := n.Recipients
	for i,value := range n.Data.Headers{
		column_num := mappingXlsx[i+1]
		_ = f.SetCellValue("Sheet2", fmt.Sprintf("%s1", column_num), value)
	}
	for index,each := range n.Data.Content{
		index := index+2
		for i,value := range each{
			column_num := mappingXlsx[i+1]
			_ = f.SetCellValue("Sheet2", fmt.Sprintf("%s%d",column_num,index), value)
		}
	}
	f.SetActiveSheet(indexXiShu)
	var file_name string
	file_name = fmt.Sprintf("%s.xlsx",Email_name)
	if err := f.SaveAs(file_name); err != nil {
		fmt.Println(err)
	}

4、这里我们要遍历结构体里的data，插入到excel种，所以要利用双遍历[：遍历row,遍历cell]的index构造出需要写入数据的单元格坐标[eg:“A1”,“B1”,“C1”,“A2”,“B2”,“C2”…]

wb, err := excelize.OpenFile(file_name)
	Check(err)
	sheetName := wb.GetSheetName(wb.GetActiveSheetIndex())
	sty_idx, err := wb.NewStyle(&excelize.Style{
		Fill: excelize.Fill{
			Type: "gradient",
			Color:   []string{"#1d2c53", "#1d2c53"},
			Shading: 1,
		}, Font: &excelize.Font{
			Bold: true,
			Size:   11,
			Family: "宋体",
			Color: "#FFFFFF",
		}, Alignment: &excelize.Alignment{
			Horizontal: "center",
			Vertical:   "center",
		}, Protection: &excelize.Protection{
			Hidden: true,
			Locked: true,
		}, NumFmt: 0,
		Lang:          "zh-cn",
		DecimalPlaces: 2,
		NegRed: true,
	})
	if err != nil {
		fmt.Println(err)
	}
	sty_idx2, err := wb.NewStyle(&excelize.Style{
		Fill: excelize.Fill{
			Type: "gradient",
			Color:   []string{"#f6f6f8", "#f6f6f8"},
			Shading: 1,
		}, Font: &excelize.Font{
			Bold: false,
			Size:   11,
			Family: "宋体",
			Color: "#000000",
		}, Alignment: &excelize.Alignment{
			Horizontal: "center",
			Vertical:   "center",
		}, Protection: &excelize.Protection{
			Hidden: true,
			Locked: true,
		}, NumFmt: 0,
		Lang:          "zh-cn",
		DecimalPlaces: 2,
		NegRed: true,
	})
	if err != nil {
		fmt.Println(err)
	}
	sty_idx3, err := wb.NewStyle(&excelize.Style{
		Fill: excelize.Fill{
			Type: "gradient",
			Color:   []string{"#edeef1", "#edeef1"},
			Shading: 1,
		}, Font: &excelize.Font{
			Bold: false,
			Size:   11,
			Family: "宋体",
			Color: "#000000",
		}, Alignment: &excelize.Alignment{
			Horizontal: "center",
			Vertical:   "center",
		}, Protection: &excelize.Protection{
			Hidden: true,
			Locked: true,
		}, NumFmt: 0,
		Lang:"zh-cn",
		DecimalPlaces: 2,
		NegRed: true,
	},)
	if err != nil {
		fmt.Println(err)
	}
	rows,_ := wb.GetRows("Sheet2")
	for index,row := range rows{
		list_con := []string{}
		for index2,_ := range row{
			fmt.Println(fmt.Sprintf("%s%d",mappingXlsx[index2+1],index+1),)
			list_con = append(list_con,fmt.Sprintf("%s%d",mappingXlsx[index2+1],index+1),)
		}
		fmt.Println("列表-->",list_con,index)
		if index == 0{
			if err := wb.SetCellStyle(sheetName, list_con[0], list_con[len(list_con)-1], sty_idx); err != nil {
				fmt.Println(err)
			}
		}else if index > 0 && index%2 == 1{
			if err := wb.SetCellStyle(sheetName, list_con[0], list_con[len(list_con)-1], sty_idx2); err != nil {
				fmt.Println(err)
			}
		}else if index > 0 && index%2 == 0{
			if err := wb.SetCellStyle(sheetName, list_con[0], list_con[len(list_con)-1], sty_idx3); err != nil {
				fmt.Println(err)
			}
		}
	}
	wb.Save()
	return file_name,Recipients_list
}

这里写了三组表格样式，样式1为深色样式，用于表头；样式2和样式3为浅色样式，交替用于表格内容【斑马线效果，让读者醒目】，然后就是字体大小和居中显示，表头加粗，样式具体设计可以查看官方文档。https://xuri.me/excelize/zh-hans/最后将生成的excel文件名和邮件接收人的切片返回。

下一篇介绍邮件发送的内容。