excel - spreadsheetlight - spreadsheetdocument c#

¿Qué indica que una celda Office Open XML contiene un valor de fecha/hora?

Question

Estoy leyendo un archivo .xlsx usando Office Open XML SDK y estoy confundido acerca de la lectura de valores de Fecha / Hora. Una de mis hojas de cálculo tiene este marcado (generado por Excel 2010)

<x:row r="2" spans="1:22" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:c r="A2" t="s">
    <x:v>56</x:v>
  </x:c>
  <x:c r="B2" t="s">
    <x:v>64</x:v>
  </x:c>
  .
  .
  .
  <x:c r="J2" s="9">
    <x:v>17145</x:v>
  </x:c>

La celda J2 tiene un valor de serie de fecha y un atributo de estilo s="9" . Sin embargo, la Especificación de Office Open XML dice que 9 corresponde a un hipervínculo seguido. Esta es una captura de pantalla de la página 4,999 de ECMA-376, Segunda edición, Parte 1 - Fundamentos y lenguaje de marcado Reference.pdf .

El archivo presetCellStyles.xml incluido con la especificación también hace referencia a builtinId 9 como hipervínculo seguido.

<followedHyperlink builtinId="9">

Todos los estilos en la especificación son simplemente estilos visuales de formateo, no estilos numéricos. ¿Dónde se definen los estilos de número y cómo se diferencia una referencia de estilo s="9" de indicar un estilo de formato de celda (visual) frente a un estilo de número?

Obviamente estoy buscando en el lugar equivocado para que coincida con los estilos en las celdas con sus formatos numéricos. ¿Dónde está el lugar correcto para encontrar esta información?

Answer 1

El atributo s hace referencia a una entrada style xf en styles.xml. El estilo xf a su vez hace referencia a una máscara de formato numérico. Para identificar una celda que contiene una fecha, debe realizar el estilo xf -> numberformat searchup, luego identifique si esa máscara de formate numérico es una máscara de formato numérico de fecha / hora (en lugar de, por ejemplo, un porcentaje o una máscara de formato numérico contable).

El archivo style.xml tiene elementos como:

<xf numFmtId="14" ... applyNumberFormat="1" />
<xf numFmtId="1" ... applyNumberFormat="1" />

Estas son las entradas xf, que a su vez le dan un numFmtId que hace referencia a la máscara de formato de número.

Debería encontrar la sección numFmts en algún lugar cerca de la parte superior de style.xml, como parte del elemento styleSheet

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
    <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <numFmts count="3">
            <numFmt numFmtId="164" formatCode="[$-414]mmmm/ yyyy;@" /> 
            <numFmt numFmtId="165" formatCode="0.000" /> 
            <numFmt numFmtId="166" formatCode="#,##0.000" /> 
        </numFmts>

El ID de formato de número puede estar aquí, o puede ser uno de los formatos integrados. Los códigos de formato numérico (numFmtId) menos de 164 están "incorporados".

La lista que tengo es incompleta:

0 = ''General'';
1 = ''0'';
2 = ''0.00'';
3 = ''#,##0'';
4 = ''#,##0.00'';

9 = ''0%'';
10 = ''0.00%'';
11 = ''0.00E+00'';
12 = ''# ?/?'';
13 = ''# ??/??'';
14 = ''mm-dd-yy'';
15 = ''d-mmm-yy'';
16 = ''d-mmm'';
17 = ''mmm-yy'';
18 = ''h:mm AM/PM'';
19 = ''h:mm:ss AM/PM'';
20 = ''h:mm'';
21 = ''h:mm:ss'';
22 = ''m/d/yy h:mm'';

37 = ''#,##0 ;(#,##0)'';
38 = ''#,##0 ;[Red](#,##0)'';
39 = ''#,##0.00;(#,##0.00)'';
40 = ''#,##0.00;[Red](#,##0.00)'';

44 = ''_("$"* #,##0.00_);_("$"* /(#,##0.00/);_("$"* "-"??_);_(@_)'';
45 = ''mm:ss'';
46 = ''[h]:mm:ss'';
47 = ''mmss.0'';
48 = ''##0.0E+0'';
49 = ''@'';

27 = ''[$-404]e/m/d'';
30 = ''m/d/yy'';
36 = ''[$-404]e/m/d'';
50 = ''[$-404]e/m/d'';
57 = ''[$-404]e/m/d'';

59 = ''t0'';
60 = ''t0.00'';
61 = ''t#,##0'';
62 = ''t#,##0.00'';
67 = ''t0%'';
68 = ''t0.00%'';
69 = ''t# ?/?'';
70 = ''t# ??/??'';

Los valores que faltan se relacionan principalmente con los formatos de variante de Asia oriental.

Answer 2

En caso de que alguien más esté teniendo dificultades con esto, esto es lo que hice:

1) Cree un nuevo archivo de Excel e ingrese una cadena de fecha y hora en la celda A1

2) Cambie el formato en la celda a lo que desee, luego guarde el archivo.

3) Ejecute el siguiente script de powershell para extraer la hoja de estilo de .xlxs

[Reflection.Assembly]::LoadWithPartialName("DocumentFormat.OpenXml")

$xlsx = (ls C:/PATH/TO/FILE.xlsx).FullName
$package = [DocumentFormat.OpenXml.Packaging.SpreadsheetDocument]::Open($xlsx, $true)

[xml]$style = $package.WorkbookPart.WorkbookStylesPart.Stylesheet.OuterXml
Out-File -InputObject $style.OuterXml -FilePath "style.xml"

