New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Issue with 'total_function' #216

Closed
PetiiMat opened this Issue Jan 27, 2015 · 9 comments

Comments

3 participants
@PetiiMat

I run the 0.6.6 version with Python 2.7.9 on Win7.

I'm trying to get a table with a formula (sum) in the totals row. The formula works fine, however, Excel needs also the value, otherwise it complains when closing the workbook that the data have changed in the meantime (because it recalculates the sum itself). There are also ZEROs displayed in non-Excel applications, e.g. preview in MS Outlook.

Code:

from xlsxwriter import Workbook
workbook = Workbook('sample.xlsx')
data = [['a', 0, 15.2],
        ['b', 15.2, -3.4], 
        ['c', -4, -5]]
worksheet = workbook.add_worksheet()
numfmt = workbook.add_format({'num_format' : '#,##0.00'})
options = { 'data': data, 
            'style' : 'Table Style Light 21', 
            'total_row' : True, 
            'first_column' : True, 
            'columns' : [{'header' : 'Text 1', 'total_string' : 'Totals'}, 
                         {'header' : 'EUR', 'total_function' : 'sum', 'format' : numfmt}, 
                         {'header' : 'USD', 'total_function' : 'sum', 'format' : numfmt}]}
worksheet.add_table(0, 0, 4, 2, options)
workbook.close()
@gigster99

This comment has been minimized.

Show comment
Hide comment
@gigster99

gigster99 Jan 27, 2015

Considered turning off auto calc in excel.

We routinely turn off auto calc on large workbooks to improve performance.

Considered turning off auto calc in excel.

We routinely turn off auto calc on large workbooks to improve performance.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Jan 27, 2015

Owner

Hi @PetiiMat,

That is the documented behaviour and is covered in FAQ:

Q. Why do my formulas show a zero result in some, non-Excel applications?

Due to wide range of possible formulas and interdependencies between them XlsxWriter doesn’t, and realistically cannot, calculate the result of a formula when it is written to an XLSX file. Instead, it stores the value 0 as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened.

This is the method recommended in the Excel documentation and in general it works fine with spreadsheet applications. However, applications that don’t have a facility to calculate formulas, such as Excel Viewer, or several mobile applications, will only display the 0 results.

If required, it is also possible to specify the calculated result of the formula using the optional value parameter in write_formula():

worksheet.write_formula('A1', '=2+2', num_format, 4)

Regards,

John

Owner

jmcnamara commented Jan 27, 2015

Hi @PetiiMat,

That is the documented behaviour and is covered in FAQ:

Q. Why do my formulas show a zero result in some, non-Excel applications?

Due to wide range of possible formulas and interdependencies between them XlsxWriter doesn’t, and realistically cannot, calculate the result of a formula when it is written to an XLSX file. Instead, it stores the value 0 as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened.

This is the method recommended in the Excel documentation and in general it works fine with spreadsheet applications. However, applications that don’t have a facility to calculate formulas, such as Excel Viewer, or several mobile applications, will only display the 0 results.

If required, it is also possible to specify the calculated result of the formula using the optional value parameter in write_formula():

worksheet.write_formula('A1', '=2+2', num_format, 4)

Regards,

John

@jmcnamara jmcnamara added the question label Jan 27, 2015

@PetiiMat

This comment has been minimized.

Show comment
Hide comment
@PetiiMat

PetiiMat Jan 29, 2015

Hi John,
I doubt it is intentional that there is no possibility to conform to the Excel's needs... :-(

Btw, I know about the 'workaround' with 'write_formula', however, this doesn't work for the Worksheet Tables - I had tried it before I wrote to you and it just breaks the table's footer and behaves as a 'normal' cell outside the range of the table.

Would you consider adding the (optional) 'value' sub-property to the 'columns' parameter of the 'add_table' method, similarly as it is in the 'write_formula' function?

Thank you & Regards,
Petr

Hi John,
I doubt it is intentional that there is no possibility to conform to the Excel's needs... :-(

Btw, I know about the 'workaround' with 'write_formula', however, this doesn't work for the Worksheet Tables - I had tried it before I wrote to you and it just breaks the table's footer and behaves as a 'normal' cell outside the range of the table.

Would you consider adding the (optional) 'value' sub-property to the 'columns' parameter of the 'add_table' method, similarly as it is in the 'write_formula' function?

