- package handler
- import (
- "fmt"
- "git.shannonai.com/public_info_prophet/prophet_risk_aggregation/model"
- "github.com/360EntSecGroup-Skylar/excelize"
- "github.com/gin-gonic/gin"
- "go.uber.org/zap"
- "net/http"
- "os"
- "path/filepath"
- "strconv"
- "time"
- )
- // NewRequest 写入 Excel POST 请求结构体
- type NewRequest struct {
- StartDate string `json:"beginDate" binding:"required"`
- EndDate string `json:"endDate" binding:"required"`
- FilePath string `json:"filePath"`
- }
- // NewExcel POST
- func NewExcel(c *gin.Context) {
- logger := c.MustGet("logger").(*zap.SugaredLogger)
- var request NewRequest
- if err := c.BindJSON(&request); err != nil {
- logger.Error("解析 json 失败原因,", err.Error())
- c.JSON(http.StatusBadRequest, gin.H{
- "status": "fail",
- "message": "json 解析失败",
- "error": err.Error(),
- })
- return
- }
- startDate, _ := time.Parse("2006-01-02 15:04:05", request.StartDate)
- endDate, _ := time.Parse("2006-01-02 15:04:05", request.EndDate)
- filePath := "C:/Users/dell/Documents/prophet_risk_aggregation"
- path := CreateDateDir(startDate, filePath)
- // 数据库查询数据
- t1 := time.Now()
- finNewsOnlineDAO := model.FinNewsOnlineDAO{}
- var news []model.FinNewsOnline
- fmt.Println(startDate, endDate)
- var err error
- news, err = finNewsOnlineDAO.FindNews(startDate, endDate)
- newsCount := len(news)
- fmt.Println("查询新闻数量:", newsCount)
- count := newsCount/500 + 1
- fmt.Println("要分为", count, "个 excel 表格")
- if err != nil {
- logger.Error("数据库查询错误")
- c.Status(500)
- return
- }
- t2 := time.Now()
- wastingTime := t2.Sub(t1)
- fmt.Println("耗时:", wastingTime)
- for i := 0; i <count; i++ {
- newsList := listPaging(news, i+1, 500)
- xlsxNews := excelize.NewFile()
- var starPushDate time.Time
- var endPushDate time.Time
- //var count int
- count := len(newsList)
- c := strconv.Itoa(count + 1)
- for index, news := range newsList {
- if index == 0 {
- xlsxNews.SetSheetRow("Sheet1", "A1", &[]interface{}{
- "NEWS_ID", "DUP_ID", "PUBLISH_DATE",
- "TITLE", "ORIGIN_TITLE", "CONTENT", "SOURCE", "URL", "SENTIMENT", "ORIGIN",
- "SHANNON_ID", "HASH_ID", "INSERT_TIME",
- "ORIGINAL_INSERT_TIME", "CONTENT_SIMHASH",
- })
- }
- lint := strconv.Itoa(index + 2)
- if lint == "2" {
- starPushDate = news.PublishDate
- }
- if lint == c {
- endPushDate = news.PublishDate
- }
- xlsxNews.SetSheetRow("Sheet1", "A"+lint, &[]interface{}{
- news.NewsID, news.DupID, news.PublishDate.Format("2006-01-02 15:04:05"),
- news.Title, news.OriginTitle, news.Content, news.Source, news.URL, news.Sentiment, news.Origin,
- news.ShannonID, news.HashID, news.InsertTime.Format("2006-01-02 15:04:05"),
- news.OriginalInsertTime.Format("2006-01-02 15:04:05"), news.ContentSimhash,
- })
- }
- // 四位字符串右对齐填充 0
- fileSerialNumber := fmt.Sprintf("%04d", i)
- _= xlsxNews.SaveAs(path + "./" + fileSerialNumber + "_news.xlsx")
- fmt.Println(fileSerialNumber + "_news.xlsx>>>>> 写入成功")
- var newsRisk []model.NewsRisk
- riskRecordDAO := model.RiskRecordDAO{}
- newsRisk, err = riskRecordDAO.FindNewsRisk(starPushDate, endPushDate)
- newsRiskCount := len(newsRisk)
- fmt.Println("查询新闻风险数量:", newsRiskCount)
- xlsxRisk := excelize.NewFile()
- for index, risk := range newsRisk {
- if index == 0 {
- // 如果为 0 写入新的 Excel 第一行为字段名称
- xlsxRisk.SetSheetRow("Sheet1", "A1", &[]interface{}{
- "OBJECT_ID", "NEWS_ID", "ORIGIN", "SHANNON_ID",
- "PUBLISH_DATE", "NEWS_SENTIMENT", "NEWS_SENTIMENT_SCORE",
- "COMPANY_NAME", "COMPANY_CODE", "IS_MAJOR_COMPANY",
- "ENTITY_TYPE", "COMPANY_WEIGHT", "COMPANY_SENTIMENT",
- "SHANNON_RISK_TYPE", "SHANNON_SENTIMENT_TYPE", "SHANNON_RISK_WEIGHT",
- "SHANNON_RISK_CONFIDENCE", "HINT", "VERSION_ID", "INSERT_TIME",
- "IN_TITLE", "HINT_COUNT",
- })
- }
- // 因为 index 是从 0 开始, 第一行被字段占用, 从第二行开始写入整行数据
- lint := strconv.Itoa(index + 2)
- xlsxRisk.SetSheetRow("Sheet1", "A"+lint, &[]interface{}{
- risk.ObjectID, risk.NewsID, risk.ORIGIN, risk.ShannonID,
- risk.PublishDate.Format("2006-01-02 15:04:05"),
- risk.NewsSentiment, risk.NewsSentimentScore, risk.CompanyName,
- risk.CompanyCode, risk.IsMajorCompany, risk.EntityType,
- risk.CompanyWeight, risk.CompanySentiment, risk.ShannonRiskType,
- risk.ShannonSentimentType, risk.ShannonRiskWeight, risk.ShannonRiskConfidence,
- risk.Hint, risk.VersionID, risk.InsertTime.Format("2006-01-02 15:04:05"),
- risk.InTitle, risk.HintCount,
- })
- }
- _ = xlsxRisk.SaveAs(path + "./" + fileSerialNumber + "_risk.xlsx")
- fmt.Println(fileSerialNumber + "_risk.xlsx>>>>> 写入成功?")
- }
- c.JSON(http.StatusOK, gin.H{
- "status": "successful",
- })
- }
- // listPaging 列表分页
- func listPaging(newsList []model.FinNewsOnline, pageNum, pageSize int) (resNewsList []model.FinNewsOnline) {
- offset, limit := GetOffsetAndLimit(pageNum, pageSize, len(newsList))
- selectedNewsList := newsList[offset : offset+limit]
- for i := 0; i <len(selectedNewsList); i++ {
- item := model.FinNewsOnline{
- NewsID: selectedNewsList[i].NewsID,
- DupID: selectedNewsList[i].DupID,
- PublishDate: selectedNewsList[i].PublishDate,
- Title: selectedNewsList[i].Title,
- OriginTitle: selectedNewsList[i].OriginTitle,
- Content: selectedNewsList[i].Content,
- Source: selectedNewsList[i].Source,
- URL: selectedNewsList[i].URL,
- Sentiment: selectedNewsList[i].Sentiment,
- Origin: selectedNewsList[i].Origin,
- ShannonID: selectedNewsList[i].ShannonID,
- HashID: selectedNewsList[i].HashID,
- InsertTime: selectedNewsList[i].InsertTime,
- OriginalInsertTime: selectedNewsList[i].OriginalInsertTime,
- ContentSimhash: selectedNewsList[i].ContentSimhash,
- }
- resNewsList = append(resNewsList, item)
- }
- return resNewsList
- }
- // GetOffsetAndLimit func
- func GetOffsetAndLimit(pageNum, pageSize, totalNum int) (offset, limit int) {
- offset = (pageNum - 1) * pageSize
- if (pageNum-1)*pageSize>= totalNum {
- return 0, 0
- } else if pageNum*pageSize> totalNum && (pageNum-1)*pageSize < totalNum {
- limit = totalNum - (pageNum-1)*pageSize
- } else {
- limit = pageSize
- }
- return offset, limit
- }
- // CreateDateDir 根据当前日期来创建文件夹
- func CreateDateDir(date time.Time, Path string) string {
- folderName := date.Format("20060102")
- folderPath := filepath.Join(Path, folderName)
- if _, err := os.Stat(folderPath); os.IsNotExist(err) {
- // 先创建文件夹, 再修改权限
- _ = os.Mkdir(folderPath, 777)
- _ = os.Chmod(folderPath, 777)
- }
- return folderPath
- }
来源: http://www.bubuko.com/infodetail-3329678.html