func writeHeaderRowToSheet(columns []string, sheet string) error {
activeSheet, err := getActiveSheet(sheet)
if err != nil {
return err
}
headerFont := xlsx.NewFont(12, "Verdana")
headerFont.Bold = true
headerFont.Underline = true
headerStyle := xlsx.NewStyle()
headerStyle.Font = *headerFont
var cell *xlsx.Cell
row := activeSheet.AddRow()
for _, col := range columns {
cell = row.AddCell()
cell.SetStyle(headerStyle)
cell.Value = strings.ToTitle(strings.Replace(col, "_", " ", -1))
}
return nil
}
func (p *program) run() {
var id int
var q string
var daySend int = 0
conf, err := os.Stat(Path + "config.json")
config := getJSON(Path + "config.json")
cS := config.Settings
cF := config.Filters
days := []int{cS.Day1, cS.Day2, cS.Day3, cS.Day4, cS.Day5, cS.Day6, cS.Day7}
emailUser := &EmailUser{
cS.MailFrom,
decodePass(cS.MailPass),
cS.MailServer,
cS.MailPort,
}
usersList := ""
lim := ""
for k, _ := range cF.Users {
a := cF.Users[k]
if a == 1 {
usersList += lim + k
}
lim = ", "
}
doorsList := ""
lim = ""
for k, _ := range cF.Doors {
a, _ := cF.Doors[k]
if a == 1 {
doorsList += lim + k
}
lim = ", "
}
summaryFilters := ""
if usersList != "" && cS.UsersFlag == 1 {
summaryFilters += " AND l.USERID IN (" + usersList + ")"
}
if doorsList != "" && cS.DoorsFlag == 1 {
summaryFilters += " AND l.APID IN (" + doorsList + ")"
}
db, err := sql.Open("mysql",
cS.ServerUser+
":"+
decodePass(cS.ServerPass)+
"@tcp("+
cS.ServerIp+
":"+
strconv.Itoa(cS.ServerPort)+
")/")
checkErr(err)
getLastId(db, &id)
q = `SELECT l.LOGTIME, l.CLIENTIP,
CASE WHEN ISNULL(u.NAME) THEN '<Нет>' ELSE (u.NAME) END AS UNAME,
CASE WHEN ISNULL(d.NAME) THEN '<Нет>' ELSE (d.NAME) END AS DNAME,
l.TEXT, p.NAME as OPNAME
FROM ` + "`tc-db-main`" + `.userlog AS l
LEFT OUTER JOIN ` + "`tc-db-main`" + `.devices AS d ON l.APID=d.ID
LEFT OUTER JOIN ` + "`tc-db-main`" + `.personal as u ON l.OBJID=u.ID
LEFT OUTER JOIN ` + "`tc-db-main`" + `.personal as p ON l.USERID=p.ID
WHERE l.ID > ? ` + summaryFilters + ` ORDER BY l.LOGTIME`
for true {
today := int(time.Time.Weekday(time.Now()))
nowHour := int(time.Time.Hour(time.Now()))
nowMin := int(time.Time.Minute(time.Now()))
check, err := os.Stat(Path + "config.json")
if conf.ModTime() != check.ModTime() {
go p.run()
return
}
if today == 0 {
today = 7
}
for _, v := range days {
if v == today && cS.StartHour == nowHour && cS.StartMin == nowMin && daySend != today {
weekAgo := time.Time.AddDate(time.Now(), 0, 0, -7)
now := time.Now()
qxls := ""
qxls = `SELECT l.LOGTIME, l.CLIENTIP,
CASE WHEN ISNULL(u.NAME) THEN '<Нет>' ELSE (u.NAME) END AS UNAME,
CASE WHEN ISNULL(d.NAME) THEN '<Нет>' ELSE (d.NAME) END AS DNAME,
l.TEXT, p.NAME as OPNAME
FROM ` + "`tc-db-main`" + `.userlog AS l
LEFT OUTER JOIN ` + "`tc-db-main`" + `.devices AS d ON l.APID=d.ID
LEFT OUTER JOIN ` + "`tc-db-main`" + `.personal as u ON l.OBJID=u.ID
LEFT OUTER JOIN ` + "`tc-db-main`" + `.personal as p ON l.USERID=p.ID
WHERE l.LOGTIME BETWEEN '` + weekAgo.Format(time.RFC3339) + `' AND '` + now.Format(time.RFC3339) + `' ` + summaryFilters + ` ORDER BY l.LOGTIME`
rowsXls, err := db.Query(qxls)
checkErr(err)
var logStringsXls []LogString
for rowsXls.Next() {
LSX := LogString{}
err := rowsXls.Scan(&LSX.LogTime,
&LSX.ClientIp,
&LSX.UName,
&LSX.DName,
&LSX.Text,
&LSX.OpName)
checkErr(err)
logStringsXls = append(logStringsXls, LSX)
}
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var buf bytes.Buffer
file = xlsx.NewFile()
sheet = file.AddSheet("Отчёт")
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = fmt.Sprintf("Отчёт за %02d.%02d - %02d.%02d", int(weekAgo.Month()), weekAgo.Day(), int(now.Month()), now.Day())
row = sheet.AddRow()
for _, v := range logStringsXls {
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = v.LogTime
if t := cS.EcU; t == 1 {
cell = row.AddCell()
cell.Value = v.OpName
}
if t := cS.EcIP; t == 1 {
cell = row.AddCell()
cell.Value = v.ClientIp
}
if t := cS.EcD; t == 1 {
cell = row.AddCell()
cell.Value = v.DName
}
if t := cS.EcO; t == 1 {
cell = row.AddCell()
cell.Value = v.UName
}
cell = row.AddCell()
cell.Value = v.Text
}
err = file.Save(Path + "spnx.xlsx")
checkErr(err)
xlsxFile, _ := ioutil.ReadFile(Path + "spnx.xlsx")
checkErr(err)
encoded := base64.StdEncoding.EncodeToString(xlsxFile)
lineMaxLength := 500
nbrLines := len(encoded) / lineMaxLength
for i := 0; i < nbrLines; i++ {
buf.WriteString(encoded[i*lineMaxLength:(i+1)*lineMaxLength] + "\n")
}
buf.WriteString(encoded[nbrLines*lineMaxLength:])
header := make(map[string]string)
header["From"] = emailUser.Username
header["To"] = cS.MailTo
header["Subject"] = "Отчёт"
header["MIME-Version"] = "1.0"
header["Content-Type"] = "application/csv; name=\"spnx.xlsx\""
header["Content-Transfer-Encoding"] = "base64"
header["Content-Disposition"] = "attachment; filename=\"spnx.xlsx\""
message := ""
for k, v := range header {
message += fmt.Sprintf("%s: %s\r\n", k, v)
}
message += "\r\n" + fmt.Sprintf("%s\r\n", buf.String())
SendMail(emailUser.Username,
cS.MailTo,
emailUser.EmailServer,
strconv.Itoa(emailUser.Port),
message,
)
daySend = today
}
}
lastId := id
rows, err := db.Query(q, id)
checkErr(err)
getLastId(db, &id)
var logStrings []LogString
for rows.Next() {
LS := LogString{}
err := rows.Scan(&LS.LogTime,
&LS.ClientIp,
&LS.UName,
&LS.DName,
&LS.Text,
&LS.OpName)
checkErr(err)
logStrings = append(logStrings, LS)
}
if lastId != id && lastId != 0 {
text := ""
for _, v := range logStrings {
text += fmt.Sprintf("%s", v.LogTime)
if t := cS.EcU; t == 1 {
text += fmt.Sprintf("\tПользователь: %s", v.OpName)
}
if t := cS.EcIP; t == 1 {
text += fmt.Sprintf(" (%s)", v.ClientIp)
}
if t := cS.EcD; t == 1 {
text += fmt.Sprintf("\tТочка прохода: %s", v.DName)
}
if t := cS.EcO; t == 1 {
text += fmt.Sprintf("\tОбъект: %s", v.UName)
}
text += fmt.Sprintf("\t%s\n", v.Text)
}
header := make(map[string]string)
header["From"] = emailUser.Username
header["To"] = cS.MailTo
header["Subject"] = "Отчёт"
message := ""
for k, v := range header {
message += fmt.Sprintf("%s: %s\r\n", k, v)
}
message += "\r\n" + text
SendMail(emailUser.Username,
cS.MailTo,
emailUser.EmailServer,
strconv.Itoa(emailUser.Port),
message,
)
}
time.Sleep(cS.Delay * time.Second)
}
}
func (client *TrelloClient) formatterExcel(cards []*TrelloCardSearchResult) (err error) {
var (
file *xlsx.File
sheet *xlsx.Sheet
row *xlsx.Row
cell *xlsx.Cell
)
client.config.QuoteChar = "" // we do not need quoting in excel
file = xlsx.NewFile()
if sheet, err = file.AddSheet("Sheet1"); err != nil {
return
}
for _, card := range cards {
row = sheet.AddRow()
for _, column := range client.buildOutputLine(card) {
cell = row.AddCell()
cell.Value = column
}
}
if err != nil {
fmt.Printf(err.Error())
}
err = file.Write(os.Stdout)
if err != nil {
fmt.Printf(err.Error())
}
return err
}
func main() {
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
var RawNbr int = 4
file = xlsx.NewFile()
sheetName := [2]string{"Big", "Small"}
datafile := [2]string{"../data/moveline/big", "../data/moveline/small"}
for i, v := range sheetName {
sheet = file.AddSheet(v)
// 每列标题
text := [4]string{"ID", "X", "Y", "Angle"}
for i := 0; i < RawNbr; i++ {
if i == 0 {
row = sheet.AddRow()
}
cell = row.AddCell()
cell.Value = text[i]
}
files, _ := ListDir(datafile[i], ".dat")
for j := 0; j < len(files); j++ {
write(files[j], sheet, RawNbr)
}
}
fmt.Println("正在向excel写数据......")
err = file.Save("../outexcel/单条鱼路径.xlsx")
if err != nil {
fmt.Printf(err.Error())
}
}
func writeFooterRowToSheet(totals []int64, sheet string) error {
activeSheet, err := getActiveSheet(sheet)
if err != nil {
return err
}
footerFont := xlsx.NewFont(12, "Verdana")
footerFont.Bold = true
footerStyle := xlsx.NewStyle()
footerStyle.Font = *footerFont
var cell *xlsx.Cell
row := activeSheet.AddRow()
cell = row.AddCell()
cell.SetStyle(footerStyle)
cell.Value = "Total"
for i := 1; i < len(totals); i++ {
cell = row.AddCell()
cell.SetStyle(footerStyle)
cell.SetFloatWithFormat(float64(totals[i]), "#,##0")
}
activeSheet.AddRow()
activeSheet.AddRow()
return nil
}
func main() {
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
var RawNbr int = 6
file = xlsx.NewFile()
sheet = file.AddSheet("Sheet1")
// 每列标题
text := [6]string{"ID", "鱼类型", "出现帧", "脚本ID", "X", "Y"}
for i := 0; i < RawNbr; i++ {
if i == 0 {
row = sheet.AddRow()
}
cell = row.AddCell()
cell.Value = text[i]
}
files, err := ListDir("../data/timeline", ".dat")
for j := 0; j < len(files); j++ {
write(files[j], sheet, 6)
}
err = file.Save("../outexcel/巡游鱼路径.xlsx")
if err != nil {
fmt.Printf(err.Error())
}
}
func test2AddSheet() {
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
file, _ = xlsx.OpenFile("MyXLSXFile.xlsx")
sheet = file.AddSheet("addSheet")
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = "addSheet000101"
cell = row.AddCell()
cell.Value = "addSheet中文1"
err := file.Save("MyXLSXFile.xlsx")
if err != nil {
fmt.Printf(err.Error())
}
}
func test2() {
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
file, _ = xlsx.OpenFile("MyXLSXFile.xlsx")
sheet = file.Sheet["Sheet1"]
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = "000101"
cell = row.AddCell()
cell.Value = "中文1"
err = file.Save("MyXLSXFile1.xlsx")
if err != nil {
fmt.Printf(err.Error())
}
}
func test1() {
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
file = xlsx.NewFile()
sheet = file.AddSheet("Sheet1")
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = "000101"
cell = row.AddCell()
cell.Value = "中文"
err = file.Save("MyXLSXFile.xlsx")
if err != nil {
fmt.Printf(err.Error())
}
}
func (client *TrelloClient) memberFormatterExcel(members []*TrelloMember) (err error) {
var (
file *xlsx.File
sheet *xlsx.Sheet
row *xlsx.Row
cell *xlsx.Cell
)
client.config.QuoteChar = "" // we do not need quoting in excel
file = xlsx.NewFile()
if sheet, err = file.AddSheet("Sheet1"); err != nil {
return
}
header := strings.Split(client.config.SearchResultFields, ",")
row = sheet.AddRow()
for _, column := range header {
cell = row.AddCell()
cell.Value = strings.Title(column)
}
for _, member := range members {
row = sheet.AddRow()
for _, column := range client.buildMemberSlice(member) {
cell = row.AddCell()
cell.Value = column
}
}
if err != nil {
fmt.Printf(err.Error())
}
err = file.Write(os.Stdout)
if err != nil {
fmt.Printf(err.Error())
}
return err
}
func addWarningInfo(sheet *xlsx.Sheet, value string, title string, time string, source string, url string) {
row := sheet.AddRow()
var cellTitle *xlsx.Cell
var cellTime *xlsx.Cell
var cellSource *xlsx.Cell
var cellUrl *xlsx.Cell
var cellValue *xlsx.Cell
cellTitle = row.AddCell()
cellTitle.Value = title
cellTime = row.AddCell()
cellTime.Value = time
cellSource = row.AddCell()
cellSource.Value = source
cellUrl = row.AddCell()
cellUrl.Value = url
cellValue = row.AddCell()
cellValue.Value = value
}
func writeRowToSheet(data [][]byte, sheet string, format map[string]string) ([]int, error) {
activeSheet, err := getActiveSheet(sheet)
if err != nil {
return nil, err
}
var cell *xlsx.Cell
counts := make([]int, len(data))
row := activeSheet.AddRow()
for idx, bytes := range data {
cell = row.AddCell()
if num, err := strconv.Atoi(string(bytes)); err == nil {
counts[idx] = num
if val, ok := format[strconv.Itoa(idx)]; ok {
cell.SetFloatWithFormat(float64(num), val)
} else {
cell.SetFloatWithFormat(float64(num), "#,##0")
}
} else {
if bytes == nil {
cell.Value = "Unknown"
} else if empty(string(bytes)) {
cell.Value = "Blank"
} else {
cell.Value = string(bytes)
}
}
}
return counts, nil
}
func addEmbassyInfo(sheet *xlsx.Sheet, value string, continentName string, embassyNameCN string, embassyNameEN string) {
row := sheet.AddRow()
var cellFlag *xlsx.Cell
var cellContinentName *xlsx.Cell
var cellEmbassyNameCN *xlsx.Cell
var cellEmbassyNameEN *xlsx.Cell
var cellValue *xlsx.Cell
cellFlag = row.AddCell()
cellFlag.Value = "A"
cellContinentName = row.AddCell()
cellContinentName.Value = continentName
cellEmbassyNameCN = row.AddCell()
cellEmbassyNameCN.Value = embassyNameCN
cellEmbassyNameEN = row.AddCell()
cellEmbassyNameEN.Value = embassyNameEN
cellValue = row.AddCell()
cellValue.Value = value
}
func addEmbassyInfoByDetails(sheet *xlsx.Sheet, value string, continentName string, embassyNameCN string, embassyNameEN string) {
row := sheet.AddRow()
var cellFlag *xlsx.Cell
var cellContinentName *xlsx.Cell
var cellCountryName *xlsx.Cell
var cellEmbassyNameCN *xlsx.Cell
var cellEmbassyNameEN *xlsx.Cell
var cellMobile *xlsx.Cell
var cellAddress *xlsx.Cell
var cellAddressalid *xlsx.Cell
var cellValue *xlsx.Cell
cellFlag = row.AddCell()
cellFlag.Value = "B"
cellContinentName = row.AddCell()
cellContinentName.Value = continentName
cellCountryName = row.AddCell()
cellCountryName.Value = ""
cellEmbassyNameCN = row.AddCell()
cellEmbassyNameCN.Value = embassyNameCN
cellEmbassyNameEN = row.AddCell()
cellEmbassyNameEN.Value = embassyNameEN
cellMobile = row.AddCell()
cellMobile.Value = ""
cellAddress = row.AddCell()
cellAddress.Value = ""
cellAddressalid = row.AddCell()
cellAddressalid.Value = ""
cellValue = row.AddCell()
cellValue.Value = value
}
func main() {
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
file = xlsx.NewFile()
sheet = file.AddSheet("Sheet1")
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = "I am a cell!"
err = file.Save("d:/Desktop/demo_write.xlsx")
if err != nil {
fmt.Printf(err.Error())
}
}
func addHeader(sheet *xlsx.Sheet) {
var cell *xlsx.Cell
var row *xlsx.Row
row = sheet.AddRow()
cell = row.AddCell()
cell.SetString("筆數")
cell = row.AddCell()
cell.Value = "捐款人姓名"
cell = row.AddCell()
cell.Value = "累計(元) "
cell = row.AddCell()
cell.Value = "捐款明細 / 捐款日期"
cell = row.AddCell()
cell.Value = "星期"
cell = row.AddCell()
cell.Value = "捐款日期與報導日期間隔差時間"
cell = row.AddCell()
cell.Value = "捐款人姓名> 4個字"
}
func init() {
defer func() {
// 获取输出方式列表
for out, _ := range Output {
OutputLib = append(OutputLib, out)
}
util.StringsSort(OutputLib)
}()
/************************ excel 输出 ***************************/
Output["excel"] = func(self *Collector, dataIndex int) {
defer func() {
if err := recover(); err != nil {
Log.Println(err)
}
}()
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
folder1 := "result/data"
folder2 := folder1 + "/" + self.startTime.Format("2006年01月02日 15时04分05秒")
filename := folder2 + "/" + util.FileNameReplace(self.Spider.GetName()+"_"+self.Spider.GetKeyword()+" "+fmt.Sprintf("%v", self.sum[0])+"-"+fmt.Sprintf("%v", self.sum[1])) + ".xlsx"
// 创建文件
file = xlsx.NewFile()
// 添加分类数据工作表
for Name, Rule := range self.GetRules() {
// 跳过不输出的数据
if len(Rule.GetOutFeild()) == 0 {
continue
}
// 添加工作表
sheet = file.AddSheet(util.ExcelSheetNameReplace(Name))
// 写入表头
row = sheet.AddRow()
for _, title := range Rule.GetOutFeild() {
cell = row.AddCell()
cell.Value = title
}
cell = row.AddCell()
cell.Value = "当前链接"
cell = row.AddCell()
cell.Value = "上级链接"
cell = row.AddCell()
cell.Value = "下载时间"
num := 0 //小计
for _, datacell := range self.DockerQueue.Dockers[dataIndex] {
if datacell["RuleName"].(string) == Name {
row = sheet.AddRow()
for _, title := range Rule.GetOutFeild() {
cell = row.AddCell()
vd := datacell["Data"].(map[string]interface{})
if v, ok := vd[title].(string); ok || vd[title] == nil {
cell.Value = v
} else {
cell.Value = util.JsonString(vd[title])
}
}
cell = row.AddCell()
cell.Value = datacell["Url"].(string)
cell = row.AddCell()
cell.Value = datacell["ParentUrl"].(string)
cell = row.AddCell()
cell.Value = datacell["DownloadTime"].(string)
num++
}
}
// Log.Printf("[任务：%v | 关键词：%v | 小类：%v] 输出 %v 条数据！！！\n", self.Spider.GetName(), self.Spider.GetKeyword(), Name, num)
}
// 创建/打开目录
f2, err := os.Stat(folder2)
if err != nil || !f2.IsDir() {
if err := os.MkdirAll(folder2, 0777); err != nil {
Log.Printf("Error: %v\n", err)
}
}
// 保存文件
err = file.Save(filename)
if err != nil {
Log.Println(err)
}
}
/************************ CSV 输出 ***************************/
Output["csv"] = func(self *Collector, dataIndex int) {
defer func() {
if err := recover(); err != nil {
Log.Println(err)
}
}()
folder1 := "result/data"
folder2 := folder1 + "/" + self.startTime.Format("2006年01月02日 15时04分05秒")
filenameBase := folder2 + "/" + util.FileNameReplace(self.Spider.GetName()+"_"+self.Spider.GetKeyword()+" "+fmt.Sprintf("%v", self.sum[0])+"-"+fmt.Sprintf("%v", self.sum[1]))
// 创建/打开目录
f2, err := os.Stat(folder2)
if err != nil || !f2.IsDir() {
if err := os.MkdirAll(folder2, 0777); err != nil {
Log.Printf("Error: %v\n", err)
}
}
// 按数据分类创建文件
for Name, Rule := range self.GetRules() {
// 跳过不输出的数据
if len(Rule.GetOutFeild()) == 0 {
continue
}
file, err := os.Create(filenameBase + " (" + util.FileNameReplace(Name) + ").csv")
if err != nil {
Log.Println(err)
continue
}
file.WriteString("\xEF\xBB\xBF") // 写入UTF-8 BOM
w := csv.NewWriter(file)
th := Rule.GetOutFeild()
th = append(th, []string{"当前链接", "上级链接", "下载时间"}...)
w.Write(th)
num := 0 //小计
for _, datacell := range self.DockerQueue.Dockers[dataIndex] {
if datacell["RuleName"].(string) == Name {
row := []string{}
for _, title := range Rule.GetOutFeild() {
vd := datacell["Data"].(map[string]interface{})
if v, ok := vd[title].(string); ok || vd[title] == nil {
row = append(row, v)
} else {
row = append(row, util.JsonString(vd[title]))
}
}
row = append(row, datacell["Url"].(string))
row = append(row, datacell["ParentUrl"].(string))
row = append(row, datacell["DownloadTime"].(string))
w.Write(row)
num++
}
}
// 发送缓存数据流
w.Flush()
// 关闭文件
file.Close()
// 输出报告
// Log.Printf("[任务：%v | 关键词：%v | 小类：%v] 输出 %v 条数据！！！\n", self.Spider.GetName(), self.Spider.GetKeyword(), Name, num)
}
}
/************************ MongoDB 输出 ***************************/
Output["mgo"] = func(self *Collector, dataIndex int) {
session, err := mgo.Dial(config.DB_URL) //连接数据库
if err != nil {
panic(err)
}
defer session.Close()
session.SetMode(mgo.Monotonic, true)
db := session.DB(config.DB_NAME) //数据库名称
collection := db.C(config.DB_COLLECTION) //如果该集合已经存在的话，则直接返回
for i, count := 0, len(self.DockerQueue.Dockers[dataIndex]); i < count; i++ {
err = collection.Insert((interface{})(self.DockerQueue.Dockers[dataIndex][i]))
if err != nil {
panic(err)
}
}
}
/************************ HBase 输出 ***************************/
var master = cache.Task.Master
var port = ":" + fmt.Sprintf("%v", cache.Task.Port)
var hbaseSocket = teleport.New().SetPackHeader("tentinet")
var hbaseOnce sync.Once
Output["hbase"] = func(self *Collector, dataIndex int) {
hbaseOnce.Do(func() { hbaseSocket.Client(master, port) })
for i, count := 0, len(self.DockerQueue.Dockers[dataIndex]); i < count; i++ {
hbaseSocket.Request(self.DockerQueue.Dockers[dataIndex][i], "log")
}
}
/************************ Mysql 输出 ***************************/
Output["mysql"] = func(self *Collector, dataIndex int) {
db, err := sql.Open("mysql", config.MYSQL_USER+":"+config.MYSQL_PW+"@tcp("+config.MYSQL_HOST+")/"+config.MYSQL_DB+"?charset=utf8")
if err != nil {
fmt.Println(err)
}
defer db.Close()
var newMysql = new(myTable)
for Name, Rule := range self.GetRules() {
//跳过不输出的数据
if len(Rule.GetOutFeild()) == 0 {
continue
}
newMysql.setTableName("`" + self.Spider.GetName() + "-" + Name + "-" + self.Spider.GetKeyword() + "`")
for _, title := range Rule.GetOutFeild() {
newMysql.addColumn(title)
}
newMysql.addColumn("当前连接", "上级链接", "下载时间").
create(db)
num := 0 //小计
for _, datacell := range self.DockerQueue.Dockers[dataIndex] {
if datacell["RuleName"].(string) == Name {
for _, title := range Rule.GetOutFeild() {
vd := datacell["Data"].(map[string]interface{})
if v, ok := vd[title].(string); ok || vd[title] == nil {
newMysql.addRow(v)
} else {
newMysql.addRow(util.JsonString(vd[title]))
}
}
newMysql.addRow(datacell["Url"].(string), datacell["ParentUrl"].(string), datacell["DownloadTime"].(string)).
update(db)
num++
}
}
newMysql = new(myTable)
}
}
}
func createDonation() {
var currentYear = 0
var currentMonth = 0
r := helpcase.GetAllHelpcase()
var file *xlsx.File
var sheet1 *xlsx.Sheet
log.Println("donation export begin")
for _, hp := range r {
dt := helpcase.GetAllDonationDetail(hp.SerialNo)
if len(dt) == 0 {
continue
}
test, _ := time.Parse("2006/1/2", hp.Date)
var isCurrent = isCurrentYearMonthMatch(currentYear, currentMonth, test.Year(), int(test.Month()))
if !isCurrent {
if file != nil {
var err error
if currentMonth < 10 {
err = file.Save(outfolder + "/donation_" + strconv.Itoa(currentYear) + "0" + strconv.Itoa(currentMonth) + ".xlsx")
} else {
err = file.Save(outfolder + "/donation_" + strconv.Itoa(currentYear) + strconv.Itoa(currentMonth) + ".xlsx")
}
if err != nil {
fmt.Printf(err.Error())
}
}
file = xlsx.NewFile()
currentYear = test.Year()
currentMonth = int(test.Month())
}
sheet1 = file.AddSheet(hp.SerialNo)
var publishDate, _ = time.Parse("2006/1/2", hp.Date)
var cell *xlsx.Cell
var row *xlsx.Row
addHeader(sheet1)
for _, donator := range dt {
row = sheet1.AddRow()
cell = row.AddCell()
cell.SetString(donator.SerialNo)
cell = row.AddCell()
cell.Value = donator.Name
cell = row.AddCell()
cell.SetInt(donator.Amount)
cell.NumFmt = "#,##0 ;(#,##0)"
cell = row.AddCell()
cell.Value = donator.Date
cell = row.AddCell()
cell.SetFormula("weekday(\"" + donator.Date + "\",2)")
var dDate, _ = time.Parse("2006/1/2", donator.Date)
duration := dDate.Sub(publishDate)
cell = row.AddCell()
cell.Value = strconv.Itoa(int(duration.Hours() / 24))
cell = row.AddCell()
if donator.LongFour == 1 {
cell.Value = "YES"
} else {
cell.Value = "NO"
}
}
row = sheet1.AddRow()
row.AddCell()
row = sheet1.AddRow()
cell = row.AddCell()
cell.Value = "捐款頁面的URL"
cell = row.AddCell()
cell.SetFormula("HYPERLINK(\"http://search.appledaily.com.tw/charity/projdetail/proj/" + hp.SerialNo + "\",\"http://search.appledaily.com.tw/charity/projdetail/proj/" + hp.SerialNo + "\")")
row = sheet1.AddRow()
cell = row.AddCell()
cell.Value = "出刊日期"
cell = row.AddCell()
cell.Value = hp.Date
cell = row.AddCell()
cell.Value = "專案狀況"
cell = row.AddCell()
cell.Value = hp.Status
row = sheet1.AddRow()
cell = row.AddCell()
cell.Value = "捐款總計"
cell = row.AddCell()
cell.SetInt(hp.Amount)
cell.NumFmt = "#,##0 ;(#,##0)"
cell = row.AddCell()
cell.Value = "捐款筆數"
cell = row.AddCell()
cell.Value = strconv.Itoa(len(dt)) + "筆"
}
if file != nil {
var err error
if currentMonth < 10 {
err = file.Save(outfolder + "/donation_" + strconv.Itoa(currentYear) + "0" + strconv.Itoa(currentMonth) + ".xlsx")
} else {
err = file.Save(outfolder + "/donation_" + strconv.Itoa(currentYear) + strconv.Itoa(currentMonth) + ".xlsx")
}
if err != nil {
fmt.Printf(err.Error())
}
}
log.Println("donation export done")
}
func createSheet2(sheet *xlsx.Sheet) {
r := helpcase.GetAllHelpcaseDetail()
var row *xlsx.Row
var cell *xlsx.Cell
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = "編號"
cell = row.AddCell()
cell.Value = "報導標題"
cell = row.AddCell()
cell.Value = "刊登日期"
cell = row.AddCell()
cell.Value = "星期"
cell = row.AddCell()
cell.Value = "按讚數"
cell = row.AddCell()
cell.Value = "段落數"
cell = row.AddCell()
cell.Value = "報導字數"
cell = row.AddCell()
cell.Value = "報導內圖片數"
cell = row.AddCell()
cell.Value = "報導URL"
cell = row.AddCell()
cell.Value = "報導內容全部"
for _, helpcase := range r {
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = helpcase.SerialNo
cell = row.AddCell()
cell.Value = helpcase.Title
cell = row.AddCell()
cell.Value = helpcase.Date
cell = row.AddCell()
cell.SetFormula("weekday(\"" + helpcase.Date + "\",2)")
cell = row.AddCell()
cell.SetInt(helpcase.LikeCount)
cell = row.AddCell()
cell.SetInt(helpcase.ParagraphCount)
cell = row.AddCell()
cell.SetInt(helpcase.WordCount)
cell = row.AddCell()
cell.SetInt(helpcase.ImgCount)
cell = row.AddCell()
cell.SetFormula("HYPERLINK(\"" + helpcase.DetailUrl + "\",\"" + helpcase.DetailUrl + "\")")
cell = row.AddCell()
cell.Value = helpcase.Content
}
}
func createSheet1(sheet *xlsx.Sheet) {
r := helpcase.GetAllHelpcase()
var row *xlsx.Row
var cell *xlsx.Cell
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = "編號"
cell = row.AddCell()
cell.Value = "報導標題"
cell = row.AddCell()
cell.Value = "刊登日期"
cell = row.AddCell()
cell.Value = "星期"
cell = row.AddCell()
cell.Value = "狀態"
cell = row.AddCell()
cell.Value = "累計(元)"
cell = row.AddCell()
cell.Value = "捐款明細"
font := &xlsx.Font{Color: "blue", Underline: true}
style := xlsx.NewStyle()
style.Font = *font
for _, helpcase := range r {
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = helpcase.SerialNo
cell = row.AddCell()
cell.Value = helpcase.Title
cell = row.AddCell()
cell.Value = helpcase.Date
cell = row.AddCell()
cell.SetFormula("weekday(\"" + helpcase.Date + "\",2)")
cell = row.AddCell()
cell.Value = helpcase.Status
cell = row.AddCell()
cell.SetInt(helpcase.Amount)
cell.NumFmt = "#,##0 ;(#,##0)"
cell = row.AddCell()
cell.SetStyle(style)
cell.SetFormula("HYPERLINK(\"http://search.appledaily.com.tw/charity/projdetail/proj/" + helpcase.SerialNo + "\",\"明細\")")
}
}
/************************ excel 输出 ***************************/
func (self *Collector) excel(dataIndex int) {
defer func() {
if err := recover(); err != nil {
Log.Println(err)
}
}()
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
folder1 := "data"
_folder2 := strings.Split(cache.StartTime.Format("2006-01-02 15:04:05"), ":")
folder2 := _folder2[0] + "时" + _folder2[1] + "分" + _folder2[2] + "秒"
folder2 = folder1 + "/" + folder2
filename := folder2 + "/" + self.Spider.GetName() + "_" + self.Spider.GetKeyword() + " " + strconv.Itoa(self.sum[0]) + "-" + strconv.Itoa(self.sum[1]) + ".xlsx"
file = xlsx.NewFile()
// 添加分类数据工作表
for Name, Rule := range self.GetRules() {
// 跳过不输出的数据
if len(Rule.GetOutFeild()) == 0 {
continue
}
sheet = file.AddSheet(Name)
row = sheet.AddRow()
for _, title := range Rule.GetOutFeild() {
cell = row.AddCell()
cell.Value = title
}
cell = row.AddCell()
cell.Value = "当前链接"
cell = row.AddCell()
cell.Value = "上级链接"
cell = row.AddCell()
cell.Value = "下载时间"
num := 0 //小计
for _, datacell := range self.DockerQueue.Dockers[dataIndex] {
if datacell["RuleName"].(string) == Name {
row = sheet.AddRow()
for _, title := range Rule.GetOutFeild() {
cell = row.AddCell()
vd := datacell["Data"].(map[string]interface{})
if v, ok := vd[title].(string); ok || vd[title] == nil {
cell.Value = v
} else {
j, _ := json.Marshal(vd[title])
cell.Value = string(j)
}
}
cell = row.AddCell()
cell.Value = datacell["Url"].(string)
cell = row.AddCell()
cell.Value = datacell["ParentUrl"].(string)
cell = row.AddCell()
cell.Value = datacell["DownloadTime"].(string)
num++
}
}
// Log.Printf("[任务：%v | 关键词：%v | 小类：%v] 输出 %v 条数据！！！\n", self.Spider.GetName(), self.Spider.GetKeyword(), Name, num)
}
// 创建/打开目录
f2, err := os.Stat(folder2)
if err != nil || !f2.IsDir() {
if err := os.MkdirAll(folder2, 0777); err != nil {
Log.Printf("Error: %v\n", err)
}
}
// 保存文件
err = file.Save(filename)
if err != nil {
Log.Println(err)
}
}
func exportPaidOrders(syncName string, conn redis.Conn, rewardIDs []string) {
resp, err := http.Get("http://country.io/names.json")
if err != nil {
logrus.Fatal(err)
}
defer resp.Body.Close()
jsonCountriesData, err := ioutil.ReadAll(resp.Body)
if err != nil {
logrus.Fatal(err)
}
//countriesMap := make(map[string]interface{})
countriesMap := make(map[string]string)
err = json.Unmarshal([]byte(jsonCountriesData), &countriesMap)
if err != nil {
logrus.Fatal(err)
}
values, err := redis.Values(conn.Do("SMEMBERS", syncName))
if err != nil {
logrus.Fatal(err)
}
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
file = xlsx.NewFile()
sheet, err = file.AddSheet("Sheet1")
if err != nil {
logrus.Fatal(err)
}
// get invalid orders, to exclude them form export
invalidOrders, errInvalidOrders := redis.Strings(redis.Values(conn.Do("SMEMBERS", syncName+"_invalidOrders")))
if errInvalidOrders != nil {
logrus.Fatal(errInvalidOrders)
}
// logrus.Println("invalidOrders:", invalidOrders)
conn.Send("MULTI")
// tmp
nbEntries := 0
nbPaymentInvalid := 0
nbInvalid := 0
for _, value := range values {
b, ok := value.([]byte)
if ok {
orderID := string(b)
conn.Send("HGETALL", syncName+"_order_"+orderID)
}
}
values, err = redis.Values(conn.Do("EXEC"))
if err != nil {
logrus.Fatal(err)
}
for _, value := range values {
stringMap, _ := redis.StringMap(value, nil)
nbItems, _ := strconv.Atoi(stringMap["nbItems"])
if nbItems == 0 {
continue
}
accept := true
// filter reward ids
if rewardIDs != nil && len(rewardIDs) > 0 {
accept = false
// HACK: considering there's only one
// single item per order (id: 0)
rewardID := stringMap["item0_product"]
//logrus.Println(rewardID)
for _, id := range rewardIDs {
if id == rewardID {
accept = true
break
}
}
}
if accept {
paymentStatus, _ := strconv.Atoi(stringMap["status"])
accept = clientapi.OrderStatus(paymentStatus) == clientapi.OrderStatusPaymentDone
if !accept && clientapi.OrderStatus(paymentStatus) == clientapi.OrderStatusInvalid {
nbPaymentInvalid++
}
}
if accept {
// invalid orders are the one that can't be sent because users made a mistake
// in the shipping address (lefting one or more fields blank)
accept = isValid(invalidOrders, stringMap["orderId"])
if !accept {
nbInvalid++
}
}
if accept {
//logrus.Println(stringMap["statusDisplay"])
// logrus.Println(stringMap)
// logrus.Println(stringMap["firstName"]+" "+stringMap["lastName"], "|",
// stringMap["shippingAddr1"], "|",
// stringMap["shippingAddr2"], "|",
// stringMap["shippingCity"], "|",
// stringMap["shippingCode"], "|",
// stringMap["shippingCountry"], "|",
// stringMap["email"], "|")
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = stringMap["firstName"] + " " + stringMap["lastName"]
cell = row.AddCell()
cell.Value = stringMap["shippingAddr1"]
cell = row.AddCell()
cell.Value = stringMap["shippingAddr2"]
cell = row.AddCell()
cell.Value = stringMap["shippingCity"]
// state
cell = row.AddCell()
if stringMap["shippingCountry"] == "US" {
cell.Value = stringMap["shippingState"]
}
cell = row.AddCell()
cell.Value = stringMap["shippingCode"]
// country name
cell = row.AddCell()
cell.Value = countriesMap[stringMap["shippingCountry"]]
cell = row.AddCell()
cell.Value = stringMap["shippingCountry"]
// phone number
cell = row.AddCell()
cell.Value = stringMap["shippingPhoneNumber"]
cell = row.AddCell()
cell.Value = stringMap["email"]
nbEntries++
// if nbDisplayed >= 10 {
// break
// }
}
}
fileName := "/data/" + syncName + ".xlsx"
err = file.Save(fileName)
if err != nil {
logrus.Fatal(err)
}
logrus.Println(nbEntries, "entries written in", fileName)
logrus.Println("(" + strconv.Itoa(nbPaymentInvalid) + " payment invalid)")
logrus.Println("(" + strconv.Itoa(nbInvalid) + " invalid shipping addresses)")
}
//экспорт данных datas в файл xlsx используя сортировку keys - массив указывающий в каком порядке выводить в таблицу
func savetoxlsx0(namef string, datas map[string]DataTelMans, keys []string) {
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
file = xlsx.NewFile()
sheet, err = file.AddSheet("лог звонков")
if err != nil {
fmt.Println(err.Error())
}
//заголовок таблицы
row = sheet.AddRow() // добавить строку
cell = row.AddCell() // добавить ячейку в текущей строке
cell.Value = "выгружено: " + time.Now().String()
row = sheet.AddRow() // добавить строку
titletab := []string{"ФИО РГ",
"номер телефона",
"ФИО менеджера",
"всего продолжит-ть",
"всего кол-во звонков",
"кол-во уникальных телефонов",
"кол-во результ. звонков",
"продолжительность уникальных",
"средняя время звонка"}
for i := 0; i < len(titletab); i++ {
cell = row.AddCell() // добавить ячейку в текущей строке
cell.Value = titletab[i]
}
for i := 0; i < len(keys); i++ {
key := keys[i]
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = datas[key].fio_rg
cell = row.AddCell()
cell.Value = key
cell = row.AddCell()
cell.Value = datas[key].fio_man
cell = row.AddCell()
cell.Value = sec_to_s(datas[key].totalsec)
cell = row.AddCell()
cell.Value = strconv.Itoa(datas[key].totalzv)
cell = row.AddCell()
cell.Value = strconv.Itoa(datas[key].kolunik)
cell = row.AddCell()
cell.Value = strconv.Itoa(datas[key].kolresult)
cell = row.AddCell()
cell.Value = sec_to_s(datas[key].secresult)
cell = row.AddCell()
cell.Value = sec_to_s(devidezero(datas[key].totalsec, datas[key].totalzv))
}
err = file.Save(namef)
if err != nil {
fmt.Println(err.Error())
}
}
func (this *HistoryController) CreateExcel() {
path := beego.AppConfig.String("xlspath")
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
file = xlsx.NewFile()
sheet, err = file.AddSheet("Sheet1")
if err != nil {
beego.Error(err)
return
}
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = "客户"
cell = row.AddCell()
cell.Value = "订单号"
cell = row.AddCell()
cell.Value = "物料编码"
cell = row.AddCell()
cell.Value = "物料品名"
cell = row.AddCell()
cell.Value = "包装规格(kg/桶)"
cell = row.AddCell()
cell.Value = "日期"
cell = row.AddCell()
cell.Value = "数量"
cell = row.AddCell()
cell.Value = "单价"
cell = row.AddCell()
cell.Value = "金额"
cell = row.AddCell()
cell.Value = "备注"
for _, v := range Recode {
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = GetCustomerObj(v.ClientId).Name
cell = row.AddCell()
cell.Value = v.OrderId
product := GetProductObj(v.ProductId)
cell = row.AddCell()
cell.Value = product.PId
cell = row.AddCell()
cell.Value = product.PName
cell = row.AddCell()
cell.Value = product.PType
cell = row.AddCell()
cell.Value = FormatTime(v.CTime)
cell = row.AddCell()
cell.Value = fmt.Sprintf("%0.1f", v.Count)
cell = row.AddCell()
cell.Value = fmt.Sprintf("%0.4f", v.Price)
cell = row.AddCell()
cell.Value = fmt.Sprintf("%0.4f", v.Price*v.Count)
cell = row.AddCell()
cell.Value = v.Remark
}
now := time.Now().Format("2006-01-02_15-04")
err = file.Save(path + "myrecod " + now + ".xlsx")
if err != nil {
beego.Error(err)
return
}
this.Redirect("/history", 302)
}
func (this *DataBrowserController) genExcelFile(header []string, selectedColumn []string, datas []tk.M, dataSummary []tk.M, DisplaySumList []SumList) (string, error) {
today := time.Now().UTC()
fileName := "files/databrowser_" + today.Format("2006-01-02T150405") + ".xlsx"
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
file = xlsx.NewFile()
sheet, _ = file.AddSheet("Sheet1")
for i, data := range datas {
if i == 0 {
row = sheet.AddRow()
for _, hdr := range header {
cell = row.AddCell()
cell.Value = hdr
}
}
row = sheet.AddRow()
for _, field := range selectedColumn {
cell = row.AddCell()
cell.SetValue(this.getExcelValue(data, field))
}
}
if DisplaySumList != nil && len(DisplaySumList) > 0 {
var summary = dataSummary[0]
row = sheet.AddRow()
cell = row.AddCell()
cell.SetValue("Average")
cell.Merge(len(DisplaySumList)-1, 0)
row = sheet.AddRow()
for _, i := range DisplaySumList {
cell = row.AddCell()
if i.tipe == "string" || i.tipe == "date" {
cell.SetValue("-")
} else {
field := strings.Replace(i.field, ".", "", -1) + "avg"
cell.SetValue(summary.Get(field))
}
}
row = sheet.AddRow()
cell = row.AddCell()
cell.Merge(len(DisplaySumList)-1, 0)
cell.SetValue("Total")
row = sheet.AddRow()
for _, i := range DisplaySumList {
cell = row.AddCell()
if i.tipe == "string" || i.tipe == "date" {
cell.SetValue("-")
} else {
field := strings.Replace(i.field, ".", "", -1) + "sum"
cell.SetValue(summary.Get(field))
}
}
}
err = file.Save(fileName)
// file := xlsx.NewFile()
// sheet := file.AddSheet("Sheet1")
// // header := []string{"Matnr", "Matkl"}
// // for i, _ := range datas {
// // if i == 0 {
// // rowHeader := sheet.AddRow()
// // for _, hdr := range header {
// // cell := rowHeader.AddCell()
// // cell.Value = hdr
// // }
// // }
// // }
// err := file.Save(fileName)
return fileName, err
}
/************************ excel 输出 ***************************/
func init() {
Output["excel"] = func(self *Collector, dataIndex int) {
defer func() {
if err := recover(); err != nil {
logs.Log.Error("%v", err)
}
}()
var file *xlsx.File
var sheets = make(map[string]*xlsx.Sheet)
var row *xlsx.Row
var cell *xlsx.Cell
var err error
// 创建文件
file = xlsx.NewFile()
// 添加分类数据工作表
for _, datacell := range self.DockerQueue.Dockers[dataIndex] {
var subNamespace = util.FileNameReplace(self.subNamespace(datacell))
if _, ok := sheets[subNamespace]; !ok {
// 添加工作表
sheet, err := file.AddSheet(subNamespace)
if err != nil {
logs.Log.Error("%v", err)
continue
}
sheets[subNamespace] = sheet
// 写入表头
row = sheets[subNamespace].AddRow()
for _, title := range self.GetRule(datacell["RuleName"].(string)).GetOutFeild() {
cell = row.AddCell()
cell.Value = title
}
cell = row.AddCell()
cell.Value = "当前链接"
cell = row.AddCell()
cell.Value = "上级链接"
cell = row.AddCell()
cell.Value = "下载时间"
}
row = sheets[subNamespace].AddRow()
for _, title := range self.GetRule(datacell["RuleName"].(string)).GetOutFeild() {
cell = row.AddCell()
vd := datacell["Data"].(map[string]interface{})
if v, ok := vd[title].(string); ok || vd[title] == nil {
cell.Value = v
} else {
cell.Value = util.JsonString(vd[title])
}
}
cell = row.AddCell()
cell.Value = datacell["Url"].(string)
cell = row.AddCell()
cell.Value = datacell["ParentUrl"].(string)
cell = row.AddCell()
cell.Value = datacell["DownloadTime"].(string)
}
folder := config.COMM_PATH.TEXT + "/" + self.startTime.Format("2006年01月02日 15时04分05秒")
filename := fmt.Sprintf("%v/%v__%v-%v.xlsx", folder, util.FileNameReplace(self.namespace()), self.sum[0], self.sum[1])
// 创建/打开目录
f2, err := os.Stat(folder)
if err != nil || !f2.IsDir() {
if err := os.MkdirAll(folder, 0777); err != nil {
logs.Log.Error("Error: %v\n", err)
}
}
// 保存文件
err = file.Save(filename)
if err != nil {
logs.Log.Error("%v", err)
}
}
}
func writesheet(project *lair.Project, outfile string) {
header := []string{
"#",
"Title",
"CVSS",
"Rating",
"Description",
"Evidence",
"Solution",
"CVEs",
"References",
"Host",
"Hostname(s)",
"Port",
"Service Note",
"Issue Note(s)",
}
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
file = xlsx.NewFile()
sheet, err := file.AddSheet(project.Name)
if err != nil {
log.Printf(err.Error())
}
row = sheet.AddRow()
for _, h := range header {
cell = row.AddCell()
cell.Value = h
}
for count, issue := range project.Issues {
var issuenote string
for _, note := range issue.Notes {
issuenote += note.Title
issuenote += note.Content + "\n"
}
for _, host := range issue.Hosts {
var refs []string
for _, ref := range issue.References {
refs = append(refs, ref.Link)
}
row = sheet.AddRow()
cell = row.AddCell()
cell.SetInt(count + 1)
cell = row.AddCell()
cell.Value = issue.Title
cell = row.AddCell()
cell.SetFloat(issue.CVSS)
cell = row.AddCell()
cell.Value = issue.Rating
cell = row.AddCell()
cell.Value = issue.Description
cell = row.AddCell()
cell.Value = issue.Evidence
cell = row.AddCell()
cell.Value = issue.Solution
cell = row.AddCell()
cell.Value = strings.Join(issue.CVEs, "\n")
cell = row.AddCell()
cell.Value = strings.Join(refs, "\n")
cell = row.AddCell()
cell.Value = host.IPv4
cell = row.AddCell()
cell.Value = gethostname(host.IPv4, &project.Hosts)
cell = row.AddCell()
cell.SetInt(host.Port)
cell = row.AddCell()
cell.Value = getcomment(&project.Hosts, issue.Title, host.IPv4, host.Port)
cell = row.AddCell()
cell.Value = issuenote
}
}
err = file.Save(outfile)
if err != nil {
log.Fatal("Fatal: Unable to write file")
}
}
func EnsureStatistic(toPath string) error {
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
file = xlsx.NewFile()
type Source struct {
Name string `bson:"_id"`
}
sources := []Source{}
dbsWithOrders := getDbsWithOrders()
log.Printf("Databases with orders is: %+v", dbsWithOrders)
for _, dbName := range dbsWithOrders {
db := d.NewMainDb(config.Main.Database.ConnString, dbName)
sheet, err = file.AddSheet(dbName)
if err != nil {
log.Printf("Error at edding sheet to file")
continue
}
db.Orders.Collection.Pipe(
[]bson.M{bson.M{"$group": bson.M{"_id": "$source"}}},
).All(&sources)
for _, source := range sources {
row = sheet.AddRow()
cell = row.AddCell()
cell.Value = source.Name
row = sheet.AddRow()
for _, h_cell := range []string{"Телефон", "Статус", "Дата", "Стоимость", "Адрес подачи", "Адрес назначения", "Позывной автомобиля", "ФИО водителя"} {
cell = row.AddCell()
cell.Value = h_cell
}
orders, err := db.Orders.GetBy(bson.M{"source": source.Name})
if err != nil {
log.Printf("Error at getting orders from %+v is: %v", config.Main.Database, err)
return err
}
for _, order := range orders {
log.Printf("adding row for order %+v", order)
user, u_err := db.Users.GetUserById(order.Whom)
if u_err != nil || user == nil {
log.Printf("No user found at id: %v", order.Whom)
continue
}
row = sheet.AddRow()
ph_c := row.AddCell()
ph_c.SetString(user.Phone)
stat_c := row.AddCell()
if state, ok := t.InfinityStatusesName[order.OrderState]; ok {
stat_c.SetString(state)
} else {
stat_c.SetString("Не определен")
}
time_c := row.AddCell()
time_c.SetDateTime(order.When)
if len(order.OrderData.Content) > 0 {
log.Printf("we have additional data of order %v", order.OrderId)
cost := order.OrderData.Get("Cost")
if cost != nil {
cost_c := row.AddCell()
cost_c.SetInt(order.OrderData.Get("Cost").(int))
deliv_c := row.AddCell()
deliv_c.SetString(order.OrderData.Get("DeliveryStr").(string))
dest_c := row.AddCell()
dest_c.SetString(order.OrderData.Get("DestinationsStr").(string))
car_c := row.AddCell()
car_c.SetString(order.OrderData.Get("Car").(string))
driver_c := row.AddCell()
driver_c.SetString(order.OrderData.Get("Drivers").(string))
}
}
log.Printf("Added row: %+v", row)
}
}
}
fName := fmt.Sprintf("%v/statistic.xlsx", toPath)
file.Save(fName)
return nil
}
/************************ excel 输出 ***************************/
func init() {
Output["excel"] = func(self *Collector, dataIndex int) {
defer func() {
if err := recover(); err != nil {
Log.Println(err)
}
}()
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
folder1 := "result/data"
folder2 := folder1 + "/" + self.startTime.Format("2006年01月02日 15时04分05秒")
filename := folder2 + "/" + util.FileNameReplace(self.Spider.GetName()+"_"+self.Spider.GetKeyword()+" "+fmt.Sprintf("%v", self.sum[0])+"-"+fmt.Sprintf("%v", self.sum[1])) + ".xlsx"
// 创建文件
file = xlsx.NewFile()
// 添加分类数据工作表
for Name, Rule := range self.GetRules() {
// 跳过不输出的数据
if len(Rule.GetOutFeild()) == 0 {
continue
}
// 添加工作表
sheet = file.AddSheet(util.ExcelSheetNameReplace(Name))
// 写入表头
row = sheet.AddRow()
for _, title := range Rule.GetOutFeild() {
cell = row.AddCell()
cell.Value = title
}
cell = row.AddCell()
cell.Value = "当前链接"
cell = row.AddCell()
cell.Value = "上级链接"
cell = row.AddCell()
cell.Value = "下载时间"
num := 0 //小计
for _, datacell := range self.DockerQueue.Dockers[dataIndex] {
if datacell["RuleName"].(string) == Name {
row = sheet.AddRow()
for _, title := range Rule.GetOutFeild() {
cell = row.AddCell()
vd := datacell["Data"].(map[string]interface{})
if v, ok := vd[title].(string); ok || vd[title] == nil {
cell.Value = v
} else {
cell.Value = util.JsonString(vd[title])
}
}
cell = row.AddCell()
cell.Value = datacell["Url"].(string)
cell = row.AddCell()
cell.Value = datacell["ParentUrl"].(string)
cell = row.AddCell()
cell.Value = datacell["DownloadTime"].(string)
num++
}
}
// Log.Printf("[任务：%v | 关键词：%v | 小类：%v] 输出 %v 条数据！！！\n", self.Spider.GetName(), self.Spider.GetKeyword(), Name, num)
}
// 创建/打开目录
f2, err := os.Stat(folder2)
if err != nil || !f2.IsDir() {
if err := os.MkdirAll(folder2, 0777); err != nil {
Log.Printf("Error: %v\n", err)
}
}
// 保存文件
err = file.Save(filename)
if err != nil {
Log.Println(err)
}
}
}
func Unload(p Panel, fname string) { // ADD ERROR
// p.Clean()
switch path.Ext(fname) {
case ".csv":
// Convert to CSV-like structure
width := p.Size().Width
length := p.Size().Length
headers := make(map[string]int)
colnum := 0
for head, _ := range p {
headers[head] = colnum
colnum += 1
}
records := [][]string{}
record := make([]string, width)
for head, _ := range p {
record[headers[head]] = head
}
records = append(records, record)
for i := 0; i < length; i++ {
record := make([]string, width)
for head, col := range p {
var val string
switch t := col[i].(type) {
case time.Time:
d110, _ := dateFormat(110)
tt := t.Format(d110)
fmt.Println(tt)
val = tt
default:
val = fmt.Sprintf("%v", t)
}
record[headers[head]] = val
}
records = append(records, record)
}
// fmt.Println(records)
// Write to file
file := new(os.File)
if _, err := os.Stat(fname); err == nil {
file, _ = os.OpenFile(fname, os.O_RDWR|os.O_APPEND, 0777)
} else {
file, _ = os.Create(fname)
}
w := csv.NewWriter(file)
// w.WriteAll(records)
for _, record := range records {
if err := w.Write(record); err != nil {
log.Fatalln("error writing record to csv:", err)
}
}
w.Flush() // Write any buffered data to the underlying writer
if err := w.Error(); err != nil {
log.Fatal(err)
}
case ".xml":
case ".tsv":
case ".json":
export := []map[string]interface{}{}
// width := p.Size().Width
length := p.Size().Length
for i := 0; i < length; i++ {
row := make(map[string]interface{})
for header, series := range p {
row[header] = series[i]
}
export = append(export, row)
}
j, _ := json.Marshal(export)
file := new(os.File)
if _, err := os.Stat(fname); err == nil {
file, _ = os.OpenFile(fname, os.O_RDWR|os.O_APPEND, 0777)
} else {
file, _ = os.Create(fname)
}
_, err := file.Write(j)
if err != nil {
fmt.Println(err)
}
file.Close()
case ".xlsx", ".xls":
// Convert to CSV-like structure
width := p.Size().Width
length := p.Size().Length
headers := make(map[string]int)
colnum := 0
for head, _ := range p {
headers[head] = colnum
colnum += 1
}
records := [][]string{}
record := make([]string, width)
for head, _ := range p {
record[headers[head]] = head
}
records = append(records, record)
for i := 0; i < length; i++ {
record := make([]string, width)
for head, col := range p {
var val string
switch t := col[i].(type) {
case time.Time:
d110, _ := dateFormat(110)
tt := t.Format(d110)
fmt.Println(tt)
val = tt
default:
val = fmt.Sprintf("%v", t)
}
record[headers[head]] = val
}
records = append(records, record)
}
// fmt.Println(records)
// Write to file
// file := new(os.File)
// if _, err := os.Stat(fname); err == nil {
// file, _ = os.OpenFile(fname, os.O_RDWR|os.O_APPEND, 0777)
// } else {
// file, _ = os.Create(fname)
// }
var excel *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
excel = xlsx.NewFile()
sheet, err = excel.AddSheet("Sheet1")
if err != nil {
fmt.Printf(err.Error())
}
for _, rec := range records {
row = sheet.AddRow()
for _, field := range rec {
cell = row.AddCell()
cell.Value = fmt.Sprintf("%s", field)
}
}
err = excel.Save(fname)
if err != nil {
fmt.Printf(err.Error())
log.Fatalln("error writing record to xlsx:", err)
}
default:
file := new(os.File)
if _, err := os.Stat(fname); err == nil {
file, _ = os.OpenFile(fname, os.O_RDWR|os.O_APPEND, 0777)
} else {
file, _ = os.Create(fname)
}
file.Write([]byte(fmt.Sprintf("%s", p)))
}
}