turntable

[js]

Is there a macro that can "turn" a table, exchanging rows and columns?

[Kino]

For that purpose, I wrote this a while ago. Note that it does not work for a table containing a merged or split cell as explained in comment lines.

Code: Select all

### Swap Rows & Columns ###

# This macro transforms a table like this

#     1   2   3   4
# A  a1  a2  a3  a4
# B  b1  b2  b3  b4
# C  c1  c2  c3  c4

# into

#     A   B   C
# 1  a1  b1  c1
# 2  a2  b2  c2
# 3  a3  b3  c3
# 4  a4  b4  c4

# This macro tries to find
#  1. a table in which the caret is situated
#  2. a table just below the insertion point (select start)
#  3. a table actually selected (from within or from without)
# in this order and transforms it.

# ATTENTION: This macro will not work poperly for a table containing
# merged or split cells.

Require Application Version '3.1'
$doc = Document.active
$tableSel = $doc.tableSelection
if $tableSel == undefined  # i.e. if there is no table having a selection in it...
	Select Start  # Deselect a selection if any
	$check = Select Next Table
	if $check  # i.e. if the command above returned <true>...
		$tableSel = $doc.tableSelection
	else
		exit 'No table found below the insertion point, exit...'
	end
end

$table = $tableSel.table
$numRows = $numNewCols = $table.rowCount
$numCols = $numNewRows = $table.columnCount
$tableData = Array.new

$i = 0
while $i < $numRows  # put cell data in $tableData from left to right and from up to down
	$j = 0
	while $j < $numCols
		$t = $table.textAtRowAndColumn $i, $j
		$tableData.appendValue $t.copy  # use copy to make the value independant
		$j += 1
	end
	$i += 1
end

if $numRows != $numCols  # then, transform a table (N x M) into a table (M x N)
	if $numRows < $numCols
		$i = $j = $numCols - $numRows
		$addRowRange = Range.new $numRows - 1, 1
		$addColRange = Range.new 0, 1
		$delRowRange = Range.new 0, 1
		$delColRange = Range.new $numCols - $j, $j
		$add = ':Table:Insert:Row Below'
		$del = ':Table:Delete:Columns'
	else
		$i = $j = $numRows - $numCols
		$addRowRange = Range.new 0, 1
		$addColRange = Range.new $numCols - 1, 1
		$delRowRange = Range.new $numRows - $j, $j
		$delColRange = Range.new 0, 1
		$add = ':Table:Insert:Column to the Right'
		$del = ':Table:Delete:Rows'
	end
	$sel = TableSelection.new $table, $addRowRange, $addColRange
	$doc.setSelection $sel
	while $i  # add rows or columns
		Menu $add
		$i -= 1
	end
	$sel = TableSelection.new $table, $delRowRange, $delColRange
	$doc.setSelection $sel
	Menu $del  # delete rows or columns
	Select Start  # deselect selection
end

$i = 0
while $i < $numNewCols  # replace cell data from up to down and from left to right
	$j = 0
	while $j < $numNewRows
		$data = $table.textAtRowAndColumn $j, $i
		$range = Range.new 0, $data.length
		$data.replaceInRange $range, $tableData.dequeue
		$j += 1
	end
	$i += 1
end

### end of macro ###

Formatted macro file:
http://www2.odn.ne.jp/alt-quinon/files/ ... ns_nwm.zip

[js]

Thank you, Kino. I was sure I had seen that somewhere ...

[phspaelti]

Here is my version of such a macro that *does* work for tables with merged cells.

Code: Select all

# Macro Swap Rows and Columns with Merged Cells
# version 1.0
# written 2009.3.16 by p. spaelti
#
# This macro has been inspired by and benefitted from code written by Kino.
# 
#
# The following macro transforms a table of the form
#
#   1 2 3		  A B
# A . . .	->	1 . .
# B . . .		2 . .
#        		3 . .
#
# while respecting differing numbers of rows and columns
# as well as *merged cells*.

# Make sure that a table object is selected.
# 
$doc = Document.active
$tbl_sel = $doc.tableSelection
if Defined $tbl_sel
	Select:Table
else
	Select Start
	if Select Next Table
		$tbl_sel = $doc.tableSelection
	else
		die “Can’t find any table.”
	end
end
#
# Define variables
#
$tbl = $tbl_sel.table
$row_cnt = $tbl.rowCount
$col_cnt = $tbl.columnCount
$tbl_size = $row_cnt * $col_cnt
$table_has_merged_cells = $doc.textSelections.count < $tbl_size

if $table_has_merged_cells
#Prompt “this table has merged cells”

#
# The next section creates coordinate pairs for all the merged cells.
# Assuming a continuous cell numbering (0, 1, 2…) through all cells,
# it finds the number of the top/left and the bottom/right cell and stores
# them in the hash $merge_pairs. The top/left coordinate is the key.
#
# The method for finding the coordinate pairs is based on the fact that 
# the “. textAtRowAndColumn” can insert text even in ‘hidden’ cells.
# First a counter is inserted in every table cell. Then we check the text
# selection for each cell. In the case of merged cells the text selection will
# contain the counter for the top/left cell rather than the expected counter.
#
$merge_pairs = Hash.new
$counter = 0
$row = 0
while $row < $row_cnt
	$row_range = Range.new $row, 1
	$col = 0
	while $col < $col_cnt
		$cell_text = $tbl.textAtRowAndColumn $row, $col
		$insert_text = $counter & “ ”
		$cell_text.insertAtIndex 0, $insert_text
		$col_range = Range.new $col, 1
		$sel = TableSelection.new $tbl, $row_range, $col_range
		$doc.setSelection $sel
		$num_range = Range.newWithLocationAndBound 0, $doc.textSelection.text.rangeOfString(“ ”)
		$num = $doc.textSelection.text.subtextInRange $num_range
		if $num != $counter # if the number and the counter don’t match this is a merged cell
			$delete_text = $counter & “ ”
			$del_range = $cell_text.rangeOfString $delete_text
			$cell_text.deleteInRange $del_range # Remove the counter from ‘hidden’ cells.
			$merge_pairs{$num} = $counter
		end
		$counter += 1
		$col +=1
	end
	$row += 1
