ravi45722 has asked for the wisdom of the Perl Monks concerning the following question:

At first I tried my programs by using  Spreadsheet::ParseExcel; But due to so many limitations I selected Excel::Writer::XLSX. But in this I need to write a long number in the cell. For example 17,89,624

For this I am writing 

 $worksheet->write_number( $row, $col, sprintf ('%d',$data{'Peak_MO'})
+, $number);


$worksheet->write( $row, $col, sprintf ('%d',$data{'Peak_MO'}), $numbe
+r);

[download]

It's working fine but its writing as "1789624". But I need it comma separated for easy reading. Is there any format for that ??? 


my $number = $workbook->add_format(
                                        font     => 'Arial',
                                        border   => 1,
                                        size     => 10,
                                        align    => 'center',
                                        valign   => 'vcenter',
                                        italic   => '1',
                                  );

[download]

Replies are listed 'Best First'.
Re: Need help in Excel::Writer::XLSX
by Corion (Patriarch) on Feb 02, 2016 at 13:13 UTC

    Please read the section in Excel::Writer::XLSX on formats. Especially the method ->set_num_format().

[reply]
[d/l]
Re: Need help in Excel::Writer::XLSX
by poj (Abbot) on Feb 02, 2016 at 14:37 UTC

    If you want the Indian numbering 12,34,567 it can get complicated. This only covers positive numbers up to 9 digits.

    #!perl
use strict;
use warnings;
use Excel::Writer::XLSX;

my $book = Excel::Writer::XLSX->new( 'comma.xlsx' );
my $sheet = $book->add_worksheet();

#  Add and define a format
my $num = join ';','[>=10000000]##\,##\,##\,###',
                         '[>=100000]##\,##\,###',
                                         '#,##0';
                                         
#$num = '##,##0'; # this does not work
my $fmt_comma = $book->add_format(num_format=>$num);


# format whole column
my $width = 20;
$sheet->set_column( 0, 1, $width, $fmt_comma );

my $n = 1;
for (1..10){
  $n=$n*10;
  $sheet->write_number( $_, 0, $n);
  $sheet->write_number( $_, 1, -$n);
}

    [download]
    poj
[reply]
[d/l]
      #$num = '##,##0'; # this does not work

      The following code works with '#,##0'. This was copied from the cpan page and modified slightly.

      use strict;
use warnings;
use Excel::Writer::XLSX;

# Create a new Excel workbook
my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' );

# Add a worksheet
my $worksheet = $workbook->add_worksheet();
my $format02 = $workbook->add_format();
my $format03 = $workbook->add_format();
    
$format02->set_num_format( '#,##0' );
$worksheet->write( 1, 0, 1234.56, $format02 );      # 1,235
$worksheet->write( 2, 0, 123456789, $format02 );      # 123,456,789

$format03->set_num_format( '#,##0.00' );
$worksheet->write( 3, 1, 1234.56, $format03 );      # 1,234.56

      [download]

      Update: I used set_num_format per Corion's suggestion.

      Update: poj has modified Re: Need help in Excel::Writer::XLSX to add the link to the Wikipedia page Indian numbering system after I added this link in my other reply.

[reply]
[d/l]
[select]

        Yes, but the OP wanted 17,89,624 - 2 digits between commas

        poj
[reply]

Back to Seekers of Perl Wisdom