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
  • Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

  • 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).

  • Or could it be that it has been stable for so long? I've recently implemented it for creating documents, works like a charm.

  • EPPlus works nicely (if you don't mind it being a Windows-only solution). It is efficient, reliable and quite easy to use. 

    https://github.com/JanKallman/EPPlus

    https://www.nuget.org/packages/EPPlus/

    There's a simple example in this thread:

    https://community.progress.com/community_groups/openedge_development/f/19/p/20295/74631#74631

    You need EPPlus.dll in your assemblies folder and a reference to it in assemblies.xml:
    <assembly name="EPPlus"/>

    DocxFactory has an advantage of being cross-platform but I have no experiences with that.

  • Hi ,

    DocxFactory was recently acquired by Akioma. The project will remain opensource!

    We have made tons of updates.

    New website coming soon!

    Thank you,

    Alon

  • 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

  • You can certainly do it using COM-HANDLE stuff, but I wouldn't recommend it when DocxFactory is available. COM-HANDLE is slow, clunky, and prone to errors. Also, it relies on (the correct version of) Excel being installed on the machine you use to produce the files. DocxFactory does not.

  • Thank you for your valuable comments.

  • One additional thing I want to know about these libraries,

    Will there be any issues in printing arabic characters?

    Please advise.

  • Although not free (developer license required), www.spreadsheetgear.com/ is very good. We were having performance issues using the com automation approach, something that was taking hours to complete takes seconds using this library. It also does not require you to have Excel installed for it to work.

  • > On Sep 12, 2018, at 2:54 AM, ithrees wrote:

    >

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

    >

    >

    depending on your requirements, you could generate an HTML file. Excel can read those.

  • > DocxFactory was recently acquired by Akioma. The project will remain opensource!

    > We have made tons of updates.

    Congrats my Friend!!!!!

    The good work has it's rewards!!!!   Bless you!

  • Absolutely!

    There are many Arabic, Persian, Chinese etc. DocxFactory users.

    If you need any help with the installation or binaries, feel free to email me at alonblich@gmail.com

  • Thank you Alon Blich, Its so nice of you.

  • hi ithrees,

    it is quite easy if you have MS EXCEL installed on server or on machine which is running 4GL code.

    Just create a template XLS file with everyting you want like LOGO, colours, heading, footer

    now replace VALUES with mergemarks like change

    Total=100$

    with

    Total=%AMT%

    making sure mergemark name does not conflict with each other

    create a tiny 4GL to read this template, make a copy and reply mergemarks with datavalues

    code snippet -

       CREATE "Excel.Application" chExcelApp.

       ASSIGN chWorkbook1 = chExcelApp:Workbooks:OPEN(MyXLSTemplate).

       ASSIGN chWorkSheet1 = chExcelApp:Sheets:ITEM(1).

       ASSIGN chWorkbook  = chExcelApp:Workbooks:ADD.

       ASSIGN chWorkSheet = chWorkbook:Sheets:ITEM(1).

       chWorkSheet1:COPY (chWorkSheet).

       chWorkbook1:CLOSE(NO).

       ASSIGN chWorkSheet = chWorkbook:Sheets:ITEM(1).

    ...

    ...

       chWorksheet:Range(cRange):VALUE = lcDBValue.