MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.


I'm not curious

Import Data From Excel to Datawindow in PowerBuilder

Published on 04 November 17
2255
0
0
Some times users want to upload their reports to PowerBuilder applications. Generally They will have the data in the form of Excel/CSV. We can directly import the data into the Datawindow from Excel/CSV. Lets see how we can achieve this.
Points To Be Noted:
1/ When Importing the data from excel files to datawindow, the datawindow columns and their respective datatypes should match the excel files cells data
2/ If any mismatch is there we will get the "Item does not pass validation Error"

Steps:
1/ Create Datawindow (dw_1)
DataSource Of Data Window :
SELECT import_emp.place,import_emp.name,import_emp.phone,import_emp.initial FROM import_emp

2/ Have an Excel sheet with the column names in in the first row, Data in the next rows
Import Data From Excel to Datawindow in PowerBuilder - Image 1
3/ Now set the transaction object for datawindow
4/ Place a Button on window. (Assuming that the datawindow is on a window. )
5/ Write the below code in the Clicked event of the Button
OLEObject excel

Integer li_RetValue, li_rtn
Boolean lb_sheet_rtn
Long ll_cnt
String ls_ClipBoard

// Varibles for File Name,path

string docpath, docname[]
integer i, li_cnt, li_filenum
long ll_xls_cols,ll_xls_rows

// Get file path

GetFileOpenName("Select File", docpath, docname[], "DOC", "Excel Files (*.xlsx),*.xlsx," + "All Files (*.*), *.*", "C:\Program Files\Sybase", 18)

if docpath='' then return

//MessageBox("Document Path ", docpath)

excel = create OLEObject

li_rtn = excel.ConnectToNewObject("excel.application")
IF li_rtn <> 0 THEN
MessageBox('Excel error','can not run Excel Program')
DESTROY excel
RETURN 0
END IF

excel.WorkBooks.Open(docpath )
excel.Application.Visible = false
excel.windowstate = 2 // 1 : Normal, 2 : Minimize, 3 : Maximize

lb_sheet_rtn = excel.worksheets(1).Activate

ls_ClipBoard = Clipboard() //Store current clipboard Data

// To Find the No Of Rows and Columns in the excel sheet

ll_xls_cols = excel.worksheets(1).Usedrange.columns.count
ll_xls_rows = excel.worksheets(1).Usedrange.rows.count
ll_xls_rows = ll_xls_rows -1 // Considering row 1 has headers

MessageBox("Rows and Cols In the Excel",string(ll_xls_rows)+ " Rows and "+string(ll_xls_cols)+" Columns Will be imported to application")

// To get the value of a cell in the excel sheet
// You can do your validations on the Excel Cells data here based on your business requirements
MessageBox("A1 cell value",string(excel.worksheets(1).cells(1,1).value)) // First Row First Column in the excel sheet
MessageBox("A2 cell value",string(excel.worksheets(1).cells(2,1).value)) // First Row First Column in the excel sheet

excel.Worksheets(1).Range("A2:D5").Copy // I have hard coded the Cells here your can change by using no of rows and columns and concatinating them
// copy to clipboard - Take enough Range. .. Or use the UsedRage property. If data ha header row, use correct statring range.. (B1:... )
ll_cnt = dw_1.importclipboard()

Clipboard(ls_ClipBoard) //Restore clipboard

IF ll_cnt <= 1 THEN
Messagebox("Information", "Unable to Load the data")
END IF
excel.Application.Quit
excel.DisConnectObject()
DESTROY excel
Output:
i/ Run the application
ii/ Click the Import Button
iii/ Select the file
iv/ open
Import Data From Excel to Datawindow in PowerBuilder - Image 2
Import Data From Excel to Datawindow in PowerBuilder - Image 3
Import Data From Excel to Datawindow in PowerBuilder - Image 4
Import Data From Excel to Datawindow in PowerBuilder - Image 5
Import Data From Excel to Datawindow in PowerBuilder - Image 6
“Right Knowledge is the Ultimate Solution to All Our Problems - BG”
Some times users want to upload their reports to PowerBuilder applications. Generally They will have the data in the form of Excel/CSV. We can directly import the data into the Datawindow from Excel/CSV. Lets see how we can achieve this.Points To Be Noted:

