乐趣区

关于go:golang-操作-excel


package main

import (
    "encoding/json"
    "errors"
    "fmt"
    "github.com/xuri/excelize/v2"
    "os"
    "path"
    "time"
)

// 参考文档:https://www.bookstack.cn/read/excelize-v2.0/spilt.4.1.md
func ReadContent(path string, title []string) (*[][]string, error) {
    // 关上文件
    f, err := excelize.OpenFile(path)
    if err != nil {return nil, err}
    rows, err := f.GetRows("Sheet1")
    var list [][]string

    // 对 sheet1 中的内容进行操作
    for i, row := range rows {
        // 题目不操作
        if i == 0 {list = append(list, title)
            //continue
        }
        list = append(list, row)
    }
    fmt.Println(list)
    return &list, nil
}

//AppendSaveExcel 追加
/*
    dirPath := "log"
    fileName := "Book1.xlsx"
    dataList := [][]interface{}{{"姓名", "电话", "公司", "职位", "退出工夫"}, {1, 2, "刘犇, 刘犇, 刘犇", "4", "5"}}
    AppendSaveExcel(dirPath, fileName, &dataList) // 会存到 log/Book1.xlsx 里
   文件没有创立并写入,有追加写
*/
func AppendSaveExcel(dirPath, fileName string, dataList *[][]interface{}) (err error) {if len(*dataList) == 0 {return errors.New("数据不能为空")
    }
    // 不存在创立目录
    _ = CreateFolder(dirPath, true)
    activeSheetName := "Sheet1"
    // 文件门路
    fileNamePath := path.Join(dirPath, fileName)
    // 从第几行开始写数据
    rowNum := 0
    // excel 最初数据所有行数
    lastLineNum := 0
    var f *excelize.File
    // 创立 excel
    // 判断文件是否存在,不存在新建
    fileExistsBool := FileExists(fileNamePath)
    if !fileExistsBool {f = excelize.NewFile()
    } else { // 追加写入 excel
        f, _ = excelize.OpenFile(fileNamePath)
        rows, _ := f.GetRows(activeSheetName)
        lastLineNum = len(rows) // 找到最初一行
    }
    // Create a new sheet.
    index, _ := f.NewSheet(activeSheetName)

    // 设置工作簿的默认工作表
    f.SetActiveSheet(index)

    // 设置列宽度为 16
    var ColAbc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    // 须要存入数据的列长度
    dataColLen := len((*dataList)[0])
    if dataColLen > 26 {err = f.SetColWidth("Sheet1", "A", "Z", 16)
    } else {err = f.SetColWidth("Sheet1", "A", ColAbc[dataColLen-1:dataColLen], 16)
    }
    if err != nil {fmt.Println(err)
        return errors.New(fmt.Sprintf("f.SetColWidth failed, err:%v", err))
    }
    // 从文件内容尾行写入
    rowNum = lastLineNum
    // 循环按行赋值
    for _, list := range *dataList {
        rowNum += 1
        // 按行赋值 从 aN 开始按行赋值
        f.SetSheetRow(activeSheetName, fmt.Sprintf("A%d", rowNum), &list)
    }
    // 保留 excel
    if err := f.SaveAs(fileNamePath); err != nil {fmt.Println(err)
        return errors.New(fmt.Sprintf("save file failed, path:(%s)", fileNamePath))
    }
    return nil
}

func CreateFolder(p string, ignoreExists bool) error {_, FolderExists := os.Stat(p)
    if FolderExists != nil && ignoreExists == false {err := errors.New("folder exists")
        return err
    }
    if FolderExists == nil {err := os.MkdirAll(p, os.ModePerm)
        if err != nil {return err}
    }
    return nil
}

func FileExists(filename string) bool {info, err := os.Stat(filename)
    if os.IsNotExist(err) {return false}
    if info == nil {return false}
    return true
}

func main() {var title = []string{"ip", "保健组", "lables", "大城市"}
    ll, _ := ReadContent("./data/target.xlsx", title)
    //fmt.Println(ll)
    b, _ := json.Marshal(&ll)
    var aa *[][]interface{}
    _ = json.Unmarshal(b, &aa)
    lname := fmt.Sprintf("fftest_%s.xlsx", time.Now().Format("20060102150405"))
    AppendSaveExcel("./data", lname, aa)
}

退出移动版