Re: copy-paste from within comment spreadsheet

[Czesław Wolański]

If you mean a comment (note) attached to a cell,

see section "Editing comments" at the following link:

https://wiki.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide/Adding_notes


Regards,
Czesław


-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

copy-paste from within comment spreadsheet

[James Jenkins]

I'm working in Apache Openoffice Spreadsheet, have a (comment) with a 
web-site address . . . unable to copy and paste from the (comment) ?



-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

RE: simple "clipboard" needed for write and spreadsheet

[Jörg Schmidt]

Hello, 

> -Original Message-
> From: James Jenkins [mailto:ljenki...@centurylink.net] 
> Sent: Tuesday, January 26, 2021 5:09 PM
> To: Apache Openoffice
> Subject: simple "clipboard" needed for write and spreadsheet
> 
> I've been using "OO" for many years, now need a clipboard, 
> don't really 
> want to go to "Libre"
> 
> I need a simple "Clipboard" for "OO" for both spreadsheet and write 
> Win10 (64 bit)

And LibreOffice has such a clipboard? Where?

imho:
OO, as well as LO, use the normal clipboard of the operating system. 


greetings,
Jörg


-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: simple "clipboard" needed for write and spreadsheet

[Bidouille]

The clipboard is part of your operating system, not of OpenOffice


- Mail original -
> De: "James Jenkins" 
> À: "Apache Openoffice" 
> Envoyé: Mardi 26 Janvier 2021 17:09:17
> Objet: simple "clipboard" needed for write and spreadsheet
> 
> I've been using "OO" for many years, now need a clipboard, don't
> really
> want to go to "Libre"
> 
> I need a simple "Clipboard" for "OO" for both spreadsheet and write
> Win10 (64 bit)
> 
> 
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
> 
> 

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

simple "clipboard" needed for write and spreadsheet

[James Jenkins]

I've been using "OO" for many years, now need a clipboard, don't really 
want to go to "Libre"


I need a simple "Clipboard" for "OO" for both spreadsheet and write 
Win10 (64 bit)



-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: 4.1.7 spreadsheet problem

[Marcus]

Am 07.12.19 um 23:35 schrieb T.phd:
When I click on a cell, the page will *jump* to the right hiding the 
cell I clicked on.


When clicking on the vertical scroll bar, the page will very quickly 
scroll to the right.


do you mix up vertical with horizontal? When I click on the vertical 
scrollbar, the page scrolls up or down. Horizontal is to the right or left.


But when OpenOffice is behaving exactly like you have described, then I 
can tell you that I never heard about a problem like this. The only 
advise I can give you is to try again with a clean and empty user 
profile. How to trys this can be read here [1].


[1] https://forum.openoffice.org/en/forum/viewtopic.php?f=74&t=12426

HTH

Marcus


-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

4.1.7 spreadsheet problem

[T.phd]

Greetings,

I've hunted the forum for an answer without success.

When I click on a cell, the page will *jump* to the right hiding the 
cell I clicked on.


When clicking on the vertical scroll bar, the page will very quickly 
scroll to the right.


I have uninstalled and reinstalled 4.1.7.

I don't remember having this problem in the past.  I don't know how to 
install 4.1.6 which may solve the problem.


Thank you,

Ed Leach

Re: creating a spreadsheet (was: new cool feature)

[F C. Costero]

I changed the LOOKUP() function in columns D and E to be a VLOOKUP() with
the fourth parameter set to 0 so the function looks for an exact match. The
LOOKUP() function requires that the lookup vector be ordered. Since column
A of the Database sheet is not ordered, incorrect results were returned.
I fixed the formulas in column I to have the form
=H4-G4 + (G4 >H4)
This accounts for cases where the end time (H4) has a smaller clock value
(e.g. 07:00) than the start time (e.g. 19:00) but is in the next day. Times
are stored in units of days. The time 07:00 has the value 7/24 and the time
19:00 has the value 19/24. You were subtracting
7/24 - 19/24 = -12/24 = -0.5
If a cell is formatted as time, -0.5 is displayed as12:00, but the value is
still -0.5.
I appended
+ (G4 > H4)
which returns FALSE (equal to zero) when H4 is larger than or equal to G4.
When G4 is larger than H4, it returns TRUE (equal to one) that gives
H4-G4 + (G4 >H4) = 7/24 - 19/24 + (19/24 > 7/24) = -0.5 + (TRUE) = -0.5 + 1
= 0.5
The 0.5, when formatted as time, is 12:00 and it is the desired answer.
This is not a bug. All spreadsheets treat times as fractions of a day.

As Peter says, it is better to get help on the forum or the user list.

Francis


On Wed, Aug 15, 2018 at 11:57 PM, Peter Kovacs  wrote:

> Hi Archie,
>
>
> Welcome to OpenOffice. I whish you a lot of fun with the Software.
>
> There are lots of possible ways to make your live easier.
>
> I would recommend to use our forums or users mailing list to ask questions
> on ways what you want to do.
>
> I think there are all the features you do expect, but it is made
> differently then you might think.
>
>
> I have quickly exchanged the Postcode and the Miles with a lookup
> function, I would use. But there is an issue. Can someone else have a look?
> I do not find what I did wrong.
>
>
> I loop in users for better support. Sorry, got to go. I am late for work
> ... :S
>
>
> HTH
>
> Peter
>
>
> On 8/16/18 5:59 AM, Archie Dyno Wizard wrote:
>
> Dear developers! I'm beginning my experience with OpenOffice Calc, and I'm
> finding a few ugly bugs. First I think I'm too stupid, but then I realize
> it is a bug that doesn't depend on my knowledge. I have made one bug report
> about calculating time consumption and using the result in a formula for
> next cell.. But this mail is not about that. I was trying to find how to
> make my spreadsheet to fill cells according to previous cell, and finally
> I've found, that there is no option for that, so I created a long formula
> based on "IF" logic task. So now when I type a Name in "LOCATION" cell, it
> automatically recognizes it, and fills following cells "MILES", "POSTCODE",
> and "PAYRATE". Makes it so much easier, but makes difficult creating
> and maintaining the formula. So for now my 3-customer formula looks like
> this:
>  =IF(C371="global 
> stansted";"CM235PU";IF(C371="Mojito";"CM235PU";IF(C371="grafton
> cambridge";"CM11HE";"-")))
> And it is only beginning of my Self Employment.
>
>  So my suggestion is to create an additional AutoFill form where user can
> make a list of related data in specific columns or rows to fill up multiple
> cells at the same time.
>
> Thank you very much for such a wonderful opportunity to use a free Office
> Sofware!!! You guys rock!!!
>
> Attaching a piece of my Spreadsheet that shows  bug in calculation of a
> "TOTAL INC" column, and the idea about AutoFill...
>
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>
>
>
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>


Spreadsheet-bug_FJCC.ods
Description: application/vnd.oasis.opendocument.spreadsheet

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

creating a spreadsheet (was: new cool feature)

[Peter Kovacs]

Hi Archie,


Welcome to OpenOffice. I whish you a lot of fun with the Software.

There are lots of possible ways to make your live easier.

I would recommend to use our forums or users mailing list to ask 
questions on ways what you want to do.


I think there are all the features you do expect, but it is made 
differently then you might think.



I have quickly exchanged the Postcode and the Miles with a lookup 
function, I would use. But there is an issue. Can someone else have a 
look? I do not find what I did wrong.



I loop in users for better support. Sorry, got to go. I am late for work 
... :S



HTH

Peter


On 8/16/18 5:59 AM, Archie Dyno Wizard wrote:
Dear developers! I'm beginning my experience with OpenOffice Calc, and 
I'm finding a few ugly bugs. First I think I'm too stupid, but then I 
realize it is a bug that doesn't depend on my knowledge. I have made 
one bug report about calculating time consumption and using the result 
in a formula for next cell.. But this mail is not about that. I was 
trying to find how to make my spreadsheet to fill cells according to 
previous cell, and finally I've found, that there is no option for 
that, so I created a long formula based on "IF" logic task. So now 
when I type a Name in "LOCATION" cell, it automatically recognizes it, 
and fills following cells "MILES", "POSTCODE", and "PAYRATE". Makes it 
so much easier, but makes difficult creating and maintaining the 
formula. So for now my 3-customer formula looks like this:
=IF(C371="global 
stansted";"CM235PU";IF(C371="Mojito";"CM235PU";IF(C371="grafton 
cambridge";"CM11HE";"-")))

And it is only beginning of my Self Employment.

 So my suggestion is to create an additional AutoFill form where user 
can make a list of related data in specific columns or rows to fill up 
multiple cells at the same time.


Thank you very much for such a wonderful opportunity to use a free 
Office Sofware!!! You guys rock!!!


Attaching a piece of my Spreadsheet that shows  bug in calculation of 
a "TOTAL INC" column, and the idea about AutoFill...


-----
To unsubscribe, e-mail:dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail:dev-h...@openoffice.apache.org


Spreadsheet-bug.ods
Description: application/vnd.oasis.opendocument.spreadsheet

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Spreadsheet

[Dave Barton]

 Original Message 
From: ed tinervin 
To: dev@openoffice.apache.org 
Date: Fri, 2 Feb 2018 11:48:23 + (utc)

> I have not found anything to tell me how to print the cell lines in the 
> spreadsheet. Can you help Thanks

Hi Ed,

How-to questions like this are best addressed to the user support list
us...@openoffice.apache.org

You can find the answer to your question by clicking on the Calc Help
menu or pressing the F1 function key on your keyboard, then type grids.

Summary: From the Calc menu select "Format Page..." and in the dialog
(window) that opens click to tick/check the "Grid" under the "Print"
option of the "Sheet" tab.

Hope this helps.

Dave

-- 
Give a man a fish to feed him for a day. Teach a man to fish and feed
him for a lifetime.

Spreadsheet

[ed tinervin]

I have not found anything to tell me how to print the cell lines in the 
spreadsheet. Can you help Thanks

Suggestion: Correction of Spreadsheet Menu;Name>Define

[oscarbober]

Currentproblem: When I put the <=> sign in a spreadsheet cell and thengo to the 
'Location Cell' which is situated just 


abovethe column headings on the left side of the screen and click on 
thelocation cell, there appears a partial list of 


functionsbeginning with the function for sum().  This list prevents thelisting 
of created names for locations on the 


spreadsheetwhich I made to access so as to travel to and use for 
calculationpurposes.  The functions so listed in this area 


area partial duplication of the complete list of functions obtained bythe 
insert.Currently, I haveto 


knowthe geographical location of all forms I copied onto the spreadsheetpage in 
order to move the cursor to the location 


(by notusing an equal sign and then after entering the location cell I 
canlocate the defined name, copy it and return to my original cell 


andcopy the name which then gives me the amount I am interested 
in).Alternatively, if I remember the defined name I can enter it 


inthe cell I want to enter a calculation and then complete thecalculation. 
(I've devloped on a spreadsheet copies of  IRS 


formsI use with the appropriate formulae required to calculate my incometax 
each year).  I want to use the = sign so when 


Ienter = where I am working and then place my cursor on the locationcell thus 
have my list of defined names made available, I can then 


selectthe desired name and press ,enter>. This will copy the originalcell entry 
(usually, a number) into the desired cell 


whereI can work on it if desired. This procedure would save meconsiderable time 
in updating figures and checking them.


PossibleSolution: Eliminate the partial list of functions from the LocationCell 
when an = sign is created in any spreadsheet cell.




I hope my suggestion is sufficiently clear. If not please let me know
Thanks.
Oscar

Re: Chart SubTitles Do Not Get Saved In Spreadsheet

[FR web forum]

Issue reported:
https://bz.apache.org/ooo/show_bug.cgi?id=92357

- Mail original -
De: "FR web forum" 
À: dev@openoffice.apache.org
Envoyé: Vendredi 16 Décembre 2016 09:01:51
Objet: Re: Chart SubTitles Do Not Get Saved In Spreadsheet

Hello,
This happens if your document is saved in XLS.
Next time, for this kind of question, please use our user mailing list:
http://mail-archives.apache.org/mod_mbox/openoffice-users/

- Mail original -
De: "Walt & Sharon Rager" 
À: dev@openoffice.apache.org
Envoyé: Jeudi 15 Décembre 2016 19:40:07
Objet: Chart SubTitles Do Not Get Saved In Spreadsheet

Hello,

I configured several charts in a spreadsheet.  When I double click on a chart 
and then right click on it, a dialog box opens and allows me to click on 
“Insert Titles”.  The next dialog box that opens allows me to enter a Title and 
Subtitle among other things.  When I enter the Title and Subtitle they both 
appear on the chart and everything looks like it worked ok.  I then save the 
spreadsheet.  When I open the spreadsheet again, the Title is there but the 
Subtitle has disappeared.  I tried this several times and it happens every 
time.  Somehow the Subtitle is not being saved.  Am I doing something wrong or 
is there a problem in the code?

I am using program version 4.1.3 of OpenOffice.

Thank you,
Walt Rager

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org


-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Chart SubTitles Do Not Get Saved In Spreadsheet

[FR web forum]

Hello,
This happens if your document is saved in XLS.
Next time, for this kind of question, please use our user mailing list:
http://mail-archives.apache.org/mod_mbox/openoffice-users/

- Mail original -
De: "Walt & Sharon Rager" 
À: dev@openoffice.apache.org
Envoyé: Jeudi 15 Décembre 2016 19:40:07
Objet: Chart SubTitles Do Not Get Saved In Spreadsheet

Hello,

I configured several charts in a spreadsheet.  When I double click on a chart 
and then right click on it, a dialog box opens and allows me to click on 
“Insert Titles”.  The next dialog box that opens allows me to enter a Title and 
Subtitle among other things.  When I enter the Title and Subtitle they both 
appear on the chart and everything looks like it worked ok.  I then save the 
spreadsheet.  When I open the spreadsheet again, the Title is there but the 
Subtitle has disappeared.  I tried this several times and it happens every 
time.  Somehow the Subtitle is not being saved.  Am I doing something wrong or 
is there a problem in the code?

I am using program version 4.1.3 of OpenOffice.

Thank you,
Walt Rager

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Chart SubTitles Do Not Get Saved In Spreadsheet

[Walt & Sharon Rager]

Hello,

I configured several charts in a spreadsheet.  When I double click on a chart 
and then right click on it, a dialog box opens and allows me to click on 
“Insert Titles”.  The next dialog box that opens allows me to enter a Title and 
Subtitle among other things.  When I enter the Title and Subtitle they both 
appear on the chart and everything looks like it worked ok.  I then save the 
spreadsheet.  When I open the spreadsheet again, the Title is there but the 
Subtitle has disappeared.  I tried this several times and it happens every 
time.  Somehow the Subtitle is not being saved.  Am I doing something wrong or 
is there a problem in the code?

I am using program version 4.1.3 of OpenOffice.

Thank you,
Walt Rager

Re: Problems with Spreadsheet, A repeat but with an added note

[J J Wilkerson]

Of course, it's not the same day everywhere. Thank you for responding so 
quickly. Problem solved.
J. Wilkerson
 

On Sunday, July 17, 2016 12:59 PM, Dave  wrote:
 

   Original Message 
 From: J J Wilkerson mailto:wilklaw2...@yahoo.com.INVALID
 To: dev@openoffice.apache.org mailto:dev@openoffice.apache.org
 Date: Sun, 17 Jul 2016 16:15:47 + (UTC)
 
  
   Hello again,
I realize that this is Sunday so I might not get a reply for some time, and 
that is fine. Any help you can offer will be greatly appreciated.
I've been using Open Office for Word Pad and Spreadsheets for more than a year 
and installed 4.1.2 a long time ago. Yesterday I went t open a spreadsheet that 
I have been adding to for about two months. All of a sudden I can't open any 
spreadsheet with Open Office. I reinstalled 4.1.2 but that has made no 
difference. What do I do next to open my spreadsheets?
Just wanted to add that I thought about uninstalling Open Office, but I was 
concerned that that might affect all the Word Pad documents that I have and 
frequently use.

Thanks,
J. Wilkerson
 
 In some parts of the world Sunday was yesterday :-) It's a little confusing 
when you say "using Open Office for Word Pad", because Apache OpenOffice Writer 
and Windows WordPad are similar, but unrelated programs. What happens when you 
try to open your spreadsheet? Does Calc appear to be starting to load? Do you 
see any error messages and if so what do they say? Try starting Calc from the 
system menu (eg. Windows Start button) which automatically starts a new 
spreadsheet. If that works, try using Calc's main menu "File -> Open..." option 
to open your spreadsheet. The old Windows myth about uninstalling and 
reinstalling fixing problems rarely works for Apache OpenOffice. An often 
recommended solution to these kinds of issues is to delete or (preferably) 
rename what is known as the "User Profile". For details about how to do this 
see the following tutorial in the User Forum: Reset your user 
profile:https://forum.openoffice.org/en/forum/viewtopic.php?f=74&t=12426#p58403.
 Hope this helps. Dave -- 
 Any reply should only be addressed to this mailing list. All messages 
addressed to nore...@tasit.net are automatically deleted from the server and 
will never be read. 

  

Re: Problems with Spreadsheet, A repeat but with an added note

[Dave]

 Original Message 
From: J J Wilkerson 
To: dev@openoffice.apache.org 
Date: Sun, 17 Jul 2016 16:15:47 + (UTC)

>   Hello again,
> I realize that this is Sunday so I might not get a reply for some time, and 
> that is fine. Any help you can offer will be greatly appreciated.
> I've been using Open Office for Word Pad and Spreadsheets for more than a 
> year and installed 4.1.2 a long time ago. Yesterday I went t open a 
> spreadsheet that I have been adding to for about two months. All of a sudden 
> I can't open any spreadsheet with Open Office. I reinstalled 4.1.2 but that 
> has made no difference. What do I do next to open my spreadsheets?
> Just wanted to add that I thought about uninstalling Open Office, but I was 
> concerned that that might affect all the Word Pad documents that I have and 
> frequently use.
>
> Thanks,
> J. Wilkerson

In some parts of the world Sunday was yesterday :-)

