64-bit issue?

[LuckyJ]

Hey guys, has anyone tried making spreadsheets in a 64-bit environment
yet? I've encountered a problem. Let me describe:

I am using Office 2007, latest patches.
I am using Spreadsheet::WriteExcel 2.25.
I'm running Perl 5.10.

1) Generate a spreadsheet within a 32-bit Windows XP environment
2) Copy the spreadsheet to a 64-bit Windows 7 environment.
3) When I try to open the spreadsheet in Win7, I get "Excel found
unreadable content in 'Results.xls'. Do you want to recover the
contents of this workbook?"
4) It will open the spreadsheet, but it clobbers some of the
formatting.
5) The Windows XP environment opens the spreadsheet just fine.

So, I went back to the Windows XP 32-bit environment, and did a "Save
As" to Office 2007 xlsx format. I then copied the xlsx to the Windows
7 64-bit environment, and it opened just fine.

Any ideas? I tried enabling compatibility mode, but that did not help.
My initial guess is that the module may be doing something in a non 64-
bit clean fashion...but I really don't know.

[jmcnamara]

On Nov 5, 10:58 pm, LuckyJ <jason.luttg...@gmail.com> wrote:
> Hey guys, has anyone tried making spreadsheets in a 64-bit environment
> yet? I've encountered a problem. Let me describe:

> ...

> 1) Generate a spreadsheet within a 32-bit Windows XP environment
> 2) Copy the spreadsheet to a 64-bit Windows 7 environment.
> 3) When I try to open the spreadsheet in Win7, I get "Excel found
> unreadable content in 'Results.xls'. Do you want to recover the
> contents of this workbook?"

Hi,

There aren't any 64-bit Windows issues that I am aware of.

The "Unreadable Content" error is usually an indication that the file
is corrupted in some way. A common cause of this on Windows is FTPing
a file in non binary mode. Check that the file size on the host system
is the same on the target system after it is transferred.

John.
--

[rpn...@ibksoftware.com]

I run the 2.25 version under windows 2008 server x64 using Strawberry
Perl 5.8.x and it runs just fine, producing excel files that open
without issue...Have you tried the XP compatibility mode in Win 7?

[rmeichhorn]

Hi John,

I have just come across this issue myself.

It has nothing to do with 64 bit. It is a Windows 7 issue.

When I open a file generated by Spreadsheet::Writeexcel on Windows 7
in either Excel 2007 or 2003, I get the "Excel found unreadable
content" error. I can open the same file under XP, Vista or OSX
without a problem.

I did a bit of digging and found people were having the same problem
with PHPExcel.

See -

http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=63779

and

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10059

It appears they have found the problem. Hopefully they can give you a
heads up in fixing it too.

Cheers,
Richard.

On Nov 10, 8:54 pm, jmcnamara <jmcnam...@cpan.org> wrote:
> On Nov 5, 10:58 pm, LuckyJ <jason.luttg...@gmail.com> wrote:
>
> > Hey guys, has anyone tried making spreadsheets in a 64-bit environment
> > yet? I've encountered a problem. Let me describe:
> > ...

> > 1) Generate a spreadsheet within a 32-bitWindowsXP environment

> > 2) Copy the spreadsheet to a 64-bitWindows7environment.
> > 3) When I try to open the spreadsheet in Win7, I get "Excel found
> > unreadable content in 'Results.xls'. Do you want to recover the
> > contents of this workbook?"
>
> Hi,
>

> There aren't any  64-bitWindowsissues that I am aware of.

>
> The "Unreadable Content" error is usually an indication that the file

> is corrupted in some way. A common cause of this onWindowsis FTPing

[jmcnamara]

On Nov 19, 12:40 am, rmeichhorn <rmeichh...@gmail.com> wrote:
> Hi John,
>
> I have just come across this issue myself.
>
> It has nothing to do with 64 bit.  It is a Windows 7 issue.
>

> ...

>
> I did a bit of digging and found people were having the same problem
> with PHPExcel.

Hi,

Excellent. Well done.

From reading through their response and patches it looks like an issue
with their port of OLE::Storage_Lite.

Which means that it will also affect WriteExcel but I should be able
to fix it.

Is it affecting all S::WE programs on Windows 7 or only ones that
create files larger than 7MB or that use the set_properties() method?

Does it, for instance, affect the a_simple.pl program in the examples
directory of the S:WE distro.

John.
--

[rmeichhorn]

Hi John,

I have experimented with some of the examples, and have found that it
is using set_properties which causes the problem under Windows 7.

The sheet generated by the example program properties.pl will
demonstrate it for you.

I tried a_simple.pl, demo.pl and format.pl and they all worked fine.

I went back to my application and commented out the call to
set_properties and it now works.

Just to be sure I also created a spreadsheet of 15Mb (with using
set_properties), and it opened fine.

Cheers,
Richard.

[jmcnamara]

On Nov 19, 7:59 am, rmeichhorn <rmeichh...@gmail.com> wrote:
> Hi John,
>

> I have experimented with some of the examples, and have found that it
> is using set_properties which causes the problem under Windows 7.

Hi Richard,

