twapi and Excel ; excel.exe still running background

[Nikh]

I am using 4.1.27 (I think it also happens with 4.2.12 ) 64bit twapi to write some data to excel and It is generating excel fine. However, I see excel.exe running in TaskManager after the operation.

twapi and Excel ; excel.exe still running background. Is there anything I need to need do other than -destroy commands?

package require twapi

set excelOutFile {C:\AA\1.xlsx}

set application [::twapi::comobj Excel.Application]
set workbooks [$application Workbooks]
set workbook [$workbooks Add]
set worksheets [$workbook Worksheets]
set worksheet [$worksheets Item [expr 1]]
$worksheet Activate
set cells [$worksheet Cells]

set range [$worksheet Range "A1"]
$range ColumnWidth [expr "20.0"]

set range [$worksheet Range "A1" "A8"]


$range NumberFormat "0.0"
set a "88501200600667"
$cells Item [expr 1] [expr 1] $a


$workbook SaveAs $excelOutFile
#$application Quit
$cells -destroy
$range -destroy
$worksheet -destroy
$worksheets -destroy
$workbook -destroy
$workbooks -destroy
$application -destroy

unset application

[Peter Dean]

I think it's still running because of #$application Quit (remove the #)

Here's how it might be done with Cawt

package require cawtcore
package require cawtoffice
package require cawtexcel

file mkdir c:/AA

set xls C:/AA/2.xlsx
catch {file delete -force $xls }

set application [Excel OpenNew]
set workbook [Excel AddWorkbook $application]
set worksheet [Excel GetWorksheetIdByIndex $workbook 1]
Excel SetWorksheetName $worksheet Sample
Excel SetColumnWidth $worksheet 1 20

set fmt1 [Excel GetNumberFormat $application "0" "0" .]
Excel SetRangeFormat \
[Excel SelectRangeByIndex $worksheet 1 1 8 1] real $fmt1

set a 88501200600667
Excel SetCellValue $worksheet 1 1 $a real $fmt1
Excel SetMatrixValues $worksheet \
[list [list {=R[-1]C/2}] [list {=R[-1]C*2}]] 2 1

Excel SaveAs $workbook $xls
Excel Quit $application
Cawt Destroy

[Nikh]

On Tuesday, February 13, 2018 at 6:38:58 PM UTC-5, Peter Dean wrote:

> I think it's still running because of #$application Quit (remove the #)
>
> Here's how it might be done with Cawt
>
> package require cawtcore
> package require cawtoffice
> package require cawtexcel
>
> file mkdir c:/AA
>
> set xls C:/AA/2.xlsx
> catch {file delete -force $xls }
>
> set application [Excel OpenNew]
> set workbook [Excel AddWorkbook $application]
> set worksheet [Excel GetWorksheetIdByIndex $workbook 1]
> Excel SetWorksheetName $worksheet Sample
> Excel SetColumnWidth $worksheet 1 20
>
> set fmt1 [Excel GetNumberFormat $application "0" "0" .]
> Excel SetRangeFormat \
> [Excel SelectRangeByIndex $worksheet 1 1 8 1] real $fmt1
>
> set a 88501200600667
> Excel SetCellValue $worksheet 1 1 $a real $fmt1
> Excel SetMatrixValues $worksheet \
> [list [list {=R[-1]C/2}] [list {=R[-1]C*2}]] 2 1
>
> Excel SaveAs $workbook $xls
> Excel Quit $application
> Cawt Destroy

I had it with and without '$application Quit' command but same behavior. I was finally able to figure this out. I have t different 'range' in the code. If I do 'range -destroy' before I invoke the subsequent 'range' it clears the excel.exe running in the background. So if I have 3 range's I'll have 3 range - destroy commands. I was using tcom before (32bit) and I only need to do $application Quit. I recently switched to twapi package. May be I'll consider cawt package going forward.

[pal...@yahoo.com]

On Wednesday, February 14, 2018 at 8:51:19 PM UTC+5:30, Nikh wrote:
> I had it with and without '$application Quit' command but same behavior. I was finally able to figure this out. I have t different 'range' in the code. If I do 'range -destroy' before I invoke the subsequent 'range' it clears the excel.exe running in the background. So if I have 3 range's I'll have 3 range - destroy commands. I was using tcom before (32bit) and I only need to do $application Quit. I recently switched to twapi package. May be I'll consider cawt package going forward.

Yes, unlike tcom which binds object lifetimes to references, twapi's com interface is layered on TclOO and requires explicit destroying. This can be mitigated to some extent using the "-with" option to reduce the number of temporary objects that need to be destroyed. For example, your script could be written as

package require twapi
set excelOutFile {c:\temp\aaa.xlsx}

set application [twapi::comobj Excel.Application]

