Scripting button using openpyxl

[Arun Marathe]

I am using openpyxl to script pivot tables. Because pivot tables are not directly supported, I created a macro that creates a pivot table, and that macro is script-able.
More details can be found in Issue # 295 for those interested.

Now I have put a button upon pressing of which the pivot table macro is run. The issue that I am having is that the button itself seems not scripted by openpyxl.
Any suggestions or workarounds?

Thanks,
Arun

[John Bovey]

Hi,

I was responsible for the VBA scripting support. What you are
describing should work but it is possible that support for scripting
has been broken by the extensive changes to the the package since I
did it. If I am going to fix it the problem I will need to reproduce it.
Could you send me a spreadsheet and python script that demonstrate the
bug.

John Bovey

[Arun Marathe]

Hi John,

A simple bug repro is attached.

"NonScriptableButton.xlsm" contains a macro that creates a pivot table. There is a button there called "Button" upon pressing of which the pivot table is created.

The python file "bug_repro.py" opens "NonScriptableButton.xlsm" workbook, adds a data row at the end of existing rows on Sheet2, redefines the data range, and saves the workbook
under a new name "NonScriptableButtonOut.xlsm".

To run the repro:
-- Put the three files in a directory.
-- Delete the "NonScriptableButtonOut.xlsm" file (it will be created for you). I have just provided a sample.
-- Run "python bug_repro.py".
-- Open the newly created "NonScriptableButtonOut.xlsm" file, and see that the button is absent from it.
-- You can still run the macro (called "Macro1") by hand to verify that the newly added data row is reflected in the calculation of the pivot table rows.

Hope this helps.

Arun

[John Bovey]

Hi Arun

I have started to have a look at this and my initial guess was correct.
Controls in spreadsheets used to be preserved but they aren't any more.
It will take me a day or two to come up with a fix and some tests - I'll
let you know when I have something for you to try.

John

On Fri, 20 Mar 2015 10:05:39 -0700 (PDT)

[Charlie Clark]

Am .03.2015, 11:55 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> I have started to have a look at this and my initial guess was correct.
> Controls in spreadsheets used to be preserved but they aren't any more.
> It will take me a day or two to come up with a fix and some tests - I'll
> let you know when I have something for you to try.

Hiya John,

thanks for looking at this. I can see the <s:controls /> stuff is missing
but can't find any tests that relate to it which is possibly how it got
lost.

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

[John Bovey]

I am happy to share the blame. Would you like me to fix it or would you prefer to do it?

John

On 22 March 2015 16:12:55 GMT+00:00, Charlie Clark <charli...@clark-consulting.eu> wrote:

Am .03.2015, 11:55 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

 I have started to have a look at this and my initial guess was correct.
 Controls in spreadsheets used to be preserved but they aren't any more.
 It will take me a day or two to come up with a fix and some tests - I'll
 let you know when I have something for you to try.

Hiya John,

thanks for looking at this. I can see the <s:controls /> stuff is missing  
but can't find any tests that relate to it which is possibly how it got  
lost.

Charlie

--

Sent from my Android phone.

[Charlie Clark]

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

> I am happy to share the blame. Would you like me to fix it or would you
> prefer to do it?

Well, the macro itself is persisted, though I get a runtime error when I
try to use it.

I'm happy to review your code ;-) Presumably we need to keep the
"{%s}controls" % SHEET_MAIN_NS node and append it?

[John Bovey]

Hi Arun,

I have found the bug and can give you a patch to fix it in the short
term. I'd like to set up a test before I send Charlie a pull request
and get it added into the main repository.

In the file openpyxl/reader/worksheet.py, the method definition for
parse_legacy_drawing should be replaced by

def parse_legacy_drawing(self, element):
self.ws.vba_controls = element.get("{%s}id" % REL_NS)

Let me know if it works, or if you get any other scripting related
problems.

John

On Fri, 20 Mar 2015 10:05:39 -0700 (PDT)

[Charlie Clark]

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

> Hi Arun,
> I have found the bug and can give you a patch to fix it in the short
> term. I'd like to set up a test before I send Charlie a pull request
> and get it added into the main repository.
> In the file openpyxl/reader/worksheet.py, the method definition for
> parse_legacy_drawing should be replaced by
> def parse_legacy_drawing(self, element):
> self.ws.vba_controls = element.get("{%s}id" % REL_NS)

