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 createDonation() {
var currentYear = 0
var currentMonth = 0
r := helpcase.GetAllHelpcase()
var file *xlsx.File
var sheet1 *xlsx.Sheet
log.Println("donation export begin")
for _, hp := range r {
dt := helpcase.GetAllDonationDetail(hp.SerialNo)
if len(dt) == 0 {
continue
}
test, _ := time.Parse("2006/1/2", hp.Date)
var isCurrent = isCurrentYearMonthMatch(currentYear, currentMonth, test.Year(), int(test.Month()))
if !isCurrent {
if file != nil {
var err error
if currentMonth < 10 {
err = file.Save(outfolder + "/donation_" + strconv.Itoa(currentYear) + "0" + strconv.Itoa(currentMonth) + ".xlsx")
} else {
err = file.Save(outfolder + "/donation_" + strconv.Itoa(currentYear) + strconv.Itoa(currentMonth) + ".xlsx")
}
if err != nil {
fmt.Printf(err.Error())
}
}
file = xlsx.NewFile()
currentYear = test.Year()
currentMonth = int(test.Month())
}
sheet1 = file.AddSheet(hp.SerialNo)
var publishDate, _ = time.Parse("2006/1/2", hp.Date)
var cell *xlsx.Cell
var row *xlsx.Row
addHeader(sheet1)
for _, donator := range dt {
row = sheet1.AddRow()
cell = row.AddCell()
cell.SetString(donator.SerialNo)
cell = row.AddCell()
cell.Value = donator.Name
cell = row.AddCell()
cell.SetInt(donator.Amount)
cell.NumFmt = "#,##0 ;(#,##0)"
cell = row.AddCell()
cell.Value = donator.Date
cell = row.AddCell()
cell.SetFormula("weekday(\"" + donator.Date + "\",2)")
var dDate, _ = time.Parse("2006/1/2", donator.Date)
duration := dDate.Sub(publishDate)
cell = row.AddCell()
cell.Value = strconv.Itoa(int(duration.Hours() / 24))
cell = row.AddCell()
if donator.LongFour == 1 {
cell.Value = "YES"
} else {
cell.Value = "NO"
}
}
row = sheet1.AddRow()
row.AddCell()
row = sheet1.AddRow()
cell = row.AddCell()
cell.Value = "捐款頁面的URL"
cell = row.AddCell()
cell.SetFormula("HYPERLINK(\"http://search.appledaily.com.tw/charity/projdetail/proj/" + hp.SerialNo + "\",\"http://search.appledaily.com.tw/charity/projdetail/proj/" + hp.SerialNo + "\")")
row = sheet1.AddRow()
cell = row.AddCell()
cell.Value = "出刊日期"
cell = row.AddCell()
cell.Value = hp.Date
cell = row.AddCell()
cell.Value = "專案狀況"
cell = row.AddCell()
cell.Value = hp.Status
row = sheet1.AddRow()
cell = row.AddCell()
cell.Value = "捐款總計"
cell = row.AddCell()
cell.SetInt(hp.Amount)
cell.NumFmt = "#,##0 ;(#,##0)"
cell = row.AddCell()
cell.Value = "捐款筆數"
cell = row.AddCell()
cell.Value = strconv.Itoa(len(dt)) + "筆"
}
if file != nil {
var err error
if currentMonth < 10 {
err = file.Save(outfolder + "/donation_" + strconv.Itoa(currentYear) + "0" + strconv.Itoa(currentMonth) + ".xlsx")
} else {
err = file.Save(outfolder + "/donation_" + strconv.Itoa(currentYear) + strconv.Itoa(currentMonth) + ".xlsx")
}
if err != nil {
fmt.Printf(err.Error())
}
}
log.Println("donation export done")
}
func 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
}