This article introduces how to use Go language to operate Excel, to simplify some of the more tedious mechanical operations in life.
There are four versions of changes, from basic functionality implementation to progressive modifications. The basic features and the fourth edition provide complete code.
In the summary, I gave the final use of the complete code, if you need, you can refer to and modify the use.
- The cause of
- Train of thought
- First edition: Basic features
- Second edition: Regular matching
- Third edition: Multiple assignments
- Fourth Edition: Final edition
- conclusion
Note that the final version of version 4 and the version I used at the end have changed slightly. The version I finally used added multiple class registrations and archiving of processed work files as required by the teacher. Readers who do not need these features can simply look at version 4 code.
The cause of
Last night when I came back to the lab, I found that my classmates were recording the situation of undergraduate students submitting their homework to the teacher.
There is probably a list of files, file name is the student number, name, a number of assignments.
In addition, there is an Excel with a list of students, in which the information of the students who have submitted homework should be recorded.
It looks something like this:
Using nine students as an example, my classmate actually had to deal with over 600 submissions.
More than 600 students, one by one to search, and then to tick, think about scalp tingling.
A programmer has a programmer’s way of doing things.
Train of thought
We need to read the list of files, and then check the submission of the corresponding student number in Excel.
The steps are simply divided into two steps:
- Read the file list, from which the student number information.
- Read the Excel file and record it in the cell of the line corresponding to the student number.
There is an optimized detail:
Every time we get a student number, we need to search the entire table to know which row the student number is in.
Even when students are not in the class, searching the entire table yields nothing, wasting time and computing resources.
Therefore, when we read the table, we will cache the information of student number and line number, including key student number and value line number.
First edition: Basic features
Read the Excel file and cache the line numbers
To operate Excel, you need to use the “github.com/tealeg/xlsx” library. Be sure to import your own. The full code will be provided at the end of the article.
In order to facilitate the reader to understand the implementation of the idea, all error handling has been omitted, if you need to see the full code behind.
tablePath := "E:\\Desktop\\ New folder \\ log table. XLSX"
// Get the table sheet
table, err := xlsx.OpenFile(tablePath)
sheet := table.Sheets[0]
// Cache table contents. Key is the student number and value is the line number
m := map[int]int{}
for index, row := range sheet.Rows{
number, err := strconv.Atoi(row.Cells[0].String())
iferr ! =nil {
continue
}
m[number] = index
}
Copy the codeRead the file list, and extract the student number
Because the file name is fixed format, we can directly get the student number
homeworkPath := "E:\\Desktop\\ New folder \\ Homework"
files, err := os.ReadDir(homeworkPath)
for _, file := range files {
number := file.Name()[15:27]
fmt.Println(number)
}
Copy the codeOutput the student id as follows:
12345678901
12345678902
12345678903
12345678904
12345678905
12345678906
12345678907
12345678908
12345678909
Copy the codeFill the excel table
Let’s change the fmt.pirntln () function in the above code to fill in the table:
for _, file := range files {
number, _ := strconv.Atoi(file.Name()[16:27])
index, ok := m[number]
if ok == false {
continue
}
/ / box
sheet.Rows[index].Cells[5].SetString("Tick")}Copy the codeSave the excel
err = table.Save(tablePath)
Copy the codeThe complete code
package main
import (
"github.com/tealeg/xlsx"
"os"
"strconv"
)
func main(a) {
tablePath := "E:\\Desktop\\ New folder \\ log table. XLSX"
// Get the table sheet
table, err := xlsx.OpenFile(tablePath)
iferr ! =nil {
panic(err)
}
sheet := table.Sheets[0]
// Cache table contents. Key is the student number and value is the line number
m := map[int]int{}
for index, row := range sheet.Rows{
number, err := strconv.Atoi(row.Cells[0].String())
iferr ! =nil {
continue
}
m[number] = index
}
homeworkPath := "E:\\Desktop\\ New folder \\ Homework"
files, err := os.ReadDir(homeworkPath)
iferr ! =nil {
panic(err)
}
for _, file := range files {
number, _ := strconv.Atoi(file.Name()[16:27])
index, ok := m[number]
if ok == false {
continue
}
/ / box
sheet.Rows[index].Cells[5].SetString("Tick")}// Save the table file
err = table.Save(tablePath)
iferr ! =nil {
panic(err)
}
}
Copy the codeRun it and let’s look at the table:
Perfect! Directly all the students all hook up!
Second edition: Regular matching
However, the ideal is very rich, the reality is very skinny, not all students are in accordance with the uniform format to submit homework, in fact, may be submitted in this form.
As can be seen, the jiafu student has his own ideas, his student number is extracted in the front, so we can not be fixed by the following table to extract his student number.
So, we have to adopt a new way to extract student numbers from file names.
After observation, we found that student numbers are fixed length, such as 11 digits.
We can use regular expressions to extract student numbers.
// Use the regular expression to match the 11-digit student number
reg := regexp.MustCompile(` [0-9] {11} `)
for _, file := range files {
number, _ := strconv.Atoi(reg.FindString(file.Name()))
/ /... Omit invariable parts
}
Copy the codeIn this way, no matter where the student number is placed, I can correctly extract the student number.
Third edition: Multiple assignments
Careful students have found that there is not only the first homework in the section, but also the second homework.
As one, the above code can only register the first job. To register the second job, you have to modify the code again.
As a programmer, of course, this mechanical work is left to the code.
Suppose that now the student submits more documents, including the second assignment:
We define an array or slice of job batches, for example: [” first job “, “second job “]
This way, we can tell the job number based on which string the file name contains.
If it’s your first job, insert into the cell in excel column 6 (table 5 under index).
If it’s the second job, insert it into the cell in excel column 7 (table 6 under index).
homeworks := []string{"Homework One"."Second assignment"}
// Use the regular expression to match the 11-digit student number
reg := regexp.MustCompile(` [0-9] {11} `)
for _, file := range files {
fileName := file.Name()
for homeworkIndex, homework := range homeworks {
if strings.Contains(fileName, homework) {
number, _ := strconv.Atoi(reg.FindString(file.Name()))
index, ok := m[number]
if ok == false {
continue
}
/ / box
sheet.Rows[index].Cells[homeworkIndex + 5].SetString("Tick")}}}Copy the codeComplete steps explained:
- Determine if each file name contains fields from the first or second job.
- If yes, then
homeworkIndexUsed to record the inserted column with an offset of5.
The end of operation is shown in the figure below:
Perfect! No matter where the student put the student number, we just need to run the sequence program to register the two assignments.
Fourth Edition: Final edition
If you think it’s over by the third edition, you’re wrong.
What the students pay attention to now is the all-round development of morality, intelligence, body, beauty and labor, and carry forward the personality!
After I used the above program to process the files of the 600 students, I found that there were still more than 200 students who were not registered!
I saw that some students named the first assignment as the first assignment.
Dude, I’m straight dude! This is not according to the teacher’s requirements, this is I did not think of.
Continue to modify the code, we will make the homework section into two-dimensional, so that there is no fear of students in a batch of homework to change the flower name.
homeworkSlice := [][]string{{"Homework One"."Assignment number one"},
{"Second assignment"."Assignment 2"}}
// Use the regular expression to match the 11-digit student number
reg := regexp.MustCompile(` [0-9] {11} `)
for _, file := range files {
fileName := file.Name()
// homeworkIndex indicates the number of jobs
for homeworkIndex, homeworks := range homeworkSlice {
// There may be different names in the same batch
for _, homework := range homeworks {
if strings.Contains(fileName, homework) {
number, _ := strconv.Atoi(reg.FindString(file.Name()))
index, ok := m[number]
if ok == false {
continue
}
/ / box
sheet.Rows[index].Cells[homeworkIndex + 5].SetString("Tick")}}}}Copy the codeRun the above code again, and you have basically processed all the job files.
The complete code
package main
import (
"github.com/tealeg/xlsx"
"os"
"regexp"
"strconv"
"strings"
)
func main(a) {
tablePath := "E:\\Desktop\\ New folder \\ log table. XLSX"
// Get the table sheet
table, err := xlsx.OpenFile(tablePath)
iferr ! =nil {
panic(err)
}
sheet := table.Sheets[0]
// Cache table contents. Key is the student number and value is the line number
m := map[int]int{}
for index, row := range sheet.Rows{
number, err := strconv.Atoi(row.Cells[0].String())
iferr ! =nil {
continue
}
m[number] = index
}
homeworkPath := "E:\\Desktop\\ New folder \\ Homework"
files, err := os.ReadDir(homeworkPath)
iferr ! =nil {
panic(err)
}
homeworkSlice := [][]string{{"Homework One"."Assignment number one"},
{"Second assignment"."Assignment 2"}}
// Use the regular expression to match the 11-digit student number
reg := regexp.MustCompile(` [0-9] {11} `)
for _, file := range files {
fileName := file.Name()
for homeworkIndex, homeworks := range homeworkSlice {
for _, homework := range homeworks {
if strings.Contains(fileName, homework) {
number, _ := strconv.Atoi(reg.FindString(file.Name()))
index, ok := m[number]
if ok == false {
continue
}
/ / box
sheet.Rows[index].Cells[homeworkIndex + 5].SetString("Tick")}}}}// Save the table file
err = table.Save(tablePath)
iferr ! =nil {
panic(err)
}
}
Copy the codeconclusion
To write an automated tool, you still have to account for all sorts of situations, but because Go is so easy to use, it’s easy to modify.
Since it was used temporarily, I did not extract each parameter into the configuration file or the command line parameter, so the whole program seems to lack flexibility. If the teacher needs a perfect tool, he may spend time to perfect it later.
Finally, present a complete code, complete functions as follows:
- Can record for multiple classes, each class corresponding to an Excel sheet
- Multiple batches of jobs can be registered
- The registered assignments are archived by class and batch
If you need to use this code, make sure you back up your source files and change the offset.
package main
import (
"fmt"
"github.com/tealeg/xlsx"
"os"
"regexp"
"strconv"
"strings"
)
func main(a) {
// The folder where the job files are located
homeworkPath := "E: \ \ Desktop \ \"
// Enter the folder where the table is sitting
tablePath := "E:\\Desktop\\"
// enter the table name
classSlice := []string{"2.xlsx"."1.xlsx"}
// Name the job
homeworkSlice := [][]string{{"Homework One"."Assignment number one"."The first time"},
{"Second assignment"."Assignment 2"."The second time"}}
// Record the job submission
for _, className := range classSlice {
for homeworkIndex, homeworks := range homeworkSlice {
checkHomework(homeworkPath, tablePath + className, homeworks, homeworkIndex)
}
}
}
func checkHomework(homeworkPath string, tablePath string, homeworks []string, homeworkIndex int) {
// Get the table sheet
table, err := xlsx.OpenFile(tablePath)
iferr ! =nil {
panic(err)
}
sheet := table.Sheets[0]
// Cache table contents. Key is the student number and value is the line number
m := map[int]int{}
for index, row := range sheet.Rows{
number, err := strconv.Atoi(row.Cells[0].String())
iferr ! =nil {
continue
}
m[number] = index
}
// Read the list of job files
files, err := os.ReadDir(homeworkPath)
iferr ! =nil {
panic(err)
}
// Record the number of files that have been processed
count := 0
// Use the regular expression to match the 11-digit student number
reg := regexp.MustCompile(` [0-9] {11} `)
// Iterate over the list of job files
for _, file := range files {
fileName := file.Name()
// Each assignment may have a different name, e.g., first assignment, first assignment
for _, homework := range homeworks {
if strings.Contains(fileName, homework) {
// Retrieve the student id from the file name
number, _ := strconv.Atoi(reg.FindString(fileName))
index, ok := m[number]
if ok == false {
continue
}
/ / box
sheet.Rows[index].Cells[homeworkIndex + 5].SetString("Tick")
// Move the processed files to the folder corresponding to the class batch
newPath := tablePath[:len(tablePath) - 5] + "Ben-no" + strconv.Itoa(homeworkIndex + 1) + "Secondary assignment"
mkdir(newPath)
err = os.Rename(homeworkPath + "\ \" + fileName, newPath + "\ \" + fileName)
iferr ! =nil {
panic(err)
}
count++
break
}
}
}
fmt.Println(count, tablePath, homeworks)
// Save the table file
err = table.Save(tablePath)
iferr ! =nil {
panic(err)
}
}
// Create a folder
func mkdir(path string) {
_, err := os.Stat(path)
if os.IsNotExist(err) {
err = os.Mkdir(path, os.ModePerm)
iferr ! =nil {
panic(err)
}
}
}
Copy the code