I get an error when using a formula like this [file.SetCellFormula(sheetName, "A6", "=IF(C6<>\"\";IF(OR((F6-E6)>0;G6<>\"\";J6>0);\"O\";\"X\");\"\")")], I'm sure the formula is correct, I have copied this formula from a working excel file

func excel() error {
    sheetName := "022022"

    if IsFileExists("./excels/Rikai_TimeSheet_202202_SonNguyen.xlsx") {
        err := os.Remove("./excels/Rikai_TimeSheet_202202_SonNguyen.xlsx")
        if err != nil {
            return err
        }
    }

    file, err := excelize.OpenFile("./excels/Rikai_TimeSheet_202202_SonNguyen_template.xlsx")
    if err != nil {
        return err
    }
    defer func() {
        _ = file.Close()
    }()
    

    err = file.SetCellFormula(sheetName, "A6", "=IF(C6<>\"\";IF(OR((F6-E6)>0;G6<>\"\";J6>0);\"O\";\"X\");\"\")")
    if err != nil {
        return err
    }

    f, err := os.OpenFile("./excels/Rikai_TimeSheet_202202_SonNguyen.xlsx", os.O_WRONLY|os.O_CREATE|os.O_APPEND, 0600)
    if err != nil {
        return err
    }

    err = file.Write(f)
    if err != nil {
        return err
    }
    return nil
}

Error popup when open the file

CodePudding user response：

2 suggestions:

1. Try doubling the double quotes within the formula string.
   e.g: if the formula in Excel is =if(A2="";"Empty";"ok") then, when surrounded by quotes like in your code, it should become:
   "=if(A2="""";""Empty"";""ok"")"
2. Replace those semicolons (;) by commas (,)
   (As a general idea, always use the US standards, not your local one)

CodePudding user response：

Try to use backticks ` instead of escaping quotes:

err = file.SetCellFormula(sheetName, "A6", `=IF(C6<>"";IF(OR((F6-E6)>0;G6<>"";J6>0);"O";"X");"")`)