using Excel COM to read Sheet into Array -> DBF/SQL

Discuss anything else that does not suite other forums.

Moderator: Rathinagiri

Post Reply
User avatar
AUGE_OHR
Posts: 943
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 145 times
Been thanked: 242 times

using Excel COM to read Sheet into Array -> DBF/SQL

Post by AUGE_OHR »

hi,

Part1

i have prepare 1st Demo to show how to get a Excel Sheet using COM into Array and show it in GRID
it also include This.CellColIndex BUG which show Col+1 :shock:

Array get from Excel are Column so we need to convert it to Row :!:

Code: Select all

   iMax := LEN( aExcel )
   FOR i := 1 TO iMax
      AADD( aHead, aExcel[ i ] [ 1 ] )     
      AADD( aWidths, aWide[ i ] * 10 )    // twips ...
   NEXT

   iMax := LEN( aExcel )                  // Rows
   jMax := LEN( aExcel[ 1 ] )             // Columns
   j := 1
   FOR j := 1 TO jMax                     // every Column
      aLine := {}
      i := 1
      FOR i := 1 TO iMax                  // build Line
         IF NIL = aExcel[ i ] [ j ]       // if NIL -> ""
            aExcel[ i ] [ j ] := ""
         ENDIF
         AADD( aLine, aExcel[ i ] [ j ] ) // Line Array
      NEXT
      //  IF J <> 1                       // no Headerline
      AADD( aNew, ACLONE(aLine))
      //  ENDIF
   NEXT
   nEnde := LEN( aNew )
as you seen i create a new Array which i can "manipulate" later but have "Original" in Background.

now we got Data in GRID but it might be that not all belong to "Data" which we want to transfer.
Programmer "expect" that in 1st ROW are Header ... but what it 1st ROW contain "other" data ?
also End must not be last Row so we need nStart, nEndRow ... and nEndCol (click in Header to show BUG)

if we got the Area we still can have Problem with FIELDPUT() while VALTYPE() might incorrect e.g. NIL
not sure about International Version of Excel : does it use "," or "." for Decimal :?:
i have to figure out how to get "Nation" Sign e.g. for "empty" Date like " . . " or " / / "
Excel_Demo1.jpg
Excel_Demo1.jpg (66.86 KiB) Viewed 945 times
hb_EXCEL_Demo1.ZIP
(10.29 KiB) Downloaded 40 times
to be continue ...
have fun
Jimmy

User avatar
AUGE_OHR
Posts: 943
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 145 times
Been thanked: 242 times

Post by AUGE_OHR »

Part2

i have change Layout and add more Button and Textbox.
it is need when Excel Sheet have "Different" Layout or Data Type.
Excel_Demo5.JPG
Excel_Demo5.JPG (54.83 KiB) Viewed 894 times
Excel can use all Type with "Standard" but it will got NIL if Excel can't recognize Type.
this happens e.g. when use a Numeric and use "." instead of "," (or "," instead of "." ?)

i avoid those Problem it is recommend to use

Code: Select all

   oSheet:Columns( i ) :NumberFormat := "#0,00"
   oSheet:Columns( i ) :NumberFormat := "TT.MM.JJJJ"
this might be a Nation Problem while i use a German Excel Version.
so please check it for your Language also Nation Date Format " . . " or " / / "

Question : are there any harbour / HMG Function for Nation Message :?:

---

as Layout a Programmer "expect" Header in 1st line and Data at 2nd Line.
but what if User have used "other" Layout and Header begin in other Line.
for this i include F5-F8 to "set" Line to work and F9 is same for Data Type

look at Sample 1st Data Row.
1st Column "seem" Type "N" but if you scroll down you will see A-Z so this Column ist Type "C"
Column 3 & 4 have NIL and it seems Type "N" but how long (LEN) and how many Decimal :?:
Excel_Demo3.JPG
Excel_Demo3.JPG (96.93 KiB) Viewed 894 times
if i use VAR2CHAR(Number) to get a String and count LEN() i got 6 but when scroll down you will see bigger Number so it is 7.
for LEN() of Type "C" i have try to use "wide" of Excel Column (look at "o:AutoFit()" ) but i'm not sure how to convert Twips :idea:


how to :
to use F5 - F9 first click on Button or use F-Key and than DblClick into "Data-Area" of GRID to mark line.

F8 does NOT work that Way while it try to get "last Column" (there can be Infomation right side which are no Data)
you have to click on Header of Column but This.CellColIndex have a BUG and give Col+1 so type it manuell if need

! Note : if you have used F5-F8 please press F9 again before press F4 else you got to

Code: Select all

Procedure CompareTypeFormat() 
which is only a Dummy which i want to explain in Part3

next Windows show Structure
Excel_Demo4.jpg
Excel_Demo4.jpg (82.38 KiB) Viewed 894 times
here you still can change Structure before start transfer but i do NOT check e.g. FIELD Name (A-Z, 0-9 and NO Number on Front)
i recommend to use F9 and use other line in "Data-Area" for Structure.

btw. ESC will close Window

here is the EXE
it is still under construction. Comments welcome
hb_EXCEL_EXE_1.ZIP
(1.27 MiB) Downloaded 35 times
will be continue ...
have fun
Jimmy

User avatar
bpd2000
Posts: 1159
Joined: Sat Sep 10, 2011 4:07 am
Location: India
Has thanked: 248 times
Been thanked: 142 times

