代码如下：
package main
import (
_ "
gopkg.in/goracle.v2"
_ "
github.com/asifjalil/cli"
"
github.com/jmoiron/sqlx"
"flag"
"fmt"
"
github.com/axgle/mahonia"
"strings"
"os"
"strconv"
"io/ioutil"
"database/sql"
"bufio"
"bytes"
"time"
"
github.com/360EntSecGroup-Skylar/excelize"
"runtime"
)
func DataGetter(db *sqlx.DB,query string,rowChan chan <- []interface{},columnChan chan <- []string){
defer db.Close()
row,err := db.Queryx(query)
if err != nil{
panic(err)
}
defer row.Close()
columns,err := row.Columns()
columnChan <- columns
close(columnChan)
if err !=nil {
panic(fmt.Sprint("failed to add sheet:%s",err.Error()))
}
for row.Next(){
r,err := row.SliceScan()
if err !=nil{
panic("db row query failed")
}
rowChan <- r
}
close(rowChan)
}
func ExcelWriter(sheetHead string,fileName string,rowChan <- chan[]interface{},columnChan <- chan[]string){
cnt := 2
sheetcnt := 1
var r []interface{}
columns := <- columnChan
hasNext := true
excel := excelize.NewFile()
excel.NewSheet(sheetHead)
excel.SetSheetRow(sheetHead,"A1",columns)
//excel.SetSheetRow(sheetHead,"A"+strconv.Itoa(cnt),columns)
for hasNext{
r,hasNext = <- rowChan
for a := 0;a<len(columns);a++{
t,ok := r[a].(time.Time)
if ok{
if t.IsZero(){
excel.SetCellValue(sheetHead,excelize.ToAlphaString(a)+strconv.Itoa(cnt),"")
}else{
excel.SetCellValue(sheetHead,excelize.ToAlphaString(a)+strconv.Itoa(cnt),t)
}
}else{
excel.SetCellValue(sheetHead,excelize.ToAlphaString(a)+strconv.Itoa(cnt),r[a])
}
}
cnt = cnt + 1
if cnt >= 100000{
excel.NewSheet(sheetHead+strconv.Itoa(sheetcnt))
sheetHead = sheetHead+strconv.Itoa(sheetcnt)
excel.SetSheetRow(sheetHead,"A1",columns)
cnt = 2
sheetcnt = sheetcnt + 1
}
}
excel.SaveAs(fileName+".xlsx")
}
func getConn(dbconn string,dbtype string)(db *sqlx.DB){
if dbtype == "oracle"{
driver := "goracle"
return sqlx.MustOpen(driver,strings.Replace(dbconn,":","/",1))
}else if dbtype == "db2"{
driver := "cli"
userPart := strings.Split(dbconn,"@")[0]
username := strings.Split(userPart,":")[0]
password := strings.Split(userPart,":")[1]
dbPart := strings.Split(dbconn,"@")[0]
dbname := strings.Split(dbPart,"/")[1]
dbip := strings.Split(strings.Split(dbPart,"/")[0],":")[0]
dbport := strings.Split(strings.Split(dbPart,"/")[0],":")[1]
connString := fmt.Sprintf("Driver={IBM DB2 ODBC Driver};Hostname=%s;Port=%s;Protocol=TCPIP;Database=%s;CurrentSchema=%s;UID=%s;PWD=%s;",
dbip,dbport,dbname,username,password)
return sqlx.MustOpen(driver,connString)
}else if dbtype == "postgres"{
driver := "postgres"
connString := "postgres://" + dbconn
return sqlx.MustOpen(driver,connString)
}else{
fmt.Println("dbtype not matched!")
os.Exit(-1)
return
}
}
func main() {
//输入参数解析
dbconn := flag.String("d","",`Database connect string,use "user:password@ip:port/dbname" for db2 or "user:password@tnsname" for oracle`)
dbtype := flag.String("t","","Database type:oracle db2 mysql mssql")
filetype := flag.String("f","xlsx","exported file type:xlsx or txt default:xlsx")
//xlsx require options
sheetname := flag.String("h","Sheet1","sheet name: default Sheet1")
//txt require options
//charset := flag.String("c","utf-8","charset for exported text file:gbk utf-8 and so on")
//separator := flag.String("s","/","separator: default:/")
//sql options
query := flag.String("q","","sql in one line")
sqlfile := flag.String("l","","sqlfile")
filename := flag.String("n",time.Now().Format("20060102150405"),"filename")
flag.Parse()
if *dbconn == "" || *dbtype == "" || *filetype == ""{
flag.Usage()
return
}
if *query == "" && *sqlfile == ""{
flag.Usage()
return
}
if *sqlfile != "" {
sqlbyte,err := ioutil.ReadFile(*sqlfile)
if err != nil{
panic("read sqlfile failed!")
}
utf8 := mahonia.NewEncoder("utf-8")
*query = utf8.ConvertString(string(sqlbyte))
}
runtime.GOMAXPROCS(2)
if *filetype == "xlsx"{
rowChan := make(chan []interface{},50000)
columnsChan := make(chan []string)
db := getConn(*dbconn,*dbtype)
go DataGetter(db,*query,rowChan,columnsChan)
ExcelWriter(*sheetname,*filename,rowChan,columnsChan)
//}else if *filetype == "txt"{
// db := getConn(*dbconn,*dbtype)
// TextFileExporter(db,*charset,*separator,*filename,*query)
//}else{
flag.Usage()
return
}
}