Golang Gin Practice Serial Eleven Implement export and import to Excel

foreword

In this section, we will implement the export and import functions of label information. This is a very standard function. I hope you can master the basic usage.
In addition, we use two Excel packages in this article. Some of the structure of the original XML format file of excelize was evolved from the tealeg/xlsx format file structure, so it is intended to show that you can use it according to your scene and like to use

configure

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

[app]
...
ExportSavePath = export/

Modify the App struct of setting.go:

type App struct {
    ...
    PrefixUrl string
    ExportSavePath 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 "gin_log/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 + setting.AppSetting.ExportSavePath
}

Some commonly used methods are written here. If the value method changes in the future, you can directly change the internal code, which is invisible to the outside world.

try the standard library

	_ = os.MkdirAll(export.GetExcelFullPath(), os.ModePerm)
	f, err := os.Create(export.GetExcelFullPath() + "test.csv")
	if err != nil {
		fmt.Println(err)
	}
	defer func() {
		_ = f.Close()
	}()
	//_, _ = f.WriteString("\\xEF\\xBB\\xBF")
	w := csv.NewWriter(f)
	data := [][]string{
		{"1", "Ma Yanan", "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 is done:

1,os.Create:
Created a test.csv file

2,f.WriteString("\xEF\xBB\xBF"):
\xEF\xBB\xBF is the hexadecimal format of UTF-8 BOM, the use here is to identify the encoding format of the file, which usually appears at the beginning of the file, so the first step is to write it. If the encoding format of UTF-8 is not identified, the written Chinese characters will be displayed as garbled characters

3,csv.NewWriter:

// NewWriter returns a new Writer that writes to w.
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()
}

WriteAll is actually an encapsulation of Write. It should be noted that w.w.Flush() is called at the end, which fully illustrates the usage scenario of WriteAll. You can think about the author's design intention

export

Download the library for operating excel
go get github.com/tealeg/xlsx

Service method

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

Click to view code
func (t *Tag) Export() (string, error) {
	tags := models.GetTags(t.PageNum, t.PageSize, map[string]any{"name": t.Name, "state": t.State})
	if len(tags) == 0 {
		return "", errors.New("No export data yet")
	}

	file := xlsx.NewFile()
	sheet, err := file.AddSheet("Label Information")
	if err != nil {
		return "", err
	}

	titles := []string{"ID", "name", "founder", "creation time", "Modified by", "Change the time"}
	row := sheet.AddRow()

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

	for _, tag := range tags {
		values := []string{
			strconv.Itoa(tag.ID),
			tag.Name,
			tag.CreatedBy,
			strconv.Itoa(int(tag.CreatedOn)),
			tag.ModifiedBy,
			strconv.Itoa(int(tag.ModifiedOn)),
		}
		row = sheet.AddRow()
		for _, value := range values {
			cell = row.AddCell()
			cell.Value = value
		}
	}

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

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

routers entry

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

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

	tagService := cache_service.Tag{Name: name, State: state, PageNum: util.GetPage(ctx), PageSize: 1000}
	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 the routing method to the routers/router.go file, as follows

{
    apiv1.POST("/tags/export", v1.ExportTag)
}

Authentication interface

http://127.0.0.1:8000/api/v1/tags/export

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

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

StaticFS

Then think about it, it is definitely impossible to download files by directly accessing the address now, so what should we do?
Open the router.go file and add the following code:

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

If you do not understand, it is strongly recommended to review the previous chapters and draw inferences from each other

Verify download

Visit the export_url above again, like: http://127.0.0.1:8000/export/tags-1528903393.xlsx, is it successful?

import

Install the library for operating excel
go get github.com/xuri/excelize/v2

Service method

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

func (t *Tag) Import(r io.Reader) error {
	file, err := excelize.OpenReader(r)
	if err != nil {
		return err
	}
	rows, err := file.GetRows("Label Information")
	if err != nil {
		return err
	}
	for iRow, row := range rows {
		fmt.Println(iRow)
		if iRow > 0 {
			var data []string
			for _, cell := range row {
				data = append(data, cell)
			}
			models.AddTag(data[1], 1, data[2])
		}
	}
	return nil
}

routers entry

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

// ImportTag import tag
func ImportTag(ctx *gin.Context) {
	appG := app.Gin{C: ctx}

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

	tag := cache_service.Tag{}
	err = tag.Import(file)
	if err != nil {
		logging.Warn(err)
		appG.Response(http.StatusOK, e.ERROR, nil)
		return
	}

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

routing

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

apiv1.POST("/tags/import", v1.ImportTag)

verify

http://127.0.0.1:8000/api/v1/tags/import
Here we use the previously exported Excel file as input, visit http://127.0.0.01:8000/tags/import, check whether the return and data are stored correctly

Summarize

In this article, I briefly introduce how to import and export Excel, using the following 2 packages:
https://github.com/tealeg/xlsx
https://github.com/qax-os/excelize

Posted by WLC135 on Thu, 15 Sep 2022 21:34:46 +0300