Simplest and efficient method to create excel (.xlsx or .xls

Posted by ithrees on 12-Sep-2018 01:52

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

Posted by Mike Fechner on 12-Sep-2018 02:08
Posted by rayherring on 12-Sep-2018 02:12

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

Posted by Richard.Kelters on 12-Sep-2018 02:36

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

Posted by Marko Myllymäki on 12-Sep-2018 02:56

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.

Posted by Alon Blich on 12-Sep-2018 03:12

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

Posted by ithrees on 12-Sep-2018 04:56

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

Posted by James Palmer on 12-Sep-2018 05:02

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.

Posted by ithrees on 12-Sep-2018 05:56

Thank you [mention:77cbb45621a246f9a1a1f2f378e535b1:e9ed411860ed4f2ba0265705b8793d05] for your valuable comments.

Posted by ithrees on 12-Sep-2018 09:16

One additional thing I want to know about these libraries,

Will there be any issues in printing arabic characters?

Please advise.

Posted by oedev on 12-Sep-2018 09:21

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.

Posted by gus bjorklund on 12-Sep-2018 11:52

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

Posted by OctavioOlguin on 12-Sep-2018 12:26

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

Posted by Alon Blich on 13-Sep-2018 02:22

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

Posted by ithrees on 13-Sep-2018 04:29

Thank you Alon Blich, Its so nice of you.

Posted by avtar.jain on 13-Sep-2018 05:27

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.

Posted by jquerijero on 24-Sep-2018 14:41

[quote user="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).

[/quote]

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. 

Posted by Alon Blich on 25-Sep-2018 03:07

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.

Posted by jmc12 on 25-Sep-2018 04:23
Posted by jquerijero on 03-Oct-2018 14:07

[quote user="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

[/quote]

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.  

Posted by jquerijero on 03-Oct-2018 14:36

[quote user="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

[/quote]

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.  

Posted by Marco Mendoza on 04-Oct-2018 12:38

Opensource.

Do you know where can I download the sourcecode?

Posted by Mike Fechner on 04-Oct-2018 12:45

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.

Posted by integrasoftware on 11-Oct-2018 06:35

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.

  

This thread is closed