Namespace: Consultingwerk.Utilities.Excel
Class 
ExcelUtility Copy to Clipboard
Parent classes:
Progress.Lang.Object
Consultingwerk.Utilities.Excel.ExcelStyleSheetUtility
Consultingwerk.Utilities.Excel.ExcelUtility

Inherits: Consultingwerk.Utilities.Excel.ExcelStyleSheetUtility
Inherited by: Consultingwerk.Utilities.Excel.ExcelWorksheetExporter Consultingwerk.Utilities.Excel.ExcelWorksheetImporter


Navigate to: Methods | Constructors | Events | Properties | ProDatasets | Temp-Tables



Top Method Summary
Options Name Purpose
CloseSpreadsheetDocument (SpreadsheetDocument) Closes the SpreadsheetDocument by calling the Dispose method.
GenerateRowData (WorkbookPart, SheetData, integer, Object[], Object[], logical, integer) Generates a new Row with Cell Values based on the provided values Object[]
CHARACTER GetCellValue (WorkbookPart, Cell) Gets and returns the Value of a Cell
DocumentFormat.OpenXml.Spreadsheet.Row GetHeaderRow (SheetData) Gets the header row from the Excel Worksheet
DocumentFormat.OpenXml.Spreadsheet.Cell GetRowCell (Row, character) Gets the Cell Object from a Row
DocumentFormat.OpenXml.Spreadsheet.Sheet GetSheet (WorkbookPart, character) Gets and returns the Sheet of the specified WorkbookPart
DocumentFormat.OpenXml.Spreadsheet.Sheet GetSheet (WorkbookPart, integer) Gets and returns the Sheet of the specified WorkbookPart
DocumentFormat.OpenXml.Packaging.WorkbookPart GetWorkbookPart (SpreadsheetDocument) Gets and returns the WorkbookPart of the specified SpreadsheetDocument
DocumentFormat.OpenXml.Packaging.WorksheetPart GetWorksheetPart (WorkbookPart) Gets and returns the WorksheetPart of the specified WorkbookPart
DocumentFormat.OpenXml.Spreadsheet.SheetData GetWorksheetSheetData (WorkbookPart) Gets and returns the WorksheetSheetData of the specified WorkbookPart
DocumentFormat.OpenXml.Spreadsheet.SheetData GetWorksheetSheetData (WorkbookPart, integer, character) Gets and returns the WorksheetSheetData of the specified WorkbookPart
InsertCellValue (WorksheetPart, character, integer, character, integer) Inserts a Cell Value into the indexed Row and Column of an Excel Worksheet
InsertHeaderRow (WorkbookPart, SheetData, integer, Object[], integer) Inserts a header row into the Excel Worksheet
DocumentFormat.OpenXml.Packaging.SpreadsheetDocument OpenSpreadsheetDocument (character, logical) Opens the SpreadsheetDocument
PostProcessDateStyles (character) Post-processes an xlsx file to fix OpenXML SDK re-serialization issues
ProcessStyleSheet (SpreadsheetDocument, ExportWorksheetArguments) Processes the Stylesheet for the Excel Stylesheet
ReadCellFormatCount (character) Reads the cellXfs count from an xlsx file using raw Package API
SaveSpreadsheetDocument (SpreadsheetDocument) Saves and closes the SpreadsheetDocument
SaveWorkbook (WorkbookPart) Saves the Workbook element of the specified WorkbookPart
SetExcelCellValues (WorksheetPart, integer, Object[], integer) Sets the Excel Cell Values based on the provided values Object[]
SetStylesheetProperties (ExportWorksheetArguments) Sets the StylesheetProperties
ThrowException (character) Throws an exception with the specified error text
UpdateRowData (Row, Object[], integer) Updates a Row Cell Values based on the provided values Object[]


Method Detail
Top

