func generateCSVFromXLSXFile(excelFileName string, sheetIndex int, csvPath string, outputf Outputer) error {
var xlFile *xlsx.File
var error error
var sheetLen int
var rowString string
xlFile, error = xlsx.OpenFile(excelFileName)
if error != nil {
return error
}
sheetLen = len(xlFile.Sheets)
switch {
case sheetLen == 0:
e := new(XLSX2CSVError)
e.error = "This XLSX file contains no sheets.\n"
return *e
case sheetIndex >= sheetLen:
e := new(XLSX2CSVError)
e.error = fmt.Sprintf("No sheet %d available, please select a sheet between 0 and %d\n", sheetIndex, sheetLen-1)
return *e
}
sheet := xlFile.Sheets[sheetIndex]
file, error := os.Create(csvPath)
if error != nil {
return error
}
defer file.Close()
//file.WriteString("\xEF\xBB\xBF") // 写入UTF-8 BOM
writer := csv.NewWriter(file)
var record []string
for _, row := range sheet.Rows {
rowString = ""
if row != nil {
for cellIndex, cell := range row.Cells {
record = append(record, cell.String())
if cellIndex > 0 {
rowString = fmt.Sprintf("%s%s%s", rowString, *delimiter, cell.String())
} else {
rowString = fmt.Sprintf("%s", cell.String())
}
}
writer.Write(record)
record = nil
rowString = fmt.Sprintf("%s\n", rowString)
//outputf(rowString)
}
}
writer.Flush()
return nil
}
// 解析出excel中的各个工作表，将解析结果存入结构体
func parse_excel(excel *string, cfgbase *Cfgbase_t, errmsgs *tool.Errvec_t, parse_option Enum_parse_option) bool {
var parse_clock tool.Clock
parse_clock.Start_tick()
if false == tool.Exist(excel) {
*errmsgs = append(*errmsgs, *excel+"文件不存在!")
return false
}
// 1. 载入excel文件
xlFile, err := xlsx.OpenFile(*excel)
if err != nil {
*errmsgs = append(*errmsgs, tool.GetErrMsg("错误：解析<%s>文件失败，请确保该文件存在且未被打开!", *excel))
return false
}
var succ bool = true
// 2. 依次解析各个工作表
for _, sheet := range xlFile.Sheets {
var cfg Cfg_t
// 2.1 解析出工作表的内容，并将结果存放到cfg中
var ok bool = parse_sheet(sheet, &cfg, errmsgs, parse_option)
if false == ok {
succ = false
continue
}
var conflict_by_prop *Cfg_t = cfgbase.find_by_en_name(&cfg.En_name)
if nil != conflict_by_prop {
*errmsgs = append(*errmsgs, tool.GetErrMsg("错误：定义了两个同样地文件<%s>", cfg.Cn_name))
*errmsgs = append(*errmsgs, tool.GetErrMsg(" <%s = %s>", conflict_by_prop.Cn_name, conflict_by_prop.En_name))
*errmsgs = append(*errmsgs, tool.GetErrMsg(" <%s = %s>", cfg.Cn_name, cfg.En_name))
succ = false
continue
}
cfgbase.Cfgs = append(cfgbase.Cfgs, cfg)
}
// 将第一个工作表名作为文件名（比如，excel中有2个工作表item、hero，则将item作为文件名）
if len(cfgbase.Cfgs) > 0 {
cfgbase.Filename = cfgbase.Cfgs[0].En_name
}
tool.EchoWarn("执行完毕, 共耗时<%f>秒", parse_clock.End_tick())
return succ
}
func main() {
var xlFile *xlsx.File
var err error
if xlFile, err = xlsx.OpenFile("/Users/twer/Documents/BmwProject/dealerinfo.xlsx"); err != nil {
fmt.Println(err)
return
}
db, e := sql.Open("mysql", "root@/bmw?charset=utf8")
defer db.Close()
if e != nil {
return
}
recordList := list.New()
initMigrationRecords(db, recordList)
fmt.Println("migration record number: ", recordList.Len())
count := 0
file, err := os.OpenFile("无法匹配到的用户.csv", os.O_APPEND|os.O_RDWR|os.O_CREATE, 0666)
if err != nil {
panic(err)
}
defer file.Close()
b := []byte{0xEF, 0xBB, 0xBF}
n, err := file.Write(b)
if err != nil {
panic(err)
}
fmt.Println("Write ", n, " bytes")
csvHead := []string{"姓名", "电话", "邮箱", "备注"}
w := csv.NewWriter(file)
w.Write(csvHead)
for e := recordList.Front(); e != nil; e = e.Next() {
v := e.Value.(MigrationRecord)
id := findMatchRow(xlFile.Sheets[0], v.name_cn, 12, v.email)
f := func() string {
if v.mobile == "" {
return v.phone
}
return v.mobile
}
if id == "" {
id = findMatchRow(xlFile.Sheets[0], v.name_cn, 10, f())
}
if id != "" {
v.sso_user = id
count++
updateDatabase(db, v)
} else {
remark := ""
r := outputUnmatchedRow(xlFile.Sheets[0], v.name_cn)
if r == nil {
remark = "Excel文件中没有该人信息"
} else {
remark = "与Excel文件中信息不匹配，excel中的信息为[" + r.Cells[1].String() + "," + r.Cells[10].String() + "," + r.Cells[12].String() + "]"
}
csvRecord := []string{v.name_cn, f(), v.email, remark}
if err := w.Write(csvRecord); err != nil {
panic(err)
}
fmt.Println(v, " ")
}
}
w.Flush()
fmt.Println("There are ", count, " record matched")
}