Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved Show Display Column when saving in Excel.

  1. How-to
0
Votes
Undo
  1. Help Desk Constantí
    Help Desk Constantí
  2. PowerBuilder
  3. Friday, 22 March 2019 14:33 PM UTC

Hello everybody.

I have PowerBuilder 8 and I'm migrating to 2017 R3.

Someone could indicate me If there is an easy way to show the Display Column of the Code Table when I save a datawindow as Excel? Now in PowerBuilder 8 is it possible? I have 1 column with Code Table, and when I save in Excel this column show de value saved on the database, but I need to see the Display Column.

Maybe the solution it's only in the newest version of PowerBuilder?

 

Thanks in advance.

  1. PowerBuilder
Migrating a .NET Soap client web service project t...
System Error Encountered: Error Opening PBSHELL.dl...
Accepted Answer
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 25 March 2019 17:24 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi;

  Its pretty easy ... you just have to remember that the SaveAs () exports the data from the current DWO's "primary buffer" As Is. So, just create an External DWO (for example) and transfer the datum from the current DWO to the external DWO's primary buffer. For coded columns of DDLB / DDDW use a LookupDisplay() PowerScript command to get the current "display" value instead of the code and then transfer that to the external DWO.

  In the external you could even create all "String" type columns and then use the String (, ) command to do some more "pre-formatting" before you DS.SaveAs (Excel!).

Food for thought.

Regards ... Chris

Comment
Help Desk Constantí
  1. Help Desk Constantí
  2. Wednesday, 27 March 2019 11:11 AM UTC
Hi, Thanks a lot Chris and Marco.



Finally I used the external dwo.

To help other users, this is the exact line used to found the Display Value:

For i_row = 1 to dw_datos.RowCount()

dw_excel.SetItem(i_row, "dest_column", dw_datos.Describe("Evaluate('LookUpDisplay(orig_column)', " + String(i_row) + ")"))



dw_excel is the external dwo, and dw_datos is the original datawindow.



Regards.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 27 March 2019 13:50 PM UTC
That is excellent news ... glad that my suggestion worked for you! :-)
  1. Helpful
There are no comments made yet.
Responses (5)
Marco Meoni
Marco Meoni Accepted Answer Pending Moderation
  1. Monday, 25 March 2019 15:23 PM UTC
  2. PowerBuilder
  3. # 1

btw,

since HTMLTable didn't fit your expectation, I didn't mention this beautiful library, by it would be yet another option:

https://community.appeon.com/index.php/articles-blogs/tips-tricks-techniques-articles/17-powerbuilder/190-exporting-datawindows-to-excel-html-without-loosing-format

Best,

.m

Comment
There are no comments made yet.
Marco Meoni
Marco Meoni Accepted Answer Pending Moderation
  1. Monday, 25 March 2019 15:18 PM UTC
  2. PowerBuilder
  3. # 2

Hi, if "simple" solution means one line of code you are out of luck :-)

But with just one loop you can kinda sort it out:

1) Solution 1, OLE object (writing down code by pure memory, please verify syntax)


lole_excel = create oleobject
lole_excel.ConnectToNewObject("excel.application")

lole_excel.WorkBooks.Open("dw.xls")
lole_workbook = lole_excel.application.workbooks(1)
lole_worksheet = lole_workbook.worksheets(1)

For i = 1 To li_columnscount
   // Set the cell value, they are on first row
   lole_worksheet.cells(1, i).value = ls_displayColumns[i]
Next

lole_workbook.save()
lole_excel.application.quit()
lole_excel.DisconnectObject()

2) Solution 2, copy your DW to a second temporary external DW, and set display names on first row. If needed (e.g. number of columns not predictable beforehand), create the DW from syntax.

Best,

.m

Comment
There are no comments made yet.
Help Desk Constantí
Help Desk Constantí Accepted Answer Pending Moderation
  1. Monday, 25 March 2019 12:59 PM UTC
  2. PowerBuilder
  3. # 3

Hello Marco,

 

I tried the HtmlTable!, but the format is not useful in our case.

 

I was looking for a simple solution. I supose that Ole automation may be a little bit complicated.

 

Thanks, 

Comment
There are no comments made yet.
Marco Meoni
Marco Meoni Accepted Answer Pending Moderation
  1. Friday, 22 March 2019 15:28 PM UTC
  2. PowerBuilder
  3. # 4

Hello,

you need to use OLE Automation to access individual cells and replace table names with display names.

Or, with below simple change, you will both improve the document look&feel and have display names.

Replace

dw_1.SaveAs('dw.xls', Excel!, True)

with

dw_1.SaveAs('dw.xls', HtmlTable!, True)

Best,

.m

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.