Thanks, that indicates that the problem occurs when using
OLE::Storage_Lite for files less than 7MB. In general this only
happens in S::WE when set_properties() is in use. (It may be possible
that the error doesn't occur for files over 4k that use set_properties
() but that probably isn't worth testing).

I don't currently have a copy of Windows 7 so I'll need to get one and
install it in a VM. This may take a few days.

In the meantime perhaps you (or someone else reading along at home)
can help me test this by making some changes to OLE::Storage_Lite.pm.
Either, back up your Storage_Lite.pm module or download version 0.18
and make the changes there.

In the Storage_Lite.pm file you will see the following around line 378
(in version 0.18).

375 , pack("V", 0)
376 , pack("V", 0x1000)
377 , pack("V", 0) #Small Block Depot
378 , pack("V", 1)

Just in case it isn't clear the numbers to the left are line numbers
and aren't part of the file. Change line 378 so that it reads as
follows:

375 , pack("V", 0)
376 , pack("V", 0x1000)
377 , pack("V", 0) #Small Block Depot
378 , pack("V", $iSBDcnt)

Install this module and retest properties.pl (in the examples folder).
If there is still a problem change line 377 as well:


375 , pack("V", 0)
376 , pack("V", 0x1000)
377 , pack("V", $iSBDcnt ? 0 : -2)
#Small Block Depot
378 , pack("V", $iSBDcnt)

Retest properties.pl and let me know.

John.
--

[Sanju Shah]

Hi John,

Not a perl savvy but have been using S::WE for a while now. If you have some time, I don't mind doing a webex session with you and providing a Win 7 environment where you could make the changes and test it out. I am in EST zone.

-sanju

John.
--

--
--
You received this message because you are subscribed to the Spreadsheet::WriteExcel Google Group.

For posting and other options visit this group at:
http://groups.google.com/group/spreadsheet-writeexcel?hl=en

You can also post by sending an email to:
http://groups.google.com/group/spreadsheet-writeexcel?hl=en

To unsubscribe send an email to spreadsheet-write...@googlegroups.com

[jmcnamara]

On Nov 19, 2:44 pm, Sanju Shah <sanju.s...@gmail.com> wrote:
> Hi John,
>
> Not a perl savvy but have been using S::WE for a while now. If you have some
> time, I don't mind doing a webex session with you and providing a Win 7
> environment where you could make the changes and test it out. I am in EST
> zone.

Hi,

Thanks for the kind offer but I should be okay. If I run into problems
I may get back to you.

John.
--

[rmeichhorn]

Hi John,

I made the changes to Storage_Lite.pm as you showed, but it still
doesn't work. I tried changing line 378 first and it didn't work, so
I changed 377 as well.

I had a look at the file generated by properties.pl, and the header is
exactly the same. The value of $iSDBcnt must be evaluating to 1,
which would give the same result.

From byte 0000003C : 00 00 00 00 01 00 00 00

To be sure the change I made to Storage_Lite was being picked up, I
modified other bytes in the header, and they were different.

Did you get a different header when you made the changes?

Cheers,
Richard.

[jmcnamara]

On Nov 20, 2:55 am, rmeichhorn <rmeichh...@gmail.com> wrote:
> Hi John,
>

> I made the changes to Storage_Lite.pm as you showed, but it still
> doesn't work.  I tried changing line 378 first and it didn't work, so
> I changed 377 as well.

Hi,

I'll look at it in more detail over the weekend. In the meantime could
you try the following program and let me know if it causes problems:

#!/usr/bin/perl -w


use strict;
use Spreadsheet::WriteExcel::Big;


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

$worksheet->write(0, 0, "Hi Excel!");

__END__


Thanks for you help,

John.
--

[jmcnamara]

On Nov 20, 2:55 am, rmeichhorn <rmeichh...@gmail.com> wrote:
> Hi John,
>

> I made the changes to Storage_Lite.pm as you showed, but it still
> doesn't work.

Hi,

I have a Win7 environment set up now and I can reproduce the bug.

It is in the OLE::Storage_Lite module, which I maintain but didn't
write, and it will probably be tricky to find.

I post any progress in this thread.

John.
--

[jmcnamara]

Hi,

Just to let you know that I think that I found the general problem in
OLE::Storage_Lite and that I am working on it.

It will take 2-3 days to fix and test.

I'll keep you informed in this thread.

John.
--

[jmcnamara]

Hi,

It turns out that the problem was caused by an external patch that I
applied to the OLE::Storage_Lite module shortly after I took over
maintainership. In 2004!!.

It only occurs when creating OLE documents (in this case Excel files)
less than 7MB and only on Windows 7 (32 and 64 bit).
Spreadsheet::WriteExcel uses its own OLE creator for files less than
7MB so this bug has been largely hidden. S::WE only uses
OLE::Storage_Lite for files greater than 7MB or for files less than
7MB when setting document properties via the set_properties() method.

Windows 7 appears to be fussier about the structure of OLE files so it
throws an error when you try to read one of these dodgy
OLE::Storage_Lite files.

Anyway, I have a preliminary fix available on github:
http://github.com/jmcnamara/ole-storage-lite/archives/master

Can anyone on Windows 7 try install the update and let me know if you
encounter any problems.

John.
--

[rmeichhorn]

Hi John,

I have installed the new version of OLE::Storage and it now works.

The properties.xls file generated by properties.pl opens fine, as well
as files generated by my application.

Thanks for the quick response, and all the hard work on S::WE.

Cheers,
Richard.

[jmcnamara]

Hi,

I've released version 0.19 of OLE::Storage_Lite to CPAN. Upgrading to
this version should solve these Windows 7 issues when using
set_properties().

This also fixes similar problems that other applications such as
Gnumeric have with set_properites() WriteExcel files.

http://search.cpan.org/~jmcnamara/OLE-Storage_Lite-0.19/

I will be releasing a version of Spreadsheet::WriteExcel shortly that
makes OLE::Storage_Lite 0.19 a prerequisite. This should eliminate
these issues in future.

John.
--