opms_backend/opms_parent/app/service/partner/excel.go

package partner

import (
	"bytes"
	"fmt"
	"io"
	"io/ioutil"
	"net/http"
	"strconv"

	"github.com/xuri/excelize/v2"
)

type ExcelHeader struct {
	Name  string
	Width float64
}

func NewExcel(headers []ExcelHeader, data [][]string) (*excelize.File, error) {
	f := excelize.NewFile()
	sheet := "Sheet1"
	colStyle, err := f.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{
			Horizontal: "center",
			Vertical:   "center",
			WrapText:   true,
		},
		Font: &excelize.Font{
			Size:   11,
			Family: "宋体",
		},
	})
	if err != nil {
		return nil, err
	}
	headerStyle, err := f.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{
			Horizontal: "center",
		},
		Fill: excelize.Fill{
			Type:    "pattern",
			Color:   []string{"#a6a6a6"},
			Pattern: 1,
		},
		Border: []excelize.Border{
			{Type: "left", Color: "#000000", Style: 1},
			{Type: "top", Color: "#000000", Style: 1},
			{Type: "bottom", Color: "#000000", Style: 1},
			{Type: "right", Color: "#000000", Style: 1},
		},
	})
	if err != nil {
		return nil, err
	}

	lastColName, err := excelize.ColumnNumberToName(len(headers))
	if err != nil {
		return nil, err
	}
	err = f.SetColStyle(sheet, "A:"+lastColName, colStyle)
	if err != nil {
		return nil, err
	}
	err = f.SetCellStyle(sheet, "A1", lastColName+"1", headerStyle)
	if err != nil {
		return nil, err
	}
	for i, h := range headers {
		n, err := excelize.ColumnNumberToName(i + 1)
		if err != nil {
			return nil, err
		}
		f.SetCellValue(sheet, n+"1", h.Name)
	}

	for i, h := range headers {
		n, err := excelize.ColumnNumberToName(i + 1)
		if err != nil {
			return nil, err
		}
		err = f.SetColWidth(sheet, n, n, h.Width)
		if err != nil {
			return nil, err
		}
	}

	for row, item := range data {
		for col, v := range item {
			colName, err := excelize.ColumnNumberToName(col + 1)
			if err != nil {
				return nil, err
			}
			rowName := strconv.Itoa(row + 2)
			f.SetCellValue(sheet, colName+rowName, v)

		}
	}

	index := f.NewSheet(sheet)
	f.SetActiveSheet(index)
	return f, nil
}

func ParseExcelFromUrl(url string) ([]map[string]string, error) {
	b, err := DownFile(url)
	if err != nil {
		return nil, fmt.Errorf("下载 excel 异常 %s", err.Error())
	}
	return ParseExcel(bytes.NewBuffer(b))
}

func ParseExcel(r io.Reader) ([]map[string]string, error) {
	f, err := excelize.OpenReader(r)
	if err != nil {
		return nil, err
	}
	sheet := "Sheet1"
	rows, err := f.GetRows(sheet)
	if err != nil {
		return nil, err
	}

	fields := rows[0]
	data := []map[string]string{}
	for _, row := range rows[1:] {
		item := map[string]string{}
		for i := range row {
			item[fields[i]] = row[i]
		}
		data = append(data, item)
	}
	return data, nil
}

func DownFile(url string) ([]byte, error) {
	r, err := http.Get(url)
	if err != nil {
		return nil, err
	}
	if r.StatusCode != http.StatusOK {
		return nil, fmt.Errorf("DownFile from %s StatusCode %d", url, r.StatusCode)
	}
	defer r.Body.Close()
	return ioutil.ReadAll(r.Body)
}