Simplest and efficient method to create excel (.xlsx or .xls) file and export data - Forum - OpenEdge Development - Progress Community

Simplest and efficient method to create excel (.xlsx or .xls) file and export data

 Forum

Simplest and efficient method to create excel (.xlsx or .xls) file and export data

This question is not answered

Dears,

I have a case where I have to write an excel file with the data from a procedure so the excel file will have a main header, column header and column data.

As I am looking for the most efficient and simplest way to get this done, I am looking for your great suggestion for the same.

** this is not about writing csv file

Thank You in advance,

Ithrees

All Replies
  • rayherring

    From what I can tell, DocXFactory appears to be unmaintained? The last GitHub commit was almost 2yrs ago (Jan-2017), and the website is gone (All the downloads were only on the website).

    I believe it is just a wrapper to the Office Open XML specification. The wrapper might not be updated, but I believe the specification is always supported by Microsoft Office, which is independent of the wrapper. You can write a XML formatted Excel file by hand if you need to. 

  • have you ever seen the specification? have you ever tried to create an xlsx or docx document? have you had a look at the project or spoken to someone that used it?

    last time i looked, the specification was 12 thousand pages long.

    besides xlsx and docx are not a flat xml file. there's zip, opc etc.

    but why would anyone manually write/maintain a huge and complex xml file instead of designing the document in office and merging it with a dataset.

  • ithrees

    Thank You so much to all of you guys.

    I'll try both method and check what is feasible for me.

    Also, is it possible to implement the same using COM-HANDLE attributes something like in the following article,

           knowledgebase.progress.com/.../Sample-Code-to-do-Excel-Grouping

    ?

    Thank You & Best Regards,

    Ithrees

    Since you seem to be going the Windows route, is there a reason why you are not looking at the Excel features that come with Infragistics? I think you might find Infragistics.Documents.Excel (and also UltraGrid Excel Export) more than suitable for generating tabular information.  

  • ithrees

    Dears,

    I have a case where I have to write an excel file with the data from a procedure so the excel file will have a main header, column header and column data.

    As I am looking for the most efficient and simplest way to get this done, I am looking for your great suggestion for the same.

    ** this is not about writing csv file

    Thank You in advance,

    Ithrees

    Not sure why last post is not showing.

    Since you seem to be going the Windows route, have you looked at the Infragistics.Document.Excel namespace? It's part of the Infragistics component that comes with PDSOE installation. You can use it to create an Excel file on-the-fly. UltraGrid also has a corresponding Excel export feature, which can export the current view of the grid.  

  • Opensource.

    Do you know where can I download the sourcecode?


  • Von: Marco Mendoza <bounce-cnhcomp25@community.progress.com>
    Gesendet: Donnerstag, 4. Oktober 2018 19:41
    An: TU.OE.Development@community.progress.com
    Betreff: RE: [Technical Users - OE Development] Simplest and efficient method to create excel (.xlsx or .xls) file and export data

    Update from Progress Community
    Marco Mendoza

    Opensource.

    Do you know where can I download the sourcecode?

    View online

     

    You received this notification because you subscribed to the forum.  To unsubscribe from only this thread, go here.

    Flag this post as spam/abuse.

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

  • Im use Infragistics now.

    Old Method:

           DEFINE VARIABLE oBook          As Microsoft.Office.Interop.Excel.Workbook.

           DEFINE VARIABLE oSheet         As Microsoft.Office.Interop.Excel.Worksheet.

           DEFINE VARIABLE colIndex       AS INTEGER.

           DEFINE VARIABLE Rowindex       AS INTEGER.

           Dynamic-qry = hDataset:TOP-NAV-QUERY.

           Dynamic-qry:QUERY-OPEN().

           Dynamic-Bf = Dynamic-qry:GET-BUFFER-HANDLE(1).  

           Dynamic-qry:GET-FIRST.

           CREATE "Excel.Application" oExcel.

           oExcel:SheetsInNewWorkbook = 1.

           oBook = oExcel:Workbooks:Add().

           oSheet = oExcel:sheets:Item(1).

           REPEAT colIndex = 1 TO Dynamic-Bf:NUM-FIELDS:

               oExcel:Cells(1, colIndex):Value  = Dynamic-Bf:BUFFER-FIELD(colIndex):NAME.

               oexcel:Cells(1, colIndex):Borders:LineStyle = 1.

               oexcel:Cells(1, colIndex):Font:Bold = TRUE.

               oexcel:Cells(1, colIndex):Interior:ColorIndex = 35.                                    

           END.

           Rowindex = 1. /* Primer Row es Titulos */

           /* */

           REPEAT:

               Rowindex = Rowindex + 1.

               REPEAT colIndex = 1 TO Dynamic-Bf:NUM-FIELDS:

                   IF Dynamic-Bf:BUFFER-FIELD(colIndex):Type = "DECIMAL"

                       THEN oexcel:Cells(Rowindex, colIndex):Value = string(Dynamic-Bf:BUFFER-FIELD(colIndex):BUFFER-VALUE, ">>,>>>,>>9.9999").

                       ELSE oexcel:Cells(Rowindex, colIndex):Value = string(Dynamic-Bf:BUFFER-FIELD(colIndex):BUFFER-VALUE).

               END.

               Dynamic-qry:GET-NEXT.

               IF Dynamic-qry:QUERY-OFF-END THEN LEAVE.

           END.

           /* Autofit */

           Dynamic-qry:GET-FIRST.

           REPEAT colIndex = 1 TO Dynamic-Bf:NUM-FIELDS:

               oexcel:Cells(1, colIndex):EntireColumn:Autofit().

           END.

           RETURN TRUE.