@[TOC]大大
import (
"errors"
"fmt"
"github.com/360EntSecGroup-Skylar/excelize"
"strings"
)
func Sql2Xlsx(field []string,data []interface{},fileName string){
xlsx := excelize.NewFile()
//写第一行写字段名称
for col:=0;col<len(field);col++{
if colName,err:=getColName(col);err==nil{
_=xlsx.SetCellValue("Sheet1", fmt.Sprintf("%s%d",colName,1),field[col])
}
}
//写数据
for row :=0;row<len(data);row++{
for col:=0;col<len(field);col++{
if colName,err:=getColName(col);err==nil {
_=xlsx.SetCellValue("Sheet1", fmt.Sprintf("%s%d",colName,2+row),data[row].([]interface{})[col])
}
}
}
_ = xlsx.SaveAs(fileName)
}
//上限列为到ZZ
func getColName(length int )(string,error){
const asciiLength int =26 //字母数量A-Z
//701列
if length>=asciiLength*asciiLength+asciiLength{
return "",errors.New("column out of bounds")
}
Ascii:=make([]string,0)
for i:=97;i<97+26;i++{
Ascii= append(Ascii,strings.ToUpper(string(i)) )
}
if length<asciiLength{
return Ascii[length],nil
}else {
colName:=Ascii[(length/asciiLength)-1] //取头
colName+=Ascii[length%asciiLength]//取余数 为第二位
return colName,nil
}
}
func example(){
var field =[]string{"编号", "姓名", "进出状态", "时间","公司","班组"}
data:=make([]interface{},0)
ff:=[]interface{}{"1","张三",1,"2019-26-12 10:00:00","GRD","班组2"}
for i:=0;i<20;i++{
data=append(data,ff)
}
fmt.Println(len(data),data)
Sql2Xlsx(field,data,"aa.xlsx")
}