openpyxl 2.4.0

[Paul Symonds]

Charlie,

Good afternoon, thank you for the assistance in installing openpyxl 2.4.0.  I am now in a position to test features of this with my code base that works on the current release.

My environment for testing is as follows Anaconda 4.0.0 (do you recommend anything else).

#

et-xmlfile                1.0.1                     <pip>

jdcal                     1.2                       <pip>

mkl                       11.3.1                        0    defaults

numpy                     1.11.0                   py35_0    defaults

openpyxl (c:\users\xxxxxx\openpyxl) 2.4.0                     <pip>

pandas                    0.18.0              np111py35_0    defaults

pip                       8.1.1                    py35_1    defaults

python                    3.5.1                         4    defaults

python-dateutil           2.5.2                    py35_0    defaults

pytz                      2016.3                   py35_0    defaults

setuptools                20.3                     py35_0    defaults

six                       1.10.0                   py35_0    defaults

vs2015_runtime            14.00.23026.0                 0    defaults

wheel                     0.29.0                   py35_0    defaults

My code opens a template with the print area configure for 3 sheets along with many named references and then populates many other sheets then save as.

any tips on how to debug this or what may be needed to fix.  I am happy to invest time in helping where I can and within my ability and as my program progresses I can test the releases against my code.

When running my code I am getting the following error:

Traceback (most recent call last):

  File "of_main.py", line 30, in <module>

    main(sys.argv[1:])

  File "of_main.py", line 26, in main

    x = ofr.OF(cob_date, environment)

  File "D:\svn\of_main\of_reporting.py", line 46, in __init_

_

    self.xlwriter = self.writer()

  File "D:\svn\of_main\of_reporting.py", line 96, in writer

    , self.env.REPORT_TEMPLATE))

  File "c:\users\xxxx\openpyxl\openpyxl\reader\excel.py", line 240, in load_

workbook

    parser.assign_names()

  File "c:\users\xxxx\openpyxl\openpyxl\packaging\workbook.py", line 80, in

assign_names

    sheet.print_area = _unpack_print_area(defn)

  File "c:\users\xxxx\openpyxl\openpyxl\workbook\defined_name.py", line 56,

in _unpack_print_area

    return m.group("cells")

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

regards

Paul

[Charlie Clark]

Am .04.2016, 17:16 Uhr, schrieb Paul Symonds <paul.j....@gmail.com>:

> Charlie,
>
> Good afternoon, thank you for the assistance in installing openpyxl
> 2.4.0.

> I am now in a position to test features of this with my code base that
> works on the current release.

Good stuff.

> My environment for testing is as follows Anaconda 4.0.0 (do you recommend
> anything else).

> #
> et-xmlfile 1.0.1 <pip>
> jdcal 1.2 <pip>
> mkl 11.3.1 0 defaults
> numpy 1.11.0 py35_0 defaults
> openpyxl (c:\users\xxxxxx\openpyxl) 2.4.0 <pip>
> pandas 0.18.0 np111py35_0 defaults
> pip 8.1.1 py35_1 defaults
> python 3.5.1 4 defaults
> python-dateutil 2.5.2 py35_0 defaults
> pytz 2016.3 py35_0 defaults
> setuptools 20.3 py35_0 defaults
> six 1.10.0 py35_0 defaults
> vs2015_runtime 14.00.23026.0 0 defaults
> wheel 0.29.0 py35_0 defaults

No, that's fine.

> My code opens a template with the print area configure for 3 sheets along
> with many named references and then populates many other sheets then save
> as.
>
> any tips on how to debug this or what may be needed to fix. I am happy
> to invest time in helping where I can and within my ability and as my
> program progresses I can test the releases against my code.
>

> *When running my code I am getting the following error:*

>
> Traceback (most recent call last):
> File "of_main.py", line 30, in <module>
> main(sys.argv[1:])
> File "of_main.py", line 26, in main
> x = ofr.OF(cob_date, environment)
> File "D:\svn\of_main\of_reporting.py", line 46, in __init_
> _
> self.xlwriter = self.writer()
> File "D:\svn\of_main\of_reporting.py", line 96, in writer
> , self.env.REPORT_TEMPLATE))
> File "c:\users\xxxx\openpyxl\openpyxl\reader\excel.py", line 240, in
> load_
> workbook
> parser.assign_names()
> File "c:\users\xxxx\openpyxl\openpyxl\packaging\workbook.py", line 80,
> in
> assign_names
> sheet.print_area = _unpack_print_area(defn)
> File "c:\users\xxxx\openpyxl\openpyxl\workbook\defined_name.py", line
> 56,
> in _unpack_print_area
> return m.group("cells")
> AttributeError: 'NoneType' object has no attribute 'group'

hm, lemme guess: the print area is not a range of cells but another
defined name?
/me impersonates Father Jack

Can you run this in something that will let you debug it and let me know
what defn.value is? Also, if possible send me the file (directly or some
other means if it has sensitive information).

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

