[EXCEL&ADO] ADOを使ったEXCEL(.XLS) の処理


  ORACLE のテーブルを読み込んで、EXCEL(.XLS) を作成し、ADOでEXCEL を読込み、先頭行を更新する




注意 : OraSession は、GLOBAL.ASA で作成されています
  

<HTML><BODY>
<%

Function ExcelTest( strUrl )

	Dim strPath
	Dim FileSystem
	Dim ExcelSheet,Cur
	Dim OraDatabase,SqlQuery,OraDynaset,Columns
	Dim nIdx

	' 物理パスを取得
	strPath = Server.MapPath( strUrl )

	' 無条件削除
	Set FileSystem = Server.CreateObject( "Scripting.FileSystemObject" )
	On Error Resume Next
	FileSystem.DeleteFile strPath
	On Error Goto 0

	set ExcelSheet = Server.CreateObject( "Excel.Sheet" )
	Set Cur = ExcelSheet.ActiveSheet

	Set OraDatabase	= OraSession.GetDatabaseFromPool(10)
	SqlQuery	= "select * from H1.MS04 where 商品コード >= 12"
	Set OraDynaset	= OraDatabase.CreateDynaset(SqlQuery,2)
	Set Columns	= OraDynaset.Fields

	' シート名
	Cur.Name = "商品"

	' 列幅
	Cur.Columns(1).ColumnWidth = 9
	Cur.Columns(2).ColumnWidth = 31
	Cur.Columns(3).ColumnWidth = 13

	' タイトル
	Cur.Cells(1,1).Value = "商品コード"
	Cur.Cells(1,2).Value = "全角品名"
	Cur.Cells(1,3).Value = "在庫評価単価"

	nIdx = 2
	Do while not OraDynaset.EOF
		Cur.Cells(nIdx,1).Value = Columns( "商品コード" ).Value
		Cur.Cells(nIdx,2).Value = Columns( "全角品名" ).Value
		Cur.Cells(nIdx,3).Value = Columns( "在庫評価単価" ).Value
		OraDynaset.MoveNext
		nIdx = nIdx + 1
	Loop

	' 保存
	ExcelSheet.SaveAs( strPath )

	Set cn = Server.CreateObject("ADODB.Connection")
'	strConnection = "Driver={Microsoft Excel Driver (*.xls)}; DBQ=" & strPath & ";"
	strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
			"Data Source=" & strPath & ";" & _
			"Extended Properties=""Excel 8.0;"""

	cn.Open strConnection

	Set rs = Server.CreateObject( "ADODB.Recordset" )

	' 更新する為に必要
	rs.LockType = 2

	' "商品" はシート名
	SqlQuery = "select * from [商品$]"
	rs.Open SqlQuery, cn

	Do while not rs.EOF
		Response.Write rs.Fields( 0 ).Value
		Response.Write "," & rs.Fields( 1 ).Value
		Response.Write "," & rs.Fields( 2 ).Value & "<br>"
		rs.MoveNext
	Loop

	' 先頭行に移動
	rs.MoveFirst

	rs.Fields( 0 ).Value = "99999"
	rs.Fields( 1 ).Value = "TEST"
	rs.Fields( 2 ).Value = "12345"

	' 更新 (タイトルの次の行が更新される)
	rs.Update

	rs.Close
	cn.Close

End Function

Call ExcelTest( "data/TEST.XLS" )

%>
</BODY></HTML>
  







  新しいシートを追加し、項目を定義して名前付けしてデータを追加

  

<%

	Set cn = Server.CreateObject("ADODB.Connection")
	strXls = Server.MapPath( "/lightbox/data/line.xls" )
	strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
			"Data Source=" & strXls & ";" & _
			"Extended Properties=""Excel 8.0;"""

	cn.Open strConnection

	Set rs = Server.CreateObject( "ADODB.Recordset" )
	rs.LockType = 2
	SqlQuery = "select * from 商品"
	rs.Open SqlQuery, cn

	rs.AddNew
	rs.Fields( 0 ).Value = 1
	rs.Fields( 1 ).Value = "漢字名1"
	rs.Fields( 2 ).Value = 1000
	rs.Update
	rs.AddNew
	rs.Fields( 0 ).Value = 2
	rs.Fields( 1 ).Value = "漢字名2"
	rs.Fields( 2 ).Value = 2000
	rs.Update
	rs.AddNew
	rs.Fields( 0 ).Value = 3
	rs.Fields( 1 ).Value = "漢字名3"
	rs.Fields( 2 ).Value = 3000
	rs.Update

	rs.Close
	cn.Close

