Bug 51438 - FILESAVE as xslx: cached formula result is not saved for calculated date cells
Summary: FILESAVE as xslx: cached formula result is not saved for calculated date cells
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.4 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:4.2.0
Keywords:
Depends on:
Blocks:
 
Reported: 2012-06-26 01:12 UTC by Bernhard Seebass
Modified: 2014-03-10 21:42 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Attachments
xlsx-file with calculated date cell saved by LibreOffice (4.10 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2012-07-09 15:56 UTC, Bernhard Seebass 		Details
xlsx-file with calculated date cell saved by Microsoft Excel (1.40 KB, text/xml)
2012-07-09 15:57 UTC, Bernhard Seebass 		Details
xlsx-file with calculated date cell saved by Microsoft Excel (7.49 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2012-07-09 16:01 UTC, Bernhard Seebass 		Details
Show Obsolete (1) View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Bernhard Seebass 2012-06-26 01:12:06 UTC 
When saving a spread sheet as xslx containing formula cells calculating a date,
the formula result (i.e. the date value) is not saved.

This makes it impossible to read that spreadsheet, e.g. using Apache POI in a
Java application. When the saved spread sheet is opened in Microsoft Office and
re-saved the calculated values are available.


Some lines from sheet1.xml in a worbook containing a calculated date cell:


Saved by LibreOffice (yes, I2 is really a date cell and should be saved as numeric value):
      <c r="I2" s="3" t="inlineStr">
        <f aca="false">D2+1</f>
        <is>
          <t></t>
        </is>
      </c>

Saved by Microsoft Office:
      <c r="I2" s="2">
        <f t="shared" ref="I2:I14" si="3">D2+1</f>
        <v>39449</v>
      </c>
Comment 1 Joel Madero 2012-07-09 15:15:09 UTC 
Please provide an attachment showing the problem. Marking as NEEDINFO until document is attached. Once it's attached please change back to UNCONFIRMED
Comment 2 Bernhard Seebass 2012-07-09 15:56:27 UTC 
Created attachment 64029 [details]
xlsx-file with calculated date cell saved by LibreOffice

The cell A2 (in sheet1.xml) claims to be of type inlineStr (instead of numeric) and does not hold any value.

      <c r="A2" s="1" t="inlineStr">
        <f aca="false">A1+1</f>
        <is>
          <t></t>
        </is>
      </c>
Comment 3 Bernhard Seebass 2012-07-09 15:57:55 UTC 
Created attachment 64030 [details]
xlsx-file with calculated date cell saved by Microsoft Excel

The cell A2 correctly hold the formula and the calculated value 36527 

      <c r="A2" s="1">
        <f>A1+1</f>
        <v>36527</v>
      </c>
Comment 4 Bernhard Seebass 2012-07-09 16:01:50 UTC 
Created attachment 64031 [details]
xlsx-file with calculated date cell saved by Microsoft Excel


The cell A2 correctly hold the formula and the calculated value 36527 

      <c r="A2" s="1">
        <f>A1+1</f>
        <v>36527</v>
      </c>
Comment 5 Joel Madero 2012-09-23 18:23:31 UTC 
I do see the issue with 3.6.2.1. Marking as NEW and prioritizing.

Marking as:

Normal: As it could result in a user not being able to easily create high quality work.

Medium: The issue might go missed by the user and result in wrong data in a cell, something which could hurt the end user's professional quality work and make LibO look bad. 

Thanks for reporting, we'll try to get this one fixed soon
Comment 6 Kohei Yoshida 2014-03-10 21:42:48 UTC 
I re-tested this using the 4.2 branch build, and no longer see this problem.