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

Resolved Saving datawindow to Excel using XLSX!

  1. Issue
0
Votes
Undo
  1. Berit Sandvik
    Berit Sandvik
  2. PowerBuilder
  3. Friday, 3 January 2020 08:01 AM UTC

Hi all,

We have changed from saving datawindows to Excel using Excel8! to XLSX! and as a consequence the column widths in Excel have become extremely wide. The users now have to scroll far to the right in order to see the data and it is very annoying.

We are using PowerBuilder 2019 Cloud edition, build 2082.

Has anybody experienced the same and maybe found a way around this?

Thanks!

 

Berit

Crosstab DW with stored procedure as source
Issue with generateguid in an executable
Who is viewing this page
Anurag Bhandari
Responses (6)
Rolf Knaden
Rolf Knaden Accepted Answer Pending Moderation
  1. Tuesday, 7 January 2020 05:38 AM UTC
  2. PowerBuilder
  3. # 1

Hi All.

I also would prefer the behaviour to be changed to that of Excel8! For that worked well for years.

Regards

Rolf

Comment
There are no comments made yet.
Ricardo Colarina
Ricardo Colarina Accepted Answer Pending Moderation
  1. Monday, 6 January 2020 00:34 AM UTC
  2. PowerBuilder
  3. # 2

Hi Berit,

I had the same issue as well.  As a workaround, I did the following:

Described the datawindow syntax into a variable.

Replaced "char(nn)" with a smaller number for "nn" for all wide columns in the syntax.

Used Create function to create a datastore using the modified DW syntax.

Copied data from DW to DS.

Do SaveAs from the DS.

Cheers,

Ricardo

 

Comment
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Friday, 3 January 2020 16:43 PM UTC
  2. PowerBuilder
  3. # 3

Hi, Berit - 

I suggest using a similar, but alternate DataWindow Object that is tweaked more to your customer's liking, then using ShareData and a DataStore to populate it from the current DataWindow Control without having to copy the values manually. It can then be saved to XLSX format. Very efficient.

Regards,

John

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 3 January 2020 16:19 PM UTC
  2. PowerBuilder
  3. # 4

Hi Berit;

  This is not a bug in the PB product but by design, the way the DW object delivers the XLSX file based on the column width (size) defined in the DWO's column properties.

  For example, if the Address column in the DWO that is being used in the SaveAs() command is Char (100), then the Excel spreadsheet could appear very wide. Especially if the longest address data is only 40 characters.

  To control these wider than required Excel column sizes, please create another DWO with only the columns the business user needs and smaller column widths (number of characters) set to a reasonable size. Then copy the datum from the 1st DWO to a the secondary DWO that has the smaller column sizes. Then perform the SaveAs() on the 2nd DWO (I would suggest housing this in a DataStore). Then the resulting Excel spreadsheet's columns will appear much tighter.

HTH
Regards ... Chris

Comment
Berit Sandvik
  1. Berit Sandvik
  2. Monday, 6 January 2020 07:15 AM UTC
Hi Chris,

Thank you for the explanation.

However, I am not able to see the benefits of setting the Excel column width to the column width of the DWO. A lot of character columns are up to 2000 chars, but are very rarely filled with that much data. Save as Excel is a general feature that has been in our application for years and now we need to add the workaround and build a new release to all of our customers as we have got quite a lot of complaints about this.



I would therefore ask you to consider Olan Knight's suggestion that when Saving As Excel the maximum size of each column's data will be used as the column size. Even better would be to keep the column size as it is for the Excel8! type.



Thank you.



Regards,

Berit
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Monday, 6 January 2020 17:19 PM UTC
Hi Berit;

FWIW: Its *not* the column's "width" in that is used for Excel exportation... its the columns data size. For example Char (2,000) vs Char (150) in the DW Painter's "Column Specification" pane.

HTH ... Regards ... Chris
  1. Helpful
Berit Sandvik
  1. Berit Sandvik
  2. Tuesday, 7 January 2020 07:02 AM UTC
Hi Chris,

I understand that it is the columns data size that is used and that is the problem. If the database column is 2000 char, the data size in the DWO is 2000 chars and the space allocated in the Excel file is corresponding even if the column is empty.



Regards Berit

  1. Helpful
There are no comments made yet.
Berit Sandvik
Berit Sandvik Accepted Answer Pending Moderation
  1. Friday, 3 January 2020 08:55 AM UTC
  2. PowerBuilder
  3. # 5

Miguel Leeuwe,

Thank you for your answer. I will report it as a bug in PowerBuilder to Appeon.

 

Regards Berit

Comment
Olan Knight
  1. Olan Knight
  2. Saturday, 4 January 2020 00:09 AM UTC
As has been said, this is not a bug.



HOWEVER, a nice feature request might be when Saving As Excel that only the maximum size of each column's data be used as the column size when SAVING data to Excel.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 3 January 2020 08:05 AM UTC
  2. PowerBuilder
  3. # 6

If the column widths on the dw itself are not so wide and this worked correctly with older excel export formats, I think you should report this as a bug.

As for a way "around it":

Use OLE with Excel to modify the excel column widths, ideally, grab a macro while selecting all data, then double click on a right border of a column, stop the recording and edit the macro to get the correct syntax.

regards

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.