set workbook [$application -with Workbooks Add]
set worksheet [$workbook -with Worksheets Item 1]
$worksheet Activate

$worksheet -with {{Range "A1"}} ColumnWidth 20.0
$worksheet -with {{Range "A1" "A8"}} NumberFormat 0.0

set a "88501200600667"
$worksheet -with Cells Item 1 1 $a

$workbook SaveAs $excelOutFile

$worksheet -destroy
$workbook -destroy
$application Quit
$application -destroy

See http://www.magicsplat.com/book/com.html for more details if you haven't already.

As an aside, note that CAWT is also layered on twapi but much easier to use to interface to the Office API.

/Ashok

[Nikh]

On Wednesday, February 14, 2018 at 11:42:05 AM UTC-5, pal...@yahoo.com wrote:

> Yes, unlike tcom which binds object lifetimes to references, twapi's com interface is layered on TclOO and requires explicit destroying. This can be mitigated to some extent using the "-with" option to reduce the number of temporary objects that need to be destroyed. For example, your script could be written as
>
> package require twapi
> set excelOutFile {c:\temp\aaa.xlsx}
>
> set application [twapi::comobj Excel.Application]
> set workbook [$application -with Workbooks Add]
> set worksheet [$workbook -with Worksheets Item 1]
> $worksheet Activate
>
> $worksheet -with {{Range "A1"}} ColumnWidth 20.0
> $worksheet -with {{Range "A1" "A8"}} NumberFormat 0.0
>
> set a "88501200600667"
> $worksheet -with Cells Item 1 1 $a
>
> $workbook SaveAs $excelOutFile
>
> $worksheet -destroy
> $workbook -destroy
> $application Quit
> $application -destroy
>
> See http://www.magicsplat.com/book/com.html for more details if you haven't already.
>
> As an aside, note that CAWT is also layered on twapi but much easier to use to interface to the Office API.
>
> /Ashok

Thanks Ashok. I didn't know about "-with" option.

[Peter Dean]

On 15-Feb-18 1:21 AM, Nikh wrote:
> On Tuesday, February 13, 2018 at 6:38:58 PM UTC-5, Peter Dean wrote:
>
>> I think it's still running because of #$application Quit (remove the #)
>

> I had it with and without '$application Quit' command but same behavior. I was finally able to figure this out. I have t different 'range' in the code. If I do 'range -destroy' before I invoke the subsequent 'range' it clears the excel.exe running in the background. So if I have 3 range's I'll have 3 range - destroy commands. I was using tcom before (32bit) and I only need to do $application Quit. I recently switched to twapi package. May be I'll consider cawt package going forward.
>

Oops, I didn't actually test that very well.


Cawt is included with magicsplat, so easy to try out. There's also
Documentation and TestPrograms folders included.

[Peter Dean]

On 15-Feb-18 2:42 AM, pal...@yahoo.com wrote:

> $worksheet -with {{Range "A1" "A8"}} NumberFormat 0.0

$worksheet -with {{Range "A1" "A8"}} NumberFormat \
[twapi::tclcast bstr 0.0]

for completeness.
I've been using Cawt exclusively but am beginning to see the value in
understanding twapi as well.

pd

[pal...@yahoo.com]

On Thursday, February 15, 2018 at 9:07:05 AM UTC+5:30, Peter Dean wrote:
> > $worksheet -with {{Range "A1" "A8"}} NumberFormat 0.0
>
> $worksheet -with {{Range "A1" "A8"}} NumberFormat \
> [twapi::tclcast bstr 0.0]
>
> for completeness.

> pd

You are *probably* right. As a point of clarification about tclcast -

In general, you do not have to use [tclcast]. When twapi (tcom works similarly) makes a COM method call, it checks if there is a type library available on the system for the component being invoked. If so, it will automatically convert the argument to integer, string etc. as appropriate. Most major applications, including Office, have type libraries installed by default (though they can be removed) and therefore do not need the [tclcast] except in the special case mentioned below.

There are two cases where a [tclcast] or [expr ] in case of tcom, might be required. The first is when no type library for the component is registered on the system. The other is when the type library specifies the parameter type is a VARIANT (essentially a union of types). Some Office methods behave differently (by design) based on whether (e.g.) a value is passed as a integer, a boolean, a string etc. In such cases, you need to be able to control the type explicitly as well.

I'm not sure if NumberFormat falls into this latter category. If so, you will need to use [tclcast].

/Ashok

[Ashok]

And in fact, if I do

$range -print

the entry for NumberFormat shows

(dispid 193) void NumberFormat 4 ([in] variant )

Given that the parameter type is listed as a variant, it is indeed wise
to use a [tclcast] on the passed argument. See the NumberFormat
documentation if behaviour changes based on the passed type.

/Ashok