Comment interpréter une chaîne de format numérique Excel pour déterminer si la valeur doit être analysée par DateTime.FromOADate ?

Question

Comment puis-je créer une fonction "bool IsDateTime" qui déterminera de manière fiable si une chaîne de format numérique Excel comme "[$-409]h:mm:ss AM/PM;@" indique que la valeur numérique est une DateTime qui doit être transmise à DateTime.FromOADate ?

J'ai compris ce que sont les [409 $] : Format des nombres Excel : Que signifie "[$-409]" ? . C'est juste un code local.

J'ai aussi lu un peu que la chaîne de format des nombres était séparée en quatre sections de format par des points-virgules : http://office.microsoft.com/en-us/excel-help/create-or-delete-a-custom-number-format-HP005199500.aspx?CTT=5&origin=HP005198679 et ici http://www.ozgrid.com/Excel/excel-custom-number-formats.htm

Par exemple, serait-il fiable de rechercher simplement les occurrences des caractères du format date/heure comme h,m,s,y,d ? Comment Excel pourrait-il l'interpréter ?

Si la question n'est pas claire ... lorsque vous lisez un fichier Excel et que vous regardez une valeur de date/heure, vous regardez en fait une bonne vieille valeur en double précision, car c'est ainsi qu'elle est stockée dans Excel. Pour savoir s'il s'agit d'un double ordinaire ou d'un double qui doit être transmis à DateTime.FromOADate, vous devez interpréter la chaîne de format de nombre personnalisée. Je demande donc comment interpréter une telle chaîne, qui peut ou non faire référence à une valeur de date/heure, afin de déterminer si la valeur en double précision doit être convertie en une valeur de date/heure via DateTime.FromOADate. En outre, si la conversion en une valeur DateTime est réussie, je dois alors convertir la chaîne de format numérique Excel en une chaîne de format DateTime .NET équivalente afin de pouvoir afficher la valeur de date/heure comme le ferait Excel via DateTime.ToString( converted_format_string ).

Answer 1

Vous pouvez vérifier si la cellule contient l'un des formats de date intégrés en utilisant la fonction CELL et en renvoyant le format. Elle renverra "D" suivi d'un nombre si elle utilise un format intégré.

Par exemple :

=IF(LEFT(CELL("format", A1),1)="D",TRUE,FALSE)

