Hi everyone,
I am currently working with a project where i plan to use the "Open XML SDK 2.0 for Microsoft Office" components in OpenEdge.
From Microsoft example code i tested the method pasted below, and this works fine (using VS C# IDE).
My problem is how to convert this code into OpenEdge syntax.
Any help appreciated?
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
public static void CreateSpreadsheetWorkbook(string filepath)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
sheets.Append(sheet);
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
10.2B? GUI Client? Be aware that the use of .NET Classes is restricted to the GUI Client.
Check the GUI for .NET mapping reference (part of the PDF docs) for help i translating this C# to ABL code.
Statements end with . not ;
Object reference and member (method / property) are separated by : not . As in C#
Most of it is straight forward...
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
will become
DEFINE VARIABLE spreadsheetDocument AS SpreadsheetDocument NO-UNDO.
spreadSheetDocument = SpreadsheetDocument.Create (file path, SpreadsheetDocumentType.Workbook).
And so on.... However, I recommend using a different name for the Variable, as the ABL is not case-sensitive as C# is.
OpenEdge 10.2B .
This is intended to end up as a procedure or class that will be used for several purposes. First use will be to use it in a p-file with a few input parameters including parameter containing path to a CSV file (containing data), as well as parameters containing path and name to the resulting Excel file, p-file returning status of operation.
This is how far i got in using this .NET component in OpenEdge(see below), managed to create the xlsx-file but the file can not be openend in Excel. I have not been able to create code that insert sheets..
USING Progress.Lang.*.
USING DocumentFormat.OpenXml.*.
USING DocumentFormat.OpenXml.Packaging.*.
USING DocumentFormat.OpenXml.Spreadsheet.*.
DEFINE VARIABLE wSpreadsheetDocument AS SpreadsheetDocument NO-UNDO.
DEFINE VARIABLE wSheet AS Sheet NO-UNDO.
DEFINE VARIABLE wSheets AS Sheets NO-UNDO.
DEFINE VARIABLE wWorkbookpart AS WorkbookPart NO-UNDO.
DEFINE VARIABLE wWorksheetPart AS WorksheetPart NO-UNDO.
DEFINE VARIABLE wSheetData AS SheetData NO-UNDO.
DEFINE VARIABLE wCustomXmlPartType AS DocumentFormat.OpenXml.Packaging.CustomXmlPartType NO-UNDO.
wspreadsheetDocument = SpreadsheetDocument:Create("h:\testno.xlsx", DocumentFormat.OpenXml.SpreadsheetDocumentType:Workbook).
wWorkbookpart = wspreadsheetDocument:AddWorkbookPart().
wSheet = NEW DocumentFormat.OpenXml.Spreadsheet.Sheet().
...
wspreadsheetDocument:Close().
I would check out http://www.oehive.org/project/libooxml
I have not been able to create code that insert sheets..
I guess this is the code that's failing, right?
// Add Sheets to the Workbook. Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets()); Did you try the Append method instead of AppendChild? See http://msdn.microsoft.com/en-us/library/cc819854.aspx ABL supports .NET generic types in 10.2B. But I'm not sure, if the ABL can handle a generic method in a non generic class... I'm sure Shelley or someone else from the Dev team can enlight us here.
Mike is correct that you cannot call a generic method using ABL.
Starting with 10.2B03 you can use .NET in any client - not just a GUI client. AppServer support for .NET is on our roadmap but it is not committed to a release at this time.
-Shelley
At this time the functionality which Shelley has described is expected to be part of the version 11.0 release (Additionally, it will also be supported in the Windows character client)
At this time the functionality which Shelley has described is expected to be part of the version 11.0 release (Additionally, it will also be supported in the Windows character client)
Cool, and now that you have .NET support in the character client, .NET support for AppServers can't be miles away, or?
Hi,
seems like we in this project will go for a solution where we make a dll in C# (this dll handles all the Open XML SDK ... Excel activity), installs this in Global Assembly Cache and adds it to Referenced Assemblies in OpenEdge Architect.
Thank you for all informative help!