Feature Request: Saving default values to a file

Question

Feature Request: Saving default values to a file

true-alex opened this issue 2 years ago · 8 comments

When generating xml files, if the value is equal to the default value, then it will not be saved to the file, even if this value is explicitly set by calling the corresponding method.
This is usually not a problem, however I use XlsxWriter to generate charts that are pasted into DOCX files and the defaults are different in Word.

In particular, when the add_series(...smooth => 0) parameter is set, the <c:smooth val="0"/> tag will not be written, which is not important for Excel, but in Word it leads to smooth lines.

If the file created by XlsxWriter is opened in Excel, and the graph is transferred in the application, then Excel will automatically prescribe its own default values.

Answer 1 · 2022-11-25T20:37:13.000Z

@true-alex

In particular, when the add_series(...smooth => 0) parameter is set, the <c:smooth val="0"/> tag will not be written, which is not important for Excel, but in Word it leads to smooth lines.

Excel::Writer::Xlsx generates files that match Excel 2007's output. So if it doesn't add a 0 value <smooth> element then it is almost certainly because Excel doesn't. There are some Excel generated files in the test suite corresponding to these test cases:

t/regression/chart_line03.t
t/regression/chart_line04.t
t/regression/chart_scatter04.t
t/regression/chart_scatter05.t
t/regression/chart_scatter09.t
t/regression/chart_scatter10.t
t/regression/chart_scatter11.t

You can try one of those and see if you get the same behaviour.

Answer 2 · 2022-11-28T10:26:58.000Z

You're right, Excel 2007 doesn't record default values, but later versions of Excel (since 2010) do record default values. Is it possible to change the behavior of the module so that if the value is set explicitly, then write it, if the value is not set, then do not write it? This way you can maintain compatibility with the current version and create files that will work equally well in programs with different default settings.

Answer 3 · 2022-11-28T12:12:31.000Z

Is it possible to change the behavior of the module so that if the value is set explicitly, then write it, if the value is not set, then do not write it?

Possibly.

Is it just for that <c:smooth> element or are you looking for other elements as well?

Answer 4 · 2022-11-28T14:25:33.000Z

For all elements.

Answer 5 · 2022-11-28T16:37:58.000Z

For all elements.

That isn't really feasible. At a minimum it would require a lot of additional test cases based on newer Excel files and matching the additional fields added in the new formats would be a lot of additional work.

Are you 100% sure that Excel 2007 files aren't supported correctly when embedded in Word? Do you have a sample Excel::Writer::Xlsx file, or other, that demonstrates this behavior.

Answer 6 · 2022-11-29T10:50:40.000Z

I make the DOCX :: Template module, it takes the DOCX file with specially added marks to the input (I use the syntax similar to JS-Reender) and the object, it overduses the file, correctly adding and changing existing objects. Now you can use any modern Word to create the input file of the DOCX, I have no restrictions on the version.

The task of embedding graphs in Word is solved by the separate construction of Chart1.xml and its inclusion in the structure of the DOCX file. If you specify the DOCX 12 version (MS Office 2007), you can use Chart1.xml generated Excel :: Writer :: XLSX If you do not lower the version, then there is a problem with default values. I am afraid to lower the version to 12, since the source file may contain values from which are not compatible with the 12 version.

I created the DOCX file in Word2007 and replaced Chart1.xml in it with Excel :: Writer :: XLSX (with my additions with several graphs) This file is equally successfully opened in Word2007 (screenshot 1) and Word2010 (screenshot 2), If the template is created in the Word2010, then the embedded chart1.xml leads to the schedule in Word2010 (screenshot 3), while Word2007 (screenshot 4), it reflects correctly. If in the 2010 file, lower the version to 12, then the file begins to work correctly in Word 2010 (screenshot 5). If you register the default values (which happens in version 14 and above), then the file will also correctly display in all versions.

To solve my problem, I will now lower the version of the file to 12, but it would be good that it could be done without it, here I see two solutions: 1. The deferred obviously values to write to the output file, even if they coincide with the default. 2. Enter the parameter of the object of the object, the inclusion of which will lead to the fact that the default values will always be written to the file (especially since such a parameter already exists and is used $self->{_excel_version} == 2010).
Screenshot 1

Screenshot 2

Screenshot 3

Screenshot 4

Screenshot 5

Doc2007.docx
Doc2010.docx
Doc2010v12.docx

Now I have found a temporary solution to my problem. Thank you! But I would like to use a more reliable solution not related to changing the file version.

Answer 7 · 2022-12-13T12:13:46.000Z

Now I have found a temporary solution to my problem.

Good work and good analysis of the issue.

I would like to use a more reliable solution not related to changing the file version.

This is just too specific a use case to make the effort worthwhile. Sorry.

Closing as won't fix.

Answer 8 · 2022-12-13T15:59:51.000Z

I looked into the situation carefully, downgrading breaks everything else, so I made a lot of changes to my fork of Excel::Writer::XLSX here is the list of changes:

Added:

minimal support for office 2010
support for merging more than two graphs (there is an assumption that the resulting file will not be correct if there are more than two x-axes or two y-axes, but I did not have such a use case).
Pie/Doughnut chart explosion
Ability to specify an external id for the chart (required for embedding in Word)
Setting the rounding of the frames
Control the display of values that are outside the ranges max - max

Bugfix:
in chartSheet call set_high_low_lines (an exception occurred)

https://github.com/true-alex/excel-writer-xlsx/tree/excel-2010-compatibility

All these changes did not require rewriting of texts (all old tests all pass without errors).
If you are interested in any of these changes for inclusion in the main branch, write and I will move them to a separate branch.
In my current branch, I raised the version due to the peculiarities of the production rollout system (what would my version be taken from, I'm not from cpan).