This queue is for tickets about the Spreadsheet-ParseExcel CPAN distribution.
Maintainer(s)' notes
If you are reporting a bug in Spreadsheet::ParseExcel here are some pointers
1) State the issues as clearly and as concisely as possible. A simple program or Excel test file (see below) will often explain the issue better than a lot of text.
2) Provide information on your system, version of perl and module versions. The following program will generate everything that is required. Put this information in your bug report.
#!/usr/bin/perl -w
print "\n Perl version : $]";
print "\n OS name : $^O";
print "\n Module versions: (not all are required)\n";
my @modules = qw(
Spreadsheet::ParseExcel
Scalar::Util
Unicode::Map
Spreadsheet::WriteExcel
Parse::RecDescent
File::Temp
OLE::Storage_Lite
IO::Stringy
);
for my $module (@modules) {
my $version;
eval "require $module";
if (not $@) {
$version = $module->VERSION;
$version = '(unknown)' if not defined $version;
}
else {
$version = '(not installed)';
}
printf "%21s%-24s\t%s\n", "", $module, $version;
}
__END__
3) Upgrade to the latest version of Spreadsheet::ParseExcel (or at least test on a system with an upgraded version). The issue you are reporting may already have been fixed.
4) Create a small example program that demonstrates your problem. The program should be as small as possible. A few lines of codes are worth tens of lines of text when trying to describe a bug.
5) Supply an Excel file that demonstrates the problem. This is very important. If the file is big, or contains confidential information, try to reduce it down to the smallest Excel file that represents the issue. If you don't wish to post a file here then send it to me directly: jmcnamara@cpan.org
6) Say if the test file was created by Excel, OpenOffice, Gnumeric or something else. Say which version of that application you used.
7) If you are submitting a patch you should check with the maintainer whether the issue has already been patched or if a fix is in the works. Patches should be accompanied by test cases.
Asking a question
If you would like to ask a more general question there is the Spreadsheet::ParseExcel Google Group.
Owner: |
Nobody in particular
|
Requestors: |
andrea.pfister [...] mmsag.ch
|
Cc: |
|
AdminCc: |
|
|
Severity: |
(no value)
|
Broken in: |
(no value)
|
Fixed in: |
(no value)
|
|
Thu Aug 19 09:34:09 2010
andrea.pfister [...] mmsag.ch - Ticket created
Hi
I use your Modul since 2 years and i'm very happy with it.
Version: 0.54
Now i have a problem with zeros definition. If some one make this definition #'##0.;(#'##0);"―" in excel then i get a 0 in perl.
Is there a way to get the ― from the interface?
Best regards
Andrea
Multimedia Solutions AGAndrea Pfister | SoftwareentwicklerinDorfstr. 29 | CH-8037 Zurich | Schweiz T +41 44 446 83 30 | T direkt +41 44 446 83 15 | F +41 44 446 83 38andrea.pfister@mmsag.ch |
http://www.mmsag.ch
75-Jahre-Firmenjubilaum und ein neues Druckzentrum im Jahr 2010 - gute Grunde zum Feiern. Am Freitag, 24. September 2010, offnen wir um 15 Uhr die Tore zur Open-House-Veranstaltung.
Erfolgreiches erstes Geschaftsberichte-Symposium - Impressionen und Prasentationen.
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
Mon Aug 23 11:20:11 2010
jmcnamara [...] cpan.org - Correspondence added
On Thu Aug 19 09:34:09 2010, andrea.pfister@mmsag.ch wrote:
Show quoted text>
> Now i have a problem with zeros definition. If some one make this
> definition #'##0.;(#'##0);"―" in excel then i get a 0 in perl.
>
> Is there a way to get the ― from the interface?
Hi,
You can get the format that will be applied as follows:
#!/usr/bin/perl -w
use strict;
use Spreadsheet::ParseExcel;
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('new_file.xls');
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;
my $format_str = $workbook->{FmtClass}->FmtStringDef( $cell->{FormatNo},
$workbook );
print "Row, Col = ($row, $col)\n";
print "Unformatted = ", $cell->unformatted(), "\n";
print "Format string = ", $format_str, "\n";
print "Value = ", $cell->value(), "\n";
print "\n";
}
}
}
__END__
The syntax is a bit unwieldy and it will be hopefully simplified in a later release.
Anyway, I would recommend matching the format string with the format that is giving
problems and substitute the cell value as necessary.
Note, the format stored in the file may not be the same as the format that you applied in the
Excel dialog. Excel stores formats with US style punctuation so the your format would
probably be stored as something like: '#,##0.;(#,##0);"-"'
John.
--
Mon Aug 23 11:20:12 2010
The RT System itself - Status changed from 'new' to 'open'
Thu Aug 26 02:53:04 2010
andrea.pfister [...] mmsag.ch - Correspondence added
Thank you I will try this.
Have a nice day!
Andrea
Multimedia Solutions AGAndrea Pfister | SoftwareentwicklerinDorfstr. 29 | CH-8037 Zürich | Schweiz T +41 44 446 83 30 | T direkt +41 44 446 83 15 | F +41 44 446 83 38andrea.pfister@mmsag.ch |
http://www.mmsag.ch
75-Jahre-Firmenjubiläum und ein neues Druckzentrum im Jahr 2010 - gute Gründe zum Feiern. Am Freitag, 24. September 2010, öffnen wir um 15 Uhr die Tore zur Open-House-Veranstaltung.
Erfolgreiches erstes Geschäftsberichte-Symposium - Impressionen und Präsentationen.
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
Show quoted text-----Ursprüngliche Nachricht-----
Von: John McNamara via RT [mailto:bug-Spreadsheet-ParseExcel@rt.cpan.org]
Gesendet: Montag, 23. August 2010 17:20
An: Pfister Andrea
Betreff: [rt.cpan.org #60520] problem with 0 enterpretation
<URL:
https://rt.cpan.org/Ticket/Display.html?id=60520 >
On Thu Aug 19 09:34:09 2010, andrea.pfister@mmsag.ch wrote:
>
> Now i have a problem with zeros definition. If some one make this
> definition #'##0.;(#'##0);"―" in excel then i get a 0 in perl.
>
> Is there a way to get the ― from the interface?
Hi,
You can get the format that will be applied as follows:
#!/usr/bin/perl -w
use strict;
use Spreadsheet::ParseExcel;
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('new_file.xls');
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;
my $format_str = $workbook->{FmtClass}->FmtStringDef( $cell->{FormatNo},
$workbook );
print "Row, Col = ($row, $col)\n";
print "Unformatted = ", $cell->unformatted(), "\n";
print "Format string = ", $format_str, "\n";
print "Value = ", $cell->value(), "\n";
print "\n";
}
}
}
__END__
The syntax is a bit unwieldy and it will be hopefully simplified in a later release.
Anyway, I would recommend matching the format string with the format that is giving
problems and substitute the cell value as necessary.
Note, the format stored in the file may not be the same as the format that you applied in the
Excel dialog. Excel stores formats with US style punctuation so the your format would
probably be stored as something like: '#,##0.;(#,##0);"-"'
John.
--
Thu Aug 26 03:34:02 2010
andrea.pfister [...] mmsag.ch - Correspondence added
Hi John
I try it, but i gat the rong Formatting string
My Code:
my $format_str = $p->{workbook}->{FmtClass}->FmtStringDef(
$CurrentCell->{FormatNo}, $p->{workbook} );
if ($format_str and $CurrentCell->Value())
{
warn "...............................";
warn $CurrentCell->Value();
warn $CurrentCell->{'Val'};
warn $format_str;
warn "------------------------------";
}
I have a Format in Excel like this #'##0.;(#'##0.);"-";_(@_)
And the output of the program is
...............................
0.00000.
0
_ "SFr."\ * #,##0_ ;_ "SFr."\ * \-#,##0_ ;_ "SFr."\ * "-"_ ;_ @_
------------------------------
Did I do something wrong?
Greeting
Andrea
Multimedia Solutions AGAndrea Pfister | SoftwareentwicklerinDorfstr. 29 | CH-8037 Zurich | Schweiz T +41 44 446 83 30 | T direkt +41 44 446 83 15 | F +41 44 446 83 38andrea.pfister@mmsag.ch |
http://www.mmsag.ch
75-Jahre-Firmenjubilaum und ein neues Druckzentrum im Jahr 2010 - gute Grunde zum Feiern. Am Freitag, 24. September 2010, offnen wir um 15 Uhr die Tore zur Open-House-Veranstaltung.
Erfolgreiches erstes Geschaftsberichte-Symposium - Impressionen und Prasentationen.
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
Thu Aug 26 10:57:55 2010
jmcnamara [...] cpan.org - Correspondence added
On Thu Aug 26 03:34:02 2010, andrea.pfister@mmsag.ch wrote:
Show quoted text>
> I have a Format in Excel like this #'##0.;(#'##0.);"-";_(@_)
>
> And the output of the program is
>
> ...............................
> 0.00000.
> 0
> _ "SFr."\ * #,##0_ ;_ "SFr."\ * \-#,##0_ ;_ "SFr."\ * "-"_ ;_ @_
> ------------------------------
>
> Did I do something wrong?
Hi,
You didn't do anything wrong. Excel doesn't store the format in the same
way as it displays it. It the above example it has added a locale and
escaped some of the characters.
John.
--
Tue Sep 21 05:17:14 2010
jmcnamara [...] cpan.org - Correspondence added
Question answered, I hope.
Closing the issue,
John.
--
Tue Sep 21 05:17:15 2010
jmcnamara [...] cpan.org - Status changed from 'open' to 'resolved'
Tue Sep 21 05:17:31 2010
andrea.pfister [...] mmsag.ch - Correspondence added
Sehr geehrte Damen und Herren,
besten Dank für Ihre Nachricht. Heute bin ich abwesend. Ihre E-Mail wird nicht weitergeleitet. In dringenden Fällen rufen Sie bitte unsere Hauptnummer an (Telefon 044 446 83 30).
--
Dear Sir or Madam,
thank you for your message. Unfortunately, i'm not available today. For immediate assistance, please contact our switchboard (+41 44 446 83 30). Your email will not be forwarded.
Freundliche Grüsse | With best regards
Andrea Pfister
Multimedia Solutions AGAndrea Pfister | SoftwareentwicklerinDorfstr. 29 | CH-8037 Zürich | Schweiz T +41 44 446 83 30 | T direkt +41 44 446 83 15 | F +41 44 446 83 38andrea.pfister@mmsag.ch |
http://www.mmsag.ch
75-Jahre-Firmenjubiläum und ein neues Druckzentrum im Jahr 2010 - gute Gründe zum Feiern. Am Freitag, 24. September 2010, öffnen wir um 15 Uhr die Tore zur Open-House-Veranstaltung.
Erfolgreiches erstes Geschäftsberichte-Symposium - Impressionen und Präsentationen.
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
Tue Sep 21 05:17:32 2010
The RT System itself - Status changed from 'resolved' to 'open'
Tue Sep 21 05:19:25 2010
jmcnamara [...] cpan.org - Status changed from 'open' to 'resolved'