Looks like a good spot. Just needs a test with a sample sheet.xml

[John Bovey]

On Mon, 23 Mar 2015 10:21:31 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> Am .03.2015, 09:50 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:
>
> > Hi Arun,
> > I have found the bug and can give you a patch to fix it in the short
> > term. I'd like to set up a test before I send Charlie a pull request
> > and get it added into the main repository.
> > In the file openpyxl/reader/worksheet.py, the method definition for
> > parse_legacy_drawing should be replaced by
> > def parse_legacy_drawing(self, element):
> > self.ws.vba_controls = element.get("{%s}id" % REL_NS)
>
> Looks like a good spot. Just needs a test with a sample sheet.xml

Charlie,

I have created a test and pushed the changes to bitbucket. Do you know
how I can make a pull request these days? Bitbucket only seems to want
to let me make a pull request back to my own repository.

John

[Charlie Clark]

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

>
> I have created a test and pushed the changes to bitbucket. Do you know
> how I can make a pull request these days? Bitbucket only seems to want
> to let me make a pull request back to my own repository.

I can't see your repository in the list of forks. Did you clone it locally
rather thank forking on Bitbucket?

We can take this off-list if you like.

[John Bovey]

On Mon, 23 Mar 2015 12:24:43 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> Am .03.2015, 12:11 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:
>
> >
> > I have created a test and pushed the changes to bitbucket. Do you know
> > how I can make a pull request these days? Bitbucket only seems to want
> > to let me make a pull request back to my own repository.
>
> I can't see your repository in the list of forks. Did you clone it locally
> rather thank forking on Bitbucket?
>
> We can take this off-list if you like.

Yes, I think that was the mistake I made. I created my repository by
importing yours rather than forking it. I guess that I should create a
new repository by forking yours and push my changes into that. Then I
should be able to generate a pull request.

John

[Charlie Clark]

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

> Yes, I think that was the mistake I made. I created my repository by
> importing yours rather than forking it. I guess that I should create a
> new repository by forking yours and push my changes into that. Then I
> should be able to generate a pull request.

Sounds good. Lack of familiarity breeds bafflement with any of these
services!

Thanks

[John Bovey]

On Mon, 23 Mar 2015 12:29:44 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> > Yes, I think that was the mistake I made. I created my repository by
> > importing yours rather than forking it. I guess that I should create a
> > new repository by forking yours and push my changes into that. Then I
> > should be able to generate a pull request.
>
> Sounds good. Lack of familiarity breeds bafflement with any of these
> services!

Ok, done.

John

[John Bovey]

Charlie,

Things are not as simple as I had hoped. If you open a workbook that
contains controls, but you open it with keep_vba=False, then the
legacyControl tag gets added into the worksheet, but its id relation
points nowhere because the binary drawing/vmlDrawing?.vml file has not
been included. Also the sheet?.xml.rels file will not be correct.

I could just add 'if keep_vba:' to the parse_legacy_drawing method but
I wanted to check that you are ok with that since it is not my code.

John

[Charlie Clark]

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

> Charlie,

> Things are not as simple as I had hoped. If you open a workbook that
> contains controls, but you open it with keep_vba=False, then the
> legacyControl tag gets added into the worksheet, but its id relation
> points nowhere because the binary drawing/vmlDrawing?.vml file has not
> been included.

Are you sure? When I look at the archive of the file created by openpyxl
it does preserve the original file. (You need to run Arun's script with
lxml installed if you want to inspect the files in the MS tool).

Archive: NonScriptableButtonOut.xlsm
Length Date Time Name
--------- ---------- ----- ----
2044 03-20-2015 12:54 [Content_Types].xml
558 03-20-2015 12:54 _rels/.rels
1092 03-20-2015 12:54 xl/_rels/workbook.xml.rels
862 03-20-2015 12:54 docProps/app.xml
669 03-20-2015 12:54 docProps/core.xml
7079 03-20-2015 12:54 xl/theme/theme1.xml
649 03-20-2015 12:54 xl/workbook.xml
757 03-20-2015 12:54 xl/worksheets/_rels/sheet1.xml.rels
14848 03-20-2015 12:54 xl/vbaProject.bin
1595 03-20-2015 12:54 xl/drawings/drawing1.xml
1435 03-20-2015 12:54 xl/drawings/vmlDrawing1.vml
176 03-20-2015 12:54 xl/ctrlProps/ctrlProp1.xml
7984 03-20-2015 12:54 xl/printerSettings/printerSettings1.bin
792 03-20-2015 12:54 xl/worksheets/sheet1.xml
1128 03-20-2015 12:54 xl/worksheets/sheet2.xml
516 03-20-2015 12:54 xl/worksheets/sheet3.xml
186 03-20-2015 12:54 xl/sharedStrings.xml
830 03-20-2015 12:54 xl/styles.xml
--------- -------
43200 18 files

