scd_tools_go/service/excel/export.go

package excel

import (
	"scd_check_tools/tools"
	"errors"
	"fmt"
	"os"
	"strconv"
	"strings"

	"github.com/astaxie/beego/orm"

	"github.com/tealeg/xlsx"
)

var CacheFilePath = "tmp"

var tmpFileName = ""

func InitExcel(name string, p ...string) (result error) {
	if name == "" {
		fmt.Println(tools.NowTime() + "文件名不能为空")
		return errors.New("文件名不能为空")
	}
	tmpFileName = name
	if len(p) > 0 {
		path := p[0]
		if path == "" {
			fmt.Println(tools.NowTime() + "文件存储路径不能为空")
			return errors.New("文件存储路径不能为空")
		}
		CacheFilePath = path
		if _, err := os.Stat(CacheFilePath); os.IsNotExist(err) {
			// 必须分成两步：先创建文件夹、再修改权限
			os.MkdirAll(CacheFilePath, 0777) //0777也可以os.ModePerm
			os.Chmod(CacheFilePath, 0777)
		}
	}
	return nil
}

//导出数据
func ExportExcel(caption string, cols []string, colnames []string, datalist []orm.Params, autoNoCol bool) (filename string, result error) {
	excelFileName := CacheFilePath + string(os.PathSeparator) + tmpFileName
	if excelFileName == "" {
		fmt.Println(tools.NowTime() + "导出的文件路径或文件名不能为空")
		return "", errors.New("导出的文件路径或文件名不能为空")
	}
	/*
		xlFile, err := xlsx.OpenFile(excelFileName)
		if err != nil {
			fmt.Println(tools.NowTime() + err.Error())
			return "", err
		}
	*/
	var file *xlsx.File
	var sheet *xlsx.Sheet
	var row *xlsx.Row
	var cell *xlsx.Cell
	var err error
	file = xlsx.NewFile()
	if caption != "" {
		if len(caption) > 31 {
			sheet, err = file.AddSheet(caption[0:31])
		} else {
			sheet, err = file.AddSheet(caption)
		}
	} else {
		sheet, err = file.AddSheet("Sheet1")
	}
	if err != nil {
		fmt.Printf(tools.NowTime() + err.Error())
		return "", err
	}
	if len(cols) == 0 {
		//未指定导出列时，导出所有列且中文名与列表相同
		if len(datalist) == 0 {
			return "", errors.New("没有数据可导出")
		}
		for k, _ := range datalist[0] {
			cols = append(cols, k)
			colnames = append(colnames, k)
		}
	}
	if caption != "" {
		//添加标题行
		row = sheet.AddRow()
		cell = row.AddCell()
		if autoNoCol {
			cell.HMerge = len(colnames) //合并单元格数
		} else {
			cell.HMerge = len(colnames) - 1 //合并单元格数
		}
		cell.Value = caption
		style := xlsx.NewStyle()
		style.Alignment.Horizontal = "center"
		cell.SetStyle(style)
	}
	//添加列头行
	row = sheet.AddRow()
	if autoNoCol {
		cell = row.AddCell()
		cell.Value = "序号"
	}
	for _, colname := range colnames {
		cell = row.AddCell()
		cell.Value = colname
	}
	//添加数据行
	for i, dataRow := range datalist {
		row = sheet.AddRow()
		cell = row.AddCell()
		cell.Value = strconv.Itoa(i + 1) // "序号"
		for _, datacolitem := range cols {
			datavalue := dataRow[datacolitem]
			cell = row.AddCell()
			if datavalue == nil {
				cell.Value = ""
			} else {
				cell.Value = datavalue.(string)
			}
		}
	}
	err = file.Save(excelFileName + ".xlsx")
	if err != nil {
		fmt.Printf(tools.NowTime() + err.Error())
		return "", err
	}
	return tmpFileName + ".xlsx", nil
}

func FileExist(filename string) bool {
	_, err := os.Lstat(filename)
	return !os.IsNotExist(err)
}

func ImportRecord(path string, title string, fieldMapping map[string]string) ([]map[string]string, error) {
	xlFile, err := xlsx.OpenFile(path)
	if err != nil {
		return nil, err
	}
	var sheet = xlFile.Sheets[0]
	var result = []map[string]string{}
	var fields = make(map[int]string)
	for i, row := range sheet.Rows {
		if i < 2 { //判断标题是否一致
			if i == 0 && title != "" {
				for index, cell := range row.Cells {
					if index == 0 && title != cell.String() {
						return nil, errors.New("导入文件的标题必须为[" + title + "]")
					}
				}
			} else if i == 1 {
				for index2, cell := range row.Cells {
					var field = strings.ReplaceAll(cell.Value, " ", "")
					field = strings.ReplaceAll(field, "　", "")
					fields[index2] = field
				}
			}
			continue
		}
		rowdata := map[string]string{}
		for j, cell := range row.Cells {
			var fieldName = fields[j]
			var val = cell.Value
			if val != "" {
				if name, ok2 := fieldMapping[fieldName]; ok2 && tools.IsEmpty(name) != "" {
					rowdata[name] = val
				}
			}
		}
		result = append(result, rowdata)
	}
	return result, nil
}