Golang Gin Practice Serial 14 Realize Export and Import into Excel

  excel, golang, php

Golang Gin Practice Serial 14 Realize Export and Import into Excel

Original address:Gin Practice Serial 14 Realize Export and Import into Excel
Project address:https://github.com/EDDYCJY/go …

If it is helpful to you, welcome to Star.

Preface

In this section, we will realize the export and import of label information, which is standard. I hope you can master the basic usage.

In addition, we used 2 Excel packages in this article. Some structures of the original XML format file of excelize evolved from the tealeg/xlsx format file structure, so we have specially shown them here. You can use them according to your own scenes and preferences.

Configuration

First, specify the storage path of the exported Excel file, and add a configuration to app.ini:

[app]
...

ExportSavePath = export/

Modify App struct for setting.go:

type App struct {
    JwtSecret       string
    PageSize        int
    PrefixUrl string

    RuntimeRootPath string

    ImageSavePath  string
    ImageMaxSize   int
    ImageAllowExts []string

    ExportSavePath string

    LogSavePath string
    LogSaveName string
    LogFileExt  string
    TimeFormat  string
}

The ExportSavePath configuration item needs to be added here, and the previous ImagePrefixUrl is changed to PrefixUrl to support the HOST acquisition of both.

(note to modify the GetImageFullUrl method of image.go)

pkg

Create a new pkg/export/excel.go file as follows:

package export

import "github.com/EDDYCJY/go-gin-example/pkg/setting"

func GetExcelFullUrl(name string) string {
    return setting.AppSetting.PrefixUrl + "/" + GetExcelPath() + name
}

func GetExcelPath() string {
    return setting.AppSetting.ExportSavePath
}

func GetExcelFullPath() string {
    return setting.AppSetting.RuntimeRootPath + GetExcelPath()
}

Some commonly used methods have been written here. If there is any change in the value taking method in the future, it is only necessary to directly change the internal code, which is not visible to the outside world.

Try the standard library

f, err := os.Create(export.GetExcelFullPath() + "test.csv")
if err != nil {
    panic(err)
}
defer f.Close()

f.WriteString("\xEF\xBB\xBF")

w := csv.NewWriter(f)
data := [][]string{
    {"1", "test1", "test1-1"},
    {"2", "test2", "test2-1"},
    {"3", "test3", "test3-1"},
}

w.WriteAll(data)

In the standard library encoding/csv provided by Go, the reading and processing of csv files are naturally supported. In this code, the following work has been done:

1、os.Create:

Csv file was created

2、f.WriteString(“xEFxBBxBF”):

\xEF\xBB\xBFIt is the hexadecimal format of UTF-8 BOM. Its use here is to identify the encoding format of the file, which usually appears at the beginning of the file, so it must be written in the first step. If the encoding format of UTF-8 is not identified, the written Chinese characters will be displayed as garbled codes.

3、csv.NewWriter:

func NewWriter(w io.Writer) *Writer {
    return &Writer{
        Comma: ',',
        w:     bufio.NewWriter(w),
    }
}

4、w.WriteAll:

func (w *Writer) WriteAll(records [][]string) error {
    for _, record := range records {
        err := w.Write(record)
        if err != nil {
            return err
        }
    }
    return w.w.Flush()
}

Writeball is actually an encapsulation of Writeball, Write should be noted at the end of the call.w.w.Flush()This fully illustrates the usage scenario of WriteAll. You can think about the author’s design intention.

Export

Service method

Open the service/tag.go and add the Export method as follows:

func (t *Tag) Export() (string, error) {
    tags, err := t.GetAll()
    if err != nil {
        return "", err
    }

    file := xlsx.NewFile()
    sheet, err := file.AddSheet("标签信息")
    if err != nil {
        return "", err
    }

    titles := []string{"ID", "名称", "创建人", "创建时间", "修改人", "修改时间"}
    row := sheet.AddRow()

    var cell *xlsx.Cell
    for _, title := range titles {
        cell = row.AddCell()
        cell.Value = title
    }

    for _, v := range tags {
        values := []string{
            strconv.Itoa(v.ID),
            v.Name,
            v.CreatedBy,
            strconv.Itoa(v.CreatedOn),
            v.ModifiedBy,
            strconv.Itoa(v.ModifiedOn),
        }

        row = sheet.AddRow()
        for _, value := range values {
            cell = row.AddCell()
            cell.Value = value
        }
    }

    time := strconv.Itoa(int(time.Now().Unix()))
    filename := "tags-" + time + ".xlsx"

    fullPath := export.GetExcelFullPath() + filename
    err = file.Save(fullPath)
    if err != nil {
        return "", err
    }

    return filename, nil
}

