diff options
| -rw-r--r-- | cell.go | 78 | ||||
| -rw-r--r-- | cell_test.go | 22 | ||||
| -rw-r--r-- | excelize_test.go | 2 | ||||
| -rw-r--r-- | lib.go | 5 |
4 files changed, 100 insertions, 7 deletions
@@ -392,7 +392,7 @@ func (f *File) GetCellFormula(sheet, axis string) (string, error) { return "", false, nil } if c.F.T == STCellFormulaTypeShared { - return getSharedForumula(x, c.F.Si), true, nil + return getSharedForumula(x, c.F.Si, c.R), true, nil } return c.F.Content, true, nil }) @@ -977,6 +977,48 @@ func isOverlap(rect1, rect2 []int) bool { cellInRef([]int{rect2[2], rect2[3]}, rect1) } +// parseSharedFormula generate dynamic part of shared formula for target cell +// by given column and rows distance and origin shared formula. +func parseSharedFormula(dCol, dRow int, orig []byte) (res string, start int) { + var ( + end int + stringLiteral bool + ) + for end = 0; end < len(orig); end++ { + c := orig[end] + if c == '"' { + stringLiteral = !stringLiteral + } + if stringLiteral { + continue // Skip characters in quotes + } + if c >= 'A' && c <= 'Z' || c == '$' { + res += string(orig[start:end]) + start = end + end++ + foundNum := false + for ; end < len(orig); end++ { + idc := orig[end] + if idc >= '0' && idc <= '9' || idc == '$' { + foundNum = true + } else if idc >= 'A' && idc <= 'Z' { + if foundNum { + break + } + } else { + break + } + } + if foundNum { + cellID := string(orig[start:end]) + res += shiftCell(cellID, dCol, dRow) + start = end + } + } + } + return +} + // getSharedForumula find a cell contains the same formula as another cell, // the "shared" value can be used for the t attribute and the si attribute can // be used to refer to the cell containing the formula. Two formulas are @@ -985,13 +1027,43 @@ func isOverlap(rect1, rect2 []int) bool { // // 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) string { +func getSharedForumula(ws *xlsxWorksheet, si string, 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 { - return c.F.Content + col, row, _ := CellNameToCoordinates(axis) + sharedCol, sharedRow, _ := CellNameToCoordinates(c.R) + dCol := col - sharedCol + dRow := row - sharedRow + orig := []byte(c.F.Content) + res, start := parseSharedFormula(dCol, dRow, orig) + if start < len(orig) { + res += string(orig[start:]) + } + return res } } } return "" } + +// shiftCell returns the cell shifted according to dCol and dRow taking into +// consideration of absolute references with dollar sign ($) +func shiftCell(cellID string, dCol, dRow int) string { + fCol, fRow, _ := CellNameToCoordinates(cellID) + signCol, signRow := "", "" + if strings.Index(cellID, "$") == 0 { + signCol = "$" + } else { + // Shift column + fCol += dCol + } + if strings.LastIndex(cellID, "$") > 0 { + signRow = "$" + } else { + // Shift row + fRow += dRow + } + colName, _ := ColumnNumberToName(fCol) + return signCol + colName + signRow + strconv.Itoa(fRow) +} diff --git a/cell_test.go b/cell_test.go index 3954438..0af0097 100644 --- a/cell_test.go +++ b/cell_test.go @@ -226,6 +226,28 @@ func TestGetCellFormula(t *testing.T) { assert.NoError(t, f.SetCellValue("Sheet1", "A1", true)) _, err = f.GetCellFormula("Sheet1", "A1") assert.NoError(t, err) + + // Test get cell shared formula + f = NewFile() + sheetData := `<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData><row r="1"><c r="A1"><v>1</v></c><c r="B1"><f>2*A1</f></c></row><row r="2"><c r="A2"><v>2</v></c><c r="B2"><f t="shared" ref="B2:B7" si="0">%s</f></c></row><row r="3"><c r="A3"><v>3</v></c><c r="B3"><f t="shared" si="0"/></c></row><row r="4"><c r="A4"><v>4</v></c><c r="B4"><f t="shared" si="0"/></c></row><row r="5"><c r="A5"><v>5</v></c><c r="B5"><f t="shared" si="0"/></c></row><row r="6"><c r="A6"><v>6</v></c><c r="B6"><f t="shared" si="0"/></c></row><row r="7"><c r="A7"><v>7</v></c><c r="B7"><f t="shared" si="0"/></c></row></sheetData></worksheet>` + + for sharedFormula, expected := range map[string]string{ + `2*A2`: `2*A3`, + `2*A1A`: `2*A2A`, + `2*$A$2+LEN("")`: `2*$A$2+LEN("")`, + } { + f.Sheet.Delete("xl/worksheets/sheet1.xml") + f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(fmt.Sprintf(sheetData, sharedFormula))) + formula, err := f.GetCellFormula("Sheet1", "B3") + assert.NoError(t, err) + assert.Equal(t, expected, formula) + } + + f.Sheet.Delete("xl/worksheets/sheet1.xml") + f.Pkg.Store("xl/worksheets/sheet1.xml", []byte(`<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData><row r="2"><c r="B2"><f t="shared" si="0"></f></c></row></sheetData></worksheet>`)) + formula, err := f.GetCellFormula("Sheet1", "B2") + assert.NoError(t, err) + assert.Equal(t, "", formula) } func ExampleFile_SetCellFloat() { diff --git a/excelize_test.go b/excelize_test.go index 918279b..f33c3d5 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{}, "", "") // Test read cell value with given illegal rows number. _, err = f.GetCellValue("Sheet2", "a-1") @@ -93,13 +93,12 @@ func readFile(file *zip.File) ([]byte, error) { // func SplitCellName(cell string) (string, int, error) { alpha := func(r rune) bool { - return ('A' <= r && r <= 'Z') || ('a' <= r && r <= 'z') + return ('A' <= r && r <= 'Z') || ('a' <= r && r <= 'z') || (r == 36) } - if strings.IndexFunc(cell, alpha) == 0 { i := strings.LastIndexFunc(cell, alpha) if i >= 0 && i < len(cell)-1 { - col, rowstr := cell[:i+1], cell[i+1:] + col, rowstr := strings.ReplaceAll(cell[:i+1], "$", ""), cell[i+1:] if row, err := strconv.Atoi(rowstr); err == nil && row > 0 { return col, row, nil } |