Excel Exporter and Importer using OpenXML Libraries

Excel Exporter and Importer using OpenXML Libraries

Introduction

The purpose of this document is to guide and outline the functionality of using the OpenXML Libraries to perform Imports and Exports.

The usage of the OpenXML Libraries for process Imports and Exports shall be available within the:

  • SmartComponent Library Release - January 2026

The usage of the OpenXML libraries is a replacement for the previously used “Microsoft Office Interop Excel” Library (“Microsoft.Office.Interop.Excel”).

Prerequisites

Depending on the target .NET Framework to be used, there are some configuration prerequisites to be performed beforehand. These configuration prerequisites are primarily to be performed within the “SmartComponentLibrary” folder.

Configuration for .NET 4.8 Framework

The configuration for using the .NET 4.8 Framework is ensuring that the following .NET 4.8 Framework Libraries are in place: 

Assemblies/Consultingwerk

  • Consultingwerk.Utilities.Support.dll

Assemblies/Microsoft

  • DocumentFormat.OpenXml.dll

  • DocumentFormat.OpenXml.Features.dll

  • DocumentFormat.OpenXml.Framework.dll

  • DocumentFormat.OpenXml.Linq.dll

Configuration for .NET 8

The configuration for using the .NET8 is ensuring that the following .NET 8.0 Libraries are moved/located in correct place.

All the .NET8 Libraries within AssembliesNetCore should reside within the root of the ‘project’ (i.e ‘SmartComponentLibrary/AssembliesNetCore’ - “Completely separate from the current ‘Assemblies’ folder”). 

  • Consultingwerk.Utilities.Support.dll

  • DocumentFormat.OpenXml.dll

  • DocumentFormat.OpenXml.Features.dll

  • DocumentFormat.OpenXml.Framework.dll

  • DocumentFormat.OpenXml.Linq.dll

Then ensure that the project ‘startup’ parameter “-clrnetcore” is set accordingly.

Note: .NET Core does not support sub-folders in the -assemblies folder

Interface Introduction

Both the ExcelWorksheetExporter & ExcelWorksheetImporter have been altered to allow Interface usage. This has been done to provide a standardized template for how the classes are to be implemented and executed. Thus, allowing the Export / Import to be run as a ‘Service’.

The visible / exposed methods and events are the same as before and are outlined in the relevant sections below.

Using ExcelWorksheetExporter

The previous and current structure of “ExcelWorksheetExporter” depends on using an existing Excel Spreadsheet Template, which is then copied and updated.

An example of how the original code for Exporting Data to Excel is given below:

using Consultingwerk.Util.* from propath. using Consultingwerk.Utilities.Excel.* from propath. using Progress.Lang.* from propath. define variable oExporter as ExcelWorksheetExporter no-undo. define variable oExporterArgs as ExportWorksheetArguments no-undo. define variable cFileName as character no-undo. define temp-table eCustomer no-undo field CustNum as integer label "Customer Number":U field Country as character label "Country":U field Name as character label "Name":U field Contact as character label "Contact":U field SalesRep as character label "Sales Rep":U field CreditLimit as decimal label "Credit Limit":U field Balance as decimal label "Balance":U field EmailAddress as character label "Email Address":U . run FillCustomer. assign cFileName = substitute("Customers &1.xlsx":U, substitute(" (&1)":U, string(today, "99.99.9999"))). run ExcelExport (cFilename). procedure FillCustomer: temp-table eCustomer:EMPTY-TEMP-TABLE(). for each Customer where Customer.CustNum < 10. create eCustomer. buffer-copy Customer to eCustomer. end. end procedure. procedure ExcelExport: define input parameter pcFileName as character no-undo. define variable cTemplate as character no-undo. define variable cFilePath as character no-undo. define variable cFilename as character no-undo. /*Excel befüllen*/ assign cFilePath = System.Environment:GetFolderPath("System.Environment+SpecialFolder":MyDocuments) cFilename = substitute ("&1\&2":U, cFilePath, pcFileName). oExporterArgs = new ExportWorksheetArguments(). oExporter = new ExcelWorksheetExporter() . oExporter:ProgressChanged:Subscribe ("ProcessChangedHandler") . file-information:file-name = "../Templates/Customers.xlsx":U. if file-information:full-pathname > "":U then assign cTemplate = file-information:full-pathname. else undo, throw new AppError (substitute ("Kann Datei nicht finden: &1.":U, "Templates/Customers.xlsx":U), 0) . System.IO.File:Copy (cTemplate, cFilename, true). assign oExporterArgs:StartRow = 1 oExporterArgs:FileName = cFilename oExporterArgs:RaiseProgressChangedAfter = 10 oExporterArgs:ExportColumnLabel = ExportColumnLabelEnum:FieldLabel. oExporter:ExportExcel (oExporterArgs, temp-table eCustomer:handle). WindowsExplorerHelper:ShowFileInExplorer(cFilename). end procedure. procedure ProcessChangedHandler: define input parameter sender as Progress.Lang.Object no-undo . define input parameter e as Consultingwerk.EventArgs no-undo . display "Exported " oExporter:RowsExported . pause 0 before-hide . end procedure .

