Golang操作excel文件

原文件

处理前.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("Please enter your file name: ")
    fmt.Scanf("%s", &name)
    //excelFileName := "C:\\Users\\llw98\\Desktop\\灾备数据库复制总量\\2019-04-26-2019-05-02Lag延时数据.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())
    }
}