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

Excel 2013 chart style #192

Closed
Dragon2fly opened this Issue Dec 11, 2014 · 7 comments

Comments

3 participants
@Dragon2fly

I am using XlsxWriter to draw a pie chart in Excel with style.

The document said that

"The set_style() method is used to set the style of the chart to one of the 48 built-in styles available on the ‘Design’ tab in Excel"

So I tried the flowing code:

def xlsx_maker(file_path, in_data):
    data = [[str(gNo[0]) for gNo in in_data], [memNo[1] for memNo in in_data]]
    workbook = xlsxwriter.Workbook(file_path)
    worksheet = workbook.add_worksheet()
    bold = workbook.add_format({'bold': 1})
    headings = ['Group No', 'Num of member']
    worksheet.write_row('A1', headings, bold)
    worksheet.write_column('A2', data[0])  # write group name to column A
    worksheet.write_column('B2', data[1])  # write group's number of member to column B

    chart_pie = []
    for i in range(1, 49):
         chart_pie.append(workbook.add_chart({'type': 'pie'}))
         chart_pie[i-1].add_series({
             'name': 'Pie of trajectory',
             'categories': ['Sheet1', 1, 0, len(data[0]), 0],  # data labels
             'values': ['Sheet1', 1, 1, len(data[1]), 1],  # data values
             'data_labels': {'value': True,  # show value inside graph
                             'percentage': True,
                             'separator': '\n',
                             'position': 'inside_end'},
             })
         chart_pie[i-1].set_style(i)
         pos = 'C' + str(20 * (i+1))
         worksheet.insert_chart(pos, chart_pie[i-1], {'x_offset': 25, 'y_offset': 10})

48_charts
48 charts has different color set only. And from chart 42th, all 6 charts are the same. In your source code, I found in chart.py in set_style() that :

if style_id < 0 or style_id > 42:
    style_id = 2
  1. So that there is only 42 styles used. Is there any mistake from the document or from this set_style() function?

    Anyway, I can not find "48 built-in styles available on the ‘Design’ tab in Excel". If I use the style in Design tab, which is only 12 styles, each style makes change of color, background and legend in significant way.
    12_charts

  2. How can I use these 12 styles directly with xlsxWriter?
    My work around is that I have to record a macro about what I have done to the chart and then get the vbaProject.bin file, add it into workbook with workbook.add_vba_project() and make a button for it. Open the xlsm file and press a button, done! The bad thing is, if I want to change any format applied to the chart , I have to record a new macro from the beginning. So I try put some formats in my code and do the rest with vba.

  3. Will xlsxWriter support more styles?
    A way for user to save share the own styles, then import to xlsxWriter or the code in the vba project can be used directly to format chart?

@jmcnamara jmcnamara self-assigned this Dec 11, 2014

@jmcnamara jmcnamara added the bug label Dec 11, 2014

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 11, 2014

Owner

Hi @Dragon2fly,

Thank you for the detailed report.

So that there is only 42 styles used. Is there any mistake from the document or from this set_style() function?

It should be 48 in the code. That is a bug which I'll fix.

Anyway, I can not find "48 built-in styles available on the ‘Design’ tab in Excel".

They are in Excel 2007:

chart_styles

However, in the Excel 2013 User Interface they seem to have been replaced with something that was called "Chart Layouts" in Excel 2007.

Styles (in Excel 2007) are just an index that give one of the choices in the image above. Layouts (in Excel 2007 and "Styles" in Excel 2013) are a collection of modifications made to the chart. They are the same as if someone made 5-20 adjustments to the chart.

How can I use these 12 styles directly with xlsxWriter?

The only way to get the Excel 2013 styles is to modify the base chart using the XlsxWriter Chart API. There is no way for the module to specify an index that will give these.

Will xlsxWriter support more styles?

XlsxWriter cannot support Excel 2013 styles since they are a collection of modifications to the base chart. It would take too much work to implement these for all chart types. Also some of the formatting would need extensions for the Excel 2013 file format.

So in summary you will have to modify an existing chart style via the API to get the type of layout that you want or continue to use the macro workaround.

Regards,

John

Owner

jmcnamara commented Dec 11, 2014

Hi @Dragon2fly,

Thank you for the detailed report.

So that there is only 42 styles used. Is there any mistake from the document or from this set_style() function?

It should be 48 in the code. That is a bug which I'll fix.

Anyway, I can not find "48 built-in styles available on the ‘Design’ tab in Excel".

They are in Excel 2007:

chart_styles

However, in the Excel 2013 User Interface they seem to have been replaced with something that was called "Chart Layouts" in Excel 2007.

Styles (in Excel 2007) are just an index that give one of the choices in the image above. Layouts (in Excel 2007 and "Styles" in Excel 2013) are a collection of modifications made to the chart. They are the same as if someone made 5-20 adjustments to the chart.

How can I use these 12 styles directly with xlsxWriter?

