problem opening .xlsm

[abi navigator]

Hello,
I'm generating an empty .xlsm file using following code:

###
from openpyxl import Workbook

wb = Workbook()
fileName = r'myEmptyFile.xlsm'
ws = wb.create_sheet()
ws.title = 'empty worksheet'

wb.save(filename = fileName)
###

Excel2007 is not able to open myEmptyFile.xlsm file and reports
following:
"Excel cannot open the file 'myEmptyFile.xlsm' because the file format
or file extension is not valid. Verify ..."

If I change the file extension from .xlsm to .xlsx, excel2007 opens it
without any problem.

Other specification:
- openpyxl = 1.5.7
- python = 2.7
- win XP SP3

Does anybody have any idea where is the problem?

Thanks for response,
Abi

[Eric Gazoni]

Hi,
I think you should not give the xlsM extension, because the file will
contain no VBA code.
openpyxl is used to build xlsX files only ;-)

However, LibreOffice does not complain about the file, so it's probably
MS Excel specific.

Try renaming the file and let us know !

Cheers,
Eric

Le 7/02/12 10:01, abi navigator a �crit :

[Aron Kisdi]

Hi,

 

Excel will search for VBA which will not be there if you create the file with openpyxl.

 

However you can edit and save .xlsm files with openpyxl provided you created them (even if it is just an empty file) with Excel. Tested with Office 2010 and openpyxl 1.5.5

 

I hope this helps,

 

Aron

On 7 February 2012 09:09, Eric Gazoni <eric....@gmail.com> wrote:

Hi,
I think you should not give the xlsM extension, because the file will
contain no VBA code.
openpyxl is used to build xlsX files only ;-)

However, LibreOffice does not complain about the file, so it's probably
MS Excel specific.

Try renaming the file and let us know !

Cheers,
Eric

Le 7/02/12 10:01, abi navigator a écrit :

[Christoph M.]

Hi!
I have a similar problem.

openpyxl works fine with .xlsx files. Loading and saving methods both
work and produce perfect results.
But when I try to edit an .xlsm file containing VBA code, I can load
it just like an .xlsx file and manipulate the values of cell values.
The file produced by the save-method however is corrupt. It occupies a
lot less disk memory than the original file, so probably the VBA code
is not transmitted.
I use Office 2007 as well. But I cannot imagine, that is really the
problem here.

I need a possibility to edit .xlsm files online. I first tried
PHPExcel, but then found out, it cannot handle makro containing files.
So I searched for alternatives and stumbles over python openpyxl. It
is said in every description that you can work with .xlsx/.xlsm files
and I almost got it to work now, but again I strand at the .xlsm
files? :(

I would really appreciate if someone has a solution here...

Cheers,
Chris

On 5 Mrz., 07:02, Aron Kisdi <kis...@gmail.com> wrote:
> Hi,
>
> Excel will search for VBA which will not be there if you create the file
> with openpyxl.
>
> However you can edit and save .xlsm files with openpyxl provided you
> created them (even if it is just an empty file) with Excel. Tested with
> Office 2010 and openpyxl 1.5.5
>
> I hope this helps,
>
> Aron
>

[jlgut...@gmail.com]

Aron,

How do you go about performing this?  I have a very similar situation running Windows-7, Python 2.6, and openpyxl 1.6.2.  In this scenario, I am trying to update an Excel-created .xlsm, specifically a cell value.  The code I have running is as follows:

dcm_wb= load_workbook("filepath.xlsm")
dcm_ws = dcm_wb.get_sheet_by_name("ws")
duration_cell = dcm_ws.cell("E14")
duration_cell.value = 4
dcm_wb.save("filepath.xlsm")

where I am trying to load a value of 4 into cell E14 and save the edits.  However, overriding the original "filepath.xlsm" seems to remove the macros in the original.  I have not altered any of the macros themselves.

Any help would be appreciated,
Joseph

[John Bovey]

The amorris fork does support the retention of vba but it has not been
merged into the root yet. If you want to give it a try you will need to
call load_workbook() with the additional parameter keep_vba=true.

John Bovey

[jlgut...@gmail.com]

John,

Is this included in the 1.6.2 install?  I have tried the following:

dcm_wb= load_workbook("filepath.xlsm", keep_vba=True)

dcm_ws = dcm_wb.get_sheet_by_name("ws")
duration_cell = dcm_ws.cell("E14")
duration_cell.value = 4
dcm_wb.save("filepath.xlsm")

and it returned:

"TypeError: load_workbook() got an unexpected keyword argument 'keep_vba'"

I'll apologize upfront, I am green when it comes to coding.

Thanks again,
Joseph

------------------------------------------------------------------------------------------------------------

[Charlie Clark]

Am 27.06.2013, 20:21 Uhr, schrieb <jlgut...@gmail.com>:

> John,
> Is this included in the 1.6.2 install?

No, it's waiting to be integrated into the next release.

https://bitbucket.org/amorris/openpyxl/commits/branch/default

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Adam Morris]

If you have a chance to test, I'm sure both John and myself would appreciate any feedback.

You can download the branch here: https://bitbucket.org/amorris/openpyxl/get/default.zip

--Adam

[John Bovey]

On Thu, 27 Jun 2013 12:25:42 -0700 (PDT)
Adam Morris <w...@myemptybucket.com> wrote:

> If you have a chance to test, I'm sure both John and myself would
> appreciate any feedback.
>
> You can download the branch
> here: https://bitbucket.org/amorris/openpyxl/get/default.zip
>
> --Adam

I have just had a thought on this. My code to preserve included VBA
makes use of the xml.etree.ElementTree.register_namespace()
function which was only introduced in python 2.7. If this is a problem
then let me know and I will try to find a way to manage without that
function.

