excel-swap/main.go

package main

import (
	"encoding/json"
	"fmt"
	"github.com/tealeg/xlsx"
	"strings"
)

//
var origin_file = "C:\\Users\\tangs\\Desktop\\表1_教师招聘_国考合并_替换代码2\\表2_国考_20190322192526接受替换.xlsx"
var reference_file = "C:\\Users\\tangs\\Desktop\\表1_教师招聘_国考合并_替换代码2\\表1_国考_代号转换表.xlsx"
var result_file = "./result2.xlsx"

//var origin_file = "C:\\Users\\tangs\\Desktop\\表1_教师招聘_国考合并_替换代码\\表2_招聘3-20180204145207_接受替换.xlsx"
//var reference_file = "C:\\Users\\tangs\\Desktop\\表1_教师招聘_国考合并_替换代码\\表1_教师招聘_国考合并_替换代码.xlsx"
//var result_file = "./result.xlsx"

func compose_reference_file(reference_file string) map[string]map[string]string {
	r_f, err := xlsx.OpenFile(reference_file)
	if err != nil {
		panic(err)
	}

	var result = map[string]map[string]string{}
	sheet := r_f.Sheets[0]
	for row_index, row := range sheet.Rows {
		if row_index == 0 {
			continue
		}
		var key = ""
		for cell_index, cell := range row.Cells {
			value := strings.TrimSpace(cell.Value)
			value = strings.Replace(value, "—", "-", -1)
			//value = strings.Replace(value, "，", ",", -1)
			if cell_index == 0 {
				key = value
				if _, ok := result[key]; !ok {
					result[key] = map[string]string{}
				}
				continue
			}

			if cell_index == 1 {
				result[key]["first"] = value
				continue
			}

			if cell_index == 2 {
				result[key]["second"] = value
				continue
			}
		}
	}
	return result
}

func compose_reference_file2(reference_file string) map[string]string {
	o_f, err := xlsx.OpenFile(reference_file)
	if err != nil {
		panic(err)
	}
	sheet := o_f.Sheets[0]
	var result = map[string]string{}
	for row_index, row := range sheet.Rows {
		if row_index == 0 {
			continue
		}
		var key = ""
		//fmt.Println("first", row.Cells[0].Value, row.Cells[1].Value)
		for cell_index, cell := range row.Cells {
			value := strings.TrimSpace(cell.Value)
			if cell_index == 0 {
				fmt.Println("key ---- " + value)
				key = value
				continue
			}
			if cell_index == 1 {
				fmt.Println("keeeey --- " + key + " --- " + value)
				result[key] = value
			}
		}
	}
	return result
}

func swap(origin_file, reference_file string) {

	//
	//reference := compose_reference_file(reference_file)
	reference := compose_reference_file2(reference_file)
	bys, _ := json.Marshal(reference)
	fmt.Println(string(bys))

	o_f, err := xlsx.OpenFile(origin_file)
	if err != nil {
		panic(err)
	}

	sheet := o_f.Sheets[0]
	for row_index, row := range sheet.Rows {
		if row_index == 0 {
			continue
		}

		for cell_index, cell := range row.Cells {
			if cell_index != 9 {
				continue
			}
			value := strings.TrimSpace(cell.Value)
			value = strings.Replace(value, "—", "-", -1)
			value = strings.Replace(value, "，", ",", -1)
			new_value := value
			//new_value = real_swap(value, reference, true)
			//new_value = real_swap(new_value, reference, false)
			new_value = real_swap2(value, reference)
			fmt.Println(new_value)
			cell.Value = new_value
		}
	}

	err = o_f.Save(result_file)
	if err != nil {
		panic(err)
	}
}

func real_swap2(value string, reference map[string]string) string {
	multi := strings.Split(value, ",")
	temp_value := ""
	for _, single_value := range multi {
		ref, ok := reference[single_value]
		if !ok {
			temp_value = temp_value + single_value + ","
			continue
		}
		temp_value = temp_value + ref + ","
	}
	return strings.TrimSuffix(temp_value, ",")
}

func real_swap(value string, reference map[string]map[string]string, is_log bool) string {
	multi := strings.Split(value, ",")
	swap_value := value
	temp_value := ""
	for _, single_value := range multi {
		ref, ok := reference[single_value]
		if !ok {
			if is_log {
				fmt.Println(single_value + "===" + value)
			}
			single_value = add_prefix_zero(single_value)
			temp_value = temp_value + single_value + ","
			continue
		}
		if reference[single_value]["second"] != "" {
			temp_value = temp_value + ref["second"] + ","
			continue
		}

		if reference[single_value]["first"] != "" {
			temp_value = temp_value + ref["first"] + ","
			continue
		}
	}

	if temp_value != "" {
		swap_value = strings.TrimSuffix(temp_value, ",")
	}

	return swap_value
}

func add_prefix_zero(value string) string {
	multi := strings.Split(value, "-")
	var first = ""
	var second = ""
	var last = ""

	if len(multi) == 2 {
		first = multi[0]
		second = multi[1]
		if len(first) == 2 {
			first = "0" + first
		}
		if len(first) == 1 {
			first = "00" + first
		}

		if len(second) == 1 {
			second = "0" + second
		}
		if len(second) == 0 {
			second = "00" + second
		}
		last = first + "-" + second
	} else if len(multi) == 1 {
		if strings.Index(value, "-") > 0 {
			first = multi[0]
			if len(first) == 2 {
				first = "0" + first
			}
			if len(first) == 1 {
				first = "00" + first
			}
			last = first + "-00"
		}
	} else {
		last = value
	}

	return last
}

func check_reference_file(reference_file string) {
	r_f, err := xlsx.OpenFile(reference_file)
	if err != nil {
		panic(err)
	}

	sheet := r_f.Sheets[0]
	var mark = map[string]int{}
	var length = 0
	for index, row := range sheet.Rows {
		if index == 0 {
			continue
		}
		for cell_index, cell := range row.Cells {
			if cell_index != 0 {
				break
			}
			value := strings.TrimSpace(cell.Value)
			if value == "" {
				continue
			}
			length++
			mark[value] = mark[value] + 1
		}
	}

	fmt.Printf("xlsx length: %v, mark length: %v", length, len(mark))

	for k, v := range mark {
		if v > 1 {
			fmt.Println(k, v)
		}
	}
}

func main() {

	// check data in reference_file
	check_reference_file(reference_file)

	swap(origin_file, reference_file)

}