golang-把sql查询结果导出到excel

package main

import (
	"database/sql"
	"fmt"
	"strconv"
	"strings"

	_ "github.com/denisenkom/go-mssqldb"
	"github.com/xuri/excelize/v2"
)

const (
	server   = "127.0.0.1"
	port     = "1433"
	user     = "root"
	password = "password"
	database = "databasename"
)

var db *sql.DB

/*初始化数据库连接 赋值给全局变量  db */
func initDB() (err error) {

	connStr := fmt.Sprintf("server=%s;user id =%s;password=%s;port=%s;database=%s;encrypt=disable;", server, user, password, port, database)
	db, err = sql.Open("mssql", connStr)

	if err != nil {
		return err
	}
	// 尝试与数据库建立连接（校验dsn是否正确.）
	err = db.Ping()
	if err != nil {
		return err
	}
	return nil
}

 

func SQL2XLSX(sql_string string, fileName string) {

	f := excelize.NewFile()

	//sql_string := ` select * from table1	`

	if rows, err := db.Query(sql_string); err == nil {

		index := 0 //写入xls的行数
		cloumns, _ := rows.Columns()

		//写入自定义字段
		for i := 0; i < len(cloumns); i++ {

			k := strings.ToUpper(string(rune(97+i))) + "1"

			fmt.Println(k, cloumns[i])

			f.SetCellValue("Sheet1", k, cloumns[i])

		}

		values := make([]sql.RawBytes, len(cloumns))

		scanArgs := make([]interface{}, len(values))

		for i := range values {

			scanArgs[i] = &values[i]

		}

		for rows.Next() {

			err = rows.Scan(scanArgs...)

			if err != nil {
				fmt.Println(err)

			}

			var value string

			for i, col := range values {

				if col == nil {

					value = "  "

				} else {

					value = string(col)

				}

				k := strings.ToUpper(string(rune(97+i))) + strconv.Itoa(index+2) //生成XLS列名

				fmt.Println(k)

				f.SetCellValue("Sheet1", k, value)

				//	fmt.Println(cloumns[i], ": ", value)

			}

			index++

		}

		err = f.SaveAs(fileName)

		if err != nil {

			fmt.Println(err)

		}

	}

}

　　

 

再来个返回列头相同的多个查询导入到一个excel文件

sqllist 是一个sql查询字符串组成的切片

    //自定义字段名称

   autoField := []string{"列1", "列2", "列3", "列4", "列5", "列6" }

   执行  SQL2XLSX(autoField , "1.xlsx")

func SQL2XLSX(autoField []string, fileName string) {

	f := excelize.NewFile()

	//写入自定义字段
	for i := 0; i < len(autoField); i++ {

		k := strings.ToUpper(string(rune(97+i))) + "1"

		fmt.Println(k, autoField[i])

		f.SetCellValue("Sheet1", k, autoField[i])

	}

	//sql_string := ` QN_goods_in_out_wsh_jxc2 	76	`

	index := 0 //写入xls的行数

	for i, m := range sqllist {
		fmt.Println(i, m)
		sql_string := m

		if rows, err := db.Query(sql_string); err == nil {

			cloumns, _ := rows.Columns()

			values := make([]sql.RawBytes, len(cloumns))

			scanArgs := make([]interface{}, len(values))

			for i := range values {

				scanArgs[i] = &values[i]

			}

			for rows.Next() {

				err = rows.Scan(scanArgs...)

				if err != nil {
					fmt.Println(err)

				}

				var value string

				for i, col := range values {

					if col == nil {

						value = "  "

					} else {

						value = string(col)

					}

					k := strings.ToUpper(string(rune(97+i))) + strconv.Itoa(index+2) //生成XLS列名

					fmt.Println(k)

					f.SetCellValue("Sheet1", k, value)

					//	fmt.Println(cloumns[i], ": ", value)

				}

				index++

			}

			err = f.SaveAs(fileName)

			if err != nil {

				fmt.Println(err)

			}

		}
	}
}