Post by bpd2000 »

Nice creation, continue
After you finish Com version I will use your code to create tool w/o com
i.e. using .dll to read excel file
I am working on it and I will save my time to develop such tool
BPD
Convert Dream into Reality through HMG

User avatar
AUGE_OHR
Posts: 943
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 145 times
Been thanked: 242 times

Post by AUGE_OHR »

hi,

i found a Problem in last Version

Code: Select all

   BUTTON oBtTestType ;
      CAPTION "F9: Data Type" ;
      NOTRANSPARENT ;
      TOOLTIP "activate Row to test data type" ;
      ACTION { || WhatToDo( @cGoesTo, "TESTTYPE" ), GetDataType( @aData, @aType, aNew, nColEnd, nTestRowType ) }
as you can see i have 2 Function in Codeblock but only 1st Function will be called :shock:
so GetDataType() will never called again ... is this the normal harbour / HMG Way :?:
have fun
Jimmy

User avatar
AUGE_OHR
Posts: 943
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 145 times
Been thanked: 242 times

Post by AUGE_OHR »

hi,

i was sure it have work before but neverless now i use it later an work like i want
also Problem to show Row Number i found a Solution. it is when you get a Error Msg and line is 951 in Array ...
! Tip most it is a "bigger" Numeric than your FIELD

press F9 and type in wanted Row or navigate to Row and DblClick or edit manual DBF Structure when show before transfer.

here EXE v0.16
hb_EXCEL_EXE_2.ZIP
(1.27 MiB) Downloaded 29 times
if all is fine i will prepare Source to release so please try if all work with "your" Excel Sheet.
have fun
Jimmy

User avatar
AUGE_OHR
Posts: 943
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 145 times
Been thanked: 242 times

Post by AUGE_OHR »

hi,

here now Source of Part 1 & 2
hb_DBFEXCEL_SOURCE17.ZIP
(18.08 KiB) Downloaded 43 times
now you got the Source to get a Excel Sheet into DBF / SQL but it only work with "optimal" Sheet.
i have got o:Numberformat for every Column from Excel but i only use it to show it on Header.

Part 3 is try to "automate" F5-F9 to find Start/Data/End and right Structure.

when have a bad Structure you will get a Error Message which point to Line in Row.
you have use that Row Number for "next try" to find a Row which match all.

when have o:Numberformat in some case it will "confirm" what get with VALTYPE()
... but it also can "confuse" you so the Question is what is right :roll:

so i don't want to release Part 3 "as Code" while there a too many Ways so we have to discuss if a Way will produce right Value or fail

Code: Select all

   DO CASE
      CASE ccType = NIL // Excel does not recognice Type
         // ---------- safe Type C ----------*
      CASE ccType = "@" .AND. cType = "C"
      CASE ccType = "@" // can have any in a String
         // ---------- safe Type N ----------*
      CASE ccType = "#0,00"
      CASE ccType = "#0.00"
      CASE ccType = "#0"
         // ---------- safe Type D ----------*
      CASE ccType = "TT.MM.JJJJ"  // German
      CASE ccType = "TT.MM.JJ"     // German
         // ---------- safe Type L ----------*
      CASE cType = "L"
         nLen := 1
         // ---------- Type other ------*
      CASE ccType = "Standard"  // most Type
i'm not sure if it make real Sence while it can be a very long list and many User do not format Column "manual".
there a many more o:Numberformat for Type "N" and Type "D" depend on Nation Setting.

to write Part 3 those Information are need so please post what "you" have in Header of GRID and what VALTYPE() say

hope you like the Code which i wrote with harbour / HMG and Help of User from this Forum, thx
have fun
Jimmy

User avatar
danielmaximiliano
Posts: 2320
Joined: Fri Apr 09, 2010 4:53 pm
Location: Argentina
Has thanked: 372 times
Been thanked: 74 times
Contact:

Post by danielmaximiliano »

AUGE_OHR wrote:
Mon Jan 13, 2020 1:46 am
hi,

i found a Problem in last Version

Code: Select all

 ACTION { || WhatToDo( @cGoesTo, "TESTTYPE" ), GetDataType( @aData, @aType, aNew, nColEnd, nTestRowType ) }
as you can see i have 2 Function in Codeblock but only 1st Function will be called :shock:
so GetDataType() will never called again ... is this the normal harbour / HMG Way :?:
try

Code: Select all

 ON KEY F2		     OF Principal ACTION { {|| Fincom()}, {||Stock()  }}
I had the same problem and trying this way it worked
*´¨)
¸.·´¸.·*´¨) ¸.·*¨)
(¸.·´. (¸.·` *
.·`. Harbour/HMG : It's magic !
(¸.·``··*

Saludos / Regards
DaNiElMaXiMiLiAnO

Whatsapp. := +54901169026142
Telegram Name := DaNiElMaXiMiLiAnO

User avatar
dragancesu
Posts: 792
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 30 times
Been thanked: 229 times

Post by dragancesu »

Maybe without ||
this work

ON KEY F8 ACTION ( FiltRec := .F., RecordStatus_3228(), DeleteRecord_3228(), Nil )

User avatar
AUGE_OHR
Posts: 943
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 145 times
Been thanked: 242 times

Post by AUGE_OHR »

hi,

thx for Answer. i will check it again.

as you got the Source you can see where i place GetDataType() now which seems a better place.
have fun
Jimmy

Post Reply