Hi, Wang Lei, Below is my clarification for issue https://issues.apache.org/ooo/show_bug.cgi?id=119565. Comment is also added in Bugzilla.
In Excel 2003 define a defined name as "=Sheet1!$D$3:$D$5,Sheet1!$B$3:$B$5". In this defined name, a union operator(Excel uses "," and AOO uses "~") is used to form a union area. This defined name will be passed into a formula SUM. Import them in AOO, by Excel 2003 filter the union operator will be translated into ";" which is an old union operator. And SUM will take it as a parameter separator. So the SUM result seems correct while it is not in AOO. Union operator should be set as "~" in defined name in AOO. But a fix in Excel 2003 filter is not reasonable because: 1. Defined name with union(OO uses ";") created in lower version OO(OOo2.4.3) still cannot be imported correctly even if a fix is patched in Excel 2003 filter. 2. I have checked this formula "=SUM((B3:B5,D3:D5))"(union directly used) created in Excel 2003. It will be imported as "=SUM((B3:B5~D3:D5))" which is correct. While Excel 2003 filter still imports the union as ";". But as last it is "~". Core function must does something which a hint to fix union issue in defined name. So please take consideration for it in the enhancement. Regards, Jianyuan 2012/8/8 Lei Wang <[email protected]> > Hi Regina > > I saw your comments in > https://issues.apache.org/ooo/show_bug.cgi?id=120478 > > It is glad to know that ODF1.2 support sheet scoped defined name. It is > very important to comply with ODF1.2. So our plan will be changed to > Step 1, support interoperability with sheet scoped defined name in Excel. > Because the solution is ready. it is our first priority. > Step 2, support loading and saving ods file for sheet scoped defined name. > We will put it in our AOO3.5 plan. > Step 3, support create sheet scoped defined name in GUI in AOO. > > On Wed, Aug 8, 2012 at 5:00 PM, Regina Henschel <[email protected] > >wrote: > > > Hi shzh zhao, > > > > there is no need for modifying ODF, named-expressions with scope on one > > sheet are already specified and LibreOffice reads and writes such > documents. > > > > I think the order should be the other way round. First make AOO read and > > write such named-expressions in ODF and then improve the import and > export > > filter. > > > > Kind regards > > Regina > > > > shzh zhao schrieb: > > > > hi, > >> > >> here is an interoperability issue in Aoo. > >> When loading VBA that contains defined name in Aoo, the names are > changed > >> to a new one if the name is limited > >> in a worksheet in MS office.This will cause big problem. > >> > >> After my investigation,I found it is an limitation of Aoo. Because MS > >> Excel > >> 2003 can support worksheet scope defined name. but it can't be set in > UI. > >> and Excel 2007 can define duplicate name for different sheet in its name > >> manager dialog. > >> When AOO loading a xlsx/xls file which has duplicated defined name, AOO > >> will only change the name to a unique one. But any other place which > uses > >> the defined name is not changed, this is the root cause. > >> > >> this enchancement will be completed in 2 steps. > >> in Currently step, just focus on the interoperability of MS defined > >> name.,and not modify the ODF file format. > >> > >> if MS office defined range names with a sheet or a work book,import it > in > >> Aoo, > >> make the name of work book in the original,but with a flag of global > >> name,for example,the name is "Name_1",it will be "Name_1" as before in > >> the define name dialog. > >> make the name of work sheet to the original,but with a flag of sheet > >> name,for > >> example,the name is "Name_1",it will be "Name_1 (sheet 1)" in the define > >> name > >> dialog if the name is defined in the sheet 1. > >> > >> here is the issue link: > >> https://issues.apache.org/ooo/**show_bug.cgi?id=120478< > https://issues.apache.org/ooo/show_bug.cgi?id=120478> > >> > >> > > >
