incubator-ooo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jianyuan Li <lijianyuan1...@gmail.com>
Subject Re: request for enchancement of the define name function in calc
Date Fri, 10 Aug 2012 08:55:20 GMT
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>
> >>
> >>
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message