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

examples/ including datetime in axis 'max' and 'min' #73

Closed
geeohgeegeeoh opened this Issue Dec 4, 2013 · 12 comments

Comments

2 participants
@geeohgeegeeoh

geeohgeegeeoh commented Dec 4, 2013

Could you write a short example/ .py which shows using 'max' and 'min' with date in charts? I have been trying to get this to work. simple numerical values in y axis seem to work fine btw.

@ghost ghost assigned jmcnamara Dec 4, 2013

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 4, 2013

Owner

The max and min options should work for datetime objects, however, it looks like there is a bug. I'll post a fix in the next few days.

Here is a workaround using an internal function to convert the dates:

from datetime import date
import xlsxwriter

workbook = xlsxwriter.Workbook('chart.xlsx')

date_format = workbook.add_format({'num_format': 'yyyy-mm-dd'})

worksheet = workbook.add_worksheet()
chart = workbook.add_chart({'type': 'scatter', 'subtype': 'straight'})

dates = [date(2013, 11, 1), 
         date(2013, 11, 2), 
         date(2013, 11, 3), 
         date(2013, 11, 4), 
         date(2013, 11, 5), 
         date(2013, 11, 6), 
         date(2013, 11, 7), 
         date(2013, 11, 8), 
         date(2013, 11, 9)]

values = [6, 9, 5, 12, 5, 4, 6, 10, 9]

worksheet.set_column('A:A', 12);
worksheet.write_column('A1', dates, date_format)
worksheet.write_column('B1', values)

chart.add_series({
   'categories': '=Sheet1!$A$1:$A$9',
   'values':     '=Sheet1!$B$1:$B$9',
})

chart.set_x_axis({
        'min': worksheet._convert_date_time(date(2013, 11, 2)),
        'max': worksheet._convert_date_time(date(2013, 11, 8)),
        'num_font': {'rotation': -45}
        })

worksheet.insert_chart('E9', chart)

workbook.close()

screenshot

Note, max and min only work for value axes and not for category axes (that is an Excel feature). From the Chart docs:

Chart Value and Category Axes

A key point when working with Excel charts is to understand how it differentiates between a chart axis that is used for series categories and a chart axis that is used for series values.

In the example above the X axis is the category axis and each of the values is evenly spaced and sequential. The Y axis is the value axis and points are displayed according to their value.

Excel treats the the two types of axis differently and exposes different properties for each.

As such some of the XlsxWriter axis properties can be set for a value axis,some can be set for a category axis and some properties can be set for both.

For example reverse can be set for either category or value axes while the min and max properties can only be set for value axes.

Some charts such as Scatter and Stock have two value axes.

Owner

jmcnamara commented Dec 4, 2013

The max and min options should work for datetime objects, however, it looks like there is a bug. I'll post a fix in the next few days.

Here is a workaround using an internal function to convert the dates:

from datetime import date
import xlsxwriter

workbook = xlsxwriter.Workbook('chart.xlsx')

date_format = workbook.add_format({'num_format': 'yyyy-mm-dd'})

worksheet = workbook.add_worksheet()
chart = workbook.add_chart({'type': 'scatter', 'subtype': 'straight'})

dates = [date(2013, 11, 1), 
         date(2013, 11, 2), 
         date(2013, 11, 3), 
         date(2013, 11, 4), 
         date(2013, 11, 5), 
         date(2013, 11, 6), 
         date(2013, 11, 7), 
         date(2013, 11, 8), 
         date(2013, 11, 9)]

values = [6, 9, 5, 12, 5, 4, 6, 10, 9]

worksheet.set_column('A:A', 12);
worksheet.write_column('A1', dates, date_format)
worksheet.write_column('B1', values)

chart.add_series({
   'categories': '=Sheet1!$A$1:$A$9',
   'values':     '=Sheet1!$B$1:$B$9',
})

chart.set_x_axis({
        'min': worksheet._convert_date_time(date(2013, 11, 2)),
        'max': worksheet._convert_date_time(date(2013, 11, 8)),
        'num_font': {'rotation': -45}
        })

worksheet.insert_chart('E9', chart)

workbook.close()

screenshot

Note, max and min only work for value axes and not for category axes (that is an Excel feature). From the Chart docs:

Chart Value and Category Axes

A key point when working with Excel charts is to understand how it differentiates between a chart axis that is used for series categories and a chart axis that is used for series values.

In the example above the X axis is the category axis and each of the values is evenly spaced and sequential. The Y axis is the value axis and points are displayed according to their value.

Excel treats the the two types of axis differently and exposes different properties for each.

As such some of the XlsxWriter axis properties can be set for a value axis,some can be set for a category axis and some properties can be set for both.

For example reverse can be set for either category or value axes while the min and max properties can only be set for value axes.

Some charts such as Scatter and Stock have two value axes.

@geeohgeegeeoh

This comment has been minimized.

Show comment
Hide comment
@geeohgeegeeoh

geeohgeegeeoh Dec 5, 2013

although your test program worked fine, the same code interpolated into my
python and data failed. It is very probably because I have somehow managed
to make my x axis a category axis not a value axis, but I think I am
invoking use of the date conversion function in a broadly compatible
manner.

sorry to beg debug but can you point out the error?

./mkxls.py < testdata.csv test test.xlsx 2

should make the .xlsx I have appended. the text slant function has worked,
as has the y axis limits but in my OSX excel I see all the data range
across the X axis, not the limits from the code

-G

geeohgeegeeoh commented Dec 5, 2013

