Export an Excel recognized CSV in Golang

I haven’t updated my blog for a long time. Just a few lines… Otherwise I wouldn’t be able to write.

It is well known that exporting tables is a very common requirement in the middle and background, and The Golang official library provides Encoding/CSV

The simplest application is clearly stated in the official:

package main

import (
	"encoding/csv"
	"log"
	"os"
)

func main(a) {
	records := [][]string{{"first_name"."last_name"."username"},
		{"Rob"."Pike"."rob"},
		{"Ken"."Thompson"."ken"},
		{"Robert"."Griesemer"."gri"},
	}

	w := csv.NewWriter(os.Stdout)

	for _, record := range records {
		iferr := w.Write(record); err ! =nil {
			log.Fatalln("error writing record to csv:", err)
		}
	}

	// Write any buffered data to the underlying writer (standard output).
	w.Flush()

	iferr := w.Error(); err ! =nil {
		log.Fatal(err)
	}
}
Copy the code

It looks like there’s nothing wrong with the idea, don’t we just write in sequence?

It seems so. Just do it.

Consider this first: So the type passed in should be an interface{} or []interface{}. So let’s write a function that converts any type to a string. So that the official CSV function can be used:

func interfaceSliceToStringSlice(dataList []interface{}) (strList []string) {
	for _, data := range dataList {
		strList = append(strList, fmt.Sprintf("%v", data))
	}
	return
}
Copy the code

And then you call it, and you process the header and each row one by one, and you’re done, right?

func (s *Service) toCsv(ctx context.Context, header []interface{}, dataList [][]interface{}) (result []byte, err error) {
	buffer := bytes.NewBuffer(nil)
	record := csv.NewWriter(buffer)
	ifheader ! =nil {
		err = record.Write(interfaceSliceToStringSlice(header))
		iferr ! =nil {
			log.Error("toCsv Header Error: %v", err)
			return}}for _, data := range dataList {
		writeErr := record.Write(interfaceSliceToStringSlice(data))
		ifwriteErr ! =nil {
			err = writeErr
			log.Error("toCsv Content Error: %v", err)
			return
		}
	}
	record.Flush()
	iferr = record.Error(); err ! =nil {
		log.Error("toCsv Flush error: %v", err)
		return
	}
	result = buffer.Bytes()
	return
}
Copy the code

WPS and Numbers are fine. I checked the Excel garble on the Internet. It said that the default unicode parsing is used, but we packed UTF8, so the encoding recognition is wrong. I was confused: UTF8 is a kind of Unicode, isn’t it?

The solution is to do one thing, and that is to push into a BOM header. What is a BOM header? — Specifies the file encoding.

If it is UTF8, the header \xef\ XBB \ XBF will be recognized in Excel.

The following table shows the BOM head relationship:

The BOM header is just like the content/ Type header. There are several reasons for this:

1. BOM header adds unwanted space
2. Protocol restrictions (mainly in recognizing BOM headers as characters or headers)

Based on this, adding BOM head still needs to consider some compatibility issues, especially in the communication between programs, how to deal with THE BOM head may be a big problem, or to choose “whether to add BOM head” according to local conditions.

Therefore, a parameter options was finally added to configure whether or not to use the BOM header to make the function more generic and suitable for different scenarios.

References:

• Unicode.org/faq/utf_bom…