Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Excel and Win32::Clipboard

by roho (Chancellor)
on Mar 25, 2020 at 19:52 UTC ( #11114648=perlquestion: print w/replies, xml ) Need Help??

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

"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 (Pope) 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";

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

Re: Excel and Win32::Clipboard
by roho (Chancellor) 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

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

      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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11114648]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (9)
As of 2020-07-09 18:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?