Add set shared formula support and documentation for the `SetCellFormula`

5 files changed, 136 insertions, 37 deletions

diff --git a/cell.go b/cell.go
index d44a552..d176991 100644
--- a/cell.go
+++ b/cell.go
@@ -391,8 +391,8 @@ func (f *File) GetCellFormula(sheet, axis string) (string, error) {
 		if c.F == nil {
 			return "", false, nil
 		}
-		if c.F.T == STCellFormulaTypeShared {
-			return getSharedForumula(x, c.F.Si, c.R), true, nil
+		if c.F.T == STCellFormulaTypeShared && c.F.Si != nil {
+			return getSharedForumula(x, *c.F.Si, c.R), true, nil
 		}
 		return c.F.Content, true, nil
 	})
@@ -404,8 +404,50 @@ type FormulaOpts struct {
 	Ref  *string // Shared formula ref
 }
 
-// SetCellFormula provides a function to set cell formula by given string and
-// worksheet name.
+// SetCellFormula provides a function to set formula on the cell is taken
+// according to the given worksheet name (case sensitive) 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 get the calculated cell value. If
+// the Excel application doesn't calculate the formula automatically when the
+// workbook has been opened, please call "UpdateLinkedValue" after setting
+// the cell formula functions.
+//
+// Example 1, set normal formula "=SUM(A1,B1)" for the cell "A3" on "Sheet1":
+//
+//    err := f.SetCellFormula("Sheet1", "A3", "=SUM(A1,B1)")
+//
+// Example 2, set one-dimensional vertical constant array (row array) formula
+// "1,2,3" for the cell "A3" on "Sheet1":
+//
+//    err := f.SetCellFormula("Sheet1", "A3", "={1,2,3}")
+//
+// Example 3, set one-dimensional horizontal constant array (column array)
+// formula '"a","b","c"' for the cell "A3" on "Sheet1":
+//
+//    err := f.SetCellFormula("Sheet1", "A3", "={\"a\",\"b\",\"c\"}")
+//
+// Example 4, set two-dimensional constant array formula '{1,2,"a","b"}' for
+// the cell "A3" on "Sheet1":
+//
+//    formulaType, ref := excelize.STCellFormulaTypeArray, "A3:A3"
+//    err := f.SetCellFormula("Sheet1", "A3", "={1,2,\"a\",\"b\"}",
+//        excelize.FormulaOpts{Ref: &ref, Type: &formulaType})
+//
+// Example 5, set range array formula "A1:A2" for the cell "A3" on "Sheet1":
+//
+//    formulaType, ref := excelize.STCellFormulaTypeArray, "A3:A3"
+//    err := f.SetCellFormula("Sheet1", "A3", "=A1:A2",
+//	      excelize.FormulaOpts{Ref: &ref, Type: &formulaType})
+//
+//
+// Example 6, set shared formula "=A1+B1" for the cell "C1:C5"
+// on "Sheet1", "C1" is the master cell:
+//
+//    formulaType, ref := excelize.STCellFormulaTypeShared, "C1:C5"
+//    err := f.SetCellFormula("Sheet1", "C1", "=A1+B1",
+//        excelize.FormulaOpts{Ref: &ref, Type: &formulaType})
+//
 func (f *File) SetCellFormula(sheet, axis, formula string, opts ...FormulaOpts) error {
 	ws, err := f.workSheetReader(sheet)
 	if err != nil {
@@ -430,8 +472,12 @@ func (f *File) SetCellFormula(sheet, axis, formula string, opts ...FormulaOpts)
 	for _, o := range opts {
 		if o.Type != nil {
 			cellData.F.T = *o.Type
+			if cellData.F.T == STCellFormulaTypeShared {
+				if err = ws.setSharedFormula(*o.Ref); err != nil {
+					return err
+				}
+			}
 		}
-
 		if o.Ref != nil {
 			cellData.F.Ref = *o.Ref
 		}
@@ -440,6 +486,40 @@ func (f *File) SetCellFormula(sheet, axis, formula string, opts ...FormulaOpts)
 	return err
 }
 
+// setSharedFormula set shared formula for the cells.
+func (ws *xlsxWorksheet) setSharedFormula(ref string) error {
+	coordinates, err := areaRefToCoordinates(ref)
+	if err != nil {
+		return err
+	}
+	_ = sortCoordinates(coordinates)
+	cnt := ws.countSharedFormula()
+	for c := coordinates[0]; c <= coordinates[2]; c++ {
+		for r := coordinates[1]; r <= coordinates[3]; r++ {
+			prepareSheetXML(ws, c, r)
+			cell := &ws.SheetData.Row[r-1].C[c-1]
+			if cell.F == nil {
+				cell.F = &xlsxF{}
+			}
+			cell.F.T = STCellFormulaTypeShared
+			cell.F.Si = &cnt
+		}
+	}
+	return err
+}
+
+// countSharedFormula count shared formula in the given worksheet.
+func (ws *xlsxWorksheet) countSharedFormula() (count int) {
+	for _, row := range ws.SheetData.Row {
+		for _, cell := range row.C {
+			if cell.F != nil && cell.F.Si != nil && *cell.F.Si+1 > count {
+				count = *cell.F.Si + 1
+			}
+		}
+	}
+	return
+}
+
 // GetCellHyperLink provides a function to get cell hyperlink by given
 // worksheet name and axis. Boolean type value link will be ture if the cell
 // has a hyperlink and the target is the address of the hyperlink. Otherwise,
@@ -1027,10 +1107,10 @@ func parseSharedFormula(dCol, dRow int, orig []byte) (res string, start int) {
 //
 // Note that this function not validate ref tag to check the cell if or not in
 // allow area, and always return origin shared formula.
-func getSharedForumula(ws *xlsxWorksheet, si string, axis string) string {
+func getSharedForumula(ws *xlsxWorksheet, si int, axis string) string {
 	for _, r := range ws.SheetData.Row {
 		for _, c := range r.C {
-			if c.F != nil && c.F.Ref != "" && c.F.T == STCellFormulaTypeShared && c.F.Si == si {
+			if c.F != nil && c.F.Ref != "" && c.F.T == STCellFormulaTypeShared && c.F.Si != nil && *c.F.Si == si {
 				col, row, _ := CellNameToCoordinates(axis)
 				sharedCol, sharedRow, _ := CellNameToCoordinates(c.R)
 				dCol := col - sharedCol
diff --git a/cell_test.go b/cell_test.go
index 7a08560..ad78436 100644
--- a/cell_test.go
+++ b/cell_test.go
@@ -313,6 +313,51 @@ func TestOverflowNumericCell(t *testing.T) {
 	// GOARCH=amd64 - all ok; GOARCH=386 - actual: "-2147483648"
 	assert.Equal(t, "8595602512225", val, "A1 should be 8595602512225")
 }
+
+func TestSetCellFormula(t *testing.T) {
+	f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
+	if !assert.NoError(t, err) {
+		t.FailNow()
+	}
+
+	assert.NoError(t, f.SetCellFormula("Sheet1", "B19", "SUM(Sheet2!D2,Sheet2!D11)"))
+	assert.NoError(t, f.SetCellFormula("Sheet1", "C19", "SUM(Sheet2!D2,Sheet2!D9)"))
+
+	// Test set cell formula with illegal rows number.
+	assert.EqualError(t, f.SetCellFormula("Sheet1", "C", "SUM(Sheet2!D2,Sheet2!D9)"), `cannot convert cell "C" to coordinates: invalid cell name "C"`)
+
+	assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula1.xlsx")))
+
+	f, err = OpenFile(filepath.Join("test", "CalcChain.xlsx"))
+	if !assert.NoError(t, err) {
+		t.FailNow()
+	}
+	// Test remove cell formula.
+	assert.NoError(t, f.SetCellFormula("Sheet1", "A1", ""))
+	assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula2.xlsx")))
+	// Test remove all cell formula.
+	assert.NoError(t, f.SetCellFormula("Sheet1", "B1", ""))
+	assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula3.xlsx")))
+
+	// Test set shared formula for the cells.
+	f = NewFile()
+	for r := 1; r <= 5; r++ {
+		assert.NoError(t, f.SetSheetRow("Sheet1", fmt.Sprintf("A%d", r), &[]interface{}{r, r + 1}))
+	}
+	formulaType, ref := STCellFormulaTypeShared, "C1:C5"
+	assert.NoError(t, f.SetCellFormula("Sheet1", "C1", "=A1+B1", FormulaOpts{Ref: &ref, Type: &formulaType}))
+	sharedFormulaSpreadsheet := filepath.Join("test", "TestSetCellFormula4.xlsx")
+	assert.NoError(t, f.SaveAs(sharedFormulaSpreadsheet))
+
+	f, err = OpenFile(sharedFormulaSpreadsheet)
+	assert.NoError(t, err)
+	ref = "D1:D5"
+	assert.NoError(t, f.SetCellFormula("Sheet1", "D1", "=A1+C1", FormulaOpts{Ref: &ref, Type: &formulaType}))
+	ref = ""
+	assert.EqualError(t, f.SetCellFormula("Sheet1", "D1", "=A1+C1", FormulaOpts{Ref: &ref, Type: &formulaType}), ErrParameterInvalid.Error())
+	assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula5.xlsx")))
+}
+
 func TestGetCellRichText(t *testing.T) {
 	f := NewFile()
 
diff --git a/excelize_test.go b/excelize_test.go
index f33c3d5..02abce5 100644
--- a/excelize_test.go
+++ b/excelize_test.go
@@ -83,7 +83,7 @@ func TestOpenFile(t *testing.T) {
 	assert.NoError(t, err)
 	_, err = f.GetCellFormula("Sheet2", "I11")
 	assert.NoError(t, err)
-	getSharedForumula(&xlsxWorksheet{}, "", "")
+	getSharedForumula(&xlsxWorksheet{}, 0, "")
 
 	// Test read cell value with given illegal rows number.
 	_, err = f.GetCellValue("Sheet2", "a-1")
@@ -401,32 +401,6 @@ func TestGetCellHyperLink(t *testing.T) {
 
 }
 
-func TestSetCellFormula(t *testing.T) {
-	f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
-	if !assert.NoError(t, err) {
-		t.FailNow()
-	}
-
-	assert.NoError(t, f.SetCellFormula("Sheet1", "B19", "SUM(Sheet2!D2,Sheet2!D11)"))
-	assert.NoError(t, f.SetCellFormula("Sheet1", "C19", "SUM(Sheet2!D2,Sheet2!D9)"))
-
-	// Test set cell formula with illegal rows number.
-	assert.EqualError(t, f.SetCellFormula("Sheet1", "C", "SUM(Sheet2!D2,Sheet2!D9)"), `cannot convert cell "C" to coordinates: invalid cell name "C"`)
-
-	assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula1.xlsx")))
-
-	f, err = OpenFile(filepath.Join("test", "CalcChain.xlsx"))
-	if !assert.NoError(t, err) {
-		t.FailNow()
-	}
-	// Test remove cell formula.
-	assert.NoError(t, f.SetCellFormula("Sheet1", "A1", ""))
-	assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula2.xlsx")))
-	// Test remove all cell formula.
-	assert.NoError(t, f.SetCellFormula("Sheet1", "B1", ""))
-	assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellFormula3.xlsx")))
-}
-
 func TestSetSheetBackground(t *testing.T) {
 	f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
 	if !assert.NoError(t, err) {
diff --git a/merge_test.go b/merge_test.go
index 02d92fb..88fe4f9 100644
--- a/merge_test.go
+++ b/merge_test.go
@@ -181,7 +181,7 @@ func TestUnmergeCell(t *testing.T) {
 	ws, ok = f.Sheet.Load("xl/worksheets/sheet1.xml")
 	assert.True(t, ok)
 	ws.(*xlsxWorksheet).MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A1"}}}
-	assert.EqualError(t, f.UnmergeCell("Sheet1", "A2", "B3"), "parameter is invalid")
+	assert.EqualError(t, f.UnmergeCell("Sheet1", "A2", "B3"), ErrParameterInvalid.Error())
 
 	ws, ok = f.Sheet.Load("xl/worksheets/sheet1.xml")
 	assert.True(t, ok)
@@ -191,5 +191,5 @@ func TestUnmergeCell(t *testing.T) {
 
 func TestFlatMergedCells(t *testing.T) {
 	ws := &xlsxWorksheet{MergeCells: &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A1"}}}}
-	assert.EqualError(t, flatMergedCells(ws, [][]*xlsxMergeCell{}), "parameter is invalid")
+	assert.EqualError(t, flatMergedCells(ws, [][]*xlsxMergeCell{}), ErrParameterInvalid.Error())
 }
diff --git a/xmlWorksheet.go b/xmlWorksheet.go
index 697504e..a4aef4c 100644
--- a/xmlWorksheet.go
+++ b/xmlWorksheet.go
@@ -475,7 +475,7 @@ type xlsxF struct {
 	Content string `xml:",chardata"`
 	T       string `xml:"t,attr,omitempty"`   // Formula type
 	Ref     string `xml:"ref,attr,omitempty"` // Shared formula ref
-	Si      string `xml:"si,attr,omitempty"`  // Shared formula index
+	Si      *int   `xml:"si,attr"`            // Shared formula index
 }
 
 // xlsxSheetProtection collection expresses the sheet protection options to