end
# Remove counters from the visible cells (Counters in ‘hidden’ cells were already removed).
Select:Table
Find and Replace “^\\d+ ”, “”, “Esa”

#
# Convert the coordinate pairs into range variables for later selection.
#
$merged_row_ranges = Array.new
$merged_col_ranges = Array.new
foreach $first, $second in $merge_pairs
	$c1 = $first % $col_cnt
	$c2 = $second % $col_cnt
	$r1 = $first - $c1
	$r1 = $r1 / $col_cnt
	$r2 = $second - $c2
	$r2 = $r2 / $col_cnt
	$row_range = Range.newWithLocations $r1, $r2
	$col_range = Range.newWithLocations $c1, $c2
	$merged_row_ranges.push $row_range
	$merged_col_ranges.push $col_range
end

end # Special treatment of tables with merged cells

# Create an array for storing the text information of the table cells.
#
# The following code is from Kino’s Swap Row and Columns macro.
# Since the code uses the “.textAtRowAndColumn”, command it works
# correctly even for merged cells.
#
$table_data = Array.new

$i = 0
while $i < $row_cnt  # put cell data in $table_data from left to right and from up to down
	$j = 0
	while $j < $col_cnt
		$t = $tbl.textAtRowAndColumn $i, $j
		$table_data.appendValue $t.copy  # use copy to make the value independant
		$j += 1
	end
	$i += 1
end

# The next section reshapes the table, switching rows and columns
# merging the appropriate cells.

# Reduce Table to 1x1 to ensure elimination of merged cells
#
# To guarantee proper selection of ranges we need to avoid selecting
# from a merged cell. The only cell guaranteed to be unmerged is a
# newly created cell at coordinates <0,0>.
#
$range = Range.new 0,1
$sel = TableSelection.new $tbl, $range, $range
$doc.setSelection $sel
:Table:Insert:Row Above
:Table:Insert:Column to the Left

# Now delete all the old rows.
#
$del_row_range = Range.new 1, $row_cnt
$del_col_range = Range.new 0, 1
$sel = TableSelection.new $tbl, $del_row_range, $del_col_range
$doc.setSelection $sel
:Table:Delete:Rows

# Now delete all the columns.
#
$del_row_range = Range.new 0, 1
$del_col_range = Range.new 1, $col_cnt
$sel = TableSelection.new $tbl, $del_row_range, $del_col_range
$doc.setSelection $sel
:Table:Delete:Columns

# Add rows and columns again, but in flipped amounts.
#
$i = $row_cnt -1
while $i  # add columns
	:Table:Insert:Column to the Right
	$i -= 1
end
$i = $col_cnt - 1
while $i  # add rows
	:Table:Insert:Row Below
	$i -= 1
end

# Now we use the previously created ranges to merge cells.
# Again rows and columns must be flipped.
# 
if $table_has_merged_cells
	foreach $i, $col_range in $merged_row_ranges
		$row_range = $merged_col_ranges[$i]
		$sel = TableSelection.new $tbl, $row_range, $col_range
		$doc.setSelection $sel
		:Table:Merge Cells
	end
end

# Return the stored content to the new table.
#
$i = 0
while $i < $row_cnt  # replace cell data from up to down and from left to right
	$j = 0
	while $j < $col_cnt
		$data = $tbl.textAtRowAndColumn $j, $i
		$range = Range.new 0, $data.length
		$data.replaceInRange $range, $table_data.dequeue
		$j += 1
	end
	$i += 1
end
# End of macro

[js]

Impeccable! Thank you.

[phspaelti]

Well I don't know when this happened. But the guys in Solana Beach "fixed a bug" (as Colbert would say). Apparently they felt a need to change the behavior for selecting merged table cells. Previously if you tried to select the "hidden" cell (using the macro command) you would end up with the "visible cell" selected. This behavior was perhaps strange, though it did make sense to me. But now if you do the same you end with an "invisible selection". If you query the program as to what the selection is, you get the "correct" table coordinates (i.e., the ones you actually selected). This invisble cell is different from real table cells, as it doesn't give a text selection (the text selection returns "undefined").

From my perspective this is a bummer. While it is still possible to tell which cells have been merged, there is now no way to tell which cells they have been merged with.

So it seems that my macro for "turning tables with merged cells" no longer works.

Anyhow this is now a request for a proper .mergedCells property. This should presumably be a property of the table object, and should consist of an array of table selections.

[phspaelti]

Well I figured a way around the problem. So here now an updated version of the macro for swapping rows and columns of a table, that works even for tables with merged cells.

[Dirk_Barends]

Works well here, quite useful for me.
Thanks!

Dirk Barends