SetCellFormula provides a function to set formula on the cell is taken according to the given worksheet name and cell formula settings. The result of the formula cell can be calculated when the worksheet is opened by the Office Excel application or can be using the "CalcCellValue" function also can
(sheet, cell, formula string, opts ...FormulaOpts)
| 787 | // } |
| 788 | // } |
| 789 | func (f *File) SetCellFormula(sheet, cell, formula string, opts ...FormulaOpts) error { |
| 790 | ws, err := f.workSheetReader(sheet) |
| 791 | if err != nil { |
| 792 | return err |
| 793 | } |
| 794 | c, _, _, err := ws.prepareCell(cell) |
| 795 | if err != nil { |
| 796 | return err |
| 797 | } |
| 798 | f.clearCalcCache() |
| 799 | if formula == "" { |
| 800 | ws.deleteSharedFormula(c) |
| 801 | c.F = nil |
| 802 | return f.deleteCalcChain(f.getSheetID(sheet), cell) |
| 803 | } |
| 804 | |
| 805 | if c.F != nil { |
| 806 | c.F.Content = formula |
| 807 | } else { |
| 808 | c.F = &xlsxF{Content: formula} |
| 809 | } |
| 810 | |
| 811 | for _, opt := range opts { |
| 812 | if opt.Type != nil { |
| 813 | if *opt.Type == STCellFormulaTypeDataTable { |
| 814 | return err |
| 815 | } |
| 816 | c.F.T = *opt.Type |
| 817 | if c.F.T == STCellFormulaTypeArray && opt.Ref != nil { |
| 818 | if err = ws.setArrayFormula(sheet, &xlsxF{Ref: *opt.Ref, Content: formula}, f.GetDefinedName()); err != nil { |
| 819 | return err |
| 820 | } |
| 821 | } |
| 822 | if c.F.T == STCellFormulaTypeShared { |
| 823 | ws.deleteSharedFormula(c) |
| 824 | if err = ws.setSharedFormula(cell, *opt.Ref); err != nil { |
| 825 | return err |
| 826 | } |
| 827 | } |
| 828 | } |
| 829 | if opt.Ref != nil { |
| 830 | c.F.Ref = *opt.Ref |
| 831 | } |
| 832 | } |
| 833 | c.T, c.IS = "str", nil |
| 834 | return err |
| 835 | } |
| 836 | |
| 837 | // setArrayFormula transform the array formula in an array formula range to the |
| 838 | // normal formula and set cells in this range to the formula as the normal |