The only way to get the Excel 2013 styles is to modify the base chart using the XlsxWriter Chart API. There is no way for the module to specify an index that will give these.

Will xlsxWriter support more styles?

XlsxWriter cannot support Excel 2013 styles since they are a collection of modifications to the base chart. It would take too much work to implement these for all chart types. Also some of the formatting would need extensions for the Excel 2013 file format.

So in summary you will have to modify an existing chart style via the API to get the type of layout that you want or continue to use the macro workaround.

Regards,

John

jmcnamara added a commit that referenced this issue Dec 11, 2014

Fix for upper chart style limit.
Increased the chart style limit from 42 to the correct 48 (as stated
in the docs). Fix for issue #192.
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 11, 2014

Owner

I've pushed a fix to master, with tests, to increase the styles limit to the correct 48.

Thanks for the report.

Owner

jmcnamara commented Dec 11, 2014

I've pushed a fix to master, with tests, to increase the styles limit to the correct 48.

Thanks for the report.

@Dragon2fly

This comment has been minimized.

Show comment
Hide comment
@Dragon2fly

Dragon2fly Dec 11, 2014

Thanks for quick reply.

I realize that when I modified the chart in Excel and saved it, it made changes into chart.xml inside .xlsx file. So If we know which part inside the chart.xml is corresponding to Excel 2013 styles, we can make these styles available in XlsxWriter by just copying some xml code with ease.

If we can do this, it will also be able to import user-defined styles too.

I just thought.

Thanks for quick reply.

I realize that when I modified the chart in Excel and saved it, it made changes into chart.xml inside .xlsx file. So If we know which part inside the chart.xml is corresponding to Excel 2013 styles, we can make these styles available in XlsxWriter by just copying some xml code with ease.

If we can do this, it will also be able to import user-defined styles too.

I just thought.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 11, 2014

Owner

Hi @Dragon2fly,

So If we know which part inside the chart.xml is corresponding to Excel 2013 styles, we can make these styles available in XlsxWriter by just copying some xml code with ease.

I examined the changes in the file format as well and I don't think it works like that. It isn't adding a single XML style element or even a nested XML element. It is adding a range of different changes that correspond to different parts of the API.

Try setting 2 different Excel 2013 styles and diffing the resulting files. You will see that there isn't an easy way to replicate the styles.

So this can't/won't be supported in XlsxWriter. Sorry.

However, as I said above the Excel 2013 styles/layouts are just a collection of modifications. You should be able to replicate them via the standard XlsxWriter API. It will take a bit of work but it should be doable.

Regards,

John

Owner

jmcnamara commented Dec 11, 2014

Hi @Dragon2fly,

So If we know which part inside the chart.xml is corresponding to Excel 2013 styles, we can make these styles available in XlsxWriter by just copying some xml code with ease.

I examined the changes in the file format as well and I don't think it works like that. It isn't adding a single XML style element or even a nested XML element. It is adding a range of different changes that correspond to different parts of the API.

Try setting 2 different Excel 2013 styles and diffing the resulting files. You will see that there isn't an easy way to replicate the styles.

So this can't/won't be supported in XlsxWriter. Sorry.

However, as I said above the Excel 2013 styles/layouts are just a collection of modifications. You should be able to replicate them via the standard XlsxWriter API. It will take a bit of work but it should be doable.

Regards,

John

@Dragon2fly

This comment has been minimized.

Show comment
Hide comment
@Dragon2fly

Dragon2fly Dec 11, 2014

Okay, I will play with XlsxWriter API.
Thanks for creating a superb module!

Okay, I will play with XlsxWriter API.
Thanks for creating a superb module!

@jmcnamara jmcnamara closed this Dec 31, 2014

@kovla

This comment has been minimized.

Show comment
Hide comment
@kovla

kovla Mar 28, 2018

Hi,

It would appear that the 48 styles that set.style() method uses as argument no longer correspond to pre-set style in Excel 2016. I posted this as a question on SO (https://stackoverflow.com/questions/49534597/xlsxwriter-specifying-plot-style-in-terms-of-excel-2016-designs). Is it something that is likely to be implemented as feature in the future? Just a note.

Also, kudos on the awesome module. I came from R looking for something that can create native Excel plots, and the functionality of XlsxWriter is simply amazing.

Kind regards,
Maxim

kovla commented Mar 28, 2018

Hi,

It would appear that the 48 styles that set.style() method uses as argument no longer correspond to pre-set style in Excel 2016. I posted this as a question on SO (https://stackoverflow.com/questions/49534597/xlsxwriter-specifying-plot-style-in-terms-of-excel-2016-designs). Is it something that is likely to be implemented as feature in the future? Just a note.

Also, kudos on the awesome module. I came from R looking for something that can create native Excel plots, and the functionality of XlsxWriter is simply amazing.

Kind regards,
Maxim

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 28, 2018

Owner

@kovla Answered on SO.

Owner

jmcnamara commented Mar 28, 2018

@kovla Answered on SO.

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