161 lines
3.6 KiB
Go
161 lines
3.6 KiB
Go
package srv_contentexcel
|
||
|
||
import (
|
||
"bytes"
|
||
"fmt"
|
||
"strconv"
|
||
"strings"
|
||
|
||
"github.com/xuri/excelize/v2"
|
||
)
|
||
|
||
const (
|
||
colID = "ID"
|
||
colTitle = "标题"
|
||
colDesc = "描述"
|
||
colKeyworld = "关键词"
|
||
colRedLineRemark = "备注(红线-建议下架)"
|
||
sheetName = "Sheet1"
|
||
excelHeaders = 4
|
||
colRedLineRemarkIdx = 4
|
||
)
|
||
|
||
var headerAliases = map[string]int{
|
||
"id": 0, colID: 0,
|
||
"title": 1, colTitle: 1,
|
||
"desc": 2, "description": 2, colDesc: 2,
|
||
"keyworld": 3, "keywords": 3, "keyword": 3, colKeyworld: 3,
|
||
colRedLineRemark: colRedLineRemarkIdx,
|
||
}
|
||
|
||
func buildWorkbook(rows []ContentRow) ([]byte, error) {
|
||
f := excelize.NewFile()
|
||
defer f.Close()
|
||
|
||
if err := f.SetSheetRow(sheetName, "A1", &[]string{colID, colTitle, colDesc, colKeyworld}); err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
for i, row := range rows {
|
||
cell, err := excelize.CoordinatesToCellName(1, i+2)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if err := f.SetSheetRow(sheetName, cell, &[]any{
|
||
row.ID,
|
||
row.Title,
|
||
row.Desc,
|
||
strings.Join(row.Keyworld, ","),
|
||
}); err != nil {
|
||
return nil, err
|
||
}
|
||
}
|
||
|
||
var buf bytes.Buffer
|
||
if err := f.Write(&buf); err != nil {
|
||
return nil, err
|
||
}
|
||
return buf.Bytes(), nil
|
||
}
|
||
|
||
func parseWorkbook(data []byte) ([]ContentRow, error) {
|
||
f, err := excelize.OpenReader(bytes.NewReader(data))
|
||
if err != nil {
|
||
return nil, fmt.Errorf("打开 Excel 失败: %w", err)
|
||
}
|
||
defer f.Close()
|
||
|
||
sheets := f.GetSheetList()
|
||
if len(sheets) == 0 {
|
||
return nil, fmt.Errorf("Excel 无工作表")
|
||
}
|
||
|
||
rows, err := f.GetRows(sheets[0])
|
||
if err != nil {
|
||
return nil, fmt.Errorf("读取 Excel 失败: %w", err)
|
||
}
|
||
if len(rows) <= 1 {
|
||
return nil, fmt.Errorf("Excel 无数据行")
|
||
}
|
||
|
||
colIndex := mapHeaderColumns(rows[0])
|
||
if len(colIndex) < excelHeaders {
|
||
return nil, fmt.Errorf("Excel 表头需包含: ID、标题、描述、关键词")
|
||
}
|
||
|
||
result := make([]ContentRow, 0, len(rows)-1)
|
||
for lineNo, row := range rows[1:] {
|
||
contentRow, ok, err := parseDataRow(row, colIndex, lineNo+2)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if !ok {
|
||
continue
|
||
}
|
||
result = append(result, contentRow)
|
||
}
|
||
if len(result) == 0 {
|
||
return nil, fmt.Errorf("Excel 无有效数据行")
|
||
}
|
||
return result, nil
|
||
}
|
||
|
||
func mapHeaderColumns(header []string) map[int]int {
|
||
index := make(map[int]int, excelHeaders)
|
||
for i, name := range header {
|
||
key := strings.ToLower(strings.TrimSpace(name))
|
||
if col, ok := headerAliases[key]; ok {
|
||
index[col] = i
|
||
}
|
||
}
|
||
return index
|
||
}
|
||
|
||
func parseDataRow(row []string, colIndex map[int]int, lineNo int) (ContentRow, bool, error) {
|
||
if remarkIdx, ok := colIndex[colRedLineRemarkIdx]; ok {
|
||
if cellValue(row, remarkIdx) != "" {
|
||
return ContentRow{}, false, nil
|
||
}
|
||
}
|
||
|
||
idStr := cellValue(row, colIndex[0])
|
||
if idStr == "" {
|
||
return ContentRow{}, false, nil
|
||
}
|
||
id, err := strconv.ParseInt(idStr, 10, 64)
|
||
if err != nil || id <= 0 {
|
||
return ContentRow{}, false, fmt.Errorf("第 %d 行 ID 无效: %q", lineNo, idStr)
|
||
}
|
||
|
||
return ContentRow{
|
||
ID: id,
|
||
Title: cellValue(row, colIndex[1]),
|
||
Desc: cellValue(row, colIndex[2]),
|
||
Keyworld: parseKeywords(cellValue(row, colIndex[3])),
|
||
}, true, nil
|
||
}
|
||
|
||
func cellValue(row []string, index int) string {
|
||
if index < 0 || index >= len(row) {
|
||
return ""
|
||
}
|
||
return strings.TrimSpace(row[index])
|
||
}
|
||
|
||
func parseKeywords(raw string) []string {
|
||
raw = strings.TrimSpace(raw)
|
||
if raw == "" {
|
||
return nil
|
||
}
|
||
raw = strings.NewReplacer("，", ",", "；", ",", ";", ",").Replace(raw)
|
||
parts := strings.Split(raw, ",")
|
||
keywords := make([]string, 0, len(parts))
|
||
for _, part := range parts {
|
||
part = strings.TrimSpace(part)
|
||
if part != "" {
|
||
keywords = append(keywords, part)
|
||
}
|
||
}
|
||
return keywords
|
||
}
|