|
VFP ·R¥ÎªÌªÀ°Ï ¥»°Q½×°Ï¬° Visual Foxpro ·R¥ÎªÌ¸gÅç¥æ¬yªº¦a¤è, ½Ð¦h¦h§Q¥Î"·j´M"ªº¥\¯à, ¥ý¬d¬Ý¬Ý¦³µL«e¨Ò¥i´`, ¦pªGÁÙ¦³¤£À´ªº¦Aµo°Ý. ³¡¥÷¥DÃD¦³ªþ¥[ÀÉ®×, ¶·¥ýµù¥U¦¨¬°ªÀ°Ï©~¥Á¤~¥i¥H¤U¸ü.
|
¤W¤@½g¥DÃD :: ¤U¤@½g¥DÃD |
µoªí¤H |
¤º®e |
kojanlai0823
µù¥U®É¶¡: 2011-12-02 ¤å³¹: 74
²Ä 1 ¼Ó
|
µoªí©ó: ¬P´Á¥| ¤¤ë 24, 2012 12:02 pm ¤å³¹¥DÃD: IMPORT FROM EXCEL |
|
|
§Ú¥Îvfpªºimport«ü¥O..¶×¤JexcelªºÀÉ®×
¤é´Á®æ¦¡·|Åܦ¨¤å¦r..
¤]´N¬O1900/1/1---->1
2011/1/1---->40544
¦ý..§Ú«o¦b¤¤¶¡µo²{¤F¤@ӫܱj¤jªº°ÝÃD
¦pªG¥Îdtoc(ctod(date_str)+Nday)==>·|±o¨ì¤é´Á
¦ý¦b1900/2/28¤§«e³£ÁÙ¦³¬Û¹ïÀ³
¤§«áªº¤é´Á¥þ³¡¦ì²¾
«ç»ò»¡°Ç???
DATE_STR='1899/12/31'
¨º»ò·í§Ú¥ÎDTOC(CTDO(DATE_STR)+1)==>1900/1/1
²z©Ò·íµMªº..·í+59ªº®ÉÔ´N¬O1900/2/28
¦ý¦pªG¦bfoxpro+60®É=>1900/3/1
¦ýexcelªº60---->1900/2/29
©Ò¥H¤Z±qexcelª½±µimport¦Ó¨Óªº¤é´Á§Ç¦CÈ..¦b1900/2/28«áªº¤é´Á..»Ý-1«á¦A¸ò1899/12/31ªº§Ç¦CÈ°µ¬Û¥[ªº³B²z |
|
¦^³»ºÝ |
|
|
syntech
µù¥U®É¶¡: 2003-05-16 ¤å³¹: 4212 ¨Ó¦Û: Taipei,Taiwan
²Ä 2 ¼Ó
|
µoªí©ó: ¬P´Á¥| ¤¤ë 24, 2012 5:34 pm ¤å³¹¥DÃD: |
|
|
±Í¥q¤£³B²z¹q¸£µo©ú«eªº°ÝÃD.
½Ð¹q¬¢M$¤½¥q
XD _________________ ¦pªG¤½¥q¦³¤U¦C§xÂZ:
1. §ä¤£¨ì«K©y,§Ö³t,²©öªº ¥Í²£±Æµ{³nÅé
2. ¤£ª¾¹D¦p¦ó§Ö³t±Æ©w ±ÄÁÊp¹º
3. ¦¨¥»§ì¤£·Ç,¦Û¤vºâ¤ñ³nÅéºâ¦³¥Î
4. ·Q¾Ç²ß¨t²Î³W¹º,·Q§ä¨t²Î¬[ºcªºÅU°Ý
½ÐÁpµ¸§ÚÌ,¤]³\§ÚÌÀ°±o¤W¦£ |
|
¦^³»ºÝ |
|
|
ckp6250
µù¥U®É¶¡: 2004-07-30 ¤å³¹: 1645
²Ä 3 ¼Ó
|
µoªí©ó: ¬P´Á¥| ¤¤ë 24, 2012 6:35 pm ¤å³¹¥DÃD: |
|
|
import ¤§«e , ¥ý¥Î ole ¥[¤u¤@¤U |
|
¦^³»ºÝ |
|
|
garfield Site Admin
µù¥U®É¶¡: 2003-01-30 ¤å³¹: 2157
²Ä 4 ¼Ó
|
|
¦^³»ºÝ |
|
|
aizz
µù¥U®É¶¡: 2007-01-29 ¤å³¹: 171
²Ä 5 ¼Ó
|
µoªí©ó: ¬P´Á¤@ ¤¤ë 28, 2012 10:14 am ¤å³¹¥DÃD: |
|
|
syntech ¼g¨ì: | ±Í¥q¤£³B²z¹q¸£µo©ú«eªº°ÝÃD.
½Ð¹q¬¢M$¤½¥q
XD |
1900¦~¤§«eªº¸ê®Æ..........
³o½ú¤l, §Ú³£¨S¾÷·|±µÄ²
|
|
¦^³»ºÝ |
|
|
aizz
µù¥U®É¶¡: 2007-01-29 ¤å³¹: 171
²Ä 6 ¼Ó
|
µoªí©ó: ¬P´Á¤@ ¤¤ë 28, 2012 10:21 am ¤å³¹¥DÃD: |
|
|
³oÓºô¶¼gªºª±·N, ¤Ï¦Ó¤ñ¸û¦³½ì¦h¦h
|
|
¦^³»ºÝ |
|
|
marvin
µù¥U®É¶¡: 2004-06-01 ¤å³¹: 321
²Ä 7 ¼Ó
|
µoªí©ó: ¬P´Á¤» ¤»¤ë 02, 2012 12:22 am ¤å³¹¥DÃD: |
|
|
¤W±ªº VFPExcel2007Functions ¦³¤H¸Õ¹L¶Ü?
¦³ bug ¹L¤£¨ì, °£¿ù¤¤ |
|
¦^³»ºÝ |
|
|
marvin
µù¥U®É¶¡: 2004-06-01 ¤å³¹: 321
²Ä 8 ¼Ó
|
µoªí©ó: ¬P´Á¤G ¤»¤ë 19, 2012 1:30 am ¤å³¹¥DÃD: |
|
|
¸g¦h¦¸´ú¸Õ import Excel ªº¤èªk, ¤ß±o¦p¤U:
¥Î import from «ü¥O¥u¯à¶×¤J 5.0/95 ©Î¥H«eªºÀÉ®×
¤Wzªº VFPExcel2007Functions, ¬O¨Ï¥Î
Provider=Microsoft.ACE.OLEDB.12.0, ¦ýºô¤W´XµL¸ê®Æ¨Ñ°Ñ¦Ò
°ÝÃD¦b¹B¦æ®É·|µL¬G¥X²{ Select Data Source ªº dialog window, Ä~¦Ó°±¤î, ¸ò¥»¤£¯à¨Ï¥Î
¦Ó¥B³o¤è¦¡°ß¤@¦n³B¬O¯à±±¨î¸ê®Æ²Ä¤@¦Cn¤£n·í§@ header, ¨ä¥¦¥\¯à»P ODBC ¤è¦¡µL¤j¤À§O
¬G«Øij¥Î ODBC ¤è¦¡ (¦p¤U), ±o¥X cursor «á¦A¥Î Foxpro ¨Ó×¥¿©M³B²z
Driver = Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
lcSQLCmd = Select * from "Payment$A1:K36" |
|
¦^³»ºÝ |
|
|
syntech
µù¥U®É¶¡: 2003-05-16 ¤å³¹: 4212 ¨Ó¦Û: Taipei,Taiwan
²Ä 9 ¼Ó
|
µoªí©ó: ¬P´Á¥| ¤T¤ë 05, 2015 4:51 pm ¤å³¹¥DÃD: |
|
|
marvin ¼g¨ì: |
¤Wzªº VFPExcel2007Functions, ¬O¨Ï¥Î
Provider=Microsoft.ACE.OLEDB.12.0, ¦ýºô¤W´XµL¸ê®Æ¨Ñ°Ñ¦Ò
°ÝÃD¦b¹B¦æ®É·|µL¬G¥X²{ Select Data Source ªº dialog window, Ä~¦Ó°±¤î, ¸ò¥»¤£¯à¨Ï¥Î
|
¦w¸Ë³oÓ :
Microsoft Access Database Engine 2010 ¥iÂà´²µo®M¥ó
https://www.microsoft.com/zh-tw/download/details.aspx?id=13255
³oÓ¥i¯à¤]n¸Ë:
Microsoft OLE DB Provider for Visual FoxPro 9.0
http://download.microsoft.com/download/b/f/b/bfbfa4b8-7f91-4649-8dab-9a6476360365/VFPOLEDBSetup.msi
¦]¬°¥Lªºµ{¦¡ª½±µ¥Î
¥N½X: |
m.lnSQL = SQLSTRINGCONNECT([Provider=Microsoft.ACE.OLEDB.12.0;Data Source="] + m.tcXLSFile + [";Extended Properties="Excel 12.0 Xml;HDR=] + m.lcHeaderRow + [;";])
|
©Î¬O§A°®¯Ü§â¤W±¨º¬q®³±¼.
¥u¯d¤U¤U±³o¬q,¥Î¼Ð·ÇODBC³s±µ,¤£¥Î OLE DB PROVIDER³s±µ
¥N½X: |
m.lnSQL = SQLSTRINGCONNECT("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" ;
+ "DBQ="+FULLPATH(m.tcXLSFile)+";")
|
_________________ ¦pªG¤½¥q¦³¤U¦C§xÂZ:
1. §ä¤£¨ì«K©y,§Ö³t,²©öªº ¥Í²£±Æµ{³nÅé
2. ¤£ª¾¹D¦p¦ó§Ö³t±Æ©w ±ÄÁÊp¹º
3. ¦¨¥»§ì¤£·Ç,¦Û¤vºâ¤ñ³nÅéºâ¦³¥Î
4. ·Q¾Ç²ß¨t²Î³W¹º,·Q§ä¨t²Î¬[ºcªºÅU°Ý
½ÐÁpµ¸§ÚÌ,¤]³\§ÚÌÀ°±o¤W¦£
syntech ¦b ¬P´Á¥| ¤T¤ë 12, 2015 10:45 am §@¤F²Ä 3 ¦¸×§ï |
|
¦^³»ºÝ |
|
|
bx1166
µù¥U®É¶¡: 2011-12-06 ¤å³¹: 273
²Ä 10 ¼Ó
|
µoªí©ó: ¬P´Á¥| ¤T¤ë 05, 2015 6:04 pm ¤å³¹¥DÃD: |
|
|
§â³Â·Ðªº¦Ñ¤é´Á¥H¤å¦r³B²z¦AÂন¦~¤ë¤é¤À¶}ªºøó¦ì¤]³\´N·d©w¤F |
|
¦^³»ºÝ |
|
|
kojanlai0823
µù¥U®É¶¡: 2011-12-02 ¤å³¹: 74
²Ä 11 ¼Ó
|
µoªí©ó: ¬P´Á¤ ¤G¤ë 17, 2017 3:31 pm ¤å³¹¥DÃD: |
|
|
marvin ¼g¨ì: | ¸g¦h¦¸´ú¸Õ import Excel ªº¤èªk, ¤ß±o¦p¤U:
¥Î import from «ü¥O¥u¯à¶×¤J 5.0/95 ©Î¥H«eªºÀÉ®×
¤Wzªº VFPExcel2007Functions, ¬O¨Ï¥Î
Provider=Microsoft.ACE.OLEDB.12.0, ¦ýºô¤W´XµL¸ê®Æ¨Ñ°Ñ¦Ò
°ÝÃD¦b¹B¦æ®É·|µL¬G¥X²{ Select Data Source ªº dialog window, Ä~¦Ó°±¤î, ¸ò¥»¤£¯à¨Ï¥Î
¦Ó¥B³o¤è¦¡°ß¤@¦n³B¬O¯à±±¨î¸ê®Æ²Ä¤@¦Cn¤£n·í§@ header, ¨ä¥¦¥\¯à»P ODBC ¤è¦¡µL¤j¤À§O
¬G«Øij¥Î ODBC ¤è¦¡ (¦p¤U), ±o¥X cursor «á¦A¥Î Foxpro ¨Ó×¥¿©M³B²z
Driver = Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
lcSQLCmd = Select * from "Payment$A1:K36" |
§Ú¥Îmicrosoft excel driver ·|¦³mixed type columnªº°ÝÃD..·Q§JªAselect data sourceªº°ÝÃD¤S¤£ª¾¹Dn«ç»ò¤Ustringconnect |
|
¦^³»ºÝ |
|
|
garfield Site Admin
µù¥U®É¶¡: 2003-01-30 ¤å³¹: 2157
²Ä 12 ¼Ó
|
|
¦^³»ºÝ |
|
|
syntech
µù¥U®É¶¡: 2003-05-16 ¤å³¹: 4212 ¨Ó¦Û: Taipei,Taiwan
²Ä 13 ¼Ó
|
µoªí©ó: ¬P´Á¤» ¤G¤ë 18, 2017 4:13 pm ¤å³¹¥DÃD: |
|
|
¥Î odbc Ū¨ú excel ,¬Ü¨¤«Ü¦h....
¥H¤U¬° "·§©À" µ{¦¡
¥N½X: |
CXLSFn = "IID.XLS" && n¶×¤Jªºexcel ÀÉ®×
USE C:\DATA\IID.DBF ALIAS IID_TGT IN 0 EXCLUSIVE && ¥Øªºdbf
TGT_CURSOR = ALIAS()
&& §Q¥Î ODBC ¶}±Ò XLS ÀÉ
LCDSNLESS="DRIVER={MICROSOFT EXCEL DRIVER (*.XLS)};READONLY=0;DBQ="+CXLSFN+"; DRIVERID=22"
NSQLCONNECT = SQLSTRINGCONNECT(LCDSNLESS)
IF NSQLCONNECT >0
=SQLEXEC(NSQLCONNECT,'SELECT * FROM [Sheet1$] ','TMPSQL')
IF SELECT('TMPSQL') !=0
SELECT TMPSQL
SELECT &TGT_CURSOR
GNFIELDCOUNT01 = AFIELDS(GAMYARRAY01)
SELECT TMPSQL
GNFIELDCOUNT = AFIELDS(GAMYARRAY)
&& ¥Î³oÓ¤èªk¦³Ó°ÝÃD,´N¬OEXCEL ¦r¦êÀx¦s®æ³q³qÅܦ¨MEMO,©Ò¥Hn°Ê¤@¤U¤â¸}
&& ¥t¤@Ó°ÝÃD¬O,¦pªGEXCELÀx¦s®æ¨S¦³¤º®e,·|Åܦ¨NULL,¾ÉPREPLACE ¥¢±Ñ,
&& ³o¤]n°Ê¤â¸}.
&& ¥ÎODBCÂà¤Jªº¤è«K³B¬O,
&& A. EXCEL²Ä¤@µ§ROWªº¤º®e¦Û°ÊÅܦ¨Äæ¦ì¦WºÙ.
&& B. ¥u¦³¦³¤º®eªºROW¤~·|Âন°O¿ý
SCAN
SELECT &TGT_CURSOR
APPEND BLANK
FOR I=1 TO GNFIELDCOUNT
FLDNA=FIELD(I)
IF ASCAN(GAMYARRAY01,FLDNA) >0
SELECT TMPSQL
IF !ISNULL(TMPSQL.&FLDNA)
DO CASE
CASE TYPE(TGT_CURSOR+'.'+FLDNA) ='C' AND
TYPE('TMPSQL.'+FLDNA) ='N'
SELECT &TGT_CURSOR
REPLACE &FLDNA WITH ALLTRIM(STR(EVAL('TMPSQL.'+FLDNA)))
SELECT TMPSQL
CASE TYPE(TGT_CURSOR+'.'+FLDNA) ='N' AND
TYPE('TMPSQL.'+FLDNA) ='M'
SELECT &TGT_CURSOR
REPLACE &FLDNA WITH VAL(ALLTRIM('TMPSQL.'+FLDNA))
SELECT TMPSQL
CASE TYPE(TGT_CURSOR+'.'+FLDNA) ='D' AND
TYPE('TMPSQL.'+FLDNA) !='D'
&& ²¤¹L
CASE TYPE(TGT_CURSOR+'.'+FLDNA) ='T' AND
TYPE('TMPSQL.'+FLDNA) !='T'
&& ²¤¹L
OTHERWISE
SELECT &TGT_CURSOR
REPLACE &FLDNA WITH TMPSQL.&FLDNA
SELECT TMPSQL
ENDCASE
ENDIF
ENDIF
NEXT
SELECT TMPSQL
ENDSCAN
ENDIF
ENDIF
USE IN TMPSQL
=SQLDISCONNECT(NSQLCONNECT )
|
_________________ ¦pªG¤½¥q¦³¤U¦C§xÂZ:
1. §ä¤£¨ì«K©y,§Ö³t,²©öªº ¥Í²£±Æµ{³nÅé
2. ¤£ª¾¹D¦p¦ó§Ö³t±Æ©w ±ÄÁÊp¹º
3. ¦¨¥»§ì¤£·Ç,¦Û¤vºâ¤ñ³nÅéºâ¦³¥Î
4. ·Q¾Ç²ß¨t²Î³W¹º,·Q§ä¨t²Î¬[ºcªºÅU°Ý
½ÐÁpµ¸§ÚÌ,¤]³\§ÚÌÀ°±o¤W¦£ |
|
¦^³»ºÝ |
|
|
kojanlai0823
µù¥U®É¶¡: 2011-12-02 ¤å³¹: 74
²Ä 14 ¼Ó
|
µoªí©ó: ¬P´Á¤@ ¤G¤ë 20, 2017 10:43 am ¤å³¹¥DÃD: |
|
|
¦ÑÁón¨D..¤£±o¶}±Òexcel...........................¤£µM§Ú´N¶}±Òexcel,¦scsv...§¹¥þµL´Ý©À |
|
¦^³»ºÝ |
|
|
kojanlai0823
µù¥U®É¶¡: 2011-12-02 ¤å³¹: 74
²Ä 15 ¼Ó
|
µoªí©ó: ¬P´Á¤@ ¤G¤ë 20, 2017 10:45 am ¤å³¹¥DÃD: |
|
|
syntech ¼g¨ì: | ¥Î odbc Ū¨ú excel ,¬Ü¨¤«Ü¦h....
¥H¤U¬° "·§©À" µ{¦¡
¥N½X: |
CXLSFn = "IID.XLS" && n¶×¤Jªºexcel ÀÉ®×
USE C:\DATA\IID.DBF ALIAS IID_TGT IN 0 EXCLUSIVE && ¥Øªºdbf
TGT_CURSOR = ALIAS()
&& §Q¥Î ODBC �}±Ò XLS ÀÉ
LCDSNLESS="DRIVER={MICROSOFT EXCEL DRIVER (*.XLS)};READONLY=0;DBQ="+CXLSFN+"; DRIVERID=22"
NSQLCONNECT = SQLSTRINGCONNECT(LCDSNLESS)
IF NSQLCONNECT >0
=SQLEXEC(NSQLCONNECT,'SELECT * FROM [Sheet1$] ','TMPSQL')
IF SELECT('TMPSQL') !=0
SELECT TMPSQL
SELECT &TGT_CURSOR
GNFIELDCOUNT01 = AFIELDS(GAMYARRAY01)
SELECT TMPSQL
GNFIELDCOUNT = AFIELDS(GAMYARRAY)
&& ¥Î³oÓ¤èªk¦³Ó°ÝÃD,´N¬OEXCEL ¦r¦êÀx¦s®æ³q³qÅܦ¨MEMO,©Ò¥Hn°Ê¤@¤U¤â�}
&& ¥t¤@Ó°ÝÃD¬O,¦pªGEXCELÀx¦s®æ¨S¦³¤º®e,·|Åܦ¨NULL,¾ÉPREPLACE ¥¢±Ñ,
&& ³o�]n°Ê¤â�}.
&& ¥ÎODBCÂà¤Jªº¤è«K³B¬O,
&& A. EXCEL²Ä¤@µ§ROWªº¤º®e¦Û°ÊÅܦ¨Äæ¦ì¦WºÙ.
&& B. ¥u¦³¦³¤º®eªºROW¤~·|Âন°O¿ý
SCAN
SELECT &TGT_CURSOR
APPEND BLANK
FOR I=1 TO GNFIELDCOUNT
FLDNA=FIELD(I)
IF ASCAN(GAMYARRAY01,FLDNA) >0
SELECT TMPSQL
IF !ISNULL(TMPSQL.&FLDNA)
DO CASE
CASE TYPE(TGT_CURSOR+'.'+FLDNA) ='C' AND
TYPE('TMPSQL.'+FLDNA) ='N'
SELECT &TGT_CURSOR
REPLACE &FLDNA WITH ALLTRIM(STR(EVAL('TMPSQL.'+FLDNA)))
SELECT TMPSQL
CASE TYPE(TGT_CURSOR+'.'+FLDNA) ='N' AND
TYPE('TMPSQL.'+FLDNA) ='M'
SELECT &TGT_CURSOR
REPLACE &FLDNA WITH VAL(ALLTRIM('TMPSQL.'+FLDNA))
SELECT TMPSQL
CASE TYPE(TGT_CURSOR+'.'+FLDNA) ='D' AND
TYPE('TMPSQL.'+FLDNA) !='D'
&& ²¤¹L
CASE TYPE(TGT_CURSOR+'.'+FLDNA) ='T' AND
TYPE('TMPSQL.'+FLDNA) !='T'
&& ²¤¹L
OTHERWISE
SELECT &TGT_CURSOR
REPLACE &FLDNA WITH TMPSQL.&FLDNA
SELECT TMPSQL
ENDCASE
ENDIF
ENDIF
NEXT
SELECT TMPSQL
ENDSCAN
ENDIF
ENDIF
USE IN TMPSQL
=SQLDISCONNECT(NSQLCONNECT )
|
|
³oӤ覡§Ú¥Î¹L...¦ý¬O¤@¥¹¬YÄæ¬O¼Æ¦r¤å¦r¨Ã¦s..¦b¤£½Õ¾ãexcelªºª¬ªp¤U..§P©w¤å¦rÄæ..¼Æ¦rÅÜnull..§P©w¼Æ¦rÄæ..¤å¦rÅÜnull |
|
¦^³»ºÝ |
|
|
|
|
±z µLªk ¦b³oÓª©±µoªí¤å³¹ ±z µLªk ¦b³oÓª©±¦^ÂФ峹 ±z µLªk ¦b³oÓª©±½s¿è¤å³¹ ±z µLªk ¦b³oÓª©±§R°£¤å³¹ ±z µLªk ¦b³oÓª©±¶i¦æ§ë²¼ ±z µLªk ¦b³oÓª©±ªþ¥[ÀÉ®× ±z µLªk ¦b³oÓª©±¤U¸üÀÉ®×
|
Powered by phpBB © 2001, 2005 phpBB Group ¥¿Å餤¤å»y¨t¥Ñ phpbb-tw ºûÅ@»s§@
|