After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 776196 - Xlsx "Fit All Columns on One Page" Fail from gnumeric xml
Xlsx "Fit All Columns on One Page" Fail from gnumeric xml
Status: RESOLVED FIXED
Product: Gnumeric
Classification: Applications
Component: import/export MS Excel (tm)
1.12.x
Other Linux
: Normal normal
: ---
Assigned To: Jody Goldberg
Jody Goldberg
Depends on:
Blocks:
 
 
Reported: 2016-12-17 06:33 UTC by Hing
Modified: 2017-01-04 01:12 UTC
See Also:
GNOME target: ---
GNOME version: ---


Attachments
screen shot (19.81 KB, image/png)
2016-12-21 03:42 UTC, Andreas J. Guelzow
Details

Description Hing 2016-12-17 06:33:43 UTC
In setting up the printing information via gnumeric xml, like below:

      <gnm:PrintInformation>
        <gnm:Scale type="size_fit" cols="1" rows="0"/>

The output xlsx (via command "ssconvert in.xml out.xlsx) will have the value "0" forced to be "1", and lead to the problem that the "Fit all columns on one page" cannot be set finally in the xlsx (as it needs "cols=1, rows=0").

Looked into the code, the "if" statement, in plugins/excel/xlsx-write.c, line 2611 (version 1.12.32):

if (pi->scaling.dim.rows > 0)

After changing it to:

if (pi->scaling.dim.rows >= 0)

It works good finally. I didn't look into why it try not to allow "0", I think it could be a bug.
Comment 1 Morten Welinder 2016-12-19 00:38:47 UTC
I'm somewhat confused here.  Why are you messing with the gnumeric xml directly?
Comment 2 Hing 2016-12-19 01:37:57 UTC
Just gnumeric xml fit the use-case that I encounter.

The use-case is like:

1. It is required to generate a xlsx (or xls) using content from a data pool, which can be from mysql, Oracle or MongoDB ... etc. in an Linux environment.
2. The required xlsx (or xls) can be of different layout, say like it can contain some conditional formatting (for example, Cell['A{x}'] will have a string "DONE" where {x} rely on the number of rows that the data occupy, or it can have different coloring depends on the data value.)

To achieve this, we have an intermediate program to read the data pool, and generate the gnumeric xml. 

Of course, we will not generate the gnumeric xml from nothing. We are trying to put some placeholder in an existing gnumeric xml and replace our required data in some way. And the existing gnumeric xml is generated from "ssconvert", that is, "ssconvert -T Gnumeric_XmlIO:sax in.xlsx out.xml". And we will manipulate the out.xml manually (e.g. put some placeholder) as a pre-processing step.

That is why we need gnumeric xml.

It is a question of why the code do not allow "rows=0 or cols=0", as in xlsx, it can means something.
Comment 3 Andreas J. Guelzow 2016-12-20 19:01:42 UTC
Morten,
Gnumeric creates
<gnm:Scale type="size_fit" cols="1" rows="0"/>
when all columns are supposed to be on a single page and the rows may be distributed over any number of pages.

If I use ssconvert to create an xlsx file and open in Gnumeric this is correctly understood.

We need if (pi->scaling.dim.rows > 0) since rows=0 has the special meaning of "any number as required."
Comment 4 Morten Welinder 2016-12-20 20:54:28 UTC
I don't seem to be able to create that situation with the page setup dialog.
I am restricted to 1 and up.
Comment 5 Hing 2016-12-21 03:20:39 UTC
Hi Morten,

Yes, when doing "save as gnumeric xml" from Gnumeric GUI, it can create
<gnm:Scale type="size_fit" cols="1" rows="0"/>

But it only happens when I untick the checkbox. The edit box will force the value to be "1" whenever I input "0".

Hi Andreas J. Guelzow,

Yes, I tried to open the xlsx generated from ssconvert with rows=0:

Gnumeric can understand and can make "Fit All Columns on One Page", but 
Microsoft Excel do not understand and can only make it as "Fit Sheet On One Page".

May I know what is the defect of making it as "0"? What is the meaning of "any number as required". I tried to use my change (i.e. make it as ">= 0") and generate the xlsx, it is ok to open it in both Gnumeric and Excel.

Thanks for your response.

BR,
Hing
Comment 6 Andreas J. Guelzow 2016-12-21 03:42:10 UTC
Created attachment 342294 [details]
screen shot

Morten, the attached screen shot shows how to get the cols=1 rows=0.

I don't know much about xlsx export so perhaps for the xlsx export the 0 value needs to be written. It is significant though to realize that inside Gnumeric the value 0 here has a different meaning!
Comment 7 Andreas J. Guelzow 2016-12-21 03:48:41 UTC
In ECMA-376 version 3 (Office Open XML) the attribute fitToHeight is defined as "Number of vertical pages to fit on". There is no special notation for what 0 should mean, although the datatype is unsignedInt (i.e. 0 is allowed). In the definition of "scale" it says "This setting is overridden when fitToWidth and/or fitToHeight are in use" implying that just one of those attributes might be used, and that is what we doing.

So from the documentation I would expect that fitToHeight should only be set if there is a true maximum number of vertical pages.
Comment 8 Hing 2016-12-21 06:04:44 UTC
Thanks for explaining this.

It seems like, the value "0" has special meaning in code level for Gnumeric. 

In the definition from ECMA-376, "0" should be allowed I think, and it depends on the agent (Excel or Gnumeric) how it interprets the value "0". Is my understanding correct?

I just take a look to ECMA-376, I don't understand why "fitToHeight" is "optional", but "defaulted to 1". If it is "optional" then there should be no default value. If it is defaulted to "1", I assume it means that when "fitToWidth" is set and "fitToHeight" is not set, then "fitToHeight" should be defaulted to 1. This makes sense, like when we have:

<gnm:Scale type="size_fit" cols="1"/>

Then "rows=1" will be defaulted.

Anyway, from my understanding so far, "0" should be allowed and we should leave the agent to determine how this "0" should be interpreted ...
Comment 9 Hing 2017-01-03 02:33:03 UTC
Hello, happy new year, any update on this? I am new to open source, will it be accepted as a potential changes in the next release? If not, and if we are going to make the changes our own so to make it fit our use-case (ms-excel), should we commit the changes to any branch or any licensing problem?

Thanks,
Hing
Comment 10 Morten Welinder 2017-01-03 14:10:03 UTC
We'll get around to it -- we're just not a very fast project.

If we were to do it on your own, attaching a patch here would be the way
to proceed.  All code should be gpl2/gpl3 dual licensed.
Comment 11 Andreas J. Guelzow 2017-01-03 22:28:20 UTC
This problem has been fixed in our software repository. The fix will go into the next software release. Once that release is available, you may want to check for a software upgrade provided by your Linux distribution.
Comment 12 Hing 2017-01-04 01:12:25 UTC
Many Thanks.