eth_transfer_go/utils/excelhelper/excel_helper.go

package excelhelper

import (
	"encoding/csv"
	"errors"
	"fmt"
	helper "go-admin/utils"
	"log"
	"reflect"
	"strings"

	"github.com/xuri/excelize/v2"

	"github.com/gin-gonic/gin"
)

/*
导出csv文件

  - @fileName 文件名 不带拓展名
  - @header 文件头
  - @records 内容
*/
func ExportCSV(c *gin.Context, fileName string, header []string, records [][]string) error {
	disposition := fmt.Sprintf("attachment; filename=%s.csv", fileName)

	// Set headers
	c.Header("Content-Description", "File Transfer")
	c.Header("Content-Disposition", disposition)
	c.Header("Content-Type", "text/csv")

	// Create a CSV writer using the response writer
	writer := csv.NewWriter(c.Writer)
	defer writer.Flush()

	// Write CSV header
	writer.Write(header)

	for _, record := range records {
		writer.Write(record)
	}

	return nil
}

/*
导出excel

  - @fileName 文件名称
  - @data 数据源
  - @ingore 忽略header
*/
func ExportExcel[T any](c *gin.Context, fileName string, data []T, ingore []string) error {
	if len(data) == 0 {
		return errors.New("无导出记录")
	}
	// Create a new Excel file
	f := excelize.NewFile()
	// Use reflection to get the header from struct tags
	t := reflect.TypeOf(data[0])
	headers := []string{}
	for i := 0; i < t.NumField(); i++ {
		field := t.Field(i)
		excelTag := field.Tag.Get("excel")
		if excelTag != "" && !helper.ArrayAny(ingore, excelTag) {
			headers = append(headers, excelTag)
		}
	}
	// Set headers
	for i, header := range headers {
		col := string('A' + i)
		cell := fmt.Sprintf("%s1", col)
		f.SetCellValue("Sheet1", cell, header)
	}

	// Fill rows with data
	for rowIndex, item := range data {
		rowValue := reflect.ValueOf(item)
		rowType := rowValue.Type()
		for colIndex, header := range headers {
			col := string('A' + colIndex)
			cell := fmt.Sprintf("%s%d", col, rowIndex+2)
			var fieldValue reflect.Value

			for i := 0; i < rowType.NumField(); i++ {
				field := rowType.Field(i)
				if strings.EqualFold(field.Tag.Get("excel"), header) {
					fieldValue = rowValue.Field(i)
					break
				}
			}

			// Check if the fieldValue is valid before accessing it
			if fieldValue.IsValid() && fieldValue.CanInterface() {
				//f.SetCellValue("Sheet1", cell, fieldValue.Interface())
				value := fieldValue.Interface()

				// Ensure the value is a string, convert it if necessary
				var stringValue string
				if v, ok := value.(string); ok {
					stringValue = v // If it's a string, use it directly
				} else {
					stringValue = fmt.Sprintf("%v", value) // Otherwise, convert to string
				}
				f.SetCellValue("Sheet1", cell, stringValue)
			} else {
				// Handle the case where fieldValue is invalid or nil
				f.SetCellValue("Sheet1", cell, "")
			}
		}
	}
	// Set response headers and send the file to the client

	// c.Writer.Header().Set("Content-Disposition", "attachment; filename=test.xlsx")
	// c.Writer.Header().Set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=binary")
	// c.Writer.Header().Set("Content-Transfer-Encoding", "binary")
	// c.Writer.Header().Set("Expires", "0")
	// c.Writer.Header().Set("Cache-Control", "must-revalidate")
	// c.Writer.Header().Set("Pragma", "public")
	c.Header("Content-Description", "File Transfer")
	c.Header("Content-Disposition", fmt.Sprintf("attachment; filename=%s.xlsx", fileName))
	c.Header("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
	c.Header("Content-Transfer-Encoding", "binary")
	c.Header("Expires", "0")
	c.Header("Cache-Control", "must-revalidate")
	c.Header("Pragma", "public")
	c.Header("Content-Encoding", "")
	//fmt.Println("c.Writer.Header():", c.Writer.Header())
	if _, err := f.WriteTo(c.Writer); err != nil {
		log.Println("Error writing file:", err)
		return err
	}
	return nil
	//return f.WriteTo(c.Writer)
}

func MapExcelToStruct[T any](rows [][]string, headers []string) ([]T, error) {
	var results []T
	if len(rows) == 0 {
		return results, nil
	}

	for _, row := range rows {
		var result T
		v := reflect.ValueOf(&result).Elem()

		for i, header := range headers {
			fieldName := ""
			for j := 0; j < v.NumField(); j++ {
				field := v.Type().Field(j)
				tag := field.Tag.Get("excel")
				if strings.EqualFold(tag, header) {
					fieldName = field.Name
					break
				}
			}

			if fieldName != "" && i < len(row) {
				field := v.FieldByName(fieldName)
				if field.IsValid() && field.CanSet() {
					field.Set(reflect.ValueOf(row[i]).Convert(field.Type()))
				}
			}
		}
		results = append(results, result)
	}

	return results, nil
}

// 获取上传文件的数据
func GetExcelContent(c *gin.Context) (dataRows [][]string, headers []string, err error) {
	// 获取上传的文件
	file, err := c.FormFile("file")
	if err != nil {
		err = errors.New("文件上传失败")
		return
	}

	// 打开上传的文件
	src, err := file.Open()
	if err != nil {
		err = errors.New("文件打开失败")
		return
	}
	defer src.Close()

	// 使用 excelize 读取 Excel 文件
	xlFile, err := excelize.OpenReader(src)
	if err != nil {
		err = errors.New("读取 Excel 文件失败")
		return
	}

	sheetName := xlFile.GetSheetName(0)

	// 假设读取第一个工作表中的数据
	rows, err := xlFile.GetRows(sheetName)
	if err != nil {
		err = errors.New("读取 Excel 行数据失败")
		return
	}

	if len(rows) < 1 {
		err = errors.New("没有数据内容")
		return
	}

	headers = rows[0] // First row is the header
	dataRows = rows[1:]
	return
}