Excel and Win32::Clipboard

roho has asked for the wisdom of the Perl Monks concerning the following question:

When I select and copy a cell in an Excel spreadsheet and use Win32::Clipboard to retrieve it (see code below), the cell contents I retrieve via Get() and write to a file are not displayed correctly when I edit the file. I tried setting binmode and encoding to UTF-8 but that has no affect. How can I get copied data from Excel to come through Win32::Clipboard and display correctly as text? As always, TIA.

Steps to reproduce the problem:

1. Open Excel spreadsheet, select and copy a cell.
2. Run the sample code below.
3. Edit the output file.

#!/usr/bin/perl
use strict;
use warnings;
use Win32::Clipboard;

my $clip = Win32::Clipboard();
my $text = $clip->Get();

my $ofile = "xx.bat";
open my $fh2,'>', $ofile or die "Error opening output file $ofile: $!\
+n";
binmode $fh2, ":encoding(UTF-8)";

print $fh2 $text, "\n";
[download]

"It's not how hard you work, it's how much you get done."

Comment on Excel and Win32::Clipboard Download Code

Replies are listed 'Best First'.
Re: Excel and Win32::Clipboard by Corion (Patriarch) on Mar 25, 2020 at 21:13 UTC
I suspect that the text is not encoded as UTF-8 but Latin-1, so decoding your text from that most likely helps: `use Encode 'decode'; ... my $text = $clip->Get(); $text = decode('Latin1', $text); open my $fh2,'>', $ofile or die "Error opening output file $ofile: $!\ +n"; binmode $fh2, ":encoding(UTF-8)"; print $fh2 $text, "\n";` [download] If that doesn't work out, consider posting the actual hex bytes of `$text` and the (HTML-encoded, for this site) text you expect. Maybe it is UCS-2 or UTF-16...	[reply] [d/l] [select]
Re: Excel and Win32::Clipboard by roho (Bishop) on Mar 25, 2020 at 23:29 UTC
Thank you for the suggestions, but unfortunately none of those made any difference. The spreadsheet cell contains the value "Test" The output in the file (with no encode and no binmode) is 48,265 bytes long!. It only copied the first four characters when I copied/pasted it here. Needless to say, it is a far cry from "Test": `BMBl` [download] Update: It works when "GetText()" is used instead of "Get()". Problem solved. :) "It's not how hard you work, it's how much you get done."	[reply] [d/l]
Re^2: Excel and Win32::Clipboard by pryrt (Abbot) on Mar 26, 2020 at 02:00 UTC
The output in the file ... is 48,265 bytes long! ... It works when "GetText()" is used instead of "Get()" The Windows clipboard allows storing multiple formats of the same data in the same clipboard entry. When you copy from Excel, the clipboard both has a native-Excel format (your 48k), which includes all the cell information, formatting, etc; and has a plaintext; and maybe has other formats as well. Win32::Clipboard has the `EnumFormats()` to list the formats currently in the clipboard, and `GetFormatName()` to give you more info for "custom" formats. By switching to `GetText()` , you were forcing it to give you the `CF_TEXT` version instead of the native Excel format.	[reply] [d/l] [select]


XP is just a number
	PerlMonks