bileiqi/05.code/backend/excel/export.go

package excel

import (
	"encoding/csv"
	"encoding/json"
	"errors"
	"fmt"
	"io"
	"os"
	"regexp"
	"rtzh_elec_temperature/tools"
	"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 != "" {
		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()
		if autoNoCol {
			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 = tools.IsEmpty(datavalue)
			}
		}
	}
	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
}

func ImpExcel(path string) (data []map[int]string, err error) {
	xlFile, err := xlsx.OpenFile(path)
	if err != nil {
		return nil, err
	}
	var sheet = xlFile.Sheets[0]
	var result = []map[int]string{}
	reg := regexp.MustCompile(`\r\n\t`)
	for i, row := range sheet.Rows {
		if i == 0 || i == 1 {
			continue
		}
		rowdata := map[int]string{}
		for j, cell := range row.Cells {
			rowdata[j] = reg.ReplaceAllString(strings.Trim(cell.String(), " "), "")
		}
		result = append(result, rowdata)
	}
	//tools.Log(result)
	return result, nil
}

func ImpCsv(path string, opts ...map[string]string) (data []map[int]string, err error) {
	fileHanlder, err := os.Open(path)
	if err != nil {
		return nil, err
	}
	defer fileHanlder.Close()
	reader := csv.NewReader(fileHanlder)
	if reader == nil {
		return nil, errors.New("文件读取失败！")
	}
	if opts != nil {
		p1 := opts[0]
		if separator, has := p1["separator"]; has {
			reader.Comma = []rune(separator)[0]
		}
	}
	reader.TrimLeadingSpace = true
	rowindex := 0
	var csvData = []map[int]string{}
	errList := []string{}
	for {
		txt, readerr := reader.Read()
		if readerr == io.EOF {
			break
		}
		if readerr != nil {
			rowindex++
			tools.Log(readerr)
			errList = append(errList, readerr.Error())
			continue
		}
		reg := regexp.MustCompile(`\r\n\t`)
		if rowindex == 0 {
			rowindex++
			continue
		}
		rowdata := map[int]string{}
		for j, cell := range txt {
			rowdata[j] = reg.ReplaceAllString(strings.Trim(cell, " "), "")
		}
		csvData = append(csvData, rowdata)
		rowindex++
	}
	if len(errList) > 0 {
		errstr, _ := json.Marshal(errList)
		return nil, errors.New(string(errstr))
	}
	return csvData, nil
}

//获取excel列头
func ReadExcelHeader(path string) (data map[int]string, err error) {
	xlFile, err := xlsx.OpenFile(path)
	if err != nil {
		return nil, err
	}
	var sheet = xlFile.Sheets[0]
	var result = map[int]string{}
	reg := regexp.MustCompile(`\r\n\t`)
	for i, row := range sheet.Rows {
		if i == 0 {
			for j, cell := range row.Cells {
				result[j] = reg.ReplaceAllString(strings.Trim(cell.String(), " "), "")
			}
			break
		}
	}
	//tools.Log(result)
	return result, nil
}

//获取csv文件列头
func ReadCsvHeader(path string, opts ...map[string]string) (data map[int]string, err error) {
	var result = map[int]string{}
	fileHanlder, err := os.Open(path)
	if err != nil {
		fileHanlder.Close()
		return nil, err
	}
	defer fileHanlder.Close()
	reader := csv.NewReader(fileHanlder)
	if reader == nil {
		return nil, errors.New("文件读取失败！")
	}

	if opts != nil {
		p1 := opts[0]
		if separator, has := p1["separator"]; has {
			reader.Comma = []rune(separator)[0]
		}
	}
	txt, readerr := reader.Read()
	if readerr != nil {
		return nil, readerr
	}
	reg := regexp.MustCompile(`\r\n\t`)
	for j, cell := range txt {
		result[j] = reg.ReplaceAllString(strings.Trim(cell, " "), "")
	}
	return result, nil
}