Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Disecting an Excel Worksheet
Message
 
 
Information générale
Forum:
Visual FoxPro
Catégorie:
COM/DCOM et OLE Automation
Versions des environnements
Visual FoxPro:
VFP 9 SP2
Divers
Thread ID:
01386393
Message ID:
01386481
Vues:
41
In the first loop once you found both columns (Item and Price) you don't have to check the rest of the headers.

BTW, check my reply in UT forum.

>You can use VARTYPE() to check the type of the values in the Excel columns.
>
>
>oExcel = CreateObject("Excel.Application")
>oWorkbook = oExcel.Workbooks.Open(lcXlsFile)
>
>*oExcel.Visible = .T.
>oSheet = oWorkbook.ActiveSheet
>
>lnHeaderRow = 1
>lnLastCol = oSheet.UsedRange.Columns.Count
>
>lnItemCol = 0
>lnPriceCol = 0
>
>FOR lnCol=1 TO lnLastCol
>	lcHeader = oSheet.Cells(lnHeaderRow, lnCol).Value
>	DO CASE
>	CASE UPPER(ALLTRIM(lcHeader)) == "ITEM"
>		lnItemCol = lnCol
>	CASE UPPER(ALLTRIM(lcHeader)) == "PRICE"
>		lnPriceCol = lnCol
>	ENDCASE	
>ENDFOR
>
>IF lnPriceCol = 0 OR lnItemCol = 0
>	MESSAGEBOX("Wrong spreadsheet")
>	RETURN .F.
>ENDIF
>
>CREATE CURSOR Items (Item C(6), Price N(10,2))  
>lnLastRow = oSheet.UsedRange.Rows.Count
>FOR lnRow = lnHeaderRow + 1 TO lnLastRow
>	lvItemValue = oSheet.Cells(lnRow, lnItemCol).Value
>	DO CASE
>	CASE VARTYPE(lvItemValue) == "N"
>		lcItem = PADL(INT(lvItemValue), 6, "0") 
>	CASE VARTYPE(lvItemValue) == "C"
>		lcItem = lvItemValue	
>	OTHERWISE
>		* Wrong type, do something	
>		LOOP	&& Ignore the row
>	ENDCASE
>	
>	lvPriceValue = oSheet.Cells(lnRow, lnPriceCol ).Value
>	DO CASE
>	CASE VARTYPE(lvPriceValue) == "C"
>		lnPrice = CAST(lvPriceValue AS N(10,2))
>	CASE VARTYPE(lvPriceValue) == "N"
>		lnPrice = lvPriceValue 
>	OTHERWISE	
>		* Wrong type, do something	
>		LOOP	&& Ignore the row
>	ENDCASE
>
>	INSERT INTO Items VALUES (lcItem, lnPrice)
>ENDFOR
>
>...
>
>
If it's not broken, fix it until it is.


My Blog
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform