Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
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
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 studying the Monastery: (6)
As of 2014-12-22 22:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (132 votes), past polls