Mailing-List: contact ooo-dev-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: ooo-dev@incubator.apache.org
Received-SPF: pass (athena.apache.org: domain of lijianyuan1983@gmail.com
 designates 209.85.213.47 as permitted sender)
MIME-Version: 1.0
In-Reply-To: 
 <CAFpynw1=QTDdr-VGYY1e0kpmQBiUG3jDZuMjEApx=ZSJEf7_uw@mail.gmail.com>
References: 
 <CAFpynw0w9zS3qv+Jt=phGBhaT85M_8Xg7i8MD9vxGFVvJaqdUQ@mail.gmail.com>
	<50222A90.5040105@t-online.de>
	<CA+mMfTe_zdkiymw17axy6EcG6W8CekJgAippZAKn2A2Tx5C_hQ@mail.gmail.com>
	<CAPNWwk0bae0HBqNR+B50t_fqx576Nmc=azKMRQD7n47RotT1CA@mail.gmail.com>
	<CAFpynw1=QTDdr-VGYY1e0kpmQBiUG3jDZuMjEApx=ZSJEf7_uw@mail.gmail.com>
Date: Tue, 14 Aug 2012 13:15:14 +0800
Message-ID: 
 <CAPNWwk3SgoUgGnRHn9hOepsjfrsMjWzLJR1rJnsBdzxY6-76Ow@mail.gmail.com>
Subject: Re: request for enchancement of the define name function in calc
From: Jianyuan Li <lijianyuan1983@gmail.com>
To: ooo-dev@incubator.apache.org
Content-Type: multipart/alternative; boundary=14dae93408a3352bd104c732e51b

--14dae93408a3352bd104c732e51b
Content-Type: text/plain; charset=ISO-8859-1

If the defined name is correctly set, formula calculation result will be
corrected. So it is a formula issue and also a defined name issue.

2012/8/13 shzh zhao <aoo.zhaoshzh@gmail.com>

> I think it is a formula issue and not belong to name scope range.
>
> 2012/8/10 Jianyuan Li <lijianyuan1983@gmail.com>
>
> > 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 <lei.wang.leiw@gmail.com>
> >
> > > 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 <
> rb.henschel@t-online.de
> > > >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>
> > > >>
> > > >>
> > > >
> > >
> >
>
>
>
> --
> *
>
>
> mailto: *aoo.zhaoshzh@gmail.com <https://google.com/profiles>
> <https://google.com/profiles>
>

--14dae93408a3352bd104c732e51b--