—
– Created by: Takaaki Naganoya
– Created on: 2023/08/07
—
– Copyright © 2023 Piyomaru Software, All Rights Reserved
—
use AppleScript
use scripting additions
use framework "Foundation"
set targRow to 22
set targCol to 2
set targPictureID to 1
set tCell to getPictureLocatedCellRowColByItsPosition(targRow, targCol, targPictureID) of me
–> {rowNum:23, columnNum:2}
–ループで指定セルを左上とした3×3の範囲のセルから、指定のPictureと重なっている面積が最も大きいものを返す
on getPictureLocatedCellRowColByItsPosition(targRow as integer, targCol as integer, targPictureID as integer)
–Picture 1のNSRectを求める
set pRect to getAPictureRectByID(targPictureID) of me
set tmpList to {}
–始点座標から3×3の範囲のセルとPictureの重なる面積を計算
repeat with x from 0 to 2
repeat with y from 0 to 2
set tmpRow to (targRow + y)
set tmpCol to (targCol + x)
set tmpRect to retExcelCellRect(tmpRow, tmpCol) of me
–指定Pictureと指定Cellの共通部分の矩形座標を計算
set a1Res to (current application’s NSIntersectionRect(tmpRect, pRect)) as {record, list}
–指定Pictureと指定Cellの共通部分の面積を計算
set a1Area to calcAnArea(a1Res) of me
if a1Area > 0 then
set the end of tmpList to {columnNum:tmpCol, rowNum:tmpRow, interAreaWithPict:a1Area}
end if
end repeat
end repeat
–面積で降順ソート
set zList to (sortRecListByLabel(tmpList, {"interAreaWithPict"}, {false}) of me) as list
–> {{columnNum:2, rowNum:23, interAreaWithPict:4973.377807617188}, {columnNum:2, rowNum:24, interAreaWithPict:747.665495456778}, {columnNum:2, rowNum:22, interAreaWithPict:333.296168677043}}
set resCell to first item of zList
set resCol to columnNum of resCell
set resRow to rowNum of resCell
return {rowNum:resRow, columnNum:resCol}
end getPictureLocatedCellRowColByItsPosition
–IDで指定したPictureのNSRectを返す
on getAPictureRectByID(anID as integer)
tell application "Microsoft Excel"
tell active workbook
tell active sheet
set anImage to picture anID
set tmpX to left position of anImage
set tmpY to top of anImage
set tmpW to width of anImage
set tmpH to height of anImage
end tell
end tell
end tell
set aZRect to current application’s NSMakeRect(tmpX, tmpY, tmpW, tmpH)
return aZRect
end getAPictureRectByID
–NSRectの面積を計算する
on calcAnArea(aRect)
if class of aRect = list then
set xWidth to (item 1 of item 2 of aRect)
set yHeight to (item 2 of item 2 of aRect)
else
set xWidth to (aRect’s |size|’s width)
set yHeight to (aRect’s |size|’s height)
end if
set anArea to xWidth * yHeight
return anArea
end calcAnArea
–指定Row, ColumnのCellのNSRectを返す
on retExcelCellRect(y as integer, x as integer)
tell application "Microsoft Excel"
tell active workbook
tell active sheet
tell row y
tell cell x
set xMin0 to left position
set yMin0 to top
set xWidth0 to width
set yHeight0 to height
end tell
end tell
end tell
end tell
end tell
set a1Rect to current application’s NSMakeRect(xMin0, yMin0, xWidth0, yHeight0)
return a1Rect
end retExcelCellRect
–リストに入れたレコードを、指定の属性ラベルの値でソート
on sortRecListByLabel(aRecList as list, aLabelStr as list, ascendF as list)
set aArray to current application’s NSArray’s arrayWithArray:aRecList
set aCount to length of aLabelStr
set sortDescArray to current application’s NSMutableArray’s new()
repeat with i from 1 to aCount
set aLabel to (item i of aLabelStr)
set aKey to (item i of ascendF)
set sortDesc to (current application’s NSSortDescriptor’s alloc()’s initWithKey:aLabel ascending:aKey)
(sortDescArray’s addObject:sortDesc)
end repeat
return (aArray’s sortedArrayUsingDescriptors:sortDescArray)
end sortRecListByLabel
2023年に書いた価値あるAppleScript – AppleScriptの穴 says:
[…] Excelの書類上に置かれているpictureが置かれているセルのアドレスを推定する v2 […]