> Also the sheet?.xml.rels file will not be correct.

This also looks fine.

> I could just add 'if keep_vba:' to the parse_legacy_drawing method but
> I wanted to check that you are ok with that since it is not my code.

It's everyone's code. As long as it comes with unit tests there shouldn't
be any real issues.

[John Bovey]

Are you sure that is a file created with keep_vba=False? I get the
following list (output from linux unzip)

inflating: [Content_Types].xml
inflating: _rels/.rels
inflating: xl/_rels/workbook.xml.rels
inflating: docProps/app.xml
inflating: docProps/core.xml
inflating: xl/theme/theme1.xml
inflating: xl/workbook.xml
inflating: xl/worksheets/sheet1.xml
inflating: xl/worksheets/sheet2.xml
inflating: xl/worksheets/sheet3.xml
inflating: xl/sharedStrings.xml
inflating: xl/styles.xml

>
> > Also the sheet?.xml.rels file will not be correct.
>
> This also looks fine.
>
> > I could just add 'if keep_vba:' to the parse_legacy_drawing method but
> > I wanted to check that you are ok with that since it is not my code.
>
> It's everyone's code. As long as it comes with unit tests there shouldn't
> be any real issues.

Ok, thanks.

John

[Charlie Clark]

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

> Are you sure that is a file created with keep_vba=False? I get the
> following list (output from linux unzip)
> inflating: [Content_Types].xml
> inflating: _rels/.rels
> inflating: xl/_rels/workbook.xml.rels
> inflating: docProps/app.xml
> inflating: docProps/core.xml
> inflating: xl/theme/theme1.xml
> inflating: xl/workbook.xml
> inflating: xl/worksheets/sheet1.xml
> inflating: xl/worksheets/sheet2.xml
> inflating: xl/worksheets/sheet3.xml
> inflating: xl/sharedStrings.xml
> inflating: xl/styles.xml

No, with keep_vba=True which as I would expect. With keep_vba=False the
Macro file and the other junk should be disposed of. I know the code for
this works fine because there are tests for it which I broke a couple of
times when refactoring.

[John Bovey]

On Mon, 23 Mar 2015 17:47:04 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> Am .03.2015, 17:42 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:
>
> > Are you sure that is a file created with keep_vba=False? I get the
> > following list (output from linux unzip)
> > inflating: [Content_Types].xml
> > inflating: _rels/.rels
> > inflating: xl/_rels/workbook.xml.rels
> > inflating: docProps/app.xml
> > inflating: docProps/core.xml
> > inflating: xl/theme/theme1.xml
> > inflating: xl/workbook.xml
> > inflating: xl/worksheets/sheet1.xml
> > inflating: xl/worksheets/sheet2.xml
> > inflating: xl/worksheets/sheet3.xml
> > inflating: xl/sharedStrings.xml
> > inflating: xl/styles.xml
>
> No, with keep_vba=True which as I would expect. With keep_vba=False the
> Macro file and the other junk should be disposed of. I know the code for
> this works fine because there are tests for it which I broke a couple of
> times when refactoring.
>
> Charlie

The problem, though, is that the legacyDrawing tag still gets added to
the worksheet even when keep_vba=False.

John

[Charlie Clark]

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

> The problem, though, is that the legacyDrawing tag still gets added to
> the worksheet even when keep_vba=False.

Well, you can add a condition for it. I did try and simplify and
centralise the logic for this so it's possible I removed such a condition.
The important thing is that comments also use the same mechanism so we
probably need to check that we don't throw the baby out with the
bathwater, though I think the comment code ensures that the tag is always
there.

[John Bovey]

