Cargar datos desde Excel con ExcelBuffer en Business Central
Bienvenidos bibliotecarios a una nueva entrada😁.
En la entrada de hoy os voy a mostrar cómo crear un proceso para cargar datos que tenemos en un Excel a Business Central.
Esto podemos realizarlo mediante paquetes de configuración. ¿Y entonces cuál es la utilidad de este proceso?
Bien, la principal ventaja de este proceso es la posibilidad de nosotros tener el control sobre el código.😎
Lo importante, es que antes de hacer el proceso, debemos tener clara la estructura que tendrá el Excel que vamos a cargar, sabiendo en que columna se encuentra cada campo y en que fila comienzan los datos para descartar la cabecera.
Podremos usarlo con varias opciones como insertar registros, modificar registros, modificar y en caso de no existir insertar, eliminar registros, etc.
En nuestro caso vamos a editar los proveedores, en los que editaremos el campo «Contact (8, Text[100])» y el campo «Search Name (3, Code[100])» identificando el proveedor por su clave primaria «No. (1, Code[20], CP)»
Bien, dicho esto vayamos a lo que hemos venido:
- Crear el Excel que vamos a cargar:
Copiamos las líneas que vamos a modificar y las pegamos en un Excel.
En mi caso les pondré a todos los proveedores el mismo contacto y el mismo alias
- Creamos un report ProcessingOnly con una requestpage en la que pediremos al usuario el excel a cargar, la hoja y que nos indique la primera fila con datos:
report 50001 "BBC Modify Vendors"
{
Caption = 'Modify Vendors', Comment = 'ESP="Modificar proveedores"';
UsageCategory = ReportsAndAnalysis;
ApplicationArea = All;
ProcessingOnly = true;
requestpage
{
layout
{
area(Content)
{
group(Filters)
{
Caption = 'Filters', Comment = 'ESP="Filtros"';
field(Name; FileName)
{
Caption = 'Choose File', comment = 'ESP="Seleccione archivo"';
ApplicationArea = All;
Editable = false;
AssistEdit = true;
ToolTip = ' ';
trigger OnAssistEdit()
begin
FileName := FileManagement.BLOBImportWithFilter(TempBlob, ImportTxt, FileName, StrSubstNo(FileDialogTxt, FilterTxt), FilterTxt);
FileExt := FileManagement.GetExtension(FileName);
if (FileName = '') or (not TempBlob.HasValue()) or not (FileExt in ['xlsx', 'xls']) then begin
FileName := '';
Error(FileNameErr);
end;
TempBlob.CreateInStream(ImportedInStream);
end;
}
field(SheetNameField; SheetName)
{
Caption = 'Choose Sheet Name', comment = 'ESP="Seleccione hoja"';
ApplicationArea = All;
Editable = false;
ToolTip = ' ';
trigger OnAssistEdit()
begin
if (FileName = '') or (not TempBlob.HasValue()) then
Error(FileNameErr);
SheetName := rGExcelBuffer.SelectSheetsNameStream(ImportedInStream);
end;
}
field(InitRow; InitRow)
{
ApplicationArea = All;
Caption = 'First Row With Data', Comment = 'ESP="Primera filas con datos"';
}
}
}
}
trigger OnOpenPage()
begin
//* Supondremos que la mayoría de veces la fila 1 será la cabecera de los campos
InitRow := 2;
end;
}
var
rGExcelBuffer: Record "Excel Buffer";
TempBlob: Codeunit "Temp Blob";
FileManagement: Codeunit "File Management";
ImportedInStream: InStream;
FileName, SheetName, FileExt : Text;
FileNameErr: Label 'Please choose proper file', comment = 'ESP="Elija el archivo adecuado"';
FileDialogTxt: Label 'Import (%1)|%1', comment = 'ESP="Importado (%1)|%1"';
ImportTxt: Label 'Import Excel File', comment = 'ESP="Importar arhivo excel"';
FilterTxt: Label '*.xlsx;*.xls;*.*', Locked = true;
InitRow: Integer;
}- Abrimos el Excel que vamos a leer:
trigger OnPreReport()
begin
Clear(rGExcelBuffer);
rGExcelBuffer.DeleteAll(true);
rGExcelBuffer.OpenBookStream(ImportedInStream, SheetName);
rGExcelBuffer.ReadSheetContinous(SheetName, true);
//ProcessExcelImport();
Clear(rGExcelBuffer);
rGExcelBuffer.DeleteAll(true);
end;- Para comenzar el proceso de importación:
Lo primero que necesitamos es abrir una ventana para indicar el los registros del proceso, ya que si el proceso es demasiado largo, y no tenemos una ventana abierta nuestra sesión puede caducar en mitad del proceso dejando el mismo incompleto.
Seguidamente comprobaremos que el Excel cargado contiene más de un registro.:
if rGExcelBuffer.IsEmpty() then
Error(NoRecordsErr);
Por último conseguimos la última línea del Excel que contiene datos y filtramos las filas del ExcelBuffer con el valor del InitRow, para descartar la fila de la cabecera o líneas en blanco que haya encima de los datos. rGExcelBuffer.SetFilter(«Row No.», ‘%1..’, InitRow);
Para dejar todo listo para recoger los datos creamos un bucle for que recorra desde el InitRow hasta la última fila con valores.
var
rGExcelBuffer: Record "Excel Buffer";
TempBlob: Codeunit "Temp Blob";
FileManagement: Codeunit "File Management";
ImportedInStream: InStream;
FileName, SheetName, FileExt : Text;
FileNameErr: Label 'Please choose proper file', comment = 'ESP="Elija el archivo adecuado"';
FileDialogTxt: Label 'Import (%1)|%1', comment = 'ESP="Importado (%1)|%1"';
ImportTxt: Label 'Import Excel File', comment = 'ESP="Importar arhivo excel"';
FilterTxt: Label '*.xlsx;*.xls;*.*', Locked = true;
InitRow: Integer;
NoRecordsErr: Label 'There is no records to be processed.', comment = 'ESP="No hay registros que procesar"';
Window: Dialog;
Linea: Integer;
Registros: Integer;
ProgressLbl: Label 'Progreso #1###############';
procedure ProcessExcelImport();
var
Vendor: Record Vendor;
vLMaxRow: Integer;
i: Integer;
begin
Clear(Linea);
Window.Open(ProgressLbl);
Linea := 0;
if rGExcelBuffer.IsEmpty() then
Error(NoRecordsErr);
Clear(vLMaxRow);
rGExcelBuffer.Reset();
rGExcelBuffer.SetCurrentKey("Row No.");
if rGExcelBuffer.FindLast() then
vLMaxRow := rGExcelBuffer."Row No.";
rGExcelBuffer.Reset();
rGExcelBuffer.SetFilter("Row No.", '%1..', InitRow);
Registros := vLMaxRow;
for i := InitRow to vLMaxRow do begin
Linea += 1;
Window.Update(1, Format(Linea) + ' / ' + Format(Registros));
//* Código a procesar
end;
end;- Leer datos de las celdas del Excel:
Ahora viene la parte importante. Que es obtener los valores del Excel para poder trabajar con ellos.
En mi caso, para trabajar me gusta crear una función a la que pasamos la fila y columna que queremos leer y nos devuelve el valor de la celda.
/// <summary>
/// Devuelve el valor de una celda de excel cuando esta es tipo text
/// Returns the value of an excel cell when the cell is a text cell.
/// </summary>
/// <param name="Row">Fila : Integer</param>
/// <param name="Col">Columna : Integer</param>
/// <returns>Valor de la celda : Text</returns>
local procedure GetValue(Row: Integer; Col: Integer): Text;
begin
Clear(rGExcelBuffer);
if rGExcelBuffer.Get(Row, Col) then
exit(rGExcelBuffer."Cell Value as Text");
end;Además suelo crear esa misma función pero usando la función de Evaluate para también abarcar los campos de tipo Integer, Decimal, Date, etc.
/// <summary>
/// Devuelve el valor de una celda de excel cuando esta es tipo integer
/// Returns the value of an excel cell when the cell is a integer cell.
/// </summary>
/// <param name="Row">Fila : Integer</param>
/// <param name="Col">Columna : Integer</param>
/// <returns>Valor de la celda : Integer</returns>
local procedure GetValueInteger(Row: Integer; Col: Integer): Integer;
var
Result: Integer;
begin
Clear(rGExcelBuffer);
if rGExcelBuffer.Get(Row, Col) then begin
Evaluate(Result, rGExcelBuffer."Cell Value as Text");
exit(Result);
end;
end;
/// <summary>
/// Devuelve el valor de una celda de excel cuando esta es tipo decimal
/// Returns the value of an excel cell when the cell is a decimal cell.
/// </summary>
/// <param name="Row">Fila : Integer</param>
/// <param name="Col">Columna : Integer</param>
/// <returns>Valor de la celda : Decimal</returns>
local procedure GetValueDecimal(Row: Integer; Col: Integer): Decimal;
var
Result: Decimal;
begin
Clear(rGExcelBuffer);
if rGExcelBuffer.Get(Row, Col) then begin
Evaluate(Result, rGExcelBuffer."Cell Value as Text");
exit(Result);
end;
end;
/// <summary>
/// Devuelve el valor de una celda de excel cuando esta es tipo date
/// Returns the value of an excel cell when the cell is a date cell.
/// </summary>
/// <param name="Row">Fila : Integer</param>
/// <param name="Col">Columna : Integer</param>
/// <returns>Valor de la celda : Date</returns>
local procedure GetValueDate(Row: Integer; Col: Integer): Date;
var
Result: Date;
begin
Clear(rGExcelBuffer);
if rGExcelBuffer.Get(Row, Col) then begin
Evaluate(Result, rGExcelBuffer."Cell Value as Text");
exit(Result);
end;
end;Y para el caso de un campo Option o Enum realizamos un case que dependerá del valor que haya en esa celda. Aquí un ejemplo con las líneas de presupuestos:
case GetValue(i, 6) of
'Recurso':
JobBudgetLine.Type := JobBudgetLine.Type::Resource;
'Artículo':
JobBudgetLine.Type := JobBudgetLine.Type::Item;
'Cuenta de mayor':
JobBudgetLine.Type := JobBudgetLine.Type::"G/L Account";
'Grupo (Recurso)':
JobBudgetLine.Type := JobBudgetLine.Type::"Group (Resource)";
'Activo':
JobBudgetLine.Type := JobBudgetLine.Type::"Activo";
end;- Y por último, trabajar con los datos para llegar a nuestro objetivo final:
En nuestro caso con el valor de la columna 1, nos posicionaremos en el registro que necesitamos, con el valor 2 modificaremos el contacto y con el valor 3 el alias, quedando así el código. Con la funciones anteriores esto es realmente fácil😎. Siendo «i» el valor de la fila. Vamos con el ejemplo:
procedure ProcessExcelImport();
var
Vendor: Record Vendor;
vLMaxRow: Integer;
i: Integer;
begin
Clear(Linea);
Window.Open(ProgressLbl);
Linea := 0;
if rGExcelBuffer.IsEmpty() then
Error(NoRecordsErr);
Clear(vLMaxRow);
rGExcelBuffer.Reset();
rGExcelBuffer.SetCurrentKey("Row No.");
if rGExcelBuffer.FindLast() then
vLMaxRow := rGExcelBuffer."Row No.";
rGExcelBuffer.Reset();
rGExcelBuffer.SetFilter("Row No.", '%1..', InitRow);
Registros := vLMaxRow;
for i := InitRow to vLMaxRow do begin
Linea += 1;
Window.Update(1, Format(Linea) + ' / ' + Format(Registros));
if Vendor.Get(GetValue(i, 1)) then begin
Vendor.Contact := GetValue(i, 2);
Vendor."Search Name" := GetValue(i, 3);
Vendor.Modify();
end;
end;
end;En nuestro caso usamos directamente asignaciones para forzar a Business Central a que coja esos valores, aunque podría realizarse Validates() perfectamente.
Ahora vemos el resultado del código:
Y tras terminar el proceso este es el resultado:
Visto el resultado podemos observar que esto puede ser muy útil para poder modificar los datos a nuestro antojo y corregir errores que haya en los mismos. Aunque también es algo que hay que usar con cuidado, en caso de equivocación podría ser un problema grave, (hay que saber lo que es lo que se esta haciendo y sus posibles consecuencias).
Con este sencillo ejemplo, ahora podemos dar rienda suelta a la imaginación y poner escenarios más difíciles, como en caso de que no encuentre el registro lo inserte, que modifique datos en varias tablas, probar con otras tablas más complejas, etc.
Por ejemplo si encuentra el proveedor, haríamos las modificaciones anteriores, sino lo encuentra crearíamos uno nuevo validando el nombre el cual añadiríamos en la cuarta columna del Excel:
if Vendor.Get(GetValue(i, 1)) then begin
Vendor.Contact := GetValue(i, 2);
Vendor."Search Name" := GetValue(i, 3);
Vendor.Modify();
end
else begin
Vendor.Init();
Vendor."No." := GetValue(i, 1);
Vendor.Insert();
Vendor.Validate(Name, GetValue(i ,4)); //Consecuentemente el campo Alias también se rellena
Vendor.Modify();
end;
Os dejo por aquí el código completo para que podáis probarlo:
report 50001 "BBC Modify Vendors"
{
Caption = 'Modify Vendors', Comment = 'ESP="Modificar proveedores"';
UsageCategory = ReportsAndAnalysis;
ApplicationArea = All;
ProcessingOnly = true;
requestpage
{
layout
{
area(Content)
{
group(Filters)
{
Caption = 'Filters', Comment = 'ESP="Filtros"';
field(Name; FileName)
{
Caption = 'Choose File', comment = 'ESP="Seleccione archivo"';
ApplicationArea = All;
Editable = false;
AssistEdit = true;
ToolTip = ' ';
trigger OnAssistEdit()
begin
FileName := FileManagement.BLOBImportWithFilter(TempBlob, ImportTxt, FileName, StrSubstNo(FileDialogTxt, FilterTxt), FilterTxt);
FileExt := FileManagement.GetExtension(FileName);
if (FileName = '') or (not TempBlob.HasValue()) or not (FileExt in ['xlsx', 'xls']) then begin
FileName := '';
Error(FileNameErr);
end;
TempBlob.CreateInStream(ImportedInStream);
end;
}
field(SheetNameField; SheetName)
{
Caption = 'Choose Sheet Name', comment = 'ESP="Seleccione hoja"';
ApplicationArea = All;
Editable = false;
ToolTip = ' ';
trigger OnAssistEdit()
begin
if (FileName = '') or (not TempBlob.HasValue()) then
Error(FileNameErr);
SheetName := rGExcelBuffer.SelectSheetsNameStream(ImportedInStream);
end;
}
field(InitRow; InitRow)
{
ApplicationArea = All;
Caption = 'First Row With Data', Comment = 'ESP="Primera filas con datos"';
}
}
}
}
trigger OnOpenPage()
begin
//* Supondremos que la mayoría de veces la fila 1 será la cabecera de los campos
InitRow := 2;
end;
}
trigger OnPreReport()
begin
Clear(rGExcelBuffer);
rGExcelBuffer.DeleteAll(true);
rGExcelBuffer.OpenBookStream(ImportedInStream, SheetName);
rGExcelBuffer.ReadSheetContinous(SheetName, true);
ProcessExcelImport();
Clear(rGExcelBuffer);
rGExcelBuffer.DeleteAll(true);
end;
var
rGExcelBuffer: Record "Excel Buffer";
TempBlob: Codeunit "Temp Blob";
FileManagement: Codeunit "File Management";
ImportedInStream: InStream;
FileName, SheetName, FileExt : Text;
FileNameErr: Label 'Please choose proper file', comment = 'ESP="Elija el archivo adecuado"';
FileDialogTxt: Label 'Import (%1)|%1', comment = 'ESP="Importado (%1)|%1"';
ImportTxt: Label 'Import Excel File', comment = 'ESP="Importar arhivo excel"';
FilterTxt: Label '*.xlsx;*.xls;*.*', Locked = true;
InitRow: Integer;
NoRecordsErr: Label 'There is no records to be processed.', comment = 'ESP="No hay registros que procesar"';
Window: Dialog;
Linea: Integer;
Registros: Integer;
ProgressLbl: Label 'Progreso #1###############';
procedure ProcessExcelImport();
var
Vendor: Record Vendor;
vLMaxRow: Integer;
i: Integer;
begin
Clear(Linea);
Window.Open(ProgressLbl);
Linea := 0;
if rGExcelBuffer.IsEmpty() then
Error(NoRecordsErr);
Clear(vLMaxRow);
rGExcelBuffer.Reset();
rGExcelBuffer.SetCurrentKey("Row No.");
if rGExcelBuffer.FindLast() then
vLMaxRow := rGExcelBuffer."Row No.";
rGExcelBuffer.Reset();
rGExcelBuffer.SetFilter("Row No.", '%1..', InitRow);
Registros := vLMaxRow;
for i := InitRow to vLMaxRow do begin
Linea += 1;
Window.Update(1, Format(Linea) + ' / ' + Format(Registros));
if Vendor.Get(GetValue(i, 1)) then begin
Vendor.Contact := GetValue(i, 2);
Vendor."Search Name" := GetValue(i, 3);
Vendor.Modify();
end;
end;
end;
/// <summary>
/// Devuelve el valor de una celda de excel cuando esta es tipo text
/// Returns the value of an excel cell when the cell is a text cell.
/// </summary>
/// <param name="Row">Fila : Integer</param>
/// <param name="Col">Columna : Integer</param>
/// <returns>Valor de la celda : Text</returns>
local procedure GetValue(Row: Integer; Col: Integer): Text;
begin
Clear(rGExcelBuffer);
if rGExcelBuffer.Get(Row, Col) then
exit(rGExcelBuffer."Cell Value as Text");
end;
/// <summary>
/// Devuelve el valor de una celda de excel cuando esta es tipo integer
/// Returns the value of an excel cell when the cell is a integer cell.
/// </summary>
/// <param name="Row">Fila : Integer</param>
/// <param name="Col">Columna : Integer</param>
/// <returns>Valor de la celda : Integer</returns>
local procedure GetValueInteger(Row: Integer; Col: Integer): Integer;
var
Result: Integer;
begin
Clear(rGExcelBuffer);
if rGExcelBuffer.Get(Row, Col) then begin
Evaluate(Result, rGExcelBuffer."Cell Value as Text");
exit(Result);
end;
end;
}
Espero que os haya sido de utilidad😜.
Si no entendéis algo no dudéis en dejar un comentario o escribirnos a info@labibliotecadebc.com.
¡Ahora te toca a ti!😎
¡Un saludo!😁
LA BIBLIOTECA DE BC
Gran post!
Muchas gracias Sergio😜
Espero que te sirva en tu día a día
Hola buenas tardes, buen post. Tengo un par de dudas.
¿Qué pasa si es un excel cuya estructura la genera un programa externo y no queremos leer todas las columnas? ¿Cómo lo podría controlar?
¿Qué pasa si la celda contiene una descripción con más de 250 caracteres?
Un saludo!
Buenas Diego.
A la primera pregunta, no es necesario leer todas las columnas del Excel. Al sabes la estructura del mismo podes elegir que columnas leer y cuales no. Por ejemplo, imaginemos que tenemos un Excel con 4 columnas pero solo necesitamos asignar la columna 1, 2 y 4. Dentro de nuestro bucle únicamente haremos el getvalue a las esas columnas ignorando así el valor de la columna 3;
Vendor.FieldA := GetValue(i,1);
Vendor.FieldB := GetValue(i,2);
Vendor.FieldD := GetValue(i,4);
El valor de la columna C será ignorado y en ningún caso será asignado a ningún campo.
Por otra parte, bien es cierto que ExcelBuffer tiene una limitación de 250 caracteres en el campo de «Cell Value as Text». Tenemos dos opciones, o bien dividimos en más celdas los valores y concatenaremos los valores mediante código:
Vendor.FieldAB := GetValue(i,1) + GetValue(i,2);
O también tenemos la opción de convertir nuestro excel a un csv separado por «;» y cargarlo mediante xmlport.
Espero que esto resuelva tus dudas😜.
Muchas gracias y un saludo!!😁