Export composite datawindow to excel

[Former Member]

Hi All,

(Powerbuilder 12.5 classic)

I have problem to export composite datawindow to excel, where it always results an 'a' in the A1 cell of Excel as below:

Save as function used = dw_1.saveas("excelreport.xlsx",XLSX! ,true)

- This save as function is working fine it there is only one datawindow to export to excel, example: export a tabular datawindow.

I tried dw_1.saveas("excelreport.xlsx",HTMLTable! ,true) also, but still not working.

Kindly advise.

Thank you.

Regards,

Yow

[Former Member · Accepted solution]

Hi Yow;

  The SaveAs ( ) method only works on the immediate parent DW Object within the DW Control. Since a composite DW has many child DWO's inside it, your SaveAs command is not addressing the children.

  Currently, this is the limitation of the SaveAs command for either a Nested or Composite DW. You do have a few other options ...

1) Create a Tabular or Grid DW that houses all the columns that you would like the Excel spreadsheet to see. Then populate this DW inside a hidden DataStore separately when the user requests an export of the composite DW to Excel.

2) You can access a composite DW's entire data buffers by using the DW "DOT" notation. You would have to code something like this or each child DW in the composite DW ....

  Any  la_data[]

  ls_data =  DC.Object.<ChildDW>.Object.Data

  Once you have extracted all the child DWO's data - you could then export it to Excel using the procedure stated in #1 or using OLE interaction with Excel. Either way, definitely some significant coding work on your part.

Regards ... Chris

[Former Member]

Actually there is way to do this that should be much simpler.

When you use SaveAs - save it as an XML file type.  When you go into excel

and try to open the xml file you get a couple of dialog boxes you can just say Yes to

and then the data is imported with the column names as headers.

You get all the column names from nested reports and it can be a bit

of a mess, but the data is there, the column names are there and it is usable

after a bit of cleanup.

I also noticed some of the computed columns might be off / missing, but you

could probably fix that by adding a 'dummy/blank column to your select statement

i.e. select dummy column as 'BLANK'   then in your dw set the dummy column to

the value of the computed field (not sure if this would work or not have not tried it)

Also you could probably get cleaner spreadsheet if you used an xml template (can specify one

in the dw properties) but I havent used xml much so not sure how that all works.

The 'general' way above works good enough for most situations.

[Former Member]

I don't know a native way. You may export the data of a nested datawindow but not the composite dw.

Look for dw2xls as an external solution: Dw2xls - PowerBuilder library for converting datawindow to Excel