It's a little confusing when you say "/using Open Office for Word Pad/",
because Apache OpenOffice Writer and Windows WordPad are similar, but
unrelated programs.

What happens when you try to open your spreadsheet? Does Calc appear to
be starting to load? Do you see any error messages and if so what do
they say?

Try starting Calc from the system menu (eg. Windows Start button) which
automatically starts a new spreadsheet. If that works, try using Calc's
main menu "File -> Open..." option to open your spreadsheet.

The old Windows myth about uninstalling and reinstalling fixing problems
rarely works for Apache OpenOffice. An often recommended solution to
these kinds of issues is to delete or (preferably) rename what is known
as the "/User Profile/". For details about how to do this see the
following tutorial in the User Forum: Reset your user profile:
https://forum.openoffice.org/en/forum/viewtopic.php?f=74&t=12426#p58403.

Hope this helps.

Dave

-- 
Any reply should only be addressed to this mailing list. All messages
addressed to nore...@tasit.net are automatically deleted from the server
and will never be read.

Problems with Spreadsheet, A repeat but with an added note

[J J Wilkerson]

  Hello again,
I realize that this is Sunday so I might not get a reply for some time, and 
that is fine. Any help you can offer will be greatly appreciated.
I've been using Open Office for Word Pad and Spreadsheets for more than a year 
and installed 4.1.2 a long time ago. Yesterday I went t open a spreadsheet that 
I have been adding to for about two months. All of a sudden I can't open any 
spreadsheet with Open Office. I reinstalled 4.1.2 but that has made no 
difference. What do I do next to open my spreadsheets?
Just wanted to add that I thought about uninstalling Open Office, but I was 
concerned that that might affect all the Word Pad documents that I have and 
frequently use.

Thanks,
J. Wilkerson

Fw: First Problem is Spreadsheet

[Howard Morris (aka Col Boogie)]

Letting everyone know the status
Howard
___

From: Mike Rivera 
Sent: Tuesday, January 26, 2016 7:12 PM
To: Howard Morris (aka Col Boogie) 
Subject: Re: First Problem is Spreadsheet

I tried again & it worked properly ,, thx alot for
the prompt reply... Mike




On Tuesday, January 26, 2016 4:07 PM, Howard Morris (aka Col Boogie) 
 wrote:




Sorry, I missed that.
I found your h15 with the hand entry. I erased that (via backspace) and copied 
h14 (ctrl +c) and pasted it (ctrl +v) to h15 and all was well.
This time I could not repeat your problem.

Howard

__

If you go to the bottom of sheet, you will 3 sections- first section is 
checking, next section is Savings, and last one is Budget.. You will find h15 
in the Savings section..and I copied it to paste the formula and it still 
didn't work. Also, I deleted that row and tried it again and still didn't 
work...




On Monday, January 25, 2016 10:01 PM, Howard Morris (aka Col Boogie) 
 wrote:




Thank you,
  I am a bit confused. Your example shows “trf to chking”. I did not find that 
in the spreadsheet you sent me. However, I did a little testing. I did some 
testing anyway, and some curious things happened. Sometimes when I changes 
things, the value was not recalculated. However, when I copied pasted the BAL 
formula from the line above it, things were quickly fixed. I am sorry that this 
is just a workaround for your problem, but we will look deeper into it later.

Howard

Re: First Problem is Spreadsheet

[Howard Morris (aka Col Boogie)]

Sorry, I missed that.
I found your h15 with the hand entry. I erased that (via backspace) and copied 
h14 (ctrl +c) and pasted it (ctrl +v) to h15 and all was well.
This time I could not repeat your problem.

Howard

__

If you go to the bottom of sheet, you will 3 sections- first section is 
checking, next section is Savings, and last one is Budget.. You will find h15 
in the Savings section..and I copied it to paste the formula and it still 
didn't work. Also, I deleted that row and tried it again and still didn't 
work...




On Monday, January 25, 2016 10:01 PM, Howard Morris (aka Col Boogie) 
 wrote:




Thank you,
  I am a bit confused. Your example shows “trf to chking”. I did not find that 
in the spreadsheet you sent me. However, I did a little testing. I did some 
testing anyway, and some curious things happened. Sometimes when I changes 
things, the value was not recalculated. However, when I copied pasted the BAL 
formula from the line above it, things were quickly fixed. I am sorry that this 
is just a workaround for your problem, but we will look deeper into it later.

Howard

Re: First Problem is Spreadsheet

[Howard Morris (aka Col Boogie)]

Thank you,
  I am a bit confused. Your example shows “trf to chking”. I did not find that 
in the spreadsheet you sent me. However, I did a little testing. I did some 
testing anyway, and some curious things happened. Sometimes when I changes 
things, the value was not recalculated. However, when I copied pasted the BAL 
formula from the line above it, things were quickly fixed. I am sorry that this 
is just a workaround for your problem, but we will look deeper into it later.

Howard

Re: First Problem is Spreadsheet

[Howard Morris (aka Col Boogie)]

Hi,
I am not sure, but please check the ranges used in that box for the spread 
sheet.  You may have used sum(D2:Z2) and the data has extended beyond Z2. While 
I do not know of a maximum number of things that can be added, etc., You may 
also try sum of the first so many things and use that value as part of another 
sum.
Howard Morris

PS. It would be easier for us if you sent a copy of the spreadsheet and tell us 
which cell you are getting erroneous results. 

First Problem is Spreadsheet

[Mike Rivera]

Hi, I love your software but, I have encountered a problem with it. I use it as 
a budget spreadsheet and suddenly  one of the boxes that keeps my balance 
correctly does not do so... I've had to write in the amount  cause no matter 
what I do, it does not calculate correctly... Please reply if you have a 
remedy..
thank you,, Mike 
949-230-2045I use your software for everything..

Re: DATE ISSUE in OpenOffice Spreadsheet

[F C. Costero]

On Mon, Oct 5, 2015 at 11:02 AM, Oliver Brinzing 
wrote:

> Hi,
>
> > There seems to be an issue of how some1900 dates are being stored in the
> OpenOffice spreadsheet.
>
>> It is my understanding that dates are stored with spreadsheet as a serial
>> number starting with 01/01/1900
>> being stored as a '1'.
>> When 01/01/1900 is looked at as a general number format in OpenOffice it
>> appears as '2' versus a '1'.
>>
>
> The default starting date is 30 December 1899 = 0
> please see
> https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_Date_%26_Time_functions
>
> Regards
> Oliver
>
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>
> Part of the confusion is that 1900 was not a leap year. Years that are
multiples of 100 are not leap years unless they are also multiples of 400.
That is to account for the actual year being slightly less than 365.25 days
and is the difference between the Gregorian calendar and the earlier Julian
calendar.. Not all spreadsheets get that right.
Best regards,
Francis

Re: DATE ISSUE in OpenOffice Spreadsheet

[Oliver Brinzing]

Hi,

> There seems to be an issue of how some1900 dates are being stored in the 
OpenOffice spreadsheet.

It is my understanding that dates are stored with spreadsheet as a serial 
number starting with 01/01/1900
being stored as a '1'.
When 01/01/1900 is looked at as a general number format in OpenOffice it 
appears as '2' versus a '1'.


The default starting date is 30 December 1899 = 0
please see 
https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_Date_%26_Time_functions

Regards
Oliver

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

DATE ISSUE in OpenOffice Spreadsheet

[Perry 'Johnny' Levine]

There seems to be an issue of how some1900 dates are being stored in the 
OpenOffice spreadsheet.

It is my understanding that dates are stored with spreadsheet as a serial 
number starting with 01/01/1900
being stored as a '1'.

When 01/01/1900 is looked at as a general number format in OpenOffice it 
appears as '2' versus a '1'.
When 02/01/1900 is looked at as a general number format in OpenOffice is 
appears as '33' versus '32'.
When 12/31/1900 is looked at as a general number format in OpenOffice it 
appears as '366' which would be correct
since 1900 is a leap year.


12/31/1900 is the stored as the correct serial number because 02/29/1900 is not 
considered a valid date in 
OpenOffice and therefore 03/01/1900 is stored as a '61' which is correct and 
the following dates are stored
correct for 1900. 

Will look at other years which are leap years to see if similar problem exists.

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 09.09.2013 22:03, Andrea Pescetti wrote:

On 03/09/2013 Herbert Duerr wrote:

That's a good idea. Here is a first very rough draft:
https://blogs.apache.org/roller-ui/authoring/preview/OOo/?previewEntry=writing_spreadsheet_test_cases_now


That link is behind an editor password. Here's the public preview link:

https://blogs.apache.org/preview/OOo/?previewEntry=writing_spreadsheet_test_cases_now


And the post is nice too! I would just add something about how people
can help. I assume the best way is to contact the QA list and say that
they could contribute testcases.


Good idea. And thanks for the feedback! I'm afraid I have to postpone 
working on it until we released 4.0.1, though.



Since testcases must be attached anyway, one way to get them could be to
open a specific issue in Bugzilla and attach testcases to is as they
become available.


Yes. And I'm sure that we already have quite a few test documents in 
bugzilla that need just a few tweaks to be usable for automated this 
automated formula testing.


Herbert

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Shemil Hashan]

I need help on steps to make the build on fedora 19.
Please give me steps in simple language with terminal code.
It is urgent. I'm a student of University of Moratuwa,doing a project to
give predictive text for Open Office.
Reply me ASAP,im stuck on the build.


On Mon, Sep 9, 2013 at 4:03 PM, Andrea Pescetti  wrote:

> On 03/09/2013 Herbert Duerr wrote:
>
>> That's a good idea. Here is a first very rough draft:
>> https://blogs.apache.org/**roller-ui/authoring/preview/**
>> OOo/?previewEntry=writing_**spreadsheet_test_cases_now
>>
>
> That link is behind an editor password. Here's the public preview link:
>
> https://blogs.apache.org/**preview/OOo/?previewEntry=**
> writing_spreadsheet_test_**cases_now
>
> And the post is nice too! I would just add something about how people can
> help. I assume the best way is to contact the QA list and say that they
> could contribute testcases.
>
> Since testcases must be attached anyway, one way to get them could be to
> open a specific issue in Bugzilla and attach testcases to is as they become
> available.
>
> Regards,
>   Andrea.
>
>
> --**--**-
> To unsubscribe, e-mail: 
> dev-unsubscribe@openoffice.**apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>
>

Re: Example of spreadsheet formula testing

[Andrea Pescetti]

On 03/09/2013 Herbert Duerr wrote:

That's a good idea. Here is a first very rough draft:
https://blogs.apache.org/roller-ui/authoring/preview/OOo/?previewEntry=writing_spreadsheet_test_cases_now


That link is behind an editor password. Here's the public preview link:

https://blogs.apache.org/preview/OOo/?previewEntry=writing_spreadsheet_test_cases_now

And the post is nice too! I would just add something about how people 
can help. I assume the best way is to contact the QA list and say that 
they could contribute testcases.


Since testcases must be attached anyway, one way to get them could be to 
open a specific issue in Bugzilla and attach testcases to is as they 
become available.


Regards,
  Andrea.

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 02.09.2013 21:25, Andrea Pescetti wrote:

On 27/08/2013 Herbert Duerr wrote:

A good idea. I just wrote a task [1] and a script [2] that takes a
slightly modified version of Regina's sample document and checks whether
all of its tests pass. This script is now part of the Functional
Verification Test (a.k.a. FVT).
[1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
[2] http://svn.apache.org/r1517802


Really nice stuff! Where's the blog post?

Seriously, with this simple framework we make testcase preparation
accessible to almost all Calc users, so let's give it the visibility it
deserves.


That's a good idea. Here is a first very rough draft:
https://blogs.apache.org/roller-ui/authoring/preview/OOo/?previewEntry=writing_spreadsheet_test_cases_now

Herbert

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Andrea Pescetti]

On 27/08/2013 Herbert Duerr wrote:

A good idea. I just wrote a task [1] and a script [2] that takes a
slightly modified version of Regina's sample document and checks whether
all of its tests pass. This script is now part of the Functional
Verification Test (a.k.a. FVT).
[1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
[2] http://svn.apache.org/r1517802


Really nice stuff! Where's the blog post?

Seriously, with this simple framework we make testcase preparation 
accessible to almost all Calc users, so let's give it the visibility it 
deserves.


Regards,
  Andrea.

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

4.0.1_release_blocker granted: [Bug 122982] Copying spreadsheet into another document in bit map format has disappeared in Version 4. Worked on Version 3.

[bugzilla]

j...@apache.org has granted Armin Le Grand 's request for
4.0.1_release_blocker:
Bug 122982: Copying spreadsheet into another document in bit map format has
disappeared in Version 4.  Worked on Version 3.
https://issues.apache.org/ooo/show_bug.cgi?id=122982


--- Additional Comments from j...@apache.org
approve showstopper request

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

4.0.1_release_blocker requested: [Bug 122982] Copying spreadsheet into another document in bit map format has disappeared in Version 4. Worked on Version 3.

[bugzilla]

Armin Le Grand  has asked  for 4.0.1_release_blocker:
Bug 122982: Copying spreadsheet into another document in bit map format has
disappeared in Version 4.  Worked on Version 3.
https://issues.apache.org/ooo/show_bug.cgi?id=122982


--- Additional Comments from Armin Le Grand 
ALG: Okay, looks good and is plausible. Also a candidate for AOO401, setting
flag. Comitted to trunk.

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 28.08.2013 16:04, Rob Weir wrote:

On Wed, Aug 28, 2013 at 10:00 AM, Herbert Duerr  wrote:

[...]
Yes, just keep the test-ids of the rows with intermediate results empty.
They will be ignored.


Marking lines to ignore with content in a specific column -- this is
so FORTRAN.  I love it.


I think the approach is quite intuitive and there is nothing retro about 
it: Only results that are interesting enough to be reported if they fail 
are checked. These reports should of course contain the name of the 
failed test. If there is no name then there isn't anything worth reporting.


Herbert

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Rob Weir]

On Wed, Aug 28, 2013 at 10:00 AM, Herbert Duerr  wrote:
> On 28.08.2013 14:30, Rob Weir wrote:
>>
>> On Wed, Aug 28, 2013 at 4:51 AM, Herbert Duerr  wrote:
>>>
>>> On 27.08.2013 15:29, Rob Weir wrote:
>>>>
>>>>
>>>> On Tue, Aug 27, 2013 at 8:44 AM, Herbert Duerr  wrote:
>>>>>
>>>>>
>>>>> [...]
>>>>>
>>>>> A good idea. I just wrote a task [1] and a script [2] that takes a
>>>>> slightly
>>>>> modified version of Regina's sample document and checks whether all of
>>>>> its
>>>>> tests pass. This script is now part of the Functional Verification Test
>>>>> (a.k.a. FVT).
>>>>>
>>>>> [1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
>>>>> [2] http://svn.apache.org/r1517802
>>>>>
>>>>> Adding further test spreadsheets is simple. They need a "TestID" row
>>>>> and
>>>>> a
>>>>> "TestOK" row like [3]. Such new test spreadsheets have to be added to
>>>>> [4]'s
>>>>> testFormulaDocs() method to be picked up.
>>>>>
>>>>
>>>> Nice.  If we can have test cases written by non-programmers, and then
>>>> automated testing of these sheets, then we'll have the best of both
>>>> worlds.
>>>>
>>>> I assume we'd want to agree on a test template.
>>>
>>>
>>>
>>> Here is a suggested test template. It is small and fulfills the
>>> requirements
>>> of containing exactly one sheet and having two columns named "TestID" and
>>> "TestOK". Please use a monospaced font for your mail client to view it:
>>>
>>>   A   B   CD   E   F
>>> 1  TestIDAddend1 Addend2 ExpectedSum CalculatedSum  TestOK
>>> 2   "OnePlusOne"1   1 2 =B2+C2 =(D2=E2)
>>> 3  "Seven"  3   4 7 =B3+C3 =(D3=E3)
>>>
>>>
>>
>> You are able to then introspect the sheet, similar to JUnit, and
>> report the results of each row?  That would be good.
>
>
> Yes, the script uses Liu Zhe's test framework based on junit to introspect
> the sheet.
>
> It only looks for the identifiers "TestID" and "TestOK" so it doesn't matter
> in which columns they are. For performance reason it doesn't check every one
> of the 32 million possible cell positions for these markers but only the
> first 8x8 cells for now. These limits can be increased, but as the test
> documents are also intended for human consumption keeping these markers at
> the start of the test document is a reasonable restriction.
>
>
>> A few complicating factors to consider:
>>
>> 1) Not all functions take two parameters.  Some take more, some less,
>> and many are variable number.  So if the test driver is sensitive to
>> only the label "TestID" and "TestOK" (and not the column index) this
>> would be best.
>
>
> Yes. The other columns are completely ignored. They are just for making the
> calculation clean and obvious for the reader of the spreadsheet.
>
>
>> 2) Some functions operate on ranges and require additional data, stuff
>> that cannot easily be fit into a single row.  For example, DCOUNT(),
>> VLOOKUP(), etc.  Where should we put that test data, so it will not
>> mess up the automation?  Would it be safe to mark the end of the test
>> cases by a blank row, and then any extra test data after that?
>
>
> Rows with empty test-ids are ignored. The test-id of a row is the text
> content of the cell "TestID" column.
>
>
>> 3) Then we have array functions, for example matrix functions like
>> MMULT().  These don't return a single value, but return values into a
>> range.  Maybe in those cases we would do the calculation in an special
>> "test data" area of the sheet, and then have test cases, one per row,
>> to test each value of the returned matrix.  So a 2x2 matrix would have
>> 4 test rows.
>
>
> Yes, just keep the test-ids of the rows with intermediate results empty.
> They will be ignored.
>

