package main
import (
"fmt"
"github.com/xuri/excelize/v2"
"strconv"
)
func main() {
// 保存
meta := map[string]interface{}{
"title": []map[string]string{
{"field": "sn", "value": "订单编号", "width": "15"},
{"field": "name", "value": "收件人", "width": "9"},
{"field": "tel", "value": "联系电话", "width": "15"},
{"field": "goods_name", "value": "商品名称", "width": "15"},
{"field": "goods_number", "value": "数量", "width": "9"},
},
"col_merge": []map[string]interface{}{
{"field": "sn", "children": []string{"name", "tel"}},
},
}
data := []map[string]interface{}{
{"sn": "10000", "name": "小张", "tel": "13880808080", "goods_name": "商品A", "goods_number": "1份"},
{"sn": "10000", "name": "小张", "tel": "13880808080", "goods_name": "商品B", "goods_number": "1份"},
{"sn": "10000", "name": "小张", "tel": "13880808080", "goods_name": "商品C", "goods_number": "1份"},
{"sn": "10000", "name": "小张", "tel": "13880808080", "goods_name": "商品D", "goods_number": "1份"},
{"sn": "10001", "name": "小李", "tel": "13880808081", "goods_name": "商品A", "goods_number": "1份"},
{"sn": "10002", "name": "小王", "tel": "13880808082", "goods_name": "商品A", "goods_number": "1份"},
{"sn": "10002", "name": "小王", "tel": "1388080808X", "goods_name": "商品B", "goods_number": "1份"},
{"sn": "10003", "name": "小赵", "tel": "13880808083", "goods_name": "商品A", "goods_number": "1份"},
{"sn": "10003", "name": "小赵", "tel": "13880808083", "goods_name": "商品B", "goods_number": "1份"},
{"sn": "10004", "name": "小周", "tel": "13880808084", "goods_name": "商品A", "goods_number": "1份"},
}
_ = SaveExcel(meta, data, "订单.xlsx")
// 读取
dataExcel, _ := ReadExcel("./订单.xlsx", []string{"sn", "name", "tel", "goods_name", "goods_number"})
fmt.Println(dataExcel)
}
// 数字转Excel列编号
func stringFromColumnIndex(pColumnIndex int) string {
var index string
if pColumnIndex < 26 {
index = fmt.Sprintf("%c", 65+pColumnIndex)
} else if pColumnIndex < 702 {
index = fmt.Sprintf("%c%c", 64+(pColumnIndex/26), 65+pColumnIndex%26)
} else {
index = fmt.Sprintf("%c%c%c", 64+((pColumnIndex-26)/676), 65+(((pColumnIndex-26)%676)/26), 65+pColumnIndex%26)
}
return index
}
// 保存Excel
func SaveExcel(meta map[string]interface{}, data []map[string]interface{}, name string) error {
// 准备数据
// 表头、列合并、字段关联列序号、数据长度
title, _ := meta["title"].([]map[string]string)
colMerge, _ := meta["col_merge"].([]map[string]interface{})
fieldColumnIndex := map[string]string{}
dataLen := len(data)
// 创建工作簿
newFile := excelize.NewFile()
// 创建工作表
index := newFile.NewSheet("Sheet1")
// 处理数据
for key, value := range title {
// 索引转列序号
columnIndex := stringFromColumnIndex(key)
// 字段关联列序号
fieldColumnIndex[value["field"]] = columnIndex
// 设置列宽度
i, _ := strconv.ParseFloat(value["width"], 64)
_ = newFile.SetColWidth("Sheet1", columnIndex, columnIndex, i)
// 设置表头
_ = newFile.SetCellValue("Sheet1", fmt.Sprintf("%s1", columnIndex), value["value"])
// 填充数据
for keyRow, valueRow := range data {
_ = newFile.SetCellValue("Sheet1", fmt.Sprintf("%s%d", columnIndex, 2+keyRow), valueRow[value["field"]])
}
}
// 处理列合并
for _, value := range colMerge {
// 拿到列序号
column, ok := fieldColumnIndex[value["field"].(string)]
if !ok {
continue
}
// 根据主列需要处理的子列
children, ok := value["children"].([]string)
// 处理主列
// 块数据标记起始、结束行、上一行内容
startLine := 2
endLine := 2
upLineValue := ""
// 表头占一行,所以起始行为2
for i := 2; i < dataLen+2; i++ {
// 单元格内容
cellValue, _ := newFile.GetCellValue("Sheet1", fmt.Sprintf("%s%d", column, i))
// 起始行仅存储单元格内容
if i == 2 {
upLineValue = cellValue
} else {
// 当前行和上一行内容一致时，把块数据结束行移动到当前行
if upLineValue == cellValue {
endLine = i
}
// 当前行和上一行内容不一致 或者 是最后一行 时，需要对内容判断合并
if upLineValue != cellValue || i == dataLen+1 {
// 如果起始行和结束行标记是一样的，说明之前的内容不是一样的，重新定义标记块数据从当前行开始
// 否则开始行和结束行不一样，说明数据块是一样的，需要做合并处理
if startLine == endLine {
startLine = i
endLine = i
upLineValue = cellValue
} else {
// 合并主字列
_ = newFile.MergeCell("Sheet1",
fmt.Sprintf("%s%d", column, startLine),
fmt.Sprintf("%s%d", column, endLine),
)
// 处理子列
// 处理子列逻辑和处理主列一样，就不在赘述
for _, valueSlave := range children {
columnSlave, ok := fieldColumnIndex[valueSlave]
if !ok {
continue
}
startLineSlave := startLine
endLineSlave := startLine
upLineValueSlave := ""
for j := startLine; j <= endLine; j++ {
cellValueSlave, _ := newFile.GetCellValue("Sheet1", fmt.Sprintf("%s%d", columnSlave, i))
if j == startLine {
upLineValueSlave = cellValueSlave
} else {
if upLineValueSlave == cellValueSlave {
endLineSlave = j
}
if upLineValueSlave != cellValueSlave || j == endLine {
if startLineSlave == endLineSlave {
startLineSlave = j
endLineSlave = j
upLineValueSlave = cellValueSlave
} else {
_ = newFile.MergeCell("Sheet1",
fmt.Sprintf("%s%d", columnSlave, startLineSlave),
fmt.Sprintf("%s%d", columnSlave, endLineSlave),
)
}
}
}
}
}
startLine = i
endLine = i
upLineValue = cellValue
}
}
}
}
}
// 设置工作簿的默认工作表
newFile.SetActiveSheet(index)
// 保存方式
return newFile.SaveAs(name)
}
// 读取Excel
func ReadExcel(filename string, title []string) ([]map[string]interface{}, error) {
// 打开文件
newFile, err := excelize.OpenFile(filename)
if err != nil {
return nil, err
}
defer newFile.Close()
// 获取Sheet1工作表所有行
rows, err := newFile.GetRows("Sheet1")
if err != nil {
return nil, err
}
// 读取所有数据
data := []map[string]interface{}{}
titleLen := len(title)
for key, row := range rows {
// 剔除表头
if key == 0 {
continue
}
rowData := map[string]interface{}{}
for colKey, colCell := range row {
if !(colKey < titleLen) {
break
}
rowData[title[colKey]] = colCell
}
data = append(data, rowData)
}
return data, nil
}