1. I have a table with 2 columns(A & B) and 8 rows.
2.I write formula =D5+F5 in cell "B6"
3.After the formula is executed the Gridweb automatically adds blank rows and columns till F column as the formula is referring to that column.
Screenshots attached.
I dont want such additional rows and columns to be inserted.Instead of that if the formula is referring to some cell which is not existing in the table it should throw some error and the formula should not be executed.
We tested the project with latest version.The problem still exists.
Sample Project attached.
Test scenarios:(Screenshots attached)
Test-1. Gridweb has 8 rows and 8 columns(A to H) initially.I add a formula in cell “=I5”.Column “I” does not exist initially but on execution of formula columns till “I” are created in Gridweb.
Test-2. Gridweb has 8 rows and 9 columns(A to I) initially. I add formula “=SUM(B1:B10)” in a cell.There are 8 rows only but on execution of formula 2 extra rows are added making it 10.
Requirement :
I don’t need such extra rows and columns to be added in case of formula execution.If the formula refers to any cell which is not existing in GridWeb then the formula execution should result in throwing some error message. Extra rows and columns should not be added in any case.
Kindly treat the issue as urgent and suggest necessary changes.
We tried with AutoExtendMaxRowColumn=“False” in the GridWeb. Here are some scenarios we encountered.
Initial State : GridWeb with 8 rows and 2 columns(A & B).
Test 1 : (Screenshot Attached )
I enter a formula “=D4+D5” in cell “A7”. The formula is calculated and gives result 0.In this case no additional rows or columns are added in the Gridweb.
Test 2:(Screenshot Attached )
I enter a formula “=SUM(D4:D7)” in cell “A8”.The formula is calculated and gives result 0.In this case additional rows and columns are added in the Gridweb.
Requirement:
I want that in whatever format I enter formula ,No additional rows and columns should be added in the Gridweb. Also instead of the result “0” displayed there should be some error message displayed like “#NAME?” or something else.
Kindly look into the issue and give further suggestions asap.
I want that in whatever format I enter formula ,No additional rows and columns should be added in the Gridweb. Also instead of the result “0” displayed there should be some error message displayed like “#NAME?” or something else.
Well, Aspose.Cells.GridWeb follows MS Excel standards where a sheet actually has 256 columns and 65536 rows for XLS and for XLSX file format, it will have long list of rows and columns accordingly. You may try to check it in MS Excel sheet, it won’t give any error if the rows and columns indexes/names are within the sheet’s range of cells. Initial display of rows and columns does not take into account as it follows the MS Excel standards.
Our many users are using the grid as a replacement for MS Excel and they are used to it and they think it should always follow Ms Excel standards and behavior.
Anyways, we will check if we can enhance it for your custom needs, we need to check the feasibility of your custom request though. We have logged it against your existing issue id and will look into it soon.
Once we have any update on it, we will let you know here.
When I use AutoExtendMaxRowColumn="Falseand enter formula in“=D4+D5” format it works fine and no additional rows and columns are added.That meets our requirement.If it will display “0” as answer it will be OK with us.
But when we enter formula in “=SUM(D4:D7)” format the additional rows and columns are added.We don’t want such rows to be added in the Grid.Instead if it will display answer as “0” then it will be OK.If it does not show error then also it is OK.
So i revise my requirement as :
"I want that in whatever format I enter the formula ,No additional rows and columns should be added in the Gridweb if I set AutoExtendMaxRowColumn=“False” .It will be OK if it displays “0”.If it does not show error that is also OK.I just want to avoid the automatic insertion of extra rows and columns"
OK fine, we will look into your minimal requirements if we can support it soon. We have logged your revised needs into our database and our concerned developer will look into it soon.
Once we have any update on it, we will let you know here.
To update you, we will do maximum row/column check for formula reference when the AutoExtendMaxRowColumn is set to false. We will release the new fix that supports this feature in 1-2 days.