用到了go语言的excelize库,在github或者gitee上面都有,自行百度学习吧。
上代码,这是1.0的版本:
package main
import (
"encoding/csv"
"fmt"
"io"
"io/ioutil"
"math"
"os"
"path"
"strconv"
"strings"
"github.com/360EntSecGroup-Skylar/excelize"
"gopkg.in/ini.v1"
)
func isDir(path string) bool {
s, err := os.Stat(path)
if err != nil {
return false
}
return s.IsDir()
}
func isFile(path string) bool {
return !isDir(path)
}
var tmp string
func getExcelize(excelpath string) *excelize.File {
_, err := os.Stat(excelpath)
if os.IsNotExist(err) {
fmt.Println("创建文件:" + excelpath)
f := excelize.NewFile()
f.SaveAs(excelpath)
}
f, err := excelize.OpenFile(excelpath)
if err != nil {
fmt.Println(err)
}
fmt.Println("打开文件:" + excelpath)
return f
}
func getAxisY(count int) string {
arr := [27]string{"", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}
mod := count % 26
divisor := int(math.Floor(float64(count) / 26))
if mod == 0 && divisor > 0 {
mod = 26
divisor -= 1
}
return arr[divisor] + arr[mod]
}
func main() {
pwd, _ := os.Getwd()
length := 100000
autosave := false
autosavelength := 10000
maxlength := 0
inipath := pwd + "\\config.ini"
input := pwd + "\\input"
output := pwd + "\\output"
_, err := os.Stat(inipath)
if !os.IsNotExist(err) { //能读取到ini
cfg, err := ini.Load(inipath)
if err != nil {
fmt.Println(err)
}
cfg_length, err := cfg.Section("config").Key("length").Int()
if err != nil {
fmt.Println(err)
}
if cfg_length > 0 {
length = cfg_length
}
cfg_autosave, err := cfg.Section("config").Key("autosave").Bool()
if err != nil {
fmt.Println(err)
}
if cfg_autosave == true {
autosave = cfg_autosave
}
cfg_autosavelength, err := cfg.Section("config").Key("autosavelength").Int()
if err != nil {
fmt.Println(err)
}
if cfg_autosavelength > 0 {
autosavelength = cfg_autosavelength
}
cfg_maxlength, err := cfg.Section("config").Key("maxlength").Int()
if err != nil {
fmt.Println(err)
}
if cfg_maxlength > 0 {
maxlength = cfg_maxlength
}
cfg_input := cfg.Section("config").Key("input").String()
if isDir(cfg_input) {
input = cfg_input
}
cfg_output := cfg.Section("config").Key("output").String()
if isDir(cfg_output) {
output = cfg_output
}
}
if !isDir(input) {
fmt.Println("找不到需要处理的input文件夹,请在当前路径下建立input文件夹,或在config.ini的[config]下指定input路径...")
fmt.Scanln(&tmp)
} else {
fileInfoList, err := ioutil.ReadDir(input)
if err != nil {
fmt.Println(err)
fmt.Scanln(&tmp)
}
for i := range fileInfoList {
f := fileInfoList[i].Name()
filenameall := path.Base(f)
filesuffix := strings.ToLower(path.Ext(f))
fileprefix := filenameall[0 : len(filenameall)-len(filesuffix)]
fmt.Println("开始处理[" + fileprefix + "]...")
if filesuffix == ".csv" || filesuffix == ".txt" {
if !isDir(output) {
err := os.Mkdir(output, os.ModePerm)
if err != nil {
fmt.Println(err)
} else {
fmt.Println("创建[output]文件夹...")
}
}
thisoutput := output + "\\" + fileprefix
if !isDir(thisoutput) {
err := os.Mkdir(thisoutput, os.ModePerm)
if err != nil {
fmt.Println(err)
} else {
fmt.Println("在[output]文件夹下创建[" + fileprefix + "]文件夹...")
}
}
count := 0
namer := 1
current := 1
currentexcel := getExcelize(thisoutput + "\\" + fileprefix + strconv.Itoa(namer) + ".xlsx")
csv_file, _ := os.Open(input + "\\" + f)
r := csv.NewReader(csv_file)
r.FieldsPerRecord = -1
r.Comma = ';'
r.LazyQuotes = true
arr := make([]string, 0)
for {
record, err := r.Read()
if err == io.EOF {
currentexcel.Save()
fmt.Println("保存第" + strconv.Itoa(namer) + "个excel...")
break
}
if err != nil {
fmt.Println(err)
}
records := strings.Split(record[0], ",")
if count == 0 {
for _, v := range records {
arr = append(arr, v)
}
for k, v := range arr {
currentexcel.SetCellStr("Sheet1", getAxisY(k+1)+"1", v)
}
} else {
for k, v := range records {
currentexcel.SetCellValue("Sheet1", getAxisY(k+1)+strconv.Itoa(current+1), v)
}
current += 1
}
if autosave && count%autosavelength == 0 {
currentexcel.Save()
fmt.Println("自动保存...")
}
count += 1
if current > length {
fmt.Println("保存第" + strconv.Itoa(namer) + "个excel...")
currentexcel.Save()
namer += 1
currentexcel = getExcelize(thisoutput + "\\" + fileprefix + strconv.Itoa(namer) + ".xlsx")
for k, v := range arr {
currentexcel.SetCellStr("Sheet1", getAxisY(k+1)+"1", v)
}
if maxlength > 0 {
if count > maxlength {
break
}
}
current = 1
}
fmt.Println("处理第" + strconv.Itoa(count) + "条...")
}
}
}
fmt.Println("任务完成,请按回车键退出...")
fmt.Scanln(&tmp)
}
}
接着是改进后的2.0的版本:
package main
import (
"encoding/csv"
"fmt"
"io"
"io/ioutil"
"math"
"os"
"path"
"regexp"
"strconv"
"strings"
"time"
"github.com/360EntSecGroup-Skylar/excelize"
"gopkg.in/ini.v1"
)
func isDir(path string) bool {
s, err := os.Stat(path)
if err != nil {
return false
}
return s.IsDir()
}
func now() string {
n := time.Now()
return n.Format("2006-01-02 15:04:05") + " " //go语言诞生的时间
}
var tmp string
func getExcelize(excelpath string) *excelize.File {
_, err := os.Stat(excelpath)
empty := false
if os.IsNotExist(err) {
empty = true
fmt.Println(now() + "创建文件:[" + excelpath + "]")
f := excelize.NewFile()
f.SaveAs(excelpath)
}
f, err := excelize.OpenFile(excelpath)
if err != nil {
fmt.Println(err)
}
if empty {
fmt.Println(now() + "打开文件:[" + excelpath + "]...")
} else {
fmt.Println(now() + "打开现有文件:[" + excelpath + "],请稍候...")
}
return f
}
func getAxisY(count int) string {
arr := [27]string{"", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}
mod := count % 26
divisor := int(math.Floor(float64(count) / 26))
if mod == 0 && divisor > 0 {
mod = 26
divisor -= 1
}
return arr[divisor] + arr[mod]
}
func main() {
fmt.Println(now() + "任务开始...")
pwd, _ := os.Getwd()
length := 100000
autosave := false
autosavelength := 10000
maxlength := 0
inipath := pwd + "\\config.ini"
input := pwd + "\\input"
output := pwd + "\\output"
_, err := os.Stat(inipath)
if !os.IsNotExist(err) { //能读取到ini
cfg, err := ini.Load(inipath)
if err != nil {
fmt.Println(err)
}
cfg_length, err := cfg.Section("config").Key("length").Int()
if err != nil {
fmt.Println(err)
}
if cfg_length > 0 {
length = cfg_length
}
cfg_autosave, err := cfg.Section("config").Key("autosave").Bool()
if err != nil {
fmt.Println(err)
}
if cfg_autosave == true {
autosave = cfg_autosave
}
match, _ := regexp.MatchString("^[0-9]+$", cfg.Section("config").Key("autosavelength").String())
if match {
cfg_autosavelength, err := cfg.Section("config").Key("autosavelength").Int()
if err != nil {
fmt.Println(err)
}
if cfg_autosavelength > 0 {
autosavelength = cfg_autosavelength
}
}
match, _ = regexp.MatchString("^[0-9]+$", cfg.Section("config").Key("maxlength").String())
if match {
cfg_maxlength, err := cfg.Section("config").Key("maxlength").Int()
if err != nil {
fmt.Println(err)
}
if cfg_maxlength > 0 {
maxlength = cfg_maxlength
}
}
cfg_input := cfg.Section("config").Key("input").String()
if isDir(cfg_input) {
input = cfg_input
}
cfg_output := cfg.Section("config").Key("output").String()
if isDir(cfg_output) {
output = cfg_output
}
}
if !isDir(input) {
fmt.Println("找不到需要处理的input文件夹,请在当前路径下建立input文件夹,或在config.ini的[config]下指定input路径...")
fmt.Scanln(&tmp)
} else {
fileInfoList, err := ioutil.ReadDir(input)
if err != nil {
fmt.Println(err)
fmt.Scanln(&tmp)
}
account := 0
for i := range fileInfoList {
f := fileInfoList[i].Name()
filesuffix := strings.ToLower(path.Ext(f))
if filesuffix == ".csv" || filesuffix == ".txt" {
account += 1
}
}
if account == 0 {
fmt.Println(now() + "没有需要处理的任务,结束...")
}
ii := 0
for i := range fileInfoList {
f := fileInfoList[i].Name()
filenameall := path.Base(f)
filesuffix := strings.ToLower(path.Ext(f))
fileprefix := filenameall[0 : len(filenameall)-len(filesuffix)]
if filesuffix == ".csv" || filesuffix == ".txt" {
ii += 1
fmt.Println(now() + "开始处理第" + strconv.Itoa(ii) + "个文件[" + fileprefix + "],共计" + strconv.Itoa(account) + "个...")
if !isDir(output) {
err := os.Mkdir(output, os.ModePerm)
if err != nil {
fmt.Println(err)
} else {
fmt.Println(now() + "创建[output]文件夹...")
}
}
readlength := 0
csv_file, _ := os.Open(input + "\\" + f)
r := csv.NewReader(csv_file)
r.FieldsPerRecord = -1
r.Comma = ';'
r.LazyQuotes = true
for {
_, err := r.Read()
readlength += 1
if err == io.EOF {
readlength -= 1
break
}
if err != nil {
fmt.Println(err)
}
}
csv_file, _ = os.Open(input + "\\" + f)
r = csv.NewReader(csv_file)
r.FieldsPerRecord = -1
r.Comma = ';'
r.LazyQuotes = true
arr := make([]string, 0)
count := 0
namer := 1
current := 1
var currentexcel *excelize.File
if readlength < length {
currentexcel = getExcelize(output + "\\" + fileprefix + ".xlsx") //如果没那么多条目就不需要分那么多个了,就不需要namer计数
} else {
currentexcel = getExcelize(output + "\\" + fileprefix + "_" + strconv.Itoa(namer) + ".xlsx")
}
for {
record, err := r.Read()
if err == io.EOF {
if length < readlength {
fmt.Println(now() + "保存第" + strconv.Itoa(ii) + "个excel的第" + strconv.Itoa(namer) + "个分文件...")
fmt.Println(now() + "第" + strconv.Itoa(ii) + "个excel全部处理完毕...")
} else {
fmt.Println(now() + "第" + strconv.Itoa(ii) + "个excel处理完毕,保存中,请稍候,剩余" + strconv.Itoa(account-ii) + "个...")
}
currentexcel.Save()
fmt.Println(now() + "第" + strconv.Itoa(ii) + "个excel保存完毕...")
break
}
if err != nil {
fmt.Println(err)
}
records := strings.Split(record[0], ",")
if count == 0 {
for _, v := range records {
arr = append(arr, v)
}
for k, v := range arr {
currentexcel.SetCellStr("Sheet1", getAxisY(k+1)+"1", v)
}
} else {
for k, v := range records {
currentexcel.SetCellValue("Sheet1", getAxisY(k+1)+strconv.Itoa(current+1), v)
}
current += 1
}
if autosave && count%autosavelength == 0 {
currentexcel.Save()
fmt.Println(now() + "自动保存...")
}
count += 1
if current > length {
fmt.Println(now() + "保存第" + strconv.Itoa(ii) + "个excel的第" + strconv.Itoa(namer) + "个分文件...")
currentexcel.Save()
if count >= maxlength-1 || count >= readlength-1 {
break
}
namer += 1
currentexcel = getExcelize(output + "\\" + fileprefix + "_" + strconv.Itoa(namer) + ".xlsx")
for k, v := range arr {
currentexcel.SetCellStr("Sheet1", getAxisY(k+1)+"1", v)
}
current = 1
}
//fmt.Println("处理第" + strconv.Itoa(count) + "条...")
if current%1000 == 0 || current == readlength || (namer*length >= readlength && current == readlength-(namer-1)*length) {
fmt.Println(now() + "处理" + strconv.Itoa(current) + "条...")
}
}
}
}
fmt.Println(now() + "任务完成,请按回车键退出...")
fmt.Scanln(&tmp)
}
}
由于只是小工具,就没有做UI了,有兴趣的就自己用go walk或者go sciter库自己加吧。应该说代码还是很够看的。
最后是下载链接:https://download.csdn.net/download/sinolzeng/15446659