Pour un cas plus général, je vérifierais d'abord si la cellule est un numéro ( ISNUMBER() ) et dans la fourchette d'une date (c.-à-d., entre 0 et TODAY() - qui est de 39296 aujourd'hui). Je vérifierais ensuite que le format des nombres ne contient pas au moins un d, m, y, h, M ou s, car cela signifie que la cellule contient une date.

J'espère que cela vous aidera,

Dave

Answer 2

J'ai implémenté une classe pour analyser la chaîne de format des nombres d'Excel. Elle examine la première section (sur les quatre possibles dans la chaîne de format), et utilise une Regex pour capturer les caractères de format personnalisés spécifiques à la date et à l'heure tels que "y", "m", "d", "h", "s", "AM/PM", et renvoie null si aucun n'est trouvé. Cette première étape décide simplement si la chaîne de format est destinée à une valeur de date/heure, et nous laisse avec une liste ordonnée orientée objet de spécificateurs de format de date/heure logiques pour un traitement ultérieur.

En supposant qu'il a été décidé que la chaîne de format est destinée à une valeur de date/heure, les valeurs capturées et classées sont triées dans l'ordre où elles se trouvent dans la chaîne de format originale.

Ensuite, il applique les bizarreries de formatage spécifiques à Excel, comme le fait de décider si "m" signifie mois ou minute, en l'interprétant comme "minute" uniquement s'il apparaît immédiatement après un "h" ou avant un "s" (le texte littéral est autorisé entre les deux, donc ce n'est pas exactement "immédiatement" avant/après). Excel impose également l'heure en 24 heures pour le caractère "h" si "AM/PM" n'est pas également spécifié, donc si "AM/PM" n'est pas trouvé, il utilise la minuscule m (heure en 24 heures dans .NET), sinon il la convertit en M majuscule (heure en 12 heures dans .NET). Il convertit également "AM/PM" en l'équivalent .NET "tt", et efface les expressions conditionnelles, qui ne peuvent pas être incluses dans une chaîne de format DateTime .NET ordinaire.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections;

namespace utilities.data
{
    public enum NumberFormatCaptureType
    {
        Condition,
        LiteralText,
        Year,
        Month,
        Day,
        Hour,
        Minute,
        Second,
        AMPM
    }

    public class NumberFormatTypedCapture
    {
        private class ClassificationPair
        {
            public string Name;
            public NumberFormatCaptureType Type;
            public bool IndicatesDateTimeValue;
        }

        private static readonly Regex regex = new Regex( @"(?<c>\[[^]]*])*((?<y>yyyy|yy)|(?<m>mmmm|mmm|mm|m)|(?<d>dddd|ddd|dd|d)|(?<h>hh|h)|(?<s>ss|s)|(?<t>AM/PM)|(?<t>am/pm)|(?<l>.))*", RegexOptions.Singleline | RegexOptions.ExplicitCapture | RegexOptions.Compiled );
        private static readonly ClassificationPair[] classifications = new ClassificationPair[] {
            new ClassificationPair() {Name="c", Type=NumberFormatCaptureType.Condition, IndicatesDateTimeValue=false},
            new ClassificationPair() {Name="y", Type=NumberFormatCaptureType.Year, IndicatesDateTimeValue=true},
            new ClassificationPair() {Name="m", Type=NumberFormatCaptureType.Month, IndicatesDateTimeValue=true},
            new ClassificationPair() {Name="d", Type=NumberFormatCaptureType.Day, IndicatesDateTimeValue=true},
            new ClassificationPair() {Name="h", Type=NumberFormatCaptureType.Hour, IndicatesDateTimeValue=true},
            new ClassificationPair() {Name="s", Type=NumberFormatCaptureType.Second, IndicatesDateTimeValue=true},
            new ClassificationPair() {Name="t", Type=NumberFormatCaptureType.AMPM, IndicatesDateTimeValue=true},
            new ClassificationPair() {Name="l", Type=NumberFormatCaptureType.LiteralText, IndicatesDateTimeValue=false}
        };
        private Capture Capture;
        private string mutable_value;
        public NumberFormatCaptureType Type;

        public NumberFormatTypedCapture( Capture c, NumberFormatCaptureType t )
        {
            this.Capture = c;
            this.Type = t;
            mutable_value = c.Value;
        }

        public int Index
        {
            get {return Capture.Index;}
        }

        public string Value
        {
            get {return mutable_value;}
            set {mutable_value = value;}
        }

        public int Length
        {
            get {return mutable_value.Length;}
        }

        public static string ConvertToDotNetDateTimeFormat( string number_format )
        {
            string[] number_formats = number_format.Split( ';' );
            Match m = regex.Match( number_formats[0] );
            bool date_time_formatting_encountered = false;
            bool am_pm_encountered = false;

            //Classify the catured values into typed NumberFormatTypedCapture instances
            List<NumberFormatTypedCapture> segments = new List<NumberFormatTypedCapture>();
            foreach (ClassificationPair classification in classifications)
            {
                CaptureCollection captures = m.Groups[classification.Name].Captures;
                if (classification.IndicatesDateTimeValue && captures.Count > 0)
                {
                    date_time_formatting_encountered = true;
                    if (classification.Type == NumberFormatCaptureType.AMPM)
                        am_pm_encountered = true;
                }
                segments.AddRange( captures.Cast<Capture>().Select<Capture,NumberFormatTypedCapture>( (capture) => new NumberFormatTypedCapture( capture, classification.Type ) ) );
            }

            //Not considered a date time format unless it has at least one instance of a date/time format character
            if (!date_time_formatting_encountered)
                return null;

            //Sort the captured values in the order they were found in the original string.
            Comparison<NumberFormatTypedCapture> comparison = (x,y) => (x.Index < y.Index) ? -1 : ((x.Index > y.Index) ? 1 : 0);
            segments.Sort( comparison );

            //Begin conversion of the captured Excel format characters to .NET DateTime format characters
            StringComparer sc = StringComparer.CurrentCultureIgnoreCase;
            for (int i = 0; i < segments.Count; i++)
            {
                NumberFormatTypedCapture c = segments[i];
                switch (c.Type)
                {
                    case NumberFormatCaptureType.Hour: //In the absense of an the AM/PM, Excel forces hours to display in 24-hour time
                        if (am_pm_encountered)
                            c.Value = c.Value.ToLower(); //.NET lowercase "h" formats hourse in 24-hour time
                        else
                            c.Value = c.Value.ToUpper(); //.NET uppercase "H" formats hours in 12-hour time
                        break;
                    case NumberFormatCaptureType.Month: //The "m" (month) designator is interpretted as minutes by Excel when found after an Hours indicator or before a Seconds indicator.
                        NumberFormatTypedCapture prev_format_character = GetAdjacentDateTimeVariable( segments, i, -1 );
                        NumberFormatTypedCapture next_format_character = GetAdjacentDateTimeVariable( segments, i, 1 );
                        if ((prev_format_character != null && prev_format_character.Type == NumberFormatCaptureType.Hour) || (next_format_character != null && next_format_character.Type == NumberFormatCaptureType.Second))
                            c.Type = NumberFormatCaptureType.Minute; //Format string is already lowercase (Excel seems to force it to lowercase), so just leave it lowercase and set the type to Minute
                        else
                            c.Value = c.Value.ToUpper(); //Month indicator is uppercase in .NET framework
                        break;
                    case NumberFormatCaptureType.AMPM: //AM/PM indicator is "tt" in .NET framework
                        c.Value = "tt";
                        break;
                    case NumberFormatCaptureType.Condition: //Conditional formatting is not supported in .NET framework
                        c.Value = String.Empty;
                        break;
                    //case NumberFormatCaptureType.Text: //Merge adjacent text elements
                        //break;
                }
            }

            //Now that the individual captures have been blanked out or converted to the .NET DateTime format string, concatenate it all together than return the final format string.
            StringBuilder sb = new StringBuilder();
            foreach (NumberFormatTypedCapture c in segments)
                sb.Append( c.Value );
            return sb.ToString();
        }

        private static NumberFormatTypedCapture GetAdjacentDateTimeVariable( List<NumberFormatTypedCapture> captures, int current, int direction )
        {
        check_next:
            current += direction;
            if (current >= 0 && current < captures.Count)
            {
                NumberFormatTypedCapture capture = captures[current];
                if (capture.Type == NumberFormatCaptureType.Condition || capture.Type == NumberFormatCaptureType.LiteralText)
                    goto check_next;
                return capture;
            }
            return null;
        }
    }
}

La classe ci-dessus peut être utilisée dans le contexte suivant pour lire des valeurs de chaîne dans une DataTable à partir des colonnes d'un fichier Excel qui ont des en-têtes non nuls. Plus précisément, elle tente d'acquérir une instance DateTime valide et, si elle en trouve une, elle tente de construire une chaîne de format DateTime .NET valide à partir de la chaîne de format numérique Excel. Si les deux étapes précédentes sont réussies, il stocke la chaîne de date et d'heure formatée dans la table de données, sinon il convertit la valeur présente en chaîne de caractères (en s'assurant de supprimer d'abord le formatage de texte riche s'il est présent) :

using (ExcelPackage package = new ExcelPackage( fileUpload.FileContent ))
{
    Dictionary<string,string> converted_dt_format_strings = new Dictionary<string,string>();
    ExcelWorksheet sheet = package.Workbook.Worksheets.First();
    int end_column = sheet.Dimension.End.Column;
    int end_row = sheet.Dimension.End.Row;

    DataTable datatable = new DataTable();

    //Construct columns
    int i_row = 1;
    List<int> valid_columns = new List<int>();
    for (int i_col = 1; i_col <= end_column; i_col++)
    {
        ExcelRange range = sheet.Cells[i_row, i_col];
        string field_name_text = range.IsRichText ? range.RichText.Text : (range.Value ?? String.Empty).ToString();
        if (field_name_text != null)
        {
            valid_columns.Add( i_col );
            datatable.Columns.Add( field_name_text, typeof(string) );
        }
    }

    int valid_column_count = valid_columns.Count;
    for (i_row = 2; i_row <= end_row; i_row++)
    {
        DataRow row = datatable.NewRow();
        for (int i_col = 0; i_col < valid_column_count; i_col++)
        {
            ExcelRange range = sheet.Cells[i_row, valid_columns[i_col]];

            //Attempt to acquire a DateTime value from the cell
            DateTime? d = null;
            try
            {
                if (range.Value is DateTime)
                    d = (DateTime)range.Value;
                else if (range.Value is double)
                    d = DateTime.FromOADate( (double)range.Value );
                else
                    d = null;
            }
            catch
            {
                d = null;
            }

            string field_value_text = range.IsRichText ? (range.RichText.Text ?? String.Empty) : (range.Value ?? String.Empty).ToString(); //Acquire plain text string version of the object, which will be used if a formatted DateTime string cannot be produced
            string field_value_dt_text = null;

            if (d.HasValue)
            {
                try
                {
                    string excel_number_format = range.Style.Numberformat.Format;
                    string date_time_format = null;
                    if (excel_number_format != null)
                    {
                        if (!converted_dt_format_strings.TryGetValue( excel_number_format, out date_time_format ))
                        {
                            date_time_format = NumberFormatTypedCapture.ConvertToDotNetDateTimeFormat( excel_number_format );
                            converted_dt_format_strings.Add( excel_number_format, date_time_format );
                        }
                        if (date_time_format != null) //Appears to have Date/Time formatting applied to it
                            field_value_dt_text = d.Value.ToString( date_time_format );
                    }   
                }
                catch
                {
                    field_value_dt_text = null;
                }
            }

            row[i_col] = (field_value_dt_text == null) ? field_value_text : field_value_dt_text;
        }
        datatable.Rows.Add( row );
    }
    return datatable;
}