/
ExcelWorksheetExporter and Importer using OpenXML Libraries

ExcelWorksheetExporter and Importer using OpenXML Libraries

Introduction

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

 

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 .NET8

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

Base Classes

ExcelUtility

The class ExcelUtility is ‘inherited’ by both ExcelWorksheetExporter and ExcelWorksheetImporter. The class itself contains the following methods:

  • CloseSpreadsheetDocument

    • @param: poSpreadsheetDocument The reference to the Excel SpreadsheetDocument

  • GetCellValue

    • @param: poWorkbookPart The reference to the Excel WorkbookPart

    • @param: poCell Cell

    • @return: Cell Value

  • GetRowCell

    • @param: poRow The reference to the Excel Row

    • @param: pcCellReference The cell reference

    • @return: Cell Object

  • GetWorkbookPart

    • @param: oSpreadsheetDocument The reference to the Excel SpreadsheetDocument

    • @return: WorkbookPart Object

  • GetWorksheetSheetData

    • @param: poWorkbookPart The reference to the Excel WorkbookPart

    • @return: SheetData Object

  • GetWorksheetPart

    • @param: poWorkbookPart The reference to the Excel WorkbookPart

    • @return: WorksheetPart Object

  • InsertCellValue

    • @param: poWorksheetPart WorksheetPart

    • @param: pcContent Character The content to insert

    • @param: piRowIndex Integer The row to insert into

    • @param: pcCol Character The column to insert into

  • OpenSpreadsheetDocument

    • @param: pcFileName The file name of the Excel file

    • @param: plEditable Logical indicating if the document should be opened in update mode

    • @return: SpreadsheetDocument Object

  • SaveWorkbook

    • @param: poWorkbookPart The reference to the Excel WorkbookPart

  • SaveSpreadsheetDocument

    • @param: poSpreadsheetDocument The reference to the Excel SpreadsheetDocument

  • SetExcelCellValues

    • @param: poWorksheetPart WorksheetPart

    • @param: piCount Integer The row to insert into

    • @param: poValues Object[] The values to insert

 

The section below shall expand on the aforementioned methods, how they are used and what purpose they serve.

OpenSpreadsheetDocument

The method OpenSpreadsheetDocument opens an existing spreadsheet in either update or read-only mode. The premise here is that the relevant ‘spreadsheet’ exists.

GetWorkbookPart

The method GetWorkbookPart accesses and returns the WorkbookPart element of the spreadsheet.

GetWorksheetPart

The method GetWorksheetPart accesses and returns the WorksheetPart element of the WorkbookPart.

GetWorksheetSheetData

The method GetWorksheetSheetData accesses and returns the SheetData element of the WorkbookPart.

GetRowCell

The method GetRowCell is used to retrieve a Cell Object from a specified Row & Cell Reference. This achieved by calling into the ExcelHelper Library method ‘GetRowCell”.

GetCellValue

The method GetCellValue is used to retrieve a specific Cell Object value.

SetExcelCellValues

The method SetExcelCellValues is used to set a Row’s cell values for a given Worksheet. The assignment of the individual cells is performed by calling into ‘InsertCellValue’.

InsertCellValue

The method InsertCellValue is used to populate the cells on a Spreadsheet Row. Each cell of the relevant row is extracted as a Cell Object from calling into the ExcelHelper Library method ‘InsertCellInWorksheet”. The Cell Objects attributes “CellValue” and “DataType” are then populated.

SaveWorkbook

The method SaveWorkbook is used to ‘save’ the Workbook element.

SaveSpreadsheetDocument

The method SaveSpreadsheetDocument is used to ‘save’ the spreadsheet and then dispose of the spreadsheet memory element. This method is used for when the Spreadsheet has been opened in ‘update mode’. The internal aspect of the method calls the ‘Save’ and ‘Close’ attributes the spreadsheet memory element.

 

CloseSpreadsheetDocument

The method CloseSpreadsheetDocument is used to dispose of the spreadsheet memory element. This method is used for when the Spreadsheet has been opened in read mode’. The internal aspect of the method is only calling the ‘Close’ attribute the spreadsheet memory element.

 

ExcelWorksheetExporter

The original “ExcelWorksheetExporter” was constructed to use “Microsoft.Office.Interop.Excel” for performing the Exporting of data to Excel. The details below outline the changes that were required to use the ‘OpenXML Libraries’.

