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

Detecting and Deleting empty rows and cols in excel using win32::OLE

by Anonymous Monk
on Sep 19, 2008 at 13:05 UTC ( #712517=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks, I am working on large excel sheet which has huge empty rows and cols. I searched net for any code sample but in vain. I also searched the documentation in MSDN on Excel methods. Request you to show some sample code to detect and delete empty rows and cols. Thanks
  • Comment on Detecting and Deleting empty rows and cols in excel using win32::OLE

Replies are listed 'Best First'.
Re: Detecting and Deleting empty rows and cols in excel using win32::OLE
by psini (Deacon) on Sep 19, 2008 at 13:32 UTC

    Disclaimer: I don't have a Win box at hand now, so I can't verify what I'm saying.

    I think that if you give a "select all" command and then examine the selection object, you should be able to determine the last used column and row. After that, it's only a matter of iterating through rows and columns to find the empty ones.

    Rule One: "Do not act incautiously when confronting a little bald wrinkly smiling man."

Re: Detecting and Deleting empty rows and cols in excel using win32::OLE
by strat (Canon) on Sep 20, 2008 at 09:17 UTC

    Hello,

    you can use the method UsedRange on a worksheet object to find the last used cell in a worksheet object. This way, you don't need to iterate over all 64K rows (or more with Excel 2007) which should save much time.

    But I don't know a better way to find out which lines or columns are empty than an iteration.

    Best regards,
    perl -e "s>>*F>e=>y)\*martinF)stronat)=>print,print v8.8.8.32.11.32"

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://712517]
Approved by moritz
help
Chatterbox?
[GotToBTru]: $aohoaoh->[0] is a hash reference, so $aohoaoh->[0]->{ key1} would work
[1nickt]: If 1nickt is the some guy, that's not what he said.
[GotToBTru]: thepkd might not know about last hour of cb
[1nickt]: my $x = $aohoaoh->[0]->{' foo'}->[0]->{'bar' }; should work
[thepkd]: its not $aohoaoh->[0]. its $aohoaoh[0]->{'foo '}. Tried it. Its working. Thnaks
[thepkd]: Hey 1nickt sorry man. What
[GotToBTru]: a ref too far ;)

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (12)
As of 2016-12-06 13:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    On a regular basis, I'm most likely to spy upon:













    Results (105 votes). Check out past polls.