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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85

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