首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何解释Excel数字格式字符串以确定值是否应由DateTime.FromOADate解析

问如何解释Excel数字格式字符串以确定值是否应由DateTime.FromOADate解析
EN

Stack Overflow用户
提问于 2011-08-02 17:43:14
回答 2查看 3.2K关注 0票数 2

如何创建一个函数"bool IsDateTime“,从而可靠地确定像"$-409h:mm:ss /PM;@”这样的Excel数字格式字符串是否表示应该传递给DateTime.FromOADate的数值是DateTime?

我已经弄清楚了$-409是什么:Excel Number Format: What is "[$-409]"?。只是个地区代码。

我还读过一些关于数字格式字符串被分号分隔为四个格式部分的内容:http://office.microsoft.com/en-us/excel-help/create-or-delete-a-custom-number-format-HP005199500.aspx?CTT=5&origin=HP005198679和http://www.ozgrid.com/Excel/excel-custom-number-formats.htm

例如,简单地搜索日期/时间格式字符(如h,m,s,y,d )的出现情况是否可靠?Excel如何解释它呢?

,万一问题不清楚,.当您读取一个Excel文件并查看一个日期/时间值时,您实际上是在查看一个普通的旧的双精度值,因为它是如何存储在Excel中的。要确定应该传递给DateTime.FromOADate的是普通的双还是双的,您必须解释自定义的数字格式字符串。因此,我询问如何解释这样一个字符串,该字符串可能引用或不引用日期/时间值,以确定是否应该通过DateTime将双精度值转换为DateTime.FromOADate值。此外,如果成功转换为DateTime值,则需要将Excel格式字符串转换为等效的.NET DateTime格式字符串,这样我就可以通过DateTime.ToString( converted_format_string )显示日期/时间值。

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2011-08-04 15:38:54

我实现了一个类来解析Excel数字格式字符串。它查看第一部分(格式字符串中的四个可能的部分),并使用Regex捕获日期/时间特定的自定义格式字符,如"y“、"m”、"d“、"h”、"s“、"AM/PM",如果没有找到,则返回null。第一步简单地决定格式字符串是否用于日期/时间值,并留给我们一个面向对象的有序的逻辑日期/时间格式说明符列表,以供进一步处理。

假设确定格式字符串用于日期/时间值,则捕获值和分类值按照在原始格式字符串中找到的顺序排序。

接下来,它应用Excel特有的格式设置怪癖,比如决定"m“是指月还是分钟,只有在"h”之后或"s“之前出现时才把它解释为”分钟“(允许两者之间使用文字文本,所以它并不完全是”紧接“在”h“或”s“之前)。如果没有指定"AM/PM“,Excel还会强制"h”字符使用24小时时间,因此如果找不到"AM/PM“,则使用小写的m(.NET中的24小时时间),否则它会将其转换为大写M(.NET中的12小时时间)。它还将"AM/PM“转换为.NET等效的"tt",并删除不能包含在普通.NET DateTime格式字符串中的条件表达式。

代码语言:javascript
复制
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;
        }
    }
}

以上类可在以下上下文中用于从具有非空标题的DataTable文件中的列中读取字符串值。具体来说,它尝试获取一个有效的DateTime实例,如果找到一个实例,它将尝试从Excel格式字符串构造一个有效的.NET DateTime格式字符串。如果前面的两个步骤都成功,它将格式化的日期时间字符串存储在数据表中,否则它会将任何存在的值转换为字符串(如果存在,首先确保删除富文本格式):

代码语言:javascript
复制
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;
}
票数 1
EN

Stack Overflow用户

发布于 2011-08-03 15:07:19

通过使用单元格函数并返回格式,可以检查单元格是否包含任何内置的日期格式。如果它使用内置格式,它将返回"D“,后面跟着一个数字。

例如:

代码语言:javascript
复制
=IF(LEFT(CELL("format", A1),1)="D",TRUE,FALSE)

对于更一般的情况,我将首先检查单元格A数(ISNUMBER())和日期范围内(即0到TODAY()之间--即今天的39296 )。然后,我将检查数字格式,以便至少出现一个d、m、y、h、m或s,因为这应该表示您在单元格中有一个日期。

希望这能帮上忙

戴夫

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/6916201

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档