excel文件中批量生成insert語(yǔ)句

1、有時(shí)因?yàn)楣ぷ餍枰覀円虮碇袑?xiě)入很多的insert語(yǔ)句，現(xiàn)在有一個(gè)簡(jiǎn)單的方法使我們直接利用excel即可生成insert語(yǔ)句=5文件褊輯=5文件褊輯視圖插入(I)格式工具數(shù)據(jù)窗口址)幫助心日詁蘭丄丨毬逆*逼囂ty|耳卩“鶴已辭和|私心ioo%ABCDEF1MATERIELNAMEM_TESTMATERIELTYPEIN_TIMEOUT_TIMEFLAG2FTLF8524E2KNL8501052011-12-14I3FTLF8524E2KNL850NM52011-12-144FTLF8524E2KNL850NM52011-12-145FTLF8524E2KNL850NM52011-12-146F

2、TLF8524E2KNL850NM52011-12-147FTLF8524E2KNL850NM52011-12-148FTLF8524E2KNL8501052011-12-149FTLF8524E2KNL850NM52011-12-1410FTLF8524E2KNL850NM52011-12-1411FTLF8524E2KNL850NM52011-12-1412FTLF8524E2KNL850NM52011-12-1413FTLF8524E2KNL850NM52011-12-1414151617巴行出開(kāi)始利用公司拼接字2首:先在第一仃數(shù)據(jù)的右側(cè)空付串尋手12B旦G2=(.insertintom

3、list(.materielnamejmaterieltypeiii-tiinejcontractid)valuesCrrN2382rr,todateCyyyy-inm-dd)rr2rr)BCDEFGHIJMTESTMATERIELTYPEINTIMEOUTTIMEcontractid52011-12-14zb0003irtfrtiitom_list(materielnamejm52011-12-14zb0003QA11-1Q-1A在黑框處寫(xiě)如下字符串拼接語(yǔ)句:=(insertintom_list(materielname,materieltype,in_time,contractid)val

4、ues(&A2&,&C2&,to_date(&D2&,yyyy-mm-dd),&F2&);)解釋如下：在“=”后面寫(xiě)公式，字符串部分用雙引號(hào)括起來(lái)，表格公式之間用&連接。A2代表第A(yíng)列，第二行，以此類(lèi)推。在輸入時(shí)間格式時(shí)需注意，數(shù)字在右對(duì)齊時(shí)excel默認(rèn)為數(shù)字型，生成insert語(yǔ)句如下：(insertintomlist(materielnamematerieltype,in-timecontractid)valuesCtcidai:亡&D2此，yyyy-mm一dd)J&F2&)GHIJKLMN0PQRSTUVidlins亡rtintcimlist(mat亡ri亡nam巴mat亡ri亡.ty

5、p亡，intiip.GCintraGtid)walu亡s(FTLF呂524E2KNL呂50NIT.5,tudat(40呂91,wwinm-dd)zb0003)時(shí)間類(lèi)型變成了如下格式，若需要excel識(shí)別其為文本格式，需要在當(dāng)前行左側(cè)加一個(gè)單引號(hào)。例:此時(shí)時(shí)間格式才會(huì)作為文本傳入。(insertintomlist(materielnamejmaterieltypeiii-tiinejcontractid)valuesC/y&A21:口(1玄1:亡(yyyy-inm一ddGHIJKLMN0PQRSTUVidIinsurti此時(shí)的sqliJtomlist(materielname,materielt

6、ype,intime,contractid)valuesCFTLF8524E2KNL850NMJ5jtodateC2011-12-14Jyyyy-inm-ddJ)/zb0003)f才正常。在將其公式下拉復(fù)制到每一行中，批量的sql語(yǔ)句就生成啦:(f000q?(fPP-I-AAAAf-j平po；弓fOOS8TOE3ES8jniJf)sanpA(PTQEMLirb9IHTlUTa1?lipTJzairiinLgiurmygiza口iiQUIOUIT(fsoooqz/(fpp-i-M/1-Zl-TlOZf)3Po；冷ffMN038TOEStESSnijIf)saFApi；DEJ：LIODf9UIT；

7、LITfgdA.giJ：g.euiuiEuygTjgEuiuiOLIT(foooqz/(fpp-i-M/I-El-TIOEj科它po；寫(xiě)ffO038TOEStESSnijIf)sanpApi；3EJ：LI03f9UIT；LITdAfaiJig.euiuiEuyaTjaEuiUIOLIT(foooqs/(fpp-i-Mfff)3Pi冷ffO038TOESESSnijIf)sanAfauipUTdA-faijguiguiEuyaTjaEiiiUI01LIT-(f000qzff(fPp-inui-AAAAfffl-El-TlOEf)3珂o；冷ffWN038TOEStESSnijIf)sanpApi；

8、DEJ：l.LIODf9UIT；UTfadAq.pTJ：gq.EuifauiEUgTJ：g；Eui苗HUIOLIT(foooqz/(fpp-i-M/1-31-1103j平po；乜ffOOS8TOESESSniJ；)sIapi；DEJ：l.LIODf9UIT；_LITdAq.fgTJigq.EuifauiEUgTJ：g；Eui_UIOLIT(foooqz/(fpp-i-M/沖叩o；寫(xiě)ffOOS8TOE3ES8jniJf)sanpApi；DEJ：l.LIODf9UIT；LITfgdAq.piJ：gq.EuifauiEUgTJ：g；EuiUIOLIT(fsoooqz/(fpp-i-M/I-ZI-TI

9、OZf)3Po；冷ffMN038TOEStESSnijIf)saFApi；DEJ：LIODf9UIT；LITfgdA.giJ：g.euiuiEuygTjgEuiUIOLIT(foooqz/(fpp-i-M/I-El-TIOEj科它po；寫(xiě)ffO038TOEStESSnijIf)sanpApi；3EJ：LI03f9UIT；LITdAfaiJig.euiuiEuyaTjaEuiUIOLIT(foooqs/(fpp-i-Mfff)3Pi冷ffO038TOESESSnijIf)sanAfauipUTdA-faijguiguiEuyaTjaEiiiUI01LIT-(f000qzff(fPp-inui-A

10、AAAfffl-El-TlOEf)3珂o；冷ffWN038TOEStESSnijIf)sanpApi；DEJ：l.LIODfauiT；UTfadAq.pTJ：gq.EuifauiEUgTJ：g；Eui苗HUIOLIT(foooqz/(fpp-i-M/1-31-1103j平po；乜ffOOS8TOESESSniJ；)sIapi；DEJ：l.LIODf9UIT；_LITdAq.fgTJigq.EuifauiEUgTJ：g；Eui_UI01-UTIjzasui4JZ9SUT4JZ9SUTIJSSUT4JZ9SUT4JZ9SUT4JZ9SUT4JZ9SUT4JZ9SUTIJSSUT4JZ9SUT4JZ9SUTPHAISQd0