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

[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

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 (66.86 KiB) Viewed 3143 times

hb_EXCEL_Demo1.ZIP

(10.29 KiB) Downloaded 172 times

to be continue ...

[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 (54.83 KiB) Viewed 3092 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 (96.93 KiB) Viewed 3092 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


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 (82.38 KiB) Viewed 3092 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 183 times

will be continue ...

[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

[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
so GetDataType() will never called again ... is this the normal harbour / HMG Way

[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 172 times

if all is fine i will prepare Source to release so please try if all work with "your" Excel Sheet.

[AUGE_OHR]

hi,

here now Source of Part 1 & 2

hb_DBFEXCEL_SOURCE17.ZIP

(18.08 KiB) Downloaded 199 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

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

[danielmaximiliano]

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
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

[dragancesu]

Maybe without ||
this work

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

[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.