package main

import (
	"fmt"
	"strconv"
	"strings"

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

type MyExcel struct {
	f *excelize.File
}

// pages 文件里生成几页 默认1 生成两页写2
func (this *MyExcel) WriteBegin(pages int) *MyExcel {
	f := excelize.NewFile()
	this.f = f
	if pages > 1 {
		//创建工作表
		for i := 2; i <= pages; i++ {
			f.NewSheet("Sheet" + strconv.Itoa(i))
		}
	}
	return this
}

/*
current当前执行插入页 不能超过总页数
tableK插入到表格的哪行哪列
tableV插入的值
*/
func (this *MyExcel) WriteExcel(current int, tableK string, tableV string) *MyExcel {
	this.f.SetCellValue("Sheet"+strconv.Itoa(current), tableK, tableV)
	return this
}

// Save 生成Excel文件名(路径)
func (this *MyExcel) Save(filaname string) error {
	//设置打开后默认 页
	//f.SetActiveSheet(index)
	err := this.f.SaveAs(filaname)
	if err != nil {
		return err
	}
	return nil
}

// filename 文件名  readCount代表要读取Excel第几页（如果多页的话）
func (this *MyExcel) ReadExcel(filename string, readCount int) ([][]string, error) {
	f, err := excelize.OpenFile(filename)

	if err != nil {
		fmt.Println(err)
	}
	if readCount > 0 {
		readCount--
	} else {
		readCount = 0
	}
	//f.GetSheetName(0)里面的012分别代表是123页
	firstSheet := f.GetSheetName(readCount)
	defer f.Close()
	rows, err1 := f.GetRows(firstSheet)
	return rows, err1
}

// strArr  := []string{"name", "phone", "address", "quantity"} Map字段   再通过map[int]map[string]interface{} 遍历插入数据库
func (this *MyExcel) ExcelToMaps(filename string, readCount int, strArr []string) map[int]map[string]interface{} {
	rows1, _ := this.ReadExcel(filename, 13)
	mm := make(map[int]map[string]interface{})
	mmi := 0
	for _, v := range rows1 {
		i := 0
		m := make(map[string]interface{})
		for _, v2 := range v {
			strS := strings.Split(v2, " ")
			switch i {
			case 0:
				m[strArr[i]] = strS[0]
			case 1:
				m[strArr[i]] = strS[0]
			case 2:
				m[strArr[i]] = strS[0]
			case 3:
				m[strArr[i]] = strS[0]
			}
			i++
		}
		mm[mmi] = m
		mmi++
	}
	return mm
}

/*  //数据库导出Excel
e := MyExcel{}
var name, phone, address string
var quantity, cid int
i := 1
thisI := 1
Db := NewGdo("xyz")
Db.Sql("SELECT count(id) FROM CommerceCustomer WHERE user=?", "webserver").Fetch(&cid)
page := cid / 40
if cid > (page * 40) {
	page++
}
fmt.Println(cid)
fmt.Println(page)
e.WriteBegin(page)
rows, _ := Db.Sql("SELECT name,phone,address,quantity FROM CommerceCustomer WHERE user=?", "webserver").FetchAll()

defer rows.Close()
for rows.Next() {
	rows.Scan(&name, &phone, &address, &quantity)
	e.WriteExcel(thisI, "A"+strconv.Itoa(i), name)
	e.WriteExcel(thisI, "B"+strconv.Itoa(i), phone)
	e.WriteExcel(thisI, "C"+strconv.Itoa(i), address)
	e.WriteExcel(thisI, "D"+strconv.Itoa(i), strconv.Itoa(quantity))
	//就翻页插入
	if i == 40 {
		//翻页第几页
		thisI++
		i = 0
	}
	i++
}
e.Save("xyz.xlsx")
*/
