Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 758
  • Last Modified:

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?

0
sunny82
Asked:
sunny82
1 Solution
 
Osvel_SQLCommented:
hi, I use the windows keyboard shortcut  "control + H" to replace a space with no space
0
 
scarybotCommented:
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

0
 
sunny82Author Commented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
wilcoxonCommented:
This should work (though I didn't test it so let me know if there are any errors)...
#!/usr/local/bin/perl
use strict;
use warnings;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;

my $parser = Spreadsheet::ParseExcel::SaveParser->new();
my $xls = $parser->Parse('Spreadsheet.xls') or die "could not parse xls: $!";

foreach my $sheet ($xls->worksheets) {
    my ($row_min, $row_max) = $sheet->row_range();
    my ($col_min, $col_max) = $sheet->col_range();
    for my $row ($row_min..$row_max) {
COL:
        for my $col ($col_min..$col_max) {
            my $val = $sheet->get_cell($row, $col);
            next COL unless $val;
            $val = $val->value;
            if ($val =~ m{^\s} or $val =~ m{\s$}) {
                $val =~ s{^\s+}{};
                $val =~ s{\s+$}{};
                $sheet->AddCell($row, $col, $val);
            }
        }
    }
}

$xls->SaveAs('Spreadsheet.xls');

Open in new window

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

Thx a lot as always..
0
 
sunny82Author Commented:
Thx..it worked great.. I am closing this..
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now