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
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 [mention:77cbb45621a246f9a1a1f2f378e535b1:e9ed411860ed4f2ba0265705b8793d05] 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.
[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.
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.
[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.
[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.
Opensource.
Do you know where can I download the sourcecode?
|
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.