Marking lines to ignore with content in a specific column -- this is
so FORTRAN.  I love it.

-Rob


>
> Herbert
>
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 28.08.2013 14:30, Rob Weir wrote:

On Wed, Aug 28, 2013 at 4:51 AM, Herbert Duerr  wrote:

On 27.08.2013 15:29, Rob Weir wrote:


On Tue, Aug 27, 2013 at 8:44 AM, Herbert Duerr  wrote:


[...]

A good idea. I just wrote a task [1] and a script [2] that takes a
slightly
modified version of Regina's sample document and checks whether all of
its
tests pass. This script is now part of the Functional Verification Test
(a.k.a. FVT).

[1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
[2] http://svn.apache.org/r1517802

Adding further test spreadsheets is simple. They need a "TestID" row and
a
"TestOK" row like [3]. Such new test spreadsheets have to be added to
[4]'s
testFormulaDocs() method to be picked up.



Nice.  If we can have test cases written by non-programmers, and then
automated testing of these sheets, then we'll have the best of both
worlds.

I assume we'd want to agree on a test template.



Here is a suggested test template. It is small and fulfills the requirements
of containing exactly one sheet and having two columns named "TestID" and
"TestOK". Please use a monospaced font for your mail client to view it:

  A   B   CD   E   F
1  TestIDAddend1 Addend2 ExpectedSum CalculatedSum  TestOK
2   "OnePlusOne"1   1 2 =B2+C2 =(D2=E2)
3  "Seven"  3   4 7 =B3+C3 =(D3=E3)




You are able to then introspect the sheet, similar to JUnit, and
report the results of each row?  That would be good.


Yes, the script uses Liu Zhe's test framework based on junit to 
introspect the sheet.


It only looks for the identifiers "TestID" and "TestOK" so it doesn't 
matter in which columns they are. For performance reason it doesn't 
check every one of the 32 million possible cell positions for these 
markers but only the first 8x8 cells for now. These limits can be 
increased, but as the test documents are also intended for human 
consumption keeping these markers at the start of the test document is a 
reasonable restriction.



A few complicating factors to consider:

1) Not all functions take two parameters.  Some take more, some less,
and many are variable number.  So if the test driver is sensitive to
only the label "TestID" and "TestOK" (and not the column index) this
would be best.


Yes. The other columns are completely ignored. They are just for making 
the calculation clean and obvious for the reader of the spreadsheet.



2) Some functions operate on ranges and require additional data, stuff
that cannot easily be fit into a single row.  For example, DCOUNT(),
VLOOKUP(), etc.  Where should we put that test data, so it will not
mess up the automation?  Would it be safe to mark the end of the test
cases by a blank row, and then any extra test data after that?


Rows with empty test-ids are ignored. The test-id of a row is the text 
content of the cell "TestID" column.



3) Then we have array functions, for example matrix functions like
MMULT().  These don't return a single value, but return values into a
range.  Maybe in those cases we would do the calculation in an special
"test data" area of the sheet, and then have test cases, one per row,
to test each value of the returned matrix.  So a 2x2 matrix would have
4 test rows.


Yes, just keep the test-ids of the rows with intermediate results empty. 
They will be ignored.


Herbert

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Rob Weir]

On Wed, Aug 28, 2013 at 4:51 AM, Herbert Duerr  wrote:
> On 27.08.2013 15:29, Rob Weir wrote:
>>
>> On Tue, Aug 27, 2013 at 8:44 AM, Herbert Duerr  wrote:
>>>
>>> [...]
>>>
>>> A good idea. I just wrote a task [1] and a script [2] that takes a
>>> slightly
>>> modified version of Regina's sample document and checks whether all of
>>> its
>>> tests pass. This script is now part of the Functional Verification Test
>>> (a.k.a. FVT).
>>>
>>> [1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
>>> [2] http://svn.apache.org/r1517802
>>>
>>> Adding further test spreadsheets is simple. They need a "TestID" row and
>>> a
>>> "TestOK" row like [3]. Such new test spreadsheets have to be added to
>>> [4]'s
>>> testFormulaDocs() method to be picked up.
>>>
>>
>> Nice.  If we can have test cases written by non-programmers, and then
>> automated testing of these sheets, then we'll have the best of both
>> worlds.
>>
>> I assume we'd want to agree on a test template.
>
>
> Here is a suggested test template. It is small and fulfills the requirements
> of containing exactly one sheet and having two columns named "TestID" and
> "TestOK". Please use a monospaced font for your mail client to view it:
>
>  A   B   CD   E   F
> 1  TestIDAddend1 Addend2 ExpectedSum CalculatedSum  TestOK
> 2   "OnePlusOne"1   1 2 =B2+C2 =(D2=E2)
> 3  "Seven"  3   4 7 =B3+C3 =(D3=E3)
>
>

You are able to then introspect the sheet, similar to JUnit, and
report the results of each row?  That would be good.

A few complicating factors to consider:

1) Not all functions take two parameters.  Some take more, some less,
and many are variable number.  So if the test driver is sensitive to
only the label "TestID" and "TestOK" (and not the column index) this
would be best.

2) Some functions operate on ranges and require additional data, stuff
that cannot easily be fit into a single row.  For example, DCOUNT(),
VLOOKUP(), etc.  Where should we put that test data, so it will not
mess up the automation?  Would it be safe to mark the end of the test
cases by a blank row, and then any extra test data after that?

3) Then we have array functions, for example matrix functions like
MMULT().  These don't return a single value, but return values into a
range.  Maybe in those cases we would do the calculation in an special
"test data" area of the sheet, and then have test cases, one per row,
to test each value of the returned matrix.  So a 2x2 matrix would have
4 test rows.

Regards,

-Rob

>>  For example, one
>> approach is to bootstrap the tests like this:
>>
>> 1) Have the fundamental test cases for IF(), TRUE(), FALSE() and a
>> handful of other core functions written in Java, not requiring a test
>> spreadsheet.
>
>
> Core functionionality must be checked in one of the pure automatic test
> suites like BVT ("Build Verification Tests") or FVT ("Functional
> Verification Tests") of course.
>
> The idea that additionally to this traditional tests there should also be
> test cases suitable for both manual and automated testing and that can be
> understood, checked and enhanced by non-developers opens options for testers
> and power-users that were not open before to non-developers.
>
>
>> 2) Then write the test cases for the specialized functions in test
>> documents, and reserve a cell (A1 for example) to return 1 if all
>> tests in the document pass, 0 otherwise.
>
>
> The new test script checks the results of each individual sub-test and the
> test log reports each failed test. Having a specialized cell that summarizes
> all the sub-tests into one result is possible of course. For the example
> above a cell =(SUM(F2:F3)=COUNT(F2:F3)) would provide just that. It isn't
> needed for the new automatism though.
>
> The test spreadsheets should mostly be developed to be most useful for
> manual testing and for extending by testers and power users while keeping
> the requirements ("one sheet", "one TestID column" and "one TestOK column")
> for the new automation at a minimum. Adding more requirements again would be
> counterproductive as lowering the barrier of entry is the central point of
> the new mechanism.
>
>
>> 3) Automate the loading and evaluation of the test documents.
>>
>> (Since the test documents would depend on correct operation of IF(),
>> we

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 27.08.2013 15:29, Rob Weir wrote:

On Tue, Aug 27, 2013 at 8:44 AM, Herbert Duerr  wrote:

[...]
A good idea. I just wrote a task [1] and a script [2] that takes a slightly
modified version of Regina's sample document and checks whether all of its
tests pass. This script is now part of the Functional Verification Test
(a.k.a. FVT).

[1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
[2] http://svn.apache.org/r1517802

Adding further test spreadsheets is simple. They need a "TestID" row and a
"TestOK" row like [3]. Such new test spreadsheets have to be added to [4]'s
testFormulaDocs() method to be picked up.



Nice.  If we can have test cases written by non-programmers, and then
automated testing of these sheets, then we'll have the best of both
worlds.

I assume we'd want to agree on a test template.


Here is a suggested test template. It is small and fulfills the 
requirements of containing exactly one sheet and having two columns 
named "TestID" and "TestOK". Please use a monospaced font for your mail 
client to view it:


 A   B   CD   E   F
1  TestIDAddend1 Addend2 ExpectedSum CalculatedSum  TestOK
2   "OnePlusOne"1   1 2 =B2+C2 =(D2=E2)
3  "Seven"  3   4 7 =B3+C3 =(D3=E3)


 For example, one
approach is to bootstrap the tests like this:

1) Have the fundamental test cases for IF(), TRUE(), FALSE() and a
handful of other core functions written in Java, not requiring a test
spreadsheet.


Core functionionality must be checked in one of the pure automatic test 
suites like BVT ("Build Verification Tests") or FVT ("Functional 
Verification Tests") of course.


The idea that additionally to this traditional tests there should also 
be test cases suitable for both manual and automated testing and that 
can be understood, checked and enhanced by non-developers opens options 
for testers and power-users that were not open before to non-developers.



2) Then write the test cases for the specialized functions in test
documents, and reserve a cell (A1 for example) to return 1 if all
tests in the document pass, 0 otherwise.


The new test script checks the results of each individual sub-test and 
the test log reports each failed test. Having a specialized cell that 
summarizes all the sub-tests into one result is possible of course. For 
the example above a cell =(SUM(F2:F3)=COUNT(F2:F3)) would provide just 
that. It isn't needed for the new automatism though.


The test spreadsheets should mostly be developed to be most useful for 
manual testing and for extending by testers and power users while 
keeping the requirements ("one sheet", "one TestID column" and "one 
TestOK column") for the new automation at a minimum. Adding more 
requirements again would be counterproductive as lowering the barrier of 
entry is the central point of the new mechanism.



3) Automate the loading and evaluation of the test documents.

(Since the test documents would depend on correct operation of IF(),
we cannot use the test documents to test IF() itself)


A build where simple things like IF() don't work should never make it 
into manual testing. Testers should expect things like that to work.


Herbert

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Rob Weir]

On Tue, Aug 27, 2013 at 8:44 AM, Herbert Duerr  wrote:
> On 25.08.2013 16:12, Andrea Pescetti wrote:
>>
>> On 19/08/2013 Herbert Duerr wrote:
>>>
>>> An example of a test case where a formula ("addition") is checked is in
>>> [1]. This file is clean and easy enough that it could be used as a
>>> template for more formula checks.
>>> [1]
>>>
>>> http://svn.apache.org/repos/asf/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/AddtionOperatorInFormula.java
>>>
>>
>> I find it quite verbose (well, it's Java) and too hard for an "ordinary"
>> QA volunteer, who could probably write a simple ODT file to check a
>> bugfix in formula handling but couldn't write a Java testcase.
>>
>> Of course, this approach has also huge benefits, like automation.
>>
>> Perhaps it is possible to convert a few key test cases, like Regina's
>> ODT file in the blocker bug
>> https://issues.apache.org/ooo/show_bug.cgi?id=122997 to this framework?
>
>
> A good idea. I just wrote a task [1] and a script [2] that takes a slightly
> modified version of Regina's sample document and checks whether all of its
> tests pass. This script is now part of the Functional Verification Test
> (a.k.a. FVT).
>
> [1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
> [2] http://svn.apache.org/r1517802
>
> Adding further test spreadsheets is simple. They need a "TestID" row and a
> "TestOK" row like [3]. Such new test spreadsheets have to be added to [4]'s
> testFormulaDocs() method to be picked up.
>

Nice.  If we can have test cases written by non-programmers, and then
automated testing of these sheets, then we'll have the best of both
worlds.

I assume we'd want to agree on a test template.  For example, one
approach is to bootstrap the tests like this:

1) Have the fundamental test cases for IF(), TRUE(), FALSE() and a
handful of other core functions written in Java, not requiring a test
spreadsheet.

2) Then write the test cases for the specialized functions in test
documents, and reserve a cell (A1 for example) to return 1 if all
tests in the document pass, 0 otherwise.

3) Automate the loading and evaluation of the test documents.

(Since the test documents would depend on correct operation of IF(),
we cannot use the test documents to test IF() itself)

Would something like this work?

-Rob


> [2]
> http://svn.apache.org/viewvc/openoffice/trunk/test/testuno/data/uno/sc/fvt/FormulaTest1.ods
> [3]
> http://svn.apache.org/viewvc/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/TestFormulaDocs.java?view=markup
>
> Herbert
>
>
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 25.08.2013 16:12, Andrea Pescetti wrote:

On 19/08/2013 Herbert Duerr wrote:

An example of a test case where a formula ("addition") is checked is in
[1]. This file is clean and easy enough that it could be used as a
template for more formula checks.
[1]
http://svn.apache.org/repos/asf/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/AddtionOperatorInFormula.java



I find it quite verbose (well, it's Java) and too hard for an "ordinary"
QA volunteer, who could probably write a simple ODT file to check a
bugfix in formula handling but couldn't write a Java testcase.

Of course, this approach has also huge benefits, like automation.

Perhaps it is possible to convert a few key test cases, like Regina's
ODT file in the blocker bug
https://issues.apache.org/ooo/show_bug.cgi?id=122997 to this framework?


A good idea. I just wrote a task [1] and a script [2] that takes a 
slightly modified version of Regina's sample document and checks whether 
all of its tests pass. This script is now part of the Functional 
Verification Test (a.k.a. FVT).


[1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
[2] http://svn.apache.org/r1517802

Adding further test spreadsheets is simple. They need a "TestID" row and 
a "TestOK" row like [3]. Such new test spreadsheets have to be added to 
[4]'s testFormulaDocs() method to be picked up.


[2] 
http://svn.apache.org/viewvc/openoffice/trunk/test/testuno/data/uno/sc/fvt/FormulaTest1.ods
[3] 
http://svn.apache.org/viewvc/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/TestFormulaDocs.java?view=markup


Herbert

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Regina Henschel]

Hi Andrea,

Andrea Pescetti schrieb:

On 19/08/2013 Herbert Duerr wrote:

An example of a test case where a formula ("addition") is checked is in
[1]. This file is clean and easy enough that it could be used as a
template for more formula checks.
[1]
http://svn.apache.org/repos/asf/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/AddtionOperatorInFormula.java



I find it quite verbose (well, it's Java) and too hard for an "ordinary"
QA volunteer, who could probably write a simple ODT file to check a
bugfix in formula handling but couldn't write a Java testcase.

Of course, this approach has also huge benefits, like automation.

Perhaps it is possible to convert a few key test cases, like Regina's
ODT file in the blocker bug
https://issues.apache.org/ooo/show_bug.cgi?id=122997 to this framework?
In this case we have the advantage that we know the expected result, so
ExpectedData() needn't be reimplemented in Java, but it can just
function as a simple operand->result mapping.


I have reworked the testfile in a way, that it shows in a single cell, 
whether there is a regression in calculating. Perhaps someone from the 
QA team can make a test case of it?


Kind regards
Regina

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Andrea Pescetti]

On 19/08/2013 Herbert Duerr wrote:

An example of a test case where a formula ("addition") is checked is in
[1]. This file is clean and easy enough that it could be used as a
template for more formula checks.
[1]
http://svn.apache.org/repos/asf/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/AddtionOperatorInFormula.java


I find it quite verbose (well, it's Java) and too hard for an "ordinary" 
QA volunteer, who could probably write a simple ODT file to check a 
bugfix in formula handling but couldn't write a Java testcase.


Of course, this approach has also huge benefits, like automation.

Perhaps it is possible to convert a few key test cases, like Regina's 
ODT file in the blocker bug 
https://issues.apache.org/ooo/show_bug.cgi?id=122997 to this framework? 
In this case we have the advantage that we know the expected result, so 
ExpectedData() needn't be reimplemented in Java, but it can just 
function as a simple operand->result mapping.


Regards,
  Andrea.

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Inge Wallin]

On Friday, August 16, 2013 13:32:30 Rob Weir wrote:
> Moving this topic to its own thread.
> 
> It should be possible to code a very thorough set of test cases in a
> spreadsheet, without using macros or anything fancy.  Just careful
> reading of the ODF 1.2 specification and simple spreadsheet logic.
> 
> I'd like to share an example of this that I created for one of the ODF
> Plugfests.  This is a test of a single function -- YEARFRAC.  You
> probably have never touched this function, but it exhibits all the
> pathological behavior, in a purer form, of the other financial
> functions.  Specifically, it is a pure test of our "date counting
> conventions", the various ways that accountants handle date
> calculations.
> 
> The test document is here:
> 
> http://www.robweir.com/basis-test.xls
> 
> (I did it in XLS format since I wanted to make sure Microsoft could
> use it at the Plugfest as well.  At that time they were not able to
> read ODF formulas.)
> 
> This is likely the most complicated set of test cases of any
> spreadsheet formula.  So if we can test YEARFRAC this way then we can
> test any function this way.
> 
> Column C is the formula to evaluate.  Column F is the expected value,
> which is calculated by hand, according to the ODF standard.  And
> colu,mn G reports whether they match or not.  (This would be a good
> place for us to use conditional formatting as well, though in the
> Plugfest case I needed to make the spreadsheet be as vanilla as
> possible so every editor could load it)
> 
> Note that this is an exhaustive set of test cases that aim to test
> every corner of the formula.  It is a torture test.  Excel gets all
> the test cases right.  Not a surprise, since we took Excel's behavior
> as normative when writing this part of the standard.

Good test!

So how is AOO doing?

Just FYI, Calligra Sheets nails the test. :)

