Learn the go language from a small case-distribute all parts of Excel to different e-mail addresses

This is a creation in Article, where the information may have evolved or changed.

Key points:

• Go language Read Excel
• Go-language regular expressions
• Go language Send Email

Case scenario

Today, the company's administration of the small sister ran to ask, what can be the payroll automatically sent to each employee's business mailbox? Each employee's salary bar in the form of Excel in the same document, before the use of OA sent, copy and paste, the operation is quite simple, but the company asked to use e-mail to send payroll, to the administration of the Department of colleagues to increase the workload, and every month need to do, it is a waste of time, So readily agreed to help solve.

Situation Grooming

The company's payroll is probably like this.

For convenience, the administration will arrange the payroll for everyone in the same sheet in the same Excel file. The whole picture will look something like this.

Paste_image.png

The sister of the Administration department wants to be able to automatically send each person's own salary bar to the respective mailbox, so at least there must be a place to fill in the mailbox number, so I added a line on everyone's pay bar, Mark everyone's mailbox, so the document became such

Paste_image.png

Well, the Excel format is determined, and for programmers, he's just a two-dimensional array. Next, start writing code.

Read Excel with Go

The go language itself has a CSV library, but in this scenario, it is more appropriate to use the GITHUB.COM/TEALEG/XLSX library to process xlsx files.
Create a go file, put the payroll and go files in the same directory, this article assumes that the Payroll table Excel named list.xlsx

package mainimport (    "bufio"    "fmt"    "github.com/tealeg/xlsx"    "log")func main() {    excelFileName := "./list.xlsx"    xlFile, err := xlsx.OpenFile(excelFileName)    if err != nil {        log.Fatalln("err:", err.Error())    }}

Xlsx opens Excel file successfully and returns an xlsx. The file object, in addition to some basic methods of working with files, contains a sheets object, which is a sheet map collection in an Excel file that can be traversed to obtain all sheet.
The sheet contains an object named rows, which is a collection of all the rows in the sheet.
Rows contains an object called cells, which is a collection of all the squares in the row.

So, an xlsx. A file object is equivalent to a three-dimensional array, regardless of the method it contains.

We only need to do three nested loops to get all of the cell data, like this

func main() {    excelFileName := "./list.xlsx"    xlFile, err := xlsx.OpenFile(excelFileName)    if err != nil {        log.Fatalln("err:", err.Error())    }    for _, sheet := range xlFile.Sheets {        for _, row := range sheet.Rows {            for _, cell := range row.Cells {                fmt.Printf("%s\n", cell.Value)            }        }    }}

Next, to enter the key point, the data read out, to separate no personal pay bar, from the previous picture can be seen, when the table appears once the e-mail content of the cell, is a new person's pay bar. Therefore, it is necessary to use regular expressions to determine whether a cell is read to an e-mail message, and if it is read, the contents of the payroll bar will be saved with the new storage space.

Use regular expressions to find cells that contain email addresses

Regular expression judgment is simple, create a function, read the entire row of data, if there is an e-mail message, return the true, and the e-mail string (this place can not wear anti-isemail This parameter, just to determine whether the email is 0 value is OK)

func isEmailRow(r []string) (isEmail bool, email string) {    reg := regexp.MustCompile(`^[a-zA-Z_0-9.-]{1,64}@([a-zA-Z0-9-]{1,200}.){1,5}[a-zA-Z]{1,6}$`)    for _, v := range r {        if reg.MatchString(v) {            return true, v        }    }    return false, ""}

For ease of operation, I used the getcellvalues function to read the cells of the row directly into a string array and filter the spaces and line breaks.

func getCellValues(r *xlsx.Row) (cells []string) {    for _, cell := range r.Cells {        txt := strings.Replace(strings.Replace(cell.Value, "\n", "", -1), " ", "", -1)        cells = append(cells, txt)    }    return}

I used a map to unify the payroll data for different people, use e-mail as a key value, and then assemble the data into an HTML table row code (because it would be possible to send an HTML-formatted e-mail message in a tabular format). So, the loop code in main becomes the same

