Output file error after formula manipulation

[ArmandoZepeda]

Thank you for your work on excelize. This is a really helpful and flexible tool.

I found an issue when an existing formula on the input file is deleted from the cell or moved down when new rows are added.
The error messages are the same to #152

Scenario description

Input file structure

• Rows at beginning acting as sheet header.
• Data section that I need to populate with new rows.
• Cells at bottom with formulas that I need to "rebuild"(update vertical range) after previous step is done.
  It would be so helpful if some day this rebuild could be automatic(like Microsoft Excel editor does)

Excelize steps
To apply final formulas result, I need to perform the following:

1. Scan the whole sheet and find cells with formulas.
2. Backup sheet formulas info.
3. Populate sheet with new rows.
4. Put back the formulas, with the new range calculation.

Issue

Input file info:

• Name: HistoryTemplate.xlsx
• Number values from "E9" to "E12"
• Formula at "E14": SUM(E9:E12)

1. Formula deletion:
   xlsx, _ := excelize.OpenFile("./HistoryTemplate.xlsx")
sheetName := "Paros"
xlsx.SetCellFormula(sheetName, "E14", "")
xlsx.SaveAs("./output1.xlsx")

2. Formula moved down:
   xlsx, _ := excelize.OpenFile("./HistoryTemplate.xlsx")
sheetName := "Paros"
xlsx.DuplicateRowTo(sheetName, 10, 11)
xlsx.SaveAs("./output2.xlsx")

Error message to open output file:
"Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)"

Files:

HistoryTemplate.xlsx
output1.xlsx
output2.xlsx

go env:
set GOARCH=amd64 set GOBIN= set GOCACHE=C:\Users\Mind\AppData\Local\go-build set GOEXE=.exe set GOHOSTARCH=amd64 set GOHOSTOS=windows set GOOS=windows set GOPATH=C:\GO_Projects set GORACE= set GOROOT=C:\Go set GOTMPDIR= set GOTOOLDIR=C:\Go\pkg\tool\windows_amd64 set GCCGO=gccgo set CC=gcc set CXX=g++ set CGO_ENABLED=1 set CGO_CFLAGS=-g -O2 set CGO_CPPFLAGS= set CGO_CXXFLAGS=-g -O2 set CGO_FFLAGS=-g -O2 set CGO_LDFLAGS=-g -O2 set PKG_CONFIG=pkg-config set GOGCCFLAGS=-m64 -mthreads -fno-caret-diagnostics -Qunused-arguments -fmessage-length=0 -fdebug-prefix-map=C:\Users\Mind\AppData\Local\Temp\go-build976948498=/tmp/go-build -gno-record-gcc-switches

[xuri]

Hi @ArmandoZepeda, thanks for your issue. I have fixed the issue corrupted xlsx after deleting formula of cell.

However, when using DuplicateRow and DuplicateRowTo, it is still possible to cause file corruption. I have noted this potential problem in the documentation for these two functions.

Since that which will affect changes in references such as formulas, charts, and so on. If there is any referenced value of the worksheet, it will cause a file error when you open it. The excelize only partially updates these references currently.

I think that solving this problem requires the completion of the function adjustHelper. I'll certainly accept that patch if somebody did that.

[ArmandoZepeda]

Thanks for your fixing.
I know that DuplicateRow and DuplicateRowTo implies hard work.

Now the output file has no errors when formula is removed from cell.
Right now, this is enough for me.