how to remove blank cell with perl

meghavarshney has asked for the wisdom of the Perl Monks concerning the following question:

hiii any body tell me whats wrong with this prog... actually m trying to make a prog to delete blank cell form my excel sheet but m unable in it ...if u know then plz let me know. My code is

 #!/usr/bin/perl -w

use strict;
use warnings;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Spreadsheet::WriteExcel;

$Win32::OLE::Warn = 3;     

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{Visible} = 1;
my $Book = $Excel->Workbooks->Open("c:\\perl\\copy of BK11.xls"); 

my $Sheet = $Book->Worksheets(1);
my $count=0;

foreach my $row (1..3226)
{
 foreach my $col (1..14)
 {

 if (!defined($Sheet->Cells($row,$col)->{'Value'})) 
{

$Sheet->Cells($row,$col)->EntireRow->Delete;
$count++;
}

  }
}
[download]

Comment on how to remove blank cell with perl Download Code

Replies are listed 'Best First'.
Re: how to remove blank cell with perl by SuicideJunkie (Vicar) on Aug 03, 2011 at 13:50 UTC
Did you want to delete a cell or a row? You said: `$Sheet->Cells($row,$col)->EntireRow->Delete;` Also, note that you do not correct for the movement of the remaining cells... your rows and/or columns will be different after removing some cells, and that misalignment will cause trouble as well. Please provide a better description of what you want to happen, and what is happening. Examples are good!	[reply] [d/l]
Re^2: how to remove blank cell with perl by meghavarshney (Novice) on Aug 03, 2011 at 17:09 UTC
i want to delete that every row which contain a single blank cell. i want that my prog chk every line or single cell ,if its contain any blank cell then it has to delete that entire row. but whenver m trying to that it's not working properly.... example : let's suppose this is my data 0.344828497 0.344828497 0.650764559 1.09085343 0.124328135 0.98550043 0.310340121 0.757023247 1.15704371 0.214124805 0.613531653 0.90303827 0.250961574 0.214124805 0.613531653 0.454175893 0.028569152 0.50589093 0.056583528 i want... 0.650764559 1.09085343 0.124328135 0.98550043 0.310340121 0.757023247 1.15704371 0.214124805 0.613531653 0.90303827 0.250961574 0.214124805 but the o/p was somehowe like in this formate 0.344828497 0.344828497 0.650764559 1.09085343 0.124328135 0.98550043 0.613531653 0.90303827 0.250961574 0.214124805 0.613531653 0.454175893 0.028569152 [download]	[reply] [d/l]
Re: how to remove blank cell with perl by moritz (Cardinal) on Aug 03, 2011 at 13:22 UTC
In what way does the code fail to do what you want it to do? Perl 6 - second systems done right	[reply]
Re^2: how to remove blank cell with perl by meghavarshney (Novice) on Aug 03, 2011 at 13:31 UTC
when ever i trying to run my prog its just run in simple way but in o/p it's deleted most of the values(blank cell and valued cell).......so will u help me.. ??	[reply]
Re^3: how to remove blank cell with perl by moritz (Cardinal) on Aug 03, 2011 at 13:45 UTC
it's deleted most of the values(blank cell and valued cell) Looking at your code again, it deletes the whole row whenever it finds an empty cell. If that's not what you want, don't do it that way (maybe try to delete the cell instead?) Perl 6 - second systems done right	[reply]
Re: how to remove blank cell with perl by davies (Monsignor) on Aug 03, 2011 at 17:23 UTC
In Re: Delete Row in Excel, I described a common mistake in deleting rows. I've made it frequently myself. But I've also shown in that post how to set up Excel so that your would-be helpers can tell what your problem is. If the node I have linked to doesn't solve your problem, please post code that actually shows the problem. I had a colleague who frequently came to me with the problem "it doesn't work". My invariable reply was "is your computer switched on?", which eventually convinced him that helpers have to start somewhere, and the chosen starting point may not be very near the actual problem. But don't DREAM of posting 3226 rows of data! Cut it down to the minimum that will show the problem and I would bet that you will get it solved quite quickly. A few comments on the code you have posted: first, you specify `Spreadsheet::WriteExcel` without ever using it. I don't know it myself, as all machines I use have Excel and controlling Excel directly gives far greater power, which I usually need. Second, the `with` construct is safe in VBA and Perl, but it is buggy (and documented as such) in VB6, so it is something I avoid in any language except VBA, and frequently even then. Third, you define and increment `$count` but never use it. Fourth, your indentation has (let's be charitable) got scrambled in transit from the source to here, and is confusing to the reader as a result Regards, John Davies Update: fixed minor typo	[reply] [d/l] [select]