-Inge


> If we used an approach like this on the other spreadsheet functions,
> we could have a semi-automated test suite that would practically
> guarantee that Calc is free of calculations errors.  Once we're
> written the test cases, a modest upfront investment, it will benefit
> us with every release we do.  Heck, it would benefit LibreOffice,
> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
> they might already have such test cases defined internally.
> 
> Anyone interesting in helping with this kind of test case development?
> 
> Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
> we're not starting from a  perfect score.  But we should find an easy
> way to report on regressions.
> 
> -Rob
> 
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[sebb]

On 19 August 2013 11:41, Regina Henschel  wrote:
> Hi Jan,
>
> janI schrieb:
>
>> On 19 August 2013 12:24, Regina Henschel  wrote:
>>
> [..]
>
>>> But for both kind of testing there exists a problem with "expected
>>> values". For example, calculation of PMT needs expm1 and log1p, or
>>> calculation of LINEST needs lot of matrix calculation. It is not
>>> impossible
>>> to calculate this with Java, but who will do it? And when you use
>>> constant
>>> expected values, from where do you get them and how to ensure, that they
>>> are valid?
>>>
>>
>> Just one simple question, how do you do manually today ? use the same
>> constants.
>
>
> I use my MuPad 3.1 and sometimes calculate values on
> http://www.wolframalpha.com/
>
> Or I compare the values to those from Gnumeric and Excel. Having the same
> value does not mean that they are correct, but the other way round, having
> different values means that I have to investigate it.
>
>
>>
>> We should not try to invent a total new road, just do what we do today
>> more
>> efficiently.
>
>
> Suggestions?

If there are a *lot* of similar calculations that need to be tested,
then it might be worth investing some time looking at at ways to
expresss these simply as a script, and then write a parser to drive
the existing test code from the script, rather than having to update
the Java test code.

The advantage would be that just about anyone can create test cases,
and they are easy to check by hand.

However the parser may be a lot of work; I don't know.

Maybe there is already a suitable script language which already has
suitable examples?

Also there are various script interpreters that already undestand some
calculations, for example Commons JEXL.
It should be possible to use them to check the test cases, and
possibly even use as a basis for parsing the test cases.

> Kind regards
> Regina
>
>
>
>
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
>
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Regina Henschel]

Hi Jan,

janI schrieb:

On 19 August 2013 12:24, Regina Henschel  wrote:


[..]

But for both kind of testing there exists a problem with "expected
values". For example, calculation of PMT needs expm1 and log1p, or
calculation of LINEST needs lot of matrix calculation. It is not impossible
to calculate this with Java, but who will do it? And when you use constant
expected values, from where do you get them and how to ensure, that they
are valid?



Just one simple question, how do you do manually today ? use the same
constants.


I use my MuPad 3.1 and sometimes calculate values on 
http://www.wolframalpha.com/


Or I compare the values to those from Gnumeric and Excel. Having the 
same value does not mean that they are correct, but the other way round, 
having different values means that I have to investigate it.




We should not try to invent a total new road, just do what we do today more
efficiently.


Suggestions?

Kind regards
Regina




-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[janI]

On 19 August 2013 12:24, Regina Henschel  wrote:

