介绍

xlsx是golang的第三方库，支持的文件格式是 .xlsx

安装

go get github.com/tealeg/xlsx

导入

excel 文件：

import.png

导入导出代码:

package main

import (
    "encoding/json"
    "fmt"
    "github.com/tealeg/xlsx"
    "log"
    "strconv"
)

var inFile = "E:/workspace/src/xlsx_test/test.xlsx"
var outFile = "E:/workspace/src/xlsx_test/testOut.xlsx"

type Student struct {
    Name   string
    Age    int
    Phone  string
    Gender string
    Mail   string
}

//导入
func Import() {
    //打开文件
    xlsxFile, err := xlsx.OpenFile(inFile)
    if err != nil {
        log.Println(err)
        return
    }
    students := make([]*Student, 0, 5)

    //遍历sheet页
    for _, sheet := range xlsxFile.Sheets {
        fmt.Println("sheet name:", sheet.Name)
        //遍历行读取
        for i, row := range sheet.Rows {
            if i > 0 {
                student := new(Student)
                //遍历每行的列读取
                for k, cell := range row.Cells {
                    //fmt.Println(k,cell.Value)
                    switch k {
                    case 0:
                        student.Name = cell.Value
                    case 1:
                        age, err := strconv.Atoi(cell.Value)
                        if err != nil {
                            log.Println(err)
                            return
                        }
                        student.Age = age
                    case 2:
                        student.Phone = cell.Value
                    case 3:
                        student.Gender = cell.Value
                    case 4:
                        student.Mail = cell.Value
                    default:
                        return
                    }
                }
                students = append(students, student)
            }
        }
    }
    resp, err := json.Marshal(students)
    if err != nil {
        log.Println(err)
        return
    }
    fmt.Println(string(resp))
}

//导出
func Export() {
    file := xlsx.NewFile()
    sheet, err := file.AddSheet("student_list")
    if err != nil {
        log.Println(err)
        return
    }
    stus := make([]Student, 0, 5)
    stus = []Student{
        {
            Name:   "刘备",
            Age:    20,
            Phone:  "54685468546",
            Gender: "男",
            Mail:   "liubei@163.com",
        },
        {
            Name:   "关羽",
            Age:    20,
            Phone:  "54315431543",
            Gender: "男",
            Mail:   "guanyu@sina.com",
        },
        {
            Name:   "张飞",
            Age:    20,
            Phone:  "54335433543",
            Gender: "女",
            Mail:   "zhangfei@qq.com",
        },
    }
    //加列字段名
    WriteTatel(sheet)
    //添加数据
    for _, stu := range stus {
        row := sheet.AddRow()

        row.AddCell().Value = stu.Name
        row.AddCell().Value = strconv.Itoa(stu.Age)
        row.AddCell().Value = stu.Phone
        row.AddCell().Value = stu.Gender
        row.AddCell().Value = stu.Mail
    }
    err = file.Save(outFile)
    if err != nil {
        log.Println(err)
        return
    }
    fmt.Println("\n\nexport success")

}

func WriteTatel(sheet *xlsx.Sheet) {
    row := sheet.AddRow()

    row.AddCell().Value = "Name"
    row.AddCell().Value = "Age"
    row.AddCell().Value = "Phone"
    row.AddCell().Value = "Gender"
    row.AddCell().Value = "Mail"
}

func main() {
    Import()
    Export()
}

结果:

sheet name: Sheet1
sheet name: Sheet2
sheet name: Sheet3
[{"Name":"大香蕉","Age":20,"Phone":"13800138000","Gender":"男","Mail":"bigBanana@126.com"},{"Name":"小葡萄","Age":18,"Phone":"13800138001","Gender":"女","Mail":"littleGrapelet@163.com"}]


export success

Process finished with exit code 0

生成的e'xcel文件:

export.png