On Mon, 23 Mar 2015 18:00:08 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> Well, you can add a condition for it. I did try and simplify and
> centralise the logic for this so it's possible I removed such a condition.
> The important thing is that comments also use the same mechanism so we
> probably need to check that we don't throw the baby out with the
> bathwater, though I think the comment code ensures that the tag is always
> there.

What kind of comments do you mean, so I can generate an example and
make sure it is ok.

John

[Charlie Clark]

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


> What kind of comments do you mean, so I can generate an example and
> make sure it is ok.

Just add a comment to a cell. But I still don't know what you're working
on with regard to the controls. An additional check that keep_vba==True ?

What we really need is a proper modelling of the relationships so that the
drawing will be preserved as a relationship even if the macro is dropped.
But that's going to be a while coming I fear. With drawings the problem is
exacerbated by the fact that VML isn't part of the specification. It's
supposed to have been replaced by DrawingML but I suspect the support for
this is poor for charts. Excel 2013 can apparently generate files based on
the strict version of the spec but I can't find a similar switch in Excel
2015 for Mac preview. In the meantime we'll have to continue treating the
drawings as blobs.

[John Bovey]

On Mon, 23 Mar 2015 19:09:53 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> Am .03.2015, 18:06 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:
>
>
> > What kind of comments do you mean, so I can generate an example and
> > make sure it is ok.
>
> Just add a comment to a cell. But I still don't know what you're working
> on with regard to the controls. An additional check that keep_vba==True ?

At present, if keep_vba is false then no vml files are preserved from
the loaded workbook and so there cannot be any legacyDrawing references
to them in the output worksheet. The easiest way I can see to do that is
to pass the keep_vba parameter through to the worksheet parser and make
the initialisation of vba_controls dependent on whether it is true.

Another solution would be to preserve vml files regardless of
the value of keep_vba. That would would also mean preserving the
entries in the sheet.xml.rels file. At present, I think those files are
preserved intact if keep_vba is true, which would probably break if
openpyxl tried to add any other drawings to the sheet. Another solution
would be to generate an exception if someone tries to load a
spreadsheet containing VBA but don't set keep_vba=True.

I have to take your lead on what is important. When I added the VBA
support I had a particular use-case in which I wanted to create
spreadsheets full of data (exam marks) but needed to have some VBA
functions there too so that people could manipulate the tables of
numbers in different ways. A convenient way to do that is to have a
template with the VBA and then use openpyxl to add the numeric data.
My feeling is, that is probably what most of the (very few?) people who
use openpyxl with VBA would want to do, but I am willing to be
corrected. Is it important to take care of far-fetched scenarios in
which people want to load spreadsheets containing macros, but
discard the macros or add drawings to the worksheets?

>
> What we really need is a proper modelling of the relationships so that the
> drawing will be preserved as a relationship even if the macro is dropped.
> But that's going to be a while coming I fear. With drawings the problem is
> exacerbated by the fact that VML isn't part of the specification. It's
> supposed to have been replaced by DrawingML but I suspect the support for
> this is poor for charts. Excel 2013 can apparently generate files based on
> the strict version of the spec but I can't find a similar switch in Excel
> 2015 for Mac preview. In the meantime we'll have to continue treating the
> drawings as blobs.

I agree entirely.

John

[Arun Marathe]

Hi John,

I can confirm that with your patch, the button is now correctly scripted, and it works.
Please let me know which product version the fix will be included in.

Thanks,
Arun

[Charlie Clark]

Am .03.2015, 20:24 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> At present, if keep_vba is false then no vml files are preserved from
> the loaded workbook and so there cannot be any legacyDrawing references
> to them in the output worksheet. The easiest way I can see to do that is
> to pass the keep_vba parameter through to the worksheet parser and make
> the initialisation of vba_controls dependent on whether it is true.

if self._parent.vba_archive is not None:
… # do what needs to be done.

I'm really trying to reduce the parameters passed around in the reader
code. I wrote the parse_legacy_drawing() method to do something that was
happening in a bunch of code elsewhere that I couldn't quite figure out
but I think I wanted to avoid parsing the worksheet twice. There are some
big worksheets out there where we don't want to load the XML into memory.

> Another solution would be to preserve vml files regardless of
> the value of keep_vba. That would would also mean preserving the
> entries in the sheet.xml.rels file. At present, I think those files are
> preserved intact if keep_vba is true, which would probably break if
> openpyxl tried to add any other drawings to the sheet. Another solution
> would be to generate an exception if someone tries to load a
> spreadsheet containing VBA but don't set keep_vba=True.

