Help need on Perl: how can I delete entire column in excel and write updated data in new excel file using Perl?

See more:

Perl

I am new to Perl. I have excel file say sample.xls; which looks like follows.

			Extra Information		Date	Usage				xyz	Coverage Area	
Item Name	Customer Name	Age	Hobbies	Activities	Time Stamp	First 15 days	Second 30 days	Min Price	Max Price	Units	City	Rural
iPhone_6S_Silver	Rob_C_Miller_01	NA	---	---	5/2/2010			99	499	1	0	0
iPhone_6Plus_Silver	Rob_C_Miller_01	NA	---	---	5/5/2010			99	499	2	0	0
iPhone_6S_Silver	Rob_C_Miller_02	NA	---	---	6/30/2010			99	499	2	0	0
iPhone_6Plus_Silver	Rob_C_Miller_02	NA	---	---	8/10/2011			99	499	4	0	0
iPhone_6S_Silver	Ralph_M_Walter_03	NA	---	---	2/10/2011			99	499	2	0	0
iPhone_6Plus_Silver	Ralph_M_Walter_03	NA	---	---	9/3/2010			99	499	3	0	0
iPhone_6S_Silver	Traver_A_Roberts_04	NA	---	---	4/25/2011			99	499	5	0	0
iPhone_6Plus_Silver	Traver_N_Roberts_04	NA	---	---	1/12/2012			99	499	1	0	0

There are about data of 1000 rows like this. I want to parse this file and write it in another file say "output.xls" with following output format.

Item Name	Spec	First name	Middle Name	Last Name	Customer Number	Age	Units
iPhone_6S_Silver	6S	Rob	C	 Miller	1	NA	1
iPhone_6S_Silver	6S	Rob	C	Miller	2	NA	2
iPhone_6S_Silver	6S	Ralph	M	Walter	3	NA	2
iPhone_6S_Silver	6S	Traver	A	Roberts	4	NA	5
							
							
iPhone_6Plus_Silver	6Plus	Rob	C	Miller	1	NA	2
iPhone_6Plus_Silver	6Plus	Rob	C	Miller	2	NA	4
iPhone_6Plus_Silver	6Plus	Ralph	M	Walter	3	NA	3
iPhone_6Plus_Silver	6Plus	Traver	N	Roberts	4	NA	1

I have written a script in perl, however, it doesn't give me the exact output the way I want. Also, looks like the script is not very efficient. Can anyone guide me how I can improve my script as well as have my output as shown in "output.xls" ??

Here's the Script:

What I have tried:

#!/usr/bin/perl –w

use strict;
use warnings;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Chart;


# Read the input and output filenames.
my $inputfile  = "path/sample.xls";
my $outputfile = "path/output.xls";

if ( !$inputfile || !$outputfile ) {
    die( "Couldn't find file\n" );
}

my $parser      = Spreadsheet::ParseExcel->new();
my $inwb = $parser->parse( $inputfile );

if ( !defined $inwb ) {
    die "Parsing error: ", $parser->error(), ".\n";
}

my $outwb = Spreadsheet::WriteExcel->new( $outputfile );


my $inws  = $inwb->worksheet( "Sheet1" );
my $outws = $outwb->add_worksheet("Sheet1");
my $out_row       = 0;

my ( $row_min, $row_max ) = $inws->row_range();
my ( $col_min, $col_max ) = $inws->col_range();

my $format = $outwb->add_format(
center_across => 1,
bold => 1,
size => 10,
border => 4,
color => 'black',
border_color => 'black',
align => 'vcenter',
);


$outws->write(0,0, "Item Name", $format);
$outws->write(0,1, "Spec", $format);
$outws->write(0,2, "First name", $format);
$outws->write(0,3, "Middle Name", $format);
$outws->write(0,4, "Last Name", $format);
$outws->write(0,5, "Customer Number", $format);
$outws->write(0,6, "Age", $format);
$outws->write(0,7, "Units", $format);

my $col_count = 1;
#$row_min = 1;
for my $inws ( $inwb->worksheets() ) {
    my ( $row_min, $row_max ) = $inws->row_range();
    my ( $col_min, $col_max ) = $inws->col_range();

    for my $in_row ( 2 .. $row_max ) {

        for my $col (  0 .. 0 ) {


            my $cell = $inws->get_cell( $in_row, $col);

            my @fields = split /_/, $cell->value();
                next unless $cell;


            $outws->write($in_row,$col, $cell->value());
            $outws->write($in_row,$col+1, $fields[1]);
        }
    }   

    for my $in_row ( 2 .. $row_max ) {

        for my $col (  1 .. 1 ) {

            my $cell = $inws->get_cell( $in_row, $col);

            my @fields = split /_/, $cell->value();
                next unless $cell;


            #$outws->write($in_row,$col+1, $cell->value());
            $outws->write($in_row,$col+1, $fields[0]);
            $outws->write($in_row,$col+2, $fields[1]);
            $outws->write($in_row,$col+3, $fields[2]);
            $outws->write($in_row,$col+4, $fields[3]);
        }
    }   

    for my $in_row ( 2 .. $row_max ) {

        for my $col (  2 .. 2 ) {

            my $cell = $inws->get_cell( $in_row, $col);

            my @fields = split /_/, $cell->value();
                next unless $cell;


            $outws->write($in_row,6, $cell->value());
        }
    }   

    for my $in_row ( 2 .. $row_max ) {

        for my $col (  3 .. 9 ) {

            my $cell = $inws->get_cell( $in_row, $col);

            next unless $cell;


        }
    }   

    for my $in_row ( 2 .. $row_max ) {
        for my $col ( 10 .. 10 ) {

            my $cell = $inws->get_cell( $in_row, $col );

            next unless $cell;


            $outws->write($in_row,7, $cell->value());

        }
    }

}