Re: How to cancel numeric-formatting?

[Lvovich]

Add one more example:

the text in this cell "600,12395" the first time was transformed into "600.12395000004", and the second time was transformed into "600.123949999997"

How to download such cells without distortion, to keep two of code, separated by a comma, not to convert the numeric?

Help, please!

[jmcnamara]

On Dec 2, 8:08 pm, Lvovich <shtank2...@gmail.com> wrote:
> I have in the table cells with a list of codes separated by commas:
> "12576,16244", "600,12395", "15801,15976".
> In Excel type of cells - the text.
> When parsing such a cell is defined as Numeric, and are converted to:
> 12576,16244 > 12576.16244
> 600,12395 > 600.12395
> and etc.
> Moreover, the cell->value() and cell->unformatted() give the same value.
>
> How to make so that there was no change?
> It is necessary that the comma was.


Hi,

If you are getting numeric data out of Spreadsheet::ParseExcel then
the data in Excel in probably numeric. It doesn't coerce string data
to numeric data.

For example, consider the following program that produces an Excel
file with a string from your example:


use strict;
use warnings;
use Spreadsheet::WriteExcel;


my $workbook = Spreadsheet::WriteExcel->new( 'perl.xls' );
my $worksheet = $workbook->add_worksheet();

$worksheet->write_string( 'A1', '12576,16244' );

Parsing this with the example program that is in the Synopsis section
of the ParseExcel docs (http://search.cpan.org/~jmcnamara/Spreadsheet-
ParseExcel/lib/Spreadsheet/ParseExcel.pm#SYNOPSIS) gives the
following:

$ perl spe02.pl perl.xls
Worksheet name: Sheet1

Row, Col = (0, 0)
Value = 12576,16244
Unformatted = 12576,16244

So as you can see, the string is untouched in the Value and
Unformatted cases.

Also, it is reasonably hard to enter data like "12576,16244" in Excel
and get it to maintain it as a string instead of changing it to a
formatted number.

Check the cells in Excel and see if they have a number format applied.
Check what happens when you set the format to "General".

If you think that the data really is strings and that ParseExcel is
doing something wrong then send me an example file and I will have a
look at it.


Regards,

John.
--

[jmcnamara]

Hi,

This also reinforces the suspicion that the data is actually numeric.
This is the result of standard floating point rounding.

You can round it back using sprintf/printf. Something like:

$ perl -le 'printf "%.5f\n", 600.12395000004'
600.12395


Regards,

John.

[Lvovich]

Hi, John.

I spent two tests:

1) Book1.xls - all the cells of the General

2) Book2.xls - all cells in the Text

A test script parse_excel.cgi:

#!/usr/local/bin/perl

use strict;

use Spreadsheet::ParseExcel;

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

my $workbook = $parser->parse('Book1.xls');

open(FILE,'>Book1.txt');

if (!defined $workbook ) {

print FILE $parser->error()."\n";

}

for my $worksheet ( $workbook->worksheets() ) {

my ( $row_min, $row_max ) = $worksheet->row_range();

my ( $col_min, $col_max ) = $worksheet->col_range();

for my $row ( $row_min .. $row_max ) {

for my $col ( $col_min .. $col_max ) {

my $cell = $worksheet->get_cell( $row, $col );

next unless $cell;

print FILE "Row, Col    = ($row, $col)\n";

print FILE "Value       = ", $cell->value(),       "\n";

print FILE "Unformatted = ", $cell->unformatted(), "\n";

print FILE "Type        = ", $cell->type(), "\n";

print FILE "\n";

}

}

}

close(FILE);

exit;

The results - in the files Book1.txt, Book2.txt

Pay attention to the results of the type:

Row, Col    = (3, 2)

Value       = 12576.16244

Unformatted = 12576.16244

Type        = Numeric

The original text in this cell = "12576,16244"

List of values (separated by commas) into the Numeric!

I ask Your help!

P.S. All materials - in test_parse.zip

[jmcnamara]

On Tuesday, December 4, 2012 10:17:00 AM UTC, Lvovich wrote:

Hi, John.

I spent two tests:

1) Book1.xls - all the cells of the General

2) Book2.xls - all cells in the Text

 

 

 

Hi,

I had a look at the files you sent and the data that you think are strings are actually numbers.

Here is a screenshot of what I see when I open the file.

 

 

 

 

Perhaps you are on a system that shows the decimal point as a comma.

 

Also, if I use a data dumping tool on the raw Excel binary file this is what I see:

 

 

 

[LABELSST] (Record = 0x00FD, Length = 0x000A, 10)
    FD 00 0A 00 02 00 02 00 3F 00 03 00 00 00       |........?.....  |

    0   Record                   = 0x00FD         253
    2   Length                   = 0x000A         10 
    4   Row                      = 0x0002         2  
    6   Column                   = 0x0002         2  
    8   XF                       = 0x003F         63 
    10  Sting index              = 0x0003         3  

[NUMBER] (Record = 0x0203, Length = 0x000E, 14)
    03 02 0E 00 03 00 02 00 3F 00 C8 7B D5 CA 14 90 |........?..{....|
    C8 40                                           |.@              |

    0   Record                   = 0x0203         515
    2   Length                   = 0x000E         14 
    4   Row                      = 0x0003         3  
    6   Column                   = 0x0002         2  
    8   XF index                 = 0x003F         63 
    10  Double                   = 12576.162440

 

 

Cell (2,2) is a string and cell 3,2 is a number.

 

So, you are going to have to figure out some way of converting the decimal numbers back into a string in the format that you want. Here is a example to get you started. You will probably have to add extra logic to cover all cases you will encounter:

 

 

#!/usr/local/bin/perl
use strict;
use Spreadsheet::ParseExcel;

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

my $workbook = $parser->parse( 'Book2.xls' );

open( FILE, '>Book1.txt' );

if ( !defined $workbook ) {
    print FILE $parser->error() . "\n";
}

for my $worksheet ( $workbook->worksheets() ) {

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

    for my $row ( $row_min .. $row_max ) {
        for my $col ( $col_min .. $col_max ) {

            my $cell = $worksheet->get_cell( $row, $col );
            next unless $cell;

            print FILE "Row, Col    = ($row, $col)\n";

            if ( $col == 2 && $cell->type() eq 'Numeric' ) {
                my $value = $cell->value();

                # Reformat the number a string.
                $value = sprintf "%.5f", $value;

                # Change decimal point to comma.
                $value =~ s/\./,/;

                # Remove ,00000 item.
                $value =~ s/,00000//;

                print FILE "Value*      = ", $value, "\n";
            }
            else {

                print FILE "Value       = ", $cell->value(), "\n";
            }

            print FILE "Unformatted = ", $cell->unformatted(), "\n";
            print FILE "Type        = ", $cell->type(),        "\n";
            print FILE "\n";

        }
    }
}

close( FILE );

exit;

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

 

 

 

 

 

[Lvovich]

Hi, John.

Thank you very much for the help!

It helped me your analysis of the problem.

The reason was in the settings of the Windows + Excel. On my computer the Windows default separator of integer and fractional part of the comma. In the settings of the Excel was included support to regional standards. Therefore, the decimal numbers I've seen with a semicolon.

When I set in the regional settings of the separator of the integer and fractional parts of a = point, then in Excel immediately saw a decimal number, which previously seen as a transfer of a comma.

Once again - thank you!