[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 = "漢字名１"
	rs.Fields( 2 ).Value = 1000
	rs.Update
	rs.AddNew
	rs.Fields( 0 ).Value = 2
	rs.Fields( 1 ).Value = "漢字名２"
	rs.Fields( 2 ).Value = 2000
	rs.Update
	rs.AddNew
	rs.Fields( 0 ).Value = 3
	rs.Fields( 1 ).Value = "漢字名３"
	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>