The structure of “ExcelWorksheetExporter” currently depends on using an existing Excel Spreadsheet Template, which is then copied and updated. Hence the reason that the code uses the ‘OpenSpreadsheetDocument’ method and uses the second parameter to indicate whether or not the Spreadsheet is updateable.

In addition, an Interface for the ExcelWorksheetExporter has been introduced.

The conversion of “ExcelWorksheetExporter” required the following changes:

Using Statements

Original

New

using Consultingwerk.Util.*.

using Consultingwerk.Utilities.Excel.*.

using Consultingwerk.Utilities.Support.*.

using Microsoft.Office.Interop.Excel.

using Progress.Lang.*.

using Consultingwerk.*.

using Consultingwerk.Util.*.

using Consultingwerk.Utilities.Excel.*.

using DocumentFormat.OpenXml.*.

using DocumentFormat.OpenXml.Packaging.*.

using DocumentFormat.OpenXml.Spreadsheet.*.

using Progress.Lang.*.

Methods

Only two methods were required to be modified.

ExportExcel

The ExportExcel method is the main method and entry point into Exporting Data to Excel. The two tables below show the original code and the updated code.

 

Original Code

method public void ExportExcel (poExportWorksheetArguments as ExportWorksheetArguments,

phTempTable as handle):

 

define variable oExcel as Microsoft.Office.Interop.Excel.Application no-undo .

define variable oWorkbook as Workbook no-undo .

define variable oWorksheet as Worksheet no-undo .

define variable iStartRow as INTEGER no-undo .

define variable cFileName as CHARACTER no-undo .

 

assign cFileName = FileHelper:FindFile (poExportWorksheetArguments:FileName, TRUE)

iStartRow = poExportWorksheetArguments:StartRow

.

 

oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass () .

oWorkbook = OpenWorkbook (oExcel, cFileName) .

oWorksheet = this-object:GetWorksheet (oWorkbook,

poExportWorksheetArguments:WorksheetName,

poExportWorksheetArguments:WorksheetIndex) .

 

assign oWorksheet:EnableCalculation = false

oWorksheet:EnableFormatConditionsCalculation = false

oWorksheet:EnableOutlining = false .

this-object:ExportRows (oWorksheet,

poExportWorksheetArguments,

phTempTable,

iStartRow) .

oWorksheet:Calculate () .

oWorkbook:Save () .

finally:

if valid-object (oWorkbook) then

oWorkbook:Saved = true .

if valid-object (oExcel) then

oExcel:Quit() .

end finally.

end method.

New Code

method public void ExportExcel (poExportWorksheetArguments as ExportWorksheetArguments,

                                    phTempTable               as handle):

        define variable oSpreadsheetDocument as SpreadsheetDocument no-undo.

        define variable oWorkbookPart       as WorkbookPart         no-undo.

        define variable oWorksheetPart      as WorksheetPart     no-undo.

        define variable iStartRow           as integer           no-undo .

        define variable cFileName         as character         no-undo .

 

        assign cFileName = FileHelper:FindFile (poExportWorksheetArguments:FileName, TRUE)

    iStartRow  = poExportWorksheetArguments:StartRow

                .

 

        oSpreadsheetDocument = this-object:OpenSpreadsheetDocument (cFileName, true).

        oWorkbookPart         = this-object:GetWorkbookPart (oSpreadsheetDocument).

        oWorksheetPart       = this-object:GetWorksheetPart(oWorkbookPart).

 

        this-object:ExportRows (oWorksheetPart,

                              poExportWorksheetArguments,

                                phTempTable,

                                iStartRow) .

 

        this-object:SaveWorkbook(oWorkbookPart).

        this-object:SaveSpreadsheetDocument(oSpreadsheetDocument).

 

        finally:

            GarbageCollectorHelper:DeleteObject(oWorkbookPart).

            GarbageCollectorHelper:DeleteObject(oWorksheetPart).

            GarbageCollectorHelper:DeleteObject(oSpreadsheetDocument).

        end finally.

    end method.

The code highlighted in “red” within the original code is what has been replaced, and the code highlighted in “green” within the new code shows what the code has been replaced with.

 

ExportRows

The changes required for this method were relatively simple, as outlined below.

The first method parameter changed from

  • “poWorksheet AS Worksheet”

to

  • poWorksheetPart as WorksheetPart

 

All occurrences of

  • ExcelHelper:SetExcelCellValues (poWorksheet, iCount, oValues).

replaced with

  • this-object:SetExcelCellValues (poWorksheetPart, iCount, oValues).

(the SetExcelCellValues is contained within the class ExcelUtility).

 