John

[jlgut...@gmail.com]

John & Adam,

My apologies I was using 2.6 because of an ArcGIS 10 install on my cpu.  After installing Python 2.7.5 and running the new version of openpyxl, I ran the following code:

I ran the following code:
wb= load_workbook("filepath.xlsm", keep_vba=True)
ws = wb.get_sheet_by_name("ws")
ws.cell("E14").value = 4
wb.save("filepath.xlsm")

which generated in the following error

Traceback (most recent call last):
  File "C:\Users\user\My Documents\Aptana Studio 3 Workspace\PyECAT27\userinput.py", line 441, in <module>
    dcm_wb.save("c:\ECAT\DCM\DCM.xlsm")
  File "build\bdist.win-amd64\egg\openpyxl\workbook.py", line 215, in save
  File "build\bdist.win-amd64\egg\openpyxl\writer\excel.py", line 163, in save_workbook
  File "build\bdist.win-amd64\egg\openpyxl\writer\excel.py", line 146, in save
  File "build\bdist.win-amd64\egg\openpyxl\writer\excel.py", line 66, in write_data
  File "build\bdist.win-amd64\egg\openpyxl\writer\workbook.py", line 64, in write_content_types
  File "C:\Python27\lib\zipfile.py", line 931, in read
    return self.open(name, "r", pwd).read()
  File "C:\Python27\lib\zipfile.py", line 976, in open
    zinfo.orig_filename, fname)
zipfile.BadZipfile: File name in directory "[Content_Types].xml" and header "xl/sharedStrings.xml" differ.

I was able to successfully convert most of the vba when I gave the output .xlsm a different name but there were still some macros that were erroneous.

-Joseph

[destr...@gmail.com]

Hi all,

I am facing a similar issue when trying to save an xlsm file.
I am able to open it, edit it and save it without any issue if the file is in xlsx format. However, if the file is in xlsm format, even though I can open and edit it (loading with the keep_vba=True option), I get the error when I try to save it.

This is the code I am using:
from openpyxl import load_workbook
import csv
def update_xlsx(src, dest):
    #Open an xlsx for reading
    wb = load_workbook(filename = dest, keep_vba=True)
    #Get the current Active Sheet
    # ws = wb.get_active_sheet()
    # print ws.title
    #You can also select a particular sheet
    #based on sheet name
    ws = wb.get_sheet_by_name("OnPeak")
    #Open the csv file
    with open(src,'rU') as fin:
        #read the csv
        reader = csv.reader(fin)
        #enumerate the rows, so that you can
        #get the row index for the xlsx
        for index,row in enumerate(reader):
            # if index < 6: continue
            #Asssuming space sepeated,
            #Access the particular cell and assign
            #the value from the csv row
            ws.cell(row=index,column=3).value = row[3]
            ws.cell(row=index,column=4).value = row[4]
    #save the csb file
    wb.save(dest)


and this is the error I get:

---------------------------------------------------------------------------
BadZipfile                                Traceback (most recent call last)
<ipython-input-132-8142f5d8ab79> in <module>()
----> 1 update_xlsx("Jul_2013_Q2.csv", "Jul_2013_Q2.xlsm")

<ipython-input-131-311af13ce2a6> in update_xlsx(src, dest)
     30             ws.cell(row=index,column=4).value = row[4]
     31     #save the csb file
---> 32     wb.save(dest)

/Library/Python/2.7/site-packages/openpyxl/workbook.pyc in save(self, filename)
    230             save_dump(self, filename)
    231         else:
--> 232             save_workbook(self, filename)

