Comment convertir MS excel date de flotteur de format de date en Ruby?

Tentative d'analyser et XLSX fichier à l'aide de roo joyau dans un script ruby.

Dans excel dates sont stockées comme des flotteurs ou des entiers dans le format DDDDD.ttttt, à compter de 1900-01-00 (00 no 01). Afin de convertir une date comme 40396 - vous prendre 1900-01-00 + 40396 et vous devriez obtenir 2010-10-15, mais je suis 2010-08-08.

Je suis en utilisant active_support/temps de faire le calcul comme suit:

Time.new("1900-01-01") + 40396.days

Je fais mon calcul de mal ou il y a un bug dans le soutien actif?

Je suis à court de ruby 1.9.3-irm sur Windows 7 + dernière active_support gem (3.2.1)

MODIFIER

Je regardais de l'ancien fichier dans Excel avec les données erronées - mon script /console tiraient le droit des données - d'où ma confusion - je fait tout droit, sauf pour l'utilisation de la file de droite!!!! Au diable les nuits de folie!

Merci à tous ceux qui ont répondu, je vais garder la question ici au cas où quelqu'un a besoin d'infos sur comment convertir des dates d'excel à l'aide de ruby.

Aussi pour quelqu'un d'autre en cours d'exécution dans cette - feuille de calcul gem NE prend PAS en charge la lecture de fichiers XLSX à ce point (v 0.7.1) correctement donc je suis en utilisant les règles d'origine pour la lecture, et axlsx pour l'écriture.

OriginalL'auteur konung | 2012-05-11

26

Vous avez un tout-en-une erreur dans votre journée de numérotation - en raison d'un bogue dans Lotus 1-2-3 que Excel et autres tableurs ont soigneusement conservé pour la compatibilité avec plus de 30 ans.

À l'origine, le jour 1 est destinée à être le 1er janvier 1900 (qui, comme vous l'avez dit, faire de la journée 0 égale à décembre 31, 1899). Mais Lotus considéré à tort comme de 1900 à être une année bissextile, donc si vous utilisez les numéros de Lotus pour le présent et compter à rebours, correctement prise de 1900 d'une année commune, le nombre de jour pour tout ce qui est avant le 1er Mars 1900, sont déjà trop élevés. Jour 1 devient le 31 décembre 1899, et le jour 0 de la passe en arrière à la 30e. Donc, à l'époque de la date de l'arithmétique dans Lotus à base de feuilles de calcul est vraiment samedi 30 décembre, 1899. (Moderne Excel et quelques autres feuilles de calcul étendre le Lotus bug de compatibilité assez loin pour montrer février 1900 le fait d'avoir un 29e jour, de sorte qu'ils seront étiquette jour 0 "31 décembre" tout en reconnaissant que c'était un samedi! Mais d'autres Lotus à base de feuilles de calcul, ne le faites pas, et de Rubis n'est certainement pas non plus.)

Même en tenant compte de cette erreur, cependant, votre exemple est incorrect: Lotus nombre de jours 40,396 est le 6 août 2010, 15 octobre. J'ai confirmé cette correspondance dans Excel, LibreOffice, et des feuilles de calcul Google, qui tous d'accord. Vous devez avoir traversé des exemples quelque part.

Voici une façon de faire la conversion:
```
Time.utc(1899,12,30) + 40396.days #=> 2010-08-06 00:00:00 UTC
```
Alternativement, vous pouvez prendre avantage de toute autre correspondance. Au temps zéro pour Ruby (et POSIX systèmes en général) est le moment le 1er janvier 1970 à minuit GMT. Le 1er janvier 1970 est Lotus jour 25,569. Tant que vous n'oubliez pas de faire vos calculs en UTC, vous pouvez également le faire:
```
Time.at( (40396 - 25569).days ).utc # => 2010-08-06 00:00:00 UTC
```
Dans les deux cas, vous voudrez probablement pour déclarer une constante symbolique pour l'époque date (soit le Time objet représentant 1899-12-30 ou POSIX "jour 0" valeur 25,569).

Vous pouvez remplacer les appels à la .days avec une multiplication par 86400 (secondes par jour) si vous n'avez pas besoin active_support/core_ext/integer/time pour autre chose, et ne voulez pas charger juste pour cette.

La compatibilité s'étend jusqu'à 1/1/1900, car Excel calendrier a également 1900 comme une année bissextile.
Juste assez, @phoog, depuis la question traite avec Excel, mais je l'ai mentionné dans d'autres feuilles de calcul en tant que bien, et ils ne s'étendent pas à leur niveau de compatibilité de la mesure. Ruby n'a pas non plus, bien sûr. Donc, effectivement, le jour 0 est toujours 1899-12-30.
Il suffit de les ignorer toute chose - j'ai été confondu la cause dans mon script j'ai été en tirant sur la file de droite, mais dans Excel, j'ai eu un ancien fichier ouvert avec la mauvaise date.!!!!! Encore merci pour la réponse.

OriginalL'auteur Mark Reed
4

"Excel stocke les dates et les heures comme un nombre représentant le nombre de jours depuis 1900-Jan-0, en plus d'une partie fractionnaire d'une journée de 24 heures: ddddd.tttttt . Cela s'appelle une série de date, ou de série date-heure." (http://www.cpearson.com/excel/datetime.htm)

Si votre colonne contient une date, plutôt que juste une date, le code suivant est utile:
```
 dt = DateTime.new(1899, 12, 30) + excel_value.to_f
```
Aussi garder à l'esprit qu'il existe 2 modes de dates dans une feuille de calcul excel, 1900 et 1904, ce qui est généralement activée par défaut pour les feuilles de calcul créés sur le mac. Si vous avez toujours de trouver vos dates hors de 4 ans, vous devez utiliser une autre date de base:
```
 dt = DateTime.new(1904, 1, 1) + excel_value.to_f
```
Vous pouvez activer/désactiver de date calendrier depuis 1904 mode pour toute feuille de calcul, mais les dates apparaissent alors par de 4 ans dans la feuille de calcul si vous modifiez le paramètre après l'ajout de données. En général, vous devriez toujours utiliser 1900 date de mode depuis plus excel utilisateurs dans la nature sont basés sur windows.

Remarque: Une chasse aux sorcières avec cette méthode est que d'arrondi peuvent se produire +/- 1 seconde. Pour moi, les dates, je l'importation sont "assez proche", mais juste quelque chose à garder à l'esprit. Une meilleure solution pourrait utiliser l'arrondissement sur les fractions de secondes pour résoudre ce problème.

A coulé vous werry beaucoup!

OriginalL'auteur kgx
3

Vous avez fait votre calcul de mal. Comment voulez-vous arriver au résultat attendu de 2010-10-15?

Dans Excel, 40396 est 2010-08-06 (ne pas utiliser le calendrier 1904, bien sûr). Pour démontrer que, type 40396 dans une cellule Excel et définir le format yyyy-mm-dd.

Sinon:
```
40396 / 365.2422 = 110.6 (years -- 1900 + 110 = 2010)
0.6 * 12 = 7.2 (months -- January = 1; 1 + 7 = 8; 8 = August)
0.2 * 30 = 6 (days)
```
Excel calendrier inclut de manière incorrecte 1900-02-29; que les comptes pour un jour de différence entre votre 2010-08-08 résultat; je ne suis pas sûr de la raison pour la deuxième journée de la différence.

OriginalL'auteur phoog

Vous devez vous connecter pour publier un commentaire.