ExcelWorksheetImporter

The original “ExcelWorksheetImporter” was constructed to use “Microsoft.Office.Interop.Excel” for performing the Importing of data from Excel. The details below outline the changes that were required to use the ‘OpenXML Libraries’.

In addition, an Interface for the ExcelWorksheetImporter has been introduced.

Using Statements

Original

New

using Consultingwerk.Utilities.Excel.*.

using Microsoft.Office.Interop.Excel.

using Progress.Lang.*.

using Consultingwerk.*.

using Consultingwerk.Util.*.

using Consultingwerk.Utilities.Excel.*.

using DocumentFormat.OpenXml.*.

using DocumentFormat.OpenXml.Packaging.*.

using DocumentFormat.OpenXml.Spreadsheet.*.

using Progress.Lang.*.

 

Methods

Only two methods were required to be modified and the creation of a new method. The modified methods are:

  • Import

  • ImportRows

The new method is:

  • ExtractRowValues

 

Import

The “Import” method is the main method and entry point into Importing Data from Excel. The two tables below show the original code and the updated code.

Original Code

method public void Import (poImportWorksheetArguments as ImportWorksheetArguments,

                            OUTPUT TABLE-handle phTempTable):

 

    define variable oExcel     as Microsoft.Office.Interop.Excel.Application no-undo .

    define variable oWorkbook  as Workbook                               no-undo .

    define variable oWorksheet as Worksheet                                 no-undo .

    define variable cFileName as CHARACTER no-undo.

 

    assign cFileName = poImportWorksheetArguments:FileName .

 

    oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass () .

    oWorkbook = OpenWorkbook (oExcel, cFileName) .

    oWorksheet = this-object:GetWorksheet (oWorkbook,                                             poImportWorksheetArguments:WorksheetName,

                                      poImportWorksheetArguments:WorksheetIndex) .

 

    this-object:ImportRows (oWorksheet,

                            poImportWorksheetArguments,

                            output table-handle phTempTable by-reference) .

    finally:

        oExcel:Quit() .

    end finally.

end method.

 

New Code

method public void Import (poImportWorksheetArguments as ImportWorksheetArguments,

                            OUTPUT TABLE-HANDLE phTempTable):

    define variable oSpreadsheetDocument as SpreadsheetDocument   no-undo.

    define variable oWorkbookPart         as WorkbookPart         no-undo.

    define variable oSheetData         as SheetData           no-undo.

    define variable cFileName             as character           no-undo.

    assign cFileName = poImportWorksheetArguments:FileName .

    oSpreadsheetDocument = this-object:OpenSpreadsheetDocument (cFileName, false).

    oWorkbookPart      = this-object:GetWorkbookPart (oSpreadsheetDocument).

    oSheetData         = this-object:GetWorksheetSheetData(oWorkbookPart).

 

    this-object:ImportRows (oWorkbookPart,

                         oSheetData,

                       poImportWorksheetArguments,

                         output table-handle phTempTable by-reference) .

    finally:

        this-object:CloseSpreadsheetDocument(oSpreadsheetDocument).

        GarbageCollectorHelper:DeleteObject(oSheetData).

        GarbageCollectorHelper:DeleteObject(oWorkbookPart).

        GarbageCollectorHelper:DeleteObject(oSpreadsheetDocument).

    end finally.

end method.

The code highlighted in “red” within the original code highlights what has been replaced, and the code highlighted in “green” within the new code shows what the code has been replaced with.

Notice that here the call to “OpenSpreadsheetDocument” passes the second parameter as “false” meaning that we are opening the Spreadsheet as ‘Read-Only’. For the ‘ImportRows’ we are passing in the ‘SheetData’ as an extra parameter.

ImportRows

The “ImportRows” method is responsible for reading through the Excel Data and importing the Row values into a Temp Table. The two tables below show the original code and the updated code.

Original Code