Routers entrance

Open routers/api/v1/tag.go and add the following method:

func ExportTag(c *gin.Context) {
    appG := app.Gin{C: c}
    name := c.PostForm("name")
    state := -1
    if arg := c.PostForm("state"); arg != "" {
        state = com.StrTo(arg).MustInt()
    }

    tagService := tag_service.Tag{
        Name:  name,
        State: state,
    }

    filename, err := tagService.Export()
    if err != nil {
        appG.Response(http.StatusOK, e.ERROR_EXPORT_TAG_FAIL, nil)
        return
    }

    appG.Response(http.StatusOK, e.SUCCESS, map[string]string{
        "export_url":      export.GetExcelFullUrl(filename),
        "export_save_url": export.GetExcelPath() + filename,
    })
}

Routing

Add a routing method to the routers/router.go file as follows

apiv1 := r.Group("/api/v1")
apiv1.Use(jwt.JWT())
{
    ...
    //导出标签
    r.POST("/tags/export", v1.ExportTag)
}

Verification interface

Accesshttp://127.0.0.1:8000/tags/exportThe results are as follows:

{
    "code": 200,
    "data": {
        "export_save_url": "export/tags-1528903393.xlsx",
        "export_url": "http://127.0.0.1:8000/export/tags-1528903393.xlsx"
    },
    "msg": "ok"
}

Finally, the address and save address of the exported file are returned through the interface.

StaticFS

If you think about it, it is definitely impossible to download files by directly accessing the address now, then what should you do?

Open the router.go file and add the following code:

r.StaticFS("/export", http.Dir(export.GetExcelFullPath()))

If you don’t understand, it is strongly recommended that you review the previous chapters and make an analogy

Verify download

Visit the export_url above again, for example:http://127.0.0.1:8000/export/tags-1528903393.xlsxIs it a success?

Import

Service method

Open the service/tag.go and add the Import method as follows:

func (t *Tag) Import(r io.Reader) error {
    xlsx, err := excelize.OpenReader(r)
    if err != nil {
        return err
    }

    rows := xlsx.GetRows("标签信息")
    for irow, row := range rows {
        if irow > 0 {
            var data []string
            for _, cell := range row {
                data = append(data, cell)
            }

            models.AddTag(data[1], 1, data[2])
        }
    }

    return nil
}

Routers entrance

Open routers/api/v1/tag.go and add the following method:

func ImportTag(c *gin.Context) {
    appG := app.Gin{C: c}

    file, _, err := c.Request.FormFile("file")
    if err != nil {
        logging.Warn(err)
        appG.Response(http.StatusOK, e.ERROR, nil)
        return
    }

    tagService := tag_service.Tag{}
    err = tagService.Import(file)
    if err != nil {
        logging.Warn(err)
        appG.Response(http.StatusOK, e.ERROR_IMPORT_TAG_FAIL, nil)
        return
    }

    appG.Response(http.StatusOK, e.SUCCESS, nil)
}

Routing

Add a routing method to the routers/router.go file as follows

apiv1 := r.Group("/api/v1")
apiv1.Use(jwt.JWT())
{
    ...
    //导入标签
    r.POST("/tags/import", v1.ImportTag)
}

Verification

image

Here we will use the previously exported Excel file as a reference to accesshttp://127.0.0.01:8000/tags/import, check whether the return and data are correctly put into storage

Summary

In this article, the usage of Excel import and export is briefly introduced. The following 2 packages are used:

You can carefully read its implementation and usage, which is more helpful for your control.

extracurricular

  • Tag: Export is implemented by excelize (you may find it easier)
  • Tag: Implementation of Import and De-duplication Function
  • Artice: Implementation of Import and Export Functions

It is also a good practice for you. If you are interested, you can try it.

References

This series of sample codes

This series of catalogues