Perl syntax for formatting columns and text

Shell Programming and Scripting
1

Dear all,

Pzl let me know what is the syntax for converting the columns format to text as i have lots of values for

but when i put these values in xls sheet the values are automatically converted to

and one more question i have is how to call values from shell script into perl script
eg.

Thanks in advance

2

Are you using spreadheet::? How (what perl library) are you trying to use?
Spreadsheet::WriteExcel - search.cpan.org

3

Dear jim mcnamara,

I have just started to use perl script,pl find my code as below,every CSV created here contains text in first column as mentioned before but as soon as i open this excel sheet in windows all zeros which are prefixed to numbers are getting removed.Hence i want to convert this first column as text.
Thanks in advance.

#!/usr/bin/perl -w

use strict;
use warnings;
use Spreadsheet::Read;
use Spreadsheet::WriteExcel;
#Reading data from csv
my $workbook1 = ReadData("Overview.csv");
my @data1 = Spreadsheet::Read::rows($workbook1->[1]);

my $workbook2 = ReadData("1.csv");
my @data2 = Spreadsheet::Read::rows($workbook2->[1]);

my $workbook3 = ReadData("2.csv");
my @data3 = Spreadsheet::Read::rows($workbook3->[1]);

my $workbook4 = ReadData("3.csv");
my @data4 = Spreadsheet::Read::rows($workbook4->[1]);

my $workbook5 = ReadData("4.csv");
my @data5 = Spreadsheet::Read::rows($workbook5->[1]);

my $workbook6 = ReadData("5.csv");
my @data6 = Spreadsheet::Read::rows($workbook6->[1]);

#Creating new Excel workbook

my $workbook7 = Spreadsheet::WriteExcel->new('sagar.xls');

my $format1 = $workbook7->add_format();
## $format1->set_bold();
  $format1->set_color('black');
   $format1->set_align('center');
   $format1->set_font("Calibri");
   $format1->set_bg_color('orange');

   my $format2 = $workbook7->add_format();
    $format2->set_bold();
     $format2->set_color('green');
      $format2->set_align('right');
       $format2->set_bg_color('red');

       # Create a format for the column headings
       my $header = $workbook7->add_format();
       $header->set_bold();
       $header->set_font("Courier New");
       $header->set_align('center');
       $header->set_align('vcenter');
       $header->set_color('white');
       $header->set_bg_color('black');



       # Adding worksheet to workbook

    my $worksheet1 = $workbook7->add_worksheet("1");
    my $worksheet2 = $workbook7->add_worksheet("2");
    my $worksheet3 = $workbook7->add_worksheet("3");
    my $worksheet4 = $workbook7->add_worksheet("4");
    my $worksheet5 = $workbook7->add_worksheet("5");
    my $worksheet6 = $workbook7->add_worksheet("6");

###################################

$worksheet2->set_column(1, 1, 10);
$worksheet2->set_column(2, 2, 10);
$worksheet2->set_column(3, 3, 15);
$worksheet2->set_column(4, 4, 55);
$worksheet2->set_column(5, 5, 24);

$worksheet3->set_column(1, 1, 10);
$worksheet3->set_column(2, 2, 10);
$worksheet3->set_column(3, 3, 15);
$worksheet3->set_column(4, 4, 55);
$worksheet3->set_column(5, 5, 24);

$worksheet4->set_column(1, 1, 10);
$worksheet4->set_column(2, 2, 10);
$worksheet4->set_column(3, 3, 15);
$worksheet4->set_column(4, 4, 55);
$worksheet4->set_column(5, 5, 24);

$worksheet5->set_column(1, 1, 10);
$worksheet5->set_column(2, 2, 10);
$worksheet5->set_column(3, 3, 15);
$worksheet5->set_column(4, 4, 55);
$worksheet5->set_column(5, 5, 24);

$worksheet6->set_column(1, 1, 10);
$worksheet6->set_column(2, 2, 10);
$worksheet6->set_column(3, 3, 15);
$worksheet6->set_column(4, 4, 80);
$worksheet6->set_column(5, 5, 24);





       # Write the data to worksheet

       $worksheet1->write( 'B2',[\@data1],$format1);
       $worksheet2->write( 'B2',[\@data2],$format1);
       $worksheet3->write( 'B2',[\@data3],$format1);
       $worksheet4->write( 'B2',[\@data4],$format1);
       $worksheet5->write( 'B2',[\@data5],$format1);
       $worksheet6->write( 'B2',[\@data6],$format1);

##############

$worksheet2->write( 'B1','ID',$header);
$worksheet2->write( 'C1','ACTION',$header);
$worksheet2->write( 'D1','ACTION_DATE',$header);
$worksheet2->write( 'E1','MSG',$header);
$worksheet2->write( 'F1','DT',$header);
4

From Spreadsheet::WriteExcel perldoc page