Golang implements reading excel data and importing database

Go is a programming language suitable for writing efficient and concurrent web applications, and can also be used for data processing and analysis. In the process of data processing and analysis, Excel is a commonly used spreadsheet software. In many cases, data needs to be imported into Excel for analysis and processing. In Go, two different excel packages can be used to manipulate Excel files, namely xuri/excelize and 360EntSecGroup-Skylar/excelize.

1./xuri/excelize/v2

xuri/excelize is a third-party library for manipulating Excel files that can be imported in the following ways:

import "/xuri/excelize/v2"

xuri/excelize is one of the most popular Excel file manipulation libraries in the Go language community. It provides rich features for reading, modifying, and creating Excel files, including but not limited to:

Read data in Excel files
Modify data in Excel files
Insert an image in an Excel file
Add a chart in an Excel file
Set printing options in Excel files
Encrypt Excel files
Provide more file format support (such as CSV, XML)

The following is a sample code for xuri/excelize to read an Excel file:

package main

import (
    "fmt"

    "/xuri/excelize/v2"
)

func main() {
    file, err := ("")
    if err != nil {
        (err)
        return
    }

    cells := ("Sheet1")
    for _, row := range cells {
        for _, colCell := range row {
            (colCell, "\t")
        }
        ()
    }
}

2./360EntSecGroup-Skylar/excelize/v2

360EntSecGroup-Skylar/excelize is an Excel file processing library developed by the 360 Enterprise Security Group. It can be imported using the following methods:

import "/360EntSecGroup-Skylar/excelize/v2"

Compared to xuri/excelize, 360EntSecGroup-Skylar/excelize provides more features, including but not limited to the following features:

Read data in Excel files
Modify data in Excel files
Insert an image in an Excel file
Add a chart in an Excel file
Set printing options in Excel files
Encrypt Excel files
Provide more file format support (such as CSV, XML)
Provides more cell formatting options (such as setting cell style, setting cell width and height)

Here is a sample code for 360EntSecGroup-Skylar/excelize to read Excel files:

package main

import (
    "fmt"

    "/360EntSecGroup-Skylar/excelize/v2"
)

func main() {
    file, err := ("")
    if err != nil {
        (err)
        return
    }

    cells := ("Sheet1")
    for _, row := range cells {
        for _, colCell := range row {
            (colCell, "\t")
        }
        ()
    }
}

Practical combat:

// @description: SharedImport Import// @param {string} filePath
// @param {int64} commitID
// @param {} header
// @return {*}
func SharedImport(filePath, fileName string, commitID int64, header ) (int64, error) {
    ("SharedImport", "processing cpp file with file path specified: %v", filePath)
    f, err := (filePath)
    if err != nil {
        ("", "%v", err)
        return -1, err
    }
    sheetList := (())
    ("SharedImport", "share file contains sheet list: %v", ())
    if (sharedbilltype.SHARE_INFOMATION_CN) {
        ("SharedImport", "processing CN share template, finding key cells with chinese")
        err = ShareCNImport(f, filePath, fileName, commitID, header)
        if err != nil {
            ("", "%v", err)
            return -1, err
        }
        return commitID, nil
    } else if (sharedbilltype.SHARE_INFOMATION_EN) {
        ("SharedImport", "processing EN share template, finding key cells with english")
        err = ShareENImport(f, filePath, fileName, commitID, header)
        if err != nil {
            ("", "%v", err)
            return -1, err
        }
        return commitID, nil
    } else {
        msgCN := sharedbilltype.ERR_INVALID_SHARE_TEMPLATE_CN
        msgEN := sharedbilltype.ERR_INVALID_SHARE_TEMPLATE_EN
        overviewMsgCN := sharedbilltype.ERR_OVERVIEW_WRONG_FILE_CN
        overviewMsgEN := sharedbilltype.ERR_OVERVIEW_WRONG_FILE_EN
        ("SharedImport", msgCN)
        ("SharedImport", msgEN)
        ("SharedImport", overviewMsgCN)
        ("SharedImport", overviewMsgEN)
        err = ([]*{
            NewInvalidFormatRecord(filePath, commitID, msgCN, msgEN, 0, "", 0, "", 0, "", header, fileName),
            NewInvalidFormatOverviewRecord(filePath, commitID, 0, 0, overviewMsgCN, overviewMsgEN, header, fileName),
        })
        if err != nil {
            ("", "%v", err)
            return -1, err
        }
        return commitID, nil
    }
}

3. Summary

/xuri/excelize/v2 and /360EntSecGroup-Skylar/excelize/v2 are libraries in Golang for manipulating Excel files. The difference is that they are maintained by different developers and their functionality, performance and usage are slightly different.

/xuri/excelize/v2 is an Excel file processing library developed by Xuri. It can read, write and edit Excel files in XLSX format. It supports a large number of Excel functions, such as charts, pivot tables, conditional formats, etc. It also provides support for PDF and HTML exports. The documentation for this library is detailed, with good maintainability and ease of use.