for _, sheet := range xlFile.Sheets {        curMail := ""        for _, row := range sheet.Rows {            cells := getCellValues(row)            //如果行包含电子邮件，创建一个新字典项            if isEmail, emailStr := isEmailRow(cells); isEmail {                curMail = emailStr            }             sendList[curMail] += fmt.Sprintf("<tr><td>%s</td></tr>", strings.Join(cells, "</td><td>"))        }    }

Send email (SMTP) in the Go language

The go language is simple to send e-mails, and NET/SMTP with standard packages is enough.

First, encapsulate a function that sends a message, and use the official example to transform it.

func sendToMail(user, password, host, to, subject, body, mailtype string) error {    auth := smtp.PlainAuth("", user, password, strings.Split(host, ":")[0])    msg := []byte("To: " + to + "\r\nFrom: " + user + "\r\nSubject: " + subject + "\r\n" + "Content-Type: text/" + mailtype + "; charset=UTF-8" + "\r\n\r\n" + body)    sendto := strings.Split(to, ";")    err := smtp.SendMail(host, auth, user, sendto, msg)    return err}

Then create a function, traverse all the contents and call the Send mail function to send out

func sendMail(sendList map[string]string) {    fmt.Printf("共需要发送%d封邮件\n", len(sendList))    index := 1    for mail, content := range sendList {        fmt.Printf("发送第%d封", index)        if err := sendToMail("xxx@mybigcompany.com",            "thisismypassword",            "smtp.mybigcompany.com:25",            mail,            "工资条",            fmt.Sprintf("<table border='2'>%s</table>", content),            "html"); err != nil {            fmt.Printf(" ... 发送错误(X) %s %s \n", mail, err.Error())        } else {            fmt.Printf(" ... 发送成功(V) %s \n", mail)        }        index++        fmt.Printf("<table border='2'>%s</table> \n", content)    }}

Finally, the sendmail is placed in the main function, and the for iteration reads out all the data and it is finished.
Administrative colleagues use Windows, the use of terminal programs tend to make them confused, do not know what happened, but I can not spend too much time to develop the interface for such a small program, so even in the terminal to run, and try to provide a friendly user experience, The key information in the code tries to output friendly hints as much as possible. After the program is finished, do a terminal input wait to let the user see the results of the run.

    fmt.Print("按下回车结束")    bufio.NewReader(os.Stdin).ReadLine()

Full code

Package Mainimport ("Bufio" "FMT" "NET/SMTP" "OS" "RegExp" "Strings" "Log" "Github.com/tealeg/xls X ") func main () {excelfilename: ="./list.xlsx "Xlfile, err: = xlsx. OpenFile (excelfilename) if err! = Nil {log. Fatalln ("ERR:", err.)        Error ())} Sendlist: = Make (Map[string]string) for _, Sheet: = Range Xlfile.sheets {curmail: = "" For _, Row: = Range sheet. rows {cells: = Getcellvalues (row)//If the line contains an e-mail message, create a new dictionary entry if isemail, emailstr: = Isemailro W (cells);  isemail {curmail = emailstr} else {count: = 0 for _, c: = Range                Cells {if Len (c) > 0 {count++}} If Count > 1 {sendlist[curmail] + + FMT. Sprintf ("<tr><td>%s</td></tr>", strings.          Join (Cells, "</td><td>")} else {          Sendlist[curmail] + = FMT. Sprintf ("<tr><td colspan= '%d ' >%s</td></tr>", Len (cells), strings. Join (Cells, "")}}}}} sendMail (Sendlist) fmt. Print ("Press ENTER to end") Bufio. Newreader (OS. Stdin). ReadLine ()}func getcellvalues (R *xlsx. Row) (cells []string) {for _, Cell: = Range R.cells {txt: = strings. Replace (Strings. Replace (cell. Value, "\ n", "",-1), "", "",-1) cells = append (cells, txt)} return}func isemailrow (R []string) (Isemail b ool, email string) {reg: = RegExp. Mustcompile (' ^[a-za-z_0-9.-]{1,64}@ ([a-za-z0-9-]{1,200}.) {1,5} [A-za-z] {1,6}$ ') for _, V: = range R {if Reg.    Matchstring (v) {return true, V}} return False, ""}func SendMail (sendlist map[string]string) { Fmt. Printf ("Need to send%d messages \ n", Len (sendlist)) Index: = 1 for mail, content: = Range Sendlist {fmt.         Printf ("Send%d", index) if err: = Sendtomail ("xxx@mybigcompany.com",   "Thesismypassword", "smtp.mybigcompany.com:25", mail, "wage bar", FMT. Sprintf ("<table border= ' 2 ' >%s</table>", content), "html"); Err! = Nil {fmt. Printf ("... Send error (X)%s%s \ n ", mail, Err. Error ())} else {fmt. Printf ("... Send success (V)%s \ n ", Mail)} index++//fmt. Printf ("<table border= ' 2 ' >%s</table> \ n", content)}}func sendtomail (user, password, host, to, subject, BOD Y, Mailtype String) error {auth: = SMTP. Plainauth ("", User, password, strings. Split (Host, ":") [0]) msg: = []byte ("to:" + to + "\r\nfrom:" + user + "\r\nsubject:" + subject + "\ r \ n" + "Content-ty pe:text/"+ Mailtype +"; Charset=utf-8 "+" \r\n\r\n "+ Body" sendto: = Strings.    Split (To, ";") ERR: = SMTP. SendMail (host, auth, user, sendto, msg) return err}

Go language cross-compiling, running on different operating systems

I use the Mac 64 bit, need to compile a Windows 32-bit executable program, a sentence to fix

CGO_ENABLED=0 GOOS=windows GOARCH=386 go build

GOOS set the target system, which can be windows, Linux,darwin
Goarch set whether the target system is 32-bit or 64-bit, corresponding to 386 and AMD64 respectively
Cgo_enabled set Whether you need to use CGO, this example does not need to be set to 0, if you need to use CGO compilation, set to 1

OK, the task is completed, just edit a document such as the third picture in the format of the documents, save as LIST.XLSX, and the compiled executable file in the same directory, double-click on the execution, the contents of the document will be sent to the e-mail cell as a segmentation point, respectively, in the email address.

Summary of Knowledge points

• To read an xlsx file using the GITHUB.COM/TEALEG/XLSX package
• Using the RegExp package to make regular expression judgments
• Send e-mail using the NET/SMTP package
• Using cross-compilation commands to generate executables on different systems