La tabla Excel Buffer (370) es ese gran aliado que está con nosotros en nuestras importaciones de hojas de cálculo Excel.
Una vez importamos un archivo vemos cómo disponemos de nuestros datos en esta tabla en formato texto, decimal, fecha y hora ... pero ¿y el resto de tipos? ¿qué ocurre si importamos un fichero con datos en formato fecha hora?
La respuesta es: si no hacemos nada al respecto, perdemos el formato fecha hora en favor del formato hora y perdemos los datos de fecha contenidos en la celda.
¿Cómo es esto?
Vamos a revisar qué protagonistas intervienen en el proceso, concretamente iremos al foco del asunto, la tabla 370 Excel Buffer.
Nos centramos en el campo 15 Cell Type, aquí ya podemos intuir dónde está el problema, vemos que es un campo de tipo option cuyos posibles valores son:
Si seguimos desplazándonos a través del código localizamos la función ParseCellValue que es la encargada de evaluar el tipo de dato y rellenar el campo Cell Value as Text. Este campo es con el que finalmente trabajaremos.
Aquí observamos cómo procesa o localiza valores de tipo fecha y hora, primero evalúa si el campo contiene una hora y si la contiene, la toma cómo hora, en caso contrario localiza dígitos que le indiquen que sea una fecha y en caso de disponer de alguno, toma el valor como tal:
if StrPos(FormatString, ':') <> 0 then begin
// Excel Time is stored in OADate format
"Cell Type" := "Cell Type"::Time;
"Cell Value as Text" := Format(DT2Time(ConvertDateTimeDecimalToDateTime(Decimal)));
exit;
end;
if ((StrPos(FormatString, 'y') <> 0) or
(StrPos(FormatString, 'm') <> 0) or
(StrPos(FormatString, 'd') <> 0)) and
(StrPos(FormatString, 'Red') = 0)
then begin
"Cell Type" := "Cell Type"::Date;
"Cell Value as Text" := Format(DT2Date(ConvertDateTimeDecimalToDateTime(Decimal)));
exit;
end;
Del código se desprende que ante un campo en una celda excel de tipo fecha hora, la importación localiza el dígito dos puntos : y lo trata como una hora, con lo que la información de fecha es ignorada.
Pero veamos con un sencillo ejemplo el resultado de lo que aquí se comenta:
Creamos una nueva página cuya tabla origen sea Excel Buffer, temporal y de tipo List.
En esta página añadimos todos los campos que queramos inspeccionar de la tabla:
page 50104 "Excel Import Test"
{
ApplicationArea = All;
Caption = 'Excel Import Test';
PageType = List;
SourceTable = "Excel Buffer";
SourceTableTemporary = true;
UsageCategory = Lists;
layout
{
area(content)
{
repeater(General)
{
field("Row No."; Rec."Row No.")
{
}
field(xlRowID; Rec.xlRowID)
{
}
field("Column No."; Rec."Column No.")
{
}
field(xlColID; Rec.xlColID)
{
}
field("Cell Value as Text"; Rec."Cell Value as Text")
{
}
field(Comment; Rec.Comment)
{
}
field(Formula; Rec.Formula)
{
}
field(Bold; Rec.Bold)
{
}
field(Italic; Rec.Italic)
{
}
field(Underline; Rec.Underline)
{
}
field(NumberFormat; Rec.NumberFormat)
{
}
field(Formula2; Rec.Formula2)
{
}
field(Formula3; Rec.Formula3)
{
}
field(Formula4; Rec.Formula4)
{
}
field("Cell Type"; Rec."Cell Type")
{
}
field("Double Underline"; Rec."Double Underline")
{
}
}
}
}
}
Añadimos una acción para importar ficheros excel y mostrar el resultado de la importación:
actions
{
area(Processing)
{
action(a1)
{
Caption = 'Importar Excel';
Image = Excel;
trigger OnAction()
var
Istr: InStream;
FromFile: Text;
Sheet: Text;
Error001: Label 'No se ha seleccionado un fichero';
begin
Rec.Reset();
Rec.DeleteAll();
if UploadIntoStream('Fichero a importar', '', '', FromFile, Istr) then begin
Sheet := Rec.SelectSheetsNameStream(Istr);
Rec.OpenBookStream(Istr, Sheet);
Rec.ReadSheet();
end else
Error(Error001);
end;
}
}
area(Promoted)
{
actionref(aa1; a1) { }
}
}
Trabajaremos con un fichero de ejemplo con varios tipos de datos, pero lo que nos interesa son las columnas desde E hasta H que son celdas de tipo fecha hora con distintos formatos:
Tras publicar, importamos el fichero para comprobar lo comentado:
Observamos cómo efectivamente, a pesar de disponer de información de fecha y hora en la celda, la importación ha desechado la información de fecha y sólo ha obtenido la información de hora.
Pequeña solución al problema.
Lo primero será aumentar la disponibilidad de tipos de campos disponibles, es decir, atacamos al campo Cell Type, lamentablemente al ser un campo de tipo option, no es posible modificarlo mediante una extensión de tabla para añadirle un nuevo valor, en nuestro caso DateTime, por lo que este paso debemos abordarlo en 2 pasos:
- Crear un nuevo Enum con los tipos de datos que necesitemos para tratar los tipos de datos recibidos:
enum 50100 ExcelTypes
{
Extensible = true;
value(0; Number)
{
Caption = 'Number';
}
value(1; "Text")
{
Caption = 'Text';
}
value(2; "Date")
{
Caption = 'Date';
}
value(3; "Time")
{
Caption = 'Time';
}
value(4; "DateTime")
{
Caption = 'DateTime';
}
}
- Crear un nuevo campo en la tabla Excel Buffer con nuestro nuevo Enum:
tableextension 50102 ExcelBuffExt extends "Excel Buffer"
{
fields
{
field(50100; MyType; Enum ExcelTypes)
{
Caption = 'MyType';
DataClassification = ToBeClassified;
}
}
}
Ahora que dispondremos de un campo que nos informa del tipo de datos procesados, vamos a implementarlo.
Para ello volvemos a la tabla Excel Buffer y a la función ParseCellValue, en ella observamos que disponemos de la posibilidad de suscribirnos a la función OnBeforeParseCellValue y procesar los datos por nuestra cuenta.
Creamos una nueva codeunit, o añadimos el evento a la que tuviéramos para suscripciones a eventos, y añadimos la siguiente función:
codeunit 50101 Events
{
[EventSubscriber(ObjectType::Table, Database::"Excel Buffer", 'OnBeforeParseCellValue', '', false, false)]
local procedure OnBeforeParseCellValue(var ExcelBuffer: Record "Excel Buffer"; var Value: Text; var FormatString: Text; var IsHandled: Boolean)
var
OutStream: OutStream;
Decimal: Decimal;
RoundingPrecision: Decimal;
MyDateTime: DateTime;
begin
IsHandled := true;
ExcelBuffer.NumberFormat := CopyStr(FormatString, 1, 30);
Clear(ExcelBuffer."Cell Value as Blob");
if FormatString = '@' then begin
ExcelBuffer."Cell Type" := ExcelBuffer."Cell Type"::Text;
ExcelBuffer.MyType := ExcelBuffer.MyType::Text;
ExcelBuffer."Cell Value as Text" := CopyStr(Value, 1, MaxStrLen(ExcelBuffer."Cell Value as Text"));
if StrLen(Value) <= MaxStrLen(ExcelBuffer."Cell Value as Text") then
exit; // No need to store anything in the blob
ExcelBuffer."Cell Value as Blob".CreateOutStream(OutStream, TEXTENCODING::Windows);
OutStream.Write(Value);
exit;
end;
Evaluate(Decimal, Value);
if StrPos(FormatString, ':') <> 0 then begin
// Excel Time is stored in OADate format
//If cell format is DateTime
if (StrPos(FormatString, 'y') <> 0) then begin
ExcelBuffer."Cell Type" := ExcelBuffer."Cell Type"::Text;
ExcelBuffer.MyType := ExcelBuffer.MyType::DateTime;
MyDateTime := ExcelBuffer.ConvertDateTimeDecimalToDateTime(Decimal);
ExcelBuffer."Cell Value as Text" := Format(MyDateTime);
end else begin
//If cell format is Time
ExcelBuffer."Cell Type" := ExcelBuffer."Cell Type"::Time;
ExcelBuffer.MyType := ExcelBuffer.MyType::Time;
ExcelBuffer."Cell Value as Text" := Format(DT2Time(ExcelBuffer.ConvertDateTimeDecimalToDateTime(Decimal)));
end;
exit;
end;
if ((StrPos(FormatString, 'y') <> 0) or
(StrPos(FormatString, 'm') <> 0) or
(StrPos(FormatString, 'd') <> 0)) and
(StrPos(FormatString, 'Red') = 0)
then begin
ExcelBuffer."Cell Type" := ExcelBuffer."Cell Type"::Date;
ExcelBuffer.MyType := ExcelBuffer.MyType::Date;
ExcelBuffer."Cell Value as Text" := Format(DT2Date(ExcelBuffer.ConvertDateTimeDecimalToDateTime(Decimal)));
exit;
end;
ExcelBuffer."Cell Type" := ExcelBuffer."Cell Type"::Number;
ExcelBuffer.MyType := ExcelBuffer.MyType::Number;
RoundingPrecision := 0.000001;
ExcelBuffer."Cell Value as Text" := Format(Round(Decimal, RoundingPrecision), 0, 1);
end;
}
Vamos a comentar la función.
Es una copia de la función ParseCellValue de Excel Buffer sólo que con alguna pequeña modificación:
- En la primera línea le indicamos IsHandled := true; nosotros tomamos el control de la importación e informamos a Business central de que no haga nada más, nosotros lo hemos hecho por él.
- A partir de la línea con el comentario //If cell format is DateTime comienza la modificación. Basándonos en cómo procesa los datos, añadimos la posibilidad de que el valor que evalúa sea de tipo fecha hora, para ello localizamos el dígito que nos indique año (y).
- En caso de localizar un valor que interpretemos como fecha hora, en nuestro nuevo campo lo indicamos y para el campo existente Cell Type, al no disponer de este tipo de dato lo he indicado como de tipo Text. En adelante al evaluar el tipo de dato usaré el nuevo campo MyType, no Cell Type.
Ahora añadiremos el nuevo campo en la página que hemos creado para la prueba de importación y probamos de nuevo a importar el fichero Excel:
Resultado de la importación después de los cambios:
Ahora podemos observar cómo en el nuevo campo que hemos creado se indica que el tipo es DateTime, pero lo más importante, en el campo Cell Value as Text disponemos de toda la información y no sólo de la hora.
Espero que este pequeño ejercicio resulte de utilidad.
Ejemplo en
Github
Publicar un comentario