Solved

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

Posted on 2010-08-14
6
723 Views
Last Modified: 2012-05-10
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
Comment
Question by:sunny82
6 Comments
 

Expert Comment

by:Osvel_SQL
ID: 33437728
hi, I use the windows keyboard shortcut  "control + H" to replace a space with no space
0
 
LVL 1

Expert Comment

by:scarybot
ID: 33437781
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
 

Author Comment

by:sunny82
ID: 33437861
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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 33438001
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
 

Author Comment

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

Thx a lot as always..
0
 

Author Comment

by:sunny82
ID: 33446330
Thx..it worked great.. I am closing this..
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question