Skip to Content
0
Former Member
Dec 14, 2013 at 02:09 PM

Help with DW and Excel File

202 Views

Hello, I'm trying to export a datawindow to a excel file with 3 worksheets, because an excel file have a limit in rows (version 2007 have a limit of 1048576 rows), my code is ok (in theory) but when I try to export mor than 510000 rows, powerbuilder is close and view a error message.

oleobject myoleobject

integer rc, i, count, j, colcount, rep

string mydata, colname, colnames, ls_select, ls_dwsyntax, ls_err, el

rep = truncate(dw_2.rowcount()/1048575,0)

if mod(dw_2.rowcount(),1048575) > 0 then rep = rep +1

myoleobject = CREATE oleobject

rc = myoleobject.ConnectToNewObject("Excel.Application")

IF rc <> 0 THEN

MessageBox ( "Error", String ( rc ) )

Return

END IF

ls_select = string(dw_2.object.datawindow.table.select)

ls_dwsyntax = SQLCA.SyntaxFromSQL( ls_select, "Style(Type=tabular)", ls_err)

dw_3.Create (ls_dwsyntax, ls_err)

IF ls_err <> '' THEN

MessageBox ("error - Sintaxis", ls_err)

else

dw_3.SetTransObject (SQLCA)

end if

try

myoleobject.Application.Visible = false

myoleobject.Workbooks.Add()

for i = 1 to rep

myoleobject.activeworkbook.sheets(i).name = as_tabla+string(i)//"reporte"+string(i)

dw_3.reset()

dw_2.RowsCopy(((1048574 * (i - 1)) + i), (1048575 * i), Primary!, dw_3, 1, Primary!)

colcount = integer(dw_3.object.DataWindow.column.count)

for j = 1 to colcount

colname = dw_3.describe("#"+string(j)+".Name")

choose case j

case 1

colnames = colname

case else

colnames = colnames + '~t' + colname

end choose

next

colnames = colnames + "~r~n"

mydata = dw_3.object.datawindow.data

clipboard(colnames + mydata)

myoleobject.activeworkbook.sheets(i).paste()

next

//////

for i = 1 to myoleobject.activeworkbook.sheets.count()

el = myoleobject.activeworkbook.sheets(i).name

if el <> as_tabla+string(i) then exit

next

if i = 2 then

myoleobject.activeworkbook.sheets(2).Delete

myoleobject.activeworkbook.sheets(2).Delete

elseif i = 3 then

myoleobject.activeworkbook.sheets(3).Delete

end if

//////

myoleobject.activeworkbook.saveas(as_destino)

myoleobject.activeworkbook.close()

myoleobject.Application.quit

myoleobject.DisconnectObject()

catch ( exception e )

MessageBox ( "Error", e.GetMessage() )

finally

Destroy myoleobject

end try

please explain me the error, sorry for my english.

Thanks,

Frank M. Páucar Cáceres

Attachments

Dibujo.JPG (27.3 kB)