Excel Data into Table
Excel Data into Table
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
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
Re: Excel Data into Table
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:
Kind regards
Harald
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:
Kind regards
Harald
You do not have the required permissions to view the files attached to this post.
Re: Excel Data into Table
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
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
Re: Excel Data into Table
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:
Kind regards
Harald
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
Harald
Re: Excel Data into Table
Dear Harald,
your code worked like a charm!
I had to initialize nRow and nColumn in the loop as shown below 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
your code worked like a charm!
I had to initialize nRow and nColumn in the loop as shown below 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
You do not have the required permissions to view the files attached to this post.
Re: Excel Data into Table
Harald wrote: ↑26 Nov 2020, 16:52Hello 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:
Kind regardsCode: 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
Harald
i want to open excel file and read. i wanted to try above code. what will be in fnGetExcelFileName function?
Re: Excel Data into Table
Hello Soni,
the function saves current folder, calls SalDlgOpenFile with excel extensions, and restore the current folder.
Kind regards
Harald
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
Harald
Re: Excel Data into Table
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???mina wrote: ↑27 Nov 2020, 14:00Dear 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 tried PropGetRows, PropGetRowHeight Methods, but no luck. Can any one Please help. Thanks in advance.
Re: Excel Data into Table
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.
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.
Who is online
Users browsing this forum: [Ccbot] and 0 guests