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

上文写到了处理表格的函数返回了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)
	//here-->
	fileName,rece_list := createXlsx(newDemo)
	err2 := send_file(fileName,rece_list)
	Check(err2)
	writer.Write([]byte("successful."))
}

然后开始写发送邮件的模块：

const (
    emlUser = "*********@qq.com"
    emlPwd  = "*********"
    emlSMTP = "smtp.qq.com:666"
)

首先定义三个常量：用户名，密码，服务端地址
注意：这里的密码不是明文密码，需要开启smtp服务，获取授权码。

func send_file(filename string,rece_list []string) error{
	sendTo := rece_list
	mime := bytes.NewBuffer(nil)
	attaFile := fmt.Sprintf("C://Users/litao/Desktop/work_time_go/%s",filename)
	attaFileName := fmt.Sprintf("%s",filename)

	mime.Write([]byte("Content-Type: application/octet-stream\r\n"))
	mime.Write([]byte("Content-Transfer-Encoding: base64\r\n"))
	mime.Write([]byte("Content-Disposition: attachment; filename=\"" + attaFileName + "\"\r\n\r\n"))
	//读取并编码文件内容
	attaData, err := ioutil.ReadFile(attaFile)
	if err != nil {
		return err
	}
	b := make([]byte, base64.StdEncoding.EncodedLen(len(attaData)))
	base64.StdEncoding.Encode(b, attaData)
	mime.Write(b)
	smtpHost, _, err := net.SplitHostPort(emlSMTP)
	if err != nil {
		return err
	}
	auth := smtp.PlainAuth("", emlUser, emlPwd, smtpHost)
	fmt.Println("successful.")
	return smtp.SendMail(emlSMTP, auth, emlUser, sendTo, mime.Bytes())
}

定义一个send_file函数，入参为文件名称，字符串类型；收件人，字符串切片类型。反参为error类型。
然后在函数内部定义一个空的二进制缓冲区buffer，将邮件内容以二进制的方式或者字符串的方式写入缓冲区。

smtp.SendMail方法有五个参数，分别为：
1、发送邮件服务器地址
2、登录账号密码的auth
3、邮箱账号
4、收件人切片
5、二进制邮件正文
具体smtp API可以查看官方文档：https://pkg.go.dev/net/smtp

完整代码整理如下：

package main

import (
	"bytes"
	"encoding/base64"
	"github.com/xuri/excelize"
	"encoding/json"
	"fmt"
	"io/ioutil"
	"net"
	"net/smtp"
	"net/http"
	"log"
)

const (
    emlUser = "*********@qq.com"
    emlPwd  = "*********"
    emlSMTP = "smtp.qq.com:666"
)

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

func send_file(filename string,rece_list []string) error{
	sendTo := rece_list
	mime := bytes.NewBuffer(nil)
	attaFile := fmt.Sprintf("C://Users/litao/Desktop/work_time_go/%s",filename)
	attaFileName := fmt.Sprintf("%s",filename)

	mime.Write([]byte("Content-Type: application/octet-stream\r\n"))
	mime.Write([]byte("Content-Transfer-Encoding: base64\r\n"))
	mime.Write([]byte("Content-Disposition: attachment; filename=\"" + attaFileName + "\"\r\n\r\n"))
	attaData, err := ioutil.ReadFile(attaFile)
	if err != nil {
		return err
	}
	b := make([]byte, base64.StdEncoding.EncodedLen(len(attaData)))
	base64.StdEncoding.Encode(b, attaData)
	mime.Write(b)
	smtpHost, _, err := net.SplitHostPort(emlSMTP)
	if err != nil {
		return err
	}
	auth := smtp.PlainAuth("", emlUser, emlPwd, smtpHost)
	fmt.Println("successful.")
	return smtp.SendMail(emlSMTP, auth, emlUser, sendTo, mime.Bytes())
}

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

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)
	}
	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
}

func send_email(writer http.ResponseWriter,req *http.Request){
	body, err := ioutil.ReadAll(req.Body)
	Check(err)
	var newDemo xlsxForm
	_ = json.Unmarshal(body,&newDemo)
	fileName,rece_list := createXlsx(newDemo)
	err2 := send_file(fileName,rece_list)
	Check(err2)
	writer.Write([]byte("successful."))
}
func main(){
	server := http.Server{Addr:"localhost:8080",}
	http.HandleFunc("/send_email",send_email)
	server.ListenAndServe()
}

EOF！欢迎点赞！