package main
import (
"fmt"
"strconv"
//"pkg/mod/github.com/360!ent!sec!group-!skylar/excelize@v1.4.1"
"github.com/360EntSecGroup-Skylar/excelize"
_ "github.com/go-sql-driver/mysql"
"github.com/jinzhu/gorm"
"log"
)
var DB1 *gorm.DB
type User struct {
Rechargedate string `json:"rechargedate"`
Rechargetime string `json:"rechargetime"`
Rechargezone string `json:"rechargezone"`
Rechargechannel string `json:"rechargechannel"`
Rechargemoney string `json:"rechargemoney"`
Rechargepeople string `json:"rechargepeople"`
}
//初始化并保持连接
func init() {
var err error
//DB, err = gorm.Open("mysql", "backupinfo:backupinfo123123@tcp(127.0.0.1:xxxxx)/test?charset=utf8&parseTime=True&loc=Local")
//DB, err = gorm.Open("mysql", "backupinfo:backupinfo123123@tcp(xxxxx:xxxx)/test?charset=utf8&parseTime=True&loc=Local")
DB1, err = gorm.Open("mysql", "xx:xxx@tcp(xxx63994)/xxx?charset=utf8&parseTime=True&loc=Local")
// DB.LogMode(true)//打印sql语句
if err != nil {
log.Fatalf("database11 connect is err:%s", err.Error())
} else {
log.Print("connect database11 is success")
}
err = DB1.DB().Ping()
if err != nil {
DB1.DB().Close()
log.Fatalf("Error on opening database11 connection: %s", err.Error())
}
}
func GetRoleList1() (userlist []User, err error) {
//err = DB1.Find(&rolelist,"id < ?",10000).Error
//err = DB.Find(&rolelist,20).Error
//fmt.Println(rolelist)
//var userlist []User
//DB1.Raw("select id,publish_id from users limit 10").Find(&userlist)
sql := "SELECT DATE(updated_at) 日期,DATE_FORMAT(updated_at,'%H') 充值时间,server_id 区服,channel_id 渠道,SUM(amount)/100 AS 实付金额,COUNT(DISTINCT(open_id)) 充值人数 FROM orders limit 10"
fmt.Println(sql)
//DB1.Raw("select id,publish_id from users limit 10").Find(&userlist)
DB1.Raw("SELECT DATE(updated_at) as rechargedate ,DATE_FORMAT(updated_at,'%H') as rechargetime ,server_id as rechargezone,channel_id as rechargechannel ,SUM(amount)/100 as rechargemoney,COUNT(DISTINCT(open_id)) as rechargepeople FROM orders WHERE game_id in ('202','208') AND server_id IN ('820001','820002','820003') AND status=3 GROUP BY server_id,channel_id,DATE_FORMAT(updated_at,'%h') ORDER BY DATE_FORMAT(updated_at,'%h'),server_id LIMIT 60").Find(&userlist)
fmt.Println("sql执行结果!!!!")
fmt.Println(userlist)
return userlist, err
}
func main() {
//var user User
//var userlists []User
userlist,_ := GetRoleList1()
fmt.Println("打印数据:")
var userlists []map[string]interface{}
for i := 0; i < len(userlist); i++ {
userdic := make(map[string]interface{})
userdic["rechargedate"] = userlist[i].Rechargedate
userdic["rechargetime"] = userlist[i].Rechargetime
userdic["rechargezone"] = userlist[i].Rechargezone
userdic["rechargechannel"] = userlist[i].Rechargechannel
userdic["rechargemoney"] = userlist[i].Rechargemoney
userdic["rechargepeople"] = userlist[i].Rechargepeople
userlists = append(userlists, userdic)
}
fmt.Println(userlist)
fmt.Println(userlists)
// 列标题
//titles := []string{
// "姓名","年龄","sex",
//}
//新的列标题
titles := []string{
"充值日期","充值时间","充值区服","充值渠道","实付金额","充值人数",
}
//数据源
//data := []map[string]interface{}{
// map[string]interface{}{"name":"jack","age":18,"sex":1},
// map[string]interface{}{"name":"mary","age":28,"sex":2},
//}
//fmt.Println(data)
f := excelize.NewFile()
// Create a new sheet.
index := f.NewSheet("Sheet1")
for clumnNum,v := range titles {
sheetPosition := Div(clumnNum+1)+"1"
fmt.Print(sheetPosition)
f.SetCellValue("Sheet1", sheetPosition,v)
}
for lineNum,dic := range userlists {
clumnNum := 0
fmt.Println(lineNum,dic)
for k,_ := range dic{
clumnNum++;
Anewposition := "A" + strconv.Itoa(lineNum+2)
Bnewposition := "B" + strconv.Itoa(lineNum+2)
Cnewposition := "C" + strconv.Itoa(lineNum+2)
Dnewposition := "D" + strconv.Itoa(lineNum+2)
Enewposition := "E" + strconv.Itoa(lineNum+2)
Fnewposition := "F" + strconv.Itoa(lineNum+2)
switch k {
case "rechargedate":
fmt.Println("name的值是:",dic["name"])
f.SetCellValue("Sheet1", Anewposition,dic["rechargedate"])
break
case "rechargetime":
f.SetCellValue("Sheet1", Bnewposition,dic["rechargetime"])
break
case "rechargezone":
f.SetCellValue("Sheet1", Cnewposition,dic["rechargezone"])
break
case "rechargechannel":
f.SetCellValue("Sheet1", Dnewposition,dic["rechargechannel"])
break
case "rechargemoney":
f.SetCellValue("Sheet1", Enewposition,dic["rechargemoney"])
break
case "rechargepeople":
f.SetCellValue("Sheet1", Fnewposition,dic["rechargepeople"])
break
}
}
}
// Set active sheet of the workbook.
f.SetActiveSheet(index)
//生成图形
if err := f.AddChart("Sheet1", "M1", `{"type":"col3DClustered","series":[{"name":"Sheet1!$C$2","categories":"Sheet1!$B$2:$B$61","values":"Sheet1!$E$2:$E$61"},{"name":"Sheet1!$C$13","categories":"Sheet1!$B$2:$B$61","values":"Sheet1!$E$2:$E$61"},{"name":"Sheet1!$C$32","categories":"Sheet1!$B$2:$B$61","values":"Sheet1!$E$2:$E$61"}],"title":{"name":"Fruit 3D Clustered Column Chart"}}`); err != nil {
fmt.Println(err)
return
}
// Save xlsx file by the given path.
if err := f.SaveAs("Book2.xlsx"); err != nil {
println(err.Error())
}
}
// Div 数字转字母
func Div(Num int) string{
var(
Str string = ""
k int
temp []int //保存转化后每一位数据的值，然后通过索引的方式匹配A-Z
)
//用来匹配的字符A-Z
Slice := []string{"","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O",
"P","Q","R","S","T","U","V","W","X","Y","Z"}
if Num >26 { //数据大于26需要进行拆分
for {
k = Num % 26 //从个位开始拆分，如果求余为0，说明末尾为26，也就是Z，如果是转化为26进制数，则末尾是可以为0的，这里必须为A-Z中的一个
if k == 0 {
temp = append(temp, 26)
k = 26
} else {
temp = append(temp, k)
}
Num = (Num - k) / 26 //减去Num最后一位数的值，因为已经记录在temp中
if Num <= 26{ //小于等于26直接进行匹配，不需要进行数据拆分
temp = append(temp, Num)
break
}
}
}else{
return Slice[Num]
}
for _,value := range temp{
Str = Slice[value] + Str //因为数据切分后存储顺序是反的，所以Str要放在后面
}
return Str
}
有疑问加站长微信联系（非本文作者）