[Paul Symonds]

Charlie

I'll try and send something tomorrow file is sensitive so will need to recreate a test file.

The print area in the template.xls is that as defined by page setup and select the cells, set print area, then select more and add print area.

Do you just need the template ?

Regards

Paul Symonds

> --
> You received this message because you are subscribed to a topic in the Google Groups "python-excel" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/python-excel/7vjtnpCbX0o/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to python-excel...@googlegroups.com.
> To post to this group, send an email to python...@googlegroups.com.
> Visit this group at https://groups.google.com/group/python-excel.
> For more options, visit https://groups.google.com/d/optout.

[Charlie Clark]

Am .04.2016, 17:48 Uhr, schrieb Paul Symonds <paul.j....@gmail.com>:

> Charlie

> I'll try and send something tomorrow file is sensitive so will need to
> recreate a test file.

> The print area in the template.xls is that as defined by page setup and
> select the cells, set print area, then select more and add print area.

What in Excel? Can you check what this looks in the names manager.
Screenshot of what I have attached, yours should be similar just not in
German.

> Do you just need the template ?

Probably, assuming you can recreate the error just opening the template
file.

[Charlie Clark]

Am .04.2016, 17:57 Uhr, schrieb Charlie Clark
<charli...@clark-consulting.eu>:

> What in Excel? Can you check what this looks in the names manager.
> Screenshot of what I have attached, yours should be similar just not in
> German.

Is this what you have: A1:O64,A68:O97? based on the stackoverflow thread

Also
"Is it also possible after the print area and headings are set for each
sheet to export to PDF?"

No, because openpyxl really just cares about the XML. But you can easily
do this using LibreOffice in headless mode.

[Paul Symonds]

Yes

Regards

Paul Symonds

Tel: +41 76 350 92 61

Sent from my iPhone

[Paul Symonds]

Charlie

Within the Name Manager there are 3 X Print_Area 1 for each sheet

=Sheet2!$A$1:$O$64,Sheet2!$A$68:$O$97

='Sheet1'!$A$1:$O$64,'Sheet1'!$A$68:$O$97

=Sheet3!$A$1:$O$64,Sheet3!$A$68:$O$97

I need to work on sending the sheet over but that will take a little time as I need to create a dummy file.

What is odd there are single quotes around the first but not others.

Regards

Paul Symonds

> On 5 Apr 2016, at 18:09, Charlie Clark <charli...@clark-consulting.eu> wrote:
>

[Charlie Clark]

Am .04.2016, 13:29 Uhr, schrieb Paul Symonds <paul.j....@gmail.com>:

> Charlie

> Within the Name Manager there are 3 X Print_Area 1 for each sheet
=Sheet2!$A$1:$O$64,Sheet2!$A$68:$O$97
='Sheet1'!$A$1:$O$64,'Sheet1'!$A$68:$O$97
=Sheet3!$A$1:$O$64,Sheet3!$A$68:$O$97

Thanks. How are you creating the areas in Excel? Oh, I see: you can append
different ranges by holding the command key.

> I need to work on sending the sheet over but that will take a little
> time as I need to create a dummy file.

That might not be necessary if I can recreate the error.

> What is odd there are single quotes around the first but not others.

I wouldn't worry about that, sheet name quoting is optional.

[Paul Symonds]

Charlie

I'm using Office 2013 I set via page layout , page setup , print area. With this you select area set then select another area and there is option to Add to Print Area.

Regards

Paul Symonds

[Charlie Clark]

Am .04.2016, 13:48 Uhr, schrieb Paul Symonds <paul.j....@gmail.com>:

>
> I'm using Office 2013 I set via page layout , page setup , print area.
> With this you select area set then select another area and there is
> option to Add to Print Area.

No such option in Office 2011 for Mac but I managed to find a way. Except
that I don't get the exception.