method protected void ImportRows (poWorksheet as Worksheet,

                     poImportWorksheetArgs as ImportWorksheetArguments,

                     output table-handle phTempTable):

    define variable ifrom     as integer no-undo.

    define variable ito       as integer no-undo.

    define variable iRow   as integer no-undo.

    define variable iCell     as integer no-undo.

    define variable iNumFields as integer no-undo.

    define variable hBuffer     as handle no-undo.

    define variable lEmptyRow   as logical no-undo.

    define variable iEmptyRows as integer no-undo initial 0 .

    define variable oValues   as "System.Object[]":U no-undo .

 

    assign hBuffer   = phTempTable:default-buffer-handle

         iNumFields = hBuffer:num-fields

         ifrom   = poImportWorksheetArgs:SkipRowsFromtop  + 1

        ito = poWorksheet:Rows:Count.

    oValues = {Consultingwerk/new-array.i System.Object iNumFields} .

    do iRow = ifrom to ito:

        assign lEmptyRow = TRUE .

        Consultingwerk.Utilities.Support.ExcelHelper:GetExcelCellValues (poWorksheet,

iRow,

input-output oValues) .

        emptyrowloop:

        do iCell = 1 to iNumFields:

            if string (oValues:GetValue (iCell - 1)) > "":U then do:

                assign lEmptyRow = false .

                leave emptyrowloop .

            end.

        end.

 

        if lEmptyRow then

            iEmptyRows = iEmptyRows + 1 .

 

        if iEmptyRows >= poImportWorksheetArgs:StopAfterEmptyRows then

            return .

        hBuffer:buffer-create () .

        do iCell = 1 to iNumFields on error undo, throw:

            hBuffer:buffer-field (iCell):buffer-value = unbox (oValues:GetValue (iCell - 1)) .

            catch err as Progress.Lang.error:

                undo, throw new ExcelImporterror (err,

                                                  hBuffer:buffer-field (iCell):NAME,

                                       string (unbox (oValues:GetValue (iCell - 1))),

                                                    iRow,

                                       iCell).

            end catch.

        end.

        hBuffer:buffer-release () .

        this-object:RowsImported = this-object:RowsImported + 1 .

        if poImportWorksheetArgs:RaiseProgressChangedAfter <> ? then

            if this-object:RowsImported

MODULO

poImportWorksheetArgs:RaiseProgressChangedAfter = 0

then

                onProgressChanged (Consultingwerk.EventArgs:Empty) .

 

        if poImportWorksheetArgs<> ? and

this-object:RowsImported >= poImportWorksheetArgs:ImportRows then

            return .

    end.

 

    finally:

        if poImportWorksheetArgs:RaiseProgressChangedAfter <> ? then

            onProgressChanged (Consultingwerk.EventArgs:Empty) .

        onCompleted (Consultingwerk.EventArgs:Empty) .

    end finally.

end method.

New Code 

method protected void ImportRows (poWorkbookPart             as WorkbookPart, 

                                                              poSheetData                as SheetData, 

                                                              poImportWorksheetArgs as ImportWorksheetArguments, 

                                                              output table-handle phTempTable): 

    define variable oRowEnumerator as System.Collections.IEnumerator no-undo . 

    define variable iRowIndex               as integer                                                     no-undo. 

    define variable iFrom                       as integer                                             no-undo. 

    define variable iCount                        as integer                                                     no-undo. 

    define variable iVar                              as integer                                                    no-undo. 

    define variable iCell                         as integer                                             no-undo. 

    define variable iNumFields             as integer                                              no-undo. 

    define variable hBuffer                    as handle                                              no-undo. 

    define variable lEmptyRow             as logical                                               no-undo. 

    define variable iEmptyRows           as integer                                              no-undo initial 0 . 

    define variable oValues                  as "System.Object[]":U                    no-undo . 

 

    assign hBuffer                     = phTempTable:default-buffer-handle 

                iNumFields               = hBuffer:num-fields 

                iFrom                        = poImportWorksheetArgs:SkipRowsFromTop  + 1 

                   iVar                                = 64 

                   ColumnReferences = "". 

 

    oValues = {Consultingwerk/new-array.i System.Object iNumFields} . 

     do iCount = 1 to iNumFields: 

        iVar += 1. 

        ColumnReferences = ColumnReferences + chr(iVar) + ",". 

    end. 

    ColumnReferences = right-trim(ColumnReferences, ","). 

    oRowEnumerator = poSheetData:Descendants():GetEnumerator(). 

    do while oRowEnumerator:MoveNext() on error undo, throw: 

        if oRowEnumerator:Current:GetClass():TypeName = get-class(Row):TypeName then do: 

            assign lEmptyRow = true. 

            iRowIndex = UInt32Value:ToUInt32(cast(oRowEnumerator:Current,  

DocumentFormat.OpenXml.Spreadsheet.Row):RowIndex). 

            if iRowIndex ge iFrom then do: 

                ExtractRowValues(poWorkbookPart, 

                                                       cast(oRowEnumerator:Current,

DocumentFormat.OpenXml.Spreadsheet.Row), 

                                                       input-output oValues).