1/ When Importing the data from excel files to datawindow, the datawindow columns and their respective datatypes should match the excel files cells data

2/ If any mismatch is there we will get the "Item does not pass validation Error"

Steps:

1/ Create Datawindow (dw_1)

DataSource Of Data Window :

SELECT import_emp.place,import_emp.name,import_emp.phone,import_emp.initial FROM import_emp

2/ Have an Excel sheet with the column names in in the first row, Data in the next rows

Import Data From Excel to Datawindow in PowerBuilder - Image 1

3/ Now set the transaction object for datawindow4/ Place a Button on window. (Assuming that the datawindow is on a window. )

5/ Write the below code in the Clicked event of the Button

OLEObject excel

Integer li_RetValue, li_rtn

Boolean lb_sheet_rtn

Long ll_cnt

String ls_ClipBoard

// Varibles for File Name,path

string docpath, docname[]

integer i, li_cnt, li_filenum

long ll_xls_cols,ll_xls_rows

// Get file path

GetFileOpenName("Select File", docpath, docname[], "DOC", "Excel Files (*.xlsx),*.xlsx," + "All Files (*.*), *.*", "C:\Program Files\Sybase", 18)

if docpath='' then return

//MessageBox("Document Path ", docpath)

excel = create OLEObject

li_rtn = excel.ConnectToNewObject("excel.application")

IF li_rtn 0 THEN

MessageBox('Excel error','can not run Excel Program')

DESTROY excel

RETURN 0

END IF

excel.WorkBooks.Open(docpath )

excel.Application.Visible = false

excel.windowstate = 2 // 1 : Normal, 2 : Minimize, 3 : Maximize

lb_sheet_rtn = excel.worksheets(1).Activate

ls_ClipBoard = Clipboard() //Store current clipboard Data

// To Find the No Of Rows and Columns in the excel sheet

ll_xls_cols = excel.worksheets(1).Usedrange.columns.count

ll_xls_rows = excel.worksheets(1).Usedrange.rows.count

ll_xls_rows = ll_xls_rows -1 // Considering row 1 has headers

MessageBox("Rows and Cols In the Excel",string(ll_xls_rows)+ " Rows and "+string(ll_xls_cols)+" Columns Will be imported to application")

// To get the value of a cell in the excel sheet

// You can do your validations on the Excel Cells data here based on your business requirements

MessageBox("A1 cell value",string(excel.worksheets(1).cells(1,1).value)) // First Row First Column in the excel sheet

MessageBox("A2 cell value",string(excel.worksheets(1).cells(2,1).value)) // First Row First Column in the excel sheet

excel.Worksheets(1).Range("A2:D5").Copy // I have hard coded the Cells here your can change by using no of rows and columns and concatinating them

// copy to clipboard - Take enough Range. .. Or use the UsedRage property. If data ha header row, use correct statring range.. (B1:... )

ll_cnt = dw_1.importclipboard()

Clipboard(ls_ClipBoard) //Restore clipboard

IF ll_cnt Messagebox("Information", "Unable to Load the data")

END IF

excel.Application.Quit

excel.DisConnectObject()

DESTROY excel

Output: i/ Run the application

ii/ Click the Import Button

iii/ Select the file

iv/ open

Import Data From Excel to Datawindow in PowerBuilder - Image 2

Import Data From Excel to Datawindow in PowerBuilder - Image 3

Import Data From Excel to Datawindow in PowerBuilder - Image 4

Import Data From Excel to Datawindow in PowerBuilder - Image 5

Import Data From Excel to Datawindow in PowerBuilder - Image 6

Right Knowledge is the Ultimate Solution to All Our Problems - BG

This blog is listed under Development & Implementations Community

Related Posts:
Post a Comment

Please notify me the replies via email.

Important:
  • We hope the conversations that take place on MyTechLogy.com will be constructive and thought-provoking.
  • To ensure the quality of the discussion, our moderators may review/edit the comments for clarity and relevance.
  • Comments that are promotional, mean-spirited, or off-topic may be deleted per the moderators' judgment.
You may also be interested in
 
Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url

Back to Top