1. Winnie Ross
  2. PowerBuilder
  3. Wednesday, 9 March 2022 14:54 PM UTC

How do you copy texts from multiple columns in Excel to a datawindow with multiple columns?

Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 9 March 2022 15:31 PM UTC
  2. PowerBuilder
  3. # 1

Hi Winnie;

  If the Excel spreadsheet is not complex, then I use Excel to export the data as (for example) as a .CSV file. Then I use the DW Control's ImportFile() command to import the CSV datum into the DWO's primary buffer.  HTH

Regards ... Chris

Comment
  1. Winnie Ross
  2. Wednesday, 9 March 2022 19:56 PM UTC
Thanks, Chris. I am actually trying to go to the route.

I thought there's a way to to do copy and paste from Excel directly to a datawindow.

I am able to copy one column in Excel and paste to a datawindow column, but I would like to copy/paste multiple columns from Excel to datawindow.



Thanks,

Winnie
  1. Helpful
  1. Chris Pollach @Appeon
  2. Wednesday, 9 March 2022 20:01 PM UTC
You could do that via using OLE to talk directly to Excel to extract the contents of each cell and then drive that information into a DWO's primary buffer via a SetItem() command. However, that would involve some definitely challenging PowerScript coding.
  1. Helpful
  1. Olan Knight
  2. Saturday, 26 March 2022 01:17 AM UTC
"DW.ImportFile ()" is the way, assuming you can simplify the worksheet sufficiently.
  1. Helpful
There are no comments made yet.
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Thursday, 10 March 2022 09:16 AM UTC
  2. PowerBuilder
  3. # 2

Here is a sample code to read excel data

OLEObject xlapp, xlsub
String    ls_filename = 'one.xls'
Long      ll_sheet, ll_max_rows, ll_max_columns, ll_start, ll_end

ll_start = 1
ll_end = 500      // To read 500 rows

// Create OLE 
xlApp = Create OLEObject

ll_ret = xlApp.ConnectToNewObject( "Excel.Application" )
If ll_ret < 0  then
    Return
End If

try
        // Open Excel file
	xlApp.Application.Workbooks.Open(ls_filename) 

        // Open Active sheet
        ll_sheet = 1
	xlsub = xlapp.Application.ActiveWorkbook.Worksheets[ll_sheet]
	ll_max_rows     = Xlsub.UsedRange.Rows.Count
	ll_max_columns = Xlsub.UsedRange.Columns.Count
	
	If ll_start = 1 Then ll_start = 2
	
	
	dw_1.SetReDraw(False)
	
	For ll_cnt = ll_start to ll_end
		ll_row = tab_1.tabpage_1.dw_1.InsertRow(0)
		ll_inc ++
		dw_1.SetItem(ll_row, 'field1', Mid(Trim(String(xlsub.cells[ll_cnt, 1].value)),1,20))
		dw_1.SetItem(ll_row, 'field2', Mid(Trim(String(xlsub.cells[ll_cnt, 2].value)),1,10))
		dw_1.SetItem(ll_row, 'field3', Mid(Trim(String(xlsub.cells[ll_cnt, 3].value)),1,3))
		Yield()
	Next
	dw_1.SetReDraw(True)

CATCH ( runtimeerror  lo_rte)
	MessageBox('Error', "MS Excel api runtime error")
FINALLY   
	 // Quit
	XlApp.Workbooks.close()
	xlapp.quit()
	xlapp.DisconnectObject()
	tab_1.tabpage_1.dw_1.SetReDraw(True)

	 Destroy xlsub
	 Destroy xlapp
	GarbageCollect()	 
END TRY

HTH

Happiness Always
BKR Sivaprakash

 

Comment
  1. Sivaprakash BKR
  2. Friday, 11 March 2022 10:09 AM UTC
Winnie,

Btw, do you see any limitations on this 'clipboard' copy method? There seems to a limit in the no. of rows that one can process by opening the excel file through OLE object. Reading 25000 rows isn't a problem, and above that the software terminates. My application is 32bit.

  1. Helpful
  1. Winnie Ross
  2. Thursday, 24 March 2022 18:55 PM UTC
BKR,

I am not aware of any limitation on the no. of rows. For what I'm using it for, we will typically have maybe a hundred or less records and 3 columns to copy and paste. My application is 32 bit as well.



Winnie
  1. Helpful
  1. Sivaprakash BKR
  2. Friday, 25 March 2022 12:51 PM UTC
Thanks Winnie for the clarification.
  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.