/360EntSecGroup-Skylar/excelize/v2 is an Excel file processing library developed by the 360 Security team. It can also read, write and edit Excel files in XLSX format. It supports a large number of Excel functions, such as formulas, charts, pivot tables, conditional formats, etc. It also provides support for CSV, TSV and JSON exports. This library has relatively little documentation, but it has good performance and stability.

Therefore, choosing which Excel processing library to use depends on your needs and preferences. If you need to process more complex Excel files and export them to PDF or HTML files, you can choose /xuri/excelize/v2; if you need to process a large number of Excel files quickly and export them to CSV, TSV, or JSON files, you can choose /360EntSecGroup-Skylar/excelize/v2.

4. Knowledge extension

golang get Excel data to update database

package main

import (
	"bufio"
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"strconv"
	"strings"

	_ "/alexbrainman/odbc"
	"/xuri/excelize/v2"
	// "encoding/json"
	// "reflect"
)

func main() {

	chuliYuanExecl()
}

func chuliYuanExecl() {

	("This program prints the input content，enter'exit()' Exit the program！")
	// ("Please enter content:")	input := ()
	for {

		("=================！Start the conversion！=====================================")

		db, err := ("odbc", "driver={sql server};server=127.0.0.1;port=1433;uid=xx;pwd=xxx;database=xxx")
		if err != nil {
			(())
		}

		("Input File >> ")
		()
		if ((()), "") == 0 {
			continue
		}
		if ((()), "exit()") == 0 {
			(0)
		}
		("The file you entered is:", (((((), "&amp; '", "", 1), "'", "", 1), "”", "", 1), "“", "", 1))
		yuanPath := (((((), "&amp; '", "", 1), "'", "", 1), "”", "", 1), "“", "", 1)
		f, err := (yuanPath)
		if err != nil {
			(err)
			return
		}
		defer func() {
			if err := (); err != nil {
				(err)
			}
		}()

		("Start loading the database...")

		var sheel = "Sheet1"

		// Get all cells on Sheet1		rows, err := (sheel)
		if err != nil {
			(err)
			return
		}

		("Please wait...")

		// Get information		for i, _ := range rows {
			// If there is a title or header, you need to skip it			if i &lt; 1 {
				continue
			}
			// Get cell data			djbh, err := (sheel, "A"+(i+1))
			if err != nil {
				(err)
				return
			}
			szdbh, err := (sheel, "E"+(i+1))
			if err != nil {
				(err)
				return
			}
			bm, err := (sheel, "G"+(i+1))
			if err != nil {
				(err)
				return
			}
			szdzd, err := (sheel, "H"+(i+1))
			if err != nil {
				(err)
				return
			}
			djzd, err := (sheel, "I"+(i+1))
			if err != nil {
				(err)
				return
			}

			if djbh == "" {
				continue
			}
			if szdbh == "" {
				continue
			}
			if bm == "" {
				continue
			}
			if szdzd == "" {
				continue
			}
			if djzd == "" {
				continue
			}

			var (
				CGPHID  string
			)

			// Database query			var whcgphsql = `SELECT
					a.ITEM_BUSINESS_ID 
				FROM
					ITEM_PURCHASE a
					LEFT JOIN ITEM b ON a.ITEM_ID= b.ITEM_BUSINESS_ID 
				WHERE
					b.ITEM_CODE= '` + djbh + `'`

			dhuserrows, err := (whcgphsql)
			defer ()
			for () {
				err := (&amp;CGPHID)
				if err != nil {
					(err)
				}
			}

			// Database update			var xssql = `UPDATE ` + bm + ` SET ` + szdzd + `='` + szdbh + `' WHERE ` + djzd + `='` + CGPHID + `'`
			(xssql)
			x, err := (xssql)
			if err != nil {
				("UPDATE failed:", (), x)
			}
		}
		("--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------)
	}
	return
}

golang gets the specified column data in excel

Get the specified column data in golang

func ValueLoc(excel_path, sheet_name string, cols []string) [][]string{
	xlsx, err := (excel_path)
	if err != nil {
		(1)
		return 
	}

	rows := (sheet_name)
	colIndex := make([]int, len(cols))

	// Get the serial number of each col	for index, row := range rows {
		if index == 0 {
			num := 0
			for _, col := range cols {
				for key, colCell := range row {
					if colCell == col {
						colIndex[num] = key + 1
						num++
					}
				}
			}
		}
	}

	// Recorrect the amount present to solve the initial variable length problem	res_len := 0
	for _, coli := range colIndex {
		if coli-1 &gt;= 0 {
			res_len++
		}
	}

	// Get data	res_data := make([][]string, len(rows)-1)
	res_index := 0
	for index, row := range rows {
		if index != 0 {
			data := make([]string, res_len)
			for i, colindex := range colIndex {
				for key, colCell := range row {
					if key == colindex-1 {
						data[i] = colCell
					}
				}
			}
			res_data[res_index] = data
			res_index++
		}
	}
	return res_data
}

This is the article about golang reading excel data and importing database. For more related contents of go to reading excel data and importing databases, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!