前端时间遇到一个需求，系统实现的功能是查询对账单列表，查询后每一行有个链接，点击可以下载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))
}
}
}
推荐文章
发表评论