Thank you & Regards,
Petr

@PetiiMat

This comment has been minimized.

Show comment
Hide comment
@PetiiMat

PetiiMat Jan 29, 2015

My issue is not a matter of performance, and turning off the auto calc doesn't solve the issue (it's rather vice versa: you wouldn't get the total figure at all this way). It's just a 6-row-4-column table, nothing special.


Od: gig graham notifications@github.com
Komu: "jmcnamara/XlsxWriter" XlsxWriter@noreply.github.com
Datum: 27.01.2015 12:01
Předmět: Re: [XlsxWriter] Issue with 'total_function' (#216)

Considered turning off auto calc in excel.

We routinely turn off auto calc on large workbooks to improve performance.

From: PetiiMat [mailto:notifications@github.com]
Sent: Tuesday, January 27, 2015 4:57 AM
To: jmcnamara/XlsxWriter
Subject: [XlsxWriter] Issue with 'total_function' (#216)

I run the 0.6.6 version with Python 2.7.9 on Win7.

I'm trying to get a table with a formula (sum) in the totals row. The formula works fine, however, Excel needs also the value, otherwise it complains when closing the workbook that the data have changed in the meantime (because it recalculates the sum itself). There are also ZEROs displayed in non-Excel applications, e.g. preview in MS Outlook.

Code:

from xlsxwriter import Workbook
workbook = Workbook('sample.xlsx')
data = [['a', 0, 15.2],
['b', 15.2, -3.4],
['c', -4, -5]]
worksheet = workbook.add_worksheet()
numfmt = workbook.add_format({'num_format' : '#,##0.00'})
options = { 'data': data,
'style' : 'Table Style Light 21',
'total_row' : True,
'first_column' : True,
'columns' : [{'header' : 'Text 1', 'total_string' : 'Totals'},
{'header' : 'EUR', 'total_function' : 'sum', 'format' : numfmt},
{'header' : 'USD', 'total_function' : 'sum', 'format' : numfmt}]}
worksheet.add_table(0, 0, 4, 2, options)
workbook.close()


Reply to this email directly or view it on GitHub #216 . https://github.com/notifications/beacon/AB9THtOstehbw2mOAThhHcKdYens00_Dks5nl2aXgaJpZM4DXsDY.gif


Reply to this email directly or view it on GitHub:
#216 (comment)

My issue is not a matter of performance, and turning off the auto calc doesn't solve the issue (it's rather vice versa: you wouldn't get the total figure at all this way). It's just a 6-row-4-column table, nothing special.


Od: gig graham notifications@github.com
Komu: "jmcnamara/XlsxWriter" XlsxWriter@noreply.github.com
Datum: 27.01.2015 12:01
Předmět: Re: [XlsxWriter] Issue with 'total_function' (#216)

Considered turning off auto calc in excel.

We routinely turn off auto calc on large workbooks to improve performance.

From: PetiiMat [mailto:notifications@github.com]
Sent: Tuesday, January 27, 2015 4:57 AM
To: jmcnamara/XlsxWriter
Subject: [XlsxWriter] Issue with 'total_function' (#216)

I run the 0.6.6 version with Python 2.7.9 on Win7.

I'm trying to get a table with a formula (sum) in the totals row. The formula works fine, however, Excel needs also the value, otherwise it complains when closing the workbook that the data have changed in the meantime (because it recalculates the sum itself). There are also ZEROs displayed in non-Excel applications, e.g. preview in MS Outlook.

Code:

from xlsxwriter import Workbook
workbook = Workbook('sample.xlsx')
data = [['a', 0, 15.2],
['b', 15.2, -3.4],
['c', -4, -5]]
worksheet = workbook.add_worksheet()
numfmt = workbook.add_format({'num_format' : '#,##0.00'})
options = { 'data': data,
'style' : 'Table Style Light 21',
'total_row' : True,
'first_column' : True,
'columns' : [{'header' : 'Text 1', 'total_string' : 'Totals'},
{'header' : 'EUR', 'total_function' : 'sum', 'format' : numfmt},
{'header' : 'USD', 'total_function' : 'sum', 'format' : numfmt}]}
worksheet.add_table(0, 0, 4, 2, options)
workbook.close()


Reply to this email directly or view it on GitHub #216 . https://github.com/notifications/beacon/AB9THtOstehbw2mOAThhHcKdYens00_Dks5nl2aXgaJpZM4DXsDY.gif


Reply to this email directly or view it on GitHub:
#216 (comment)

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Jan 29, 2015

Owner

Would you consider adding the (optional) 'value' sub-property to the 'columns' parameter of the 'add_table' method, similarly as it is in the 'write_formula' function?

Hi @PetiiMat,

Yes. Not problem.

Until I looked the other day I thought that it was there already.

John

Owner

jmcnamara commented Jan 29, 2015

Would you consider adding the (optional) 'value' sub-property to the 'columns' parameter of the 'add_table' method, similarly as it is in the 'write_formula' function?

Hi @PetiiMat,

Yes. Not problem.

Until I looked the other day I thought that it was there already.

John

@PetiiMat

This comment has been minimized.

Show comment
Hide comment
@PetiiMat

PetiiMat Jan 29, 2015

Brilliant! Looking forward to the new release! ;-)

Thanks,
Petr

Brilliant! Looking forward to the new release! ;-)

Thanks,
Petr

jmcnamara added a commit that referenced this issue Jan 30, 2015

Added option to add function value in worksheet add_table.
Added column funtion_value option to add_table to allow
function value to be set.

Issue #216.
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Jan 30, 2015

Owner

Hi Petr,

I've pushed the changed to master. The is a new column parameter called function_value to pair with function_. You can now do the following:

from xlsxwriter import Workbook

workbook = Workbook('sample.xlsx')
worksheet = workbook.add_worksheet()
numfmt = workbook.add_format({'num_format' : '#,##0.00'})

data = [['a', 0, 15.2],
        ['b', 15.2, -3.4], 
        ['c', -4, -5]]

options = { 'data': data, 
            'style' : 'Table Style Light 21', 
            'total_row' : True, 
            'first_column' : True, 
            'columns' : [{'header' : 'Text 1', 'total_string' : 'Totals'}, 
                         {'header' : 'EUR', 
                          'total_function' : 'sum', 
                          'total_value' : 11.2, 
                          'format' : numfmt}, 
                         {'header' : 'USD', 
                          'total_function' : 'sum', 
                          'total_value' : 6.8, 
                          'format' : numfmt}]}

worksheet.add_table(0, 0, 4, 2, options)
workbook.close()

I've updated the Working with Worksheet Tables docs as well.

I'll push it up to PyPi in a few days.

John

Owner

jmcnamara commented Jan 30, 2015

Hi Petr,

I've pushed the changed to master. The is a new column parameter called function_value to pair with function_. You can now do the following:

from xlsxwriter import Workbook

workbook = Workbook('sample.xlsx')
worksheet = workbook.add_worksheet()
numfmt = workbook.add_format({'num_format' : '#,##0.00'})

data = [['a', 0, 15.2],
        ['b', 15.2, -3.4], 
        ['c', -4, -5]]

options = { 'data': data, 
            'style' : 'Table Style Light 21', 
            'total_row' : True, 
            'first_column' : True, 
            'columns' : [{'header' : 'Text 1', 'total_string' : 'Totals'}, 
                         {'header' : 'EUR', 
                          'total_function' : 'sum', 
                          'total_value' : 11.2, 
                          'format' : numfmt}, 
                         {'header' : 'USD', 
                          'total_function' : 'sum', 
                          'total_value' : 6.8, 
                          'format' : numfmt}]}

worksheet.add_table(0, 0, 4, 2, options)
workbook.close()

I've updated the Working with Worksheet Tables docs as well.

I'll push it up to PyPi in a few days.

John

@jmcnamara jmcnamara self-assigned this Feb 4, 2015

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 2, 2015

Owner

Hi,

Added in version 0.6.7, now on PyPI.

Closing.

John.

Owner

jmcnamara commented Mar 2, 2015

Hi,

Added in version 0.6.7, now on PyPI.

Closing.

John.

@jmcnamara jmcnamara closed this Mar 2, 2015

@PetiiMat

This comment has been minimized.

Show comment
Hide comment
@PetiiMat

PetiiMat Mar 2, 2015

Hi,
Thanks a lot, will try soon. :-)

Petr

PetiiMat commented Mar 2, 2015

Hi,
Thanks a lot, will try soon. :-)

Petr

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment