MSSQL Óë EXCEL»¥²Ù×÷
SQL SERVER ºÍEXCELµÄÊý¾Ýµ¼Èëµ¼³ö
1¡¢ÔÚSQL SERVERÀï²éѯExcelÊý¾Ý:
-- ======================================================
SELECT *
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
ÏÂÃæÊǸö²éѯµÄʾÀý£¬Ëüͨ¹ýÓÃÓÚ Jet µÄ OLE DB Ìṩ³ÌÐò²éѯ Excel µç×Ó±í¸ñ¡£
SELECT *
from OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------
2¡¢½«ExcelµÄÊý¾Ýµ¼ÈëSQL server :
-- ======================================================
SELECT * into newtable
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
ʵÀý:
SELECT * into newtable
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------
3¡¢½«SQL SERVERÖвéѯµ½µÄÊý¾Ýµ¼³ÉÒ»¸öExcelÎļþ
-- ======================================================
T-SQL´úÂ룺
EXEC master..xp_cmdshell 'bcp ¿âÃû.dbo.±íÃûout c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
²ÎÊý£ºS ÊÇSQL·þÎñÆ÷Ãû£»UÊÇÓû§£»PÊÇÃÜÂë
˵Ã÷£º»¹¿ÉÒÔµ¼³öÎı¾ÎļþµÈ¶àÖÖ¸ñʽ
ʵÀý:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname from pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
ÔÚVB6ÖÐÓ¦ÓÃADOµ¼³öEXCELÎļþ´úÂ룺
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 from ¿âÃû.dbo.±íÃû" queryout E:\DT.xls -c
Ïà¹ØÎĵµ£º
¼ì²éËùʹÓõÄÓï¾äÊÇ·ñ±ê×¼
/*
±ê×¼SQLºÍT-SQLÖ®¼äÓкܶàÇø±ð——Ì«¶àÁË£¬ÕâÀï¾Í²»ËµÁË¡£»¹ÓУ¬Èç¹ûÄãÔÚSQL ServerÉϹ¤×÷£¬
ÄÇôʹÓÃÕâЩ˽ÓеÄÀ©Õ¹ÊÇÓкô¦µÄ¡£ÓÉÓÚÐí¶àSQL ServerµÄÌØÐԵı¾ÖÊ£¬Ä㲻ʹÓ÷DZê×¼µÄÃüÁîµÄ»°£¬
½«»áÓкܶàÇ¿´óµÄ¹¦ÄÜÎÞ·¨ÊµÏÖ¡ ......
Õâ¶Îʱ¼ä¿´Á˹ØÓÚÔÚSQL server ÖÐͨ¹ýÈÕÖ¾ºÍʱ¼äµãÀ´»Ö¸´Êý¾Ý¡£Ò²¿´ÁËһЩÍøÉϵÄÀý×Ó£¬¿´ÈçºÎͨ¹ýÈÕÖ¾À´»Ö¸´Êý¾Ý¡£
Ç°ÌáÌõ¼þ£º
Êý¾Ý¿âµÄ¹ÊÕϻָ´¸ÄΪ·Ç¼òµ¥Ä£Ê½£¬È¥µô×Ô¶¯¹Ø±ÕºÍ×Ô¶¯ÊÕËõÁ½¸öÑ¡Ïî
&nbs ......
ÔÚÒø¹âÖйúÍø£¨SilverlightChina.Net£©ÓÐһƪ"SilverlightÓë³£ÓÃÊý¾Ý¿â»¥²Ù×÷ϵÁÐ"ÎÄÕ£¬ÆäÖнéÉÜÁËʹÓÃSilverlight´æÈ¡²»Í¬Êý¾Ý¿âµÄ·½·¨ºÍ²½Öè¡£µ«ÊǶÔÓÚSilverlight´æÈ¡MS SQL½éÉܵIJ»¹»È«Ã棬ÕâÀïÎÒÏë½éÉÜSilverlightÈçºÎͨ¹ýWCF·ÃÎÊMSSQLÊý¾Ý¿â´æ´¢¹ý³ÌµÄ¡£Ï£Íû¶Ô´ó¼ÒÄܹ»ÓÐËù°ïÖú¡£
ÎÒÃÇҪʵÏÖ£¬ Óû§ÊäÈëÓû§ÃûºÍÃÜ ......
×î½ü£¬¹¤×÷µÄÉÏÐèÒª´¦ÀíÒ»ÏÂÏîÄ¿Öеļ«ÉÙÁ¿Êý¾ÝµÄÖظ´ÎÊÌâ¡£¾¹ý·ÖÎö£¬½á¹û·¢ÏÖÊdzÌÐò²¢·¢Ã»Óд¦ÀíºÃ¶øµ¼Öµġ£¾¹ýÒ»·¬ÃþË÷£¬¶ÔÊý¾Ý¿âÖеÄËø¼°³ÌÐòµÄ²¢·¢ÓÐÁËÒ»µãµãСµÄÐĵá£ÌØдµ½´ËÀ´Óë´ó¼Ò·ÖÏíһϡ£
Ê×ÏÈ£¬ÎÒÃÇÀ´Á˽âÒ»ÏÂʲô½Ð×öËø¡£
......