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

Resolved SaveAS Excel

  1. How-to
0
Votes
Undo
  1. Bjarne Anker
    Bjarne Anker
  2. PowerBuilder
  3. Thursday, 26 March 2020 09:05 AM UTC

Hi.

We have a function that saves the content of a datawindow to Excel using this code:

try

     ll_return = dw_saveas.SaveAs(ls_xls, XLSX!, true)

catch (Throwable th)
    messagebox("",th.GetMessage())

end try

The datawindow has about 180.000 rows, which results in the following exception:

It works just fine with 90.000 rows.

I have the latest version of Excel (32 bit) on my computer.

64 bit is not an option.

 

Any suggetions?

 

br,

 

Bjarne Anker

Maritech Systems AS

  1. Excel
  2. PowerBuilder (Appeon)
How to log out from PB IDE?
Can abybody help me - NativePDF
Accepted Answer
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Thursday, 26 March 2020 10:16 AM UTC
  2. PowerBuilder
  3. # Permalink

Hi Bjarne,

PowerBuilder uses .NET to export the data to XLSX. I guess it is a limitation of this exporter. You should create a support ticket.

As workaround you could export using CSV. If you need it as Excel you can open Excel using OLEObject, import the CSV and save it to XLSX.

HTH,

René

Comment
There are no comments made yet.
Responses (5)
Marc James
Marc James Accepted Answer Pending Moderation
  1. Wednesday, 15 April 2020 19:14 PM UTC
  2. PowerBuilder
  3. # 1

Have you tried dw2xls_pb12 ?

It's a small library you add in to your project, and you can then save dataWindows or dataObjects to excel with many parameters, you can even build complete spreadsheets cell by cell using it.

https://i.ibb.co/kxK5fbX/image.png

Above is just an example of one of many commands it uses.

http://desta.com.ua/dw2xls/index.html

Above is their site

Comment
There are no comments made yet.
Eduardo G.
Eduardo G. Accepted Answer Pending Moderation
  1. Tuesday, 31 March 2020 08:45 AM UTC
  2. PowerBuilder
  3. # 2

I don't think it's a problem, try to open an xlsx file and fill it with information, finally you save it as follows. I hope this will solve it.

oe_ObjetoExcel.ActiveWorkBook.SaveAs( "c:\file.xlsx", 51, "" )

Comment
There are no comments made yet.
Bjarne Anker
Bjarne Anker Accepted Answer Pending Moderation
  1. Thursday, 26 March 2020 13:54 PM UTC
  2. PowerBuilder
  3. # 3

I've tried a lot of things, but the best solution for now is to use dw_data.saveas(file,TXT!,true) if more than 100.000 rows (which is rare anyways). And dw_data.SaveAS(file,XLSX!,true) if less.

Works just fine.

 

A workaround is to compile 64 bit version, but that will probably introduce other unforseen issues in our application.

 

Thanks all :)

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 26 March 2020 13:19 PM UTC
  2. PowerBuilder
  3. # 4

Since you say that Excel 64 bit is not an option, maybe you could set some kind of filter on your datawindow (like rowid < 90000 or something similar?) and then export. After that set the filter to rowid >= 90.000 and do the second export?

I've read that for "later" versions the limits for maximum rows and columns are:

1,048,576 rows by 16,384 columns, unless you're on an older version of Excel.

So maybe this is just a bug that should be reported to Appeon.

 Cheers

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 26 March 2020 13:27 PM UTC
Then on the other hand, could you monitor your memory usage while exporting. You might know that powerbuilder can consume at the most 2GB (3GB if you trick the executable), but even before reaching those limits, you might run into memory problems, as powerbuilders puts memory an a stack that it uses and no one - that I know - knows how that stack is managed. It seems pretty random to me.
  1. Helpful
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.