Reply to topic  [ 8 posts ] 
turntable 
Author Message

Joined: 2007-04-12 14:59:36
Posts: 229
Is there a macro that can "turn" a table, exchanging rows and columns?


2009-08-10 03:28:57
Profile

Joined: 2008-05-17 04:02:32
Posts: 400
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:
### 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/NWPro/table/SwapRows&Columns_nwm.zip


2009-08-10 07:44:42
Profile

Joined: 2007-04-12 14:59:36
Posts: 229
Thank you, Kino. I was sure I had seen that somewhere ...


2009-08-10 09:45:55
Profile
User avatar

Joined: 2007-02-07 00:58:12
Posts: 876
Location: Japan
Here is my version of such a macro that *does* work for tables with merged cells.
Code:
# 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

_________________
philip


2009-08-11 16:40:33
Profile

Joined: 2007-04-12 14:59:36
Posts: 229
Impeccable! Thank you.


2009-08-12 02:19:01
Profile
User avatar

Joined: 2007-02-07 00:58:12
Posts: 876
Location: Japan
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.

_________________
philip


2011-10-02 08:19:23
Profile
User avatar

Joined: 2007-02-07 00:58:12
Posts: 876
Location: Japan
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.


Attachments:
Swap Rows and Columns (Merged) 3.0.nwm.zip [4.75 KiB]
Downloaded 271 times

_________________
philip
2011-10-04 01:15:13
Profile

Joined: 2006-12-14 09:09:51
Posts: 42
Works well here, quite useful for me.
Thanks!

Dirk Barends


2011-10-04 22:59:05
Profile
Display posts from previous:  Sort by  
Reply to topic   [ 8 posts ] 

Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Group
Designed by ST Software