jy/src/jfw/modules/subscribepay/src/util/dataExportExcel.go

package util

//数据导出excel
import (
	"config"
	"errors"
	"fmt"
	"io"
	"os"
	qutil "qfw/util"
	"strings"
	"time"

	"github.com/tealeg/xlsx"
)

var (
	defaultExcelBorder = xlsx.Border{ //边框样式
		Left:        "thin",
		LeftColor:   "00A6A6A6",
		Right:       "thin",
		RightColor:  "00A6A6A6",
		Top:         "thin",
		TopColor:    "00A6A6A6",
		Bottom:      "thin",
		BottomColor: "00A6A6A6",
	}
	defaultExcelAlignment = xlsx.Alignment{ //对其方式
		Horizontal: "center",
		Vertical:   "center",
		WrapText:   true,
	}
	defaultCellStyle = &xlsx.Style{ //默认样式
		Border:    defaultExcelBorder,
		Alignment: defaultExcelAlignment,
	}
)

//获取
func GetExcelFilePath(orderCode string) string {
	now := time.Now()
	filename := fmt.Sprintf("%s.xlsx", fmt.Sprintf("%s_%s_%s", now.Format("20060102150405"), orderCode, qutil.GetLetterRandom(5)))
	return fmt.Sprintf("/dataexport/%s/%s/%s", now.Format("2006"), now.Format("01"), filename)
}

//获取excel导出数据长度
func GetDataExportFileDataLen(path string) (int, error) {
	xFile, err := xlsx.OpenFile(path)
	if err != nil {
		return -1, err
	}
	sheet1, ok := xFile.Sheet["Sheet1"]
	if !ok {
		return -1, fmt.Errorf("not find sheet1")
	}
	if len(sheet1.Rows) == 0 {
		return -1, fmt.Errorf("sheet1 hasn't Rows data ")
	}
	return sheet1.Rows[len(sheet1.Rows)-1].Cells[0].Int()
}

//生成数据导出excel
func CreateDataExportExcelFile(list *[]map[string]interface{}, isSenior bool, filePath string) error {
	if list == nil || len(*list) == 0 {
		return errors.New("未查询到数据")
	}
	//创建excel文件
	file := xlsx.NewFile()
	sheet, err := file.AddSheet("Sheet1")
	if err != nil {
		return errors.New("创建Sheet1失败")
	}
	var row *xlsx.Row
	var cell *xlsx.Cell
	var excelHead []*config.ExeclCell
	//添加头部标题
	if !isSenior {
		row = sheet.AddRow()
		row.SetHeight(20)
		excelHead = config.ExConf.Standard_Fields
		for index, excel := range excelHead {
			cell = row.AddCell()
			cell.SetString(excel.Name)
			cell.SetStyle(&xlsx.Style{
				Fill:      *xlsx.NewFill("solid", excel.Color, excel.Color),
				Border:    defaultExcelBorder,
				Alignment: defaultExcelAlignment,
			})
			sheet.Col(index).Width = excel.Width
		}
	} else {
		excelHead = config.ExConf.Senior_Fields
		//铺设第一层title
		row = sheet.AddRow()
		row.SetHeight(12)
		lastF := false //合并行时标识
		for _, excel := range excelHead {
			if excel.VMerge != 1 && excel.HMerge == 0 {
				if lastF { //不可读取内容
					lastF = false
					continue
				}
				cell = row.AddCell()
			} else {
				lastF = true
				cell = row.AddCell()
				cell.SetString(excel.Name)
				cell.VMerge = excel.VMerge
				cell.HMerge = excel.HMerge
			}
			if excel.HMerge != 0 {
				lastF = true
			}
			cell.SetStyle(&xlsx.Style{
				Fill:      *xlsx.NewFill("solid", excel.Color, excel.Color),
				Border:    defaultExcelBorder,
				Alignment: defaultExcelAlignment,
			})
		}
		//铺设第二层title并设置宽度
		row = sheet.AddRow()
		row.SetHeight(12)
		cNum := 0
		for _, excel := range excelHead {
			if excel.HMerge != 0 {
				continue
			}
			cell = row.AddCell()
			if excel.VMerge != 1 {
				cell.SetString(excel.Name)
			}
			sheet.Col(cNum).Width = excel.Width
			cNum++
			cell.SetStyle(&xlsx.Style{
				Fill:      *xlsx.NewFill("solid", excel.Color, excel.Color),
				Border:    defaultExcelBorder,
				Alignment: defaultExcelAlignment,
			})
		}
	}
	//填充数据
	for k, data := range *list {
		row = sheet.AddRow()
		row.SetHeight(50)
		data["index"] = k + 1
		for _, v := range excelHead {
			if v.Filed == "" {
				continue
			}
			cell = row.AddCell()

			if v.Filed == "title" || v.Filed == "url" {
				cell.SetFormula(fmt.Sprintf("=HYPERLINK(\"%s\",\"%s\")", data["url_jump"], data[v.Filed]))
			} else {
				cell.SetValue(data[v.Filed])
			}
			cell.SetStyle(&xlsx.Style{
				Border:    defaultExcelBorder,
				Alignment: defaultExcelAlignment,
			})
		}
	}
	if err := FolderCheck(filePath); err != nil {
		return err
	}
	return file.Save(filePath)
}

//校验文件夹是否存在；
//若不存在则创建；
func FolderCheck(path string) error {
	pathCheck := path[:strings.LastIndex(path, "/")]
	//校验文件夹是否存在
	_, pathError := os.Stat(pathCheck)
	if pathError != nil && os.IsNotExist(pathError) {
		// 创建文件夹
		return os.MkdirAll(pathCheck, os.ModePerm)
	}
	return nil
}

//移动文件至指定目录
func MoveFile(src, dst string) (int64, error) {
	srcFile, err := os.Open(src)
	if err != nil {
		return 0, err
	}
	defer srcFile.Close()
	//文件夹
	err = FolderCheck(dst)
	if err != nil {
		return 0, err
	}
	dstFile, err := os.Create(dst)
	if err != nil {
		return 0, err
	}
	defer dstFile.Close()
	return io.Copy(dstFile, srcFile)
}