Download it now!
Bug 55091 - FILEOPEN .xlsx shows text dates DD.MM.YYYY as integer numbers with existing date recognition pattern D.M.Y
Summary: FILEOPEN .xlsx shows text dates DD.MM.YYYY as integer numbers with existing d...
Status: RESOLVED DUPLICATE of bug 55875
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6.1.2 release
Hardware: All All
: medium major
Assignee: Markus Mohrhard
URL:
Whiteboard:
Keywords: regression
: 54672 (view as bug list)
Depends on:
Blocks:
 
Reported: 2012-09-19 12:29 UTC by sasha.libreoffice
Modified: 2012-11-17 11:14 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
simplified xlsx with date column (7.63 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2012-09-19 12:29 UTC, sasha.libreoffice
Details
the same document, saved as PDF using msExcel 2007 (103.00 KB, application/x-download)
2012-09-19 12:30 UTC, sasha.libreoffice
Details
Test Kit (15.72 KB, application/x-7z-compressed)
2012-09-19 14:26 UTC, Rainer Bielefeld Retired
Details

Note You need to log in before you can comment on or make changes to this bug.
Description sasha.libreoffice 2012-09-19 12:29:55 UTC
Created attachment 67379 [details]
simplified xlsx with date column

This document in xlsx format contains spreadsheet having one column with birth dates. In LO 3.6.1 Calc opens this document incorrectly. Dates becomes ordinary numbers.

This is not reproducible in 3.3.4 and 3.5.5, therefore regression since 3.6.0
Comment 1 sasha.libreoffice 2012-09-19 12:30:49 UTC
Created attachment 67380 [details]
the same document, saved as PDF using msExcel 2007
Comment 2 Rainer Bielefeld Retired 2012-09-19 13:24:55 UTC
NOT reproducible with parallel installation of Master "LOdev  3.7.0.0.alpha0+   -  ENGLISH UI / German Locale  [Build ID: 24761a6]"  {tinderbox: @6, pull time 2012-09-18 23:21:04} on German WIN7 Home Premium (64bit)

I tested various older versions on  WIN7 and also 3.5.2.2 on Ubuntu 12.04 64Bit, always the same text looking like date column shown. Same Result with MS EXCEL VIEWER, Gnumeric, AOOo 3.4.1

Might be localization related?

REPRODUCIBLE with a.m. 3.7.0.0.alpha0+ and Date recognitin pattern 'D.M.Y', but that feature is not available for 3.5.1 (is it in Tools -> Options -> Language Setings -> Languages - Date acceptance Patterns)'


@sasha:
In your comment you write problem came with LibO 3.6.1, but you selected Version 3.5.1. What's true?
If you create PDF if possible with cell borders and column / row headings.

Please:
- Write a meaningful Summary describing exactly what the problem is
- Attach a sample document (not only screenshot) or refer to an existing 
  sample document in an other Bug with a link; to attach a file to this 
  bug report, just click on "Add an attachment" right on this page.
- Attach screenshots with comments (also of result after FILEOPEN, may be with
  formatting dialog) if you believe that that might explain the 
  problem better than a text comment. Best way is to insert your screenshots
  into a DRAW document and to add comments that explain what you want to show
- Contribute a document related step by step instruction containing every 
  key press and every mouse click how to reproduce your problem 
  (similar to example in Bug 43431)
– if possible contribute an instruction how to create a sample document 
  from the scratch (Do you see the problem with .xlsx created from LibO?)
- add information 
  -- concerning your PC
  -- concerning your OS (Version, Distribution, Language)
  -- concerning your LibO version (with Build ID if it's not a public release)
     and localization (UI language, Locale setting)
  –- Libo settings that might be related to your problems 
    (video hardware acceleration ...)
  -- how you launch LibO and how you opened the sample document
  –- Whether your problem persists when you renamed your user profile 
     before you launch LibO (please see
     <https://wiki.documentfoundation.org/UserProfile#User_profile_location>)
  -- everything else crossing your mind after you read linked texts

@Eike:
Can you confirm my observations concerning Date acceptance Patterns? I think that would be unintended?
Comment 3 sasha.libreoffice 2012-09-19 13:54:46 UTC
Sorry. Adjusted to 3.6.1.2
Comment 4 Rainer Bielefeld Retired 2012-09-19 14:26:47 UTC
Created attachment 67385 [details]
Test Kit

Because I am pretty sure that I found the reason for the problem, I hijack sasha's bug ;-)

In attached test kit you find the source document sample.ods and some exported documents. .xls, .ods .xls and .sxc always keep text formatting when opened with parallel installation of Master "LOdev  3.7.0.0.alpha0+   -  ENGLISH UI / German Locale  [Build ID: 24761a6]"  {tinderbox: @6, pull time 2012-09-18 23:21:04} on German WIN7 Home Premium (64bit). .XLSX only keep text formatting if no acceptance pattern "D.M.Y" exists, otherwise the text will be changed to the date corresponding integer number. 

@sasha:
If this is not "your" bug please submit a new separate bug with requisted additional info.
If this is "your" bug please contribute info concerning your OS!
Comment 5 sasha.libreoffice 2012-09-19 14:33:39 UTC
It is my bug, indeed. Reproducible with both xlsx files from archive. 
Os is Linux Fedora Lovelock 64 bit
Comment 6 Rainer Bielefeld Retired 2012-09-19 14:45:51 UTC
As expected also [Reproducible] with Server Installation of "LibreOffice 3.6.2.1 rc  German UI/ German Locale [Build-ID:  ba822cc] on German WIN7 Home Premium (64bit) 

It seems the import filter is passing through a function where the date recognition becomes active and does crazy things?

Related to a new feature, so no longer regression. Serious, of course, but may be not a MAB because I doubt that there are many test formatted dates in .xlsx. but who can know?

A quick test with number formatted dates did not show a similar .xlsx import problem.

@Sasha:
Good Shot! Please feel free to add to MAB if you think it's appropriate!

@Eike:
Please set Status to ASSIGNED and add yourself to "Assigned To" if you accept this Bug or forward the Bug if it's not your turf.
Comment 7 Rainer Bielefeld Retired 2012-09-23 16:59:22 UTC
*** Bug 54672 has been marked as a duplicate of this bug. ***
Comment 8 Rainer Bielefeld Retired 2012-09-23 17:00:41 UTC
Problem also happens for other Date and time formats, please see Bug 54672!
Comment 9 Markus Mohrhard 2012-11-16 17:13:19 UTC
(In reply to comment #0)
> Created attachment 67379 [details]
> simplified xlsx with date column
> 
> This document in xlsx format contains spreadsheet having one column with
> birth dates. In LO 3.6.1 Calc opens this document incorrectly. Dates becomes
> ordinary numbers.
> 
> This is not reproducible in 3.3.4 and 3.5.5, therefore regression since 3.6.0

Ok. So in master/4.0 this document is correctly imported as document with string cells.

This bug report has nothing to do with date recognition. These cell values should not be treated like dates because they are clearly marked as strings.

The bug is that we seem to fail resolve the cell type for shared strings and just import the value.


I'll look into it.
Comment 10 Markus Mohrhard 2012-11-16 17:15:31 UTC
Hmm A current 3.6 build does not show this problem. Can anyone please check if this still happens with 3.6.4.1?
Comment 11 sasha.libreoffice 2012-11-17 06:29:01 UTC
At least in 3.6.3 on RFR 17 64 bit reproducible with first attachment
IMHO no much sense in backporing fix if we will have working 4.0.0 in 3 month
Comment 12 Markus Mohrhard 2012-11-17 11:14:12 UTC

*** This bug has been marked as a duplicate of bug 55875 ***