Excel指定列值将一行变多行工具

下载地址

链接: https://pan.baidu.com/s/1P4PBQ_BQ9d8vHf6s22L6bg 密码: ridf

参数说明及使用方法

处理Excel文件中某列的值拆分成多行其他列值不变
        Usage: oneToMoreLine -i xx.xlsx -n 3
Options:
  -h    查看帮助
  -i string
        需要处理的Excel文件
  -n int
        指定需要拆分的成多行的列序号 (default -1)
  -o string
        设置处理结果保存的位置result.xlsx (default "result.xlsx")

go编写的源码

package main

import (
	"errors"
	"flag"
	"fmt"
	"github.com/360EntSecGroup-Skylar/excelize/v2"
	"os"
	"path"
	"path/filepath"
	"strconv"
	"strings"
	"time"
)


var (
	h bool
	input string
	output string
	num int

)
func usage(){
	fmt.Fprintf(os.Stderr, `处理Excel文件中某列的值拆分成多行其他列值不变
	Usage: oneToMoreLine -i xx.xlsx -n 3
Options:
`)
	flag.PrintDefaults()
}

func init(){
	flag.BoolVar(&h, "h", false, "查看帮助")
	flag.StringVar(&input,"i","","需要处理的Excel文件")
	flag.StringVar(&output,"o","result.xlsx","设置处理结果保存的位置result.xlsx")
	flag.IntVar(&num,"n",-1,"指定需要拆分的成多行的列序号")
	flag.Usage=usage
}
// 判断所给路径文件/文件夹是否存在
func Exists(path string) bool {
	_, err := os.Stat(path)    //os.Stat获取文件信息
	if err != nil {
		if os.IsExist(err) {
			return true
		}
		return false
	}
	return true
}

// 判断所给路径是否为文件夹
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)
}


func readXlsx(filename string)([][]string,error){
	if Exists(filename){
		if !IsFile(filename){
			fmt.Println("[Error] 不是一个有效的文件: "+filename)
			return nil,errors.New("[Error] 不是一个有效的文件: "+filename)
		}
		f,err := excelize.OpenFile(filename)
		sheetName :=f.GetSheetName(0)
		fmt.Println("[Info] Will process data of sheet: "+sheetName)
		if err!=nil{
			fmt.Println("[Error] 不是一个有效的Excel文件: "+filename)
			return nil,errors.New("[Error] 不是一个有效的Excel文件: "+filename)
		}
		rows,err:=f.GetRows(sheetName)
		return  rows,nil
	}else{
		fmt.Println("[Error] 文件不存在: "+filename)
		return nil,errors.New("[Error] 文件不存在: "+filename)
	}
}

//一行变多行处理函数
func oneToMoreLine(excelData [][]string,num int)([][]string,error){

	var retRows=[][]string{}
	for idx,row := range excelData{
		if idx ==0{
			continue
		}
		if len(row)<num{
			fmt.Printf("[Warning] 数据格式不正确: Excel第 %d 行，col:%s",idx+1,row[0])
			continue
		}
		tmpRow := row[:num-1]
		for _,cell :=range (strings.Split(row[num-1]," ")){
			//fmt.Println(cell)
			newRow:=append(tmpRow,cell)
			newRow=append(newRow,row[num:]...)
			//fmt.Println(newRow)
			retRows=append(retRows,newRow)
		}
	}
	newRows :=excelData[:1]

	return append(newRows,retRows...),nil
}

//保存文件
func saveExcel(filename string,outFileName string,rows [][]string)(string,error){
	if outFileName==""{
		pathName,fileName:=filepath.Split(filename)
		outFileName=path.Base(fileName)
		fileSuffix := path.Ext(outFileName)
		outFileName =strings.TrimSuffix(outFileName , fileSuffix)
		outFileName =outFileName+time.Now().Format("2006-01-02_15_04_05")+"result.xlsx"
		outFileName = filepath.Join(pathName,outFileName)
	}

	f, err := excelize.OpenFile(filename)
	if err != nil{
		//fmt.Println(err)
		return "",err
	}
	newSheet:=time.Now().Format("2006-01-02_15_04_05")+"_result"
	f.NewSheet(newSheet)
	for idx,r :=range rows{
		startEnd:="a"+string(strconv.Itoa(idx+1))
		err=f.SetSheetRow(newSheet,startEnd,&r)
	}

	err =f.SaveAs(outFileName)
	if err!=nil{
		fmt.Println(err)
	}
	return  outFileName,nil

}

func main(){
	
	flag.Parse()
	if h {
		flag.Usage()
		return
	}
	if( input=="" || output==""){
		fmt.Println("[Error] 参数不能未空！")
		return
	}
	fmt.Println("[Info] Set input File: "+input)
	fmt.Println("[Info] Set output File: "+output)
	fmt.Println("[Info] Set column number: "+strconv.Itoa(num))
	excelData,err :=readXlsx(input)
	if err!=nil{
		//fmt.Println(err)
		return
	}
	if num==-1{
		fmt.Println("[Error] 无效的列值")
		return
	}

	retData,err:=oneToMoreLine(excelData,num)
	result,err:=saveExcel(input,output,retData)
	fmt.Println("[Info] Save as file: "+result)

}