.NET DataTable est un champ de date de mangling à partir d'un file Excel

J'écris beaucoup de code Extract Transform Load maintenant pour le travail. Pour faciliter la lecture des données des feuilles de calcul Excel générées par l'user à partir de SSIS, j'ai écrit une petite bibliothèque qui lit la disposition des en-têtes de colonne à partir d'un file Excel et décharge datatables dans une table dans une database MS SQL même schéma que le file Excel. Il fonctionne très bien jusqu'à présent, mais j'ai quelques problèmes étranges.

(Oui, je sais que SSIS prend en charge ces éléments de façon native. Il y a des raisons techniques pour l'approche que j'ai choisie, et les expliquer vont au-delà de la scope de cette publication.)

Le problème est que, avec certains files d'input, toutes les colonnes avec des informations de date sont mutilées. Par exemple, lorsque 41617 l'import, la string de la date 12/09/2013 est 41617 dans 41617 ; Dec 9, 2013 9:19:58 AM devient 41617.388866 . Je vérifie s'il essaye de traiter le 12/09/2013 comme une équation mathématique, mais (12/09)/2013 = 6.62361 . En outre, j'ai vérifié le formatting de la cellule sur cette feuille de calcul ainsi que quelques autres qui importent des champs de date sans erreurs, et c'est le même partout – Personnalisé, mm / jj / aaaa.

Le plus gros problème ici est que, en raison de la nature du process que j'ai écrit, tout dépend de ma connaissance de la structure du file au moment de l'exécution. Je peux transformer datatables à la volée, mais je ne sais pas comment travailler en arrière à partir de 41617 pour arriver au 12/09/2013 .

DateTime.TryParse est également inutile:

 DateTime dt; DateTime.TryParse("41617", out dt); Console.WriteLine(dt.ToSsortingng());

les sorties

 1/1/0001 12:00:00 AM

Voici un lien vers une copy effacée et identifiée de la feuille de calcul avec laquelle j'ai des problèmes: View / Download from Google Drive

J'utilise une string de connection OLE DB pour lire datatables du file Excel (2007):

 "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties = \"Excel 12.0 XML; HDR=YES; IMEX=1\";"

L'utilisation des classs Interop n'est pas une option. Ils ne sont pas configurés sur le server, et en tant qu'entrepreneur, il est peu probable que je puisse l'installer.

Ensuite, j'utilise OleDbCommand() qui exécute juste SELECT * contre la feuille Excel et remplit un DataSet avec le résultat.

Cela m'a vraiment perplexe, et j'apprécierais grandement toute aide.

Essayez d'utiliser

 DateTime.FromOADate(value)

Edit Je recommand vraiment d'utiliser la réponse de Joe (dont je ne connaissais pas tout à l'heure). Rappelez-vous simplement que cela se passera de 1 pour les dates entre 1899-12-31 et 1900-03-01. Je laisserai ma réponse ici pour fournir des informations générales.

Excel stocke les dates comme le nombre de jours passés à partir de Januari 1er 1900 (jour 1), sauf qu'il traite l'année 1900 comme une année bissextile. (en raison d'un bug avec Lotus 123, avec lequel ils voulaient être compatibles), il y a une journée supplémentaire pour les dates après le 28 février 1900.

Notre bien-aimé Joel Spolsky a écrit un article mentionnant cela il y a quelques années.

Vous pouvez utiliser cette méthode pour convertir une date Excel entière en DateTime

 public static DateTime ConvertFromExcelDate(int excelDate) { if (excelDate > 59) excelDate--; // 59 == februari 29 return (new DateTime(1899,12,31)).AddDays(excelDate); // 19000101 == 1, so 18991231 == 0 }

Assurez-vous de bien documenter cela dans votre code, car cela soulèvera certainement des questions à l'avenir pour les personnes qui ne connaissent pas ce problème.