although your test program worked fine, the same code interpolated into my
python and data failed. It is very probably because I have somehow managed
to make my x axis a category axis not a value axis, but I think I am
invoking use of the date conversion function in a broadly compatible
manner.

sorry to beg debug but can you point out the error?

./mkxls.py < testdata.csv test test.xlsx 2

should make the .xlsx I have appended. the text slant function has worked,
as has the y axis limits but in my OSX excel I see all the data range
across the X axis, not the limits from the code

-G

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 5, 2013

Owner

although your test program worked fine, the same code interpolated into my
python and data failed. It is very probably because I have somehow managed
to make my x axis a category axis not a value axis,

Only Scatter and Stock have primary (generally X) value axes in Excel. If you are using any other type of chart you won't be able to set the max and min values. You can verify this for yourself in Excel.

Owner

jmcnamara commented Dec 5, 2013

although your test program worked fine, the same code interpolated into my
python and data failed. It is very probably because I have somehow managed
to make my x axis a category axis not a value axis,

Only Scatter and Stock have primary (generally X) value axes in Excel. If you are using any other type of chart you won't be able to set the max and min values. You can verify this for yourself in Excel.

@geeohgeegeeoh

This comment has been minimized.

Show comment
Hide comment
@geeohgeegeeoh

geeohgeegeeoh Dec 5, 2013

I've been able to see the limit manually on these charts fine. Hmmm.

Confused!

geeohgeegeeoh commented Dec 5, 2013

I've been able to see the limit manually on these charts fine. Hmmm.

Confused!

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 5, 2013

Owner

I've been able to see the limit manually on these charts fine

What type of charts are you creating using XlsxWriter?

Owner

jmcnamara commented Dec 5, 2013

I've been able to see the limit manually on these charts fine

What type of charts are you creating using XlsxWriter?

@geeohgeegeeoh

This comment has been minimized.

Show comment
Hide comment
@geeohgeegeeoh

geeohgeegeeoh Dec 5, 2013

I sent you an example dataset, .py and .xls file.

Sorry, noe in handheld and disconnected from my source code. I think its a
line chart

geeohgeegeeoh commented Dec 5, 2013

I sent you an example dataset, .py and .xls file.

Sorry, noe in handheld and disconnected from my source code. I think its a
line chart

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 5, 2013

Owner

I sent you an example dataset, .py and .xls file.

I didn't see those. GitHub strips off attachments if you reply via email. :-(

I think its a line chart

Line charts in Excel don't support max and min on the primary category axis.

See, for example, the following discussion on StackExchange.

Owner

jmcnamara commented Dec 5, 2013

I sent you an example dataset, .py and .xls file.

I didn't see those. GitHub strips off attachments if you reply via email. :-(

I think its a line chart

Line charts in Excel don't support max and min on the primary category axis.

See, for example, the following discussion on StackExchange.

@geeohgeegeeoh

This comment has been minimized.

Show comment
Hide comment
@geeohgeegeeoh

geeohgeegeeoh Dec 5, 2013

here is a screengrab of the pane for my linechart which shows I can edit max/min. the comments on stackexchange are from 2011. is it possible that on OSX, Excel has a feature not on other platforms?

screen shot 2013-12-05 at 9 47 25 pm

geeohgeegeeoh commented Dec 5, 2013

here is a screengrab of the pane for my linechart which shows I can edit max/min. the comments on stackexchange are from 2011. is it possible that on OSX, Excel has a feature not on other platforms?

screen shot 2013-12-05 at 9 47 25 pm

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 5, 2013

Owner

Cool. So I was wrong. It is possible.

Note, if you click the Text option at the top the min and max go away, which is the case that XlsxWriter is defaulting to.

The good news is that support for the "date" case is already in the module so it is just a matter of re-wiring it.

I'll push a fix in the next few days.

Thanks for the input.

Owner

jmcnamara commented Dec 5, 2013

Cool. So I was wrong. It is possible.

Note, if you click the Text option at the top the min and max go away, which is the case that XlsxWriter is defaulting to.

The good news is that support for the "date" case is already in the module so it is just a matter of re-wiring it.

I'll push a fix in the next few days.

Thanks for the input.

jmcnamara added a commit that referenced this issue Dec 31, 2013

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 31, 2013

Owner

Support for Date Axes (and max and min values) has been added to version 0.5.2 of XlsxWriter now on PyPI.

See this example program.

Owner

jmcnamara commented Dec 31, 2013

Support for Date Axes (and max and min values) has been added to version 0.5.2 of XlsxWriter now on PyPI.

See this example program.

@jmcnamara jmcnamara closed this Dec 31, 2013

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 31, 2013

Owner

And P.S., thanks for pointing this out.

Owner

jmcnamara commented Dec 31, 2013

And P.S., thanks for pointing this out.

@geeohgeegeeoh

This comment has been minimized.

Show comment
Hide comment
@geeohgeegeeoh

geeohgeegeeoh Jan 10, 2014

works like a charm. many thanks!!

On Wed, Jan 1, 2014 at 3:02 AM, John McNamara notifications@github.comwrote:

And P.S., thanks for pointing this out.


Reply to this email directly or view it on GitHubhttps://github.com//issues/73#issuecomment-31402152
.

geeohgeegeeoh commented Jan 10, 2014

works like a charm. many thanks!!

On Wed, Jan 1, 2014 at 3:02 AM, John McNamara notifications@github.comwrote:

And P.S., thanks for pointing this out.


Reply to this email directly or view it on GitHubhttps://github.com//issues/73#issuecomment-31402152
.

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