¿Qué indica que una celda Office Open XML contiene un valor de Fecha / hora?
Estoy leyendo un.xlsx usando el Office Open XML SDK y estoy confundido acerca de la lectura de los 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 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 Referencia.pdf.
Los estilos de celdas preestablecidas.el archivo xml incluido con la especificación también se refiere a builtinId
9 como un hipervínculo seguido.
<followedHyperlink builtinId="9">
Todos los estilos en la especificación son simplemente estilos de formato visual, no estilos numéricos. ¿Dónde se definen los estilos numéricos y cómo se diferencia una referencia de estilo s="9"
de indicar un estilo (visual) de formato de celda frente a un estilo numérico?
Obviamente estoy buscando en el lugar equivocado para emparejar estilos en celdas con sus formatos numéricos. ¿Dónde está el lugar correcto para encontrar esta información?
6 answers
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 de número. Para identificar una celda que contiene una fecha, debe realizar la búsqueda de estilo xf -> numberformat, luego identificar si esa máscara de numberformat es una máscara de numberformat de fecha/hora (en lugar de, por ejemplo, un porcentaje o una máscara de numberformat de contabilidad).
El estilo.el archivo 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.
Debe encontrar la sección numFmts en algún lugar cerca de la parte superior del estilo.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 incorporados. Los códigos de formato numérico (numFmtId) inferiores a 164 son "incorporados".
La lista que tengo está 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 están relacionados principalmente con formatos variantes de Asia oriental.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-01-18 23:37:06
La respuesta elegida es acertada, pero tenga en cuenta que Excel define algunos códigos de formato numérico (numFmt) de manera diferente a la especificación OpenXML. Según la documentación de la herramienta de productividad Open XML SDK 2.5 (en la pestaña" Notas del implementador " para la clase NumberingFormat):
El estándar define el formato incorporado ID 14: "mm-dd-aa"; 22: "m/d / aa h: mm"; 37: "#,##0 ;(#,##0)"; 38: "#,##0 ;[Rojo]"; 39: "#,##0.00;(#,##0.00)"; 40: "#,##0.00;[Red]"; 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 #14 es una maravilla. Perdí un par de horas resolviendo por qué no se agregaban ceros a los meses y días de un solo dígito (por ejemplo, 01/05/14 vs.1/5/14).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-04-22 08:21:45
Pensé en agregar mi solución que he reunido para determinar si el doble valor FromOADate
es realmente una fecha o no. La razón es que también tengo un código postal en mi archivo de Excel. El numberingFormat
será nulo si es texto.
Alternativamente, puede usar el numberingFormatId
y cotejarlo con una lista de Ids
que usa Excel 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;
}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-02-12 18:32:52
En estilos.xml vea si hay un nodo numFmt. Creo que tendrá un numFmtId de " 9 " que se relacionará con el formato de fecha que se utiliza.
No se donde esta eso en el ECMA, pero si buscas numFmt, puedes encontrarlo.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-01-18 23:30:36
En caso de que alguien más esté teniendo dificultades con esto, esto es lo que he hecho:
1) Cree un nuevo archivo de Excel y coloque una cadena de fecha y hora en la celda A1
2) Cambie el formato de la celda a lo que desee, luego guarde el archivo.
3) Ejecute el siguiente script de powershell para extraer la hoja de estilos .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 inyectar a DocumentFormat.OpenXml.Spreadsheet.Stylesheet(string outerXml)
, lo que lleva a
4) Utilice el archivo extraído para construir un objeto de excel modelo
var style = File.ReadAllText(@"c:\PATH\TO\EXTRACTED\Style.xml");
var stylesheetPart = WorkbookPart_REFERENCE.AddNewPart<WorkbookStylesPart>();
stylesheetPart.Stylesheet = new Stylesheet(style);
stylesheetPart.Stylesheet.Save();
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-04-20 11:48:41
No estaba claro cómo determinar de manera confiable si una celda tiene valor de fecha/hora. Después de pasar algún tiempo experimentando, se me ocurrió el código (ver post) que buscaría formatos de fecha/hora incorporados y personalizados.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 11:47:21