Solved

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

Posted on 2010-08-14
6
747 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

631 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