style.xml ahora contiene la información que puede DocumentFormat.OpenXml.Spreadsheet.Stylesheet(string outerXml) , lo que lleva a

4) Usa el archivo extraído para construir el modelo de objetos excel

var style = File.ReadAllText(@"c:/PATH/TO/EXTRACTED/Style.xml");
var stylesheetPart = WorkbookPart_REFERENCE.AddNewPart<WorkbookStylesPart>();
stylesheetPart.Stylesheet = new Stylesheet(style);
stylesheetPart.Stylesheet.Save();

Answer 3

En styles.xml ver si hay un nodo numFmt. Creo que tendrá un valor numFmtId de "9" que se relacionará con el formato de fecha que se usa.

No sé dónde está eso en el ECMA, pero si busca numFmt, puede encontrarlo.

Answer 4

La respuesta elegida es instantánea, pero tenga en cuenta que Excel define algunos códigos de formato numérico (numFmt) de forma diferente a la especificación OpenXML. Según la documentación de Open XML SDK 2.5 Productivity Tool (en la pestaña "Notas del implementador" para la clase NumberingFormat):

El estándar define el formato ID 14 integrado: "mm-dd-aa"; 22: "m / d / aa h: mm"; 37: "#, ## 0; (#, ## 0)"; 38: "#, ## 0; [Rojo]"; 39: "#, ## 0.00; (#, ## 0.00)"; 40: "#, ## 0.00; [Rojo]"; 47: "mmss.0"; KOR fmt 55: "aaaa-mm-dd".

Excel define el ID de formato incorporado
14: "m / d / aaaa"
22: "m / d / aaaa h: mm"
37: "#, ## 0 _); (#, ## 0)"
38: "#, ## 0 _); [Rojo]"
39: "#, ## 0.00 _); (#, ## 0.00)"
40: "#, ## 0.00 _); [Rojo]"
47: "mm: ss.0"
55: "aaaa / mm / dd"

La mayoría son variaciones menores, pero el n. ° 14 es divertido. Perdí un par de horas averiguando por qué los ceros iniciales no se agregaban a un solo dígito, meses y días (p. Ej., 01/05/14 frente a 1/5/14).

Answer 5

No estaba claro cómo determinar de manera confiable si una celda tiene un valor de fecha / hora. Después de pasar algún tiempo experimentando, me encontré con el código (ver publicación) que buscaría formatos de fecha / hora incorporados y personalizados.

Answer 6

Pensé que agregaría mi solución que he reunido para determinar si el valor doble FromOADate es realmente una fecha o no. La razón es que también tengo un código postal en mi archivo de Excel. numberingFormat será nulo si es texto.

De forma alternativa, podría usar numberingFormatId y consultar una lista de Ids Que Excel usa para las fechas.

En mi caso, he determinado explícitamente el formato de todos los campos para el cliente.

    /// <summary>
    /// Creates the datatable and parses the file into a datatable
    /// </summary>
    /// <param name="fileName">the file upload''s filename</param>
    private void ReadAsDataTable(string fileName)
    {
        try
        {
            DataTable dt = new DataTable();
            using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(string.Format("{0}/{1}", UploadPath, fileName), false))
            {
                WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
                IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
                Worksheet workSheet = worksheetPart.Worksheet;
                SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                IEnumerable<Row> rows = sheetData.Descendants<Row>();

                var cellFormats = workbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
                var numberingFormats = workbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats;

                // columns omitted for brevity

                // skip first row as this row is column header names
                foreach (Row row in rows.Skip(1))
                {
                    DataRow dataRow = dt.NewRow();

                    for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                    {
                        bool isDate = false;
                        var styleIndex = (int)row.Descendants<Cell>().ElementAt(i).StyleIndex.Value;
                        var cellFormat = (CellFormat)cellFormats.ElementAt(styleIndex);

                        if (cellFormat.NumberFormatId != null)
                        {
                            var numberFormatId = cellFormat.NumberFormatId.Value;
                            var numberingFormat = numberingFormats.Cast<NumberingFormat>()
                                .SingleOrDefault(f => f.NumberFormatId.Value == numberFormatId);

                            // Here''s yer string! Example: $#,##0.00_);[Red]($#,##0.00)
                            if (numberingFormat != null && numberingFormat.FormatCode.Value.Contains("mm/dd/yy"))
                            {
                                string formatString = numberingFormat.FormatCode.Value;
                                isDate = true;
                            }
                        }

                        // replace ''-'' with empty string
                        string value = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i), isDate);
                        dataRow[i] = value.Equals("-") ? string.Empty : value;
                    }

                    dt.Rows.Add(dataRow);
                }
            }

            this.InsertMembers(dt);
            dt.Clear();
        }
        catch (Exception ex)
        {
            LogHelper.Error(typeof(MemberUploadApiController), ex.Message, ex);
        }
    }

    /// <summary>
    /// Reads the cell''s value
    /// </summary>
    /// <param name="document">current document</param>
    /// <param name="cell">the cell to read</param>
    /// <returns>cell''s value</returns>
    private string GetCellValue(SpreadsheetDocument document, Cell cell, bool isDate)
    {
        string value = string.Empty;

        try
        {
            SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
            value = cell.CellValue.InnerXml;

            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            }
            else
            {
                // check if this is a date or zip.
                // integers will be passed into this else statement as well. 
                if (isDate)
                {
                    value = DateTime.FromOADate(double.Parse(value)).ToString();
                }

                return value;
            }
        }
        catch (Exception ex)
        {
            LogHelper.Error(typeof(MemberUploadApiController), ex.Message, ex);
        }

        return value;
    }