in reply to Need help in Excel::Writer::XLSX

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

Replies are listed 'Best First'.
Re^2: Need help in Excel::Writer::XLSX
by Lotus1 (Vicar) on Feb 02, 2016 at 17:23 UTC
    #$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]

        The OP mostly commented about the lack of commas so I assumed it was a typo that there were two digits between commas. I had never heard of the Indian numbering system. I misunderstood your "this does not work" comment also. Thanks.

[reply]

In Section Seekers of Perl Wisdom