Excel Data into Table

[mina]

Hi @ll,
still working with TD 3.1 and would like to read an xlsx sheet into a table.
The sheet has a known range of 4 columns filled with strings.
All Excel samples under https://samples.tdcommunity.net/ made to create, write or modify data to excel but not the other way.

Looking for a working sample based on "Microsoft Excel 12.0 Object Library.apl"

Thanks in advance,
Mina

[Harald]

Hi Mina,

i have implemented a function which imports an excel file into an order some years ago. I quickly translated some messages, please remove all my file specific stuff (my file was a little bit more complex, with nSektion in this example it is possible to import a header, an address and the details) and try:

Sample.zip

Kind regards
Harald

[mina]

Harald,
That's really an extensive piece of code!
You use nSektion to choose parts from the sheet - right? As from an invoice Sender Data, Recipient Data, Invoice No, Order Positions etc.
But how did you define sections in an xlsx?

BR
Mina

[Harald]

Hello Mina,

yes, that's correct. The sections are defined by keywords. If the scan starts, nSektion is 0. If any cell in a row contains 'HP Enterprise Service Shipping Request for Stock Hardware', the function knows that the order header (nSektion = 1) will follow in the next rows. In the order header, the function searches for a cell with 'BLN Ref Number', and if this text is found, it's expected that this reference number is in the second cell on the right, and so on.

To make it easier for you, I created a small code snipet which shows you how to read the cells from an excel sheet:

Code: Select all

	If fnGetExcelFileName( hWndForm, sPath, sFileName ) = FALSE
		Return FALSE

	! Set sFileName = 'D:\\E-SD009020591.xls'

	Call vFileName.SetString( sFileName )
	Call vSaveChanges.SetNumber( 0, VT_I2 )

	Call SalActiveXAutoErrorMode( TRUE )

	If iExcelApplication = OBJ_Null
		Set iExcelApplication = new Excel__Application

	If NOT SalActiveXGetActiveObject( iExcelApplication, 'Excel.Application' )

		If NOT iExcelApplication.Create( )
			Call AdvSalMBWarnung( 'Excel could not be started!' )
			Return FALSE

	If iExcelApplication.PropGetWorkbooks( iExcelWorkBooks ) = FALSE

		If NOT iExcelApplication.Create( )
			Call AdvSalMBWarnung( 'Excel could not be started!' )
			Return FALSE

		Call iExcelApplication.PropGetWorkbooks( iExcelWorkBooks )

	Call vUpdateLinks.MakeOptional( )
	Call vReadOnly.MakeOptional( )
	Call vFormat.MakeOptional( )
	Call vPassword.MakeOptional( )
	Call vWriteResPassword.MakeOptional( )
	Call vIgnoreReadOnlyRecommended.MakeOptional( )
	Call vOrigin.MakeOptional( )
	Call vDelimiter.MakeOptional( )
	Call vEditable.MakeOptional( )
	Call vNotify.MakeOptional( )
	Call vConverter.MakeOptional( )
	Call vAddToMru.MakeOptional( )
	Call vLocal.MakeOptional( )
	Call vCorruptLoad.MakeOptional( )

	If iExcelWorkBooks.Open( sFileName, vUpdateLinks, vReadOnly, vFormat, vPassword, vWriteResPassword, vIgnoreReadOnlyRecommended, vOrigin, vDelimiter, vEditable, vNotify, vConverter, vAddToMru, vLocal, vCorruptLoad, iExcelWorkBook )
		Set bFileOpened = TRUE

	If NOT iExcelApplication.PropSetVisible( TRUE )
		Call AdvSalMBWarnung( 'Excel could not be started!' )
		
	...
	
	If iExcelWorkBook.PropGetActiveSheet( iExcelWorkSheet )

		If iExcelWorkSheet.PropGetCells( iExcelRange )

			While nRow < 10000

				Call vRow.SetNumber( nRow , VT_I4 )
	
				While nColumn < 100

					Call vCol.SetNumber( nColumn , VT_I4 )

					If iExcelRange.PropGetItem( vRow, vCol, vValue )

						If vValue.GetString( sString ) = TRUE

							Set sCell = sString
		
					Set nColumn = nColumn + 1

				Set nRow = nRow + 1

	! Excel will still be open

	Return TRUE

Kind regards
Harald

[mina]

Dear Harald,
your code worked like a charm!

I had to initialize nRow and nColumn in the loop as shown below

code.jpg

The next step will be a SalTblInsertRow and the "Read from Excel" can be completed.

Just wondering if there is a better way than PropGetUsedRange/PropGetRows to get the real count of filled rows
because the above functions counts also deleted entries.

Thank you, once again for your great support and assistance!

Regards,
Mina

[soni]

Hello Mina,

yes, that's correct. The sections are defined by keywords. If the scan starts, nSektion is 0. If any cell in a row contains 'HP Enterprise Service Shipping Request for Stock Hardware', the function knows that the order header (nSektion = 1) will follow in the next rows. In the order header, the function searches for a cell with 'BLN Ref Number', and if this text is found, it's expected that this reference number is in the second cell on the right, and so on.

To make it easier for you, I created a small code snipet which shows you how to read the cells from an excel sheet:

Code: Select all

	If fnGetExcelFileName( hWndForm, sPath, sFileName ) = FALSE
		Return FALSE

	! Set sFileName = 'D:\\E-SD009020591.xls'

	Call vFileName.SetString( sFileName )
	Call vSaveChanges.SetNumber( 0, VT_I2 )

	Call SalActiveXAutoErrorMode( TRUE )

	If iExcelApplication = OBJ_Null
		Set iExcelApplication = new Excel__Application

	If NOT SalActiveXGetActiveObject( iExcelApplication, 'Excel.Application' )

		If NOT iExcelApplication.Create( )
			Call AdvSalMBWarnung( 'Excel could not be started!' )
			Return FALSE

	If iExcelApplication.PropGetWorkbooks( iExcelWorkBooks ) = FALSE

		If NOT iExcelApplication.Create( )
			Call AdvSalMBWarnung( 'Excel could not be started!' )
			Return FALSE

		Call iExcelApplication.PropGetWorkbooks( iExcelWorkBooks )

	Call vUpdateLinks.MakeOptional( )
	Call vReadOnly.MakeOptional( )
	Call vFormat.MakeOptional( )
	Call vPassword.MakeOptional( )
	Call vWriteResPassword.MakeOptional( )
	Call vIgnoreReadOnlyRecommended.MakeOptional( )
	Call vOrigin.MakeOptional( )
	Call vDelimiter.MakeOptional( )
	Call vEditable.MakeOptional( )
	Call vNotify.MakeOptional( )
	Call vConverter.MakeOptional( )
	Call vAddToMru.MakeOptional( )
	Call vLocal.MakeOptional( )
	Call vCorruptLoad.MakeOptional( )

	If iExcelWorkBooks.Open( sFileName, vUpdateLinks, vReadOnly, vFormat, vPassword, vWriteResPassword, vIgnoreReadOnlyRecommended, vOrigin, vDelimiter, vEditable, vNotify, vConverter, vAddToMru, vLocal, vCorruptLoad, iExcelWorkBook )
		Set bFileOpened = TRUE

	If NOT iExcelApplication.PropSetVisible( TRUE )
		Call AdvSalMBWarnung( 'Excel could not be started!' )
		
	...
	
	If iExcelWorkBook.PropGetActiveSheet( iExcelWorkSheet )

		If iExcelWorkSheet.PropGetCells( iExcelRange )

			While nRow < 10000

				Call vRow.SetNumber( nRow , VT_I4 )
	
				While nColumn < 100

					Call vCol.SetNumber( nColumn , VT_I4 )

					If iExcelRange.PropGetItem( vRow, vCol, vValue )

						If vValue.GetString( sString ) = TRUE

							Set sCell = sString
		
					Set nColumn = nColumn + 1

				Set nRow = nRow + 1

	! Excel will still be open

	Return TRUE

Kind regards
Harald

i want to open excel file and read. i wanted to try above code. what will be in fnGetExcelFileName function?

[Harald]

Hello Soni,

the function saves current folder, calls SalDlgOpenFile with excel extensions, and restore the current folder.

Code: Select all

Function: fnGetExcelFileName
	Description:
	Returns
		Boolean:
	Parameters
		Window Handle: hWndOwner
		Receive String: sPath
		Receive String: sFileNameWithPath
	Static Variables
	Local variables
		String: strFilters[0:3]
		String: sCurrentPath
		String: sNewPath
		String: sFileName
		Number: nSelect
	Actions
		Set strFilters[0] = 'Excel files ( *.xls, *.xlsx)'
		Set strFilters[1] = '*.xls;*.xlsx'
		Set strFilters[2] = 'All files (*.*)'
		Set strFilters[3] = '*.*'
		Set nSelect = 0
		Call SalFileGetCurrentDirectory( sCurrentPath )
		If sPath = ''
			Set sPath = gv_sMyDocumentsPath
		If SalDlgOpenFile( hWndOwner, 'Datei öffnen', strFilters, 4, nSelect, sFileName, sPath ) = FALSE
			Return FALSE
		Call SalFileGetCurrentDirectory( sNewPath )
		Call SalFileSetCurrentDirectory( sCurrentPath )
		Set sFileNameWithPath = sPath
		Set sPath             = sNewPath
		Return TRUE

Kind regards
Harald

[soni]

Dear Harald,
your code worked like a charm!

I had to initialize nRow and nColumn in the loop as shown below
code.jpg

The next step will be a SalTblInsertRow and the "Read from Excel" can be completed.

Just wondering if there is a better way than PropGetUsedRange/PropGetRows to get the real count of filled rows
because the above functions counts also deleted entries.

Thank you, once again for your great support and assistance!

Regards,
Mina

I used the above code, everything is fine. But i want to run the loop with exact number of rows and columns in the Current Sheet(As it is taking long time to execute). how can i write the code???
I tried PropGetRows, PropGetRowHeight Methods, but no luck. Can any one Please help. Thanks in advance.

[Honorius]

Hi guys!
I am new on TD (7.0.4 x64) and have been developing some simple stuff.
But now my company asks me to create an application that will open an .xlsx file, read some rows/cols and insert the data into a MSSql Server table.
I already read all the code you showed above, and I think it will work for me, but I need help on how to integrate "Excel features" into my TD program. I know it is related to some ActiveX libraries. What should I do before everything?

Thanks in advance,
H.L.O.