golang 数据导出excel (github.com/360EntSecGroup-Skylar/excelize)
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
}
