Example #1
1
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)
	}
}
/*-----------------------------------------------------------------------------
-- FUNCTION:    generateHeaders
--
-- DATE:        February 13, 2016
--
-- REVISIONS:	 (DATE AND INFO)
--
-- DESIGNER:		Marc Vouve
--
-- PROGRAMMER:	Marc Vouve
--
-- INTERFACE:		generateHeaders(i interface{}, row *xlsx.Row)
--         i:   the interface to generate headers from.
--       row:   a pointer for i's field names to be written to.
--
-- RETURNS: 		void
--
-- NOTES:			This function uses reflect to get the name of the element and print
--            the name of the element to a row.
------------------------------------------------------------------------------*/
func generateHeaders(i interface{}, row *xlsx.Row) {
	fields := reflect.ValueOf(i)
	for i := 0; i < fields.NumField(); i++ {
		cell := row.AddCell()
		cell.SetString(fields.Type().Field(i).Name)
	}
}
Example #3
0
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())
	}
}
Example #4
0
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
}
/*-----------------------------------------------------------------------------
-- FUNCTION:    Generate Report
--
-- DATE:        February 13, 2016
--
-- REVISIONS:	 (DATE AND INFO)
--
-- DESIGNER:		Marc Vouve
--
-- PROGRAMMER:	Marc Vouve
--
-- INTERFACE:		func generateRow(i interface{}, row *xlsx.Row)
--         i:   the interface to generate headers from.
--       row:   a pointer to a row from a spreadsheet for the i to be printed on.
--
-- RETURNS: 		void
--
-- NOTES:			This function uses reflect to get the name of the element and print
--            the name of the element to a row.
------------------------------------------------------------------------------*/
func generateRow(i interface{}, row *xlsx.Row) {
	fields := reflect.ValueOf(i)
	for i := 0; i < fields.NumField(); i++ {
		cell := row.AddCell()
		cell.SetValue(fields.Field(i).Interface())
	}
}
Example #6
0
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())
	}
}
Example #7
0
// 写每一个*.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
}
Example #8
0
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())
	}
}
Example #9
0
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())
	}
}
Example #10
0
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())
	}
}
Example #11
0
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())
	}
}
Example #12
0
//WriteToRow write content to a excel row
func (checkResult *CheckResult) WriteToRow(row *xlsx.Row) {
	value := reflect.ValueOf(*checkResult)
	for i := 0; i < value.NumField(); i++ {
		oneField := value.Field(i)
		cell := row.AddCell()
		if oneField.Type().Name() == "string" {
			cell.SetString(oneField.String())
		} else {
			fieldContent := oneField.Int()
			if fieldContent == 2 {
				// style := xlsx.NewStyle()
				// font := xlsx.DefaultFont()
				cell.GetStyle().Font.Color = "FFFF0000"
				cell.SetBool(fieldContent == 1)
			} else if fieldContent == 1 {
				cell.GetStyle().Font.Color = "FF00FF00"
				cell.SetBool(fieldContent == 1)
			}
		}
	}
}
Example #13
0
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
}
Example #14
0
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)
}
Example #15
0
func ExportReservationsToExcel(reservations []*models.Reservation, filename string) error {
	xl, err := xlsx.OpenFile(ExportFolder + DefaultReservationExportExcelFilename)
	if err != nil {
		return errors.New("导出失败:打开模板文件失败")
	}
	sheet := xl.Sheet["export"]
	if sheet == nil {
		return errors.New("导出失败:打开工作表失败")
	}
	var row *xlsx.Row
	var cell *xlsx.Cell
	for _, reservation := range reservations {
		row = sheet.AddRow()
		// 学生申请表
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.Name)
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.Gender)
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.StudentId)
		cell = row.AddCell()
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.School)
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.Hometown)
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.Mobile)
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.Email)
		cell = row.AddCell()
		cell = row.AddCell()
		cell.SetString(reservation.StudentInfo.Problem)
		// 预约信息
		cell = row.AddCell()
		cell.SetString(reservation.TeacherFullname)
		cell = row.AddCell()
		cell.SetString(reservation.StartTime.Format(DATE_PATTERN))
		// 咨询师反馈表
		cell = row.AddCell()
		cell = row.AddCell()
		cell = row.AddCell()
		cell.SetString(reservation.TeacherFeedback.Problem)
		cell = row.AddCell()
		cell.SetString(reservation.TeacherFeedback.Solution)
		// 学生反馈表
		cell = row.AddCell()
		cell = row.AddCell()
		cell.SetString(reservation.StudentFeedback.Score)
		cell = row.AddCell()
		cell.SetString(reservation.StudentFeedback.Feedback)
		if !strings.EqualFold(reservation.StudentFeedback.Choices, "") {
			for i := 0; i < len(reservation.StudentFeedback.Choices); i++ {
				cell = row.AddCell()
				switch reservation.StudentFeedback.Choices[i] {
				case 'A':
					cell.SetString("非常同意")
				case 'B':
					cell.SetString("一般")
				case 'C':
					cell.SetString("不同意")
				default:
				}
			}
		}
	}
	err = xl.Save(ExportFolder + filename)
	if err != nil {
		return errors.New("导出失败:保存文件失败")
	}
	return nil
}
Example #16
0
//экспорт данных 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())
	}
}
Example #17
0
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
	}
}
Example #18
0
/************************ 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)
	}

}
Example #19
0
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)
		}
	}
}
Example #20
0
func main() {
	var examFiles []string
	var scoreFile *xlsx.File
	var row *xlsx.Row
	files, err := ioutil.ReadDir(fexamDir)
	if err != nil {
		log.Println("Error reading fexam directory!")
		os.Exit(1)
	}
	for _, file := range files {
		filename := file.Name()
		if strings.HasSuffix(filename, ".xlsx") {
			examFiles = append(examFiles, fexamDir+filename)
		}
	}
	//create score xlsx file to save student score
	scoreFile = xlsx.NewFile()
	scoreSheet, err := scoreFile.AddSheet("Sheet1")
	if err != nil {
		log.Println(err.Error())
	}
	//loop student exam xlsx file ,read sheet1 col2 to judge
	startTime := time.Now().UnixNano()
	for _, examFile := range examFiles {
		score := 0
		xlFile, error := xlsx.OpenFile(examFile) //open excel file
		if error != nil {
			log.Println("Error reading examfile")
		}
		//get student's name,no,class
		//example:-Unlicensed-13715050_袁慧敏_13医器_java.xlsx
		s := strings.Split(examFile[20:strings.Index(examFile, ".xlsx")], "_")
		stuNo := s[0]
		stuName := s[1]
		stuClass := s[2]
		sheet := xlFile.Sheets[0]
		var stuAnswer []string
		for _, row := range sheet.Rows {
			if row != nil {
				cell := row.Cells[2]
				stuAnswer = append(stuAnswer, fmt.Sprintf("%s", cell.String()))
			}
		}
		fmt.Println(stuAnswer)
		//get the choice right number
		rightChoiceNum := right(stuAnswer[1:21], choiceAnswer)
		//get the blank right number
		rightBlankNum := right(stuAnswer[22:27], blankAnswer)
		score = rightChoiceNum*3 + rightBlankNum*5
		fmt.Println(score)
		row = scoreSheet.AddRow()
		//add student no
		cellstuno := row.AddCell()
		cellstuno.Value = stuNo
		//add student name
		cellname := row.AddCell()
		cellname.Value = stuName
		//add student class
		cellclass := row.AddCell()
		cellclass.Value = stuClass
		//add student score
		cellscore := row.AddCell()
		cellscore.Value = strconv.Itoa(score)
	}
	err = scoreFile.Save("./score/score.xlsx")
	endTime := time.Now().UnixNano()
	log.Printf("试卷批改完成 共加载:%d 条记录, 所花时间:%.1f ms\n", len(examFiles), float64(endTime-startTime)/1000000)
	if err != nil {
		log.Println(err.Error())
	}

}
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
}
Example #22
0
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)")
}
Example #23
0
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 + "\",\"明細\")")
	}
}
Example #24
0
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個字"
}
Example #25
0
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")
}
Example #26
0
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
}
Example #27
0
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")
	}
}
Example #28
0
/************************ 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)
		}
	}
}
Example #29
0
/************************ 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)
		}

	}
}
Example #30
0
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)))
	}
}