使用的库
go get github.com/tealeg/xlsx
需要将原文件中的三个标签页合并,并将标签页名字也就是日期加到每行的后面,并将数据大于7200的数据标红。
原文件
处理前.png
处理后文件
处理后.png
1、将三个标签页放到一个标签页中;
2、在第二个文件中新加一列时间,内容为第一个文件的标签页名;
3、将值大于7200的内容标红颜色。
package main
import (
"github.com/tealeg/xlsx"
"fmt"
"strconv"
)
type ora struct {
corp string //单位
name string //业务系统
name2 string //进程名
v1000 string //10点值
v2000 string //20点值
H string // 最大值
L string // 最小值
A string // 平均值
TIME string // 时间
}
func readXlsx(filename string) []ora {
var listOra []ora
xlFile, err := xlsx.OpenFile(filename)
if err != nil {
fmt.Printf("open failed: %s\n", err)
}
for _, sheet := range xlFile.Sheets {
//fmt.Printf("Sheet Name: %s\n", sheet.Name)
tmpOra := ora{}
// 获取标签页(时间)
//tmpOra.TIME = sheet.Name
for _, row := range sheet.Rows {
var strs []string
for _, cell := range row.Cells {
text := cell.String()
strs = append(strs, text)
}
// 获取标签页(时间)
tmpOra.TIME = sheet.Name
tmpOra.corp = strs[0]
tmpOra.name = strs[1]
tmpOra.name2 = strs[2]
tmpOra.v1000 = strs[3]
tmpOra.v2000 = strs[4]
tmpOra.H = strs[5]
tmpOra.L = strs[6]
tmpOra.A = strs[7]
listOra = append(listOra, tmpOra)
}
}
return listOra
}
func main() {
var name string
fmt.Printf("写入需要处理的文件名: ")
fmt.Scanf("%s", &name)
//excelFileName := "C:\\Users\\llw98\\Desktop\\灾备数据库复制总量\2019円-04-26-2019年05月02日Lag延时数据.xlsx"
excelFileName := name
oraList := readXlsx(excelFileName)
writingXlsx(oraList)
}
func writingXlsx(oraList []ora) {
var file *xlsx.File
var sheet *xlsx.Sheet
var row *xlsx.Row
var cell *xlsx.Cell
var err error
file = xlsx.NewFile()
sheet, err = file.AddSheet("Sheet1")
if err != nil {
fmt.Printf(err.Error())
}
row = sheet.AddRow()
row.SetHeightCM(0.5)
cell = row.AddCell()
cell.Value = "单位"
cell = row.AddCell()
cell.Value = "业务系统"
cell = row.AddCell()
cell.Value = "进程名"
cell = row.AddCell()
cell.Value = "V1000"
cell = row.AddCell()
cell.Value = "V2000"
cell = row.AddCell()
cell.Value = "H"
cell = row.AddCell()
cell.Value = "L"
cell = row.AddCell()
cell.Value = "A"
cell = row.AddCell()
cell.Value = "TIME"
for _, i := range oraList {
if i.corp == "单位"{
continue
}
// 判断是否为-9999,是的变为0.0
var row1 *xlsx.Row
if i.v1000 == "-9999" {
i.v1000 = "0.0"
}
if i.v2000 == "-9999" {
i.v2000 = "0.0"
}
if i.H == "-9999" {
i.H = "0.0"
}
if i.L == "-9999" {
i.L = "0.0"
}
row1 = sheet.AddRow()
row1.SetHeightCM(0.5)
cell = row1.AddCell()
cell.Value = i.corp
cell = row1.AddCell()
cell.Value = i.name
cell = row1.AddCell()
cell.Value = i.name2
// 判断值是大于7200,大于变成红色
v1, _ := strconv.ParseFloat(i.v1000, 64)
if v1 > 7200 {
cell = row1.AddCell()
cell.Value = i.v1000
cell.GetStyle().Font.Color = "00FF0000"
} else {
cell = row1.AddCell()
cell.Value = i.v1000
}
//v2, _ := strconv.Atoi(i.v2000)
v2, _ := strconv.ParseFloat(i.v2000, 64)
if v2 > 7200 {
cell = row1.AddCell()
cell.Value = i.v2000
cell.GetStyle().Font.Color = "00FF0000"
} else {
cell = row1.AddCell()
cell.Value = i.v2000
}
//vH, _ := strconv.Atoi(i.H)
vH, _ := strconv.ParseFloat(i.H, 64)
if vH > 7200 {
cell = row1.AddCell()
cell.Value = i.H
cell.GetStyle().Font.Color = "00FF0000"
}else {
cell = row1.AddCell()
cell.Value = i.H
}
//vL, _ := strconv.Atoi(i.L)
vL, _ := strconv.ParseFloat(i.L, 64)
if vL > 7200 {
cell = row1.AddCell()
cell.Value = i.L
cell.GetStyle().Font.Color = "00FF0000"
} else {
cell = row1.AddCell()
cell.Value = i.L
}
//vA, _ := strconv.Atoi(i.A)
vA, _ := strconv.ParseFloat(i.A, 64)
if vA > 7200 {
cell = row1.AddCell()
cell.Value = i.A
cell.GetStyle().Font.Color = "00FF0000"
} else {
cell = row1.AddCell()
cell.Value = i.A
}
// 打印时间
cell = row1.AddCell()
cell.Value = i.TIME
}
err = file.Save("2019-_-_-2019-_-_Lag延时数据.xlsx")
if err != nil {
fmt.Printf(err.Error())
}
}