Atlassian uses cookies to improve your browsing experience, perform analytics and research, and conduct advertising. Accept all cookies to indicate that you agree to our use of cookies on your device.
Atlassian uses cookies to improve your browsing experience, perform analytics and research, and conduct advertising. Accept all cookies to indicate that you agree to our use of cookies on your device. Atlassian cookies and tracking notice, (opens new window)
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.