Yes, anything depending upon incremental integer keys for relations is
really likely to break. It would have been so easy for Microsoft to
specify either integers or hashes for keys but they left it up to the
individual.

> I have to take your lead on what is important. When I added the VBA
> support I had a particular use-case in which I wanted to create
> spreadsheets full of data (exam marks) but needed to have some VBA
> functions there too so that people could manipulate the tables of
> numbers in different ways. A convenient way to do that is to have a
> template with the VBA and then use openpyxl to add the numeric data.
> My feeling is, that is probably what most of the (very few?) people who
> use openpyxl with VBA would want to do, but I am willing to be
> corrected. Is it important to take care of far-fetched scenarios in
> which people want to load spreadsheets containing macros, but
> discard the macros or add drawings to the worksheets?

No idea. Never use VBA myself and I can't run the macro for some reason,
probably some restriction in Excel for Mac. There are all kinds of edge
cases out there. We have to wait for the bug reports to see whether we can
provide a quick solution or whether it's going to take real work™.

For what it's worth John McNamara's adding the ability to side-load Macros
into xlsxwriter which sounds similar to your use case.:
https://xlsxwriter.readthedocs.org/working_with_macros.html

I want to tackle relationship mapping at some point this year. It would be
nice to have VBA and other relations preserved transparently so that we
could remove the flag.

[John Bovey]

Charlie,

I have spent most of today trying to make keep_vba work alongside
comments. After a lot of time spent cutting and pasting between zip
archives and trying the results in excel, I have discovered some things.

1. My version of excel (2010) will not tolerate having two legacyDrawing
elements in the same sheet. This is a problem if we want to use these
elements with both comments and controls.

2. My excel does not provide a legacyDrawing element for comments
itself, and displays comments perfectly well without it. Presumably it
is needed in some version of excel?

Really, it should not be necessary to use the legacyDrawing vml blob to
display controls at all. As far as I can tell, the information to draw
controls seems to be in the zip archive 3 times: in the vml file, in a
drawing.xml file, and embedded as xml in the worksheet. I used the vml
version because it was simple and was what I got to work first, but I'll
have ago at doing without tomorrow.

If I can use drawing?.xml instead of vmlDrawing?.vml I'll need to treat
that as a blob copied over from the loaded workbook, at least for now.
Is that a problem?

John

[Charlie Clark]

Am .03.2015, 19:59 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> Charlie,

> I have spent most of today trying to make keep_vba work alongside
> comments. After a lot of time spent cutting and pasting between zip
> archives and trying the results in excel, I have discovered some things.

Oh, I thought it was just the unit-tests that were holding you up. I still
need these.

> 1. My version of excel (2010) will not tolerate having two legacyDrawing
> elements in the same sheet. This is a problem if we want to use these
> elements with both comments and controls.

That's what I was thinking might be the case. We have the same problem in
other situations until we fix relations. Things will blow up if someone
has a worksheet with both macros and comments but we'll just have to
document it. As this has always been the case then I guess it's currently
more of a theoretical problem than anything else.

> 2. My excel does not provide a legacyDrawing element for comments
> itself, and displays comments perfectly well without it. Presumably it
> is needed in some version of excel?

