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 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 getHeader(sheet *xlsx.Sheet) (*tool.ExportHeaderV1, error) {
headerString := strings.TrimSpace(sheet.Cell(0, 0).Value)
// 可能是空的sheet
if headerString == "" {
return nil, nil
}
var header tool.ExportHeaderV1
// 有可能的字符,一定是头
if strings.Contains(headerString, "ProtoTypeName") ||
strings.Contains(headerString, "RowFieldName") {
if err := proto.UnmarshalText(headerString, &header); err != nil {
return nil, err
}
} else {
// 有字符, 但并不是头
return nil, nil
}
return &header, nil
}
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
}
// 写每一个*.dat数据
func write(infile string, sheet *xlsx.Sheet, rawNbr int) {
fmt.Println("正在处理", infile)
var id int
start := strings.LastIndexAny(infile, "\\")
id, _ = strconv.Atoi(infile[start+1 : len(infile)-4])
fmt.Println(id)
var cell *xlsx.Cell
var row *xlsx.Row
file, err := os.Open(infile)
if err != nil {
fmt.Println("Failed to open the input file ", infile)
return
}
defer file.Close()
br := bufio.NewReader(file)
var indexi int = 0
_, _, _ = br.ReadLine()
for {
line, isPrefix, err1 := br.ReadLine()
if err1 != nil {
if err1 != io.EOF {
err = err1
}
break
}
if isPrefix {
fmt.Println("A too long line, seems unexpected.")
return
}
str := string(line) // Convert []byte to string
if len(str) < 5 {
break
}
split := str[1 : len(str)-1]
array := strings.Split(split, ",")
row = sheet.AddRow()
cell = row.AddCell()
cell.SetInt(id)
indexi = 0
for _, v := range array {
if indexi >= rawNbr-1 {
break
}
indexi = indexi + 1
cell = row.AddCell()
value, _ := strconv.Atoi(v)
cell.SetInt(value)
}
}
return
}
// Xlsx writes the day statistics in xlsx format
func (fsd *FlightStatDay) Xlsx(s *xlsx.Sheet) {
r := s.AddRow()
c := r.AddCell()
c.SetDate(fsd.Date)
c.NumFmt = "dd.mm.yyyy"
r.AddCell().SetInt(fsd.Flights)
r.AddCell().SetFloatWithFormat(fsd.Airtime.Minutes(), "0.00")
}
func addRow(sheet *xlsx.Sheet, headers ...string) {
row := sheet.AddRow()
for _, header := range headers {
cell := row.AddCell()
cell.Value = header
}
}
//AddCell sheet
func AddCell(sheet *xlsx.Sheet, row, col int) *xlsx.Cell {
// hhh
for row >= len(sheet.Rows) {
sheet.AddRow()
}
for col >= len(sheet.Rows[row].Cells) {
sheet.Rows[row].AddCell()
}
return sheet.Cell(row, col)
}
func getHeader(sheet *xlsx.Sheet) *tool.ExportHeader {
headerString := sheet.Cell(0, 0).Value
var header tool.ExportHeader
if err := proto.UnmarshalText(headerString, &header); err != nil {
return nil
}
return &header
}
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 (s *UtilTestSuite) TestCheckGetHeader(c *C) {
GetCheckResultHeader()
checkResult := CheckResult{}
var file *xlsx.File
var sheet *xlsx.Sheet
file = xlsx.NewFile()
sheet, _ = file.AddSheet("Sheet1")
sheet.AddRow()
checkResult.WriteToRow(sheet.AddRow())
c.Log(checkResult)
}
// Xlsx calls each day and then writes the month statistics in xlsx format
func (fsm *FlightStatMonth) Xlsx(s *xlsx.Sheet) {
days := []int{}
for d, _ := range fsm.Day {
days = append(days, d)
}
sort.Ints(days)
for _, d := range days {
fsd := fsm.Day[d]
fsd.Xlsx(s)
}
r := s.AddRow()
r.AddCell().SetString(fmt.Sprintf("Total %s", fsm.Date.Format("January 2006")))
r.AddCell().SetInt(fsm.Flights)
r.AddCell().SetFloatWithFormat(fsm.Airtime.Minutes(), "0.00")
}
// Xlsx calls each month and then writes the year statistics in xlsx format
func (fsy *FlightStatYear) Xlsx(s *xlsx.Sheet) {
month := []int{}
for m, _ := range fsy.Month {
month = append(month, int(m))
}
sort.Ints(month)
for _, m := range month {
fsm := fsy.Month[time.Month(m)]
fsm.Xlsx(s)
}
r := s.AddRow()
r.AddCell().SetString(fmt.Sprintf("Total %d", fsy.Year.Year()))
r.AddCell().SetInt(fsy.Flights)
r.AddCell().SetFloatWithFormat(fsy.Airtime.Minutes(), "0.00")
}
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 + "\",\"明細\")")
}
}
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 subjectGrid(sheet *xlsx.Sheet, g group.Group, subj *subject.Subject, natYear string) {
pg := g.ProgressGrid(subj, natYear)
row := sheet.AddRow()
row = sheet.AddRow()
blankCell(row)
newCell(row, "Progress Grid: "+subj.Subj, newStyle("Title", "None", "None", "Left"))
row = sheet.AddRow()
row = sheet.AddRow()
blankCell(row)
newCell(row, "KS2", newStyle("Bold", "None", "Bottom", "Left"))
for _, grd := range pg.Grades {
newCell(row, grd, newStyle("Bold", "None", "Bottom", "Center"))
}
newCell(row, "VA", newStyle("Bold", "None", "Bottom", "Center"))
for i, ks2 := range pg.KS2 {
row := sheet.AddRow()
blankCell(row)
newCell(row, ks2, newStyle("Bold", "None", "None", "Left"))
for j := range pg.Grades {
pgCell := pg.Cells[i][j]
switch {
case pg.CellVA[i][j] < -0.33:
newInt(row, len(pgCell.Students), newStyle("Default", "Red", "None", "Center"))
case pg.CellVA[i][j] > 0.67:
newInt(row, len(pgCell.Students), newStyle("Default", "Green", "None", "Center"))
default:
newInt(row, len(pgCell.Students), newStyle("Default", "Yellow", "None", "Center"))
}
}
newFloat(row, pg.RowVA[i], "+0.00;-0.00;0.00", newStyle("Default", "None", "None", "Center"))
}
row = sheet.AddRow()
blankCell(row)
newCell(row, "Total", newStyle("Bold", "None", "None", "None"))
for i := range pg.Grades {
newInt(row, pg.Counts[i], newStyle("Bold", "None", "None", "Center"))
}
newFloat(row, g.SubjectVA(subj.Subj).VA, "+0.00;-0.00;0.00", newStyle("Bold", "None", "None", "Center"))
}
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())
}
}
//экспорт данных 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 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 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 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 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 (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 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
}
/************************ 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 (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
}
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")
}
}
/************************ 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 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")
}