CloseSpreadsheetDocument (SpreadsheetDocument)
/*
Purpose: Closes the SpreadsheetDocument by calling the Dispose method.
Notes: The Dispose is important to release the memory and close the file.
(otherwise the file will be locked and cannot be opened)

Parameters:
poSpreadsheetDocument DocumentFormat.OpenXml.Packaging.SpreadsheetDocument
Top

GenerateRowData (WorkbookPart, SheetData, integer, Object[], Object[], logical, integer)
/*
Purpose: Generates a new Row with Cell Values based on the provided values Object[]
Notes: The oCellElement variable is initialized with a fixed size of 40. This is because
the extent cannot be set dynamically

Parameters:
poWorkbookPart DocumentFormat.OpenXml.Packaging.WorkbookPart
poSheetData DocumentFormat.OpenXml.Spreadsheet.SheetData
piRowIndex INTEGER
poValues System.Object[]
poDataTypes System.Object[]
plHeader LOGICAL
piStyleIndex INTEGER
Top

CHARACTER GetCellValue (WorkbookPart, Cell)
/*
Purpose: Gets and returns the Value of a Cell
Notes: The DataType of the Cell is used to determine the type of the value.
DataType values can be found at https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.cellvalues?view=openxml-3.0.1

Parameters:
poWorkbookPart DocumentFormat.OpenXml.Packaging.WorkbookPart
poCell DocumentFormat.OpenXml.Spreadsheet.Cell
Returns CHARACTER
Cell Value
Top

DocumentFormat.OpenXml.Spreadsheet.Row GetHeaderRow (SheetData)
Purpose: Gets the header row from the Excel Worksheet
Notes: The Row object is fully class defined otherwise the Type is not correctly
defined (Can conflict with WordDocument.Row or Drawing.Row).

Parameters:
poSheetData DocumentFormat.OpenXml.Spreadsheet.SheetData
Returns DocumentFormat.OpenXml.Spreadsheet.Row
Row Object
Top

DocumentFormat.OpenXml.Spreadsheet.Cell GetRowCell (Row, character)
/*
Purpose: Gets the Cell Object from a Row
Notes:

Parameters:
poRow DocumentFormat.OpenXml.Spreadsheet.Row
pcCellReference CHARACTER
Returns DocumentFormat.OpenXml.Spreadsheet.Cell
Cell Object
Top

DocumentFormat.OpenXml.Spreadsheet.Sheet GetSheet (WorkbookPart, character)
/*
Purpose: Gets and returns the Sheet of the specified WorkbookPart
Notes:

Parameters:
poWorkbookPart DocumentFormat.OpenXml.Packaging.WorkbookPart
pcSheetName CHARACTER
Returns DocumentFormat.OpenXml.Spreadsheet.Sheet
Sheet Object
Top

DocumentFormat.OpenXml.Spreadsheet.Sheet GetSheet (WorkbookPart, integer)
/*
Purpose: Gets and returns the Sheet of the specified WorkbookPart
Notes:

Parameters:
poWorkbookPart DocumentFormat.OpenXml.Packaging.WorkbookPart
piSheetIndex INTEGER
Returns DocumentFormat.OpenXml.Spreadsheet.Sheet
Sheet Object
Top

DocumentFormat.OpenXml.Packaging.WorkbookPart GetWorkbookPart (SpreadsheetDocument)
/*
Purpose: Gets and returns the WorkbookPart of the specified SpreadsheetDocument
Notes:

Parameters:
oSpreadsheetDocument DocumentFormat.OpenXml.Packaging.SpreadsheetDocument
Returns DocumentFormat.OpenXml.Packaging.WorkbookPart
WorkbookPart Object
Top

DocumentFormat.OpenXml.Packaging.WorksheetPart GetWorksheetPart (WorkbookPart)
/*
Purpose: Gets and returns the WorksheetPart of the specified WorkbookPart
Notes:

Parameters:
poWorkbookPart DocumentFormat.OpenXml.Packaging.WorkbookPart
Returns DocumentFormat.OpenXml.Packaging.WorksheetPart
WorksheetPart Object
Top

DocumentFormat.OpenXml.Spreadsheet.SheetData GetWorksheetSheetData (WorkbookPart)
/*
Purpose: Gets and returns the WorksheetSheetData of the specified WorkbookPart
Notes:

Parameters:
poWorkbookPart DocumentFormat.OpenXml.Packaging.WorkbookPart
Returns DocumentFormat.OpenXml.Spreadsheet.SheetData
SheetData Object
Top

DocumentFormat.OpenXml.Spreadsheet.SheetData GetWorksheetSheetData (WorkbookPart, integer, character)
/*
Purpose: Gets and returns the WorksheetSheetData of the specified WorkbookPart
Notes:

Parameters:
poWorkbookPart DocumentFormat.OpenXml.Packaging.WorkbookPart
piSheetIndex INTEGER
pcSheetName CHARACTER
Returns DocumentFormat.OpenXml.Spreadsheet.SheetData
SheetData Object
Top

InsertCellValue (WorksheetPart, character, integer, character, integer)
Purpose: Inserts a Cell Value into the indexed Row and Column of an Excel Worksheet
Notes:

Parameters:
poWorksheetPart DocumentFormat.OpenXml.Packaging.WorksheetPart
WorksheetPart
pcContent CHARACTER
Character The content to insert
piRowIndex INTEGER
Integer The row to insert into
pcCol CHARACTER
Character The column to insert into
piStyleIndex INTEGER
Integer The style index to apply
Top

InsertHeaderRow (WorkbookPart, SheetData, integer, Object[], integer)
Purpose: Inserts a header row into the Excel Worksheet
Notes:

Parameters:
poWorkbookPart DocumentFormat.OpenXml.Packaging.WorkbookPart
WorkbookPart
poSheetData DocumentFormat.OpenXml.Spreadsheet.SheetData
SheetData
piRowIndex INTEGER
Integer The row to insert into
poValues System.Object[]
Object[] The values to insert
piStyleIndex INTEGER
Integer The style index to apply
Top

DocumentFormat.OpenXml.Packaging.SpreadsheetDocument OpenSpreadsheetDocument (character, logical)
/*
Purpose: Opens the SpreadsheetDocument
Notes: The second attribute is optional and defaults to false. If set to true the document will be opened in update mode.

Parameters:
pcFileName CHARACTER
plEditable LOGICAL
Returns DocumentFormat.OpenXml.Packaging.SpreadsheetDocument
SpreadsheetDocument Object
Top

PostProcessDateStyles (character)
Purpose: Post-processes an xlsx file to fix OpenXML SDK re-serialization issues
Notes: The OpenXML SDK re-serializes loaded parts with x: namespace prefix and
t="str" for string cells, which causes Excel to report corruption.
This method uses the raw System.IO.Packaging API to:
1. Inject date/datetime CellFormats into styles.xml
2. Strip x: namespace prefix from workbook.xml and worksheet XML
3. Convert t="str" cells to t="inlineStr" with proper structure
Must be called AFTER the OpenXML SDK has saved and closed the file.

Parameters:
pcFileName CHARACTER
The file name of the Excel file
Top

ProcessStyleSheet (SpreadsheetDocument, ExportWorksheetArguments)
Purpose: Processes the Stylesheet for the Excel Stylesheet
Notes:

Parameters:
poSpreadsheetDocument DocumentFormat.OpenXml.Packaging.SpreadsheetDocument
poExportWorksheetArguments Consultingwerk.Utilities.Excel.ExportWorksheetArguments
Top

ReadCellFormatCount (character)
Purpose: Reads the cellXfs count from an xlsx file using raw Package API
Notes: Must be called BEFORE the OpenXML SDK opens the file. Sets
DateStyleIndex and DateTimeStyleIndex based on current count.

Parameters:
pcFileName CHARACTER
The file name of the Excel file
Top

SaveSpreadsheetDocument (SpreadsheetDocument)
/*
Purpose: Saves and closes the SpreadsheetDocument
Notes:

Parameters:
poSpreadsheetDocument DocumentFormat.OpenXml.Packaging.SpreadsheetDocument
Top

SaveWorkbook (WorkbookPart)
/*
Purpose: Saves the Workbook element of the specified WorkbookPart
Notes:

Parameters:
poWorkbookPart DocumentFormat.OpenXml.Packaging.WorkbookPart
Top

SetExcelCellValues (WorksheetPart, integer, Object[], integer)
Purpose: Sets the Excel Cell Values based on the provided values Object[]
Notes:

Parameters:
poWorksheetPart DocumentFormat.OpenXml.Packaging.WorksheetPart
WorksheetPart
piCount INTEGER
Integer The row to insert into
poValues System.Object[]
Object[] The values to insert
piStyleIndex INTEGER
Integer The style index to apply
Top

SetStylesheetProperties (ExportWorksheetArguments)
Purpose: Sets the StylesheetProperties
Notes:

Parameters:
poExportWorksheetArguments Consultingwerk.Utilities.Excel.ExportWorksheetArguments
ExportWorksheetArguments
Top

ThrowException (character)
/*
Purpose: Throws an exception with the specified error text
Notes:

Parameters:
pcErrorText CHARACTER
The error text to throw
Top

UpdateRowData (Row, Object[], integer)
Purpose: Updates a Row Cell Values based on the provided values Object[]
Notes:

Parameters:
poRow DocumentFormat.OpenXml.Spreadsheet.Row
poValues System.Object[]
piStyleIndex INTEGER


©2006-2026 Consultingwerk Ltd.         info@consultingwerk.de         http://www.consultingwerk.de       13.04.2026 10:30:51