How to paste an array into Excel with TCOM
sd
I searched in many places for clean examples on pasting a large tcl
array into an Excel 2007 spreadsheet. I can do it by creating loops
and adding values in each cell one by one, but this is a slow process.
I found this http://wiki.tcl.tk/11900 but the example under "Speeding
up Excel Writes" does not work. Neither does the suggestion just
below it. I keep getting
(bin) 35 % $range Select
0x800a03ec {Select method of Range class failed}
(bin) 36 % $range -namedarg TextToColumns Destination [::tcom::na]
DataType [::tcom::na] TextQualifier [::tcom::na] ConsecutiveDelimiter
[expr 1] Tab [::tcom::na] Semicolon [expr 1] Comma [::tcom::na] Space
[::tcom::na] Other [::tcom::na] FieldInfo $FieldInf DecimalSeparator
[::tcom::na] ThousandsSeparator [::tcom::na] TrailingMinusNumbers
[expr 1]
0x800a03ec {No data was selected to parse.}
Any direction on where to find a good example(s) would be appreciated.
/sd
hae
> Hello!
>
> I searched in many places for clean examples on pasting a large tcl
> array into an Excel 2007 spreadsheet. I can do it by creating loops
> and adding values in each cell one by one, but this is a slow process.
>
> up Excel Writes" does not work. Neither does the suggestion just
> below it. I keep getting
>
> (bin) 35 % $range Select
> 0x800a03ec {Select method of Range class failed}
>
> (bin) 36 % $range -namedarg TextToColumns Destination [::tcom::na]
> DataType [::tcom::na] TextQualifier [::tcom::na] ConsecutiveDelimiter
> [expr 1] Tab [::tcom::na] Semicolon [expr 1] Comma [::tcom::na] Space
> [::tcom::na] Other [::tcom::na] FieldInfo $FieldInf DecimalSeparator
> [::tcom::na] ThousandsSeparator [::tcom::na] TrailingMinusNumbers
> [expr 1]
> 0x800a03ec {No data was selected to parse.}
>
> Any direction on where to find a good example(s) would be appreciated.
>
> /sd
The clipboard supports different formats. Using a clipboard format spy
you will see that e.g. Excel fills the clipboard with different
formats when you copy a selection.
My guess is, that this also works the other way round. So you could
provide the data in a format in the clipboard that Excel understands
and then just automate Excel with TCOM to just paste the content of
the clipboard.
Here are 2 tools that can show you the clipboard formats:
* http://www.ciansoft.com/cflist/default.asp
* http://www.delphidabbler.com/software/cfs/scr
Rüdiger
Peter W
To paste large amounts of data from tcl to excel via the clipboard, I
use the dde instead of tcom:
package require dde
clipboard clear
clipboard append "My\tmessage\nover\t2lines"
dde execute Excel System {[PASTE()][SELECT("R[2]C")]}
This will add the data in excel at the active cell, and then move the
active cell 2 rows down.
Peter
Jeff Godfrey
> Hello!
>
> I searched in many places for clean examples on pasting a large tcl
> array into an Excel 2007 spreadsheet.
In addition to the other responses, you could also save your "large
array" to a plain CSV file (using tcllib's csv module), and then load
that file into Excel via COM using either twapi or tcom.
While that may sound more complex, it really boils down to just a (very)
little bit of code when leveraging the mentioned libraries.
Obviously, this assumes that you want the data in a *new* spreadsheet,
and not somehow appended to existing spreadsheet data.
Jeff
Paul Obermeier
data to the clipboard in CSV format.
No need for Tk (for the clipboard command) and no need for additional
conversions with the TextToColumns method.
Another advantage: It works with strings containing the CSV separator
character.
Paul
Am 28.03.11 11:40, schrieb hae:
sd
I tried your example but got an error at the last line:
(bin) 17 % $worksheetId Paste
0x800a03ec {Paste method of Worksheet class failed}
sd
something else. I'm getting close achieving my goal, but ran into an
awkward problem when it comes to converting a list into columns.
Below is a small example of what's happening. I'm creating an Excel
workbook and adding three new worksheets in it. I'm pasting the same
data in all three new worksheets, but the data in the first new
worksheet "NewSheet 1" is not delimited correctly into columns. All
the data in "NewSheet 2" and "NewSheet 3" is in correct format. Any
idea why the the first time data is pasted in NewSheet 1 is not
yielding the desired effects?
Thanks
/sd
package require tcom
package require csv
set myList {{1 2 3 4} {5 6 7 8} {9 10 11 12}}
set application [::tcom::ref createobject Excel.Application]
$application Visible 0
# Create inital workbook.
set workbooks [$application Workbooks]
set workbook [$workbooks Add]
set worksheets [$workbook Worksheets]
# create three new sheets, rename them, and enter some data
for {set n 1} {$n <= 3} {incr n} {
set lastWorksheet [$worksheets Item [$worksheets Count]]
set worksheet [$worksheets Add [::tcom::na] $lastWorksheet]
$worksheet Name "NewSheet $n"
clipboard clear
clipboard append [csv::joinlist $myList "|"]
set range [$worksheet Range A6]
$range Select
$worksheet Paste
$range -namedarg TextToColumns Other 1 OtherChar "|"
}
$workbook SaveAs {C:\test.xlsx}
set application {}
Paul Obermeier
First problem was, that the clipboard format identifier is not unique
across machines, but the name of the clipboard format: "Csv".
So you have to get the clipboard format identifier with function
register_clipboard_format.
I also added an "after 10" command, because on some machines it seems to
take some time before the clipboard data is visible for the other
application.
The Wiki page has been updated.
Paul
Paul Obermeier
But, if I replace the line
set range [$worksheet Range A6]
with
set range [$worksheet Range "A6:A8"]
it works with my Excel 2007.
Hope this helps.
Paul
sd
> set range [$worksheet Range "A6:A8"]
Actually, when I put my application to use, it might need to paste
thousands of lines, which can different from one worksheet to
another. So, to be safe I'll have to do something like:
set range [$worksheet Range "A6:A999999"]
which does the trick where I was having problems on the first set of
data. All other worksheets work fine without having to resort to the
above trick. I hate to put such crude patches when I don't know
exactly why they work... they just do.
Thanks again for your help.
/sd
Neil
> > set range [$worksheet Range "A6:A8"]
>
> Actually, when I put my application to use, it might need to paste
> thousands of lines, which can different from one worksheet to
> another. So, to be safe I'll have to do something like:
>
> set range [$worksheet Range "A6:A999999"]
Just a sidebar, really:
Excel's actual limit, as of Excel2010 is 1,048,576 rows (before Excel
2010, it was 65,536 rows).
Paul Obermeier
The needed range can be calculated quite easily from the size of the
list, so I left it as an exercise to you.
Here is the code to generalize the procedure:
set startRow 6
set endRow [expr $startRow + [llength $myList] - 1]
set rangeStr [format "A%d:A%d" $startRow $endRow]
set range [$worksheet Range $rangeStr]
puts "Selecting range $rangeStr"
Paul
Paul Obermeier
0.3.2), which implement fast data transfer via the clipboard in both
directions.
See http://www.posoft.de/html/extTcomExcel.html
Tested on Windows 7 with Excel 97, 2000, 2003 and 2007.
Paul
sd
Paul,
Thanks for providing this extension. I will try to use it by looking
at the examples provided.
/sd
sd
same workbook? I have Sheet1 with lots of charts and other data and I
want to keep it but create another copy of it. I tried different
variations such as:
${worksheet} Copy [::tcom::na] ${worksheet}
but non worked.
Regards,
/sd
Paul Obermeier
TcomExcel has a utility function called CopyWorksheet (in file
excelUtil.tcl).
Paul
Am 11.04.11 17:26, schrieb sd: