xlwt generated file is not readable in Excel 2010

[Alan Rotman]

I am using xlutils-1.4.1 and xlwt-0.7.2 to generate Excel files.
The generated files can be read by Excel 2007, but when trying to open
the files in Excel 2010,
I get the error msg:
Protected View Office has deteced a problem with this file.
Editing it may harm your omputer. Click for more details.

If Excel 2010 continues, only 1/2 of the generated file is displayed.
If I open the generated file in Excel 2007 and do "save as", then
Excel 2010 can open this new file without any problems.

Does anyone know what part of the generated .xls file is problematic
for Excel 2010?

thanks,
alan

[John Machin]

On Mon, March 14, 2011 11:03 am, Alan Rotman wrote:
> I am using xlutils-1.4.1 and xlwt-0.7.2 to generate Excel files.
> The generated files can be read by Excel 2007, but when trying to open
> the files in Excel 2010,
> I get the error msg:
> Protected View Office has deteced a problem with this file.
> Editing it may harm your omputer. Click for more details.

When you click, what "more details" are provided?

> If Excel 2010 continues, only 1/2 of the generated file is displayed.

Half of the rows in each sheet? First half? second half? Odd or even
numbered rows? Half of the columns in each sheet? Half of the sheets? Does
the boundary coincide with anything in your data or code?

> If I open the generated file in Excel 2007 and do "save as", then
> Excel 2010 can open this new file without any problems.
>
> Does anyone know what part of the generated .xls file is problematic
> for Excel 2010?

Interesting links found by searching for "Protected View":

(1) http://support.microsoft.com/kb/973736

(2)
http://www.webtlk.com/2010/01/04/how-to-turn-off-protected-view-in-office-2010/
... try enabling only one of the four options at a time and tell us which
one is causing the issue.

(3)
http://blogs.technet.com/b/office2010/archive/2009/07/21/office-2010-application-security.aspx

(4)
http://office.microsoft.com/en-us/excel-help/what-is-file-block-HA010355927.aspx

Looks like it's a blanket inspection of all (or some -- admin configurable).

Can you create a simple (one cell) XLS file (save as XLS from Excel 2003
if you have it else from 2007) and try opening it in Excel 2010?

Anyone else out there with Excel 2010?

Cheers,
John

[John Yeung]

I've got Excel 2010 (at my workplace), which I routinely use to open
xlwt-generated files. I'd be happy to try opening test files and
reporting what I get.

John Y.

[John Machin]

Attached is a ZIP file containing 4 minimal XLS files, created by xlwt,
Excel 2003, OpenOffice.org, and Gnumeric.

Testers should try to open these with Excel 2010 and report the outcome:
(1) no dialogue box at all (2) opened in "protected view" (3) failed
validation (any reasons?) (4) something else ... plus the "Trust Center"
settings that are being used.

[John Yeung]

On Mon, Mar 14, 2011 at 5:53 AM, John Machin <sjma...@lexicon.net> wrote:
>
> Attached is a ZIP file containing 4 minimal XLS files, created by xlwt,
> Excel 2003, OpenOffice.org, and Gnumeric.
>
> Testers should try to open these with Excel 2010 and report the outcome:
> (1) no dialogue box at all (2) opened in "protected view" (3) failed
> validation (any reasons?) (4) something else ... plus the "Trust Center"
> settings that are being used.

All four files opened up with (1) no dialog box at all for me.

There are a lot of Trust Center settings; is there some way to just
download them? The ones that seem most relevant to me are

Protected View:
Enable Protected View for files originating from the Internet (checked)
Enable Protected View for files located in potentially unsafe
locations (checked)

[The fact that the test files were extracted from the Zip to a trusted
location on my hard drive seems to defeat these measures.]

File Block Settings:
Open the following in Protected View: Excel 2, 3, and 4 Macrosheets
and Add-in Files; Excel 2, 3, and 4 Worksheets; Excel 4 Workbooks. No
open or save restrictions for any other file types.

If there are any other settings of interest, please let me know.

To Alan:

Is there any chance you can provide a file (without any sensitive or
illegal-to-transfer data) that is exhibiting the problematic behavior
you are seeing?

John Y.

[Alan Rotman]

In Excel 2010, NO filetypes are selected (with checkmark) as needing
to be open in protected mode.
So I don't know why MS excel is opening the .xls files in protected
mode.
If I then click to continue editing, the first 25-30 rows are
displayed and remaining rows are not displayed.

I created a small test-case which generates Excel files which run fine
in Excel 2007, but
generate a "protected view" warning in Excel 2010.
How do I attach these test case xls to this post?

thanks,
alan

>  minimal-xls-files.zip
> 6KViewDownload

[John Machin]

On Fri, March 18, 2011 10:41 am, Alan Rotman wrote:
> In Excel 2010, NO filetypes are selected (with checkmark) as needing
> to be open in protected mode.
> So I don't know why MS excel is opening the .xls files in protected
> mode.

Are you opening them from a folder that's not trusted e.g. "Downloads"?

> If I then click to continue editing, the first 25-30 rows are
> displayed and remaining rows are not displayed.
>
> I created a small test-case which generates Excel files which run fine
> in Excel 2007, but
> generate a "protected view" warning in Excel 2010.
> How do I attach these test case xls to this post?

Send an email to python...@googlegroups.com with the files as attachments.

Cheers,
John

[John Machin]

On Mar 18, 10:41 am, Alan Rotman <alan.rot...@gmail.com> wrote:
> In Excel 2010, NO filetypes are selected (with checkmark) as needing
> to be open in protected mode.
> So I don't know why MS excel is opening the .xls files in protected
> mode.
> If I then click to continue editing, the first 25-30 rows are
> displayed and remaining rows are not displayed.
>
> I created a small test-case which generates Excel files which run fine
> in Excel 2007, but
> generate a "protected view" warning in Excel 2010.

The files which you sent me via private e-mail have a bitmap image in
the top left. This is a pre-Excel 8.0 feature which is tolerated by
Excel 8.0 to 11.0 (i.e Excel 95 - 2003) and Excel 12.0 (i.e. Excel
2007). Perhaps its luck has run out in Excel 2010. Try losing the
image.

Aside: you have a large number of font and XF records in your files
relative to the number of cells. Tends to indicate that you are
creating XF (aka style) records on the fly inside your write-rows
loop. Not a good idea if your files grow bigger. Max 400 (approx) font
records per file. Your "roadmap" file has 51 already. Better to create
the XFs outside the loop and re-use them. Note that I'm presuming that
many of them are duplicates. Before you start re-writing your code,
see what you get by starting with

book = xlwt.Workbook(style_compression=2)

See page 35 of the python-excel tutorial.

How to check how many fonts etc: page 20 of ditto,

python runxlrd.py -f1 hdr yourfile.xls

Cheers,
John

[Alan Rotman]

Thanks. You correctly identified the problem for protected mode as due
to the bitmaps.
I removed the bitmaps, and there is no "protected mode" warning and
the file is correctly displayed in its entirety.
I can add a bitmap manually in any version of excel, and then there
are no warnings from within Excel.
So, how do I correctly insert images into excel-spreadsheet with xlwt
(i.e. no warnings)?

Regarding XF records, I do define all of them at the top of the file
and then reuse them.
There are just lots of styles. Going forward there should not be many
more styles.
Is this okay as it is?

alan

[John Yeung]

On Mon, Mar 21, 2011 at 6:12 PM, Alan Rotman <alan....@gmail.com> wrote:
> So, how do I correctly insert images into
> excel-spreadsheet with xlwt (i.e. no warnings)?

Apparently, not with the insert_bitmap() method mentioned in the
tutorial. I've just tried it, with the same results you describe.

John Y.

[John Machin]

On Mar 22, 9:12 am, Alan Rotman <alan.rot...@gmail.com> wrote:
> Thanks. You correctly identified the problem for protected mode as due
> to the bitmaps.
> I removed the bitmaps, and there is no "protected mode" warning and
> the file is correctly displayed in its entirety.
> I can add a bitmap manually in any version of excel, and then there
> are no warnings from within Excel.
> So, how do I correctly insert images into excel-spreadsheet with xlwt
> (i.e. no warnings)?

You can't. Doing something about this problem is way down the priority
list.

[Alan Rotman]

I checked Microsoft's website, and they state that bmp's are still
supported.
So why did you write that BMPs are "pre-Excel 8.0" and supported only
upto Excel 12.0.
Also, why is there an error for xlwt generated bitmaps, but no error
for manually added BMPs?
See
http://technet.microsoft.com/en-us/library/dd797428.aspx

File formats supported in Office 2010

Microsoft Office Clipboard file formats
You can paste data from the Microsoft Office Clipboard into Word 2010,
Excel 2010, and PowerPoint 2010 if the Office Clipboard data is in one
of the formats shown in the following table.
File format Extension
---------------- ---------------
Picture .wmf or .emf
Bitmap .bmp

[John Machin]

On Mon, April 4, 2011 9:04 am, Alan Rotman wrote:
> I checked Microsoft's website, and they state that bmp's are still
> supported.
> So why did you write that BMPs are "pre-Excel 8.0" and supported only
> upto Excel 12.0.
> Also, why is there an error for xlwt generated bitmaps, but no error
> for manually added BMPs?

The record type (IMDATA (0x7F)) used by xlwt, pyExcelerator, pyXLWriter,
and some perl package (see the genealogy at the start of xlwt/Bitmap.py)
was generated by versions of Excel earlier than 8.0. The world changed in
8.0. Instead of an OBJECT record and a (huge) IMDATA record at the end of
the relevant worksheet, you get a somewhat-smaller MSO_DRAWING_GROUP
(0xEB) in the workbook globals, and the worksheet has an MSO_DRAWING
(0xEC) followed by an OBJECT record.

Excel 97-2003, Excel 2007 and Open Office 3.x Calc will accept and display
the pre-8.0 format. If you save the file, they will write the 8.0-style
format. If you add a bitmap via the UI, they will add it in 8.0+ format.

[Max]

I realise it's not much use for people who want to create spreadsheets in Python but the Perl module Spreadsheet::WriteExcel can write spreadsheets with bitmaps in without triggering this warning. I've just converted a Perl script to Python and came across this error. It's the first of many scripts I've converted but the first with a bitmap in. If it would be useful I could create a tiny spreadsheet with a bitmap in from Perl.