/Library/Python/2.7/site-packages/openpyxl/writer/excel.pyc in save_workbook(workbook, filename)
    157     """
    158     writer = ExcelWriter(workbook)
--> 159     writer.save(filename)
    160     return True
    161 

/Library/Python/2.7/site-packages/openpyxl/writer/excel.pyc in save(self, filename)
    140         """Write data into the archive."""
    141         archive = ZipFile(filename, 'w', ZIP_DEFLATED)
--> 142         self.write_data(archive)
    143         archive.close()
    144 

/Library/Python/2.7/site-packages/openpyxl/writer/excel.pyc in write_data(self, archive)
     60         shared_string_table = self._write_string_table(archive)
     61 
---> 62         archive.writestr(ARC_CONTENT_TYPES, write_content_types(self.workbook))
     63         if not self.workbook.vba_archive:
     64             archive.writestr(ARC_ROOT_RELS, write_root_rels(self.workbook))

/Library/Python/2.7/site-packages/openpyxl/writer/workbook.pyc in write_content_types(workbook)
     62     seen = set()
     63     if workbook.vba_archive:
---> 64         root = fromstring(workbook.vba_archive.read(ARC_CONTENT_TYPES))
     65         register_namespace('', 'http://schemas.openxmlformats.org/package/2006/content-types')
     66         for elem in root.findall('{http://schemas.openxmlformats.org/package/2006/content-types}Override'):

/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/zipfile.pyc in read(self, name, pwd)
    929     def read(self, name, pwd=None):
    930         """Return file bytes (as a string) for name."""
--> 931         return self.open(name, "r", pwd).read()
    932 
    933     def open(self, name, mode="r", pwd=None):

/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/zipfile.pyc in open(self, name, mode, pwd)
    974                 raise BadZipfile, \
    975                         'File name in directory "%s" and header "%s" differ.' % (
--> 976                             zinfo.orig_filename, fname)
    977 
    978             # check for encrypted flag & handle password

BadZipfile: File name in directory "[Content_Types].xml" and header "xl/sharedStrings.xml" differ.

[John Bovey]

On Mon, 9 Dec 2013 21:09:43 -0800 (PST)
destr...@gmail.com wrote:

> Hi all,
>
> I am facing a similar issue when trying to save an xlsm file.
> I am able to open it, edit it and save it without any issue if the file is
> in xlsx format. However, if the file is in xlsm format, even though I can
> open and edit it (loading with the keep_vba=True option), I get the error
> when I try to save it.

Hi,

I'll have a look into this but it will not be for a few days. I've not
been anywhere near openpyxl for a few months and it will take me a
while to catch up with all the changes that have been made.

John

[Nick]

I'm having a similar problem when using the keep_vba=True option, however I am receiving an error when saving the workbook, specifically when the worksheets are being written:

  File "C:\Python27\lib\site-packages\openpyxl\workbook.py", line 232, in save

    save_workbook(self, filename)

  File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 159, in save_workbook

    writer.save(filename)

  File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 142, in save

    self.write_data(archive)

  File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 83, in write_data

    self._write_worksheets(archive, shared_string_table, self.style_writer)

  File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 104, in _write_worksheets

    style_writer.get_style_by_hash()))

  File "C:\Python27\lib\site-packages\openpyxl\writer\worksheet.py", line 74, in write_worksheet

    codename = vba_root.find('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetPr').get('codeName', works

heet.title)

AttributeError: 'NoneType' object has no attribute 'get'

The workbook was written and some of the contents can be recovered, but only the worksheet with the macro on it was actually saved. The rest of the worksheets are blank.

using: python 2.7

openpyxl: 1.7

Any help would be appreciated,

Nick

[Charlie Clark]

Am .12.2013, 16:12 Uhr, schrieb Nick <presco...@gmail.com>:

> The workbook was written and some of the contents can be recovered, but
> only the worksheet with the macro on it was actually saved. The rest of
> the
> worksheets are blank.
> using: python 2.7
> openpyxl: 1.7
> Any help would be appreciated,

Is it going to be possible to supply a sample file for this?

Otherwise you may have to do some digging and debugging yourself. openpyxl
didn't support preserving macros before 1.7 so John's work hasn't had a
heap of testing.

[Nick]

The included file has a macro on the first worksheet that counts the different 'status' cells on the other worksheets.

I can reproduce the issue with these steps:

wb = load_workbook(file_path, keep_vba=True)

wb.save("new" +file_path)

-Nick

[John Bovey]

On Tue, 10 Dec 2013 07:31:30 -0800 (PST)
Nick <presco...@gmail.com> wrote:

> The included file has a macro on the first worksheet that counts the
> different 'status' cells on the other worksheets.
> I can reproduce the issue with these steps:
> wb = load_workbook(file_path, keep_vba=True)
> wb.save("new" +file_path)

That is useful, thanks. I should be able to look at it next week.

John

[John Bovey]

I have fixed the bug that was causing this crash, the fixed version is
in https://jbo...@bitbucket.org/jbovey/openpyxl for now.

The saved spreadsheet seems to have working macros but xml complains
that it needs to repair it when it first loads it. Is this a known
problem? The saved workbook zip archive seems to be missing some xml
files that are in the original spreadsheet zip archive, for example the
directory customXml. As far as I know, these are not related to vba but
if that is not correct I can arrange for them to be copied intact to
the saved archive.

John

On Tue, 10 Dec 2013 07:31:30 -0800 (PST)
Nick <presco...@gmail.com> wrote:

[Charlie Clark]

Am .12.2013, 15:22 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> I have fixed the bug that was causing this crash, the fixed version is
> in https://jbo...@bitbucket.org/jbovey/openpyxl for now.

> The saved spreadsheet seems to have working macros but xml complains
> that it needs to repair it when it first loads it. Is this a known
> problem? The saved workbook zip archive seems to be missing some xml
> files that are in the original spreadsheet zip archive, for example the
> directory customXml. As far as I know, these are not related to vba but
> if that is not correct I can arrange for them to be copied intact to
> the saved archive.

As I found out when working on charts, Excel's error messages are worse
than useless. The "Productivity Tool" which John McNamara pointed me to is
really helpful in finding out what Excel doesn't actually like - you can
compare two files with each other and use the validator to find out if
you've generated rubbish.

When using it with openpyxl you need to hack writer/workbook.py so that
the root element has no namespace

This is

NSMAP = {None :
"http://schemas.openxmlformats.org/package/2006/content-types"}
root = Element('{%s}Types' % CONTYPES_NS, nsmap=NSMAP)

In write_content_types() with lxml installed which I recommend for
development, well it's a prerequisite for running the tests.
Unfortunately, I haven't come across the equivalent when using the
standard library's ElementTree but it's on the list.

[John Bovey]

On Wed, 18 Dec 2013 16:06:23 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> As I found out when working on charts, Excel's error messages are worse
> than useless. The "Productivity Tool" which John McNamara pointed me to is
> really helpful in finding out what Excel doesn't actually like - you can
> compare two files with each other and use the validator to find out if
> you've generated rubbish.
>
> When using it with openpyxl you need to hack writer/workbook.py so that
> the root element has no namespace
>
> This is
>
> NSMAP = {None :
> "http://schemas.openxmlformats.org/package/2006/content-types"}
> root = Element('{%s}Types' % CONTYPES_NS, nsmap=NSMAP)
>
> In write_content_types() with lxml installed which I recommend for
> development, well it's a prerequisite for running the tests.
> Unfortunately, I haven't come across the equivalent when using the
> standard library's ElementTree but it's on the list.

I have installed the productivity tool. Do I need to use a different
fork of openpyxl from the 1.8 one I am working with at the moment.
Sorry, I have been away from openpyxl for a few months and I'm a bit
out of touch.

John

[Charlie Clark]

Am .12.2013, 09:57 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> I have installed the productivity tool. Do I need to use a different
> fork of openpyxl from the 1.8 one I am working with at the moment.
> Sorry, I have been away from openpyxl for a few months and I'm a bit
> out of touch.

No, 1.8 is the one to use but you'll the need the hack.

[John Bovey]

On Fri, 20 Dec 2013 11:31:02 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> > I have installed the productivity tool. Do I need to use a different
> > fork of openpyxl from the 1.8 one I am working with at the moment.
> > Sorry, I have been away from openpyxl for a few months and I'm a bit
> > out of touch.
>
> No, 1.8 is the one to use but you'll the need the hack.

Thanks. The code has changed quite a lot since I worked on it 18 months
ago.

John

[Charlie Clark]

Am .12.2013, 12:05 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> Thanks. The code has changed quite a lot since I worked on it 18 months
> ago.

Hopefully for the better…

[John Bovey]

On Fri, 20 Dec 2013 12:17:38 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> > Thanks. The code has changed quite a lot since I worked on it 18 months
> > ago.
>
> Hopefully for the better…

Undoubtably.

The productivity tool is displaying the error message:

"Cannot open the file: An item with the same key has already been
added."

Does every root element need to have no namespace?

John

[Charlie Clark]

Am .12.2013, 12:35 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> Undoubtably.

> The productivity tool is displaying the error message:
> "Cannot open the file: An item with the same key has already been
> added."

Not had that one. You might want to make the file available for
inspection. Double check the archive.

> Does every root element need to have no namespace?

No. The XML we generate is perfectly valid, it's just that the Excel
validator chokes on this particular file if there is a default such as ns0
- the standard for undefined namespaces.

[John Bovey]

On Fri, 20 Dec 2013 12:52:47 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> > The productivity tool is displaying the error message:
> > "Cannot open the file: An item with the same key has already been
> > added."
>
> Not had that one. You might want to make the file available for
> inspection. Double check the archive.

I am checking the archive and I have fixed that one, I had duplicate
entries in Content_Types. Now I am getting: "Specified part does not
exist in the package."

Progress of a kind.

John

[John Bovey]

On Tue, 10 Dec 2013 07:31:30 -0800 (PST)
Nick <presco...@gmail.com> wrote:

> The included file has a macro on the first worksheet that counts the
> different 'status' cells on the other worksheets.
> I can reproduce the issue with these steps:
> wb = load_workbook(file_path, keep_vba=True)
> wb.save("new" +file_path)

I think that this problem is now fixed in the branch at:

https://jbo...@bitbucket.org/jbovey/openpyxl

Is anybody able to test it before I get the changes merged into the
main branch?

John

[Charlie Clark]

Am .12.2013, 16:17 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

>
> Is anybody able to test it before I get the changes merged into the
> main branch?

Feel free to submit a pull request any time. It makes code review easier
and won't stop you working on code.

[Nick]

Tested your changes, and while I no longer get an error when writing the new file, I do get an error when opening the workbook : "Excel found unreadable content...do you want to recover the contents..."

Excel is unable to recover/repair the content and gives me this error:

Replaced Part: /xl/worksheets/sheet1.xml part with XML error.  Undeclared prefix. Line 1, column 29100.

This is using the test workbook that I provided and just loading and saving it. Are you seeing the same behavior?

Nick

[John Bovey]

I thought that I had fixed that. When did you take your copy of the repository?

--
Sent from my Android phone.

[John Bovey]

On Fri, 20 Dec 2013 11:46:55 -0800 (PST)
Nick <presco...@gmail.com> wrote:

> Tested your changes, and while I no longer get an error when writing the
> new file, I do get an error when opening the workbook : "Excel found
> unreadable content...do you want to recover the contents..."
> Excel is unable to recover/repair the content and gives me this error:
> Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Undeclared
> prefix. Line 1, column 29100.
>
> This is using the test workbook that I provided and just loading and saving
> it. Are you seeing the same behavior?

I hope that I have fixed it now. Let me know if anything else is broken.

John

[Charlie Clark]

Am .12.2013, 22:40 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

>
> I hope that I have fixed it now. Let me know if anything else is broken.

Have you run the tests under Python 3 for that?

Install tox and bin/tox

I've only had a look at that commit but I'm not convinced that that code
is the solution.

Could you possibly write a test for it? You can add it to the tox nolxml
configuration to be able to run it easily.

[John Bovey]

On Fri, 20 Dec 2013 22:47:26 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> Am .12.2013, 22:40 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:
>
> >
> > I hope that I have fixed it now. Let me know if anything else is broken.
>
> Have you run the tests under Python 3 for that?

No, I'll try and do it over the Christmas break.

>
> Install tox and bin/tox
>
> I've only had a look at that commit but I'm not convinced that that code
> is the solution.

I agree that it is not very pretty. It might be better to drop trying
to support legacy controls. The hack stops it breaking when there aren't
any. I still need to check that it works when there are
some.

>
> Could you possibly write a test for it? You can add it to the tox nolxml
> configuration to be able to run it easily.

Yes, I will. I'll need to set up a sheet with controls and test that
they survive.

Incidentally, would it be better to have a developer mailing list
rather than inflict all this detail on users?

John

[Charlie Clark]

Am .12.2013, 23:08 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> No, I'll try and do it over the Christmas break.

Any checkout will already contain the right configuration. Just pip
install tox and bin/tox -e py33
Takes the hassle out of setting any additional virtualenv's up.

>> Install tox and bin/tox
>>
>> I've only had a look at that commit but I'm not convinced that that code
>> is the solution.

> I agree that it is not very pretty. It might be better to drop trying
> to support legacy controls. The hack stops it breaking when there aren't
> any. I still need to check that it works when there are
> some.

I'd really need to see what you're doing in greater detail. Serialising
via tostring in anything but test code looks very fishy.

>> Could you possibly write a test for it? You can add it to the tox nolxml
>> configuration to be able to run it easily.

> Yes, I will. I'll need to set up a sheet with controls and test that
> they survive.

We should try and preserve any objects that we can read but I wouldn't
support it otherwise.

> Incidentally, would it be better to have a developer mailing list
> rather than inflict all this detail on users?

I doubt it: the list is low traffic and I think most of the regular
readers are devs. I suspect a lot of users post via the web and only ever
see responses to their question. We have been given an open source account
(Jira, etc.) by Atlassian but neither Eric nor myself have really had time
to look at this in detail.

[John Bovey]

On Fri, 20 Dec 2013 23:20:53 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> I'd really need to see what you're doing in greater detail. Serialising
> via tostring in anything but test code looks very fishy.
>
> >> Could you possibly write a test for it? You can add it to the tox nolxml
> >> configuration to be able to run it easily.
>
> > Yes, I will. I'll need to set up a sheet with controls and test that
> > they survive.
>
> We should try and preserve any objects that we can read but I wouldn't
> support it otherwise.

I agree that the vba / form-controls stuff needs to be better
integrated. Initially I added vba support because I needed
it myself and I wanted to be sure that nothing would break if keep_vba
was false.

John

[Charlie Clark]

Am .12.2013, 00:04 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> I agree that the vba / form-controls stuff needs to be better
> integrated. Initially I added vba support because I needed
> it myself and I wanted to be sure that nothing would break if keep_vba
> was false.

That's how we all get involved, I think. After that it's a slippery slope
and before you know it you're implementing the maths engine!

It's all much appreciated. I just think that it's even more important as
the project grows to have test cases *especially* for apparently small
changes.

[John Bovey]

I agree. I have a bit more time to spend on it now.
John

Charlie Clark <charli...@clark-consulting.eu> wrote:

Am .12.2013, 00:04 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

I agree that the vba / form-controls stuff needs to be better
integrated. Initially I added vba support because I needed
it myself and I wanted to be sure that nothing would break if keep_vba
was false.

That's how we all get involved, I think. After that it's a slippery slope  
and before you know it you're implementing the maths engine!

It's all much appreciated. I just think that it's even more important as  
the project grows to have test cases *especially* for apparently small  
changes.

Charlie

--

[John Bovey]

On Fri, 20 Dec 2013 23:20:53 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> Any checkout will already contain the right configuration. Just pip
> install tox and bin/tox -e py33
> Takes the hassle out of setting any additional virtualenv's up.

Hi Charlie,

I have tox installed and I get a few failed tests with py33 that don't
look as though they are related to anything that I have done, but I'd
like to investigate further to be certain. To do that I need to be able
to run individual tests, perhaps with a bit of additional debugging
code. Do you know if there is a simple way to do that? I could run
'nosetests test-name' but I can't see any way to tell it to use
python3.3.

Incidentally, I am running Debian testing.

John

[Charlie Clark]

Am .12.2013, 12:58 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> Hi Charlie,
> I have tox installed and I get a few failed tests with py33 that don't
> look as though they are related to anything that I have done, but I'd
> like to investigate further to be certain.

1.8 branch has no failures on 2.6, 2.7, 3.2, 3.3

> To do that I need to be able
> to run individual tests, perhaps with a bit of additional debugging
> code. Do you know if there is a simple way to do that?

Two ways:

* setup a your own Python 3.3 virtualenv and then use that. This is what I
do when concentrating on fixing a lot of stuff for a particular version.
Hardly ever do it now that the major stuff is done. There are, of course,
dedicated virtualenvs created by tox in ./tox so you can also use those:
fuchsia-4:openpyxl charlieclark$ cd .tox/py33
fuchsia-4:py33 charlieclark$ bin/py.test -vrf ../../openpyxl

However, because tox does a full install of the package you'll need to
recreate the virtualenv after any change.

* you could create a tox configuration for just those tests you want to
run. Or use -- to be able to pass through -k or -m for relevant tests.

> I could run
> 'nosetests test-name' but I can't see any way to tell it to use
> python3.3.

Don't use nose to run the tests. Quite a few are now dependent upon pytest.

bin/py.test -xrf will give verbose information and stop at the first
failure and also copy & pastable code for running failing tests.

I don't know anything about testing on Debian. I use ports on Mac OS which
happily gives me lots of different Python versions so that I can easily
switch between them virtualenv-3.3; source bin/activate; pip install
openpyxl lxml nose pytest coverage; py.test -xrf

[John Bovey]

On Mon, 23 Dec 2013 15:07:15 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> fuchsia-4:openpyxl charlieclark$ cd .tox/py33
> fuchsia-4:py33 charlieclark$ bin/py.test -vrf ../../openpyxl

When I run tox it does not install py.test in .tox/py33/bin. Is there
anything else I need to do to make this happen?

John

[John Bovey]

On Mon, 23 Dec 2013 15:07:15 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> 1.8 branch has no failures on 2.6, 2.7, 3.2, 3.3

Are you sure? I have just cloned it and I get '12 failed, 356
passed, 1 skipped' on 2.7 and '3 failed, 363 passed, 3 skipped'
on 3.3. That is using test.py.

John

[John Bovey]

I should add, the skipped tests are in test_drawing.py because there is
something wrong with my installed PIL. That does not explain the fails,
though.

John

[Charlie Clark]

Am .12.2013, 22:15 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> Are you sure? I have just cloned it and I get '12 failed, 356
> passed, 1 skipped' on 2.7 and '3 failed, 363 passed, 3 skipped'
> on 3.3. That is using test.py.

Yes, but don't take my word for it:

https://travis-ci.org/ericgazoni/openpyxl

The failures must be related to your configuration. What does bin/pil list
give you?

Several tests are marked to skip if certain conditions exist and a couple
are expected failures.

[John Bovey]

On Mon, 23 Dec 2013 23:47:11 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> Am .12.2013, 22:15 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:
>
> > Are you sure? I have just cloned it and I get '12 failed, 356
> > passed, 1 skipped' on 2.7 and '3 failed, 363 passed, 3 skipped'
> > on 3.3. That is using test.py.
>
> Yes, but don't take my word for it:
>
> https://travis-ci.org/ericgazoni/openpyxl
>
> The failures must be related to your configuration. What does bin/pil list
> give you?
>
> Several tests are marked to skip if certain conditions exist and a couple
> are expected failures.

Charlie,

I have been looking at my failing tests under python 3.3 and, so far,
most of them are tests that compare repr(some-dictionary) to a
fixed string. This is fundamentally broken as the order of items in the
dictionary is arbitrary. There are also tests that fail because xml
that is generated by iterating through a dict is compared to a
fixed string.

Clearly, my changes have caused python to change the order in which it
iterates through keys in unrelated dicts, but the primary
fault is in the structure of the tests rather than in anything I have
done.

I am happy to fix the tests that are failing but I though I should
check first. An example of one of the culprit tests is on line 343 in
test_style.py. I would fix them by replacing 'repr(some-dict)' by
something like 'repr(sorted(some-dict.items())' and also editing the
fixed string it is compared against.

John

[Charlie Clark]

Am .12.2013, 13:06 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> Charlie,
> I have been looking at my failing tests under python 3.3 and, so far,
> most of them are tests that compare repr(some-dictionary) to a
> fixed string. This is fundamentally broken as the order of items in the
> dictionary is arbitrary. There are also tests that fail because xml
> that is generated by iterating through a dict is compared to a
> fixed string.

Yes, I had to fix a few of these in order to be able to release 1.7

> Clearly, my changes have caused python to change the order in which it
> iterates through keys in unrelated dicts, but the primary
> fault is in the structure of the tests rather than in anything I have
> done.

Indeed. I think random ordering of dictionaries in Python 3.3 may even
have been added to break this kind of test.

> I am happy to fix the tests that are failing but I though I should
> check first. An example of one of the culprit tests is on line 343 in
> test_style.py. I would fix them by replacing 'repr(some-dict)' by
> something like 'repr(sorted(some-dict.items())' and also editing the
> fixed string it is compared against.

The failures *should* be unrelated to your work. Are you sure you have a
clean checkout? In general, you should be able to check dictionaries
against each other and the order does not matter, or use OrderedDict when
it does. However, many of the dictionaries are difficult to compare
because the keys are hashable objects where hash(a) == hash(b) but a == b
- the keys are the same but the objects are different so {a:1} != {b:1}.
This should no longer be a problem since I added an __eq__ method to the
base class. Which is why I think you may have some stale code somewhere.

Could you send me a test report?

[John Bovey]

On Sat, 28 Dec 2013 01:11:24 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> Indeed. I think random ordering of dictionaries in Python 3.3 may even
> have been added to break this kind of test.

My guess is that all the dictionaries in a script are parts of the same
large hash table, and changing one changes the internal layout and order
of the others.

>
> > I am happy to fix the tests that are failing but I though I should
> > check first. An example of one of the culprit tests is on line 343 in
> > test_style.py. I would fix them by replacing 'repr(some-dict)' by
> > something like 'repr(sorted(some-dict.items())' and also editing the
> > fixed string it is compared against.
>
> The failures *should* be unrelated to your work. Are you sure you have a
> clean checkout? In general, you should be able to check dictionaries
> against each other and the order does not matter,

I tried that but they all contain other objects like fonts and styles.

> or use OrderedDict when
> it does. However, many of the dictionaries are difficult to compare
> because the keys are hashable objects where hash(a) == hash(b) but a == b
> - the keys are the same but the objects are different so {a:1} != {b:1}.
> This should no longer be a problem since I added an __eq__ method to the
> base class. Which is why I think you may have some stale code somewhere.

I have uploaded my changes (done after my email) so you can have a
look. The main change was to add another helper function canon_repr
that lists dictionaries in canonical, sorted-key order.

John

[Charlie Clark]

Am .12.2013, 09:56 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> I have uploaded my changes (done after my email) so you can have a
> look. The main change was to add another helper function canon_repr
> that lists dictionaries in canonical, sorted-key order.

You are working off the wrong branch.

hg up 1.8 and merge in your changes.

[John Bovey]

On Sat, 28 Dec 2013 12:48:16 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> You are working off the wrong branch.
>
> hg up 1.8 and merge in your changes.

Well that would explain why so much seemed to be broken. The tests that
I worked so hard to fix seem to have just been dumped. Were they not
useful?

John

[John Bovey]

On Sat, 28 Dec 2013 12:48:16 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> You are working off the wrong branch.
>
> hg up 1.8 and merge in your changes.

Done and pushed to bitbucket.

John

[Charlie Clark]

Am .12.2013, 16:45 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> Well that would explain why so much seemed to be broken. The tests that
> I worked so hard to fix seem to have just been dumped. Were they not
> useful?

I don't know without specific examples. I have done some pruning, yes. But
I don't think I got rid of anything relevant or didn't replace it with
something better. You might want to check the changelog for why some tests
were removed or changed. I did solve some issues simply by using
OrderedDicts but also by removing tests that compared string
representations of some datatypes over native comparisons.

See:

https://bitbucket.org/ericgazoni/openpyxl/commits/e5e9018c2ce73690705a5f221aeb9e4be88a0b1d

to

https://bitbucket.org/ericgazoni/openpyxl/commits/bd8eb253c76a7fc9e1ff836cfe7bdfcfa721c420

And other changes around this time. Or what changes are you referring to?
1.8 includes a heap more changes than 1.7 which is why I *did* say to work
from the 1.8 branch.

[Nick]

Alright, I've been testing out the keep_vba fix with the test excel file that I provided before and it is working for that case (awesome!), however, for other workbooks I am now seeing a keyError when the workbook is being saved and it seems to be that one of the styles is not recognized?

Stack trace:

  File "RegressionCount.py", line 181, in process_workbook

    wb.save(new_filename)

  File "build\bdist.win-amd64\egg\openpyxl\workbook.py", line 265, in save

  File "build\bdist.win-amd64\egg\openpyxl\writer\excel.py", line 187, in save_workbook

  File "build\bdist.win-amd64\egg\openpyxl\writer\excel.py", line 170, in save

  File "build\bdist.win-amd64\egg\openpyxl\writer\excel.py", line 98, in write_data

  File "build\bdist.win-amd64\egg\openpyxl\writer\excel.py", line 128, in _write_worksheets

  File "build\bdist.win-amd64\egg\openpyxl\writer\worksheet.py", line 98, in write_worksheet

  File "build\bdist.win-amd64\egg\openpyxl\writer\worksheet.py", line 209, in write_worksheet_cols

KeyError: 549131827

Attached is a workbook that you can use to reproduce this issue.

I can reproduce the issue with these steps:

wb = load_workbook(file_path, keep_vba=True)

wb.save("new" +file_path)

One note, it seems that with the keep_vba=True option, I now cannot save the file with the same file name without getting the error reported above: BadZipfile: File name in directory "[Content_Types].xml" and header "xl/sharedStrings.xml" differ.

Which seems to be related to a bug in Python (http://bugs.python.org/issue6839) . Which is why, as a workaround, I write to a different file name.

Any help with this issue would be greatly appreciated.

-Nick

[Charlie Clark]

Am .01.2014, 00:48 Uhr, schrieb Nick <presco...@gmail.com>:

> KeyError: 549131827
> Attached is a workbook that you can use to reproduce this issue.
> I can reproduce the issue with these steps:
> wb = load_workbook(file_path, keep_vba=True)
> wb.save("new" +file_path)

This seems to be the same as #46
https://bitbucket.org/ericgazoni/openpyxl/issue/46/problem-reading-one-excel-file-and-writing

At the moment I don't know whether this is a long-standing issue, or
something new caused by recent changes. What's the behaviour with 1.6.2?

> One note, it seems that with the keep_vba=True option, I now cannot save
> the file with the same file name without getting the error reported
> above:
> BadZipfile: File name in directory "[Content_Types].xml" and header
> "xl/sharedStrings.xml" differ.
> Which seems to be related to a bug in Python (
> http://bugs.python.org/issue6839) . Which is why, as a workaround, I
> write
> to a different file name.

Thanks for the pointer to the Python bug. If it is really this then we'll
have to wait for it to be fixed upstream.

> Any help with this issue would be greatly appreciated.

[John Bovey]

On the issue of saving the file with the same name when keep_vba=True.
I will look into it, though I think it might be difficult to fix. It is
a side-effect of the fact that openpyxl needs to keep the input file
zip archive open so that binary data like the vba can be copied to the
new archive (I don't think that it is related to the bug-report that
you have linked to). At the very least, though, it should fail more
gracefully if the output file and input file are the same.

For myself, I would certainly want to save to a different file, if just
for safety and to make debugging easier. You can always rename the
files from your own python script.

John

[Nick]

With 1.6.2 I have to remove the keep_vba flag as that is not supported. And without that, the script runs (opens and saves the workbook) but the workbook is not accessible due to the macro (file format or extension is not valid).

[vanvoor...@gmail.com]

Hy,

I am still getting the following error when python is using openpyxl to save my .xlsm file.

It is an excel produced file.

Using:

 Python 2.7.6

 Openpyxl 1.8.4

 Excel For Mac 2011 version 14.0.0

 MacBook Pro OSX 10.9.2

Traceback (most recent call last):

  File "/Users/PeterVanvoorden/Desktop/Herwerkte Python files/main.py", line 36, in <module>

    permutations.determine_lowest_price_permutation()

  File "/Users/PeterVanvoorden/Desktop/Herwerkte Python files/permutations.py", line 169, in determine_lowest_price_permutation

    wb.save(config.Thermopath)

  File "/Library/Python/2.7/site-packages/openpyxl-1.8.4-py2.7.egg/openpyxl/workbook.py", line 265, in save

    save_workbook(self, filename)

  File "/Library/Python/2.7/site-packages/openpyxl-1.8.4-py2.7.egg/openpyxl/writer/excel.py", line 187, in save_workbook

    writer.save(filename)

  File "/Library/Python/2.7/site-packages/openpyxl-1.8.4-py2.7.egg/openpyxl/writer/excel.py", line 170, in save

    self.write_data(archive)

  File "/Library/Python/2.7/site-packages/openpyxl-1.8.4-py2.7.egg/openpyxl/writer/excel.py", line 78, in write_data

    archive.writestr(ARC_CONTENT_TYPES, write_content_types(self.workbook))

  File "/Library/Python/2.7/site-packages/openpyxl-1.8.4-py2.7.egg/openpyxl/writer/workbook.py", line 104, in write_content_types

    root = fromstring(workbook.vba_archive.read(ARC_CONTENT_TYPES))

  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/zipfile.py", line 931, in read

    return self.open(name, "r", pwd).read()

  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/zipfile.py", line 976, in open

    zinfo.orig_filename, fname)

BadZipfile: File name in directory "[Content_Types].xml" and header "xl/sharedStrings.xml" differ.

I'll add the original file before overwriting (Branched...) and the new one (Test).

Can anybody help me?

[John Bovey]

Hi Peter,

Are you saving the file back to the original file that you loaded, or
are the loaded and saved files different? If they are the same, could
you try saving to a different file and see what happens. There is a bug
in version 1.8, fixed in 1.9, that causes a crash if the loaded and
saved files are the same.

John

[vanvoor...@gmail.com]

I saved it as a new file and than I don't get an error anymore until I open the file.

It tells me that some data got lost and asks if I want to open and repair the file. 

When I do it, the file opens but I get following message:

all graphs are missing (sheet output) and 2 sheets are empty (Buffer and Water treatment)...

I'll add the two files and the log file

Test is before writing

Test2 is after writing

Op dinsdag 29 april 2014 20:53:14 UTC+2 schreef John Bovey:

[John Bovey]

On Tue, 29 Apr 2014 12:39:30 -0700 (PDT)
vanvoor...@gmail.com wrote:

> I saved it as a new file and than I don't get an error anymore until I open
> the file.
>
> It tells me that some data got lost and asks if I want to open and repair
> the file.

That sounds like another bug that has been fixed in 1.9. Are you able
to try 1.9? If you can't, or if it still breaks using 1.9, could you
let me have a copy of the python script as well as the initial
spreadsheet. Send it to me directly as an email attachment.

John

[raghuram...@gmail.com]

Hi John,

I have a similar problem, Is this issue solved now ? If so let me know how to proceed.

Regards,

Raghu

[John Bovey]

On Thu, 17 Mar 2016 04:47:49 -0700 (PDT)
raghuram...@gmail.com wrote:

> I have a similar problem, Is this issue solved now ?

Yes, since that was nearly two years ago. Your problem must be a
different one, unless you are using a very old version.

> If so let me know how to proceed.

Post more information, including a script and input spreadsheet that
can be used to reproduce the problem.

John

[John Bovey]

On Thu, 17 Mar 2016 04:47:49 -0700 (PDT)
raghuram...@gmail.com wrote:

> Hi John,
>
> I have a similar problem, Is this issue solved now ?

Yes, since that was nearly two years ago. Your problem must be a

different one unless you are using a very old version.

> If so let me know how to proceed.

Post more information, including a copy of a script and input

[raghuram kalyanam]

Hi John,

Thanks for the reply. I am sharing a xlsx file and my script. In my script I commented out all the actual code and was testing how the openpyxl is saving the xlsx after opening it. I see that the excel opens no more after I open and save the xlsx file. What i actually intend to do with my script is copy a .csv file into one of the worksheet in .xslx file.

It would be really grateful if you could help me with this.

https://www.dropbox.com/sh/o1alwdk6q8srp56/AADXgNf0xcCvLGfEjkqFETO9a?dl=0

Cheers,
Raghu

> --
> You received this message because you are subscribed to a topic in the Google Groups "openpyxl-users" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/openpyxl-users/2PKhuWTm1ME/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to openpyxl-user...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[raghuram kalyanam]

Hi John,

A little more info, the xlsx file I was using has some links to external worksheets which, they are no more there now, so it might ask you to update links. I opened the generated xlsx with text wrangler (as excel could'nt open) I see all the work sheets as xml files.

Cheers,
Raghu

[raghuram kalyanam]

One more update is if I save the file as .xls, I could atleast open the file with excel, although it asks if I trust that file as its in different format.

Cheers,
Raghu

[Charlie Clark]

Am .03.2016, 00:28 Uhr, schrieb raghuram kalyanam
<raghuram...@gmail.com>:

> One more update is if I save the file as .xls, I could atleast open the
> file with excel, although it asks if I trust that file as its in
> different format.

And I think this tells us all we need to know. The file needs to be saved
as ".xlsm" to be readable by Excel when macros are being preserved.

The following works just fine:

import csv
import openpyxl
import os

from openpyxl import load_workbook
wb = load_workbook(filename='bug612-xl.xlsx',keep_vba=True)
ws = wb['A5']

with open("bug612.csv", 'r') as f:
reader = csv.reader(f)
ws.append(next(reader))
for row in reader:
ws.append(float(v) for v in row)

wb.save('bug612-23.xlsm')

@John you might want to look at the subtle differences in the packaging
for the source file.

[John Bovey]

On Fri, 18 Mar 2016 10:27:56 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> Am .03.2016, 00:28 Uhr, schrieb raghuram kalyanam
> <raghuram...@gmail.com>:
>

> [...]

>
> And I think this tells us all we need to know. The file needs to be saved
> as ".xlsm" to be readable by Excel when macros are being preserved.

Hi Raghu,

To expand a bit: if you load a spreadsheet using keep_vba=true then,
when you save it, it will be written out as a spreadsheet with macros,
even if the loaded file did not contain any vba macros. Files with
macros are packaged a bit differently and excel expects them to have
the suffix .xlsm - if I simply rename oas.xlsx to oas.xlsm then excel
opens it without problems.

In your case, I think the answer is just to drop the 'keep_vba=true',
since your input file does not have any vba to keep. If you move to
loading and modifying files that do contain vba then the input and
output files should both end with .xlsm and you should, of course, use
keep_vba=true.

John