To execute the new version of “ExcelWorksheetExporter” that utilizes “Interface” and OpenXML is outlined in the table below:

using Consultingwerk.Util.* from propath. using Consultingwerk.Utilities.Excel.* from propath. using Progress.Lang.* from propath. define variable oExporter as IExcelWorksheetExporter no-undo. define variable cFileName as character no-undo. define temp-table eCustomer no-undo field CustNum as integer label "Customer Number":U field Country as character label "Country":U field Name as character label "Name":U field Contact as character label "Contact":U field SalesRep as character label "Sales Rep":U field CreditLimit as decimal label "Credit Limit":U field Balance as decimal label "Balance":U field EmailAddress as character label "Email Address":U . run FillCustomer. assign cFileName = substitute("Customers &1.xlsx":U, substitute(" (&1)":U, string(today, "99.99.9999"))). run ExcelExport (cFilename). procedure FillCustomer: temp-table eCustomer:EMPTY-TEMP-TABLE(). for each Customer where Customer.CustNum < 10. create eCustomer. buffer-copy Customer to eCustomer. end. end procedure. procedure ExcelExport: define input parameter pcFileName as character no-undo. define variable oExporterArgs as ExportWorksheetArguments no-undo. define variable cTemplate as character no-undo. define variable cFilePath as character no-undo. define variable cFilename as character no-undo. assign cFilePath = System.Environment:GetFolderPath("System.Environment+SpecialFolder":MyDocuments) cFilename = substitute ("&1\&2":U, cFilePath, pcFileName). oExporterArgs = new ExportWorksheetArguments(). oExporter = {Consultingwerk/get-service.i IExcelWorksheetExporter "new ExcelWorksheetExporter()"} . oExporter:ProgressChanged:Subscribe ("ProcessChangedHandler") . file-information:file-name = "../Templates/Customers.xlsx":U. if file-information:full-pathname > "":U then assign cTemplate = file-information:full-pathname. else undo, throw new AppError (substitute ("Kann Datei nicht finden: &1.":U, "Templates/Customers.xlsx":U), 0) . System.IO.File:Copy (cTemplate, cFilename, true). assign oExporterArgs:StartRow = 1 oExporterArgs:FileName = cFilename oExporterArgs:RaiseProgressChangedAfter = 10 oExporterArgs:ExportColumnLabel = ExportColumnLabelEnum:FieldLabel. oExporter:ExportExcel (oExporterArgs, temp-table eCustomer:handle). WindowsExplorerHelper:ShowFileInExplorer(cFilename). end procedure. procedure ProcessChangedHandler: define input parameter sender as Progress.Lang.Object no-undo . define input parameter e as Consultingwerk.EventArgs no-undo . display "Exported " oExporter:RowsExported . pause 0 before-hide . end procedure .

Looking at the code sections above, it can be seen that the changes required are minimal.

The first change is changing the variable definition from

  • define variable oExporter as ExcelWorksheetExporter no-undo.

to

  • define variable oExporter  as IExcelWorksheetExporter no-undo.

The variable “oExporter” is now defined as the Interface Instance of “ExcelWorksheetExporter”.

The second change is the instantiation of the variable “oExporter”.

Previous, the invocation of “oExporter” was based directly on the “ExcelWorksheetExporter” class:

  • oExporter = new ExcelWorksheetExporter() .

Now the invocation of “oExporter” is now based on the Service style invocation:

  • oExporter = {Consultingwerk/get-service.i IExcelWorksheetExporter "new ExcelWorksheetExporter()"} .

Using the Event Handlers

The sample code provided, already illustrates how to use the Event Handler

  • ProgressChanged

The available Event Handlers are:

  • Completed

  • ProgressChanged

The code construct is the same for using Event Handlers as defined the OpenEdge Publish/Subscribe Documentation.

For detailed documentation on the technical changes made to the class “ExcelWorksheetExporter” see link.

 

ExcelWorksheetImporter

The purpose of the class “ExcelWorksheetimporter” is to allow the Importing of data from an Excel Spreadsheet. The details below outline the changes that were required to use the ‘OpenXML Libraries’ for facilitating the Importing of Data.

An example of how the original code for Importing Data from Excel is given below:

using Consultingwerk.Util.* from propath. using Consultingwerk.Utilities.Excel.* from propath. define variable oImporter as ExcelWorksheetImporter no-undo. define variable oImporterArgs as ImportWorksheetArguments no-undo. define variable cFileName as character no-undo initial "Customers.xlsx". define variable cBasePath as character no-undo initial "Datenimport\CustomerData\":U. define temp-table eCustomer field CustNum as integer field Country as character field Name as character field Address1 as character field Address2 as character field City as character field State as character field PostalCode as character field Contact as character field Phone as character field SalesRep as character field CreditLimit as decimal field Balance as decimal field Terms as character field Discount as decimal field Comments as character field EmailAddress as character . file-information:file-name = cBasePath + cFileName if file-information:full-pathname > "":U then assign cTemplate = file-information:full-pathname. else undo, throw new AppError (substitute ("Unable to find file &1.", cXLSDataFileName), 0) . oImporterArgs = NEW ImportWorksheetArguments (cFileName, "Sheet1":U) . oImporterArgs:RaiseProgressChangedAfter = 100 . oImporterArgs:SkipRowsFromTop = 1 . oImporter = NEW ExcelWorksheetImporter () . oImporter:ProgressChanged:Subscribe ("ProcessChangedHandler") . oImporter:Import (oImporterArgs, OUTPUT TABLE eCustomer BY-REFERENCE) . catch err AS Progress.Lang.Error : Consultingwerk.Util.ErrorHelper:ShowErrorMessage (err) . end catch. procedure ProcessChangedHandler: define input parameter sender as Progress.Lang.Object no-undo . define input parameter e as Consultingwerk.EventArgs no-undo . display "importing" oImporter:RowsImported . pause 0 before-hide . end procedure .

 

To execute the new version of “ExcelWorksheetImporter” that utilizes “Interface” and OpenXML is outlined in the table below:

using Consultingwerk.Util.* from propath. using Consultingwerk.Utilities.Excel.* from propath. define variable oImporter as IExcelWorksheetImporter no-undo. define variable oImporterArgs as ImportWorksheetArguments no-undo. define variable cFileName as character no-undo initial "Customers.xlsx". define variable cBasePath as character no-undo initial "Datenimport\CustomerData\":U. define temp-table eCustomer field CustNum as integer field Country as character field Name as character field Address1 as character field Address2 as character field City as character field State as character field PostalCode as character field Contact as character field Phone as character field SalesRep as character field CreditLimit as decimal field Balance as decimal field Terms as character field Discount as decimal field Comments as character field EmailAddress as character . file-information:file-name = cBasePath + cFileName if file-information:full-pathname > "":U then assign cTemplate = file-information:full-pathname. else undo, throw new AppError (substitute ("Unable to find file &1.", cXLSDataFileName), 0) . oImporterArgs = NEW ImportWorksheetArguments (cFileName, "Sheet1":U) . oImporterArgs:RaiseProgressChangedAfter = 100 . oImporterArgs:SkipRowsFromTop = 1 . oImporter = {Consultingwerk/get-service.i IExcelWorksheetImporter "new ExcelWorksheetImporter()"} . oImporter:ProgressChanged:Subscribe ("ProcessChangedHandler") . oImporter:Import (oImporterArgs, OUTPUT TABLE eCustomer BY-REFERENCE) . message "imported" oImporter:RowsImported view-as alert-box. temp-table eCustomer:write-json ("file", "C:/Work_Stream/CustomersABL.json", true). catch err AS Progress.Lang.Error : Consultingwerk.Util.ErrorHelper:ShowErrorMessage (err) . end catch. procedure ProcessChangedHandler: define input parameter sender as Progress.Lang.Object no-undo . define input parameter e as Consultingwerk.EventArgs no-undo . display "importing" oImporter:RowsImported . pause 0 before-hide . end procedure .

 

Looking at the code sections above, the changes required are minimal.

The first change is changing the variable definition from

  • define variable oImporter as ExcelWorksheetImporter no-undo.

To:

  • define variable oImporter as IExcelWorksheetImporter no-undo.

The variable “oImporter” is now defined as the Interface Instance of “ExcelWorksheetImporter”.

The second change is the instantiation of the variable “oImporter”.

Previous, the invocation of “oImporter” was based directly on the “ExcelWorksheetImporter” class:

  • oImporter = new ExcelWorksheetImporter() .

Now the invocation of “oImporter” is now based on the Service style invocation:

  • oImporter = {Consultingwerk/get-service.i IExcelWorksheetImporter "new ExcelWorksheetImporter()"} .

Using the Event Handlers

The sample code provided, already illustrates how to use the Event Handler

  • ProgressChanged

The available Event Handlers are:

  • Completed

  • ProgressChanged

The code construct is the same for using Event Handlers as defined the OpenEdge Publish/Subscribe Documentation.

For detailed documentation on the technical changes made to the class “ExcelWorksheetImporter” see link.