Indeed it is. Seems to be the default (and I can't switch it off) for
Excel 2011 and 2015 for Mac. Can you send me one of those files so I can
see if I can open it? VML is a piece of crap that I'd love to be able to
drop and use Serialisable code instead.

> Really, it should not be necessary to use the legacyDrawing vml blob to
> display controls at all. As far as I can tell, the information to draw
> controls seems to be in the zip archive 3 times: in the vml file, in a
> drawing.xml file, and embedded as xml in the worksheet. I used the vml
> version because it was simple and was what I got to work first, but I'll
> have ago at doing without tomorrow.

Belt and braces and a bloody awful spec that was rushed through. I'm
currently in contact with the Working Group on trying to find out what
specification is actually relevant. I'd love to be able to use the Strict

> If I can use drawing?.xml instead of vmlDrawing?.vml I'll need to treat
> that as a blob copied over from the loaded workbook, at least for now.
> Is that a problem?

We'll have to check the compatibility. I'm sure it'll be a problem for
Office 2007 users (and there are still some out there) and it's also a
problem for us. To support both specifications and the various revisions
Microsoft has done then we'll a clever way of managing namespaces. :-/

[John Bovey]

On Tue, 24 Mar 2015 20:16:32 +0100
"Charlie Clark" <charli...@clark-consulting.eu> wrote:

> Am .03.2015, 19:59 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:
>
> > Charlie,
>
> > I have spent most of today trying to make keep_vba work alongside
> > comments. After a lot of time spent cutting and pasting between zip
> > archives and trying the results in excel, I have discovered some things.
>
> Oh, I thought it was just the unit-tests that were holding you up. I still
> need these.

I really could not continue with copying the worksheet relations files
intact from the input to the output - it makes keep_vba incompatible
with any script that adds relations to a worksheet. After I did that
I tried a sheet with controls and comments and it broke, so I needed to
find out why. Now that I know why, I can draw a line under it, do the
unit tests and send you a pull request, if it is urgent.

>
> > 1. My version of excel (2010) will not tolerate having two legacyDrawing
> > elements in the same sheet. This is a problem if we want to use these
> > elements with both comments and controls.
>
> That's what I was thinking might be the case. We have the same problem in
> other situations until we fix relations. Things will blow up if someone
> has a worksheet with both macros and comments but we'll just have to
> document it. As this has always been the case then I guess it's currently
> more of a theoretical problem than anything else.

I don't think this has anything to do with relations. It just seems to
be a restriction built into excel. I guess that without users (at least
for the vba) it is all theoretical but it would be nice to have it
working better.

>
> > 2. My excel does not provide a legacyDrawing element for comments
> > itself, and displays comments perfectly well without it. Presumably it
> > is needed in some version of excel?
>
> Indeed it is. Seems to be the default (and I can't switch it off) for
> Excel 2011 and 2015 for Mac. Can you send me one of those files so I can
> see if I can open it? VML is a piece of crap that I'd love to be able to
> drop and use Serialisable code instead.

I have attached the workbook I have been using for experimenting. It
contains comments, vba and controls but it has not been processed with
openpyxl.

John

[Charlie Clark]

Am .03.2015, 20:55 Uhr, schrieb John Bovey <J.D....@kent.ac.uk>:

> I really could not continue with copying the worksheet relations files
> intact from the input to the output - it makes keep_vba incompatible
> with any script that adds relations to a worksheet. After I did that
> I tried a sheet with controls and comments and it broke, so I needed to
> find out why. Now that I know why, I can draw a line under it, do the
> unit tests and send you a pull request, if it is urgent.

Understood, though here the restriction should not care about comments as
the comment code doesn't.
I'd like to get 2.2.1 out at the beginning of next week with as many bug
fixes as possible.

>> > 1. My version of excel (2010) will not tolerate having two
>> legacyDrawing
>> > elements in the same sheet. This is a problem if we want to use these
>> > elements with both comments and controls.
>>
>> That's what I was thinking might be the case. We have the same problem
>> in
>> other situations until we fix relations. Things will blow up if someone
>> has a worksheet with both macros and comments but we'll just have to
>> document it. As this has always been the case then I guess it's
>> currently
>> more of a theoretical problem than anything else.

> I don't think this has anything to do with relations. It just seems to
> be a restriction built into excel. I guess that without users (at least
> for the vba) it is all theoretical but it would be nice to have it
> working better.

It does because of the way legacyDrawing is used.

>> > 2. My excel does not provide a legacyDrawing element for comments
>> > itself, and displays comments perfectly well without it. Presumably it
>> > is needed in some version of excel?

Yes, if you do a compatibility check it will tell you this. At first I
didn't think I could see the comments - in Excel 2011 they're permanent
post-its, which is presumably what the VML is for. But it works fine with
Excel 2011, OpenOffice, LibreOffice and Numbers.

>> Indeed it is. Seems to be the default (and I can't switch it off) for
>> Excel 2011 and 2015 for Mac. Can you send me one of those files so I can
>> see if I can open it? VML is a piece of crap that I'd love to be able to
>> drop and use Serialisable code instead.

> I have attached the workbook I have been using for experimenting. It
> contains comments, vba and controls but it has not been processed with
> openpyxl.

Thanks very much for this.