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)
}
}
}
}