> Hi Herbert,
>
> Herbert Duerr schrieb:
>
>  On 16.08.2013 21:37, Regina Henschel wrote:
>>
>>> Rob Weir schrieb:
>>>
>>>> Moving this topic to its own thread.
>>>>
>>>> It should be possible to code a very thorough set of test cases in a
>>>> spreadsheet, without using macros or anything fancy.  Just careful
>>>> reading of the ODF 1.2 specification and simple spreadsheet logic.
>>>>
>>>>
>>> Following the spec is not enough. For example, if the accuracy decreases
>>> from 14 digits to 7 digits, that is not covered by the spec.
>>>
>>> 
>>>
>>>  If we used an approach like this on the other spreadsheet functions,
>>>> we could have a semi-automated test suite that would practically
>>>> guarantee that Calc is free of calculations errors.  Once we're
>>>> written the test cases, a modest upfront investment
>>>>
>>>
>>> "modest"? One function a day and you need more than a year.
>>>
>>> , it will benefit
>>>
>>>> us with every release we do.  Heck, it would benefit LibreOffice,
>>>> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
>>>> they might already have such test cases defined internally.
>>>>
>>>
>>> I see a problem in how such a test suite is made available. And how the
>>> results for a special release are collected.
>>>
>>> The problem with the current test cases is, that I do not know where
>>> they are, how they are to use and how to generate new ones. It is a
>>> closed book, only for insiders.
>>>
>>
>> An example of a test case where a formula ("addition") is checked is in
>> [1]. This file is clean and easy enough that it could be used as a
>> template for more formula checks.
>>
>> [1]
>> http://svn.apache.org/repos/**asf/openoffice/trunk/test/**
>> testuno/source/fvt/uno/sc/**formula/**AddtionOperatorInFormula.java<http://svn.apache.org/repos/asf/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/AddtionOperatorInFormula.java>
>>
>>
>> The general topic on getting started with test automation is covered in
>> [2].
>>
>> [2] 
>> http://wiki.openoffice.org/**wiki/QA/test_automation_guide<http://wiki.openoffice.org/wiki/QA/test_automation_guide>
>>
>>  [..]
>
>
>> There is no need to develop a new framework. Please check Zhe Liu's
>> wonderful work on test automation that I referenced above [2] that is
>> already available in our "test/" directory. It is very powerful, clean
>> and relatively easy to use.
>>
>
> That is was I meant with "closed". This cannot be done by everyone.
>
> To get a wider testing it must be easy, so easy as "download the file,
> open it and see, whether something is red".
>
> It seems "automatic test" and "manual test" have to be separated.
>
>
> But for both kind of testing there exists a problem with "expected
> values". For example, calculation of PMT needs expm1 and log1p, or
> calculation of LINEST needs lot of matrix calculation. It is not impossible
> to calculate this with Java, but who will do it? And when you use constant
> expected values, from where do you get them and how to ensure, that they
> are valid?
>

Just one simple question, how do you do manually today ? use the same
constants.

We should not try to invent a total new road, just do what we do today more
efficiently.

rgds
jan I

>
> Kind regards
> Regina
>
>
> --**--**-
> To unsubscribe, e-mail: 
> dev-unsubscribe@openoffice.**apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>
>

Re: Example of spreadsheet formula testing

[Regina Henschel]

Hi Herbert,

Herbert Duerr schrieb:

On 16.08.2013 21:37, Regina Henschel wrote:

Rob Weir schrieb:

Moving this topic to its own thread.

It should be possible to code a very thorough set of test cases in a
spreadsheet, without using macros or anything fancy.  Just careful
reading of the ODF 1.2 specification and simple spreadsheet logic.



Following the spec is not enough. For example, if the accuracy decreases
from 14 digits to 7 digits, that is not covered by the spec.




If we used an approach like this on the other spreadsheet functions,
we could have a semi-automated test suite that would practically
guarantee that Calc is free of calculations errors.  Once we're
written the test cases, a modest upfront investment


"modest"? One function a day and you need more than a year.

, it will benefit

us with every release we do.  Heck, it would benefit LibreOffice,
Gnumeric, Calligra as well, maybe even Microsoft and Google, though
they might already have such test cases defined internally.


I see a problem in how such a test suite is made available. And how the
results for a special release are collected.

The problem with the current test cases is, that I do not know where
they are, how they are to use and how to generate new ones. It is a
closed book, only for insiders.


An example of a test case where a formula ("addition") is checked is in
[1]. This file is clean and easy enough that it could be used as a
template for more formula checks.

[1]
http://svn.apache.org/repos/asf/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/AddtionOperatorInFormula.java


The general topic on getting started with test automation is covered in
[2].

[2] http://wiki.openoffice.org/wiki/QA/test_automation_guide


[..]


There is no need to develop a new framework. Please check Zhe Liu's
wonderful work on test automation that I referenced above [2] that is
already available in our "test/" directory. It is very powerful, clean
and relatively easy to use.


That is was I meant with "closed". This cannot be done by everyone.

To get a wider testing it must be easy, so easy as "download the file, 
open it and see, whether something is red".


It seems "automatic test" and "manual test" have to be separated.


But for both kind of testing there exists a problem with "expected 
values". For example, calculation of PMT needs expm1 and log1p, or 
calculation of LINEST needs lot of matrix calculation. It is not 
impossible to calculate this with Java, but who will do it? And when you 
use constant expected values, from where do you get them and how to 
ensure, that they are valid?


Kind regards
Regina

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 19.08.2013 11:18, janI wrote:

On 19 August 2013 10:43, Herbert Duerr  wrote:

[...]
There is no need to develop a new framework. Please check Zhe Liu's
wonderful work on test automation that I referenced above [2] that is
already available in our "test/" directory. It is very powerful, clean and
relatively easy to use.


not only does it look very powerful, the documentation is easy to read and
seems very complete.

Am I correct in assuming, this is not used today for our current testing ?


Its quite easy for everyone who is able to checkout and build AOO to run 
them [1]. The "official" Apache don't run them yet. My local buildbot 
does daily BVT (Build Verification Tests) and FVT (Functional 
Verification Tests) tests.


The full test suite of BVT (Build), FVT (Functional), SVT (System), and 
PVT (Performance) tests takes about 24h on dedicated powerful hardware. 
And there are still too many false positives resulting from timeouts, 
connection problems, wrong slot IDs, etc.



If a couple of the testers started to use that and update/expand with their
tests, it would cost nearly the same time as the test does today, but with
the huge benefit, that we can repeat the test as often as we like.

big +1 from me, to get us running down this path.


Agreed. The framework is a great asset that should be leveraged. If we 
can eliminate the still too frequent false positives then we could send 
automatic reports to the mailing list.


[1] 
http://wiki.openoffice.org/wiki/QA/test_automation_guide#Getting_started_with_command_line


Herbert

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[janI]

On 19 August 2013 10:43, Herbert Duerr  wrote:

> On 16.08.2013 21:37, Regina Henschel wrote:
>
>> Rob Weir schrieb:
>>
>>> Moving this topic to its own thread.
>>>
>>> It should be possible to code a very thorough set of test cases in a
>>> spreadsheet, without using macros or anything fancy.  Just careful
>>> reading of the ODF 1.2 specification and simple spreadsheet logic.
>>>
>>>
>> Following the spec is not enough. For example, if the accuracy decreases
>> from 14 digits to 7 digits, that is not covered by the spec.
>>
>> 
>>
>>  If we used an approach like this on the other spreadsheet functions,
>>> we could have a semi-automated test suite that would practically
>>> guarantee that Calc is free of calculations errors.  Once we're
>>> written the test cases, a modest upfront investment
>>>
>>
>> "modest"? One function a day and you need more than a year.
>>
>> , it will benefit
>>
>>> us with every release we do.  Heck, it would benefit LibreOffice,
>>> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
>>> they might already have such test cases defined internally.
>>>
>>
>> I see a problem in how such a test suite is made available. And how the
>> results for a special release are collected.
>>
>> The problem with the current test cases is, that I do not know where
>> they are, how they are to use and how to generate new ones. It is a
>> closed book, only for insiders.
>>
>
> An example of a test case where a formula ("addition") is checked is in
> [1]. This file is clean and easy enough that it could be used as a template
> for more formula checks.
>
> [1] http://svn.apache.org/repos/**asf/openoffice/trunk/test/**
> testuno/source/fvt/uno/sc/**formula/**AddtionOperatorInFormula.java<http://svn.apache.org/repos/asf/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/AddtionOperatorInFormula.java>
>
> The general topic on getting started with test automation is covered in
> [2].
>
> [2] 
> http://wiki.openoffice.org/**wiki/QA/test_automation_guide<http://wiki.openoffice.org/wiki/QA/test_automation_guide>
>
>  Anyone interesting in helping with this kind of test case development?
>>>
>>
>> There exist some files already in Bugzilla. I used to make test
>> documents, when working on functions. I think, that they can be extended
>> to work in a way, that a simple look on it will tell errors. But I have
>> no ready collection on my PC and most will be already deleted from my PC
>> in the meantime.
>>
>> One problem is, that comparisons with constants have to be written in a
>> way, that they are independent from local. Eike has once corrected one
>> of my test spreadsheets that way.
>>
>>
>>> Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
>>> we're not starting from a  perfect score.  But we should find an easy
>>> way to report on regressions.
>>>
>>
>> If you will automate this, you will need to develop a frame. But
>> automation is not the total solution. Testing can be a way to bring user
>> into the community. And tests have to cover different languages and
>> scripts. I remember errors reported to LibreOffice, where a time
>> calculation was wrong only in special locals. To extend a testing frame
>> to consider this would be very expensive.
>>
>
> There is no need to develop a new framework. Please check Zhe Liu's
> wonderful work on test automation that I referenced above [2] that is
> already available in our "test/" directory. It is very powerful, clean and
> relatively easy to use.
>

not only does it look very powerful, the documentation is easy to read and
seems very complete.

Am I correct in assuming, this is not used today for our current testing ?

If a couple of the testers started to use that and update/expand with their
tests, it would cost nearly the same time as the test does today, but with
the huge benefit, that we can repeat the test as often as we like.

big +1 from me, to get us running down this path.


rgds
jan I.


> Herbert
>
>
> --**--**-
> To unsubscribe, e-mail: 
> dev-unsubscribe@openoffice.**apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>
>

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 16.08.2013 21:37, Regina Henschel wrote:

Rob Weir schrieb:

Moving this topic to its own thread.

It should be possible to code a very thorough set of test cases in a
spreadsheet, without using macros or anything fancy.  Just careful
reading of the ODF 1.2 specification and simple spreadsheet logic.



Following the spec is not enough. For example, if the accuracy decreases
from 14 digits to 7 digits, that is not covered by the spec.




If we used an approach like this on the other spreadsheet functions,
we could have a semi-automated test suite that would practically
guarantee that Calc is free of calculations errors.  Once we're
written the test cases, a modest upfront investment


"modest"? One function a day and you need more than a year.

, it will benefit

us with every release we do.  Heck, it would benefit LibreOffice,
Gnumeric, Calligra as well, maybe even Microsoft and Google, though
they might already have such test cases defined internally.


I see a problem in how such a test suite is made available. And how the
results for a special release are collected.

The problem with the current test cases is, that I do not know where
they are, how they are to use and how to generate new ones. It is a
closed book, only for insiders.


An example of a test case where a formula ("addition") is checked is in 
[1]. This file is clean and easy enough that it could be used as a 
template for more formula checks.


[1] 
http://svn.apache.org/repos/asf/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/AddtionOperatorInFormula.java


The general topic on getting started with test automation is covered in [2].

[2] http://wiki.openoffice.org/wiki/QA/test_automation_guide


Anyone interesting in helping with this kind of test case development?


There exist some files already in Bugzilla. I used to make test
documents, when working on functions. I think, that they can be extended
to work in a way, that a simple look on it will tell errors. But I have
no ready collection on my PC and most will be already deleted from my PC
in the meantime.

One problem is, that comparisons with constants have to be written in a
way, that they are independent from local. Eike has once corrected one
of my test spreadsheets that way.



Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
we're not starting from a  perfect score.  But we should find an easy
way to report on regressions.


If you will automate this, you will need to develop a frame. But
automation is not the total solution. Testing can be a way to bring user
into the community. And tests have to cover different languages and
scripts. I remember errors reported to LibreOffice, where a time
calculation was wrong only in special locals. To extend a testing frame
to consider this would be very expensive.


There is no need to develop a new framework. Please check Zhe Liu's 
wonderful work on test automation that I referenced above [2] that is 
already available in our "test/" directory. It is very powerful, clean 
and relatively easy to use.


Herbert


-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Rob Weir]

On Fri, Aug 16, 2013 at 4:47 PM, janI  wrote:
> On 16 August 2013 22:14, Rob Weir  wrote:
>
>> On Fri, Aug 16, 2013 at 3:51 PM, janI  wrote:
>> > On 16 August 2013 21:37, Regina Henschel 
>> wrote:
>> >
>> >> Hi Rob,
>> >>
>> >> Rob Weir schrieb:
>> >>
>> >>> Moving this topic to its own thread.
>> >>>
>> >>> It should be possible to code a very thorough set of test cases in a
>> >>> spreadsheet, without using macros or anything fancy.  Just careful
>> >>> reading of the ODF 1.2 specification and simple spreadsheet logic.
>> >>>
>> >>>
>> >> Following the spec is not enough. For example, if the accuracy decreases
>> >> from 14 digits to 7 digits, that is not covered by the spec.
>> >>
>> >> 
>> >>
>> >>  If we used an approach like this on the other spreadsheet functions,
>> >>> we could have a semi-automated test suite that would practically
>> >>> guarantee that Calc is free of calculations errors.  Once we're
>> >>> written the test cases, a modest upfront investment
>> >>>
>> >>
>> >> "modest"? One function a day and you need more than a year.
>> >>
>> >
>> > I think that a bit over the top, you can do quite a lot with an editor
>> :-)
>> >
>> > And dont forget, if it really takes that long, how long does it then take
>> > to test it manually, no less I assume.
>> >
>> > so its a win-win situation, first person that tests functions write the
>> > first macros and so on.
>> >
>> >
>> >>
>> >> , it will benefit
>> >>
>> >>> us with every release we do.  Heck, it would benefit LibreOffice,
>> >>> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
>> >>> they might already have such test cases defined internally.
>> >>>
>> >>
>> >> I see a problem in how such a test suite is made available. And how the
>> >> results for a special release are collected.
>> >>
>> >> The problem with the current test cases is, that I do not know where
>> they
>> >> are, how they are to use and how to generate new ones. It is a closed
>> book,
>> >> only for insiders.
>> >>
>> >
>> > Thats a matter of documentation, I did not know where build.pl was
>> stored
>> > or how the build system worked before I invested time. Everything is a
>> > closed book and for insider until you know it.
>> >
>> >
>> >>
>> >>
>> >>> Anyone interesting in helping with this kind of test case development?
>> >>>
>> >>
>> >> There exist some files already in Bugzilla. I used to make test
>> documents,
>> >> when working on functions. I think, that they can be extended to work
>> in a
>> >> way, that a simple look on it will tell errors. But I have no ready
>> >> collection on my PC and most will be already deleted from my PC in the
>> >> meantime.
>> >>
>> >
>> > ODF must also as such have test suites to test the specification.
>> >
>>
>> There are test cases, but they are incomplete.  And I think everything
>> must be hand-verified.
>>
>> A short story of what can go wrong.  When the OOXML standard was
>> written the authors included an example calculation for each function,
>> to show what the expected result was.  They also gave a text
>> description and in many cases an equation in mathematical notation.
>>
>> This all looked great.  But when I looked closely I found that the
>> test cases were just copy & paste from Excel output.  And in some
>> cases the result given was not what the mathematical equation said.
>> They were not in synch.  So it was a case of "never go to sea with two
>> compasses", because you then are lost if they disagree.
>>
>> So if we do this we really need to generate the expected values from
>> the specification itself, by hand, or using some other trusted tool,
>> like an HP financial calculator.  If you look at the details of my
>> YEARFRAC test sheet you see that is what I did.As teaches tell
>> their students, "show your work", not just the final result.
>>
>
> I see your point, and I am probably to full blooded a programmer, used to
> specs like t

Re: Example of spreadsheet formula testing

[janI]

On 16 August 2013 22:14, Rob Weir  wrote:

> On Fri, Aug 16, 2013 at 3:51 PM, janI  wrote:
> > On 16 August 2013 21:37, Regina Henschel 
> wrote:
> >
> >> Hi Rob,
> >>
> >> Rob Weir schrieb:
> >>
> >>> Moving this topic to its own thread.
> >>>
> >>> It should be possible to code a very thorough set of test cases in a
> >>> spreadsheet, without using macros or anything fancy.  Just careful
> >>> reading of the ODF 1.2 specification and simple spreadsheet logic.
> >>>
> >>>
> >> Following the spec is not enough. For example, if the accuracy decreases
> >> from 14 digits to 7 digits, that is not covered by the spec.
> >>
> >> 
> >>
> >>  If we used an approach like this on the other spreadsheet functions,
> >>> we could have a semi-automated test suite that would practically
> >>> guarantee that Calc is free of calculations errors.  Once we're
> >>> written the test cases, a modest upfront investment
> >>>
> >>
> >> "modest"? One function a day and you need more than a year.
> >>
> >
> > I think that a bit over the top, you can do quite a lot with an editor
> :-)
> >
> > And dont forget, if it really takes that long, how long does it then take
> > to test it manually, no less I assume.
> >
> > so its a win-win situation, first person that tests functions write the
> > first macros and so on.
> >
> >
> >>
> >> , it will benefit
> >>
> >>> us with every release we do.  Heck, it would benefit LibreOffice,
> >>> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
> >>> they might already have such test cases defined internally.
> >>>
> >>
> >> I see a problem in how such a test suite is made available. And how the
> >> results for a special release are collected.
> >>
> >> The problem with the current test cases is, that I do not know where
> they
> >> are, how they are to use and how to generate new ones. It is a closed
> book,
> >> only for insiders.
> >>
> >
> > Thats a matter of documentation, I did not know where build.pl was
> stored
> > or how the build system worked before I invested time. Everything is a
> > closed book and for insider until you know it.
> >
> >
> >>
> >>
> >>> Anyone interesting in helping with this kind of test case development?
> >>>
> >>
> >> There exist some files already in Bugzilla. I used to make test
> documents,
> >> when working on functions. I think, that they can be extended to work
> in a
> >> way, that a simple look on it will tell errors. But I have no ready
> >> collection on my PC and most will be already deleted from my PC in the
> >> meantime.
> >>
> >
> > ODF must also as such have test suites to test the specification.
> >
>
> There are test cases, but they are incomplete.  And I think everything
> must be hand-verified.
>
> A short story of what can go wrong.  When the OOXML standard was
> written the authors included an example calculation for each function,
> to show what the expected result was.  They also gave a text
> description and in many cases an equation in mathematical notation.
>
> This all looked great.  But when I looked closely I found that the
> test cases were just copy & paste from Excel output.  And in some
> cases the result given was not what the mathematical equation said.
> They were not in synch.  So it was a case of "never go to sea with two
> compasses", because you then are lost if they disagree.
>
> So if we do this we really need to generate the expected values from
> the specification itself, by hand, or using some other trusted tool,
> like an HP financial calculator.  If you look at the details of my
> YEARFRAC test sheet you see that is what I did.As teaches tell
> their students, "show your work", not just the final result.
>

I see your point, and I am probably to full blooded a programmer, used to
specs like those from W3N, nice BN formulas and at the same time a test
suite thats real big. It used to be a selling point to say that the html
part of our software had passed the W3C test suites.

But I agree, as you write it, we need to be careful, or to make things
simple (to get us started), assume our current calc is ok, and just to be
sure compare with e.g. excel.


> In any case, I like the idea of a focus here, for two reasons:
>
> 1) It lends itself

Re: Example of spreadsheet formula testing

[Rob Weir]

On Fri, Aug 16, 2013 at 3:51 PM, janI  wrote:
> On 16 August 2013 21:37, Regina Henschel  wrote:
>
>> Hi Rob,
>>
>> Rob Weir schrieb:
>>
>>> Moving this topic to its own thread.
>>>
>>> It should be possible to code a very thorough set of test cases in a
>>> spreadsheet, without using macros or anything fancy.  Just careful
>>> reading of the ODF 1.2 specification and simple spreadsheet logic.
>>>
>>>
>> Following the spec is not enough. For example, if the accuracy decreases
>> from 14 digits to 7 digits, that is not covered by the spec.
>>
>> 
>>
>>  If we used an approach like this on the other spreadsheet functions,
>>> we could have a semi-automated test suite that would practically
>>> guarantee that Calc is free of calculations errors.  Once we're
>>> written the test cases, a modest upfront investment
>>>
>>
>> "modest"? One function a day and you need more than a year.
>>
>
> I think that a bit over the top, you can do quite a lot with an editor :-)
>
> And dont forget, if it really takes that long, how long does it then take
> to test it manually, no less I assume.
>
> so its a win-win situation, first person that tests functions write the
> first macros and so on.
>
>
>>
>> , it will benefit
>>
>>> us with every release we do.  Heck, it would benefit LibreOffice,
>>> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
>>> they might already have such test cases defined internally.
>>>
>>
>> I see a problem in how such a test suite is made available. And how the
>> results for a special release are collected.
>>
>> The problem with the current test cases is, that I do not know where they
>> are, how they are to use and how to generate new ones. It is a closed book,
>> only for insiders.
>>
>
> Thats a matter of documentation, I did not know where build.pl was stored
> or how the build system worked before I invested time. Everything is a
> closed book and for insider until you know it.
>
>
>>
>>
>>> Anyone interesting in helping with this kind of test case development?
>>>
>>
>> There exist some files already in Bugzilla. I used to make test documents,
>> when working on functions. I think, that they can be extended to work in a
>> way, that a simple look on it will tell errors. But I have no ready
>> collection on my PC and most will be already deleted from my PC in the
>> meantime.
>>
>
> ODF must also as such have test suites to test the specification.
>

There are test cases, but they are incomplete.  And I think everything
must be hand-verified.

A short story of what can go wrong.  When the OOXML standard was
written the authors included an example calculation for each function,
to show what the expected result was.  They also gave a text
description and in many cases an equation in mathematical notation.

This all looked great.  But when I looked closely I found that the
test cases were just copy & paste from Excel output.  And in some
cases the result given was not what the mathematical equation said.
They were not in synch.  So it was a case of "never go to sea with two
compasses", because you then are lost if they disagree.

So if we do this we really need to generate the expected values from
the specification itself, by hand, or using some other trusted tool,
like an HP financial calculator.  If you look at the details of my
YEARFRAC test sheet you see that is what I did.As teaches tell
their students, "show your work", not just the final result.

In any case, I like the idea of a focus here, for two reasons:

1) It lends itself well to automation

2) From a user perspective a spreadsheet can do almost anything else
wrong, but it must not do calculations wrong.  This is the core trust
of a spreadsheet application.  So it makes sense to really nail this
area.


>
>>
>> One problem is, that comparisons with constants have to be written in a
>> way, that they are independent from local. Eike has once corrected one of
>> my test spreadsheets that way.
>>
>
> We can simply assume for a start (that would be huge) that automated
> testing runs in the en-US environment, then if there are problems it is
> very likely in the translation.
>
>
>>
>>
>>> Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
>>> we're not starting from a  perfect score.  But we should find an easy
>>> way to report on regressions.
>>>
>>
>> If you will automate this, you will need to develop a frame. But
>> automa

Re: Example of spreadsheet formula testing

[Rob Weir]

On Fri, Aug 16, 2013 at 3:37 PM, Regina Henschel
 wrote:
> Hi Rob,
>
> Rob Weir schrieb:
>
>> Moving this topic to its own thread.
>>
>> It should be possible to code a very thorough set of test cases in a
>> spreadsheet, without using macros or anything fancy.  Just careful
>> reading of the ODF 1.2 specification and simple spreadsheet logic.
>>
>
> Following the spec is not enough. For example, if the accuracy decreases
> from 14 digits to 7 digits, that is not covered by the spec.
>
> 
>

This is not hard to check.  When you save an ODF document in version
N, it saves the last-calculated value of each cell as well.  It would
be possible to verify that the same values are returned in AOO version
N as well.  Probably not via AOO itself, but you can access the
last-saved value in the cell via the ODF Toolkit, for example.   Load
sheet, save, compare to sheet saved in previous version (or some other
reference version).

>
>> If we used an approach like this on the other spreadsheet functions,
>> we could have a semi-automated test suite that would practically
>> guarantee that Calc is free of calculations errors.  Once we're
>> written the test cases, a modest upfront investment
>
>
> "modest"? One function a day and you need more than a year.
>

There are ways of making this more efficient.  You group together
functions that are related and have the same basic input values.  For
example, the many bin/oct/hex conversion functions, the logical
IF/AND/XOR functions, the DCOUNT/DMAX/DMIN database functions, etc.
Try to reuse the same test conditions for related functions.

So there is a lot of reuse possible.  But the parts are also very
independent, so the work can be done in parallel by any interested
volunteers.

>
> , it will benefit
>>
>> us with every release we do.  Heck, it would benefit LibreOffice,
>> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
>> they might already have such test cases defined internally.
>
>
> I see a problem in how such a test suite is made available. And how the
> results for a special release are collected.
>

I was thinking of storing them in SVN, something like
/test-docs/calc/functions or something like that.  I think we want to
make it easy for a tester to download the whole collection of test
sheets without needing to download them individually from a wiki page,
for example.

> The problem with the current test cases is, that I do not know where they
> are, how they are to use and how to generate new ones. It is a closed book,
> only for insiders.
>

Do we have test documents like this today?   I don't want to do redundant work.

>
>>
>> Anyone interesting in helping with this kind of test case development?
>
>
> There exist some files already in Bugzilla. I used to make test documents,
> when working on functions. I think, that they can be extended to work in a
> way, that a simple look on it will tell errors. But I have no ready
> collection on my PC and most will be already deleted from my PC in the
> meantime.
>
> One problem is, that comparisons with constants have to be written in a way,
> that they are independent from local. Eike has once corrected one of my test
> spreadsheets that way.
>

Or we agree on a locale to be used for calculation testing.  There are
only a small number of locale-dependent calculations in Calc.  I have
another test document that triggers all the implementation-dependent,
implementation-defined, locale-defined and undefined behaviors in
OpenFormula.  For those there is no single "correct" answer.  But we
can use it to verify that the calculations do not change unexpectedly
from release to release.

Or put differently, it might make sense to isolate the
locale-sensitive calculations from the locale-insensitive ones and
approach the testing of them differently.

>
>>
>> Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
>> we're not starting from a  perfect score.  But we should find an easy
>> way to report on regressions.
>
>
> If you will automate this, you will need to develop a frame. But automation
> is not the total solution. Testing can be a way to bring user into the
> community. And tests have to cover different languages and scripts. I
> remember errors reported to LibreOffice, where a time calculation was wrong
> only in special locals. To extend a testing frame to consider this would be
> very expensive.
>
> Let me not be misunderstood, I like the idea of collecting test cases.
>

I'm thinking less of "collecting" test cases and more of designing a
test suite with specific coverage goals in mind.  The coverage may not
be 100% if you consider the locale aspect of it.  Bu

Re: Example of spreadsheet formula testing

[janI]

On 16 August 2013 21:37, Regina Henschel  wrote:

> Hi Rob,
>
> Rob Weir schrieb:
>
>> Moving this topic to its own thread.
>>
>> It should be possible to code a very thorough set of test cases in a
>> spreadsheet, without using macros or anything fancy.  Just careful
>> reading of the ODF 1.2 specification and simple spreadsheet logic.
>>
>>
> Following the spec is not enough. For example, if the accuracy decreases
> from 14 digits to 7 digits, that is not covered by the spec.
>
> 
>
>  If we used an approach like this on the other spreadsheet functions,
>> we could have a semi-automated test suite that would practically
>> guarantee that Calc is free of calculations errors.  Once we're
>> written the test cases, a modest upfront investment
>>
>
> "modest"? One function a day and you need more than a year.
>

I think that a bit over the top, you can do quite a lot with an editor :-)

And dont forget, if it really takes that long, how long does it then take
to test it manually, no less I assume.

so its a win-win situation, first person that tests functions write the
first macros and so on.


>
> , it will benefit
>
>> us with every release we do.  Heck, it would benefit LibreOffice,
>> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
>> they might already have such test cases defined internally.
>>
>
> I see a problem in how such a test suite is made available. And how the
> results for a special release are collected.
>
> The problem with the current test cases is, that I do not know where they
> are, how they are to use and how to generate new ones. It is a closed book,
> only for insiders.
>

Thats a matter of documentation, I did not know where build.pl was stored
or how the build system worked before I invested time. Everything is a
closed book and for insider until you know it.


>
>
>> Anyone interesting in helping with this kind of test case development?
>>
>
> There exist some files already in Bugzilla. I used to make test documents,
> when working on functions. I think, that they can be extended to work in a
> way, that a simple look on it will tell errors. But I have no ready
> collection on my PC and most will be already deleted from my PC in the
> meantime.
>

ODF must also as such have test suites to test the specification.


>
> One problem is, that comparisons with constants have to be written in a
> way, that they are independent from local. Eike has once corrected one of
> my test spreadsheets that way.
>

We can simply assume for a start (that would be huge) that automated
testing runs in the en-US environment, then if there are problems it is
very likely in the translation.


>
>
>> Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
>> we're not starting from a  perfect score.  But we should find an easy
>> way to report on regressions.
>>
>
> If you will automate this, you will need to develop a frame. But
> automation is not the total solution. Testing can be a way to bring user
> into the community. And tests have to cover different languages and
> scripts. I remember errors reported to LibreOffice, where a time
> calculation was wrong only in special locals. To extend a testing frame to
> consider this would be very expensive.
>
> I simply disagree, I come from a company where every bug was turned into
at least one test case in the framework, that was not all expensive, merely
giving the developer a slightly different job. Using this philosophy we
guarantee that a bug never reoccurs and that we test more and more with
every regression.

Testing AOO is actually quite simple than testing live systems, where the
history changes the behavior of the system. Apart from very few test cases,
we can simply restart every time.

We do need a test framework, but that is much more documentation and then
use what we have. In the first line macros, later perhaps scripting through
the extension framework. But alone with macros we have come a long way.

> Let me not be misunderstood, I like the idea of collecting test cases.
>

I could have misunderstood what you wrote, but I know we all have the same
goal, a high quality.

rgds
jan I


> Kind regard
> Regina
>
>
>
>
> --**--**-
> To unsubscribe, e-mail: 
> dev-unsubscribe@openoffice.**apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>
>

Re: Example of spreadsheet formula testing

[Regina Henschel]

Hi Rob,

Rob Weir schrieb:

Moving this topic to its own thread.

It should be possible to code a very thorough set of test cases in a
spreadsheet, without using macros or anything fancy.  Just careful
reading of the ODF 1.2 specification and simple spreadsheet logic.



Following the spec is not enough. For example, if the accuracy decreases 
from 14 digits to 7 digits, that is not covered by the spec.





If we used an approach like this on the other spreadsheet functions,
we could have a semi-automated test suite that would practically
guarantee that Calc is free of calculations errors.  Once we're
written the test cases, a modest upfront investment


"modest"? One function a day and you need more than a year.

, it will benefit

us with every release we do.  Heck, it would benefit LibreOffice,
Gnumeric, Calligra as well, maybe even Microsoft and Google, though
they might already have such test cases defined internally.


I see a problem in how such a test suite is made available. And how the 
results for a special release are collected.


The problem with the current test cases is, that I do not know where 
they are, how they are to use and how to generate new ones. It is a 
closed book, only for insiders.




Anyone interesting in helping with this kind of test case development?


There exist some files already in Bugzilla. I used to make test 
documents, when working on functions. I think, that they can be extended 
to work in a way, that a simple look on it will tell errors. But I have 
no ready collection on my PC and most will be already deleted from my PC 
in the meantime.


One problem is, that comparisons with constants have to be written in a 
way, that they are independent from local. Eike has once corrected one 
of my test spreadsheets that way.




Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
we're not starting from a  perfect score.  But we should find an easy
way to report on regressions.


If you will automate this, you will need to develop a frame. But 
automation is not the total solution. Testing can be a way to bring user 
into the community. And tests have to cover different languages and 
scripts. I remember errors reported to LibreOffice, where a time 
calculation was wrong only in special locals. To extend a testing frame 
to consider this would be very expensive.


Let me not be misunderstood, I like the idea of collecting test cases.

Kind regard
Regina




-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Example of spreadsheet formula testing

[Rob Weir]

Moving this topic to its own thread.

It should be possible to code a very thorough set of test cases in a
spreadsheet, without using macros or anything fancy.  Just careful
reading of the ODF 1.2 specification and simple spreadsheet logic.

I'd like to share an example of this that I created for one of the ODF
Plugfests.  This is a test of a single function -- YEARFRAC.  You
probably have never touched this function, but it exhibits all the
pathological behavior, in a purer form, of the other financial
functions.  Specifically, it is a pure test of our "date counting
conventions", the various ways that accountants handle date
calculations.

The test document is here:

http://www.robweir.com/basis-test.xls

(I did it in XLS format since I wanted to make sure Microsoft could
use it at the Plugfest as well.  At that time they were not able to
read ODF formulas.)

This is likely the most complicated set of test cases of any
spreadsheet formula.  So if we can test YEARFRAC this way then we can
test any function this way.

Column C is the formula to evaluate.  Column F is the expected value,
which is calculated by hand, according to the ODF standard.  And
colu,mn G reports whether they match or not.  (This would be a good
place for us to use conditional formatting as well, though in the
Plugfest case I needed to make the spreadsheet be as vanilla as
possible so every editor could load it)

Note that this is an exhaustive set of test cases that aim to test
every corner of the formula.  It is a torture test.  Excel gets all
the test cases right.  Not a surprise, since we took Excel's behavior
as normative when writing this part of the standard.

If we used an approach like this on the other spreadsheet functions,
we could have a semi-automated test suite that would practically
guarantee that Calc is free of calculations errors.  Once we're
written the test cases, a modest upfront investment, it will benefit
us with every release we do.  Heck, it would benefit LibreOffice,
Gnumeric, Calligra as well, maybe even Microsoft and Google, though
they might already have such test cases defined internally.

Anyone interesting in helping with this kind of test case development?

Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
we're not starting from a  perfect score.  But we should find an easy
way to report on regressions.

-Rob

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Issue 122927 -- spreadsheet formula compatibility

[Rob Weir]

On Thu, Aug 15, 2013 at 10:35 AM, sebb  wrote:
> On 15 August 2013 15:21, Rob Weir  wrote:
>> On Thu, Aug 15, 2013 at 10:07 AM, sebb  wrote:
>>> On 15 August 2013 14:47, Rob Weir  wrote:
>>>> On Thu, Aug 15, 2013 at 9:41 AM, janI  wrote:
>>>>> On Aug 15, 2013 3:06 PM, "Rob Weir"  wrote:
>>>>>>
>>>>>> https://issues.apache.org/ooo/show_bug.cgi?id=122927
>>>>>>
>>>>>> It boils down to how an IF() statements are evaluated.
>>>>>>
>>>>>> Remember, the typical form is IF(Condition;X;Y) where you give a
>>>>>> return value for the case where Condition is TRUE and another value
>>>>>> when Condition is FALSE.
>>>>>>
>>>>>> But it is also possible to leave out the last parameter and have a
>>>>>> formula like this:
>>>>>>
>>>>>> IF(Condition;X)
>>>>>>
>>>>>> So what does the formula evaluate to if Condition is FALSE?
>>>>>>
>>>>>> The behavior in 4.0.0, returning FALSE, is correct according to the
>>>>>> ODF 1.2 specification and is the same as what Excel does.  However, it
>>>>>> is different than what earlier versions of OpenOffice did, namely
>>>>>> returning 0.0.
>>>>>>
>>>>>> We obviously cannot do both.  I think the AOO 4.0.0 behavior is
>>>>>> correct and should remain.
>>>>>
>>>>> I dont understand why we cannot do both, most programming languages
>>>>> interpret falase==0 and true==1, that allows the use of boolean functions
>>>>> in calculations.
>>>>>
>>>>
>>>> If the user takes the results of the IF() calculation and uses it in
>>>> another formula, then FALSE is automatically treated as 0 in any other
>>>> formula where a number is expected.  You are correct in your
>>>> assumption there.   So no one gets a wrong answer in a calculation
>>>> because of the change.
>>>>
>>>> What is different is what appears in the cell that actually has the
>>>> IF() statement in it.  AOO 4.0 and Excel show FALSE.  Earlier versions
>>>> of AOO showed 0.   In this sense we can have one default behavior or
>>>> the other, but not both.
>>>
>>> Could you not add a setting that controls the behaviour?
>>>
>>> For a fresh install AOO 4.x will show FALSE.
>>> But if the user sets the appropriate backwards compatibilty option, it
>>> will show 0.
>>>
>>
>> In theory yes, but in practice users don't really think about this as
>> a per-installation setting.
>
> Maybe not, but if it was documented it could solve problems for some users.
> And it is a fairly simple solution.
>
>> They want their spreadsheet to look the
>> same as it was when it was created, even if it was created in a
>> different version of OpenOffice,
>
> That I can understand.
>
>> or in a different spreadsheet application altogether.
>
> In which case the default may be something else entirely - e.g.
> omitted trailing values are not allowed.
>
>> So a more targeted fix would be to trigger backwards compatibility
>> mode whenever you read a spreadsheet that was created in older
>> versions of AOO.
>
> In which case, there needs to be some way to change the behaviour in
> case the user wants to update to the new behaviour without recreating
> the spreadsheet.
>
>> Even better is to have a declarative approach where the behaviors are
>> encoded in the document itself as metadata.  This approach has been
>> discussed, but is not yet standardized.
>
> That would require older sheets to be updated, unless the default for
> missing metadata varied between versions.
>
> When context-sensitive solutions work, it's great.
> However when they fail to work, it's usually not at all obvious what
> the problem is - nor the solution.
>

Maybe it is clearer if you look at it from the perspective of a future
AOO 4.2. user who receives a spreadsheet from a AOO 4.1 user who
(hypothetically) has a version that allows the user to set the default
in their application, on how IF() behaves.  The 4.1 document could
have been saved in 3.4 mode, or in 4.0 (Excel compatible) mode.  So
how does the document appear to the 4.2 user?

1) Application per-install user settings are messy.  The 4.2 user has
no idea what the setting was for the 4.1 user.

2) Logic that tries to render the appearance of a sp

Re: Issue 122927 -- spreadsheet formula compatibility

[sebb]

On 15 August 2013 15:21, Rob Weir  wrote:
> On Thu, Aug 15, 2013 at 10:07 AM, sebb  wrote:
>> On 15 August 2013 14:47, Rob Weir  wrote:
>>> On Thu, Aug 15, 2013 at 9:41 AM, janI  wrote:
>>>> On Aug 15, 2013 3:06 PM, "Rob Weir"  wrote:
>>>>>
>>>>> https://issues.apache.org/ooo/show_bug.cgi?id=122927
>>>>>
>>>>> It boils down to how an IF() statements are evaluated.
>>>>>
>>>>> Remember, the typical form is IF(Condition;X;Y) where you give a
>>>>> return value for the case where Condition is TRUE and another value
>>>>> when Condition is FALSE.
>>>>>
>>>>> But it is also possible to leave out the last parameter and have a
>>>>> formula like this:
>>>>>
>>>>> IF(Condition;X)
>>>>>
>>>>> So what does the formula evaluate to if Condition is FALSE?
>>>>>
>>>>> The behavior in 4.0.0, returning FALSE, is correct according to the
>>>>> ODF 1.2 specification and is the same as what Excel does.  However, it
>>>>> is different than what earlier versions of OpenOffice did, namely
>>>>> returning 0.0.
>>>>>
>>>>> We obviously cannot do both.  I think the AOO 4.0.0 behavior is
>>>>> correct and should remain.
>>>>
>>>> I dont understand why we cannot do both, most programming languages
>>>> interpret falase==0 and true==1, that allows the use of boolean functions
>>>> in calculations.
>>>>
>>>
>>> If the user takes the results of the IF() calculation and uses it in
>>> another formula, then FALSE is automatically treated as 0 in any other
>>> formula where a number is expected.  You are correct in your
>>> assumption there.   So no one gets a wrong answer in a calculation
>>> because of the change.
>>>
>>> What is different is what appears in the cell that actually has the
>>> IF() statement in it.  AOO 4.0 and Excel show FALSE.  Earlier versions
>>> of AOO showed 0.   In this sense we can have one default behavior or
>>> the other, but not both.
>>
>> Could you not add a setting that controls the behaviour?
>>
>> For a fresh install AOO 4.x will show FALSE.
>> But if the user sets the appropriate backwards compatibilty option, it
>> will show 0.
>>
>
> In theory yes, but in practice users don't really think about this as
> a per-installation setting.

Maybe not, but if it was documented it could solve problems for some users.
And it is a fairly simple solution.

> They want their spreadsheet to look the
> same as it was when it was created, even if it was created in a
> different version of OpenOffice,

That I can understand.

> or in a different spreadsheet application altogether.

In which case the default may be something else entirely - e.g.
omitted trailing values are not allowed.

> So a more targeted fix would be to trigger backwards compatibility
> mode whenever you read a spreadsheet that was created in older
> versions of AOO.

In which case, there needs to be some way to change the behaviour in
case the user wants to update to the new behaviour without recreating
the spreadsheet.

> Even better is to have a declarative approach where the behaviors are
> encoded in the document itself as metadata.  This approach has been
> discussed, but is not yet standardized.

That would require older sheets to be updated, unless the default for
missing metadata varied between versions.

When context-sensitive solutions work, it's great.
However when they fail to work, it's usually not at all obvious what
the problem is - nor the solution.

> -Rob
>
>>> -Rob
>>>
>>>
>>>> rgds
>>>> jan i
>>>>>
>>>>> I'd like to close the issue as NOTABUG.  But I'd like to get a few
>>>>> more thoughts on this first.
>>>>>
>>>>> Regards,
>>>>>
>>>>> -Rob
>>>>>
>>>>> -
>>>>> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
>>>>> For additional commands, e-mail: dev-h...@openoffice.apache.org
>>>>>
>>>
>>> -
>>> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
>>> For additional commands, e-mail: dev-h...@openoffice.apache.org
>>>
>>
>> -
>> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
>> For additional commands, e-mail: dev-h...@openoffice.apache.org
>>
>
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Issue 122927 -- spreadsheet formula compatibility

[Rob Weir]

On Thu, Aug 15, 2013 at 10:07 AM, sebb  wrote:
> On 15 August 2013 14:47, Rob Weir  wrote:
>> On Thu, Aug 15, 2013 at 9:41 AM, janI  wrote:
>>> On Aug 15, 2013 3:06 PM, "Rob Weir"  wrote:
>>>>
>>>> https://issues.apache.org/ooo/show_bug.cgi?id=122927
>>>>
>>>> It boils down to how an IF() statements are evaluated.
>>>>
>>>> Remember, the typical form is IF(Condition;X;Y) where you give a
>>>> return value for the case where Condition is TRUE and another value
>>>> when Condition is FALSE.
>>>>
>>>> But it is also possible to leave out the last parameter and have a
>>>> formula like this:
>>>>
>>>> IF(Condition;X)
>>>>
>>>> So what does the formula evaluate to if Condition is FALSE?
>>>>
>>>> The behavior in 4.0.0, returning FALSE, is correct according to the
>>>> ODF 1.2 specification and is the same as what Excel does.  However, it
>>>> is different than what earlier versions of OpenOffice did, namely
>>>> returning 0.0.
>>>>
>>>> We obviously cannot do both.  I think the AOO 4.0.0 behavior is
>>>> correct and should remain.
>>>
>>> I dont understand why we cannot do both, most programming languages
>>> interpret falase==0 and true==1, that allows the use of boolean functions
>>> in calculations.
>>>
>>
>> If the user takes the results of the IF() calculation and uses it in
>> another formula, then FALSE is automatically treated as 0 in any other
>> formula where a number is expected.  You are correct in your
>> assumption there.   So no one gets a wrong answer in a calculation
>> because of the change.
>>
>> What is different is what appears in the cell that actually has the
>> IF() statement in it.  AOO 4.0 and Excel show FALSE.  Earlier versions
>> of AOO showed 0.   In this sense we can have one default behavior or
>> the other, but not both.
>
> Could you not add a setting that controls the behaviour?
>
> For a fresh install AOO 4.x will show FALSE.
> But if the user sets the appropriate backwards compatibilty option, it
> will show 0.
>

In theory yes, but in practice users don't really think about this as
a per-installation setting.  They want their spreadsheet to look the
same as it was when it was created, even if it was created in a
different version of OpenOffice, or in a different spreadsheet
application altogether.

So a more targeted fix would be to trigger backwards compatibility
mode whenever you read a spreadsheet that was created in older
versions of AOO.

Even better is to have a declarative approach where the behaviors are
encoded in the document itself as metadata.  This approach has been
discussed, but is not yet standardized.

-Rob

>> -Rob
>>
>>
>>> rgds
>>> jan i
>>>>
>>>> I'd like to close the issue as NOTABUG.  But I'd like to get a few
>>>> more thoughts on this first.
>>>>
>>>> Regards,
>>>>
>>>> -Rob
>>>>
>>>> -
>>>> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
>>>> For additional commands, e-mail: dev-h...@openoffice.apache.org
>>>>
>>
>> -
>> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
>> For additional commands, e-mail: dev-h...@openoffice.apache.org
>>
>
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Issue 122927 -- spreadsheet formula compatibility

[sebb]

On 15 August 2013 14:47, Rob Weir  wrote:
> On Thu, Aug 15, 2013 at 9:41 AM, janI  wrote:
>> On Aug 15, 2013 3:06 PM, "Rob Weir"  wrote:
>>>
>>> https://issues.apache.org/ooo/show_bug.cgi?id=122927
>>>
>>> It boils down to how an IF() statements are evaluated.
>>>
>>> Remember, the typical form is IF(Condition;X;Y) where you give a
>>> return value for the case where Condition is TRUE and another value
>>> when Condition is FALSE.
>>>
>>> But it is also possible to leave out the last parameter and have a
>>> formula like this:
>>>
>>> IF(Condition;X)
>>>
>>> So what does the formula evaluate to if Condition is FALSE?
>>>
>>> The behavior in 4.0.0, returning FALSE, is correct according to the
>>> ODF 1.2 specification and is the same as what Excel does.  However, it
>>> is different than what earlier versions of OpenOffice did, namely
>>> returning 0.0.
>>>
>>> We obviously cannot do both.  I think the AOO 4.0.0 behavior is
>>> correct and should remain.
>>
>> I dont understand why we cannot do both, most programming languages
>> interpret falase==0 and true==1, that allows the use of boolean functions
>> in calculations.
>>
>
> If the user takes the results of the IF() calculation and uses it in
> another formula, then FALSE is automatically treated as 0 in any other
> formula where a number is expected.  You are correct in your
> assumption there.   So no one gets a wrong answer in a calculation
> because of the change.
>
> What is different is what appears in the cell that actually has the
> IF() statement in it.  AOO 4.0 and Excel show FALSE.  Earlier versions
> of AOO showed 0.   In this sense we can have one default behavior or
> the other, but not both.

Could you not add a setting that controls the behaviour?

For a fresh install AOO 4.x will show FALSE.
But if the user sets the appropriate backwards compatibilty option, it
will show 0.

> -Rob
>
>
>> rgds
>> jan i
>>>
>>> I'd like to close the issue as NOTABUG.  But I'd like to get a few
>>> more thoughts on this first.
>>>
>>> Regards,
>>>
>>> -Rob
>>>
>>> -
>>> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
>>> For additional commands, e-mail: dev-h...@openoffice.apache.org
>>>
>
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Issue 122927 -- spreadsheet formula compatibility

[Rob Weir]

On Thu, Aug 15, 2013 at 9:47 AM, Rob Weir  wrote:
> On Thu, Aug 15, 2013 at 9:41 AM, janI  wrote:
>> On Aug 15, 2013 3:06 PM, "Rob Weir"  wrote:
>>>
>>> https://issues.apache.org/ooo/show_bug.cgi?id=122927
>>>
>>> It boils down to how an IF() statements are evaluated.
>>>
>>> Remember, the typical form is IF(Condition;X;Y) where you give a
>>> return value for the case where Condition is TRUE and another value
>>> when Condition is FALSE.
>>>
>>> But it is also possible to leave out the last parameter and have a
>>> formula like this:
>>>
>>> IF(Condition;X)
>>>
>>> So what does the formula evaluate to if Condition is FALSE?
>>>
>>> The behavior in 4.0.0, returning FALSE, is correct according to the
>>> ODF 1.2 specification and is the same as what Excel does.  However, it
>>> is different than what earlier versions of OpenOffice did, namely
>>> returning 0.0.
>>>
>>> We obviously cannot do both.  I think the AOO 4.0.0 behavior is
>>> correct and should remain.
>>
>> I dont understand why we cannot do both, most programming languages
>> interpret falase==0 and true==1, that allows the use of boolean functions
>> in calculations.
>>
>
> If the user takes the results of the IF() calculation and uses it in
> another formula, then FALSE is automatically treated as 0 in any other
> formula where a number is expected.  You are correct in your
> assumption there.   So no one gets a wrong answer in a calculation
> because of the change.
>
> What is different is what appears in the cell that actually has the
> IF() statement in it.  AOO 4.0 and Excel show FALSE.  Earlier versions
> of AOO showed 0.   In this sense we can have one default behavior or
> the other, but not both.
>

I should mention one other subtlety.  Cells have values, types and
formats.  Types are really numeric and string.  Things like currency,
date and boolean are just formats.  You can type in a number like
123456 and format it as a number, a date, a boolean, etc.

The tricky part is that some formulas automatically set a format,
without requiring user intervention.  So if I type =TODAY() into a a
cell I see "08/15/13" instead of "41501".  The spreadsheet is smart
enough to know that with that formula the cell should be formatted as
date.  Pretty cool, yes?

Similar thing occurs with booleans.  Type FALSE() into a cell.  It
automatically is formatted as a boolean, not a number.

So with the IF(Condition;TrueValue) case, the ODF spec says that
FALSE() is returned if Condition is false.  So that is essentially why
the user sees FALSE in the end.  The spreadsheet determines the
appropriate format based on the return value being boolean.

There are ways for the user to override this.  For example, if they
use the full form, IF(Condition;TrueValue;FalseValue) then they can
simply set FalseValue to be equal to 0.  That will ensure that they
get the numeric return value.

Or, they can wrap the IF() to force the outcome to be numeric, e.g.,
=N(IF(Condition;TrueValue)).  The N() function will explicitly convert
the boolean return value into a number.

Regards,

-Rob



> -Rob
>
>
>> rgds
>> jan i
>>>
>>> I'd like to close the issue as NOTABUG.  But I'd like to get a few
>>> more thoughts on this first.
>>>
>>> Regards,
>>>
>>> -Rob
>>>
>>> -
>>> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
>>> For additional commands, e-mail: dev-h...@openoffice.apache.org
>>>

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Issue 122927 -- spreadsheet formula compatibility

[Rob Weir]

On Thu, Aug 15, 2013 at 9:41 AM, janI  wrote:
> On Aug 15, 2013 3:06 PM, "Rob Weir"  wrote:
>>
>> https://issues.apache.org/ooo/show_bug.cgi?id=122927
>>
>> It boils down to how an IF() statements are evaluated.
>>
>> Remember, the typical form is IF(Condition;X;Y) where you give a
>> return value for the case where Condition is TRUE and another value
>> when Condition is FALSE.
>>
>> But it is also possible to leave out the last parameter and have a
>> formula like this:
>>
>> IF(Condition;X)
>>
>> So what does the formula evaluate to if Condition is FALSE?
>>
>> The behavior in 4.0.0, returning FALSE, is correct according to the
>> ODF 1.2 specification and is the same as what Excel does.  However, it
>> is different than what earlier versions of OpenOffice did, namely
>> returning 0.0.
>>
>> We obviously cannot do both.  I think the AOO 4.0.0 behavior is
>> correct and should remain.
>
> I dont understand why we cannot do both, most programming languages
> interpret falase==0 and true==1, that allows the use of boolean functions
> in calculations.
>

If the user takes the results of the IF() calculation and uses it in
another formula, then FALSE is automatically treated as 0 in any other
formula where a number is expected.  You are correct in your
assumption there.   So no one gets a wrong answer in a calculation
because of the change.

What is different is what appears in the cell that actually has the
IF() statement in it.  AOO 4.0 and Excel show FALSE.  Earlier versions
of AOO showed 0.   In this sense we can have one default behavior or
the other, but not both.

-Rob


> rgds
> jan i
>>
>> I'd like to close the issue as NOTABUG.  But I'd like to get a few
>> more thoughts on this first.
>>
>> Regards,
>>
>> -Rob
>>
>> -
>> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
>> For additional commands, e-mail: dev-h...@openoffice.apache.org
>>

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Issue 122927 -- spreadsheet formula compatibility

[janI]

On Aug 15, 2013 3:06 PM, "Rob Weir"  wrote:
>
> https://issues.apache.org/ooo/show_bug.cgi?id=122927
>
> It boils down to how an IF() statements are evaluated.
>
> Remember, the typical form is IF(Condition;X;Y) where you give a
> return value for the case where Condition is TRUE and another value
> when Condition is FALSE.
>
> But it is also possible to leave out the last parameter and have a
> formula like this:
>
> IF(Condition;X)
>
> So what does the formula evaluate to if Condition is FALSE?
>
> The behavior in 4.0.0, returning FALSE, is correct according to the
> ODF 1.2 specification and is the same as what Excel does.  However, it
> is different than what earlier versions of OpenOffice did, namely
> returning 0.0.
>
> We obviously cannot do both.  I think the AOO 4.0.0 behavior is
> correct and should remain.

I dont understand why we cannot do both, most programming languages
interpret falase==0 and true==1, that allows the use of boolean functions
in calculations.

rgds
jan i
>
> I'd like to close the issue as NOTABUG.  But I'd like to get a few
> more thoughts on this first.
>
> Regards,
>
> -Rob
>
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>

Re: Issue 122927 -- spreadsheet formula compatibility

[Jürgen Schmidt]

On 8/15/13 3:06 PM, Rob Weir wrote:
> https://issues.apache.org/ooo/show_bug.cgi?id=122927
> 
> It boils down to how an IF() statements are evaluated.
> 
> Remember, the typical form is IF(Condition;X;Y) where you give a
> return value for the case where Condition is TRUE and another value
> when Condition is FALSE.
> 
> But it is also possible to leave out the last parameter and have a
> formula like this:
> 
> IF(Condition;X)
> 
> So what does the formula evaluate to if Condition is FALSE?
> 
> The behavior in 4.0.0, returning FALSE, is correct according to the
> ODF 1.2 specification and is the same as what Excel does.  However, it
> is different than what earlier versions of OpenOffice did, namely
> returning 0.0.
> 
> We obviously cannot do both.  I think the AOO 4.0.0 behavior is
> correct and should remain.
> 
> I'd like to close the issue as NOTABUG.  But I'd like to get a few
> more thoughts on this first.

I tend to agree because the current behaviour is correct according the
specification and behaves as Excel which increase the interoperability
as well.

We should explain it and why we keep the change, the affected users
won't be happy but hopefully understand that long term the change is
better for all the current and future users ...

Juergen

> 
> Regards,
> 
> -Rob
> 
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
> 


-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Issue 122927 -- spreadsheet formula compatibility

[Rob Weir]

https://issues.apache.org/ooo/show_bug.cgi?id=122927

It boils down to how an IF() statements are evaluated.

Remember, the typical form is IF(Condition;X;Y) where you give a
return value for the case where Condition is TRUE and another value
when Condition is FALSE.

But it is also possible to leave out the last parameter and have a
formula like this:

IF(Condition;X)

So what does the formula evaluate to if Condition is FALSE?

The behavior in 4.0.0, returning FALSE, is correct according to the
ODF 1.2 specification and is the same as what Excel does.  However, it
is different than what earlier versions of OpenOffice did, namely
returning 0.0.

We obviously cannot do both.  I think the AOO 4.0.0 behavior is
correct and should remain.

I'd like to close the issue as NOTABUG.  But I'd like to get a few
more thoughts on this first.

Regards,

-Rob

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

4.0.0_release_blocker granted: [Bug 118986] [RTF export] crashes when copy an empty line from Writer below a tif picture then paste into spreadsheet

[bugzilla]

j...@apache.org has granted liupingtan 's request for
4.0.0_release_blocker:
Bug 118986: [RTF export] crashes when copy an empty line from Writer below a
tif picture then paste into spreadsheet
https://issues.apache.org/ooo/show_bug.cgi?id=118986


--- Additional Comments from j...@apache.org
grant showstopper flag, fix is in place

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

OPEN OFFICE SPREADSHEET

[Henry Zuber]

OPEN OFFICE

I WISH TO OPEN AND USE YOUR SPREADSHEET

HANK ZUBER

4.0.0_release_blocker granted: [Bug 120023] [Regression]Copy and Paste from OO Text doc to Spreadsheet on OO V3.4

[bugzilla]

j...@apache.org has granted liupingtan 's request for
4.0.0_release_blocker:
Bug 120023: [Regression]Copy and Paste from OO Text doc to Spreadsheet on OO
V3.4
https://issues.apache.org/ooo/show_bug.cgi?id=120023


--- Additional Comments from j...@apache.org
grant showstopper flag, already fixed

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

4.0.0_release_blocker denied: [Bug 121062] [From Symphony]It will lead AOO freeze when select all cells in spreadsheet and paste the copied cells

[bugzilla]

j...@apache.org has denied fanyuz...@gmail.com's request for
4.0.0_release_blocker:
Bug 121062: [From Symphony]It will lead AOO freeze when select all cells in
spreadsheet and paste the copied cells
https://issues.apache.org/ooo/show_bug.cgi?id=121062


--- Additional Comments from j...@apache.org
remove showstopper request, duplicate

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Spreadsheet function translations (Excel)

[Kadal Amutham]

Dear Rob Weir,

In how many languages, MS-Office and AOO are available?

With Warm Regards

V.Kadal Amutham
919444360480
914422396480


On 7 May 2013 23:19, Rob Weir  wrote:

> I came across this reference on the web and thought it might be
> useful.  Not sure what, but it has info that I have not seen collected
> in one place before on localized names of Excel spreadsheet functions:
>
> http://wwwhome.ewi.utwente.nl/~trieschn/excel/excel.html
>
> Regards,
>
> -Rob
>
> -
> To unsubscribe, e-mail: l10n-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: l10n-h...@openoffice.apache.org
>
>

Spreadsheet function translations (Excel)

[Rob Weir]

I came across this reference on the web and thought it might be
useful.  Not sure what, but it has info that I have not seen collected
in one place before on localized names of Excel spreadsheet functions:

http://wwwhome.ewi.utwente.nl/~trieschn/excel/excel.html

Regards,

-Rob

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: calc spreadsheet

[Rajath Shashidhara]

Download sdk for linux(deb) in this way:

Downloaded sdk from:
http://www.openoffice.org/download/other.html#tested-sdk
   [1]
Downloaded openoffice.org-ure from:
http://sourceforge.net/projects/apacheoo-deb/files/debian/pool/main/o/   [2]
Downloaded ooobasis3.4.1-core01 from:
http://sourceforge.net/projects/apacheoo-deb/files/debian/pool/main/o/   [3]

Now,
Install
[2] first, then [3] and then [1].

It works!


Install NetBeans IDE for java. Install OpenOFFICE extension. This is
explained on OpenOffice website.



On Fri, Apr 19, 2013 at 7:04 PM, Rajath Shashidhara <
rajaths.raja...@gmail.com> wrote:

> Hello,
>
> Please start with setting up a build environment:
> http://openoffice.apache.org/orientation/intro-development.html
> This page provides you an orientation about OpenOFFICE development.
>
> This page helps you startoff with your development. OpenOffice API's are
> available for Java,Python,C++ and Basic.
>
> http://wiki.openoffice.org/wiki/Documentation/DevGuide/OpenOffice.org_Developers_Guide
> .
>
> Dev guide gives you some examples, gives you an idea about what are the
> functions available and how to use them.
>
> The solution for your problem is :
>
> Get the current context using Bootstrap.
>
> XComponentContext xRemoteContext = Bootstrap.bootstrap();
>
> To access the services which provide you with functions to interact with 
> objects, get the service Manager:
> XMultiComponentFactory xRemoteServiceManager = 
> xRemoteContext.getServiceManager();
>
> Then create a object which links to desktop frame:
>
> Object desktop = xRemoteServiceManager.createInstanceWithContext(
> "com.sun.star.frame.Desktop", xRemoteContext);
>
> Create Componenet loader object using desktop object:
>
> XComponentLoader xComponentLoader = (XComponentLoader)
> UnoRuntime.queryInterface(XComponentLoader.class, desktop);
>
> Now U can use the function loadComponentFromURL to open a new 
> spreadsheet/spreadsheet from hardDisk.
>
> Infact document files can also be loaded in the same way.
>
> Read the dev-guide page step by step for more detailed explanation.
>
> http://wiki.openoffice.org/wiki/Documentation/DevGuide/FirstSteps/Example:_Working_with_a_Spreadsheet_Document
>
> This page gives basic information about spreadsheets.
>
> Check this out for advanced examples:
> http://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Spreadsheet_Documents
>
> Interfaces like XCell , XCellRange will be useful in formatting and accessing 
> data.
>
>
> I'm also a beginner. Someone will confirm my explanations.
>
>
>
> On Fri, Apr 19, 2013 at 5:39 PM, Winman Software 
> wrote:
>
>> while working with calc spreadsheet how should I start with.
>>
>> how can I compile  and run the spreadsheet program.
>>
>> if I wish to work with front end development how can I continue.
>>
>> Please suggest
>>
>>
>>
>>
>
>
> --
> Rajath S,
> M.Sc(Hons.) Physics,
> Birla Institute of Technology and Science - Pilani,
> Pilani
>



-- 
Rajath S,
M.Sc(Hons.) Physics,
Birla Institute of Technology and Science - Pilani,
Pilani

Re: calc spreadsheet

[Rajath Shashidhara]

Hello,

Please start with setting up a build environment:
http://openoffice.apache.org/orientation/intro-development.html
This page provides you an orientation about OpenOFFICE development.

This page helps you startoff with your development. OpenOffice API's are
available for Java,Python,C++ and Basic.
http://wiki.openoffice.org/wiki/Documentation/DevGuide/OpenOffice.org_Developers_Guide
.

Dev guide gives you some examples, gives you an idea about what are the
functions available and how to use them.

The solution for your problem is :

Get the current context using Bootstrap.

XComponentContext xRemoteContext = Bootstrap.bootstrap();

To access the services which provide you with functions to interact
with objects, get the service Manager:
XMultiComponentFactory xRemoteServiceManager =
xRemoteContext.getServiceManager();

Then create a object which links to desktop frame:

Object desktop = xRemoteServiceManager.createInstanceWithContext(
"com.sun.star.frame.Desktop", xRemoteContext);

Create Componenet loader object using desktop object:

XComponentLoader xComponentLoader = (XComponentLoader)
UnoRuntime.queryInterface(XComponentLoader.class, desktop);

Now U can use the function loadComponentFromURL to open a new
spreadsheet/spreadsheet from hardDisk.

Infact document files can also be loaded in the same way.

Read the dev-guide page step by step for more detailed explanation.

http://wiki.openoffice.org/wiki/Documentation/DevGuide/FirstSteps/Example:_Working_with_a_Spreadsheet_Document

This page gives basic information about spreadsheets.

Check this out for advanced examples:
http://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Spreadsheet_Documents

Interfaces like XCell , XCellRange will be useful in formatting and
accessing data.


I'm also a beginner. Someone will confirm my explanations.



On Fri, Apr 19, 2013 at 5:39 PM, Winman Software wrote:

> while working with calc spreadsheet how should I start with.
>
> how can I compile  and run the spreadsheet program.
>
> if I wish to work with front end development how can I continue.
>
> Please suggest
>
>
>
>


-- 
Rajath S,
M.Sc(Hons.) Physics,
Birla Institute of Technology and Science - Pilani,
Pilani

Re: calc spreadsheet

[Joost Andrae]

Hi,

before starting to develop things for Apache OpenOffice you might 
consider to learn about it's API. There's an SDK available and there's 
tons of documentation. Probably you want to start to develop an 
extension first before you dive into the application code.


A good starting point is here:

http://wiki.openoffice.org/wiki/Development
http://wiki.openoffice.org/wiki/Documentation/DevGuide
http://wiki.openoffice.org/wiki/Documentation/Building_Guide_AOO
http://openoffice.apache.org/downloads.html (eg. the SDK)
http://openoffice.apache.org/developer-faqs.html


Am 19.04.2013 14:09, schrieb Winman Software:

while working with calc spreadsheet how should I start with.

how can I compile  and run the spreadsheet program.

if I wish to work with front end development how can I continue.



Kind regards, Joost



-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Open Office spreadsheet

[Fred Ollinger]

I don't have time now, but David has given me some great ideas on how
he uses macros. Anyone interested should ask for details.

It's nice to see an intelligent end user making detailed suggestions.

Again, I won't work on this now due to it's size, but I would like
oocalc to have a simpler macro language modeled on older ones which
makes macros easy for the savvy, but non-programmer user.

Sincerely,

Fred

On Fri, Feb 8, 2013 at 12:29 PM, Rob Weir  wrote:
> On Thu, Feb 7, 2013 at 1:40 PM, David A Yablonsky Sr
>  wrote:
>> I am, if not a spreadsheet power user, at least a spreadsheet developer,
>> since 1982. Having experienced Visicalc, Multiplan, Lotus and Lotus clones,
>> Quattro and Excel and others, I have come to the sad conclusion that
>> spreadsheet applications have become overdeveloped due to the influence of
>> Microsoft's programming philosophies.
>>
>> Specifically, Microsoft has demanded that Basic must be used to develop
>> macros in spreadsheets. For this reason, I use Quattro, which is apparently
>> the only spreadsheet to still use Lotus-style spreadsheet macros.
>>
>
> I remember reading in "Computer Languages" magazine (now defunct)
> around 1992 or so, the results of a survey of the most-common
> programming languages use in businesses.  It was not COBOL, C or
> BASIC.  The winner was the "1-2-3 macro language".  That language
> enabled end-user programming (or scripting, or whatever you want to
> call it).  You are right that today's apps concentrate more on
> capabilities for the professional programmer.
>
> I don't think this is because we're chasing after Excel.  Personally,
> I think it is because these interfaces tend to be developed by
> professional programmers.  So they tend to meet the needs of
> professional programmers.   Of course if we had always thought like
> that we wouldn't even have spreadsheets, since the spreadsheet itself,
> even without macros, is the tool that brought power to the masses, in
> the form of spreadsheet formulas.
>
> In any case, thanks for the reminder that end-user/power-user
> capabilities short of programming are important.
>
> Regards,
>
> -Rob
>
>
>> The linear style and modular storage of Basic-based macros limit the ability
>> of those users who are NOT programmers to develop useful complex macro
>> routines not only because of the requirement to become a Basic programmer,
>> but because there are actions available in Lotus-style macros that  make
>> Basic programming of the same actions a complicated nightmare.
>>
>> In addition, I deplore the loss of database construction and query in
>> contemporary spreadsheet applications. This is another reason I use Quattro.
>> I can create a relatively small database and then query it without knowing
>> SQL or using another application to query the database. What ever happened
>> to "keep it simple"? If one wants to only manage a database, Access or other
>> database applications are great, but if one wants to integrate a small
>> database with spreadsheet functions, you're out of luck.
>>
>> Spreadsheet applications all seem to want to compete with Excel, on the
>> assumption that they can garner a share of the market that Microsoft has
>> pretty much monopolized. As for me, this is wrong-headed, because if you
>> want to grab some market-share, your product should offer something
>> different than Excel; mainly, simplicity. Don't get me wrong, I use Excel
>> for many business applications. It's just that I find that for some of the
>> processes I perform on a daily basis, Excel simply can't perform due its
>> requirement that macros must be developed in Basic and the lack of ability
>> to query a limited internal database. I have downloaded Open Office and find
>> it to be on the path to Excel's blind alley. Too bad. What are needed are
>> some new views of what is really useful to the user, instead of chasing
>> Microsoft's arrogant "my way or the highway" philosophies.
>>
>>
>>
>> D. A. Yablonsky Sr.
>>
>>  <mailto:truera...@sbcglobal.net> truetax2...@gmail.com
>>
>> Phone: 951-279-7026
>>
>> Cell: 951-520-5187
>>
>>
>>
>>
>>

Re: Open Office spreadsheet

[Rob Weir]

On Thu, Feb 7, 2013 at 1:40 PM, David A Yablonsky Sr
 wrote:
> I am, if not a spreadsheet power user, at least a spreadsheet developer,
> since 1982. Having experienced Visicalc, Multiplan, Lotus and Lotus clones,
> Quattro and Excel and others, I have come to the sad conclusion that
> spreadsheet applications have become overdeveloped due to the influence of
> Microsoft's programming philosophies.
>
> Specifically, Microsoft has demanded that Basic must be used to develop
> macros in spreadsheets. For this reason, I use Quattro, which is apparently
> the only spreadsheet to still use Lotus-style spreadsheet macros.
>

I remember reading in "Computer Languages" magazine (now defunct)
around 1992 or so, the results of a survey of the most-common
programming languages use in businesses.  It was not COBOL, C or
BASIC.  The winner was the "1-2-3 macro language".  That language
enabled end-user programming (or scripting, or whatever you want to
call it).  You are right that today's apps concentrate more on
capabilities for the professional programmer.

I don't think this is because we're chasing after Excel.  Personally,
I think it is because these interfaces tend to be developed by
professional programmers.  So they tend to meet the needs of
professional programmers.   Of course if we had always thought like
that we wouldn't even have spreadsheets, since the spreadsheet itself,
even without macros, is the tool that brought power to the masses, in
the form of spreadsheet formulas.

In any case, thanks for the reminder that end-user/power-user
capabilities short of programming are important.

Regards,

-Rob


> The linear style and modular storage of Basic-based macros limit the ability
> of those users who are NOT programmers to develop useful complex macro
> routines not only because of the requirement to become a Basic programmer,
> but because there are actions available in Lotus-style macros that  make
> Basic programming of the same actions a complicated nightmare.
>
> In addition, I deplore the loss of database construction and query in
> contemporary spreadsheet applications. This is another reason I use Quattro.
> I can create a relatively small database and then query it without knowing
> SQL or using another application to query the database. What ever happened
> to "keep it simple"? If one wants to only manage a database, Access or other
> database applications are great, but if one wants to integrate a small
> database with spreadsheet functions, you're out of luck.
>
> Spreadsheet applications all seem to want to compete with Excel, on the
> assumption that they can garner a share of the market that Microsoft has
> pretty much monopolized. As for me, this is wrong-headed, because if you
> want to grab some market-share, your product should offer something
> different than Excel; mainly, simplicity. Don't get me wrong, I use Excel
> for many business applications. It's just that I find that for some of the
> processes I perform on a daily basis, Excel simply can't perform due its
> requirement that macros must be developed in Basic and the lack of ability
> to query a limited internal database. I have downloaded Open Office and find
> it to be on the path to Excel's blind alley. Too bad. What are needed are
> some new views of what is really useful to the user, instead of chasing
> Microsoft's arrogant "my way or the highway" philosophies.
>
>
>
> D. A. Yablonsky Sr.
>
>  <mailto:truera...@sbcglobal.net> truetax2...@gmail.com
>
> Phone: 951-279-7026
>
> Cell: 951-520-5187
>
>
>
>
>

Open Office spreadsheet

[David A Yablonsky Sr]

I am, if not a spreadsheet power user, at least a spreadsheet developer,
since 1982. Having experienced Visicalc, Multiplan, Lotus and Lotus clones,
Quattro and Excel and others, I have come to the sad conclusion that
spreadsheet applications have become overdeveloped due to the influence of
Microsoft's programming philosophies.

Specifically, Microsoft has demanded that Basic must be used to develop
macros in spreadsheets. For this reason, I use Quattro, which is apparently
the only spreadsheet to still use Lotus-style spreadsheet macros.

The linear style and modular storage of Basic-based macros limit the ability
of those users who are NOT programmers to develop useful complex macro
routines not only because of the requirement to become a Basic programmer,
but because there are actions available in Lotus-style macros that  make
Basic programming of the same actions a complicated nightmare.

In addition, I deplore the loss of database construction and query in
contemporary spreadsheet applications. This is another reason I use Quattro.
I can create a relatively small database and then query it without knowing
SQL or using another application to query the database. What ever happened
to "keep it simple"? If one wants to only manage a database, Access or other
database applications are great, but if one wants to integrate a small
database with spreadsheet functions, you're out of luck.

Spreadsheet applications all seem to want to compete with Excel, on the
assumption that they can garner a share of the market that Microsoft has
pretty much monopolized. As for me, this is wrong-headed, because if you
want to grab some market-share, your product should offer something
different than Excel; mainly, simplicity. Don't get me wrong, I use Excel
for many business applications. It's just that I find that for some of the
processes I perform on a daily basis, Excel simply can't perform due its
requirement that macros must be developed in Basic and the lack of ability
to query a limited internal database. I have downloaded Open Office and find
it to be on the path to Excel's blind alley. Too bad. What are needed are
some new views of what is really useful to the user, instead of chasing
Microsoft's arrogant "my way or the highway" philosophies.

 

D. A. Yablonsky Sr.

 <mailto:truera...@sbcglobal.net> truetax2...@gmail.com

Phone: 951-279-7026

Cell: 951-520-5187

 

 

Re: calc spreadsheet data validate window: 'Value' field not checked

[Shan Zhu]

Hi, Lucas
Yes, you can mark it as failed, and attach the defect number to the test
case.

Thanks.

Regards,
Shan Zhu




On Tue, Dec 11, 2012 at 7:39 AM, Lucas Burson  wrote:

> Submitted as enhancement to BZ ([1]). I'll ignore the test case on
> TestLink.
>
> Lucas
>
> [1] https://issues.apache.org/ooo/show_bug.cgi?id=121459
>
>
> On Mon, Dec 10, 2012 at 11:20 AM, Lucas Burson 
> wrote:
>
> > Can we remove the test from TestLink or should I just mark it as failed?
> > Since it isn't implemented it doesn't make much sense to have it as a
> > regression test.
> > On Dec 9, 2012 11:50 PM, "Zhang Lu"  wrote:
> >
> >> Hi Zhu shen,
> >>Sure, it can be implemented as a feature due to workload, thanks!
> >>
> >> On Mon, Dec 10, 2012 at 1:33 PM, Shan Zhu  wrote:
> >>
> >> > Hi, Lu
> >> > It's okay. IMHO, it can be implemented as a feature enhancement. Then
> >> > prepare a formal testing to verify this implementation. It will be
> >> better
> >> > than checking it as defect verification.
> >> >
> >> > Regards,
> >> > Shan Zhu
> >> >
> >> >
> >> >
> >> >
> >> > On Mon, Dec 10, 2012 at 1:23 PM, Zhang Lu 
> >> wrote:
> >> >
> >> > > Hi, Lucas and Shan
> >> > >
> >> > >There is no code for checking the validation of input value as
> Zhu
> >> > Shan
> >> > > said, I have got through this part code, not only whole numbers, but
> >> also
> >> > > all other categories existed this issue. So I think it can be
> handled
> >> as
> >> > a
> >> > > defect. If user input an illegal value,  there is meaningless for
> data
> >> > > validation.
> >> > >
> >> > > Regards,
> >> > > Lu Zhang
> >> > >
> >> >
> >>
> >
>

Re: calc spreadsheet data validate window: 'Value' field not checked

[Lucas Burson]

Submitted as enhancement to BZ ([1]). I'll ignore the test case on
TestLink.

Lucas

[1] https://issues.apache.org/ooo/show_bug.cgi?id=121459


On Mon, Dec 10, 2012 at 11:20 AM, Lucas Burson  wrote:

> Can we remove the test from TestLink or should I just mark it as failed?
> Since it isn't implemented it doesn't make much sense to have it as a
> regression test.
> On Dec 9, 2012 11:50 PM, "Zhang Lu"  wrote:
>
>> Hi Zhu shen,
>>Sure, it can be implemented as a feature due to workload, thanks!
>>
>> On Mon, Dec 10, 2012 at 1:33 PM, Shan Zhu  wrote:
>>
>> > Hi, Lu
>> > It's okay. IMHO, it can be implemented as a feature enhancement. Then
>> > prepare a formal testing to verify this implementation. It will be
>> better
>> > than checking it as defect verification.
>> >
>> > Regards,
>> > Shan Zhu
>> >
>> >
>> >
>> >
>> > On Mon, Dec 10, 2012 at 1:23 PM, Zhang Lu 
>> wrote:
>> >
>> > > Hi, Lucas and Shan
>> > >
>> > >There is no code for checking the validation of input value as Zhu
>> > Shan
>> > > said, I have got through this part code, not only whole numbers, but
>> also
>> > > all other categories existed this issue. So I think it can be handled
>> as
>> > a
>> > > defect. If user input an illegal value,  there is meaningless for data
>> > > validation.
>> > >
>> > > Regards,
>> > > Lu Zhang
>> > >
>> >
>>
>

Re: calc spreadsheet data validate window: 'Value' field not checked

[Lucas Burson]

Can we remove the test from TestLink or should I just mark it as failed?
Since it isn't implemented it doesn't make much sense to have it as a
regression test.
On Dec 9, 2012 11:50 PM, "Zhang Lu"  wrote:

> Hi Zhu shen,
>Sure, it can be implemented as a feature due to workload, thanks!
>
> On Mon, Dec 10, 2012 at 1:33 PM, Shan Zhu  wrote:
>
> > Hi, Lu
> > It's okay. IMHO, it can be implemented as a feature enhancement. Then
> > prepare a formal testing to verify this implementation. It will be better
> > than checking it as defect verification.
> >
> > Regards,
> > Shan Zhu
> >
> >
> >
> >
> > On Mon, Dec 10, 2012 at 1:23 PM, Zhang Lu  wrote:
> >
> > > Hi, Lucas and Shan
> > >
> > >There is no code for checking the validation of input value as Zhu
> > Shan
> > > said, I have got through this part code, not only whole numbers, but
> also
> > > all other categories existed this issue. So I think it can be handled
> as
> > a
> > > defect. If user input an illegal value,  there is meaningless for data
> > > validation.
> > >
> > > Regards,
> > > Lu Zhang
> > >
> >
>

Re: calc spreadsheet data validate window: 'Value' field not checked

[Zhang Lu]

Hi Zhu shen,
   Sure, it can be implemented as a feature due to workload, thanks!

On Mon, Dec 10, 2012 at 1:33 PM, Shan Zhu  wrote:

> Hi, Lu
> It's okay. IMHO, it can be implemented as a feature enhancement. Then
> prepare a formal testing to verify this implementation. It will be better
> than checking it as defect verification.
>
> Regards,
> Shan Zhu
>
>
>
>
> On Mon, Dec 10, 2012 at 1:23 PM, Zhang Lu  wrote:
>
> > Hi, Lucas and Shan
> >
> >There is no code for checking the validation of input value as Zhu
> Shan
> > said, I have got through this part code, not only whole numbers, but also
> > all other categories existed this issue. So I think it can be handled as
> a
> > defect. If user input an illegal value,  there is meaningless for data
> > validation.
> >
> > Regards,
> > Lu Zhang
> >
>

Re: calc spreadsheet data validate window: 'Value' field not checked

[Shan Zhu]

Hi, Lu
It's okay. IMHO, it can be implemented as a feature enhancement. Then
prepare a formal testing to verify this implementation. It will be better
than checking it as defect verification.

Regards,
Shan Zhu




On Mon, Dec 10, 2012 at 1:23 PM, Zhang Lu  wrote:

> Hi, Lucas and Shan
>
>There is no code for checking the validation of input value as Zhu Shan
> said, I have got through this part code, not only whole numbers, but also
> all other categories existed this issue. So I think it can be handled as a
> defect. If user input an illegal value,  there is meaningless for data
> validation.
>
> Regards,
> Lu Zhang
>

Re: calc spreadsheet data validate window: 'Value' field not checked

[Zhang Lu]

Hi, Lucas and Shan

   There is no code for checking the validation of input value as Zhu Shan
said, I have got through this part code, not only whole numbers, but also
all other categories existed this issue. So I think it can be handled as a
defect. If user input an illegal value,  there is meaningless for data
validation.

Regards,
Lu Zhang

Re: calc spreadsheet data validate window: 'Value' field not checked

[Shan Zhu]

Hi, Lucas

I think your scenario and expected result are reasonable.
In Validity dialog, Spreadsheet does not check whether the input in Value
field and the selection in Allow field are matched.
In MS Excel, it has been checked.


Regards,
Shan Zhu




On Sat, Dec 8, 2012 at 2:43 PM, Lucas Burson  wrote:

> Hi,
>
> I'm doing a regression test on a Calc spreadsheet using data validity
> for cells. It appears that if you open the validity window (toolbar
> Data -> Validity) the 'Value' box isn't checked against the allowed
> type. I don't see it in BZ... but I haven't used search enough to be
> sure.
> Or maybe I'm doing it wrong?
>
> This fails on Win7_32 and Ubuntu_64 for AOO 3.4.1 and 4.0M1 builds. It
> must have worked at one point in time since it's a regression test.
>
> 1. Create a spreadsheet.
> 2. Select some group of cells (or a single cell, doesn't matter)
> 3. On the main toolbar, select 'Data' -> 'Validity...'. The validity
> window opens.
> 4. For the Allow dropdown, select 'Whole Numbers'. Set Data to Equal.
> 5. In the Value field, enter some non-whole number (like 'm', or '1.2').
> 6. The illegal value should be rejected but it's not.
>
> Thanks,
> Lucas
>

calc spreadsheet data validate window: 'Value' field not checked

[Lucas Burson]

Hi,

I'm doing a regression test on a Calc spreadsheet using data validity
for cells. It appears that if you open the validity window (toolbar
Data -> Validity) the 'Value' box isn't checked against the allowed
type. I don't see it in BZ... but I haven't used search enough to be
sure.
Or maybe I'm doing it wrong?

This fails on Win7_32 and Ubuntu_64 for AOO 3.4.1 and 4.0M1 builds. It
must have worked at one point in time since it's a regression test.

1. Create a spreadsheet.
2. Select some group of cells (or a single cell, doesn't matter)
3. On the main toolbar, select 'Data' -> 'Validity...'. The validity
window opens.
4. For the Allow dropdown, select 'Whole Numbers'. Set Data to Equal.
5. In the Value field, enter some non-whole number (like 'm', or '1.2').
6. The illegal value should be rejected but it's not.

Thanks,
Lucas