Golang实现excel文件下载并解析（xls格式）

前端时间遇到一个需求，系统实现的功能是查询对账单列表，查询后每一行有个链接，点击可以下载excel格式（xls格式，2003格式的）的对账单文件，对账单excel文件里面有对应客户对应期间的对账单余额。但是有个财务有需求，需要把600多个客户的对账单余额，进行统计。由于客户数量多，不可能一个个查询、下载excel文件，并统计，因此用golang开发了如下格式的，进行数据取数。

对账单excel格式的，数据区域大概如下图所示：

 

如下示例代码，读取的就是黄色背景的两个单元格的数据

package main

import (
	"bufio"
	"fmt"
	"github.com/extrame/xls"
	"io"
	"log"
	"net/http"
	"net/url"
	"os"
)

// DownFile 通过Http下载文件
func DownFile(excelUrl string, fileSaveName string) (string, error) {
	params := url.Values{}
	parseURL, err := url.Parse(excelUrl)
	if err != nil {
		log.Println("err")
	}
	params.Set("id", "=qlpVQlfx0ZX7TvcpNe**************************==")
	//如果参数中有中文参数,这个方法会进行URLEncode
	parseURL.RawQuery = params.Encode()
	urlPathWithParams := parseURL.String()
	//res, httpErr := http.Get(urlPathWithParams)

	req, err := http.NewRequest(http.MethodGet, urlPathWithParams, nil)
	if err != nil {
		log.Println("err")
	}
	// 添加请求头
	req.Header.Add("Content-type", "accept: application/json, text/plain, */*")

	req.Header.Add("accept-encoding", "gzip, deflate, br")
	req.Header.Add("accept-language", "zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6")
	req.Header.Add("authorization", "bearer 5519266a-*******-*****-****-****")
	// 添加cookie
	/*cookie1 := &http.Cookie{
		Name:  "aaa",
		Value: "aaa-value",
	}
	req.AddCookie(cookie1)*/

	client := http.Client{}
	//HTTP代理
	/*proxy := "http://127.0.0.1:8080/"
	proxyAddress, _ := url.Parse(proxy)
	client := &http.Client{
		Transport: &http.Transport{
			Proxy: http.ProxyURL(proxyAddress),
		},
	}*/

	// 发送请求
	res, httpErr := client.Do(req)

	if httpErr != nil {
		return "Http Error", httpErr
	}

	defer res.Body.Close()
	reader := bufio.NewReaderSize(res.Body, 64*1024) // 获得reader对象
	file, createErr := os.Create(fileSaveName)
	if createErr != nil {
		return "Create Error", createErr
	}

	writer := bufio.NewWriter(file) // 获得writer对象
	_, copyErr := io.Copy(writer, reader)
	if copyErr != nil {
		return "Copy Error", copyErr
	}

	return fileSaveName, nil
}

// 请求 URL: https://**.**.**.**/customerReconciliation?page=0&size=10&companyCode=****&custCode=ZT****&statementMonth=&statementStatus=&saleOrgCode=&beginDateScope=&productLineCodeList=&startDate=&endDate=&label=*****%E5%8F%B8
func main() {
	filePath := "E:\\WorkDocument\\temp\\file111.xls"
	//DownFile("https://**.**.**.**/customerReconciliation/exportException", filePath)
	fmt.Println("download excel finished")

	/*
		//xlsx read
		file, err := excelize.OpenFile(filePath)
		if err != nil {
			log.Fatalf("open excel file err, error:%+v\n", err)
		}

		sheetName := file.GetSheetName(0)

		rows, err := file.GetRows(sheetName)

		for index, row := range rows {
			// 跳过第一行标题
			if index <= 7 {
				continue
			}

			fmt.Println("row data: " + row[1])
		}
	*/
	xlsFile, err := xls.Open(filePath, "utf-8")
	if err != nil {
		log.Fatal(err)
	}
	// 获取xls文件的第一个sheet
	sheet := xlsFile.GetSheet(0)
	// 从第9行开始，遍历xls文件，然后按行调用insertRowFromXls函数
    // 如下根据自己的实际需要进行具体的实现，即可
	for j := 8; j < int(sheet.MaxRow)+1; j++ {
		xlsRow := sheet.Row(j)

		cusNameRow := sheet.Row(3)
		cusName := cusNameRow.Col(2)

		//rowColCount := xlsRow.LastCol()
		//insertRowFromXls(xlsxSheet, xlsRow, rowColCount)
		//fmt.Println("row count: " + strconv.Itoa(rowColCount))
		col1Data := xlsRow.Col(1)
		//fmt.Println("row col1 data: " + col1Data)
		if col1Data == "合计" {
			xlsRowInit := sheet.Row(j + 3)
			initAmount := xlsRowInit.Col(2)
			fmt.Println(cusName + ": " + initAmount + ", " + xlsRow.Col(9))
		}
	}

}