asked on

How Do I remove spaces from an excel sheet using Perl module

Hi,

I have an excel sheet, which has several leading and trailing spaces which I want to remove from the excel sheet and then use the excel sheet for some other work. Should I use Perl Spreadsheet ParseExcel SaveParser module?

How should I do it?

Osvel_SQL

hi, I use the windows keyboard shortcut "control + H" to replace a space with no space

scarybot

Try this...

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  

my $Book = $Excel->Workbooks->Open("c:/worksheet.xls"); 

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

#change this to the number of rows/cols you want to change
foreach my $row (1..500) 
{
 foreach my $col (1..500)
 {
  next unless defined $Sheet->Cells($row,$col)->{'Value'};

   $Sheet->Cells($row,$col)->{'Value'} =~ m/^\s+(.*)\s+/;
   $Sheet->Cells($row,$col)->{'Value'} = $1;        
 }
}

$Book->Close;

Open in new window

sunny82

ASKER

Hi,

Thx fr the reply. Can you pls send an example with unix modules like Spreadsheet Parseexcel and Spreadsheet Parseexcel Saveparser etc.. I cannot use a windows perl module like OLE in my code as it is a unix box.

ASKER CERTIFIED SOLUTION

wilcoxon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sunny82

ASKER

Thx.. I will try it and let you know, if not today, by Monday definitely..

Thx a lot as always..

sunny82

ASKER

Thx..it worked great.. I am closing this..