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."

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]
      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]

Back to Seekers of Perl Wisdom