Search code examples
goexcelize

Excelize pkg.go Golang

I'm using the excelize packaging to manipulate excel files. I am having a problem with the setcellformula func, it does not apply the formula. I paste a basic example, where I was just trying

func main() {

    f := excelize.NewFile()

    f.SetCellValue("Sheet1", "A1", "ID")
    f.SetCellValue("Sheet1", "B1", "Nome")
    f.SetCellValue("Sheet1", "D1", "Cognome")
    f.SetCellValue("Sheet1", "C1", "Email")
    f.SetCellValue("Sheet1", "D1", "IDENTITY_CARD_EXPIRE_DATE")
    f.SetCellValue("Sheet1", "E1", "TOTAL")

    f.SetCellValue("Sheet1", "E2", "1")
    f.SetCellValue("Sheet1", "E3", "5")
    f.SetCellValue("Sheet1", "E4", "10")

    //formula
    f.SetCellFormula("Sheet1", "E6", "=SUBTOTALE(9;E2:E8)")

    f.SetColWidth("Sheet1", "A", "D", 30)
    if err := f.SaveAs("Personal_Data.xlsx"); err != nil {
        log.Fatal(err)
    }
}

Thank you all

Solution

  • You have three problems with your code:

    First, you add the numeric values as strings. You should use integers as the third parameter:

    f.SetCellValue("Sheet1", "E2", 1)
f.SetCellValue("Sheet1", "E3", 5)
f.SetCellValue("Sheet1", "E4", 10)

    Second, in the formula, you shouldn't add equal sign, you must use comma instead of semicolon and you must use the english function name:

    f.SetCellFormula("Sheet1", "E6", "SUBTOTAL(9,E2:E4)")

    Furthermore you have a circular reference in your formula, because it is in E6 cell, but the range is E2:E8 in you example, which has E6 in it. So you have to change that too.