Has anyone figured out how to use the command “make new table with data …”? What kind of data is being expected? An array like {{1, 2, 3}, {4, 5, 6}}?
Is there any chance to define header columns and rows and automatically resize cells to fit contents? (Other than doing it via GUI scripting.)
Thanks a lot for any suggestions.
(1) As far as I know we can’t do that. The only ways to fill cells are :
(a) use set value.
(b) GUIScripting pasting datas
(c) Create a table thru read a text file but it will create a new document.
(2) The word header doesn’t exists in the Numbers’s AppleScript dictionary so, GUI Scripting is required.
To do that, I use these three handlers :
--=====
(*
which = 10 --> set count of HEADer ROWs
my setNbXers(1, "Feuille 2", "Tableau 1", 10, 1) (* to set 1 HEADer ROW *)
which = 11 --> set count of HEADer COLUMNs
my setNbXers("my doc.numbers", "Sheet 5", "Table aux", 11, 4) (* to set 4 HEADer COLUMNs *)
which = 14 --> set count of FOOTer ROWs
my setNbXers("ASCII.numbers", "Sheet of paper", "TableTop", 14, 3) (* to set 3 FOOTer ROWs *)
*)
on setNbXers(docName, sheetName, tableName, whichItem, nbXers)
my selectTable(docName, sheetName, tableName)
set {theApp, mt, mi, ms} to {"Numbers", 6, whichItem, nbXers + 1}
tell application theApp to activate
tell application "System Events" to tell process theApp to tell menu bar 1 to ¬
tell menu bar item mt to tell menu 1 to tell menu item mi to tell menu 1
set maybe to get value of attribute "AXMenuitemMarkChar" of menu items
--> {missing value, missing value, missing value, "✓", missing value, missing value}
set existants to 0
repeat with i from 1 to count of maybe
if item i of maybe is not missing value then
set existants to i
exit repeat
end if
end repeat
if ms > existants then click menu item ms
end tell -- menu bar item.
end setNbXers
--=====
(*
Thanks to Nigel Garvey
*)
on selectTable(theDoc, theSheet, theTable)
local maybe, targetSheetRow, rowIndex, r
try
tell application "Numbers"
activate
set theDoc to name of document theDoc (* useful if the passed value is a number. Checks also that we passed the name of an open doc *)
end tell -- Numbers
on error
if my parleAnglais() then
error "The spreadsheet "" & theDoc & "" is not open !"
else
error "Le tableur « " & theDoc & " » n'est pas ouvert ! "
end if -- my parleAnglais
end try
try
tell application "Numbers" to tell document theDoc
set theSheet to name of sheet theSheet (* useful if the passed value is a number and check the availability of theSheet if it's a string *)
end tell -- Numbers
on error
if my parleAnglais() then
error "The sheet "" & theSheet & "" is unavailable in the spreadsheet "" & theDoc & "" !"
else
error "La feuille « " & theSheet & " » n'existe pas dans le tableur « " & theDoc & " » ! "
end if -- my parleAnglais
end try
try
tell application "Numbers" to tell document theDoc to tell sheet theSheet
set theTable to name of table theTable (* useful if the passed value is a number and check the availability of theSheet if it's a string *)
end tell -- Numbers
on error
if my parleAnglais() then
error "The table "" & theTable & "" is unavailable in the sheet "" & theSheet & "" of the spreadsheet "" & d & "" !"
else
error "La table « " & theTable & " » n'existe pas dans la feuille « " & theSheet & " » du tableur « " & d & " » ! "
end if -- my parleAnglais
end try
set maybe to 5 > (system attribute "sys2")
tell application "System Events" to tell application process "Numbers"
tell outline 1 of scroll area 1 of splitter group 1 of splitter group 1 of window theDoc
if maybe then (* macOS X 10.4.x
'(value of attributes contains 0)': '(value of attribute "AXDisclosureLevel" is 0)' sometimes works in Tiger, sometimes not.
The only possible instances of 0 amongst the attributes are the disclosure level of a sheet row and the index of the first row, which represents a sheet anyway.
Another possibility is '(value of attribute -1 is 0)', which makes me uneasy. *)
set targetSheetRow to first row where ((value of attributes contains 0) and (value of first static text is theSheet))
else (* macOS X 10.5.x or higher *)
set targetSheetRow to first row where ((value of attribute "AXDisclosureLevel" is 0) and ((groups is {}) and (value of first static text is theSheet)) or (value of first group's first static text is theSheet))
end if -- maybe
tell targetSheetRow to set {value of attribute "AXSelected", value of attribute "AXDisclosing"} to {true, true}
-- Get the sheet row's 0-based index + 2 for the following row's 1-based index.
set r to (value of attribute "AXIndex" of targetSheetRow) + 2
repeat until (value of first static text of row r is theTable)
set r to r + 1
end repeat
set value of attribute "AXSelected" of row r to true
end tell -- outline 1 .
end tell -- System Events
end selectTable
--=====
on parleAnglais()
local z
try
tell application "Numbers" to set z to localized string "Cancel"
on error
set z to "Cancel"
end try
return (z is not "Annuler")
end parleAnglais
--=====
Yvan KOENIG (VALLAURIS, France) mercredi 21 décembre 2011 10:57:37
Hi.
Like Yvan, I don’t think there’s any way to use ‘with data’ when making a table. This is quite fun to watch, though, and could be something on which you could build.
property minColumnWidth : 16
set theData to {{"Aardvark", "A Rather Elongated Header", "X"}, {1, 2, 3}, {4, 5, "Higginbottom"}, {"The", "bottom", "line"}}
set {x, y} to {count item 1 of theData, count theData}
tell application "Numbers"
activate
tell sheet 1 of document 1
-- Make a new table. To stabilize row heights in the column-width adjustments later on, create it with an additional column into which we'll insert numerals.
tell (make new table with properties {column count:(x + 1), row count:y})
-- Set preferred properties: say, no shaded bold header column on the left, Geneva 9 pt text in all but the headers along the top, automatic data formats and alignment.
set selection range to range ("A2:A" & y)
tell application "System Events" to keystroke "b" using {command down}
set selection range to cell "A1"
tell range ("A2:" & name of last cell of cell range)
set background color to {65535, 65535, 65535}
set font name to "Geneva"
set font size to 9
set format to automatic
set alignment to auto align
end tell
-- Enter the data.
repeat with rowNo from 1 to y
repeat with columnNo from 1 to x
set value of cell columnNo of row rowNo of it to item columnNo of item rowNo of theData
end repeat
set value of cell (x + 1) of row rowNo to 0 -- Insert the height stabilising numeral in the extra column.
end repeat
(* Adjust column widths to fit contents. *)
-- Get the current unwrapped heights of the rows.
set text wrap of cell range to false
set defaultHeights to height of rows
set text wrap of cell range to true
-- With wrap enabled, stretch all the columns until any wrap disappears and the rows are all the default heights.
set w to width of column 1
repeat until (height of rows is defaultHeights)
set w to w + 10
set width of columns to w
end repeat
-- Compress each column individually until a wrap occurs and increases a row height, then widen the column again by just enough to lose the wrap and restore the original height.
repeat with i from 1 to x
repeat with thisW from w to minColumnWidth by -5
set width of column i to thisW
if (height of rows is not defaultHeights) then exit repeat
end repeat
repeat with thisW from thisW to w
set width of column i to thisW
if (height of rows is defaultHeights) then exit repeat
end repeat
end repeat
-- Delete the temporary extra column
delete column -1
end tell
end tell
end tell
Edit: Hah! No sooner had I posted this than I tried it with an extra row of text data and the column-width adjustment went haywire. I’m thinking about it…
Later: OK. I think I’ve fixed it by means of a dreadful hack. Row heights are not reliable when they only contain non-numeric text. The hack is to append an extra column with a numeral in each cell to stabilise the row heights until the width adjustments are complete. The extra column is then deleted. Testing this with a different font, I’ve realised that I also need to unembolden the text in column “A” if it’s not to be used as a header column. The only way to do this appears to be with System Events.