go 操作 Excel
文档地址:
https://xuri.me/excelize/zh-hans/
package main
import (
"fmt"
"github.com/xuri/excelize/v2"
)
func main() {
readEecel()
//writeEecel()
//appendEecel()
}
func writeEecel() {
f := excelize.NewFile()
// 创建一个工作表
index := f.NewSheet("Sheet1")
// 设置单元格的值
f.SetCellValue("Sheet1", "A2", "Hello world.")
f.SetCellValue("Sheet1", "B2", 100)
//按行赋值
err := f.SetSheetRow("Sheet1", "A1", &[]interface{}{"39 - 38 = ", "39 - 38 = ", "39 - 38 = ", "39 - 38 = ", "39 - 38 = "})
if err != nil {
fmt.Println(err)
}
//设置列宽度
err = f.SetColWidth("Sheet1", "A", "H", 16)
if err != nil {
fmt.Println(err)
}
// 设置工作簿的默认工作表
f.SetActiveSheet(index)
// 根据指定路径保存文件
if err = f.SaveAs("Book1.xlsx"); err != nil {
fmt.Println(err)
}
}
func appendEecel() {
fileName := "Book1.xlsx"
activeSheet := "Sheet1"
f, _:= excelize.OpenFile(fileName)
// Get all the rows in the Sheet1.获取所有行的数据 按行获取
rows, err := f.GetRows(activeSheet)
//获取所有列的内容 按列获取
cols, err := f.GetCols(activeSheet)
if err != nil {
fmt.Println(err)
return
}
fmt.Println("rows_len:", len(rows))
fmt.Println("cols_len:", len(cols))
//fmt.Println("rows_content:",rows)
lineHeight, err := f.GetRowHeight(activeSheet,1)
fmt.Println("lineHeight:", lineHeight)
lineLevel, err := f.GetRowOutlineLevel(activeSheet,2)
fmt.Println("lineLevel:", lineLevel)
f.SetCellValue(activeSheet, fmt.Sprintf("A%d",len(rows)+1), fmt.Sprintf("aa%d",len(rows)+1))
f.SetCellValue(activeSheet, fmt.Sprintf("B%d",len(rows)+1), fmt.Sprintf("bb%d",len(rows)+1))
f.SetCellValue(activeSheet, fmt.Sprintf("C%d",len(rows)+1), fmt.Sprintf("cc%d",len(rows)+1))
if err := f.SaveAs(fileName); err != nil {
fmt.Println(err)
}
}
func readEecel() {
//f, err := excelize.OpenFile("Book1.xlsx")
f, err := excelize.OpenFile("信息系统暴露面模板_20211027.xlsx")
if err != nil {
fmt.Println(err)
return
}
defer func() {
if err := f.Close(); err != nil {
fmt.Println(err)
}
}()
// 获取工作表中指定单元格的值
//cell, err := f.GetCellValue("Sheet1", "B2")
//if err != nil {
// fmt.Println(err)
// return
//}
//fmt.Println(cell)
// 获取 Sheet1 上所有单元格
rows, err := f.GetRows("Sheet1")
if err != nil {
fmt.Println(err)
return
}
for _, row := range rows {
for _, colCell := range row {
fmt.Print(colCell, "\t")
}
fmt.Println()
}
fmt.Println("===========================================")
res:= arrayTwoStringGroupsOf(rows,3)
fmt.Println(res)
}
func arrayTwoStringGroupsOf(arr [][]string, num int64) [][][]string {
max := int64(len(arr))
//判断数组大小是否小于等于指定分割大小的值，是则把原数组放入二维数组返回
if max <= num {
return [][][]string{arr}
}
//获取应该数组分割为多少份
var quantity int64
if max%num == 0 {
quantity = max / num
} else {
quantity = (max / num) + 1
}
//声明分割好的二维数组
var segments = make([][][]string, 0)
//声明分割数组的截止下标
var start, end, i int64
for i = 1; i <= quantity; i++ {
end = i*num
if i != quantity {
segments = append(segments, arr[start:end])
} else {
segments = append(segments, arr[start:])
}
start = i*num
}
return segments
}
func arrayInGroupsOf(arr []int, num int64) [][]int {
max := int64(len(arr))
//判断数组大小是否小于等于指定分割大小的值，是则把原数组放入二维数组返回
if max <= num {
return [][]int{arr}
}
//获取应该数组分割为多少份
var quantity int64
if max%num == 0 {
quantity = max / num
} else {
quantity = (max / num) + 1
}
//声明分割好的二维数组
var segments = make([][]int, 0)
//声明分割数组的截止下标
var start, end, i int64
for i = 1; i <= quantity; i++ {
end = i*num
if i != quantity {
segments = append(segments, arr[start:end])
} else {
segments = append(segments, arr[start:])
}
start = i*num
}
return segments
}
应用场景:
gorm 从 mysql 里分批查出数据,并分批追加写入excel
// 全量excel分批导出poc
func (e *ModelExploits) ExportModelExploits(c *gin.Context) {
msgID := tools.GenerateMsgIDFromContext(c)
d := new(dto.ModelExploitsSearch)
//fmt.Println("d222", d)
db, err := tools.GetOrm(c)
if err != nil {
log.Error(err)
return
}
//查询列表
err = d.Bind(c)
if err != nil {
e.Error(c, http.StatusUnprocessableEntity, err, "参数验证失败")
return
}
//数据权限检查
p := actions.GetPermissionFromContext(c)
serviceStudent := service.ModelExploits{}
serviceStudent.MsgID = msgID
serviceStudent.Orm = db
fileNamePath, err := serviceStudent.ExportModelExploitsPage(d, p)
if err != nil {
e.Error(c, http.StatusUnprocessableEntity, err, "查询失败")
return
}
e.OK(c, gin.H{"filePath": fileNamePath}, "ok")
}
func (e *ModelExploits) ExportModelExploitsPage(c *dto.ModelExploitsSearch, p *actions.DataPermission) (fileNamePath string, err error) {
//var err error
var data models.ModelExploits
msgID := e.MsgID
dirPath := "static/downloads" //导出的目录
fileName := fmt.Sprintf("%s_%s", time.Now().Format("20060102150405"), "poc_data.xlsx") //文件名称
fileNamePath = path.Join(dirPath, fileName) //文件全路径
batchSize := 100
var lis []models.ModelExploits
result := e.Orm.Model(&data).
Scopes(
cDto.MakeCondition(c.GetNeedSearch()),
//cDto.Paginate(c.GetPageSize(), c.GetPageIndex()),
actions.Permission(data.TableName(), p),
).
//分批处理
FindInBatches(&lis, batchSize, func(tx *gorm.DB, batch int) error {
// 批量处理找到的记录
total := 0
if len(lis) == batchSize {
total = batch * batchSize
} else {
total = (batch-1)*batchSize + len(lis)
}
fmt.Printf("第 %d 批 , 每批 %d 条 ,已经处理 %d 条\n", batch, batchSize, total) // batch // Batch 1, 2, 3
//for _, result := range lis {
// fmt.Println(result.Id)
//}
// 批量处理找到的记录 分批存到excel里
err := appendSaveEexcel(dirPath, fileName, &lis)
if err != nil {
return err
}
//tx.Save(&lis)
//fmt.Println(tx.RowsAffected) // 本次批量操作影响的记录数
// 如果返回错误会终止后续批量操作
return nil
})
if result.Error != nil {
log.Errorf("msgID[%s] db error:%s", msgID, result.Error)
return "", result.Error
}
//fmt.Printf("total4444---->:%d", len(lis)) // batch // Batch 1, 2, 3
//fmt.Println(result.Error) // returned error
//fmt.Println(result.RowsAffected) // 整个批量操作影响的记录数
return fileNamePath, nil
}
//save excel 追加写入
func appendSaveEexcel(dirPath, fileName string, lists *[]models.ModelExploits) error {
_ = tools.PathDirCreate(dirPath) //不存在创建目录
activeSheetName := "Sheet1"
fileNamePath := path.Join(dirPath, fileName)
exists, err := tools.PathFileExists(fileNamePath) //判断文件是否存在创建目录
rowNum := 0
lastLineNum := 0
var f *excelize.File
// 创建excel
if !exists || err != nil {
f = excelize.NewFile()
// Create a new sheet.
index := f.NewSheet(activeSheetName)
// Set active sheet of the workbook.
f.SetActiveSheet(index)
// Set tabletitle value of a cell.
tableInfo := map[string]string{
"A1": "Id",
"B1": "Filename",
"C1": "Product",
"D1": "Fofaquery",
}
for k, v := range tableInfo {
f.SetCellValue(activeSheetName, k, v)
}
} else { // 追加写入excel
f, _ = excelize.OpenFile(fileNamePath)
rows, _ := f.GetRows(activeSheetName)
lastLineNum = len(rows) //找到最后一行
}
// Set table content value of a cell.
for index, list := range *lists {
if !exists || err != nil {
//如果不存在从第2行写入
rowNum = index + 2
} else {
//否则从文件内容尾行写入
rowNum = lastLineNum + index + 1
}
f.SetCellValue(activeSheetName, fmt.Sprintf("A%d", rowNum), list.Id)
f.SetCellValue(activeSheetName, fmt.Sprintf("B%d", rowNum), list.Filename)
f.SetCellValue(activeSheetName, fmt.Sprintf("C%d", rowNum), list.Product)
f.SetCellValue(activeSheetName, fmt.Sprintf("D%d", rowNum), list.Fofaquery)
}
// Save spreadsheet by the given path. static/downloads/Book1.xlsx
if err := f.SaveAs(fileNamePath); err != nil {
fmt.Println(err)
return errors.New(fmt.Sprintf("save file failed, path:(%s)", fileNamePath))
}
return nil
}
封装
/*
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 {
if FolderExists(p) == true && ignoreExists == false {
err := errors.New("folder exists")
return err
}
if FolderExists(p) == false {
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
}
[Haima的博客]
http://www.cnblogs.com/haima/
