Golang操作excel文件

原文件

处理后文件

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("Please enter your file name: ")
 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())
 }
}

有疑问加站长微信联系(非本文作者)

• 请尽量让自己的回复能够对别人有帮助
• 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
• 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
• 图片支持拖拽、截图粘贴等方式上传