Can you possibly unzip an Excel file and send me the workbook.xml file?
(Check it to see if it has any sensitive data but this one shouldn't)

I actually just need the definedNames bit which should a bit like this:

<definedNames>
<definedName name="_xlnm.Print_Area"
localSheetId="0">Blatt1!$A$1:$F$14,Blatt1!$H$10:$I$17,Blatt1!$I$16:$K$25,Blatt1!$C$15:$G$30,Blatt1!$D$10:$E$18</definedName>
</definedNames>

[Paul Symonds]

Charlie

 

<definedName name="_xlnm.Print_Area" localSheetId="0">'Sheet One'!$A$1:$O$64,'Sheet One'!$A$68:$O$97</definedName>

<definedName name="_xlnm.Print_Area" localSheetId="1">Sheet2!$A$1:$O$64,Sheet2!$A$68:$O$97</definedName>

<definedName name="_xlnm.Print_Area" localSheetId="2">Sheet3!$A$1:$O$64,Sheet3!$A$68:$O$97</definedName>

 

Note I have many other named ranges defined for various cells.

Paul

[Charlie Clark]

Am .04.2016, 14:18 Uhr, schrieb Paul Symonds <paul.j....@gmail.com>:


> <definedName name="_xlnm.Print_Area" localSheetId="0">'Sheet
> One'!$A$1:$O$64,'Sheet One'!$A$68:$O$97</definedName>
> <definedName name="_xlnm.Print_Area"
> localSheetId="1">Sheet2!$A$1:$O$64,Sheet2!$A$68:$O$97</definedName>
> <definedName name="_xlnm.Print_Area"
> localSheetId="2">Sheet3!$A$1:$O$64,Sheet3!$A$68:$O$97</definedName>

> Note I have many other named ranges defined for various cells.

Sure. BTW. it does look like the quoting is what's causing the problem. If
you delete the print area for that sheet the openpyxl should be able to
process. It'll have reduced the print area to a single range but that's a
different matter.

[Paul Symonds]

Charlie 

The single quotes are there because that is what Excel does when there is a space in the sheet name.

Note that earlier versions of  openpyxl are fine although I assume they don't have this feature.

I have the unfortunate misfortune to be reliant totally on Windows 7 which does not help.

Paul

[Charlie Clark]

Am .04.2016, 14:25 Uhr, schrieb Paul Symonds <paul.j....@gmail.com>:

> Charlie
>
> The single quotes are there because that is what Excel does when there
> is a space in the sheet name.

I know that. It's just that that is what's causing the regex to fail. Easy
enough to work round.

> Note that earlier versions of openpyxl are fine although I assume they
> don't have this feature.

Correct, but they do come with nice, big and friendly warnings telling you
this.

> I have the unfortunate misfortune to be reliant totally on Windows 7
> which does not help.

You should be able to work fine with openpyxl on Windows. The only hurdle
is getting binaries for lxml.

[Charlie Clark]

Am .04.2016, 15:11 Uhr, schrieb Charlie Clark
<charli...@clark-consulting.eu>:

> I know that. It's just that that is what's causing the regex to fail.
> Easy enough to work round.

Okay, I've just pushed a fix to the 2.4 branch that should solve this.

To test it: hg pull && hg up then run your code.

[John Yeung]

On Wed, Apr 6, 2016 at 9:11 AM, Charlie Clark
<charli...@clark-consulting.eu> wrote:

> You should be able to work fine with openpyxl on Windows. The only hurdle is
> getting binaries for lxml.

There are Windows lxml installers for some versions of Python. For
other versions, there are "unofficial" binaries, and if *that* fails,
openpyxl still has the pure-Python et_xmlfile to fall back on.

John Y.

[Charlie Clark]

Am .04.2016, 15:57 Uhr, schrieb John Yeung <gallium....@gmail.com>:

> There are Windows lxml installers for some versions of Python. For
> other versions, there are "unofficial" binaries, and if *that* fails,
> openpyxl still has the pure-Python et_xmlfile to fall back on.

Yes, but lxml is needed for development and for running tests. The docs
contain instructions on how to get and install the relevant binaries in
order for tox to be able to run.

But nice of you to notice that we also have et_xmlfile as a fallback. :-)

[Paul Symonds]

Charlie

Code now runs to completion without error but whilst I can see the print area in named ranges, it loses it from the page setup sheet view in print area.

Also the print options are not carried over such a color mode etc.

Should they be?

Regards

Paul Symonds

Tel: +41 76 350 92 61

Sent from my iPhone

[Paul Symonds]

lxml is included in anaconda 4.0.0 which I highly recommend.

Regards

Paul Symonds

Sent from my iPhone

[Charlie Clark]

Am .04.2016, 16:17 Uhr, schrieb Paul Symonds <paul.j....@gmail.com>:

> Charlie

> Code now runs to completion without error but whilst I can see the print
> area in named ranges, it loses it from the page setup sheet view in
> print area.

I'll have to investigate that further.

> Also the print options are not carried over such a color mode etc.

Those are almost certainly kept in some of the binary blobs that we don't
preserve. Have to a file to work with to be sure.

[Paul Symonds]

Charlie,

I will endeavor to find time to produce a simple one with some code that replicates what I am doing.  I will get back to you.

In the meantime can you list what you use on your Mac for your development and testing, I can create the same environment on mine.

Thanks.

Paul.

[Charlie Clark]

Am .04.2016, 16:25 Uhr, schrieb Paul Symonds <paul.j....@gmail.com>:

>
> I will endeavor to find time to produce a simple one with some code that
> replicates what I am doing. I will get back to you.

Ta.

> In the meantime can you list what you use on your Mac for your
> development and testing, I can create the same environment on mine.

Pretty standard, really.

I use MacPorts to manage my Python versions: 2.6, 2.7, 3.3, 3.4, 3.5, pypy
SourceTree as my main Mercurial client
ITerm2 as a Terminal
WingIDE as my main editor, with TextMate for playing with XML because of
the builtin support for tidy. Though on larger files you have to use tidy
on the command line.