Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
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?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (7)
As of 2016-09-30 06:16 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Extraterrestrials haven't visited the Earth yet because:







    Results (562 votes). Check out past polls.