%>
  

  新規Excel ブックに ORACLE からデータを転送

  

<HTML><BODY>
IE5 -> IIS5 -> ASP -> ADO2.5 -> Excel97<br>
line.xls の sheet1の A1、B1、C1 にタイトルを入力して保存後に実行<br>
<%

	Set OraDatabase	= OraSession.GetDatabaseFromPool(10)
	SqlQuery	= "select * from H1.MS04 where 商品コード >= 12"
	Set OraDynaset	= OraDatabase.CreateDynaset(SqlQuery,2)
	Set Columns	= OraDynaset.Fields

	Set cn = Server.CreateObject("ADODB.Connection")
	strXls = Server.MapPath( "/lightbox/data/line.xls" )
	strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
			"Data Source=" & strXls & ";" & _
			"Extended Properties=""Excel 8.0;"""

	cn.Open strConnection

	Set rs = Server.CreateObject( "ADODB.Recordset" )
	rs.LockType = 2
	SqlQuery = "select * from [sheet1$]"
	rs.Open SqlQuery, cn

	Do while not OraDynaset.EOF
		rs.AddNew
		rs.Fields( 0 ).Value = Columns( "商品コード" ).Value
		rs.Fields( 1 ).Value = Columns( "全角品名" ).Value
		rs.Fields( 2 ).Value = Columns( "在庫評価単価" ).Value
		rs.Update
		OraDynaset.MoveNext
	Loop

	rs.Close

	cn.Execute "insert into [sheet1$] values( 10, '漢字', 30 )"

	cn.Close

%>
</BODT></HTML>
  




yahoo  google  MSDN  MSDN(us)  WinFAQ  Win Howto  tohoho  ie_DHTML  vector  wdic  辞書  天気 


[aspSample]
claudebot
24/03/29 22:16:48
InfoBoard Version 1.00 : Language=Perl

1 BatchHelper COMprog CommonSpec Cprog CprogBase CprogSAMPLE CprogSTD CprogSTD2 CprogWinsock Cygwin GameScript HTML HTMLcss InstallShield InstallShieldFunc JScript JScriptSAMPLE Jsfuncs LLINK OldProg OracleGold OracleSilver PRO PRObrowser PROc PROconePOINT PROcontrol PROftpclient PROjscript PROmailer PROperl PROperlCHAT PROphp PROphpLesson PROphpLesson2 PROphpLesson3 PROphpfunction PROphpfunctionArray PROphpfunctionMisc PROphpfunctionString PROsql PROvb PROvbFunction PROvbString PROvbdbmtn PROvbonepoint PROwebapp PROwin1POINT PROwinSYSTEM PROwinYOROZU PROwindows ProjectBoard RealPHP ScriptAPP ScriptMaster VBRealtime Vsfuncs a1root access accreq adsi ajax amazon argus asp aspSample aspVarious aspdotnet aw2kinst cappvariety centura ckeyword classStyle cmaterial cmbin cmdbapp cmenum cmlang cmlistbox cmstd cmstdseed cmtxt cs daz3d db dbCommon dbaccess dnettool dos download flex2 flex3 flex4 framemtn framereq freeWorld freesoft gimp ginpro giodownload google hdml home hta htmlDom ie9svg install java javaSwing javascript jetsql jquery jsp jspTest jspVarious lightbox listasp listmsapi listmsie listmsiis listmsnt listmspatch listmsscript listmsvb listmsvc memo ms msde mysql netbeans oraPlsql oracle oracleWiper oraclehelper orafunc other panoramio pear perl personal pgdojo pgdojo_cal pgdojo_holiday pgdojo_idx pgdojo_ref pgdojo_req php phpVarious phpguide plsql postgres ps r205 realC realwebapp regex rgaki ruby rule sboard sc scprint scquest sdb sdbquest seesaa setup sh_Imagick sh_canvas sh_dotnet sh_google sh_tool sh_web shadowbox shgm shjquery shvbs shweb sjscript skadai skywalker smalltech sperl sqlq src systemdoc tcpip tegaki three toolbox twitter typeface usb useXML vb vbdb vbsfunc vbsguide vbsrc vpc wcsignup webanymind webappgen webclass webparts webtool webwsh win8 winofsql wmi work wp youtube