Read Numbers File

I am trying to read row of columns into a lists. But so far no success. Also it seems pretty slow when it reads.

Any suggestions on how to do this and make it fast?? I get this error Numbers got an error: Can’t make «class » “B4:I4” of table “Table 1” of sheet “FM1” of document “RSC.numbers” into type reference.

thanks
Roger


display dialog “Enter Table Number:” default answer “1” buttons {“Restart”, “Cancel”, “Next”} default button 3

set RSCFile to “Macintosh HD 2:RSC:RSC.Numbers”

– open numbers
tell application “Numbers”
launch
activate

-- set file 
set RSCDoc to (open RSCFile)

-- tell numbers file to
tell RSCDoc
	tell table 1 of sheet 1
		copy range "B4:I4" to myCitType
		set myTemp to item 1 of myCitType
		display dialog myTemp
	end tell
end tell

end tell

You can copy the values stored in the cells, not a range as you did.
Use something like :


tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set the_values to value of cells "B4" thru "I4"
end tell

Yvan KOENIG (VALLAURIS, France) vendredi 26 octobre 2012 18:26:11

Thanks for the help that made it work.

The set RSCDoc to (open RSCFile) takes too long to open the file so I am going to have to rethink this unless I can speed it up somehow.

Roger

Hello

Try this version :


--display dialog "Enter Table Number:" default answer "1" buttons {"Restart", "Cancel", "Next"} default button 3


set RSCFile to "Macintosh HD 2:RSC:RSC.Numbers" as alias
--set RSCFile to ((path to desktop as text) & "aaah.numbers:") as alias
-- open numbers
tell application "Numbers"
	activate
	
	-- set document 
	set RSCDoc to open RSCFile
	
	-- tell numbers document
	tell RSCDoc
		tell table 1 of sheet 1
			# scheme 1
			set myCitType to value of cells "B4" thru "I4"
			set myTemp to item 1 of myCitType
			# trick to get rid of non fatal errors issued when we call an OSAX feature from a tell application block.
			tell application "SystemUIServer" to display dialog myTemp
			#scheme 2
			set myTemp to value of cell "B4"
			# trick to get rid of non fatal errors issued when we call an OSAX feature from a tell application block.
			tell application "SystemUIServer" to display dialog myTemp
		end tell # table.
	end tell # document
end tell # Numbers

Here it’s fast.

Yvan KOENIG (VALLAURIS, France) vendredi 26 octobre 2012 21:34:15

Another way is this:


tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set the_values to value of cells of range "B4:I4"
end tell

It seems to be slightly faster, although you wouldn’t notice it except with large numbers of items or extractions. And it produces different results when more than one row is involved. If the range were “B4:I5”, value of cells “B4” thru “I5” would include the value of cell “A5”, whereas value of cells of range “B4:I5” would not. Similarly, with a single column, value of cells of range “B4:B5” returns just two values, where as value of cells “B4” thru “B5” returns a whole lot more.

Edit: Range descriptor typo corrected in the paragraph above! :rolleyes:

Thanks

where it is slow is the opening of the file.

The cell loading works fast and great

Roger

Good point Nigel.
I forgot the difference between
value of cells “B4” thru “I5”
and value of cells of range “B4:I5”

Happily, here we grabbed values in a single row.

Yvan KOENIG (VALLAURIS, France) samedi 27 octobre 2012 11:17:24

Hello

As far as I know, we can’t change the speed of file opening except perhaps store the documents on a SSD unit.

Oops.
There is an alternate way but ii’s a bit cumbersome.
With Lion and Mountain Lion, the feature named Versions add a serious offset to file opening because the app opens the file and extracts the datas describing the old versions.
When you save a document after editing it, save also a duplicate.
This one will have no versions datas.
So, when you just want to extract values, it’s more efficient to extract them from the replicate.
But as I wrote, it’s cumbersome because we must be sure that the replicate matches the main doc contents.
Of course iwe may use System Events to duplicate the original and read the replicate to extract values but I’m not sure that this scheme reduce the access time.

The structure of the index.xml file describing the document is complicated so I assume that extracting directly from it would be really difficult and I’m sure that I am unable to achieve such task.

With a bit of luck, Apple will be fair enough to give us a new parameter allowing us to ask a script to open only the main document without getting the Versions datas.

Yvan KOENIG (VALLAURIS, France) samedi 27 octobre 2012 11:20:30

And of course there was a typo in my post, which I’ve now corrected. :rolleyes:

Maybe this short script displaying the behaviour of two different schemes may be useful :


set col1 to 2
set row1 to 4
set col2 to 8
set row2 to 7
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set theValues to value of cells of range ((name of cell col1 of row row1) & ":" & (name of cell col2 of row row2))
	--> {"B4", "C4", "D4", "E4", "F4", "G4", "H4", "B5", "C5", "D5", "E5", "F5", "G5", "H5", "B6", "C6", "D6", "E6", "F6", "G6", "H6", "B7", "C7", "D7", "E7", "F7", "G7", "H7"}
	set the_Values to {}
	repeat with r from row1 to row2
		set end of the_Values to value of cells of range ((name of cell col1 of row r) & ":" & name of cell col2 of row r)
	end repeat
end tell
the_Values
-->{{"B4", "C4", "D4", "E4", "F4", "G4", "H4"}, {"B5", "C5", "D5", "E5", "F5", "G5", "H5"}, {"B6", "C6", "D6", "E6", "F6", "G6", "H6"}, {"B7", "C7", "D7", "E7", "F7", "G7", "H7"}}

set E6contents to item (((6 - row1) * (1 + col2 - col1)) + (1 + 5 - col1)) of theValues

set E6_contents to item (1 + 5 - col1) of item (1 + 6 - row1) of the_Values
{E6contents, E6_contents}

As you see, the first one returns a single list. Extracting the values is less clever than with the second one which extracts a list of lists.

Yvan KOENIG (VALLAURIS, France) samedi 27 octobre 2012 16:42:58