This project has moved. For the latest updates, please go here.
1
Vote

Formula which includes cell ref like "A:B" cannot copy correctly.

description

// with
cell1.Formula == "VLOOKUP(C1, A:B, 2)"
// do
cell1.Copy(cell2)
// then
cell2.Formula == "VLOOKUP(C2, #REF!, 2)"
It is probrem of ExcelCellBase::UpdateFormulaReferences() or ExcelAddressBase called by ExcelRangeBase::Copy.

I think that ExcelAddressBase recognizes "A:B" as "A1:B1048576", so, cell1.Copy(cell2) makes "A2:B1048577" and B1048577 causes "#REF!".

I think that it is good to correct ExcelAddressBase so that "A: B" is recognized as "A$1:B$1048576", but I do not have confidence to correct it properly.

comments

anothersphere wrote Aug 14 at 4:12 AM

We have a big spreadsheet that uses lots of vlookup with ranges Col1:Col2
e.g. vlookup(CELLREF, A:Z, 2)

We find that once the sheet has gone through eeplus, that we get the #REF error described above.
We have had to at great effort, replace A:Z with A2:Z9999 or similar (where 9999 is more rows than we are likely to have in the sheet.

As an aside, these lookups are against a tab that we are inserting rows into with epplus,